Jälgige Exceli valemirakkude muudatusi. Kas saate näidata, millised üksused on just teatud sisendrakkude muutmise tagajärjel muutunud?
Vaata videot
- Exceli muudatuste jälgimine on veidi veider.
- Eesmärk on jälgida, milliseid valemirakke Excelis muudetakse.
- Töövihiku salvestamiseks XLSM-na salvestage Salvesta nimega.
- Muutke makroturvet.
- Salvestage makro, et selgitada välja kood, et seadistada tingimusvorming numbritele, mis ei ole võrdsed 2-ga.
- Valige soovitud vormindamine.
- Salvestage teine makro, et teada saada, kuidas CF töölehelt eemaldada.
- Lisage makrosse iga töölehe jaoks silmus.
- Lisage IF-lause, et takistada selle pealkirja käivitamist.
- Iga valemiraku kontrollimiseks lisage silmus.
- Lisage tingimuslik vormindamine, et näha, kas lahtri väärtus makro töötab.
- Minge tagasi Exceli juurde.
- Lisage kuju. Määrake makro kujule.
- Makro käivitamiseks klõpsake nuppu Kuju.
- Boonusenõu: lohistage VBA moodul uude töövihikusse.
Video ärakiri
Exceli õppimine Podcastist, episood 2059: Exceli jälgimismuudatused (valemi tulemustes)
Tere, tere tulemast tagasi netisaate juurde, ma olen Bill Jelen. Montrealist saadeti tänane küsimus raja muutuste kohta. Jälgige muudatusi, olgu. Nii et siin on see, mis meil on. Meil on 4 sisendrakku ja terve hulk valemirakke, mis toetuvad nendele sisendrakkudele. Ja kui ma sisse lülituksin, lähen tagasi vahekaardile Ülevaade, lülitan sisse valiku Esiletõstetud muudatused, Jälgige muutmise ajal muudatusi, klõpsake nuppu OK, olgu. Ja nad hoiatasid mind, et nad peavad töövihiku salvestama ja makrosid ei saa jagatud töövihikutes kasutada. Sa tead seda? See on probleem, kui jälgite muudatusi, nad jagavad töövihikut ja jagatud töövihikutes ei saa juhtuda tervet hulka asju, nagu näiteks makrod ja terve hulk muid asju. Kuid vaatame lihtsalt, kuidas muudatuste jälgimine Excelis täna töötab.
Võtame selle 2 ja muudame 2-st 22-ni ja võtame selle 4 ja muudame selle 4-st 44. Hästi, ja näete, mida nad on raja muutustes märkinud, on see, et need kaks lahtrit muutusid, olgu, need lillad kolmnurgad on tegelik rööbastee muutus. Kõiki neid punaseid asju ei juhtu, aga ma lihtsalt illustreerisin, et kõik need punased rakud muutuvad ja muutuste jälgimine ei ütle nende muutuste kohta midagi, eks? Niisiis, lihtsalt öeldakse, neid kahte rakku muudeti, kuid muudeti ka kõiki neid teisi rakke. Ja siis on küsimus Montrealist, kas on võimalik, et raja muutused näitavad meile tegelikult kõike, mis muutub, mitte ainult need sisendrakud pole muutunud?
Hästi, nii et esimene asi, mida peame tegema, on Exceli sisseehitatud muudatuste jälgimine välja lülitada. Ja kas siis on olemas viis, kuidas saame - saame luua oma raja muutmise süsteemi, mis võimaldab meil näha kõiki muutunud valemirakke? Hästi, nii et esimene samm ja see samm on kõige olulisem samm, ärge jätke seda vahele. Vaadake oma faili, teie faili nimi on XLSX, peate selle salvestama: Fail, Salvesta nimega, Makrotoega töövihikuna või ükski neist ei toimi. Peate paremklõpsama, kohandama linti, lülitama arendaja sisse, kui olete arendaja juurde jõudnud, minge makroturbe juurde, muutke seda seadet - see, mis ütleb, et me ei lase makrodel töötada ega isegi ütle teile, et nad on selle seade juures. Peate tegema need kaks sammu. Olen need kaks sammu juba teinud. Elan iga päev nende kahe sammuga.See on juba fikseeritud, kuid kui olete makrode jaoks uus, on see teile uus. Ja siis peame välja mõtlema, millist vormingut soovite. Hästi, nii et ma lihtsalt valin siin mõned lahtrid, salvestan makro, mille nimi on HowToCFRed, ma ei määra otseteeklahvi, sest see ei hakka enam kunagi käima. Ma lihtsalt salvestan koodi, et aru saada, kuidas tingimuslik vormindamine töötab. Ja jõuame kodust, tingimuslikust vormindamisest, esiletõstetud lahtritest, mis pole võrdsed - nii, veel reegleid, lahtrite vormindamine pole võrdne - kas näete seda? See pole algses rippmenüüs, kuid kui tulete siia, siis pole võrdne 2-ga ja siis valige vorming. See on oluline osa. Nii et valin punase tausta. Valite siin mis värvi soovite, eks? Isegi minge jaotisse Rohkem värve, valige mõni muu punane,minna Customi, valida mõni muu punane, korras? See on makromagnetofoni ilu, nad saavad meile ideaalse punase või sinise või mis iganes soovite. Hästi, klõpsake nuppu OK. Ja siis lõpetame salvestamise, olgu. Jällegi on kogu selle mõte ainult selleks, et näha, mis on tingimuslike vormingute kood.
Ma lähen makrosse, kuidas tingimuslikult punaseks vormindada, ja redigeerin. Hästi, nii et siin on selle koodi olulised osad. Ma näen, et nad lisavad tingimusliku vormingu, kasutades xlNotEquali, ja me tsiteerime seda raskelt, et see ei oleks võrdne 2-ga. Ja siis muudame lahtri sisemuse sellele värvile.
Hästi, ma pean ka välja mõtlema, kuidas lehel kõik tingimuslikud vormingud kustutada. Niisiis, tagasi Exceli juurde, salvestage veel üks makro, kuidas kustutada kõik tingimuslikud, OK. Tule siia vahekaardile Avaleht, minge jaotisele Tingimuslik vormindamine, Tühjenda reegel kogu lehelt, Lõpeta salvestamine ja läheme seda koodi vaatama. Suurepärane, see on ühe rea makro. Ja mulle meeldib siin isegi see, et see, kuidas nad seda kogu lehe jaoks teevad, viitab lihtsalt lahtritele. Teisisõnu, kõik aktiivse lehe lahtrid.
Nüüd pean selle makro, salvestatud makro, tegema natuke üldisemaks. Ja ma olen kirjutanud palju raamatuid selle kohta, kuidas Excelis VBA-d teha, ja olen teinud videoid selle kohta, kuidas VBA-d Excelis teha, ja siin on lihtne: peate suutma sellist makrot salvestada, kuid seejärel lisage umbes viis või kuus rida, et makro oleks piisavalt üldine.
Ja ma räägin nendest joontest, olgu. Nii et esimese asjana tahan öelda seda, et tahan läbi vaadata aktiivse töövihiku, läbida kõik töölehed. Nii et iga töölehe jaoks on WS objektimuutuja, ma vaatan kõik töölehed läbi. Ja Montrealist pärit inimene ütles: "Hei, on üks leht, mis ei taha, et see juhtuks." Niisiis, kui WS.Name koos töölehe punktinimega ei ole võrdne pealkirjaga, siis teeme koodi makros. Siin on lehe nimi: .Cells.FormatConditions.Delete. Niisiis, läheme läbi iga lehe üksiku, välja arvatud pealkiri, ja kustutame kõik vormingutingimused. Seejärel läbime lehe kõik lahtrid, kuid mitte kõik lahtrid, vaid ainult valemitega lahtrid . Kui sellel pole valemit, siis ma ei tahaSeda ei pea vormindama, sest see ei muutu. Cell.FormatConditions.Add, see on otse makrost, kuigi salvestatud makro ütles Valik - ma ei taha, et peaksin seda valima, nii et ma lihtsalt ütlen Cell, see on iga üksik lahter. Kasutame xlNotEquali ja valemi: = ”=” 2 asemel, mida salvestatud kood seal tegi, olen liitnud kõik, mis selles lahtris on. Nii et kontrollige, kas see pole praeguse väärtusega võrdne. Nii et kui lahtril on praegu 2, siis ütleme, et see pole võrdne 2-ga. Kui lahtril on praegu 16,5, ütleme, et see pole võrdne 16,5-ga. Ja siis on ülejäänud osa lihtsalt sirge salvestatud makro, salvestatud makro, salvestatud makro, salvestatud makro. Kõik see pärineb salvestatud makrost. Lõpeta see, kui lõpuga If. Lõpeta see järgmise WS-iga. ei muutu. Cell.FormatConditions.Add, see on otse makrost, kuigi salvestatud makro ütles Valik - ma ei taha, et peaksin seda valima, nii et ma lihtsalt ütlen Cell, see on iga üksik lahter. Kasutame xlNotEquali ja valemi: = ”=” 2 asemel, mida salvestatud kood seal tegi, olen liitnud kõik, mis selles lahtris on. Nii et kontrollige, kas see pole praeguse väärtusega võrdne. Nii et kui lahtril on praegu 2, siis ütleme, et see pole võrdne 2-ga. Kui lahtril on praegu 16,5, ütleme, et see pole võrdne 16,5-ga. Ja siis on ülejäänud osa lihtsalt sirge salvestatud makro, salvestatud makro, salvestatud makro, salvestatud makro. Kõik see pärineb salvestatud makrost. Lõpeta see, kui on lõpp If. Lõpeta see järgmise WS-iga.ei muutu. Cell.FormatConditions.Add, see on otse makrost, kuigi salvestatud makro ütles Valik - ma ei taha, et peaksin selle valima, nii et ma lihtsalt ütlen Cell, see on iga üksik lahter. Kasutame xlNotEquali ja valemi: = ”=” 2 asemel, mida salvestatud kood seal tegi, olen liitnud kõik, mis selles lahtris on. Nii et kontrollige, kas see pole praeguse väärtusega võrdne. Nii et kui lahtril on praegu 2, siis ütleme, et see pole võrdne 2-ga. Kui lahtril on praegu 16,5, ütleme, et see pole võrdne 16,5-ga. Ja siis on ülejäänud osa lihtsalt sirge salvestatud makro, salvestatud makro, salvestatud makro, salvestatud makro. Kõik see pärineb salvestatud makrost. Lõpeta see, kui lõpuga If. Lõpeta see järgmise WS-iga.see on otse makrost, kuigi salvestatud makro ütles valik - ma ei taha, et peaksin seda valima, nii et ma lihtsalt ütlen, et Cell, see on iga üksik lahter. Kasutame xlNotEquali ja valemi: = ”=” 2 asemel, mida salvestatud kood just seal tegi, olen liitnud kõik, mis selles lahtris on. Nii et kontrollige, kas see pole praeguse väärtusega võrdne. Nii et kui lahtril on praegu 2, siis ütleme, et see pole võrdne 2-ga. Kui lahtril on praegu 16,5, ütleme, et see pole võrdne 16,5-ga. Ja siis on ülejäänud osa lihtsalt sirge salvestatud makro, salvestatud makro, salvestatud makro, salvestatud makro. Kõik see pärineb salvestatud makrost. Lõpeta see, kui on lõpp If. Lõpeta see järgmise WS-iga.see on otse makrost, kuigi salvestatud makro ütles valik - ma ei taha, et peaksin seda valima, nii et ma lihtsalt ütlen, et Cell, see on iga üksik lahter. Kasutame xlNotEquali ja valemi: = ”=” 2 asemel, mida salvestatud kood just seal tegi, olen liitnud kõik, mis selles lahtris on. Nii et kontrollige, kas see pole praeguse väärtusega võrdne. Nii et kui lahtril on praegu 2, siis ütleme, et see pole võrdne 2-ga. Kui lahtril on praegu 16,5, ütleme, et see pole võrdne 16,5-ga. Ja siis on ülejäänud osa lihtsalt sirge salvestatud makro, salvestatud makro, salvestatud makro, salvestatud makro. Kõik see pärineb salvestatud makrost. Lõpeta see, kui on lõpp If. Lõpeta see järgmise WS-iga.Ma ei taha seda valida, nii et ma lihtsalt ütlen Cell, see on iga üksik lahter. Kasutame xlNotEquali ja valemi: = ”=” 2 asemel, mida salvestatud kood seal tegi, olen liitnud kõik, mis selles lahtris on. Nii et kontrollige, kas see pole praeguse väärtusega võrdne. Nii et kui lahtril on praegu 2, siis ütleme, et see pole võrdne 2-ga. Kui lahtril on praegu 16,5, ütleme, et see pole võrdne 16,5-ga. Ja siis on ülejäänud osa lihtsalt sirge salvestatud makro, salvestatud makro, salvestatud makro, salvestatud makro. Kõik see pärineb salvestatud makrost. Lõpeta see, kui on lõpp If. Lõpeta see järgmise WS-iga.Ma ei taha seda valida, nii et ma lihtsalt ütlen Cell, see on iga üksik lahter. Kasutame xlNotEquali ja valemi: = ”=” 2 asemel, mida salvestatud kood just seal tegi, olen liitnud kõik, mis selles lahtris on. Nii et kontrollige, kas see pole praeguse väärtusega võrdne. Nii et kui lahtril on praegu 2, siis ütleme, et see pole võrdne 2-ga. Kui lahtril on praegu 16,5, ütleme, et see pole võrdne 16,5-ga. Ja siis on ülejäänud osa lihtsalt sirge salvestatud makro, salvestatud makro, salvestatud makro, salvestatud makro. Kõik see pärineb salvestatud makrost. Lõpeta see, kui lõpuga If. Lõpeta see järgmise WS-iga.= ”=” 2, mida salvestatud kood just seal tegi, olen liitnud kõik, mis selles lahtris on. Nii et kontrollige, kas see pole praeguse väärtusega võrdne. Nii et kui lahtril on praegu 2, siis ütleme, et see pole võrdne 2-ga. Kui lahtril on praegu 16,5, ütleme, et see pole võrdne 16,5-ga. Ja siis on ülejäänud osa lihtsalt sirge salvestatud makro, salvestatud makro, salvestatud makro, salvestatud makro. Kõik see pärineb salvestatud makrost. Lõpeta see, kui lõpuga If. Lõpeta see järgmise WS-iga.= ”=” 2, mida salvestatud kood just seal tegi, olen liitnud kõik, mis selles lahtris on. Nii et kontrollige, kas see pole praeguse väärtusega võrdne. Nii et kui lahtril on praegu 2, siis ütleme, et see pole võrdne 2-ga. Kui lahtril on praegu 16,5, ütleme, et see pole võrdne 16,5-ga. Ja siis on ülejäänud osa lihtsalt sirge salvestatud makro, salvestatud makro, salvestatud makro, salvestatud makro. Kõik see pärineb salvestatud makrost. Lõpeta see, kui on lõpp If. Lõpeta see järgmise WS-iga.salvestatud makro, salvestatud makro. Kõik see pärineb salvestatud makrost. Lõpeta see, kui on lõpp If. Lõpeta see järgmise WS-iga.salvestatud makro, salvestatud makro. Kõik see pärineb salvestatud makrost. Lõpeta see, kui on lõpp If. Lõpeta see järgmise WS-iga.
Hästi, nii et mul on makro nimega ApplyCF. Minge tagasi Exceli juurde, lisage kuju. Siin on kuju lihtne saada: sisestage, valin alati ümardatud ristküliku, tippige käsk Lähtesta praegustele väärtustele. Rakendame kodu, keskpunkti ja keskus muudab selle natuke suuremaks. Ma armastan sära. Ma arvan, et see on rumal, kui näete, et seda pole olemas, kuma, seade, mis mulle meeldib, pole olemas, nii et ma lähen alati lehe paigutuse ja efektide juurde ja valin selle teise. Ja siis, kui naasen formaadi juurde, saan valida sellise, millel on tegelikult natuke sära. Mulle tundub, et see tundub lahe, ma arvan, et see on seda väärt. Paremklõpsake käsku Määra makro ja öelge ApplyCF, klõpsake nuppu OK. Olgu, ja siis, kui see klõpsan, siis see läbib kõik need lehed, leiab kõik valemirakud ja seadistab tingimusliku vormingu, mis ütleb: Kui need lahtrid pole võrdsed 7-ga,värvi muuta, olgu? See selleks. See on nii kiire, juhtus nii kiiresti. BAM! See on tehtud. Ja nüüd, vaadake, kas ma muudan selle 11-ks, kõik need lahtrid lihtsalt muutusid. Kui nüüd läheb tagasi väärtuseni 1, siis ahh, värvid muutusid. Nii et olenemata väärtusest, kui me muutume - kui ma muudan seda lahtrit, muutuvad kõik need lahtrid. Kui ma muudan seda lahtrit, siis kõik need lahtrid muutuvad. Kui ma muudan seda lahtrit, siis kõik need lahtrid muutuvad.kõik need rakud muutuvad.kõik need rakud muutuvad.
Hästi, nüüd on see uus normaalne. Siit edasi tahan jällegi jälile jõuda. Niisiis lähtestan praegused väärtused ja kui ma muudan selle väärtuseks 3, muutuvad need müügid. Oh, muide, need rakud siia tagasi ja ka need muud lehed muutusid sellele vastuseks. Kas saate Excelis muudatusi jälgida, kui see on olemas? Jah, see on tõesti labane. See ei näita teile asju, mis muutusid ja töövihiku näitamine on jube, jube asi. Kuid selle lihtsa, lihtsa väikese makro abil see töötab.
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 hei, ma tahan tänada teid peatumast. Järgmisel korral näeme järgmise võrguülekande jaoks.
Laadige fail alla
Laadige näidisfail alla siit: Podcast2059.xlsm
