Pratite promjene u Excel ćelijama formule. Možete li pokazati koje su se stavke upravo promijenile kao rezultat promjene određenih ulaznih ćelija?
Gledaj video
- Praćenje promjena u Excelu pomalo je bizarno.
- Cilj je pratiti što se mijenja u ćelijama formule u Excelu.
- Spremi kao za spremanje radne knjige kao XLSM.
- Promijenite sigurnost makronaredbi.
- Snimite makronaredbu da biste shvatili kôd za postavljanje uvjetnog oblikovanja za brojeve koji nisu jednaki 2.
- Odaberite oblikovanje koje želite.
- Snimite još jednu makronaredbu da biste naučili kako ukloniti CF s radnog lista.
- U makronaredbu dodajte petlju za svaki radni list.
- Dodajte izraz IF da biste spriječili njegovo pokretanje u naslovu.
- Dodajte petlju za provjeru svake ćelije formule.
- Dodajte uvjetno oblikovanje da biste vidjeli pokreće li se vrijednost ćelije u trenutku.
- Vratite se u Excel.
- Dodajte oblik. Dodijelite makronaredbu obliku.
- Kliknite Oblik za pokretanje makronaredbe.
- Bonus savjet: Povlačenjem VBA modula u novu radnu knjigu.
Prijepis videozapisa
Naučite Excel iz Podcasta, Epizoda 2059: Promjene praćenja programa Excel (u rezultatima Formule)
Hej, dobrodošao natrag u prijenos uživo, ja sam Bill Jelen. Današnje pitanje poslano iz Montreala o promjenama staze. Promjene na stazi, u redu. Dakle, evo što imamo. Imamo 4 ulazne stanice i cijelu gomilu ćelija Formule koje se oslanjaju na te ulazne stanice. A ako bih uključio, vratit ću se na karticu Pregled, uključiti Istaknuti promjene, Pratiti promjene tijekom uređivanja, u redu kliknuti U redu. I upozorili su me da moraju spasiti radnu knjigu i da se makronaredbe ne mogu koristiti u zajedničkim radnim knjigama. Ti to znaš? To je problem kad pratite promjene, one dijele radnu knjigu i postoji čitava hrpa stvari koje se ne mogu dogoditi u zajedničkim radnim knjigama, kao što su makronaredbe i čitava hrpa drugih stvari. No, pogledajmo samo kako promjene zapisa danas djeluju u programu Excel.
Uzmimo ovo 2 i promijenimo s 2 na 22, a uzmemo ovo 4 i promijenimo ga sa 4 na 44. U redu, i vidite, ono što su primijetili u promjenama staza je da su se ove dvije stanice promijenile, u redu, ti ljubičasti trokuti su stvarne staze se mijenjaju. Sve se ove crvene stvari ne događaju, ali upravo sam ilustrirao da se sve te crvene stanice mijenjaju i da promjene ne govore ništa o tim promjenama, u redu? Dakle, samo kaže, ove dvije stanice su promijenjene, ali su promijenjene i sve ove druge stanice. I onda je pitanje iz Montreala, postoji li način da nam promjene traga zapravo pokažu sve što se mijenja, a ne samo da su se promijenile ove ulazne stanice?
U redu, dakle, prvo što moramo učiniti je isključiti ugrađene promjene zapisa u programu Excel. I onda, možemo li doći do načina - možemo izgraditi vlastiti sustav promjena staza koji će nam omogućiti da vidimo sve stanice formule koje su se promijenile? U redu, dakle, korak 1 i ovaj korak je najvažniji korak, nemojte ovo preskočiti. Pogledajte datoteku, datoteka se zove nešto XLSX, morate spremiti ovo: Datoteka, Spremi kao, Kao radna knjiga s omogućenom makronaredbom, ili ništa od toga neće raditi. Morate kliknuti desnom tipkom miša, prilagoditi vrpcu, uključiti razvojnog programera, nakon što dođete do razvojnog programera, idite na Macro Security, promijenite postavku - onu koja kaže da makronaredbe nećemo pustiti da se pokreću ili čak nećemo reći ti da su oni tu u ovoj postavci. Morate napraviti ta dva koraka. Već sam učinio ta dva koraka. Živim svaki dan s ta dva koraka.Već je popravljeno, ali ako ste novi u makronaredbama, ovo je novo za vas. A onda, moramo shvatiti kakvo oblikovanje želite. U redu, pa ću ovdje odabrati samo neke ćelije, snimit ću makronaredbu koja se zove HowToCFRed, neću dodijeliti u prečac, jer se ovo više nikada neće pokrenuti. Snimam samo kod da bih otkrio kako funkcionira uvjetno formatiranje. I ući ćemo u Početna, Uvjetno oblikovanje, Označi stanice koje nisu jednake - Dakle, Više pravila, Oblikuj stanice koje nisu jednake - Vidiš li to? Nije u originalnom padajućem izborniku, ali ako uđete ovdje, nije jednako 2, a zatim odaberite format. Ovo je važan dio. Pa ću odabrati crvenu pozadinu. Ovdje odabireš koju god boju želiš, u redu? Čak idite na Više boja, odaberite neku drugu crvenu,ući u Custom, odabrati neku drugu crvenu, u redu? U tome je ljepota Macro Recorder-a, donijet će nam savršenu crvenu za vas ili plavu ili što god želite. U redu, kliknite U redu. A onda ćemo zaustaviti snimanje, u redu. Opet, cijela poanta ovoga je samo vidjeti kakav je kod za uvjetne formate.
Idem na Makronaredbe, Kako uvjetno oblikovati crveno i urediti. U redu, ovdje su važni dijelovi ovog koda. Vidim da dodaju uvjetni format pomoću xlNotEqual i teško ga citiramo da ne bude jednak 2. A onda mijenjamo unutrašnjost ćelije u tu boju.
U redu, također moram shvatiti kako izbrisati sve uvjetno oblikovanje na listu. Dakle, natrag u Excel, Snimite još jedan makronaredba, Kako izbrisati sve uvjetne, u redu. Dođite ovdje na karticu Početna, idite na Uvjetno oblikovanje, Obriši pravilo s cijelog lista, Zaustavi snimanje i pogledat ćemo taj kod. Super, to je makronaredba u jednom retku. A ovdje mi se čak sviđa što se to čini za cijeli list tako što se odnosi samo na stanice. Drugim riječima, sve stanice na aktivnom listu.
Sada moram ovu makronaredbu, snimljenu makronaredbu, učiniti malo generičkijom. Napisao sam puno knjiga o tome kako raditi VBA u Excelu, a radio sam i video zapise o tome kako raditi VBA u Excelu, i evo jednostavne stvari: morate biti u mogućnosti snimiti ovakvu makronaredbu, ali zatim dodajte otprilike pet ili šest redaka kako bi se makronaredba mogla učiniti dovoljno generičkom.
I razgovarat ću o tim redovima, u redu. Dakle, prva stvar koju želim učiniti je da želim reći, želim proći kroz aktivnu radnu knjigu, proći kroz sve radne listove. Dakle, za svaki radni list WS je objektna varijabla, proći ću kroz sve radne listove. A osoba iz Montreala je rekla, "Hej, postoji jedan list na kojem ne želim da se ovo dogodi." Dakle, ako naziv WS.Name s nazivom točke radnog lista nije jednak naslovu, tada ćemo napraviti kod u makronaredbi. Evo naziva lista: .Cells.FormatConditions.Delete. Dakle, proći ćemo kroz svaki pojedinac lista, osim naslova i izbrisati sve uvjete formatiranja, a zatim ćemo proći kroz svaku ćeliju lista, ali ne sve stanice, već samo stanice koje imaju formule . Ako nema formulu, onda nemam 'ne treba ga formatirati jer se to neće promijeniti. Cell.FormatConditions.Add, ovo je izravno iz makronaredbe, iako je snimljena makronaredba rekla Selection - ne želim je morati odabrati, pa ću samo reći Cell, to je svaka pojedinačna stanica. Upotrijebit ćemo xlNotEqual i umjesto Formule: = ”=” 2, što je snimljeni kod upravo učinio, spojio sam sve što se nalazi u toj ćeliji. Stoga provjeravamo nije li jednak trenutnoj vrijednosti. Dakle, ako stanica trenutno ima 2, kažemo da nije jednako 2. Ako stanica trenutno ima 16,5, kažemo da nije jednako 16,5. A onda je ostatak samo ravno snimljeni makronaredba, snimljena makronaredba, snimljena makronaredba, snimljena makronaredba. Sve je to iz snimljene makronaredbe. Završite ovo Ako s krajem Ako. Završite ovo za sljedeći WS. s se neće promijeniti. Cell.FormatConditions.Add, ovo je izravno iz makronaredbe, iako je snimljena makronaredba rekla Selection - ne želim je morati odabrati, pa ću samo reći Cell, to je svaka pojedinačna stanica. Upotrijebit ćemo xlNotEqual i umjesto Formule: = ”=” 2, što je snimljeni kod upravo učinio, spojio sam sve što se nalazi u toj ćeliji. Stoga provjeravamo nije li jednak trenutnoj vrijednosti. Dakle, ako stanica trenutno ima 2, kažemo da nije jednako 2. Ako stanica trenutno ima 16,5, kažemo da nije jednako 16,5. A onda je ostatak samo ravno snimljeni makronaredba, snimljena makronaredba, snimljena makronaredba, snimljena makronaredba. Sve je to iz snimljene makronaredbe. Završite ovo Ako s krajem Ako. Završite ovo za sljedeći WS.s se neće promijeniti. Cell.FormatConditions.Add, ovo je izravno iz makronaredbe, iako je snimljena makronaredba rekla Selection - ne želim je morati odabrati, pa ću samo reći Cell, to je svaka pojedinačna stanica. Upotrijebit ćemo xlNotEqual i umjesto Formule: = ”=” 2, što je snimljeni kod upravo učinio, spojio sam sve što se nalazi u toj ćeliji. Stoga provjeravamo nije li jednak trenutnoj vrijednosti. Dakle, ako stanica trenutno ima 2, kažemo da nije jednako 2. Ako stanica trenutno ima 16,5, kažemo da nije jednako 16,5. A onda je ostatak samo ravno snimljeni makronaredba, snimljena makronaredba, snimljena makronaredba, snimljena makronaredba. Sve je to iz snimljene makronaredbe. Završite ovo Ako s krajem Ako. Završite ovo za sljedeći WS.ovo je izravno iz makronaredbe, iako je snimljena makronaredba rekla Selection - ne želim je odabrati, pa ću samo reći Cell, to je svaka pojedinačna stanica. Upotrijebit ćemo xlNotEqual i umjesto Formule: = ”=” 2, što je snimljeni kod upravo učinio, spojio sam sve što se nalazi u toj ćeliji. Stoga provjeravamo nije li jednak trenutnoj vrijednosti. Dakle, ako stanica trenutno ima 2, kažemo da nije jednako 2. Ako stanica trenutno ima 16,5, kažemo da nije jednako 16,5. A onda je ostatak samo ravno snimljeni makronaredba, snimljena makronaredba, snimljena makronaredba, snimljena makronaredba. Sve je to iz snimljene makronaredbe. Završite ovo Ako s krajem Ako. Završite ovo za sljedeći WS.ovo je izravno iz makronaredbe, iako je snimljena makronaredba rekla Selection - ne želim je odabrati, pa ću samo reći Cell, to je svaka pojedinačna stanica. Upotrijebit ćemo xlNotEqual i umjesto Formule: = ”=” 2, što je snimljeni kod upravo učinio, spojio sam sve što se nalazi u toj ćeliji. Stoga provjeravamo nije li jednak trenutnoj vrijednosti. Dakle, ako stanica trenutno ima 2, kažemo da nije jednako 2. Ako stanica trenutno ima 16,5, kažemo da nije jednako 16,5. A onda je ostatak samo ravno snimljeni makronaredba, snimljena makronaredba, snimljena makronaredba, snimljena makronaredba. Sve je to iz snimljene makronaredbe. Završite ovo Ako s krajem Ako. Završite ovo za sljedeći WS.ne želim je odabrati, pa ću samo reći Cell, to je svaka pojedinačna stanica. Upotrijebit ćemo xlNotEqual i umjesto Formule: = ”=” 2, što je snimljeni kod upravo učinio, spojio sam sve što se nalazi u toj ćeliji. Stoga provjeravamo nije li jednak trenutnoj vrijednosti. Dakle, ako stanica trenutno ima 2, kažemo da nije jednako 2. Ako stanica trenutno ima 16,5, kažemo da nije jednako 16,5. A onda je ostatak samo ravno snimljeni makronaredba, snimljena makronaredba, snimljena makronaredba, snimljena makronaredba. Sve je to iz snimljene makronaredbe. Završite ovo Ako s krajem Ako. Završite ovo za sljedeći WS.ne želim je odabrati, pa ću samo reći Cell, to je svaka pojedinačna stanica. Upotrijebit ćemo xlNotEqual i umjesto Formule: = ”=” 2, što je snimljeni kod upravo učinio, spojio sam sve što se nalazi u toj ćeliji. Stoga provjeravamo nije li jednak trenutnoj vrijednosti. Dakle, ako stanica trenutno ima 2, kažemo da nije jednako 2. Ako stanica trenutno ima 16,5, kažemo da nije jednako 16,5. A onda je ostatak samo ravno snimljeni makronaredba, snimljena makronaredba, snimljena makronaredba, snimljena makronaredba. Sve je to iz snimljene makronaredbe. Završite ovo Ako s krajem Ako. Završite ovo za sljedeći WS.= ”=” 2, što je snimljeni kod upravo učinio, spojio sam sve što se nalazi u toj ćeliji. Stoga provjeravamo nije li jednak trenutnoj vrijednosti. Dakle, ako stanica trenutno ima 2, kažemo da nije jednako 2. Ako stanica trenutno ima 16,5, kažemo da nije jednako 16,5. A onda je ostatak samo ravno snimljeni makronaredba, snimljena makronaredba, snimljena makronaredba, snimljena makronaredba. Sve je to iz snimljene makronaredbe. Završite ovo Ako s krajem Ako. Završite ovo za sljedeći WS.= ”=” 2, što je upravo tamo napravio snimljeni kod, spojio sam sve što se nalazi u toj ćeliji. Stoga provjeravamo nije li jednak trenutnoj vrijednosti. Dakle, ako stanica trenutno ima 2, kažemo da nije jednako 2. Ako stanica trenutno ima 16,5, kažemo da nije jednako 16,5. A onda je ostatak samo ravno snimljeni makronaredba, snimljena makronaredba, snimljena makronaredba, snimljena makronaredba. Sve je to iz snimljene makronaredbe. Završite ovo Ako s krajem Ako. Završite ovo za sljedeći WS.snimljeni makro, snimljeni makro. Sve je to iz snimljene makronaredbe. Završite ovo Ako s krajem Ako. Završite ovo za sljedeći WS.snimljeni makro, snimljeni makro. Sve je to iz snimljene makronaredbe. Završite ovo Ako s krajem Ako. Završite ovo za sljedeći WS.
U redu, tako da imam makronaredbu koja se zove ApplyCF. Vratite se u Excel, dodajte oblik. Ovdje je lako imati oblik: Umetni, uvijek biram zaobljeni pravokutnik, tip Reset To Current Values. Primijenit ćemo Home, centar i centar da ga malo povećaju. Volim sjaj. Pretpostavljam da mislite da je glupo kad nema toga, sjaj, postavka koja mi se sviđa nisu tu, pa uvijek idem na Izgled stranice i efekti i odaberem onu drugu. A onda, kad se vratim na format, mogu odabrati onaj koji zapravo ima malo sjaja. Meni se čini da izgleda cool, mislim da vrijedi. Desnom tipkom miša kliknite Dodijeli makronaredbu i izgovorite ApplyCF, kliknite U redu. U redu, a što će ovo učiniti, kad je kliknem, proći će kroz sve ove listove, pronaći sve ćelije formule i postaviti uvjetno oblikovanje koje kaže: Ako ove stanice nisu jednake 7,promijeniti boju, u redu? To je to. To je tako brzo, dogodilo se tako brzo. BAM! Gotovo je. A sada, pazite ako ovu promijenim na 11, sve su se te stanice upravo promijenile. Ako se vrati na 1, ahh, promijenile su se boje. Dakle, kakva god bila vrijednost, kad se promijenimo - ako promijenim ovu ćeliju, promijene se sve te stanice. Ako promijenim ovu ćeliju, promijene se sve te stanice. Ako promijenim ovu ćeliju, promijene se sve te stanice.sve se te stanice mijenjaju.sve se te stanice mijenjaju.
U redu, sad je ovo novo normalno. Sada odavde, želim ponovno pratiti. Stoga se vraćam na trenutne vrijednosti, a ako promijenim ovu na 3, ta se prodaja promijeni. Usput, ove stanice ovdje i ovi drugi listovi također su se promijenili kao odgovor na to. Pratiti promjene u programu Excel onako kako postoje? Da, stvarno je jadno. Ne pokazuje vam stvari koje su se promijenile, a to što morate pokazati radnu knjižicu je užasna, užasna stvar. Ali s ovim jednostavnim, jednostavnim malim makronaredbom to djeluje.
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.
Pa, hej, želim ti zahvaliti što si svratio. Vidimo se sljedeći put za još jedan prijenos od.
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Podcast2059.xlsm
