Sledujte zmeny v bunkách vzorcov programu Excel. Môžete ukázať, ktoré položky sa práve zmenili v dôsledku zmeny určitých vstupných buniek?
Pozeraj video
- Sledovanie zmien v programe Excel je trochu bizarné.
- Cieľom je sledovať, aké bunky vzorca v programe Excel sa menia.
- Uložiť ako na uloženie zošita ako XLSM.
- Zmeňte zabezpečenie makra.
- Zaznamenajte makro a zistite kód na nastavenie podmieneného formátovania pre čísla, ktoré sa nerovnajú 2.
- Vyberte požadované formátovanie.
- Zaznamenajte ďalšie makro, aby ste sa naučili, ako odstrániť CF z pracovného hárka.
- V makre pridajte slučku pre každý pracovný hárok.
- Pridajte príkaz IF, aby ste zabránili jeho spusteniu v nadpise.
- Pridajte slučku a skontrolujte každú bunku vzorca.
- Pridajte podmienené formátovanie, aby ste zistili, či je hodnota bunky v čase spustenia makra.
- Vráťte sa do Excelu.
- Pridajte tvar. Priraďte makro k tvaru.
- Kliknutím na tvar spustíte makro.
- Bonusový tip: Presunutie modulu VBA do nového zošita.
Prepis videa
Naučte sa Excel z Podcastu, epizóda 2059: Sledovanie zmien v programe Excel (vo výsledkoch vzorca)
Ahoj, vitaj späť na netcastu, som Bill Jelen. Dnešná otázka z Montrealu o zmenách trate. Sledujte zmeny, dobre. Takže tu je to, čo máme. Máme 4 vstupné bunky a celú kopu buniek vzorca, ktoré sa spoliehajú na tieto vstupné bunky. A ak by som sa zapol, vrátim sa na kartu Kontrola, zapnem Zvýrazniť zmeny, Sledovať zmeny pri úpravách, v poriadku kliknite na OK. A varovali ma, že musia zošit uložiť a že makrá nemožno používať v zdieľaných zošitoch. Vieš to? To je problém, keď sledujete zmeny, zdieľajú zošit a existuje veľa vecí, ktoré sa v zdieľaných zošitoch nemôžu stať, viete, napríklad makrá a kopa ďalších vecí. Poďme sa však pozrieť na to, ako dnes fungujú zmeny sledovania v Exceli.
Zoberme si túto 2 a zmeňte z 2 na 22 a zoberme túto 4 a zmeňte to na 4 na 44. Dobre, a vidíte, to, čo si všimli pri zmenách stopy, je, že tieto dve bunky sa zmenili, dobre, tie fialové trojuholníky sú skutočné zmeny trate. Všetky tieto červené veci, to sa nestane, ale iba som ilustroval, že všetky tieto červené krvinky sa menia a sledovanie zmien o nich nič nehovorí, dobre? Hovorí sa teda, že tieto dve bunky boli zmenené, ale zmenené boli aj všetky tieto ďalšie bunky. A tak potom otázka z Montrealu znie, existuje spôsob, ako nám zmeny na trati môžu skutočne ukázať všetko, čo sa mení, nielen tieto vstupné bunky sa zmenili?
Alright, so, first thing we have to do is turn off the Excel built-in Track Changes. And then, is there a way that we can get- we can build our own track changes system that will allow us to see all of the formula cells that changed? Alright, so Step 1 and this step is the most important step, do not skip this. Look at your file, your file is called something XLSX, you have to save this: File, Save As, As a macro enabled workbook , or none of this will work. You have to right-click, Customize the Ribbon, turn on Developer, once you get to Developer, go to Macro Security, change from this setting - the one that says we're not going to let macros run or not even going to tell you that they're there to this setting. You have to do those two steps. I've already done those two steps. I live every day with those two steps. Already fixed, but if you're new to macros, this is new to you. And then, we need to figure out what kind of formatting you want. Alright, so I'm just going to choose some cells here, I'm going to Record a Macro that's called HowToCFRed, I’m not going to assign into a shortcut key because this is never going to run again. I'm just recording code to figure out how conditional formatting works. And we'll get into Home, Conditional Formatting, Highlight Cells that are not equal to - So, More Rules, Format cells not equal to - See that? It's not in the original drop-down but if you come in here, not equal to 2, and then choose the format. This is the important part. So I'm going to choose a red background. You choose whatever color you want here, alright? Even go to More Colors, choose some other red, go into Custom, choose some other red, alright? That's the beauty of the Macro Recorder, they are going to get us some perfect red for you or blue or whatever it is that you want. Alright, click OK. And then, we're going to stop recording, alright. Again, the whole point of this is just to see what the code is for conditional formats.
I'm going to Macros, How to conditional format red, and edit. Alright, so here's the important parts of this code. I can see they're adding a conditional format using the xlNotEqual and we're hard quoting it to not be equal to 2. And then we're changing the interior of the cell to that color.
Alright, I also need to figure out how to delete all conditional formatting on the sheet. So, back to Excel, Record another Macro, How to delete all conditional, OK. Come here to the Home tab, go to Conditional Formatting, Clear Rule from the entire sheet, Stop Recording and we'll go look at that code. Great, it's a one-line macro. And I even like here that the way they do it for the entire sheet is it just refer to cells. So in other words, all the cells on the active sheet.
Now, I need to make this macro, the recorded macro, a little bit more generic. And I've written lots of books about how to do VBA in Excel and I've done videos on how to do VBA in Excel, and here's the simple thing: you need to be able to record a macro like this but then, add about five or six lines in order to be able to make the macro generic enough.
And I'm going to talk about those lines, alright. So the first thing I want to do is I want to say, I want to go through the active workbook, go through all of the worksheets. So for each worksheet, WS is the object variable, I'll go through all the worksheets. And the person from Montreal said, “Hey, there is one sheet that I don't want to have this happen on.” So, if the WS.Name, with the worksheet dot name, is not equal to Title then we're going to do the code in the macro. Here's the sheet name: .Cells.FormatConditions.Delete. So, we're going to go through each individual of the sheet except for the title and delete all the format conditions, then we're going to go through each cell in the sheet but not all the cells, just the cells that have formulas. If it doesn't have a formula then I don't need to format it because it's not going to change. Cell.FormatConditions.Add, this is directly from the macro although the recorded macro said Selection - I don't want to have to select it so I'm just going to say Cell, that's each individual cell. We're going to use the xlNotEqual and instead of Formula:=”=”2 which is what the recorded code did right there, I've concatenated whatever's in that cell. So checking to see if it's not equal to the current value. So if the cell currently has 2, we're saying not equal to 2. If the cell currently has 16.5, we're saying not equal to 16.5. And then the rest of this is just straight recorded macro, recorded macro, recorded macro, recorded macro. All of that is from a recorded macro. Finish this If with an End If. Finish this For with a Next WS.
Alright, so I have a macro called ApplyCF. Go back to Excel, add a shape. Easy to have a shape here: Insert, I always choose a rounded rectangle, type Reset To Current Values. We’ll apply Home, the center, and the center make it a little bit larger. I love the glow. I suppose you think it's silly seeing it's not there, the glow, the setting I like isn't there so I always go to Page Layout and Effects and choose that second one. And then when I go back to the format, I can choose one that actually has a little bit of glow. To me, I think it looks cool, I think it's worth it. Right-click, Assign Macro and say ApplyCF, click OK. Alright, and then what this will do is when I click it, it’ll go through all of these sheets, find all of the formula cells and set up a conditional formatting that says: If these cells not equal to 7, change the color, alright? That's it. It's that fast it, happened that fast. BAM! It's done. And now, watch if I change this one to 11, all of those cells just changed. Now if it goes back to the 1, ahh, the colors changed. So, whatever the value was, when we change- if I change this cell, all of those cells change. If I change this cell, all of those cells change. If I change this cell, all of those cells change.
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.
No hej, chcem sa ti poďakovať, že si sa zastavil. Uvidíme sa nabudúce pri ďalšom netcaste z.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2059.xlsm
