Kövesse nyomon az Excel képlet celláinak változásait. Meg tudja mutatni, hogy mely elemek éppen megváltoztak bizonyos bemeneti cellák megváltoztatása következtében?
Videót nézni
- Az Excel változásainak nyomon követése kissé furcsa.
- A cél az, hogy nyomon kövessük, hogy az Excel képletcellái mit változtatnak.
- Mentés másként a munkafüzet XLSM formátumban történő mentéséhez.
- Módosítsa a Makró biztonságát.
- Rögzítsen egy makrót, hogy kitalálja a kódot, hogy beállítsa a feltétel nélküli formázást a 2-vel nem egyenlő számokhoz.
- Válassza ki a kívánt formázást.
- Rögzítsen egy másik makrót, hogy megtudja, hogyan lehet eltávolítani a CF-t a munkalapról.
- A makróban adjon meg egy hurkot az egyes munkalapokhoz.
- Adjon hozzá egy IF utasítást, hogy megakadályozza a cím futását.
- Adjon hozzá egy hurkot az egyes képlet cellák ellenőrzéséhez.
- Adja hozzá a Feltételes formázást, ha meg szeretné tudni, hogy a cella értéke a makróban fut-e.
- Menjen vissza az Excel programba.
- Adjon hozzá egy alakzatot. Rendelje hozzá a makrót az alakhoz.
- Kattintson a Shape gombra a makró futtatásához.
- Bónusztipp: Húzza a VBA modult egy új munkafüzetbe.
Videó átirat
Ismerje meg az Excel programot a Podcast 2059. epizódjából: Az Excel nyomon követi a változásokat (a Képlet eredményei között)
Hé, üdvözlöm újra a neten, Bill Jelen vagyok. A mai kérdés Montrealból érkezett a pályaváltásokról. A változások nyomon követése, rendben. Tehát itt van, amink van. 4 bemeneti cellánk van, és egy csomó Formula-sejt, amelyek ezekre a bemeneti cellákra támaszkodnak. És ha bekapcsolnám, visszatérek az Ellenőrzés fülre, bekapcsolom a Változtatások kiemelése, a módosítások nyomon követését szerkesztés közben, kattintson az OK gombra, rendben. És figyelmeztettek, hogy el kell menteniük a munkafüzetet, és hogy a makrókat nem lehet megosztott munkafüzetekben használni. Tudod mit? Ez a probléma, amikor nyomon követi a változásokat, megosztják a munkafüzetet, és egy csomó olyan dolog nem fordulhat elő a megosztott munkafüzetekben, mint például a makrók és egy csomó más dolog. De nézzük csak meg, hogy a változások nyomon követése hogyan működik ma az Excelben.
Vegyük ezt a 2-t, és váltsunk 2-ről 22-re, és vegyük ezt a 4-et és változtassuk meg 4-ről 44-re. Rendben, és látod, amit a sávváltozások során megjegyeztek, az az, hogy ez a két cella megváltozott, rendben, ezek a lila háromszögek a tényleges vágányváltozások. Mindezek a vörös dolgok, ez nem történik meg, de csak azt illusztráltam, hogy ezek a vörös sejtek változnak, és a változások nyomon követése nem mond semmit ezekről a változásokról, rendben? Tehát, csak annyit mond, hogy ez a két sejt megváltozott, de a többi sejt is megváltozott. És akkor a montreali kérdés az, hogy van-e mód arra, hogy a pálya változásai valóban megmutassanak nekünk mindent, ami változik, nemcsak ezek az input cellák változtak?
Rendben, tehát első dolog, amit meg kell tennünk, hogy kikapcsoljuk az Excel beépített Változások nyomon követését. És akkor van-e olyan módszer, amellyel megszerezhetjük - felépíthetjük a saját pályaváltási rendszerünket, amely lehetővé teszi számunkra az összes megváltozott képletcella megtekintését? Rendben, tehát az 1. lépés és ez a legfontosabb lépés, ne hagyja ki ezt. Nézze meg a fájlt, a fájl neve XLSX, ezt el kell mentenie: Fájl, Mentés másként, Makróként engedélyezett munkafüzetként, különben egyik sem fog működni. Kattintson a jobb gombbal, Testreszabja a szalagot, kapcsolja be a Fejlesztőt, miután elérte a Fejlesztőt, lépjen a Macro Security oldalra, váltson erről a beállításról - arra, amely azt mondja, hogy nem engedjük a makrókat futtatni, vagy nem is fogjuk megmondani hogy ott vannak ebben a beállításban. Meg kell tennie ezt a két lépést. Már megtettem ezt a két lépést. Minden nap ezzel a két lépéssel élek.Már javítva van, de ha még nem ismered a makrókat, akkor ez új számodra. És akkor meg kell találnunk, hogy milyen formázást szeretne. Rendben, szóval itt csak néhány cellát választok, rögzítek egy makrot, amelynek neve HowToCFRed, nem fogok gyorsbillentyűt rendelni, mert ez soha nem fog többé futni. Csak kódot rögzítek, hogy kiderüljön, hogyan működik a feltételes formázás. És bejutunk a Kezdőlap, a Feltételes formázás, a Jelölő cellák közé, amelyek nem egyenlőek - Tehát, További szabályok, Formázási cellák nem egyenlőek - Látja ezt? Ez nem az eredeti legördülő menüben található, de ha bejön ide, akkor nem egyenlő a 2-vel, majd válassza a formátumot. Ez a fontos rész. Tehát piros hátteret fogok választani. Bármilyen színt választasz itt, rendben? Lépjen a További színek oldalra, válasszon más pirosat,menj be a Custom-ba, válassz más pirosat, rendben? Ez a makró-felvevő szépsége, tökéletes pirosat kapnak nekünk, kéket vagy bármit, amire vágynak. Rendben, kattintson az OK gombra. És akkor leállítjuk a felvételt, rendben. Ennek megint csak az a lényege, hogy megnézzük, mi a feltételes formátumok kódja.
Megyek a Makrókra, a Feltételes piros formázás és a Szerkesztés oldalra. Rendben, szóval itt vannak ennek a kódnak a fontos részei. Látom, hogy feltételes formátumot adnak hozzá az xlNotEqual használatával, és nehezen idézzük, hogy ne legyen egyenlő 2-vel. És akkor a cella belsejét erre a színre változtatjuk.
Rendben, azt is meg kell találnom, hogyan lehet törölni az összes feltételes formázást a lapról. Tehát, visszatérve az Excel programhoz, rögzítsen egy másik makrót, az összes feltételes törlése, OK. Gyere ide a Főoldal fülre, menj a Feltételes formázás, a Szabály törlése a teljes lapról, A felvétel leállítása lehetőségre, majd megnézzük ezt a kódot. Nagyszerű, ez egy egysoros makró. És még itt is tetszik, hogy ahogyan az egész lapon csinálják, csak cellákra utal. Más szóval, az aktív lap összes cellája.
Most egy kicsit általánosabbá kell tennem ezt a makrót, a rögzített makrót. És rengeteg könyvet írtam arról, hogyan kell VBA-t csinálni Excel-ben, és videókat készítettem arról, hogyan kell VBA-t csinálni Excel-ben, és itt van az egyszerű dolog: képesnek kell lennie egy ilyen makró rögzítésére, de aztán hozzá kb. öt vagy hat sort annak érdekében, hogy a makrót kellően általánosvá tudjuk tenni.
És beszélni fogok ezekről a sorokról, rendben. Tehát az első dolog, amit meg akarok tenni, azt akarom mondani, hogy át akarom nézni az aktív munkafüzetet, végig kell néznem az összes munkalapot. Tehát minden munkalapon a WS az objektumváltozó, átmegyek az összes munkalapon. És a montreáli személy azt mondta: "Hé, van egy lap, amin nem akarom, hogy ez megtörténjen." Tehát, ha a WS.Name a munkalap dot nevével nem egyenlő a címmel, akkor a kódot a makróban fogjuk megtenni. Itt van a lap neve: .Cells.FormatConditions.Delete. Tehát a cím kivételével minden egyes lapot át kell néznünk, és törölnünk kell az összes formázási feltételt, majd át kell néznünk a lap egyes celláit, de nem az összes cellát, csak azokat a cellákat, amelyeknek képlete van . Ha nincs képlete, akkor nemNem kell formázni, mert nem fog változni. Cell.FormatConditions.Add, ez közvetlenül a makróról származik, bár a rögzített makró azt mondta, hogy Kiválasztás - Nem akarom, hogy ki kell jelölnöm, ezért csak azt mondom, hogy Cell, ez minden egyes cella. Az xlNotEqual-t fogjuk használni, és a Formula: = ”=” 2 helyett, amit a rögzített kód pontosan ott csinált, összefűztem bármit, ami abban a cellában található. Tehát ellenőrizze, hogy nem egyenlő-e az aktuális értékkel. Tehát ha a cellának jelenleg 2 van, akkor azt mondjuk, hogy nem egyenlő a 2-vel. Ha a cellának jelenleg 16,5-e van, akkor azt mondjuk, hogy nem egyenlő a 16,5-tel. És akkor a maradék csak egyenesen rögzített makró, rögzített makró, rögzített makró, rögzített makró. Mindez egy rögzített makróból származik. Fejezze be ezt, ha egy Véget Ha. Fejezze be ezt a következő WS-szel. s nem fog változni. Cell.FormatConditions.Add, ez közvetlenül a makróról származik, bár a rögzített makró azt mondta, hogy Kiválasztás - Nem akarom, hogy kiválasszam, ezért csak azt mondom, hogy Cell, ez minden egyes cella. Az xlNotEqual-t fogjuk használni, és a Formula: = ”=” 2 helyett, amit a rögzített kód pontosan ott csinált, összefűztem bármit, ami abban a cellában található. Tehát ellenőrizze, hogy nem egyenlő-e az aktuális értékkel. Tehát ha a cellának jelenleg 2 van, akkor azt mondjuk, hogy nem egyenlő a 2-vel. Ha a cellának jelenleg 16,5-e van, akkor azt mondjuk, hogy nem egyenlő a 16,5-tel. És akkor a maradék csak egyenesen rögzített makró, rögzített makró, rögzített makró, rögzített makró. Mindez egy rögzített makróból származik. Fejezze be ezt, ha egy Véget Ha. Fejezze be ezt a következő WS-szel.s nem fog változni. Cell.FormatConditions.Add, ez közvetlenül a makróról származik, bár a rögzített makró azt mondta, hogy Kiválasztás - Nem akarom, hogy ki kell jelölnöm, ezért csak azt mondom, hogy Cell, ez minden egyes cella. Az xlNotEqual-t fogjuk használni, és a Formula: = ”=” 2 helyett, amit a rögzített kód pontosan ott csinált, összefűztem bármit, ami abban a cellában található. Tehát ellenőrizze, hogy nem egyenlő-e az aktuális értékkel. Tehát ha a cellának jelenleg 2 van, akkor azt mondjuk, hogy nem egyenlő a 2-vel. Ha a cellának jelenleg 16,5-e van, akkor azt mondjuk, hogy nem egyenlő a 16,5-tel. És akkor a maradék csak egyenesen rögzített makró, rögzített makró, rögzített makró, rögzített makró. Mindez egy rögzített makróból származik. Fejezze be ezt, ha egy Véget Ha. Fejezze be ezt a következő WS-szel.ez közvetlenül a makróról származik, bár a rögzített makró azt mondta, hogy Kiválasztás - Nem akarom, hogy kijelöljem, ezért csak azt mondom, hogy Cell, ez minden egyes cella. Az xlNotEqual-t fogjuk használni, és a Formula: = ”=” 2 helyett, amit a rögzített kód pontosan ott csinált, összefűztem bármit, ami abban a cellában található. Tehát ellenőrizze, hogy nem egyenlő-e az aktuális értékkel. Tehát ha a cellának jelenleg 2 van, akkor azt mondjuk, hogy nem egyenlő a 2-vel. Ha a cellának jelenleg 16,5-e van, akkor azt mondjuk, hogy nem egyenlő a 16,5-tel. És akkor a maradék csak egyenesen rögzített makró, rögzített makró, rögzített makró, rögzített makró. Mindez egy rögzített makróból származik. Fejezze be ezt, ha egy Véget Ha. Fejezze be ezt a következő WS-szel.ez közvetlenül a makróról származik, bár a rögzített makró azt mondta, hogy Kiválasztás - Nem akarom, hogy kijelöljem, ezért csak azt mondom, hogy Cell, ez minden egyes cella. Az xlNotEqual-t fogjuk használni, és a Formula: = ”=” 2 helyett, amit a rögzített kód pontosan ott csinált, összefűztem bármit, ami abban a cellában található. Tehát ellenőrizze, hogy nem egyenlő-e az aktuális értékkel. Tehát ha a cellának jelenleg 2 van, akkor azt mondjuk, hogy nem egyenlő a 2-vel. Ha a cellának jelenleg 16,5-e van, akkor azt mondjuk, hogy nem egyenlő a 16,5-tel. És akkor a maradék csak egyenesen rögzített makró, rögzített makró, rögzített makró, rögzített makró. Mindez egy rögzített makróból származik. Fejezze be ezt, ha egy Véget Ha. Fejezze be ezt a következő WS-szel.Nem akarom, hogy kiválasszuk, ezért csak azt mondom, hogy Cell, ez az egyes cellák. Az xlNotEqual-t fogjuk használni, és a Formula: = ”=” 2 helyett, amit a rögzített kód ott csinált, összefűztem bármit, ami abban a cellában van. Tehát ellenőrizze, hogy nem egyenlő-e az aktuális értékkel. Tehát ha a cellának jelenleg 2 van, akkor azt mondjuk, hogy nem egyenlő a 2-vel. Ha a cellának jelenleg 16,5-e van, akkor azt mondjuk, hogy nem egyenlő a 16,5-tel. És akkor a maradék csak egyenesen rögzített makró, rögzített makró, rögzített makró, rögzített makró. Mindez egy rögzített makróból származik. Fejezze be ezt, ha egy Véget Ha. Fejezze be ezt a következő WS-szel.Nem akarom, hogy kiválasszuk, ezért csak azt mondom, hogy Cell, ez az egyes cellák. Az xlNotEqual-t fogjuk használni, és a Formula: = ”=” 2 helyett, amit a rögzített kód pontosan ott csinált, összefűztem bármit, ami abban a cellában található. Tehát ellenőrizze, hogy nem egyenlő-e az aktuális értékkel. Tehát ha a cellának jelenleg 2 van, akkor azt mondjuk, hogy nem egyenlő a 2-vel. Ha a cellának jelenleg 16,5-e van, akkor azt mondjuk, hogy nem egyenlő a 16,5-tel. És akkor a maradék csak egyenesen rögzített makró, rögzített makró, rögzített makró, rögzített makró. Mindez egy rögzített makróból származik. Fejezze be ezt, ha egy Véget Ha. Fejezze be ezt a következő WS-szel.= ”=” 2 amit a rögzített kód ott csinált, összefűztem bármit, ami abban a cellában van. Tehát ellenőrizze, hogy nem egyenlő-e az aktuális értékkel. Tehát ha a cellának jelenleg 2 van, akkor azt mondjuk, hogy nem egyenlő a 2-vel. Ha a cellának jelenleg 16,5-e van, akkor azt mondjuk, hogy nem egyenlő a 16,5-tel. És akkor a maradék csak egyenesen rögzített makró, rögzített makró, rögzített makró, rögzített makró. Mindez egy rögzített makróból származik. Fejezze be ezt, ha egy Véget Ha. Fejezze be ezt a következő WS-szel.= ”=” 2 amit a rögzített kód ott csinált, összefűztem bármit, ami abban a cellában van. Tehát ellenőrizze, hogy nem egyenlő-e az aktuális értékkel. Tehát ha a cellának jelenleg 2 van, akkor azt mondjuk, hogy nem egyenlő a 2-vel. Ha a cellának jelenleg 16,5-e van, akkor azt mondjuk, hogy nem egyenlő a 16,5-tel. És akkor a maradék csak egyenesen rögzített makró, rögzített makró, rögzített makró, rögzített makró. Mindez egy rögzített makróból származik. Fejezze be ezt, ha egy Véget Ha. Fejezze be ezt a következő WS-szel.rögzített makró, rögzített makró. Mindez egy rögzített makróból származik. Fejezze be ezt, ha egy Véget Ha. Fejezze be ezt a következő WS-szel.rögzített makró, rögzített makró. Mindez egy rögzített makróból származik. Fejezze be ezt, ha egy Véget Ha. Fejezze be ezt a következő WS-szel.
Rendben, tehát van egy ApplyCF nevű makróm. Menjen vissza az Excel programba, adjon hozzá egy alakzatot. Könnyű itt alakzatot kialakítani: Helyezzen be, mindig egy lekerekített téglalapot választok, írja be a Visszaállítás az aktuális értékekhez parancsot. Alkalmazzuk az Otthont, a központot, és a központ kicsit nagyobb lesz. Imádom a fényt. Gondolom, szerinted butaság látni, hogy nincs meg, nincs fény, a beállítás, ami nekem tetszik, ezért mindig az Oldalelrendezés és effektusok oldalra lépek, és kiválasztom azt a másodikat. Aztán amikor visszatérek a formátumhoz, kiválaszthatok egyet, amely valójában egy kis ragyogással rendelkezik. Számomra szerintem klassznak tűnik, szerintem megéri. Kattintson a jobb gombbal, a Makró hozzárendelése elemre, és mondja ki, hogy ApplyCF, kattintson az OK gombra. Rendben, és akkor mit fog tenni, amikor rákattintok, átmegy az összes lapon, megtalálja az összes képlet cellát, és beállít egy feltételes formázást, amely így szól: Ha ezek a cellák nem egyenlőek 7-vel,megváltoztatja a színét, rendben? Ez az. Olyan gyorsan történt, olyan gyorsan történt. BAM! Kész. És most figyelje meg, hogy ezt megváltoztatom-e 11-re, az összes cella csak megváltozott. Ha most visszatér az 1-re, akkor a színek megváltoztak. Tehát bármi is volt az érték, amikor változtunk - ha megváltoztatom ezt a cellát, akkor az összes sejt megváltozik. Ha megváltoztatom ezt a cellát, akkor az összes sejt megváltozik. Ha megváltoztatom ezt a cellát, akkor az összes sejt megváltozik.az összes sejt megváltozik.az összes sejt megváltozik.
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.
Hát hé, szeretném megköszönni, hogy betértél. Legközelebb találkozunk egy újabb netcast-lal.
Fájl letöltése
Töltse le a minta fájlt innen: Podcast2059.xlsm
