Проследяване на промените в клетки с формула на Excel. Можете ли да покажете кои елементи са се променили в резултат на промяна на определени входни клетки?
Гледам видео
- Проследяването на промените в Excel е малко странно.
- Целта е да се проследи какви клетки от формули в Excel се променят.
- Save As, за да запазите работната книга като XLSM.
- Променете Macro Security.
- Запишете макрос, за да разберете кода, за да настроите условно форматиране за числа, които не са равни на 2.
- Изберете желаното форматиране.
- Запишете друг макрос, за да научите как да премахнете CF от работния лист.
- В макроса добавете цикъл за всеки работен лист.
- Добавете оператор IF, за да го предотвратите да се изпълнява в Title.
- Добавете цикъл, за да проверите всяка клетка с формула.
- Добавете условно форматиране, за да видите дали стойността на клетката в момента се изпълнява.
- Върнете се в Excel.
- Добавете фигура. Задайте макроса на формата.
- Щракнете върху Shape, за да стартирате макроса.
- Бонус съвет: Плъзгане на VBA модул към нова работна книга.
Видео транскрипция
Научете Excel от подкаст, епизод 2059: Промени в проследяването на Excel (в резултатите от Формула)
Хей, добре дошъл в мрежата, аз съм Бил Джелен. Днешният въпрос, изпратен от Монреал за промените в пистите. Проследяване на промените, добре. Така че ето какво имаме. Имаме 4 входни клетки и цял куп клетки от Формула, които разчитат на тези входни клетки. И ако бих включил, ще се върна в раздела Преглед, ще включа подчертаване на промените, Проследяване на промените по време на редактиране, щракнете върху OK И ме предупредиха, че трябва да запазят работната книга и че макросите не могат да се използват в споделени работни книги. Ти знаеш това? Това е проблемът, когато проследявате промените, те споделят работната книга и има цял куп неща, които не могат да се случат в споделените работни книги, знаете ли, като макроси и цял куп други неща. Но нека просто да разгледаме как промяната на следите работи в Excel днес.
Да вземем това 2 и да променим от 2 на 22, и да вземем това 4 и да го променим от 4 на 44. Добре, и виждате ли, това, което те са отбелязали при промените в пистата е, че тези две клетки са се променили, добре, тези лилави триъгълници са действителните промени в пистата. Всички тези червени неща, това не се случва, но просто илюстрирах, че всички тези червени клетки се променят и проследяването на промени не казва нищо за тези промени, нали? И така, просто се казва, че тези две клетки са променени, но всички тези други клетки също са променени. И така, тогава въпросът от Монреал е, има ли начин промяната на следите да ни покаже всичко, което се променя, не само тези входни клетки са се променили?
Добре, така че първото нещо, което трябва да направим, е да изключим вградените в Excel Track Changes. И тогава, има ли начин, по който можем да се сдобием - можем да изградим собствена система за промяна на следите, която да ни позволи да видим всички променили се клетки с формула? Добре, така че стъпка 1 и тази стъпка е най-важната стъпка, не пропускайте това. Погледнете файла си, той се нарича нещо XLSX, трябва да запазите това: File, Save As, As a macro enabled workbook, или нищо от това няма да работи. Трябва да щракнете с десния бутон на мишката, да персонализирате лентата, да включите Developer, след като стигнете до Developer, да отидете на Macro Security, да промените тази настройка - тази, която казва, че няма да оставим макросите да работят или дори няма да кажем вие, че те са там за тази настройка. Трябва да направите тези две стъпки. Вече направих тези две стъпки. Живея всеки ден с тези две стъпки.Вече е коригирано, но ако не сте нов в макросите, това е ново за вас. И тогава трябва да разберем какъв вид форматиране искате. Добре, така че просто ще избера някои клетки тук, ще запиша макрос, наречен HowToCFRed, няма да задавам в клавиш за бърз достъп, защото това никога няма да се стартира отново. Просто записвам код, за да разбера как работи условното форматиране. И ще влезем в Начало, Условно форматиране, Маркиране на клетки, които не са равни на - И така, Още правила, Форматиране на клетки, които не са равни - Виждате ли това? Това не е в оригиналното падащо меню, но ако влезете тук, не е равно на 2 и след това изберете формата. Това е важната част. Така че ще избера червен фон. Тук избираш какъвто цвят искаш, нали? Дори отидете на More Colors, изберете някой друг червен,отидете в Custom, изберете някой друг червен, нали? Това е красотата на Macro Recorder, те ще ни дадат идеално червено за вас или синьо или каквото и да е, което искате. Добре, щракнете върху OK. И тогава, ще спрем да записваме, добре. Отново, целият смисъл на това е само да видим какъв е кодът за условни формати.
Отивам на Макроси, Как да форматирам условно червено и да редактирам. Добре, ето важните части на този код. Виждам, че добавят условен формат с помощта на xlNotEqual и трудно го цитираме, за да не е равно на 2. И тогава променяме вътрешността на клетката на този цвят.
Добре, аз също трябва да разбера как да изтрия цялото условно форматиране на листа. И така, обратно към Excel, Запишете друг макрос, Как да изтрия всички условни, ОК. Елате тук в раздела Начало, отидете на Условно форматиране, Изчистете правилото от целия лист, Спрете записването и ще разгледаме този код. Чудесно, това е едноредов макрос. И дори ми харесва тук, че начинът, по който го правят за целия лист, е, че той просто се отнася до клетки. С други думи, всички клетки на активния лист.
Сега трябва да направя този макрос, записаният макрос, малко по-общ. И написах много книги за това как да правя VBA в Excel и съм правил видеоклипове за това как да правя VBA в Excel и ето простото нещо: трябва да можете да запишете макрос като този, но след това добавете около пет или шест реда, за да може макросът да стане достатъчно общ.
И ще говоря за тези редове, добре. Така че първото нещо, което искам да направя, е да кажа, искам да прегледам активната работна книга, да прегледам всички работни листове. Така че за всеки работен лист WS е обектната променлива, ще разгледам всички работни листове. И човекът от Монреал каза: „Хей, има един лист, на който не искам това да се случва.“ Така че, ако WS.Name с името на точката на работния лист не е равно на Title, тогава ще направим кода в макроса. Ето името на листа: .Cells.FormatConditions.Delete. И така, ще преминем през всеки отделен елемент на листа с изключение на заглавието и ще изтрием всички условия за форматиране, след това ще преминем през всяка клетка на листа, но не всички клетки, а само клетките, които имат формули . Ако няма формула, тогава нямамне трябва да го форматирате, защото няма да се промени. Cell.FormatConditions.Add, това е директно от макроса, въпреки че записаният макрос казва Selection - не искам да трябва да го избирам, така че просто ще кажа Cell, това е всяка отделна клетка. Ще използваме xlNotEqual и вместо Formula: = ”=” 2, което записаният код направи точно там, обединих всичко, което е в тази клетка. Така че проверяваме дали не е равно на текущата стойност. Така че, ако в момента клетката има 2, казваме, че не е равно на 2. Ако в момента клетката има 16,5, казваме, че не е равно на 16,5. И тогава останалото е просто направо записан макрос, записан макрос, записан макрос, записан макрос. Всичко това е от записан макрос. Завършете това Ако с край Ако. Завършете това за със следващ WS. няма да се промени. Cell.FormatConditions.Add, това е директно от макроса, въпреки че записаният макрос казва Selection - не искам да трябва да го избирам, така че просто ще кажа Cell, това е всяка отделна клетка. Ще използваме xlNotEqual и вместо Formula: = ”=” 2, което записаният код направи точно там, обединих всичко, което е в тази клетка. Така че проверяваме дали не е равно на текущата стойност. Така че, ако клетката в момента има 2, казваме, че не е равно на 2. Ако в момента клетката има 16,5, казваме, че не е равно на 16,5. И тогава останалото е просто направо записан макрос, записан макрос, записан макрос, записан макрос. Всичко това е от записан макрос. Завършете това Ако с край Ако. Завършете това за със следващ WS.няма да се промени. Cell.FormatConditions.Add, това е директно от макроса, въпреки че записаният макрос казва Selection - не искам да трябва да го избирам, така че просто ще кажа Cell, това е всяка отделна клетка. Ще използваме xlNotEqual и вместо Formula: = ”=” 2, което записаният код направи точно там, обединих всичко, което е в тази клетка. Така че проверяваме дали не е равно на текущата стойност. Така че, ако в момента клетката има 2, казваме, че не е равно на 2. Ако в момента клетката има 16,5, казваме, че не е равно на 16,5. И тогава останалото е просто направо записан макрос, записан макрос, записан макрос, записан макрос. Всичко това е от записан макрос. Завършете това Ако с край Ако. Завършете това за със следващ WS.това е директно от макроса, въпреки че записаният макрос казва Selection - не искам да трябва да го избирам, така че просто ще кажа Cell, това е всяка отделна клетка. Ще използваме xlNotEqual и вместо Formula: = ”=” 2, което записаният код направи точно там, обединих всичко, което е в тази клетка. Така че проверяваме дали не е равно на текущата стойност. Така че, ако в момента клетката има 2, казваме, че не е равно на 2. Ако в момента клетката има 16,5, казваме, че не е равно на 16,5. И тогава останалото е просто направо записан макрос, записан макрос, записан макрос, записан макрос. Всичко това е от записан макрос. Завършете това Ако с край Ако. Завършете това за със следващ WS.това е директно от макроса, въпреки че записаният макрос казва Selection - не искам да трябва да го избирам, така че просто ще кажа Cell, това е всяка отделна клетка. Ще използваме xlNotEqual и вместо Formula: = ”=” 2, което записаният код направи точно там, обединих всичко, което е в тази клетка. Така че проверяваме дали не е равно на текущата стойност. Така че, ако в момента клетката има 2, казваме, че не е равно на 2. Ако в момента клетката има 16,5, казваме, че не е равно на 16,5. И тогава останалото е просто направо записан макрос, записан макрос, записан макрос, записан макрос. Всичко това е от записан макрос. Завършете това Ако с край Ако. Завършете това за със следващ WS.не искам да трябва да го избирам, така че просто ще кажа Cell, това е всяка отделна клетка. Ще използваме xlNotEqual и вместо Formula: = ”=” 2, което записаният код направи точно там, обединих всичко, което е в тази клетка. Така че проверяваме дали не е равно на текущата стойност. Така че, ако в момента клетката има 2, казваме, че не е равно на 2. Ако в момента клетката има 16,5, казваме, че не е равно на 16,5. И тогава останалото е просто направо записан макрос, записан макрос, записан макрос, записан макрос. Всичко това е от записан макрос. Завършете това Ако с край Ако. Завършете това за със следващ WS.не искам да трябва да го избирам, така че просто ще кажа Cell, това е всяка отделна клетка. Ще използваме xlNotEqual и вместо Formula: = ”=” 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, добавете фигура. Тук е лесно да се оформи: Вмъкване, винаги избирам закръглен правоъгълник, тип Reset To Current Values. Ще приложим Начало, центъра и центъра ще го направим малко по-голям. Обичам блясъка. Предполагам, че си мислите, че е глупаво, тъй като не е там, сиянието, настройката, която харесвам, не е там, така че винаги отивам на Page Layout and Effects и избирам втората. И тогава, когато се върна към формата, мога да избера такъв, който всъщност има малко блясък. За мен мисля, че изглежда готино, мисля, че си заслужава. Щракнете с десния бутон, Присвояване на макрос и кажете 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
