Verfolgen Sie Änderungen in Formelzellen - Excel-Tipps

Verfolgen Sie Änderungen in Excel-Formelzellen. Können Sie zeigen, welche Elemente sich aufgrund der Änderung bestimmter Eingabezellen gerade geändert haben?

Schau Video

Änderungen in Excel zu verfolgen ist etwas bizarr.
Ziel ist es zu verfolgen, welche Formelzellen in Excel sich ändern.
Speichern unter, um die Arbeitsmappe als XLSM zu speichern.
Ändern Sie die Makrosicherheit.
Zeichnen Sie ein Makro auf, um den Code zum Einrichten der bedingten Formatierung für Zahlen ungleich 2 herauszufinden.
Wählen Sie die gewünschte Formatierung.
Zeichnen Sie ein anderes Makro auf, um zu erfahren, wie Sie CF aus dem Arbeitsblatt entfernen.
Fügen Sie im Makro für jedes Arbeitsblatt eine Schleife hinzu.
Fügen Sie eine IF-Anweisung hinzu, um zu verhindern, dass sie auf Title ausgeführt wird.
Fügen Sie eine Schleife hinzu, um jede Formelzelle zu überprüfen.
Fügen Sie die bedingte Formatierung hinzu, um festzustellen, ob der Zellenwert zum Zeitpunkt der Ausführung des Makros angezeigt wird.
Gehen Sie zurück zu Excel.
Fügen Sie eine Form hinzu. Weisen Sie das Makro der Form zu.
Klicken Sie auf die Form, um das Makro auszuführen.
Bonus-Tipp: Ziehen Sie ein VBA-Modul in eine neue Arbeitsmappe.

Video-Transkript

Lernen Sie Excel aus Podcast, Episode 2059: Änderungen in Excel verfolgen (in Formelergebnissen)

Hey, willkommen zurück im Netcast, ich bin Bill Jelen. Die heutige Frage aus Montreal zu Streckenänderungen. Verfolgen Sie Änderungen, in Ordnung. Also hier ist was wir haben. Wir haben 4 Eingabezellen und eine ganze Reihe von Formelzellen, die auf diesen Eingabezellen basieren. Und wenn ich mich einschalten würde, kehre ich zur Registerkarte Überprüfen zurück, aktiviere Änderungen hervorheben, verfolge die Änderungen während der Bearbeitung und klicke auf OK. Und sie haben mich gewarnt, dass sie die Arbeitsmappe speichern müssen und dass Makros nicht in freigegebenen Arbeitsmappen verwendet werden können. Du weißt, dass? Dies ist das Problem, wenn Sie Änderungen nachverfolgen, diese die Arbeitsmappe gemeinsam nutzen und es eine ganze Reihe von Dingen gibt, die in freigegebenen Arbeitsmappen nicht passieren können, wie Makros und eine ganze Reihe anderer Dinge. Aber schauen wir uns nur an, wie Änderungen in Excel heute funktionieren.

Nehmen wir diese 2 und ändern sie von 2 auf 22 und nehmen diese 4 und ändern sie von 4 auf 44. Okay, und Sie sehen, was sie bei Spuränderungen festgestellt haben, ist, dass sich diese beiden Zellen geändert haben, okay, diese lila Dreiecke sind Die tatsächliche Spur ändert sich. All diese roten Dinge, das passiert nicht, aber ich habe nur gezeigt, dass sich all diese roten Blutkörperchen ändern und Änderungen verfolgen nichts über diese Änderungen aussagt, okay? Es heißt also nur, dass diese beiden Zellen geändert wurden, aber alle diese anderen Zellen wurden ebenfalls geändert. Die Frage aus Montreal lautet also: Gibt es eine Möglichkeit, Änderungen nachverfolgen zu lassen, die uns tatsächlich alles zeigen, was sich ändert, nicht nur diese Eingabezellen?

Also gut, als erstes müssen wir die in Excel integrierten Track Changes deaktivieren. Und dann gibt es eine Möglichkeit, die wir bekommen können - wir können unser eigenes Track-Änderungssystem aufbauen, mit dem wir alle geänderten Formelzellen sehen können? Okay, also Schritt 1 und dieser Schritt ist der wichtigste Schritt, überspringen Sie dies nicht. Schauen Sie sich Ihre Datei an, Ihre Datei heißt XLSX. Sie müssen Folgendes speichern: Datei, Speichern unter, Als makrofähige Arbeitsmappe, sonst funktioniert nichts davon. Sie müssen mit der rechten Maustaste klicken, die Multifunktionsleiste anpassen, Entwickler aktivieren, sobald Sie zu Entwickler gelangen, zur Makrosicherheit wechseln, diese Einstellung ändern - die Einstellung, die besagt, dass wir keine Makros ausführen lassen oder nicht einmal sagen werden Sie, dass sie zu dieser Einstellung da sind. Sie müssen diese beiden Schritte ausführen. Ich habe diese beiden Schritte bereits ausgeführt. Ich lebe jeden Tag mit diesen beiden Schritten.Bereits behoben, aber wenn Sie neu in Makros sind, ist dies neu für Sie. Und dann müssen wir herausfinden, welche Art von Formatierung Sie möchten. Okay, also werde ich hier nur einige Zellen auswählen. Ich werde ein Makro mit dem Namen HowToCFRed aufnehmen. Ich werde keine Tastenkombination zuweisen, da dies nie wieder ausgeführt wird. Ich nehme nur Code auf, um herauszufinden, wie die bedingte Formatierung funktioniert. Und wir werden uns mit Home, Bedingte Formatierung, Hervorheben von Zellen befassen, die nicht gleich sind - Also, Mehr Regeln, Zellen ungleich formatieren - Sehen Sie das? Es ist nicht in der ursprünglichen Dropdown-Liste, aber wenn Sie hier reinkommen, ungleich 2, und wählen Sie dann das Format. Dies ist der wichtige Teil. Also werde ich einen roten Hintergrund wählen. Sie wählen hier die gewünschte Farbe, okay? Gehen Sie sogar zu Mehr Farben, wählen Sie ein anderes Rot,Gehe in Custom, wähle ein anderes Rot, okay? Das ist das Schöne am Macro Recorder. Sie bringen uns ein perfektes Rot für Sie oder Blau oder was auch immer Sie wollen. Okay, klicken Sie auf OK. Und dann hören wir auf aufzunehmen, okay. Auch hier geht es nur darum zu sehen, was der Code für bedingte Formate ist.

Ich gehe zu Makros, Wie man bedingtes Format rot und bearbeite. Okay, hier sind die wichtigen Teile dieses Codes. Ich kann sehen, dass sie mit xlNotEqual ein bedingtes Format hinzufügen, und wir zitieren es nur schwer als ungleich 2. Und dann ändern wir das Innere der Zelle in diese Farbe.

In Ordnung, ich muss auch herausfinden, wie alle bedingten Formatierungen auf dem Blatt gelöscht werden. Zurück zu Excel: Ein anderes Makro aufzeichnen, Alle Bedingungen löschen, OK. Kommen Sie hierher zur Registerkarte "Startseite", gehen Sie zu "Bedingte Formatierung", "Regel aus dem gesamten Blatt löschen", "Aufzeichnung beenden" und wir werden uns diesen Code ansehen. Großartig, es ist ein einzeiliges Makro. Und ich mag es sogar hier, dass die Art und Weise, wie sie es für das gesamte Blatt tun, sich nur auf Zellen bezieht. Mit anderen Worten, alle Zellen auf dem aktiven Blatt.

Jetzt muss ich dieses Makro, das aufgezeichnete Makro, etwas allgemeiner gestalten. Und ich habe viele Bücher darüber geschrieben, wie man VBA in Excel macht, und ich habe Videos gemacht, wie man VBA in Excel macht, und hier ist die einfache Sache: Sie müssen in der Lage sein, ein Makro wie dieses aufzuzeichnen, aber dann fügen Sie hinzu ungefähr fünf oder sechs Zeilen, um das Makro generisch genug machen zu können.

Und ich werde über diese Zeilen sprechen, okay. Das erste, was ich tun möchte, ist zu sagen, dass ich die aktive Arbeitsmappe durchgehen möchte, alle Arbeitsblätter durchgehen möchte. Für jedes Arbeitsblatt ist WS die Objektvariable. Ich werde alle Arbeitsblätter durchgehen. Und die Person aus Montreal sagte: "Hey, es gibt ein Blatt, auf dem ich das nicht haben möchte." Wenn also der WS.Name mit dem Arbeitsblattpunktnamen nicht gleich dem Titel ist, führen wir den Code im Makro aus. Hier ist der Blattname: .Cells.FormatConditions.Delete. Wir werden also jedes einzelne Blatt mit Ausnahme des Titels durchgehen und alle Formatbedingungen löschen. Dann werden wir jede Zelle im Blatt durchgehen, aber nicht alle Zellen, nur die Zellen, die Formeln haben . Wenn es keine Formel gibt, dann mache ich nichtsSie müssen es nicht formatieren, da es sich nicht ändern wird. Cell.FormatConditions.Add, dies ist direkt aus dem Makro, obwohl das aufgezeichnete Makro Auswahl sagte - Ich möchte es nicht auswählen müssen, also sage ich nur Zelle, das ist jede einzelne Zelle. Wir werden xlNotEqual verwenden und anstelle von Formel: = ”=” 2, was der aufgezeichnete Code genau dort getan hat, habe ich alles verkettet, was sich in dieser Zelle befindet. Überprüfen Sie also, ob es nicht dem aktuellen Wert entspricht. Wenn die Zelle derzeit 2 hat, sagen wir, dass sie nicht gleich 2 ist. Wenn die Zelle derzeit 16,5 hat, sagen wir, dass sie nicht gleich 16,5 ist. Und dann ist der Rest nur ein direkt aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro. All das stammt aus einem aufgezeichneten Makro. Beenden Sie dieses If mit einem End If. Beenden Sie dies für mit einem nächsten WS. wird sich nicht ändern. Cell.FormatConditions.Add, dies ist direkt aus dem Makro, obwohl das aufgezeichnete Makro Auswahl sagte - Ich möchte es nicht auswählen müssen, also sage ich nur Zelle, das ist jede einzelne Zelle. Wir werden xlNotEqual verwenden und anstelle von Formel: = ”=” 2, was der aufgezeichnete Code genau dort getan hat, habe ich alles verkettet, was sich in dieser Zelle befindet. Überprüfen Sie also, ob es nicht dem aktuellen Wert entspricht. Wenn die Zelle derzeit 2 hat, sagen wir ungleich 2. Wenn die Zelle derzeit 16,5 hat, sagen wir ungleich 16,5. Und dann ist der Rest nur ein direkt aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro. All das stammt aus einem aufgezeichneten Makro. Beenden Sie dieses If mit einem End If. Beenden Sie dies für mit einem nächsten WS.wird sich nicht ändern. Cell.FormatConditions.Add, dies ist direkt aus dem Makro, obwohl das aufgezeichnete Makro Auswahl sagte - Ich möchte es nicht auswählen müssen, also sage ich nur Zelle, das ist jede einzelne Zelle. Wir werden xlNotEqual verwenden und anstelle von Formel: = ”=” 2, was der aufgezeichnete Code genau dort getan hat, habe ich alles verkettet, was sich in dieser Zelle befindet. Überprüfen Sie also, ob es nicht dem aktuellen Wert entspricht. Wenn die Zelle derzeit 2 hat, sagen wir ungleich 2. Wenn die Zelle derzeit 16,5 hat, sagen wir ungleich 16,5. Und dann ist der Rest nur ein direkt aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro. All das stammt aus einem aufgezeichneten Makro. Beenden Sie dieses If mit einem End If. Beenden Sie dies für mit einem nächsten WS.Dies ist direkt aus dem Makro, obwohl das aufgezeichnete Makro Auswahl sagte - ich möchte es nicht auswählen müssen, also sage ich nur Zelle, das ist jede einzelne Zelle. Wir werden xlNotEqual verwenden und anstelle von Formel: = ”=” 2, was der aufgezeichnete Code genau dort getan hat, habe ich alles verkettet, was sich in dieser Zelle befindet. Überprüfen Sie also, ob es nicht dem aktuellen Wert entspricht. Wenn die Zelle derzeit 2 hat, sagen wir ungleich 2. Wenn die Zelle derzeit 16,5 hat, sagen wir ungleich 16,5. Und dann ist der Rest nur ein direkt aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro. All das stammt aus einem aufgezeichneten Makro. Beenden Sie dieses If mit einem End If. Beenden Sie dies für mit einem nächsten WS.Dies ist direkt aus dem Makro, obwohl das aufgezeichnete Makro Auswahl sagte - ich möchte es nicht auswählen müssen, also sage ich nur Zelle, das ist jede einzelne Zelle. Wir werden xlNotEqual verwenden und anstelle von Formel: = ”=” 2, was der aufgezeichnete Code genau dort getan hat, habe ich alles verkettet, was sich in dieser Zelle befindet. Überprüfen Sie also, ob es nicht dem aktuellen Wert entspricht. Wenn die Zelle derzeit 2 hat, sagen wir, dass sie nicht gleich 2 ist. Wenn die Zelle derzeit 16,5 hat, sagen wir, dass sie nicht gleich 16,5 ist. Und dann ist der Rest nur ein direkt aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro. All das stammt aus einem aufgezeichneten Makro. Beenden Sie dieses If mit einem End If. Beenden Sie dies für mit einem nächsten WS.Ich möchte es nicht auswählen müssen, also sage ich nur Zelle, das ist jede einzelne Zelle. Wir werden xlNotEqual verwenden und anstelle von Formel: = ”=” 2, was der aufgezeichnete Code genau dort getan hat, habe ich alles verkettet, was sich in dieser Zelle befindet. Überprüfen Sie also, ob es nicht dem aktuellen Wert entspricht. Wenn die Zelle derzeit 2 hat, sagen wir ungleich 2. Wenn die Zelle derzeit 16,5 hat, sagen wir ungleich 16,5. Und dann ist der Rest nur ein direkt aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro. All das stammt aus einem aufgezeichneten Makro. Beenden Sie dieses If mit einem End If. Beenden Sie dies für mit einem nächsten WS.Ich möchte es nicht auswählen müssen, also sage ich nur Zelle, das ist jede einzelne Zelle. Wir werden xlNotEqual verwenden und anstelle von Formel: = ”=” 2, was der aufgezeichnete Code genau dort getan hat, habe ich alles verkettet, was sich in dieser Zelle befindet. Überprüfen Sie also, ob es nicht dem aktuellen Wert entspricht. Wenn die Zelle derzeit 2 hat, sagen wir ungleich 2. Wenn die Zelle derzeit 16,5 hat, sagen wir ungleich 16,5. Und dann ist der Rest nur ein direkt aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro. All das stammt aus einem aufgezeichneten Makro. Beenden Sie dieses If mit einem End If. Beenden Sie dies für mit einem nächsten WS.= ”=” 2 Genau das hat der aufgezeichnete Code genau dort getan. Ich habe alles verkettet, was sich in dieser Zelle befindet. Überprüfen Sie also, ob es nicht dem aktuellen Wert entspricht. Wenn die Zelle derzeit 2 hat, sagen wir, dass sie nicht gleich 2 ist. Wenn die Zelle derzeit 16,5 hat, sagen wir, dass sie nicht gleich 16,5 ist. Und dann ist der Rest nur ein direkt aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro. All das stammt aus einem aufgezeichneten Makro. Beenden Sie dieses If mit einem End If. Beenden Sie dies für mit einem nächsten WS.= ”=” 2 Genau das hat der aufgezeichnete Code genau dort getan. Ich habe alles verkettet, was sich in dieser Zelle befindet. Überprüfen Sie also, ob es nicht dem aktuellen Wert entspricht. Wenn die Zelle derzeit 2 hat, sagen wir ungleich 2. Wenn die Zelle derzeit 16,5 hat, sagen wir ungleich 16,5. Und dann ist der Rest nur ein direkt aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro, ein aufgezeichnetes Makro. All das stammt aus einem aufgezeichneten Makro. Beenden Sie dieses If mit einem End If. Beenden Sie dies für mit einem nächsten WS.aufgezeichnetes Makro, aufgezeichnetes Makro. All das stammt aus einem aufgezeichneten Makro. Beenden Sie dieses If mit einem End If. Beenden Sie dies für mit einem nächsten WS.aufgezeichnetes Makro, aufgezeichnetes Makro. All das stammt aus einem aufgezeichneten Makro. Beenden Sie dieses If mit einem End If. Beenden Sie dies für mit einem nächsten WS.

Okay, ich habe ein Makro namens ApplyCF. Gehen Sie zurück zu Excel und fügen Sie eine Form hinzu. Einfach, hier eine Form zu haben: Einfügen, ich wähle immer ein abgerundetes Rechteck, tippe Auf aktuelle Werte zurücksetzen. Wir werden Home anwenden, das Zentrum und das Zentrum machen es ein bisschen größer. Ich liebe das Leuchten. Ich nehme an, Sie finden es albern zu sehen, dass es nicht da ist, das Leuchten, die Einstellung, die ich mag, ist nicht da, also gehe ich immer zu Seitenlayout und Effekten und wähle die zweite. Und wenn ich dann zum Format zurückkehre, kann ich eines auswählen, das tatsächlich ein wenig leuchtet. Für mich finde ich es cool, ich denke es lohnt sich. Klicken Sie mit der rechten Maustaste auf Makro zuweisen und sagen Sie ApplyCF. Klicken Sie auf OK. Okay, und wenn ich darauf klicke, werden alle diese Blätter durchgesehen, alle Formelzellen gefunden und eine bedingte Formatierung eingerichtet, die besagt: Wenn diese Zellen nicht gleich 7 sind,die Farbe ändern, okay? Das ist es. Es ist so schnell, es ist so schnell passiert. BAM! Es ist fertig. Und jetzt sehen Sie, wenn ich diese auf 11 ändere, haben sich alle diese Zellen gerade geändert. Wenn es nun zurück zur 1 geht, ahh, haben sich die Farben geändert. Was auch immer der Wert war, wenn wir uns ändern - wenn ich diese Zelle ändere, ändern sich alle diese Zellen. Wenn ich diese Zelle ändere, ändern sich alle diese Zellen. Wenn ich diese Zelle ändere, ändern sich alle diese Zellen.Alle diese Zellen ändern sich.Alle diese Zellen ändern sich.

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.

Hey, ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.

Download-Datei

Laden Sie die Beispieldatei hier herunter: Podcast2059.xlsm