Tabellenblätter einer Mappe vergleichen
Gegeben sei eine Arbeitsmappe mit zwei relevanten Tabellenblättern. Im ersten Blatt sind 50.000 Artikelnummern aufgeführt, im zweiten eine Untermenge der Nummern des ersten Blattes. Das dritte Blatt ist per Makro gegen (versehentliches) Löschen geschützt, es dient als Kopier-Vorlage für das erste Blatt, damit Sie nach einem Probelauf rasch wieder die Ausgangssituation herstellen können. Aus diesem Grunde werden Sie auch das Ausführen von Makros gesondert erlauben müssen oder in Kauf nehmen, dass das Reserve-Blatt dennoch versehentlich gelöscht werden kann.
Alle Datensätze aus Blatt 2 löschen
Wenn Sie diese Arbeitsmappe herunterladen werden Sie anhand der Registerbezeichnungen rasch erkennen, was geschehen soll: Im Blatt Alle Artikel sollen von den 50.000 Positionen all jene Artikel gelöscht werden, die im Arbeitsblatt Löschen aufgeführt sind.
Dabei sind zwei mehr oder weniger erschwerende Dinge zu beachten: Beide Listen sind nicht sortiert (und sollen auch in dieser Reihenfolge bleiben) und es sind keine Zahlen, sondern wegen der führenden Nullen, die beim kopieren auch so übernommen werden sollen Texte, die wie Zahlen aussehen. Daher auch das kleine, grüne Dreieck in den Zellen oben links. Übrigens: Wie Sie diese Fehler-Symbole komfortabel entfernen können, sehen Sie in diesem Beitrag.
Wie so oft im Windows- und Excel-Leben führen viele Wege zum Ziel. Zwei davon, beide ohne VBA/Makros werde ich Ihnen hier vorstellen. Und für beide Lösungen gilt, dass Sie eine Hilfsspalte anlegen müssen. Dazu schreiben Sie in B1 eine beliebige Überschrift; die ist unbedingt erforderlich, weil anschließend noch gefiltert werden soll. Ich selbst verwende meist „sprechende” Bezeichnungen in Kurzschreibweise, beispielsweise 2del (to delete, zu löschen).
Wenn Sie mit Excel 2003 oder neuer arbeiten, empfehle ich Ihnen, irgendwo in Spalte A oder Spalte B zu Klicken und dann StrgL (Excel 2003 oder neuer) oder (in Excel 2007 oder neuer) StrgT zu betätigen. Es wird sich solch eine Nachfrage öffnen:
Bestätigungs-Nachfrage für die Tabellen-Erstellung
Achten Sie darauf, dass das Häkchen beim Menüpunkt Tabelle hat Überschriften gesetzt ist. Das Ganze führt zu einer deutlich sichtbaren Änderung der Darstellung:
Farbliche Hervorhebung der Zeilen
Sie haben damit eine so genannte Intelligente Tabelle erstellt, die einige Vorteile hat. Im folgenden Abschnitt setze ich eine solche Liste voraus, im nächsten Abschnitt verwende ich nicht nur eine andere Formel sondern arbeite auch ohne diese Form der Liste, damit Sie auch bei älteren Excel-Versionen davon profitieren können. Die Aufgabe ist beide Male die gleiche: In Tabelle Alle Artikel sollen alle Datensätze gelöscht werden, die im Blatt Löschen aufgeführt sind 😛 .
Lösung mit ZÄHLENWENN()
Ich versichere Ihnen, dass das erstellen der Formel schneller geht als die Berechnung durch Excel wenn Sie so vorgehen, wie hier aufgezeigt:
- Klicken Sie in B2
- Geben Sie dort den ersten Teil einer Formel ein: =ZÄHLENWENN(Löschen!A:A;
- Klicken Sie auf A2
- Fügen Sie nun an die Formel eine schließende Klammer ) sowie >0 an.
Die komplette Formel sieht nun so aus, falls Sie exakt den obigen Angaben gefolgt sind:
=ZÄHLENWENN(Löschen!A:A;[@[Artikel-Nr.]])>0
Wenn Sie die Formel nun per Eingabe (Return, Enter) abschließen, ist Excel einige Zeit mit der Berechnung beschäftigt. Dazu ein kleiner Hinweis: Etwas schneller würde es gehen, wenn Sie in der Formel nicht Löschen!A:A (also die komplette Spalte) sondern den exakten Bereich verwendet hätten, also Löschen!A2:A19500. Alternativ wäre auch diese Formel möglich:
=ZÄHLENWENN(Löschen!$A$2:$A$19500;‘Alle Artikel’!A2)
Sie ist etwas schneller, weil eine Berechnung weniger stattfindet. Und da kann ich bzw. können Sie dann auf größer Null filtern, was dann zum gleichen Ergebnis führt.
Spätestens jetzt haben Sie aber auch einen enormen Vorteil der Intelligenten Tabellen kennen gelernt. Sie haben die Formel ein einziges Mal eingetragen und schon wird sie in allen 50.000 Zeilen angewendet.
Eine kleine Erklärung zur Formel bin ich Ihnen schuldig. Mit ZÄHLENWENN() an sich lasse ich mir die Anzahl der Datensätze berechnen, die in der zweiten Tabelle genau den Wert aus Spalte A haben. Gibt es den Wert nicht, dann ist das Ergebnis 0, sonst mindestens 1. Dann „behaupte” ich, dass das Ergebnis dieser Funktion größer Null ist. Diese Aussage kann entweder stimmen oder nicht. Sie ist WAHR oder FALSCH. Und genau dieses Ergebnis steht jetzt in Spalte B. Und die vielleicht etwas kryptisch anmutende Formulierung [@[Artikel-Nr.]] steht für „Alle Zeilen der Spalte mit der Bezeichnung Artikel-Nr.” in der Überschrift.
Nun in B2 rechts auf den ▼ Klicken und in der Filter-Auswahl nur das WAHR anzeigen lassen. Das Ergebnis stellt sich nun so dar:
Die gefilterten Datensätze
Um nun möglichst komfortabel die Datensätze zu löschen, gehen Sie so vor:
- Klicken Sie auf den Zeilenkopf von A3, welches ja die erste sichtbare Zeile des Datenbereichs ist. Jetzt ist die komplette Zeile markiert.
- Nun StrgShift↓. Automatisch werden alle Zeilen bis Zeile 50001 markiert
- Rechtsklick und aus dem Kontextmenü Zeile löschen anklicken
- Die Warnmeldung lesen, verstehen und bestätigen.
Nach mehr oder weniger kurzer Wartezeit werden die Datensätze gelöscht sein. Anschließend den Filter aus B1 aufheben und dann kontrollieren, ob der unterste Datensatz immer noch in Zeile 50.001 steht. Das wird nicht der Fall sein, es ist die Zeile 30.282. Damit ist die Aufgabe gelöst.
Lösung ohne Intelligente Tabelle, Funktion VERGLEICH()
Vorsicht Glatteis! Diesen Satz stelle ich hier an den Anfang, weil ich Sie erst einmal ein wenig aufs Glatteis führe. Diesen sehr verbreiteten Fehler sollten Sie einfach einmal selber machen, um daraus zu lernen. Aber erst einmal zu den notwendigen Vorbereitungen. Entweder laden Sie sich die Tabelle noch einmal hier vom Blog herunter oder Sie sorgen auf eine Ihnen genehme Art dafür, dass das Arbeitsblatt Alle Artikel wieder den ursprünglichen Zustand hat. Dazu müssen Sie in jedem Fall die Tabelle in einen normalen Bereich umwandeln. Das geht so:
- Rechtsklick in die Tabelle
- Tabelle | In Bereich konvertieren
- Bestätigen und eventuell die Hintergrundfarbe entfernen. A1 dann noch die Schriftfarbe auf schwarz setzen.
Als erstes sollten Sie des späteren Filterns wegen in B1 eine beliebige Überschrift einfügen. Und anschließend kommt in B2 die :
=VERGLEICH(A2; Löschen!A2:A19500)
… und es kommt ein Ergebnis heraus, welches kein Fehlerwert ist. Na gut, ziehen Sie nun die Formel einfach einmal bis B10 nach unten. OK, kein #Fehler! … Aber wonach soll dann gefiltert werden? Na gut, Einstieg in die Radikal-Kur. Bis Zeile 50 nach unten kopieren. Ah ja, jetzt kommen die ersten Fehlermeldungen. Vielleicht kann ja das als Indikator herangezogen werden? Nein, keineswegs. Sie werden die Diskrepanz rasch erkennen, wenn Sie die Formel ganz nach unten, bis zur letzten Datenzeile kopieren. Das geht ganz gut per Doppelklick auf das Ausfüllkästchen. Ab Zeile 19501 kommt nur noch #NV. Versuchen Sie doch einfach einmal, dem Fehler auf die Spur zu kommen!
Zugegeben, es sind mehrere Fehler in der Formel. Den einen sollten Sie erkennen, wenn Sie die Formeln in den Zeilen 2 bis 4 vergleichen. Der Bezug auf den zu durchsuchenden Bereich im Blatt Löschen ist relativ und nicht absolut. Darum sind die ganzen unteren Ergebnisse auch #NV. Wenn Sie die Formel bereits in B2 anpassen:
=VERGLEICH(A2; Löschen!A$2:A$19500)
und dann nach unten kopieren, dann sieht das Ergebnis etwas anders aus. Einige Fehlermeldungen sind noch vorhanden, aber längst nicht mehr in dem Ausmaß wie vorher. Aber es muss noch ein gravierender Fehler in der Formel sein. Der erste Wert, Zeile 3 müsste irgendwie so gekennzeichnet oder auswertbar sein, dass die Zeile gelöscht werden soll.
Sie werden auf die Lösung kommen, wenn Sie die VERGLEICH()-Formel einmal konsequent analysieren. Es gibt noch einen dritten, wenn auch optionalen Parameter. Und wenn Sie diesen auf 0 setzen, dann wird nach genau dem zu vergleichenden Wert gesucht. Hier noch einmal die neue Formel:
=VERGLEICH(A2; Löschen!A$2:A$19500; 0)
Bingo,alle zu löschenden Zeilen sind mit einem Fehlerwert #NV versehen. Bleibt nur noch die „Kleinigkeit” diese Zeilen zu filtern und dann zu löschen. Kurz und knapp: Mit dem AutoFilter geht das nicht. Dazu bedarf es des Erweiterten Filters. Damit werden alle Zeilen mit dem bewussten Fehlerwert gefiltert. – Wollen Sie dennoch den AutoFilter verwenden, dann bietet sich beispielsweise solch eine Formel an:
=ISTFEHLER(VERGLEICH(A2; Löschen!A$2:A$19500; 0))
wo ‑wie im ersten Beispiel- WAHR oder FALSCH zurück gegeben wird. Alternativ geht natürlich auch ISTNV() als Funktion, denn es soll ja speziell dieser Fehlertyp abgefragt werden.
Um nun möglichst komfortabel die Datensätze zu löschen, gehen Sie so vor:
- Klicken Sie auf den Zeilenkopf von A3, welches ja die erste sichtbare Zeile des Datenbereichs ist. Jetzt ist die komplette Zeile markiert.
- Nun StrgShift↓. Automatisch werden alle Zeilen bis Zeile 50001 markiert
- Rechtsklick und aus dem Kontextmenü Zeile löschen anklicken
- Die Warnmeldung lesen, verstehen und bestätigen.
Nach mehr oder weniger kurzer Wartezeit werden die Datensätze gelöscht sein. Anschließend den Filter aus B1 aufheben und dann kontrollieren, ob der unterste Datensatz immer noch in Zeile 50.001 steht. Das wird nicht der Fall sein, es ist die Zeile 30.282. Damit ist die Aufgabe gelöst.
Hinweis: Bei dieser Menge von Daten dauert die Berechnung der Tabelle nach beiden Methoden ziemlich lange. Meine Empfehlung: Schalten Sie ‑auf welchem Wege auch immer- die automatische Berechnung auf Manuell. Das finden Sie beispielsweise unter dem Menüpunkt Formeln | Berechnungsoptionen. Immer wenn Sie wollen, das die Anzeige aktualisiert wird, einfach F9 und die Berechnung des kompletten Blattes wird durchgeführt.
[NachObenLetzte Verweis=„ML: Datenabgleich (1)”]