Sledite spremembam v celicah formule - Excel Nasveti

Sledite spremembam v celicah formule Excel. Ali lahko pokažete, kateri elementi so se pravkar spremenili zaradi spremembe določenih vhodnih celic?

Oglejte si video

Sledenje spremembam v Excelu je nekoliko bizarno.
Cilj je slediti spremembam celic formule v Excelu.
Shrani kot, da shranite delovni zvezek kot XLSM.
Spremenite varnost makra.
Posnemite makro, da ugotovite kodo, da nastavite pogojno oblikovanje za števila, ki niso enaka 2.
Izberite želeno oblikovanje.
Posnemite še en makro, da se naučite odstraniti CF z delovnega lista.
V makro dodajte zanko za vsak delovni list.
Dodajte stavek IF, da preprečite njegovo izvajanje v naslovu.
Dodajte zanko, da preverite vsako celico formule.
Dodajte pogojno oblikovanje, da vidite, ali se vrednost celice v času izvaja makronaredbe.
Vrnite se v Excel.
Dodajte obliko. Makri dodelite obliko.
Kliknite obliko, da zaženete makro.
Bonus nasvet: povlecite modul VBA v nov delovni zvezek.

Video zapis

Naučite se Excel iz podcasta, epizoda 2059: Spremembe sledi v Excelu (v rezultatih formule)

Hej, dobrodošli nazaj v oddaji, jaz sem Bill Jelen. Današnje vprašanje o spremembah tirov, poslano iz Montreala. Spremembe sledi, v redu. Torej, to imamo. Imamo 4 vhodne celice in cel kup celic formule, ki se zanašajo na te vhodne celice. In če bi vklopil, se vrnem na zavihek Pregled, vklopim Označi spremembe, Spremljam spremembe med urejanjem in v redu kliknem V redu. In opozorili so me, da morajo shraniti delovni zvezek in da makrov ni mogoče uporabljati v skupnih delovnih zvezkih. Veste to? To je težava, ko sledite spremembam, si delijo delovni zvezek in veste, da se v skupnih delovnih zvezkih ne more zgoditi cel kup stvari, kot so makri in cel kup drugih stvari. A poglejmo si le, kako danes v programu Excel delujejo spremembe skladb.

Vzemimo to 2 in spremenimo z 2 na 22, vzamemo pa to 4 in spremenimo s 4 na 44. V redu, in vidite, pri spremembah skladb so opazili, da sta se ti dve celici spremenili, prav, ta vijolična trikotnika sta dejanske spremembe skladbe. Vse te rdeče stvari se sicer ne zgodijo, vendar sem samo ponazoril, da se vse te rdeče celice spreminjajo in spremembe sledi nič ne govorijo o teh spremembah, v redu? Torej, samo piše, da sta bili spremenjeni ti dve celici, spremenjene pa so bile tudi vse te druge celice. Torej, vprašanje iz Montreala je, ali obstaja možnost, da nam spremembe sledi dejansko pokažejo vse, kar se spremeni, ne samo te vhodne celice?

Torej, prva stvar, ki jo moramo storiti, je, da izklopimo vgrajene spremembe programa Track. In potem, ali obstaja način, ki ga lahko dobimo - lahko zgradimo lasten sistem sprememb sledi, ki nam bo omogočil, da vidimo vse celice formule, ki so se spremenile? Torej, 1. korak in ta korak je najpomembnejši korak, tega ne preskočite. Poglejte svojo datoteko, datoteka se imenuje nekako XLSX, shraniti morate to: Datoteka, Shrani kot, Kot delovni zvezek z omogočenim makrom, ali nič od tega ne bo delovalo. Z desno miškino tipko morate klikniti, prilagoditi trak, vklopiti razvijalca, ko pridete do razvijalca, pojdite na varnost makrov in spremenite to nastavitev - tisto, ki pravi, da makrov ne bomo pustili zagnati ali jih sploh ne bomo povedali vi, da so tam v tej nastavitvi. Ti dve koraki morate storiti. Ta dva koraka sem že naredil. Vsak dan živim s tema dvema korakoma.Že popravljeno, vendar če ste v makrih novi, je to za vas novo. In potem moramo ugotoviti, kakšno oblikovanje želite. Torej, tukaj bom izbral samo nekatere celice, posnel bom makro, ki se imenuje HowToCFRed, ne bom ga dodal v bližnjični ključ, ker se ta ne bo nikoli več zagnal. Samo zapisujem kodo, da ugotovim, kako deluje pogojno oblikovanje. In vstopili bomo v Domov, Pogojno oblikovanje, Označevanje celic, ki niso enake - Torej, Več pravil, Oblikovanje celic, ki niso enake - Vidite to? Ni v prvotnem spustnem meniju, če pa vstopite sem, ni enako 2 in nato izberite obliko. To je pomemben del. Torej bom izbral rdeče ozadje. Tukaj izbereš katero koli barvo, v redu? Pojdite celo na Več barv, izberite drugo rdečo,pojdite v Custom, izberite kakšno drugo rdečo, v redu? To je lepota Macro Recorderja, dobili nam bodo popolno rdečo za vas ali modro ali karkoli že želite. V redu, kliknite V redu. In potem bomo nehali snemati, v redu. Še enkrat, bistvo tega je samo videti, kakšna je koda za pogojne formate.

Odpravil se bom na Makri, Kako pogojno oblikovati rdeče in urediti. Torej, tukaj so pomembni deli te kode. Vidim, da s pogojem xlNotEqual dodajo pogojno obliko in ga težko navajamo, da ni enak 2. In potem spremenimo notranjost celice v to barvo.

Prav tako moram tudi ugotoviti, kako na listu izbrišem vse pogojno oblikovanje. Torej, nazaj v Excel, posnemite še en makro, Kako izbrisati vse pogojne, OK. Pojdite sem na zavihek Domov, pojdite na Pogojno oblikovanje, Počisti pravilo s celotnega lista, Ustavi snemanje in si bomo ogledali to kodo. Super, to je enovrstični makro. In tukaj mi je celo všeč, da način, kako to počnejo za celoten list, je, da se nanaša samo na celice. Z drugimi besedami, vse celice na aktivnem listu.

Zdaj moram narediti ta makro, posneti makro, nekoliko bolj splošen. In napisal sem veliko knjig o tem, kako narediti VBA v Excelu, in naredil sem videoposnetke o tem, kako narediti VBA v Excelu, in tukaj je preprosto: posneti morate takšen makro, nato pa dodajte približno pet ali šest vrstic, da bi lahko naredili makro dovolj generičen.

In govoril bom o teh vrsticah, v redu. Torej, prva stvar, ki jo želim narediti, je, da želim povedati, želim si ogledati aktivni delovni zvezek, pregledati vse delovne liste. Torej, za vsak delovni list je WS spremenljivka predmeta, pregledal bom vse delovne liste. In oseba iz Montreala je rekla: "Hej, en list je, na katerem nočem, da se to zgodi." Torej, če ime WS.Name z imenom pike na delovnem listu ni enako naslovu, bomo naredili kodo v makru. Tu je ime lista: .Cells.FormatConditions.Delete. Torej, pregledali bomo vsakega posameznega lista, razen naslova in izbrisali vse pogoje za oblikovanje, nato pa bomo pregledali vsako celico lista, ne pa tudi vseh celic, samo celice, ki imajo formule . Če nima formule, potem neni ga treba formatirati, ker se ne bo spremenil. Cell.FormatConditions.Add, to je neposredno iz makra, čeprav je posneti makro dejal Selection - ne želim, da bi ga moral izbrati, zato bom rekel samo Cell, to je vsaka posamezna celica. Uporabili bomo xlNotEqual in namesto Formule: = ”=” 2, kar je posneta koda storila tam, združil sem vse, kar je v tej celici. Torej preverite, ali ni enaka trenutni vrednosti. Torej, če ima celica trenutno 2, rečemo, da ni enako 2. Če ima celica trenutno 16,5, rečemo, da ni enako 16,5. In preostanek tega je samo posneti makro, posneti makro, posneti makro, posneti makro. Vse to je iz posnetega makra. Dokončajte to s koncem If. Dokončajte to z naslednjim WS. se ne bo spremenilo. Cell.FormatConditions.Add, to je neposredno iz makra, čeprav je posneti makro dejal Selection - ne želim, da bi ga moral izbrati, zato bom rekel samo Cell, to je vsaka posamezna celica. Uporabili bomo xlNotEqual in namesto Formule: = ”=” 2, kar je posneta koda storila tam, združil sem vse, kar je v tej celici. Torej preverite, ali ni enaka trenutni vrednosti. Torej, če ima celica trenutno 2, rečemo, da ni enako 2. Če ima celica trenutno 16,5, rečemo, da ni enako 16,5. In preostanek tega je samo posneti makro, posneti makro, posneti makro, posneti makro. Vse to je iz posnetega makra. Dokončajte to s koncem If. Dokončajte to z naslednjim WS.se ne bo spremenilo. Cell.FormatConditions.Add, to je neposredno iz makra, čeprav je posneti makro dejal Selection - ne želim, da bi ga moral izbrati, zato bom rekel samo Cell, to je vsaka posamezna celica. Uporabili bomo xlNotEqual in namesto Formule: = ”=” 2, kar je posneta koda storila tam, združil sem vse, kar je v tej celici. Torej preverite, ali ni enaka trenutni vrednosti. Torej, če ima celica trenutno 2, rečemo, da ni enako 2. Če ima celica trenutno 16,5, rečemo, da ni enako 16,5. In preostanek tega je samo posneti makro, posneti makro, posneti makro, posneti makro. Vse to je iz posnetega makra. Dokončajte to s koncem If. Dokončajte to z naslednjim WS.to je neposredno iz makra, čeprav je v posnetem makroju pisalo Selection - nočem, da ga moram izbrati, zato bom rekel samo Cell, to je vsaka posamezna celica. Uporabili bomo xlNotEqual in namesto Formule: = ”=” 2, kar je posneta koda storila tam, združil sem vse, kar je v tej celici. Torej preverite, ali ni enaka trenutni vrednosti. Torej, če ima celica trenutno 2, rečemo, da ni enako 2. Če ima celica trenutno 16,5, rečemo, da ni enako 16,5. In preostanek tega je samo posneti makro, posneti makro, posneti makro, posneti makro. Vse to je iz posnetega makra. Dokončajte to s koncem If. Dokončajte to z naslednjim WS.to je neposredno iz makra, čeprav je v posnetem makroju pisalo Selection - ne želim, da bi ga moral izbrati, zato bom rekel samo Cell, to je vsaka posamezna celica. Uporabili bomo xlNotEqual in namesto Formule: = ”=” 2, kar je posneta koda storila tam, združil sem vse, kar je v tej celici. Torej preverite, ali ni enaka trenutni vrednosti. Torej, če ima celica trenutno 2, rečemo, da ni enako 2. Če ima celica trenutno 16,5, rečemo, da ni enako 16,5. In preostanek tega je samo posneti makro, posneti makro, posneti makro, posneti makro. Vse to je iz posnetega makra. Končaj to If s End If. Dokončajte to z naslednjim WS.ne bi ga moral izbrati, zato bom rekel samo Cell, to je vsaka posamezna celica. Uporabili bomo xlNotEqual in namesto Formule: = ”=” 2, kar je posneta koda storila tam, združil sem vse, kar je v tej celici. Torej preverite, ali ni enaka trenutni vrednosti. Torej, če ima celica trenutno 2, pravimo, da ni enako 2. Če ima celica trenutno 16,5, rečemo, da ni enako 16,5. In preostanek tega je samo posneti makro, posneti makro, posneti makro, posneti makro. Vse to je iz posnetega makra. Dokončajte to s koncem If. Dokončajte to z naslednjim WS.ne bi ga moral izbrati, zato bom rekel samo Cell, to je vsaka posamezna celica. Uporabili bomo xlNotEqual in namesto Formule: = ”=” 2, kar je posneta koda storila tam, združil sem vse, kar je v tej celici. Torej preverite, ali ni enaka trenutni vrednosti. Torej, če ima celica trenutno 2, rečemo, da ni enako 2. Če ima celica trenutno 16,5, rečemo, da ni enako 16,5. In preostanek tega je samo posneti makro, posneti makro, posneti makro, posneti makro. Vse to je iz posnetega makra. Dokončajte to s koncem If. Dokončajte to z naslednjim WS.= ”=” 2, kar je posneta koda storila tam, združil sem vse, kar je v tej celici. Torej preverite, ali ni enaka trenutni vrednosti. Torej, če ima celica trenutno 2, rečemo, da ni enako 2. Če ima celica trenutno 16,5, rečemo, da ni enako 16,5. In preostanek tega je samo posneti makro, posneti makro, posneti makro, posneti makro. Vse to je iz posnetega makra. Dokončajte to s koncem If. Dokončajte to z naslednjim WS.= ”=” 2, kar je posneta koda storila tam, združil sem vse, kar je v tej celici. Torej preverite, ali ni enaka trenutni vrednosti. Torej, če ima celica trenutno 2, rečemo, da ni enako 2. Če ima celica trenutno 16,5, rečemo, da ni enako 16,5. In preostanek tega je samo posneti makro, posneti makro, posneti makro, posneti makro. Vse to je iz posnetega makra. Dokončajte to s koncem If. Dokončajte to z naslednjim WS.posneti makro, posneti makro. Vse to je iz posnetega makra. Dokončajte to s koncem If. Dokončajte to z naslednjim WS.posneti makro, posneti makro. Vse to je iz posnetega makra. Dokončajte to s koncem If. Dokončajte to z naslednjim WS.

V redu, torej imam makro z imenom ApplyCF. Vrnite se v Excel, dodajte obliko. Tukaj je enostavno imeti obliko: Vstavi, vedno izberem zaobljen pravokotnik, vnesite Ponastavi na trenutne vrednosti. Uporabili bomo Home, center in center, da bo nekoliko večji. Obožujem sijaj. Predvidevam, da se vam zdi neumno, če je ni, sijaja, nastavitve, ki mi je všeč, ni, zato vedno grem na Postavitev strani in učinki in izberem tisto drugo. In potem, ko se vrnem k obliki, lahko izberem tisto, ki ima dejansko malo sijaja. Zame se mi zdi kul, mislim, da se splača. Z desno miškino tipko kliknite Assign Macro in recite ApplyCF, kliknite OK. V redu, in potem bo to storilo, ko ga kliknem, bo prešel vse te liste, poiskal vse celice formule in vzpostavil pogojno oblikovanje, ki pravi: Če te celice niso enake 7,spremeniti barvo, v redu? To je to. Tako hitro se je zgodilo tako hitro. BAM! Opravljeno je. In zdaj, pazi, če spremenim tega na 11, so se pravkar spremenile vse te celice. Če se vrne na 1, ahh, so se spremenile barve. Torej, kakršna koli je bila vrednost, ko se spremenimo - če spremenim to celico, se spremenijo vse te celice. Če spremenim to celico, se spremenijo vse te celice. Če spremenim to celico, se spremenijo vse te celice.vse te celice se spremenijo.vse te celice se spremenijo.

V redu, zdaj je to novo normalno. Zdaj želim spet slediti. Torej se ponastavim na trenutne vrednosti in potem, če to spremenim na 3, se ta prodaja spremeni. O, mimogrede, te celice nazaj in ti drugi listi so se prav tako spremenili kot odgovor na to. Ali želite slediti spremembam v Excelu, kakršne obstajajo? Ja, res šepa. Ne kaže vam stvari, ki so se spremenile, in pokazati delovni zvezek je grozno, grozno. Toda s tem preprostim, preprostim majhnim makrom deluje.

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, rad bi se vam zahvalil, da ste se ustavili. Se vidimo naslednjič za še eno oddajo iz.

Prenesite datoteko

Prenesite vzorčno datoteko tukaj: Podcast2059.xlsm