Stebėkite „Excel“ formulės langelių pokyčius. Ar galite parodyti, kurie elementai ką tik pasikeitė keičiant tam tikras įvesties langelius?
Žiūrėti video
- „Excel“ pokyčių stebėjimas yra šiek tiek keistas.
- Tikslas yra stebėti, kokias formulės langelius keičia „Excel“.
- Išsaugoti kaip, jei norite išsaugoti darbaknygę kaip XLSM.
- Pakeiskite „Macro Security“.
- Įrašykite makrokomandą, kad išsiaiškintumėte kodą, kad nustatytumėte sąlyginį skaičių, nevienodo 2 formatavimą.
- Pasirinkite norimą formatą.
- Įrašykite kitą makrokomandą, kad sužinotumėte, kaip pašalinti CF iš darbalapio.
- Makrokomandoje pridėkite kiekvieno darbalapio kilpą.
- Pridėkite IF sakinį, kad jis nebūtų rodomas pavadinime.
- Pridėkite kilpą, kad patikrintumėte kiekvieną formulės langelį.
- Pridėkite sąlyginį formatavimą, jei norite sužinoti, ar veikia makrokomandos langelio vertė.
- Grįžkite į „Excel“.
- Pridėkite figūrą. Priskirkite makrokomandą formai.
- Spustelėkite formą, kad paleistumėte makrokomandą.
- Premijos patarimas: vilkite VBA modulį į naują darbaknygę.
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš „Podcast“, 2059 serija: „Excel“ stebėkite pakeitimus („Formulės“ rezultatuose)
Ei, sveiki sugrįžę į internetinę transliaciją, aš Billas Jelenas. Šiandienos klausimas išsiųstas iš Monrealio apie bėgių kelio pakeitimus. Stebėkite pakeitimus, gerai. Taigi štai ką turime. Mes turime 4 įvesties langelius ir visą „Formula“ ląstelių, kurios remiasi šiomis įvesties ląstelėmis, krūvą. Ir jei įsijungčiau, grįšiu į skirtuką „Peržiūra“, įjungsiu „Paryškinti pakeitimus“, stebėkite pakeitimus redaguodami, spustelėkite Gerai, gerai. Jie mane perspėjo, kad jie turi išsaugoti darbaknygę ir kad makrokomandų negalima naudoti bendrose darbaknygėse. Tu žinai tai? Tai yra problema, kai stebite pakeitimus, jie dalijasi darbaknygė ir yra daugybė dalykų, kurių negali atsitikti bendrose darbaknygėse, pvz., Makrokomandas ir daugybę kitų dalykų. Pažvelkime tik į tai, kaip „Excel“ pakeitimai veikia šiandien.
Paimkime šį 2 ir pakeiskime iš 2 į 22, paimkime šį 4 ir pakeiskime jį iš 4 į 44. Gerai, ir matai, ką jie pastebėjo bėgių kelio pokyčiuose, tai, kad šios dvi ląstelės pasikeitė, gerai, tie violetiniai trikampiai yra faktiniai bėgių kelio pokyčiai. Visi šie raudoni dalykai to neįvyks, bet aš tiesiog iliustravau, kad visos šios raudonosios ląstelės keičiasi ir sekimo pokyčiai nieko apie šiuos pokyčius nesako, gerai? Taigi, tiesiog sakoma, šios dvi ląstelės buvo pakeistos, tačiau visos šios kitos ląstelės taip pat buvo pakeistos. Taigi klausimas iš Monrealio yra: ar yra būdas, kad kelio pakeitimai iš tikrųjų parodytų mums viską, kas keičiasi, pasikeitė ne tik šios įvesties ląstelės?
Gerai, todėl pirmiausia turime išjungti „Excel“ integruotą „Track Changes“. Ir ar yra būdas, kurį galime gauti - mes galime sukurti savo takelių keitimo sistemą, kuri leis mums pamatyti visas pasikeitusias formulės langelius? Gerai, todėl 1 žingsnis ir šis žingsnis yra svarbiausias žingsnis, to nepraleiskite. Pažvelkite į savo failą, jūsų failas vadinamas kažkuo XLSX. Turite tai išsaugoti: Failas, Išsaugoti kaip, Kaip makrokomandą įgalinanti darbaknygė arba niekas iš jų neveiks. Turite spustelėti dešiniuoju pelės mygtuku, Tinkinti juostelę, įjungti „Developer“, kai pateksite į „Developer“, eikite į „Macro Security“, pakeiskite šį nustatymą - tą, kuris sako, kad mes neleisime makrokomandoms paleisti ar net nesakysime jums, kad jie laikosi šio nustatymo. Turite atlikti tuos du veiksmus. Aš jau padariau tuos du žingsnius. Aš kasdien gyvenu tais dviem žingsniais.Jau ištaisyta, bet jei makrokomandas naudojate dar ne pirmą kartą, tai jums nauja. Tada turime išsiaiškinti, kokio formatavimo norite. Gerai, todėl čia aš tiesiog pasirinksiu keletą langelių, aš įrašysiu makrokomandą, vadinamą „HowToCFRed“, nesiruošiu priskirti sparčiojo klavišo klavišui, nes tai niekada nebebus paleista. Aš tiesiog įrašau kodą, kad suprasčiau, kaip veikia sąlyginis formatavimas. Mes pateksime į Namai, Sąlyginis formatavimas, Paryškinti langelius, kurie nėra lygūs - Taigi, Daugiau taisyklių, Formatuoti langelius, kurie nėra lygūs - Matote tai? Tai nėra pradiniame išskleidžiamajame meniu, bet jei jūs čia atėjote, tai nėra lygu 2, o tada pasirinkite formatą. Tai yra svarbi dalis. Taigi aš pasirinksiu raudoną foną. Čia pasirenkate kokią spalvą norite, gerai? Net eikite į „Daugiau spalvų“, pasirinkite kitą raudoną,eikite į „Custom“, pasirinkite kitą raudoną, gerai? Tai yra „Macro Recorder“ grožis: jie gaus mums tobulą raudoną spalvą arba mėlyną spalvą ar ką tik norite. Gerai, spustelėkite Gerai. Ir tada mes sustabdysime įrašymą, gerai. Vėlgi, visa tai yra tik norint sužinoti, koks yra sąlyginių formatų kodas.
Einu į makrokomandas „Kaip sąlyginai formatuoti raudonai“ ir redaguoti. Gerai, todėl pateikiame svarbias šio kodo dalis. Matau, kad jie prideda sąlyginį formatą naudodami „xlNotEqual“, ir mes sunkiai cituojame, kad jis nebūtų lygus 2. Tada pakeičiame langelio vidų į šią spalvą.
Gerai, aš taip pat turiu sugalvoti, kaip ištrinti visą sąlyginį formatavimą lape. Taigi, grįžkite į „Excel“, įrašykite kitą makrokomandą, kaip ištrinti visus sąlyginius, gerai. Eikite čia į skirtuką Pagrindinis, eikite į Sąlyginis formatavimas, Išvalyti taisyklę iš viso lapo, Sustabdyti įrašymą ir mes pažvelgsime į tą kodą. Puiku, tai vienos eilutės makrokomanda. Man čia net patinka, kad tai, kaip jie daro visą lapą, reiškia tik ląsteles. Kitaip tariant, visos aktyvaus lapo ląstelės.
Dabar turiu padaryti šią makrokomandą, įrašytą makrokomandą, šiek tiek bendresnę. Aš parašiau daug knygų apie tai, kaip atlikti „VBA“ programoje „Excel“, ir sukūriau vaizdo įrašų apie tai, kaip padaryti „VBA“ sistemoje „Excel“, ir čia yra paprastas dalykas: turite mokėti įrašyti tokią makrokomandą, bet tada pridėkite apie penkias ar šešias eilutes, kad makrokomanda būtų pakankamai bendrinė.
Aš kalbėsiu apie tas eilutes, gerai. Taigi pirmas dalykas, kurį noriu padaryti, yra tai, kad noriu pasakyti: noriu peržiūrėti aktyvią darbaknygę, pereiti visus darbalapius. Taigi kiekviename darbalapyje WS yra objekto kintamasis, aš pereisiu visus darbalapius. Asmuo iš Monrealio pasakė: „Ei, yra vienas lapas, kurio nenoriu, kad tai atsitiktų“. Taigi, jei WS.Name su darbalapio taško pavadinimu nėra lygus pavadinimui, kodą atliksime makrokomandoje. Štai lapo pavadinimas: .Cells.FormatConditions.Delete. Taigi, mes eisime per kiekvieną lapo asmenį, išskyrus pavadinimą, ir ištrinsime visas formatavimo sąlygas, tada eisime per kiekvieną lapo langelį, bet ne visas langelius, tik langelius, kuriuose yra formulės . Jei jis neturi formulės, aš to nedarauNereikia jo formatuoti, nes tai nesikeis. „Cell.FormatConditions.Add“, tai yra tiesiogiai iš makrokomandos, nors įrašytoje makrokomandoje buvo „Selection“ - aš nenoriu, kad turėčiau ją pasirinkti, todėl aš tiesiog pasakysiu „Cell“, tai yra kiekviena ląstelė. Mes naudosime xlNotEqual ir vietoj Formulės: = ”=” 2, kurią įrašytas kodas padarė būtent ten, aš sujungiau viską, kas yra toje ląstelėje. Taigi patikrinkite, ar ji nėra lygi dabartinei vertei. Taigi, jei langelyje šiuo metu yra 2, sakome, kad jis nėra lygus 2. Jei langelyje šiuo metu yra 16,5, sakome, kad jis nėra lygus 16,5. Tada visa kita yra tiesiog tiesiai įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda. Visa tai yra iš įrašytos makrokomandos. Užbaikite tai, jei baigsite If. Užbaikite tai kitam WS. nesikeis. „Cell.FormatConditions.Add“, tai yra tiesiogiai iš makrokomandos, nors įrašytoje makrokomandoje buvo „Selection“ - aš nenoriu, kad turėčiau ją pasirinkti, todėl aš tiesiog pasakysiu „Cell“, tai yra kiekviena ląstelė. Mes naudosime xlNotEqual ir vietoj Formulės: = ”=” 2, kurią įrašytas kodas padarė būtent ten, aš sujungiau viską, kas yra toje ląstelėje. Taigi patikrinkite, ar ji nėra lygi dabartinei vertei. Taigi, jei langelyje šiuo metu yra 2, sakome, kad jis nėra lygus 2. Jei langelyje šiuo metu yra 16,5, sakome, kad jis nėra lygus 16,5. Tada visa kita yra tiesiog tiesiai įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda. Visa tai yra iš įrašytos makrokomandos. Užbaikite tai, jei baigsite If. Užbaikite tai kitam WS.nesikeis. „Cell.FormatConditions.Add“, tai tiesiogiai iš makrokomandos, nors įrašytoje makrokomandoje buvo „Selection“ - aš nenoriu, kad turėčiau ją pasirinkti, todėl aš tiesiog pasakysiu „Cell“, tai yra kiekviena ląstelė. Mes naudosime xlNotEqual ir vietoj Formulės: = ”=” 2, kurią įrašytas kodas padarė būtent ten, aš sujungiau viską, kas yra toje ląstelėje. Taigi patikrinkite, ar ji nėra lygi dabartinei vertei. Taigi, jei langelyje šiuo metu yra 2, sakome, kad jis nėra lygus 2. Jei langelyje šiuo metu yra 16,5, sakome, kad jis nėra lygus 16,5. Tada visa kita yra tiesiog tiesiai įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda. Visa tai yra iš įrašytos makrokomandos. Užbaikite tai, jei baigsite If. Užbaikite tai už kitą WS.tai tiesiogiai iš makrokomandos, nors įrašytoje makrokomandoje buvo sakoma „Pasirinkimas“ - aš nenoriu, kad jos turėčiau pasirinkti, todėl aš tiesiog pasakysiu „Ląstelė“, tai yra kiekviena ląstelė. Mes naudosime xlNotEqual ir vietoj Formulės: = ”=” 2, kurią įrašytas kodas padarė būtent ten, aš sujungiau viską, kas yra toje ląstelėje. Taigi patikrinkite, ar ji nėra lygi dabartinei vertei. Taigi, jei langelyje šiuo metu yra 2, sakome, kad jis nėra lygus 2. Jei langelyje šiuo metu yra 16,5, sakome, kad jis nėra lygus 16,5. Tada visa kita yra tiesiog tiesiai įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda. Visa tai yra iš įrašytos makrokomandos. Užbaikite tai, jei baigsite If. Užbaikite tai už kitą WS.tai tiesiogiai iš makrokomandos, nors įrašytoje makrokomandoje buvo sakoma „Pasirinkimas“ - aš nenoriu, kad jos turėčiau pasirinkti, todėl tiesiog pasakysiu „Ląstelė“, tai yra kiekviena ląstelė. Mes naudosime xlNotEqual ir vietoj Formulės: = ”=” 2, kurią įrašytas kodas padarė būtent ten, aš sujungiau viską, kas yra toje ląstelėje. Taigi patikrinkite, ar ji nėra lygi dabartinei vertei. Taigi, jei langelyje šiuo metu yra 2, sakome, kad jis nėra lygus 2. Jei langelyje šiuo metu yra 16,5, sakome, kad jis nėra lygus 16,5. Tada visa kita yra tiesiog tiesiai įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda. Visa tai yra iš įrašytos makrokomandos. Užbaikite tai, jei baigsite If. Užbaikite tai už kitą WS.Nenoriu, kad jį pasirinktumėte, todėl aš tik pasakysiu „Ląstelė“, tai yra kiekviena ląstelė. Mes naudosime xlNotEqual ir vietoj Formulės: = ”=” 2, kurią įrašytas kodas padarė būtent ten, aš sujungiau viską, kas yra toje ląstelėje. Taigi patikrinkite, ar ji nėra lygi dabartinei vertei. Taigi, jei langelyje šiuo metu yra 2, sakome, kad jis nėra lygus 2. Jei langelyje šiuo metu yra 16,5, sakome, kad jis nėra lygus 16,5. Tada visa kita yra tiesiog tiesiai įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda. Visa tai yra iš įrašytos makrokomandos. Užbaikite tai, jei baigsite If. Užbaikite tai už kitą WS.Nenoriu, kad jį pasirinktumėte, todėl aš tik pasakysiu „Ląstelė“, tai yra kiekviena ląstelė. Mes naudosime xlNotEqual ir vietoj Formulės: = ”=” 2, kurią įrašytas kodas padarė būtent ten, aš sujungiau viską, kas yra toje ląstelėje. Taigi patikrinkite, ar ji nėra lygi dabartinei vertei. Taigi, jei langelyje šiuo metu yra 2, sakome, kad jis nėra lygus 2. Jei langelyje šiuo metu yra 16,5, sakome, kad jis nėra lygus 16,5. Tada visa kita yra tiesiog tiesiai įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda. Visa tai yra iš įrašytos makrokomandos. Užbaikite tai, jei baigsite If. Užbaikite tai už kitą WS.= ”=” 2 tai, ką įrašytas kodas padarė ten, aš sujungiau viską, kas yra toje ląstelėje. Taigi patikrinkite, ar ji nėra lygi dabartinei vertei. Taigi, jei langelyje šiuo metu yra 2, sakome, kad jis nėra lygus 2. Jei langelyje šiuo metu yra 16,5, sakome, kad jis nėra lygus 16,5. Tada visa kita yra tiesiog tiesiai įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda. Visa tai yra iš įrašytos makrokomandos. Užbaikite tai, jei baigsite If. Užbaikite tai kitam WS.= ”=” 2 tai, ką įrašytas kodas padarė ten, aš sujungiau viską, kas yra toje ląstelėje. Taigi patikrinkite, ar ji nėra lygi dabartinei vertei. Taigi, jei langelyje šiuo metu yra 2, sakome, kad jis nėra lygus 2. Jei langelyje šiuo metu yra 16,5, sakome, kad jis nėra lygus 16,5. Tada visa kita yra tiesiog tiesiai įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda, įrašyta makrokomanda. Visa tai yra iš įrašytos makrokomandos. Užbaikite tai, jei baigsite If. Užbaikite tai už kitą WS.įrašytas makrokomandas, įrašytas makrokomandas. Visa tai yra iš įrašytos makrokomandos. Užbaikite tai, jei baigsite If. Užbaikite tai už kitą WS.įrašytas makrokomandas, įrašytas makrokomandas. Visa tai yra iš įrašytos makrokomandos. Užbaikite tai, jei baigsite If. Užbaikite tai už kitą WS.
Gerai, todėl turiu makrokomandą, vadinamą „ApplyCF“. Grįžkite į „Excel“, pridėkite figūrą. Čia lengva turėti formą: įterpti, aš visada pasirenku suapvalintą stačiakampį, įveskite Reset to Current Values. Mes pritaikysime namus, centrą ir centrą, kad jis būtų šiek tiek didesnis. Aš myliu švytėjimą. Manau, kad manote, kad kvaila matyti, jog jo nėra, švytėjimo, man patinkančio nustatymo nėra, todėl visada einu į Puslapio išdėstymą ir efektus ir pasirenku tą antrą. Ir tada, kai grįšiu į formatą, galiu pasirinkti tokį, kuris iš tikrųjų turi šiek tiek švytėjimo. Manau, kad tai atrodo šaunu, manau, kad verta. Dešiniuoju pelės mygtuku spustelėkite, priskirkite makrokomandą ir pasakykite ApplyCF, spustelėkite Gerai. Gerai, tada ką tai padarys, kai spustelėsiu, jis pereis visus šiuos lapus, suras visas formulės langelius ir nustatys sąlyginį formatavimą, kuriame sakoma: Jei šios ląstelės nėra lygios 7,pakeisti spalvą, gerai? Viskas. Tai taip greitai, taip greitai atsitiko. BAM! Padaryta. Dabar stebėkite, ar aš pakeisiu šį į 11, visi tie langeliai tiesiog pasikeitė. Dabar, jei grįžtama prie 1, aaa, spalvos pasikeitė. Taigi, kad ir kokia būtų vertė, kai mes keičiamės - jei pakeisiu šią langelį, visos tos ląstelės pasikeis. Jei pakeisiu šią langelį, visos tos ląstelės pasikeis. Jei pakeisiu šią langelį, visos tos ląstelės pasikeis.visos tos ląstelės keičiasi.visos tos ląstelės keičiasi.
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.
Na, aš noriu padėkoti, kad užsukote. Pamatysime kitą kartą kitai internetinei transliacijai iš.
Atsisiųsti failą
Atsisiųskite failo pavyzdį čia: Podcast2059.xlsm
