Heute eine alte Technik namens Datenkonsolidierung. Wenn Sie Daten aus mehreren Arbeitsblättern oder mehreren Arbeitsmappen kombinieren müssen, kann Consolidation den Job bearbeiten, wenn Ihre Daten in einem bestimmten Format vorliegen. Erfahren Sie mehr über dieses alte Werkzeug.
In Excel gibt es zwei alte Konsolidierungswerkzeuge.
Um sie zu verstehen, sagen Sie, dass Sie drei Datensätze haben. Jeder hat Namen auf der linken Seite und Monate auf der Oberseite. Beachten Sie, dass die Namen unterschiedlich sind und jeder Datensatz eine andere Anzahl von Monaten enthält.
Sie möchten diese zu einem einzigen Datensatz kombinieren. Blättern Sie auf der Seite, um die beiden Methoden zu diskutieren.
Das erste Tool ist der Befehl Konsolidieren auf der Registerkarte Daten. Wählen Sie einen leeren Abschnitt der Arbeitsmappe, bevor Sie den Befehl starten. Verwenden Sie die Schaltfläche RefEdit, um auf jeden Ihrer Datensätze zu verweisen, und klicken Sie dann auf Hinzufügen. Wählen Sie unten links Top Row und Left Column.
Wenn Sie auf OK klicken, wird eine Obermenge aller drei Datensätze erstellt. Die erste Spalte enthält einen beliebigen Namen in einem der drei Datensätze. Zeile 1 enthält einen beliebigen Monat in einem beliebigen Datensatz.
Beachten Sie in der obigen Abbildung drei Belästigungen. Zelle A1 bleibt immer leer. Die Daten in A sind nicht sortiert. Wenn eine Person in einem Datensatz fehlte, bleiben die Zellen leer, anstatt mit 0 gefüllt zu werden.
Das Ausfüllen der Zelle A1 ist einfach genug. Beim Sortieren nach Namen wird Flash Fill verwendet, um den Nachnamen in Spalte N zu erhalten. So füllen Sie leere Zellen mit 0:
- Wählen Sie alle Zellen aus, die Nummern haben sollen: B2: M11.
- Wählen Sie Home, Suchen & Auswählen, Gehe zu Spezial.
- Wählen Sie Leerzeichen und klicken Sie dann auf OK. Sie werden mit allen ausgewählten leeren Zellen belassen.
-
Geben Sie 0 und dann Strg + Eingabetaste ein.
Gehe zu Special
Das Ergebnis: ein schön formatierter zusammenfassender Bericht.
Schau Video
- Konsolidieren ist eine alte Funktion in Excel
- Geben Sie mehrere Bereiche an, die konsolidiert werden sollen
- Verwenden Sie Beschriftungen in der oberen Zeile und in der linken Spalte
- Belästigungen: A1 ist immer leer, Spalte A ist nicht sortiert, Daten sind leer
- Gehen Sie zu Spezial, Leerzeichen, 0, Strg + Eingabetaste
- Konsolidieren kann auf externe Arbeitsmappen verweisen
Video-Transkript
Lernen Sie Excel aus dem Podcast, Folge 2046 - Arbeitsblätter konsolidieren!
Ich werde alle meine Tipps aus diesem Buch podcasten. Klicken Sie auf das "i" in der oberen rechten Ecke, um zur Wiedergabeliste zu gelangen!
Okay, ich habe einen großartigen, großartigen alten alten Trick. Ich habe hier drei Datensätze, Q1, Q2, Q3, alle haben eine ähnliche Form und sie haben Namen auf der linken Seite, Monate gehen über die Oberseite, aber nicht genau die gleiche Form. Q1 hat Jan-Feb-Mar, Q2 hat 5 Monate, ich denke, wir sind faul geworden und haben es Ende Juni nicht herausgefunden und jemand hat es schließlich Ende August getan, und dann hat Q4 4 Monate. Okay, der erste geht A: D, der zweite A: F, der dritte A: E. Verschiedene Namen, einige der Namen sind gleich, so wie Michael Seeley in allen vorhanden ist, aber andere Namen kommen und gehen. Okay, das ist eine erstaunliche Funktion, es gibt sie schon immer. Ich erinnere mich, dass ich dies bereits 1995 getan habe: Data, Consolidates!
Okay, wir werden die SUMME-Funktion verwenden, ich habe noch nie eine der anderen verwendet, aber ich denke, sie sind da. SUM-Funktion, das erste, was wir tun werden, ist, zu Q1 zurückzukehren und auf diesen Bereich, diese vier Spalten, zu zeigen, auf Hinzufügen zu klicken und dann zu Q2 zu gehen, diese Spalten auszuwählen, auf Hinzufügen zu klicken und dann Q4, diese Spalten auszuwählen . Okay, aktivieren Sie dieses Kontrollkästchen für Beschriftungen verwenden in der oberen Zeile und in der linken Spalte. Diese Schaltfläche Durchsuchen bedeutet, dass sich diese Datensätze in verschiedenen Arbeitsmappen befinden können! Erstellen Sie Links zu Quelldaten. Wir werden am Ende darüber sprechen. Wenn ich auf OK klicke, erhalten sie jeden Namen, der in einer der drei Listen enthalten ist, die Monate in einer der drei Listen, und wir haben jetzt diese erstaunliche Obermenge, in Ordnung, Ärger!
Dies ist eine großartige Funktion, aber hier sind die Dinge, die mich gerade gehackt haben. Sie geben mir nicht die Bezeichnung in A1, sie kümmern sich nicht darum, die Daten zu sortieren, und wenn jemand in der ersten keinen Datensatz hatte, geben sie mir Leerzeichen anstelle von Nullen. Okay, um die Lücken mit Nullen zu füllen, Home, Suchen und Auswählen, Gehe zu Spezial, wähle die Lücken aus, klicke auf OK, tippe eine Null ein, Strg + Eingabetaste füllt diese aus. Einfach genug, um die Daten, Daten, AZ und zu sortieren es wird die Daten sortieren, in Ordnung. Erstellen Sie Links, meine Güte, es funktioniert nie richtig, in Ordnung, erstellen Sie Links, damit Links erstellen funktioniert, muss es sich in einer externen Arbeitsmappe befinden. Okay, also klicke ich hier auf Durchsuchen. Ich habe eine Arbeitsmappe mit dem Namen OtherWorkbook erstellt. Die Daten befinden sich in A1: D7. Klicken Sie auf Hinzufügen, in Ordnung, und es gibt die erste. Die nächsten Daten sind in G1: L8, also werde ich OtherWorkbook durchsuchen, G1: L8,Klicken Sie auf Hinzufügen, in Ordnung. Jetzt habe ich zwei Verweise auf andere Arbeitsmappen: Obere Zeile, Linke Spalte, Links zu Quelldaten erstellen. In der Excel-Hilfe heißt es, dass Sie diese Bereiche nie mehr bearbeiten können, wenn Sie die Quelldaten erstellen verwenden. Klicken Sie erneut auf OK, und wir erhalten Folgendes.
Okay, als erstes sieht es so aus, als hätte es uns die Ergebnisse geliefert, es hat uns die Ergebnisse geliefert, aber hier gibt es eine zusätzliche Spalte B, und wir haben Gruppe und Gliederung. Und wenn wir zur Nummer 2 gehen, ah. Im Idealfall hätten Sie also eine Arbeitsmappe mit dem Namen Januar und eine andere Arbeitsmappe mit dem Namen Februar, und sie zeigt Ihnen hier den Januar, hier den Februar. Hier ist die Summe für Mike Seeley, dies sind Formeln, die auf diese Verkäufe hinweisen, und hier ist eine Summe dieser beiden, okay, es ist seltsam.
Wenn Sie dies tatsächlich die ganze Zeit verwenden, möchte ich in den YouTube-Kommentaren von Ihnen hören. Ich bin sicher, es gibt einen einfacheren Weg, dies für mich zu tun. Ich habe es in meinem Leben noch nie getan, also schon einmal vor heute und dann heute. Nur so konnte ich es erklären, okay. Konsolidieren Sie jedoch, wenn wir Blätter aus der aktuellen Arbeitsmappe konsolidieren, ein großartiger Trick. Morgen werden wir den Pivot-Tabellenbereich für mehrere Konsolidierungen vergleichen, aber all diese Tricks sind im Buch enthalten. Klicken Sie auf das „i“ in der oberen rechten Ecke, um zu diesem Buch zu gelangen.
Konsolidieren, alte, alte Funktion in Excel, Sie geben mehrere Bereiche an, die konsolidiert werden sollen. Ich aktiviere immer das Kontrollkästchen für die obere Zeile und die linke Spalte. Die Ergebnisse sind großartig, aber A1 ist leer, Spalte A ist nicht sortiert und die Daten enthalten Leerzeichen. Verwenden Sie Gehe zu speziellen Leerzeichen, geben Sie eine 0 ein, Strg + Eingabetaste, um diese Lücken zu füllen. Und dann kann das letzte Beispiel dort, Konsolidieren, auf externe Arbeitsmappen verweisen, nützlich sein!
Okay hey, ich möchte mich bei dir für deinen Besuch bedanken, wir sehen uns beim nächsten Mal für einen weiteren Netcast von!
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2046.xlsm
