Inhalte
Wir haben zwei Tabellen (z. B. die alte und die neue Version der Preisliste), die wir vergleichen müssen, um die Unterschiede schnell zu finden:
Es ist sofort klar, dass der neuen Preisliste etwas hinzugefügt wurde (Datteln, Knoblauch …), etwas verschwunden ist (Brombeeren, Himbeeren …), sich die Preise für einige Waren geändert haben (Feigen, Melonen …). All diese Änderungen müssen Sie schnell finden und anzeigen.
Für jede Aufgabe in Excel gibt es fast immer mehr als eine Lösung (normalerweise 4-5). Für unser Problem können viele verschiedene Ansätze verwendet werden:
- Funktion VPR (SVERWEIS) — Produktnamen aus der neuen Preisliste in der alten suchen und den alten Preis neben dem neuen anzeigen lassen und dann die Unterschiede erkennen
- Führen Sie zwei Listen zu einer zusammen und erstellen Sie dann darauf basierend eine Pivot-Tabelle, in der die Unterschiede deutlich sichtbar sind
- Verwenden Sie das Power Query-Add-In für Excel
Nehmen wir sie alle der Reihe nach.
Methode 1. Vergleichen von Tabellen mit der SVERWEIS-Funktion
Wenn Sie mit dieser wunderbaren Funktion noch nicht vertraut sind, dann schauen Sie zuerst hier und lesen oder sehen Sie sich ein Video-Tutorial dazu an – sparen Sie sich ein paar Jahre Leben.
In der Regel wird diese Funktion verwendet, um Daten aus einer Tabelle in eine andere zu ziehen, indem ein gemeinsamer Parameter abgeglichen wird. In diesem Fall verwenden wir es, um die alten Preise in den neuen Preis zu verschieben:
Diejenigen Produkte, bei denen sich der #N/A-Fehler herausstellte, sind nicht in der alten Liste, dh wurden hinzugefügt. Auch Preisänderungen sind deutlich sichtbar.
Vorteile diese Methode: einfach und klar, „Klassiker des Genres“, wie man so schön sagt. Funktioniert in jeder Version von Excel.
Nachteile ist auch da. Um nach Produkten zu suchen, die der neuen Preisliste hinzugefügt wurden, müssen Sie den gleichen Vorgang in umgekehrter Richtung durchführen, dh neue Preise mit Hilfe von VLOOKUP zum alten Preis ziehen. Wenn sich die Tabellengrößen morgen ändern, müssen die Formeln angepasst werden. Nun, und auf wirklich großen Tabellen (> 100 Zeilen) wird all dieses Glück anständig langsamer.
Methode 2: Vergleichen von Tabellen mit einem Pivot
Kopieren wir unsere Tabellen untereinander und fügen eine Spalte mit dem Namen der Preisliste hinzu, damit Sie später nachvollziehen können, aus welcher Liste welche Zeile stammt:
Basierend auf der erstellten Tabelle erstellen wir nun eine Zusammenfassung durch Einfügen – PivotTable (Einfügen – Pivot-Tabelle). Lassen Sie uns ein Feld werfen Produkt zum Bereich Linien, Feld PREISLISTE zu Spaltenbereich und Feld Цena ins Sortiment:
Wie Sie sehen, erstellt die Pivot-Tabelle automatisch eine Gesamtliste aller Produkte aus der alten und neuen Preisliste (keine Wiederholungen!) und sortiert die Produkte alphabetisch. Sie können die hinzugefügten Produkte (sie haben nicht den alten Preis), die entfernten Produkte (sie haben nicht den neuen Preis) und Preisänderungen, falls vorhanden, deutlich sehen.
Gesamtsummen in einer solchen Tabelle sind nicht sinnvoll und können auf der Registerkarte deaktiviert werden Konstruktor – Gesamtsummen – Für Zeilen und Spalten deaktivieren (Design – Gesamtsummen).
Ändern sich die Preise (aber nicht die Warenmenge!), dann genügt es, die erstellte Zusammenfassung einfach per Rechtsklick zu aktualisieren – Inspiration.
Vorteile: Dieser Ansatz ist bei großen Tabellen um eine Größenordnung schneller als SVERWEIS.
Nachteile: Sie müssen die Daten manuell untereinander kopieren und eine Spalte mit dem Namen der Preisliste hinzufügen. Ändert sich die Größe der Tische, dann müssen Sie alles neu machen.
Methode 3: Vergleichen von Tabellen mit Power Query
Power Query ist ein kostenloses Add-In für Microsoft Excel, mit dem Sie Daten aus nahezu jeder Quelle in Excel laden und diese Daten dann beliebig transformieren können. In Excel 2016 ist dieses Add-In bereits standardmäßig auf der Registerkarte integriert Datum (Daten), und für Excel 2010-2013 müssen Sie es separat von der Microsoft-Website herunterladen und installieren – erhalten Sie eine neue Registerkarte Power Query.
Bevor unsere Preislisten in Power Query geladen werden, müssen sie zunächst in Smart Tables umgewandelt werden. Wählen Sie dazu den Bereich mit Daten aus und drücken Sie die Kombination auf der Tastatur Ctrl+T oder wählen Sie die Registerkarte im Menüband aus Startseite – Als Tabelle formatieren (Home — Als Tabelle formatieren). Auf der Registerkarte können die Namen der erstellten Tabellen korrigiert werden Bauherr (Ich werde den Standard verlassen Tabelle 1 и Tabelle 2, die standardmäßig abgerufen werden).
Laden Sie den alten Preis in Power Query über die Schaltfläche Aus Tabelle/Bereich (Aus Tabelle/Bereich) aus der Registerkarte Datum (Datum) oder aus der Registerkarte Power Query (abhängig von der Excel-Version). Nach dem Laden kehren wir mit dem Befehl von Power Query zu Excel zurück Schließen und laden – Schließen und laden… (Schließen & Laden — Schließen & Laden nach…):
… und im erscheinenden Fenster dann auswählen Stellen Sie einfach eine Verbindung her (Nur Verbindung).
Wiederholen Sie dasselbe mit der neuen Preisliste.
Lassen Sie uns nun eine dritte Abfrage erstellen, die die Daten der beiden vorherigen kombiniert und vergleicht. Wählen Sie dazu in Excel auf der Registerkarte aus Daten – Daten abrufen – Anfragen kombinieren – Kombinieren (Daten — Daten abrufen — Abfragen zusammenführen — Zusammenführen) oder drücken Sie die Taste Kombinieren (Verschmelzen) Tab Power Query.
Wählen Sie im Join-Fenster unsere Tabellen in den Dropdown-Listen aus, wählen Sie die Spalten mit den Namen der darin enthaltenen Waren aus und legen Sie unten die Join-Methode fest – Komplett extern (Vollständig außen):
Nach dem Klicken auf OK Es sollte eine Tabelle mit drei Spalten erscheinen, in der Sie in der dritten Spalte den Inhalt verschachtelter Tabellen mit dem Doppelpfeil in der Kopfzeile erweitern müssen:
Als Ergebnis erhalten wir die Zusammenführung der Daten aus beiden Tabellen:
Besser ist es natürlich, die Spaltennamen in der Kopfzeile per Doppelklick auf verständlichere Namen umzubenennen:
Und jetzt das Interessanteste. Gehen Sie zur Registerkarte Spalte hinzufügen (Spalte hinzufügen) und klicken Sie auf die Schaltfläche Bedingte Spalte (Bedingte Spalte). Geben Sie dann in dem sich öffnenden Fenster mehrere Testbedingungen mit den entsprechenden Ausgabewerten ein:
Es bleibt zu klicken OK und laden Sie den resultierenden Bericht mit derselben Schaltfläche in Excel hoch schließen und herunterladen (Schließen & Laden) Tab Startseite (Home):
Schönheit.
Sollten sich in Zukunft Änderungen in den Preislisten ergeben (Zeilen werden hinzugefügt oder gelöscht, Preise ändern sich usw.), reicht es außerdem aus, unsere Anfragen mit einem Tastaturkürzel zu aktualisieren Ctrl+Andere+F5 oder per Taste Alle erfrischen (Alle erfrischen) Tab Datum (Datum).
Vorteile: Die vielleicht schönste und bequemste Art überhaupt. Funktioniert intelligent mit großen Tabellen. Erfordert keine manuellen Bearbeitungen beim Ändern der Tabellengröße.
Nachteile: Erfordert die Installation des Power Query-Add-Ins (in Excel 2010–2013) oder von Excel 2016. Die Spaltennamen in den Quelldaten dürfen nicht geändert werden, sonst erhalten wir den Fehler „Spalte so und so wurde nicht gefunden!“. beim Versuch, die Abfrage zu aktualisieren.
- So sammeln Sie Daten aus allen Excel-Dateien in einem bestimmten Ordner mit Power Query
- So finden Sie Übereinstimmungen zwischen zwei Listen in Excel
- Zwei Listen ohne Duplikate zusammenführen