Sledujte změny v buňkách vzorců aplikace Excel. Můžete ukázat, které položky se právě změnily v důsledku změny určitých vstupních buněk?
Sledovat video
- Sledování změn v aplikaci Excel je trochu bizarní.
- Cílem je sledovat, jaké buňky vzorce v aplikaci Excel se mění.
- Uložit jako pro uložení sešitu jako XLSM.
- Změňte zabezpečení maker.
- Zaznamenejte makro a zjistěte kód pro nastavení podmíněného formátování pro čísla, která se nerovnají 2.
- Vyberte požadované formátování.
- Zaznamenejte další makro a zjistěte, jak odstranit CF z listu.
- V makru přidejte smyčku pro každý list.
- Přidejte příkaz IF, abyste zabránili jeho spuštění v nadpisu.
- Přidejte smyčku a zkontrolujte každou buňku vzorce.
- Přidejte podmíněné formátování, abyste zjistili, zda je hodnota buňky v době spuštění makra.
- Vraťte se do Excelu.
- Přidejte tvar. Přiřaďte makro k tvaru.
- Kliknutím na Tvar spustíte makro.
- Bonusový tip: Přetažení modulu VBA do nového sešitu.
Přepis videa
Naučte se Excel z Podcastu, epizoda 2059: Sledování změn v aplikaci Excel (ve výsledcích vzorce)
Hej, vítej zpět na netcastu, jsem Bill Jelen. Dnešní otázka zaslaná z Montrealu o změnách tratí. Sledujte změny, dobře. Takže tady je to, co máme. Máme 4 vstupní buňky a celou řadu buněk vzorce, které se spoléhají na tyto vstupní buňky. A pokud bych se zapnul, vrátím se na kartu Kontrola, zapnu Zvýraznit změny, Sledovat změny během úprav, kliknout na OK, dobře. A varovali mě, že musí uložit sešit a že makra nelze použít ve sdílených sešitech. Ty to víš? To je ten problém, když sledujete změny, sdílejí sešit a existuje spousta věcí, které se ve sdílených sešitech nemohou stát, víte, jako makra a spousta dalších věcí. Pojďme se ale jen podívat na to, jak dnes fungují změny sledování v Excelu.
Vezměme si tuto 2 a změňte z 2 na 22 a vezměte tuto 4 a změňte ji ze 4 na 44. Dobře, a vidíte, to, co si všimli při změnách stopy, je, že tyto dvě buňky se změnily, dobře, ty fialové trojúhelníky jsou skutečné změny stopy. Všechny tyto červené věci, to se neděje, ale jen jsem ilustroval, že všechny tyto červené krvinky se mění a sledování změn neříká nic o těchto změnách, dobře? Prostě to říká, že tyto dvě buňky byly změněny, ale všechny tyto další buňky byly také změněny. A tak otázka z Montrealu zní: Existuje způsob, jak nám změny tratě ve skutečnosti ukazují vše, co se mění, nejen že se tyto vstupní buňky změnily?
Dobře, takže první věcí, kterou musíme udělat, je vypnout vestavěné sledování změn v Excelu. A pak, existuje způsob, který můžeme získat - můžeme si vytvořit vlastní systém změn stopy, který nám umožní vidět všechny buňky vzorců, které se změnily? Dobře, takže krok 1 a tento krok je nejdůležitějším krokem, tento krok nevynechávejte. Podívejte se na svůj soubor, váš soubor se nazývá něco XLSX, musíte ho uložit: Soubor, Uložit jako, Jako sešit s povoleným makrem, nebo nic z toho nebude fungovat. Musíte kliknout pravým tlačítkem, přizpůsobit pás karet, zapnout vývojáře, jakmile se dostanete do vývojáře, přejít na Zabezpečení maker, změnit z tohoto nastavení - to, které říká, že nebudeme nechat makra běžet nebo dokonce neřekneme že jsou tam v tomto nastavení. Musíte udělat ty dva kroky. Tyto dva kroky jsem už udělal. S těmito dvěma kroky žiji každý den.Již opraveno, ale pokud jste v makrech nováčkem, je to pro vás nové. A pak musíme zjistit, jaké formátování chcete. Dobře, takže si zde jen vyberu nějaké buňky, budu nahrávat makro, které se jmenuje HowToCFRed, nebudu přiřadit klávesovou zkratku, protože to už nikdy nebude běžet znovu. Právě nahrávám kód, abych zjistil, jak funguje podmíněné formátování. A dostaneme se do Domů, Podmíněné formátování, Zvýraznění buněk, které se nerovnají - Takže, Další pravidla, Formátovat buňky, které se nerovnají - Vidíte? Není v původním rozevíracím seznamu, ale pokud sem přijdete, nerovná se 2 a poté vyberte formát. To je důležitá součást. Takže si vyberu červené pozadí. Vy si zde vyberete jakoukoli barvu, dobře? Dokonce přejděte do části Více barev, vyberte jinou červenou,přejít na Custom, vybrat si jinou červenou, dobře? To je krása záznamu makra, dostanou pro vás perfektní červenou nebo modrou barvu nebo cokoli chcete. Dobře, klikněte na OK. A potom přestaneme nahrávat, dobře. Jde opět o to, abychom zjistili, jaký je kód pro podmíněné formáty.
Jdu na Makra, Jak podmíněně formátovat červeně a upravovat. Dobře, takže tady jsou důležité části tohoto kódu. Vidím, že přidávají podmíněný formát pomocí xlNotEqual a tvrdě jej citujeme, aby se nerovnal 2. A pak měníme vnitřek buňky na tuto barvu.
Dobře, musím také zjistit, jak odstranit všechny podmíněné formátování na listu. Takže zpět do Excelu, Zaznamenejte další makro, Jak odstranit všechny podmíněné, OK. Pojďte sem na kartu Domů, přejděte na Podmíněné formátování, Vymazat pravidlo z celého listu, Zastavit nahrávání a my se podíváme na tento kód. Skvělé, je to jednořádkové makro. A dokonce se mi tady líbí, že to, jak to dělají pro celý list, je jen odkaz na buňky. Jinými slovy tedy všechny buňky na aktivním listu.
Nyní musím udělat toto makro, zaznamenané makro, trochu obecnější. A napsal jsem spoustu knih o tom, jak dělat VBA v aplikaci Excel a udělal jsem videa o tom, jak dělat VBA v aplikaci Excel, a tady je jednoduchá věc: musíte být schopni zaznamenat takové makro, ale pak přidat asi pět nebo šest řádků, aby bylo makro dostatečně obecné.
A budu o těchto řádcích mluvit, dobře. První věc, kterou chci udělat, je tedy, že chci říct, že chci projít aktivním sešitem, projít všechny listy. Takže pro každý list je WS proměnná objektu, projdu všechny listy. A osoba z Montrealu řekla: „Hej, je tu jeden list, na kterém nechci, aby se to stalo.“ Takže pokud se WS.Name s názvem tečky listu nerovná Title, uděláme kód v makru. Zde je název listu: .Cells.FormatConditions.Delete. Projdeme tedy každého jednotlivce listu kromě názvu a odstraníme všechny podmínky formátu, pak projdeme každou buňku v listu, ale ne všechny buňky, pouze buňky, které mají vzorce . Pokud nemá vzorec, nemámNení třeba jej formátovat, protože se to nezmění. Cell.FormatConditions.Add, toto je přímo z makra, i když zaznamenané makro říká Selection - nechci to vybrat, takže řeknu jen Cell, to je každá jednotlivá buňka. Použijeme xlNotEqual a místo vzorce: = ”=” 2, což je to, co tam nahraný kód udělal, jsem zřetězil, co je v té buňce. Zkontrolujte tedy, zda se nerovná aktuální hodnotě. Takže pokud má buňka aktuálně 2, říkáme nerovná se 2. Pokud má buňka aktuálně 16,5, říkáme nerovná se 16,5. A pak zbytek je jen přímé zaznamenané makro, zaznamenané makro, zaznamenané makro, zaznamenané makro. To vše je ze zaznamenaného makra. Dokončete to Pokud s Konec Pokud. Dokončete to pro další WS. se nezmění. Cell.FormatConditions.Add, toto je přímo z makra, i když zaznamenané makro říká Selection - nechci to vybrat, takže řeknu jen Cell, to je každá jednotlivá buňka. Použijeme xlNotEqual a místo vzorce: = ”=” 2, což je to, co tam nahraný kód udělal, jsem zřetězil, co je v té buňce. Zkontrolujte tedy, zda se nerovná aktuální hodnotě. Takže pokud má buňka aktuálně 2, říkáme nerovná se 2. Pokud má buňka aktuálně 16,5, říkáme nerovná se 16,5. A pak to ostatní je jen přímé zaznamenané makro, zaznamenané makro, zaznamenané makro, zaznamenané makro. To vše je ze zaznamenaného makra. Dokončete to Pokud s Konec Pokud. Dokončete to pro další WS.se nezmění. Cell.FormatConditions.Add, toto je přímo z makra, i když zaznamenané makro říká Selection - nechci to vybrat, takže řeknu jen Cell, to je každá jednotlivá buňka. Použijeme xlNotEqual a místo vzorce: = ”=” 2, což je to, co tam nahraný kód udělal, jsem zřetězil, co je v té buňce. Zkontrolujte tedy, zda se nerovná aktuální hodnotě. Takže pokud má buňka aktuálně 2, říkáme nerovná se 2. Pokud má buňka aktuálně 16,5, říkáme nerovná se 16,5. A pak zbytek je jen přímé zaznamenané makro, zaznamenané makro, zaznamenané makro, zaznamenané makro. To vše je ze zaznamenaného makra. Dokončete to Pokud s Konec Pokud. Dokončete to pro další WS.to je přímo z makra, i když zaznamenané makro říkalo Výběr - nechci to vybrat, takže řeknu jen buňku, to je každá jednotlivá buňka. Použijeme xlNotEqual a místo vzorce: = ”=” 2, což je to, co tam nahraný kód udělal, jsem zřetězil, co je v té buňce. Zkontrolujte tedy, zda se nerovná aktuální hodnotě. Takže pokud má buňka aktuálně 2, říkáme nerovná se 2. Pokud má buňka aktuálně 16,5, říkáme nerovná se 16,5. A pak to ostatní je jen přímé zaznamenané makro, zaznamenané makro, zaznamenané makro, zaznamenané makro. To vše je ze zaznamenaného makra. Dokončete to Pokud s Konec Pokud. Dokončete to pro další WS.to je přímo z makra, i když zaznamenané makro říkalo Výběr - nechci to vybrat, takže řeknu jen buňku, to je každá jednotlivá buňka. Použijeme xlNotEqual a místo vzorce: = ”=” 2, což je to, co tam nahraný kód udělal, jsem zřetězil, co je v té buňce. Zkontrolujte tedy, zda se nerovná aktuální hodnotě. Takže pokud má buňka aktuálně 2, říkáme nerovná se 2. Pokud má buňka aktuálně 16,5, říkáme nerovná se 16,5. A pak zbytek je jen přímé zaznamenané makro, zaznamenané makro, zaznamenané makro, zaznamenané makro. To vše je ze zaznamenaného makra. Dokončete to Pokud s Konec Pokud. Dokončete to pro další WS.Nechci to vybrat, takže řeknu jen buňka, to je každá jednotlivá buňka. Použijeme xlNotEqual a místo vzorce: = ”=” 2, což je to, co tam nahraný kód udělal, jsem zřetězil, co je v té buňce. Zkontrolujte tedy, zda se nerovná aktuální hodnotě. Takže pokud má buňka aktuálně 2, říkáme nerovná se 2. Pokud má buňka aktuálně 16,5, říkáme nerovná se 16,5. A pak zbytek je jen přímé zaznamenané makro, zaznamenané makro, zaznamenané makro, zaznamenané makro. To vše je ze zaznamenaného makra. Dokončete to Pokud s Konec Pokud. Dokončete to pro další WS.Nechci to vybrat, takže řeknu jen buňka, to je každá jednotlivá buňka. Použijeme xlNotEqual a místo vzorce: = ”=” 2, což je to, co tam nahraný kód udělal, jsem zřetězil, co je v té buňce. Zkontrolujte tedy, zda se nerovná aktuální hodnotě. Takže pokud má buňka aktuálně 2, říkáme nerovná se 2. Pokud má buňka aktuálně 16,5, říkáme nerovná se 16,5. A pak to ostatní je jen přímé zaznamenané makro, zaznamenané makro, zaznamenané makro, zaznamenané makro. To vše je ze zaznamenaného makra. Dokončete to Pokud s Konec Pokud. Dokončete to pro další WS.= ”=” 2 což je to, co nahraný kód udělal právě tam, zřetězil jsem cokoli v té buňce. Zkontrolujte tedy, zda se nerovná aktuální hodnotě. Takže pokud má buňka aktuálně 2, říkáme nerovná se 2. Pokud má buňka aktuálně 16,5, říkáme nerovná se 16,5. A pak zbytek je jen přímé zaznamenané makro, zaznamenané makro, zaznamenané makro, zaznamenané makro. To vše je ze zaznamenaného makra. Dokončete to Pokud s Konec Pokud. Dokončete to pro další WS.= ”=” 2 což je to, co nahraný kód udělal právě tam, zřetězil jsem cokoli v té buňce. Zkontrolujte tedy, zda se nerovná aktuální hodnotě. Takže pokud má buňka aktuálně 2, říkáme nerovná se 2. Pokud má buňka aktuálně 16,5, říkáme nerovná se 16,5. A pak zbytek je jen přímé zaznamenané makro, zaznamenané makro, zaznamenané makro, zaznamenané makro. To vše je ze zaznamenaného makra. Dokončete to Pokud s Konec Pokud. Dokončete to pro další WS.zaznamenané makro, zaznamenané makro. To vše je ze zaznamenaného makra. Dokončete to Pokud s Konec Pokud. Dokončete to pro další WS.zaznamenané makro, zaznamenané makro. To vše je ze zaznamenaného makra. Dokončete to Pokud s Konec Pokud. Dokončete to pro další WS.
Dobře, takže mám makro s názvem ApplyCF. Vraťte se do Excelu a přidejte tvar. Tady je snadné mít tvar: Vložit, vždy si vyberu zaoblený obdélník, zadejte Obnovit na aktuální hodnoty. Aplikujeme Home, střed a střed to trochu zvětší. Miluji záři. Předpokládám, že si myslíte, že je hloupé vidět, že tam není, záře, nastavení, které se mi líbí, tam není, takže vždy přejdu na Rozvržení stránky a Efekty a vyberu si ten druhý. A pak, když se vrátím k formátu, mohu vybrat ten, který ve skutečnosti má trochu záře. Podle mě to vypadá skvěle, myslím, že to stojí za to. Klikněte pravým tlačítkem, přiřaďte makro a řekněte ApplyCF, klikněte na OK. Dobře, a pak to, co to udělá, je, když na něj kliknu, projde všechny tyto listy, najde všechny buňky vzorce a nastaví podmíněné formátování, které říká: Pokud se tyto buňky nerovnají 7,změnit barvu, dobře? A je to. Je to tak rychlé, stalo se to tak rychle. BAM! Je to hotovo. A teď sledujte, jestli změním tuto na 11, všechny tyto buňky se právě změnily. Nyní, pokud se vrátí k 1, ach, barvy se změnily. Ať už byla hodnota jakákoli, při změně - pokud změním tuto buňku, změní se všechny tyto buňky. Pokud změním tuto buňku, všechny tyto buňky se změní. Pokud změním tuto buňku, všechny tyto buňky se změní.všechny tyto buňky se mění.všechny tyto buňky se mění.
Dobře, teď je to nový normál. Nyní odsud chci sledovat znovu. Takže resetuji na aktuální hodnoty a pak, když změním tuto na 3, tyto prodeje se změní. Mimochodem, tyto buňky tady a tyto další listy se také změnily v reakci na to. Sledovat změny v Excelu, jak existují? Jo, je to opravdu chromé. Neukazuje vám věci, které se změnily, a to, že musíte ukázat sešit, je hrozná, hrozná věc. Ale s tímto jednoduchým, jednoduchým malým makrem to funguje.
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, chci ti poděkovat, že jsi se zastavil. Uvidíme se příště na dalším netcastu z.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2059.xlsm
