Відстежуйте зміни у клітинках формули Excel. Чи можете ви показати, які елементи щойно змінилися в результаті зміни певних вхідних комірок?
Переглянути відео
- Відстежувати зміни в Excel трохи химерно.
- Мета полягає в тому, щоб відстежувати, які клітинки формули в Excel змінюються.
- Зберегти як, щоб зберегти книгу як XLSM.
- Змініть безпеку макросів.
- Запишіть макрос, щоб зрозуміти код, щоб встановити умовне форматування для чисел, не рівних 2.
- Виберіть потрібне форматування.
- Запишіть ще один макрос, щоб дізнатися, як видалити CF з робочого аркуша.
- У макросі додайте цикл для кожного аркуша.
- Додайте оператор IF, щоб запобігти його запуску в заголовку.
- Додайте цикл для перевірки кожної комірки формули.
- Додайте умовне форматування, щоб побачити, чи працює значення клітинки на момент макросу.
- Поверніться до Excel.
- Додайте фігуру. Призначте макрос фігурі.
- Клацніть на форму, щоб запустити макрос.
- Бонусна порада: перетягування модуля VBA до нової книги.
Стенограма відео
Дізнайтеся Excel з подкасту, епізод 2059: Зміни треку Excel (у результатах формули)
Привіт, ласкаво просимо назад до трансляції, я Білл Джелен. Сьогоднішнє запитання, надіслане з Монреаля про зміни колії. Зміни треку, добре. Отже, ось що ми маємо. У нас є 4 вхідні клітини та ціла купа клітинок Формули, які покладаються на ці вхідні клітини. І якщо б я ввімкнув, я повернусь на вкладку "Огляд", увімкну "Виділити зміни", "Відстежувати зміни під час редагування", натисніть "ОК", добре. І вони попередили мене, що вони мають зберегти книгу і що макроси не можна використовувати у спільних книгах. Ти це знаєш? Це проблема, коли ви відстежуєте зміни, вони діляться робочою книгою, і є ціла купа речей, які не можуть трапитися у спільних книгах, знаєте, як макроси та ціла купа інших речей. Але давайте просто подивимося, як сьогодні зміни в доріжці працюють в Excel.
Давайте візьмемо це 2 і змінимо з 2 на 22, і візьмемо це 4 і змінимо його з 4 на 44. Добре, і ви бачите, те, що вони зафіксували в змінах доріжок, це те, що ці дві комірки змінилися, добре, ці фіолетові трикутники фактичні зміни доріжки. Всі ці червоні речі цього не трапляються, але я просто проілюстрував, що всі ці червоні клітини змінюються і відстеження змін нічого не говорить про ці зміни, добре? Отже, це просто говорить, що ці дві клітини були змінені, але всі ці інші клітини також були змінені. Тож питання з Монреаля полягає в тому, чи є спосіб, щоб зміни треків насправді показували нам все, що змінюється, а не лише ці клітинки введення змінилися?
Гаразд, отже, перше, що нам потрібно зробити, це вимкнути вбудовані зміни відстеження в Excel. І тоді, чи є спосіб, який ми можемо отримати - ми можемо створити власну систему змін доріжок, яка дозволить нам побачити всі клітини формул, які змінилися? Добре, тому Крок 1 і цей крок є найважливішим кроком, не пропускайте цього. Подивіться на свій файл, ваш файл називається як-то XLSX, ви повинні зберегти це: Файл, Зберегти як, Як книгу з підтримкою макросів, або нічого з цього не буде працювати. Потрібно клацнути правою кнопкою миші, налаштувати стрічку, увімкнути розробника, як тільки ви перейдете до розробника, перейдіть до Macro Security, змініть цей параметр - той, який говорить, що ми не дозволимо запускати макроси або навіть не збираємось повідомляти ви, що вони там до цього налаштування. Вам потрібно зробити ці два кроки. Я вже зробив ці два кроки. Я живу кожен день цими двома кроками.Вже виправлено, але якщо ви не знайомі з макросами, це нове для вас. А потім нам потрібно з’ясувати, яке форматування ви хочете. Гаразд, тому я просто збираюся вибрати тут кілька комірок, я збираюся записати макрос, який називається HowToCFRed, я не збираюся призначати ярлик, тому що це ніколи не буде запускатися знову. Я просто записую код, щоб зрозуміти, як працює умовне форматування. І ми потрапимо до Домашнього, умовного форматування, виділення клітинок, які не дорівнюють - Отже, більше правил, форматування клітинок не дорівнює - бачите? Це не у вихідному спадному меню, але якщо ви зайдете сюди, не дорівнює 2, а потім виберіть формат. Це важлива частина. Тож я збираюся вибрати червоний фон. Ви тут вибираєте будь-який колір, добре? Навіть перейдіть до Додаткові кольори, виберіть інший червоний,зайти в Custom, вибрати інший червоний, добре? У цьому і полягає краса Макрореєстратора, вони отримають нам ідеальний червоний для вас або синій, або що завгодно, що ви хочете. Добре, натисніть OK. А потім, ми припинимо запис, добре. Знову ж таки, вся суть цього полягає лише в тому, щоб побачити, що таке код для умовних форматів.
Я йду до Макроси, Як умовно форматувати червоний, та редагувати. Гаразд, ось важливі частини цього коду. Я бачу, що вони додають умовний формат за допомогою xlNotEqual, і ми твердо цитуємо його, щоб він не дорівнював 2. І тоді ми змінюємо інтер’єр комірки на такий колір.
Гаразд, мені також потрібно з’ясувати, як видалити всі умовні форматування на аркуші. Отже, повернувшись до Excel, Запишіть ще один макрос, Як видалити всі умовні, Добре. Зайдіть сюди на вкладку Домашня сторінка, перейдіть до Умовного форматування, Очистіть правило з цілого аркуша, Зупиніть запис, і ми підемо дивитись на цей код. Чудово, це однорядковий макрос. І мені навіть подобається тут, що те, як вони це роблять для цілого аркуша, це просто посилання на клітинки. Отже, іншими словами, усі клітинки на активному аркуші.
Тепер мені потрібно зробити цей макрос, записаний макрос, трохи загальнішим. І я написав безліч книг про те, як робити VBA в Excel, і я робив відео про те, як робити VBA в Excel, і ось просте: потрібно мати можливість записати такий макрос, але потім додайте приблизно п’ять-шість рядків, щоб зробити макрос достатньо загальним.
І я поговорю про ці рядки, добре. Отже, перше, що я хочу зробити, це те, що я хочу сказати, я хочу переглянути активну книгу, переглянути всі аркуші. Отже, для кожного робочого аркуша WS - це об’єктна змінна, я перегляну всі робочі аркуші. І людина з Монреаля сказала: "Гей, є один аркуш, на якому я не хочу, щоб це траплялося". Отже, якщо ім’я WS.Name з назвою точки робочого аркуша не дорівнює заголовку, тоді ми будемо робити код у макросі. Ось назва аркуша: .Cells.FormatConditions.Delete. Отже, ми будемо переглядати кожну окрему частину аркуша, за винятком заголовка, і видалити всі умови форматування, а потім ми будемо проходити кожну комірку аркуша, але не всі комірки, а лише комірки, які мають формули . Якщо у нього немає формули, то я не знаюне потрібно його форматувати, оскільки це не зміниться. Cell.FormatConditions.Add, це безпосередньо з макросу, хоча записаний макрос сказав Selection - я не хочу, щоб його вибирати, тому я просто скажу Cell, це кожна окрема комірка. Ми використовуватимемо xlNotEqual і замість Формули: = ”=” 2, що саме там робив записаний код, я об’єднав усе, що знаходиться в цій комірці. Тож перевіряємо, чи не дорівнює воно поточному значенню. Отже, якщо комірка наразі має 2, ми говоримо, що не дорівнює 2. Якщо клітина наразі має 16,5, ми говоримо, що не дорівнює 16,5. А решта - це просто записаний макрос, записаний макрос, записаний макрос, записаний макрос. Все це із записаного макросу. Закінчіть це якщо з кінцем якщо. Закінчіть це на наступному WS. s не збирається змінюватися. Cell.FormatConditions.Add, це безпосередньо з макросу, хоча записаний макрос сказав Selection - я не хочу, щоб його вибирати, тому я просто скажу Cell, це кожна окрема комірка. Ми використовуватимемо xlNotEqual і замість Формули: = ”=” 2, що саме там робив записаний код, я об’єднав усе, що знаходиться в цій комірці. Тож перевіряємо, чи не дорівнює воно поточному значенню. Отже, якщо комірка наразі має 2, ми говоримо, що не дорівнює 2. Якщо клітина наразі має 16,5, ми говоримо, що не дорівнює 16,5. А решта - це просто записаний макрос, записаний макрос, записаний макрос, записаний макрос. Все це із записаного макросу. Закінчіть це якщо з кінцем якщо. Закінчіть це на наступному WS.s не збирається змінюватися. Cell.FormatConditions.Add, це безпосередньо з макросу, хоча записаний макрос сказав Selection - я не хочу, щоб його вибирати, тому я просто скажу Cell, це кожна окрема комірка. Ми використовуватимемо xlNotEqual і замість Формули: = ”=” 2, що саме там робив записаний код, я об’єднав усе, що знаходиться в цій комірці. Тож перевіряємо, чи не дорівнює воно поточному значенню. Отже, якщо комірка наразі має 2, ми говоримо, що не дорівнює 2. Якщо клітина наразі має 16,5, ми говоримо, що не дорівнює 16,5. А решта - це просто записаний макрос, записаний макрос, записаний макрос, записаний макрос. Все це із записаного макросу. Закінчіть це якщо з кінцем якщо. Закінчіть це на наступному WS.це безпосередньо з макросу, хоча записаний макрос сказав «Виділення» - я не хочу, щоб його вибирати, тому я просто скажу «Клітинка», це кожна окрема комірка. Ми використовуватимемо xlNotEqual і замість Формули: = ”=” 2, що саме там робив записаний код, я об’єднав усе, що знаходиться в цій комірці. Тож перевіряємо, чи не дорівнює воно поточному значенню. Отже, якщо комірка наразі має 2, ми говоримо, що не дорівнює 2. Якщо клітина наразі має 16,5, ми говоримо, що не дорівнює 16,5. А решта - це просто записаний макрос, записаний макрос, записаний макрос, записаний макрос. Все це із записаного макросу. Закінчіть це якщо з кінцем якщо. Закінчіть це на наступному WS.це безпосередньо з макросу, хоча записаний макрос сказав «Виділення» - я не хочу, щоб його вибирати, тому я просто скажу «Клітинка», це кожна окрема комірка. Ми використовуватимемо xlNotEqual і замість Формули: = ”=” 2, що саме там робив записаний код, я об’єднав усе, що знаходиться в цій комірці. Тож перевіряємо, чи не дорівнює воно поточному значенню. Отже, якщо комірка наразі має 2, ми говоримо, що не дорівнює 2. Якщо клітина наразі має 16,5, ми говоримо, що не дорівнює 16,5. А решта - це просто записаний макрос, записаний макрос, записаний макрос, записаний макрос. Все це із записаного макросу. Закінчіть це якщо з кінцем якщо. Закінчіть це на наступному WS.я не хочу, щоб це було вибрано, тому я просто скажу Cell, це кожна окрема комірка. Ми використовуватимемо xlNotEqual і замість Формули: = ”=” 2, що саме там робив записаний код, я об’єднав усе, що знаходиться в цій комірці. Тож перевіряємо, чи не дорівнює воно поточному значенню. Отже, якщо комірка наразі має 2, ми говоримо, що не дорівнює 2. Якщо клітина наразі має 16,5, ми говоримо, що не дорівнює 16,5. А решта - це просто записаний макрос, записаний макрос, записаний макрос, записаний макрос. Все це із записаного макросу. Закінчіть це якщо з кінцем якщо. Закінчіть це на наступному WS.я не хочу, щоб це було вибрано, тому я просто скажу Cell, це кожна окрема комірка. Ми використовуватимемо xlNotEqual і замість Формули: = ”=” 2, що саме там робив записаний код, я об’єднав усе, що знаходиться в цій комірці. Тож перевіряємо, чи не дорівнює воно поточному значенню. Отже, якщо комірка наразі має 2, ми говоримо, що не дорівнює 2. Якщо клітина наразі має 16,5, ми говоримо, що не дорівнює 16,5. А решта - це просто записаний макрос, записаний макрос, записаний макрос, записаний макрос. Все це із записаного макросу. Закінчіть це якщо з кінцем якщо. Закінчіть це на наступному WS.= ”=” 2, що саме там робив записаний код, я об’єднав усе, що знаходиться в цій комірці. Тож перевіряємо, чи не дорівнює воно поточному значенню. Отже, якщо комірка наразі має 2, ми говоримо, що не дорівнює 2. Якщо клітина наразі має 16,5, ми говоримо, що не дорівнює 16,5. А решта - це просто записаний макрос, записаний макрос, записаний макрос, записаний макрос. Все це із записаного макросу. Закінчіть це якщо з кінцем якщо. Закінчіть це на наступному WS.= ”=” 2, що саме там робив записаний код, я об’єднав усе, що знаходиться в цій комірці. Тож перевіряємо, чи не дорівнює воно поточному значенню. Отже, якщо комірка наразі має 2, ми говоримо, що не дорівнює 2. Якщо клітина наразі має 16,5, ми говоримо, що не дорівнює 16,5. А решта - це просто записаний макрос, записаний макрос, записаний макрос, записаний макрос. Все це із записаного макросу. Закінчіть це якщо з кінцем якщо Закінчіть це на наступному WS.записаний макрос, записаний макрос. Все це із записаного макросу. Закінчіть це якщо з кінцем якщо. Закінчіть це на наступному WS.записаний макрос, записаний макрос. Все це із записаного макросу. Закінчіть це якщо з кінцем якщо Закінчіть це на наступному WS.
Гаразд, отже, у мене є макрос, який називається ApplyCF. Поверніться до Excel, додайте фігуру. Тут легко отримати форму: Вставте, я завжди вибираю округлий прямокутник, набираю Скинути до поточних значень. Ми застосуємо Home, центр і центр, щоб зробити його трохи більшим. Я люблю світіння. Я гадаю, ти вважаєш, що це безглуздо, оскільки його немає, світіння, налаштування, яке мені подобається, немає, тому я завжди переходжу до Макет сторінки та Ефекти і вибираю другий. А потім, повернувшись до формату, я можу вибрати той, який насправді має трохи сяйва. Мені здається, це виглядає круто, я думаю, це того варте. Клацніть правою кнопкою миші, призначте макрос і скажіть ApplyCF, натисніть кнопку OK. Добре, і тоді, що це буде робити, це коли я натисну це, він пройде всі ці аркуші, знайде всі клітинки формули та налаштує умовне форматування, яке говорить: Якщо ці клітинки не дорівнюють 7,змінити колір, добре? Це воно. Це так швидко, так швидко сталося. БАМ! Зроблено. А тепер, стежте, якщо я зміню цю на 11, всі ці клітини просто змінилися. Тепер, якщо це повернеться до 1, ах, кольори змінилися. Отже, яким би значення не було, коли ми змінюємось - якщо я зміню цю клітинку, всі ці клітини змінюються. Якщо я змінив цю клітинку, всі ці клітини змінюються. Якщо я змінив цю клітинку, всі ці клітини змінюються.всі ці клітини змінюються.всі ці клітини змінюються.
Alright, now this is the new normal. Now from here, I want to track again. So I Reset To Current Values and then if I change this one to a 3, those sales change. Oh, by the way, these cells back here and these other sheets also changed in response to this. Track changes in Excel as it exists? Yeah, it's really lame. It doesn't show you the things that changed and having to show the workbook is a horrible, horrible thing. But with this simple, simple little macro, it works.
Alright now, the question said, alright, so if this is working how do I now get this to work in my other workbook? So I have some other workbook and I want to copy this over. Alright, so this is a great little bonus tip here. I'll create a brand new workbook and we'll put some stuff in here and I'll have a couple of formulas, and put a cell up there, alright. So we changed that cell, those 4 cells are all formula cells. Now if I want this workbook, Book2, to also have the code from podcast 2059, well I could retype it all again but that would be silly. So we come here to the Developer tab, and go to Visual Basic. And I want to make sure that I can see Book2 and then I can see Podcast 2059. I simply take that module and drag it and drop it on Book2, right there. And now, that code is also in Book2. Coming back to Book2, just add a shape, right click, Assign Macro, click OK. Alright, it works. And then 3, see, we've now applied that setting to this workbook.
Great question. Great question sent in from Montreal. And in this case, great question that my initial reaction is, well yeah, you're right. Track changes is horrible in Excel. And I wonder if I could create something that would actually track the changes. What are the downsides here and I'm sure I'm going to hear about this in the YouTube comments. If you had 10,000 formula cells, well now, all of these conditional formattings are going to be volatile, the things going to slow down, too many Excel format errors. Yeah, I can see all that but, you know, for a nice small workbook 5,6, 7 sheets, maybe 50 rows per sheet, I would think that this has some chance- some chance are working.
Okay, episode recap: Track changes in Excel, it's a little bizarre especially because they share the workbook. Our goal is to track what formula cells in Excel change. You have to save the workbook as XLSM, change your Macro Security. Record a macro to figure out the code to set up conditional formatting for numbers and not equal to 2, that’s just to figure out what red you want to use. Choose the formatting you want, Record another macro to learn how to remove conditional formatting from the worksheet. And then, to that macro that we recorded the first one, add a loop for each worksheet, an IF statement to prevent it from running on the title sheet, then a loop to check each formula cell. Add conditional formatting to each cell that says, if this CELL.VALUE is not equal to the value at the time that it ran, then we're going to highlight the things. Go back to Excel, assign a shape, add a shape, assign a macro to the shape, click the shape to run the macro. And I also showed you the bonus tip: dragging a VBA module to a new workbook.
Ну, привіт, я хочу подякувати вам за заїзд. Ми побачимось наступного разу для чергової трансляції від.
Завантажити файл
Завантажте зразок файлу тут: Podcast2059.xlsm
