Bill stellte diese Woche die Frage nach redundanten Excel-Daten.
Ich erstelle eine monatliche Transaktionsliste in Excel. Am Ende des Monats muss ich die redundanten Daten entfernen und eine Summe nach Kontocode erstellen. Jeder Kontocode kann mehrmals vorkommen. Anschließend beschrieb Bill seine aktuelle Excel-Methode, die der folgenden Methode 1 ähnelt, um eine eindeutige Liste von Kontocodes zu erstellen. Er plant, eine Matrix von CSE-Formeln zu verwenden, um die Gesamtsummen zu erhalten. Er fragt, gibt es eine einfachere Möglichkeit, zu einer eindeutigen Liste von Kontocodes mit Summen für jedes Konto zu gelangen?
Dies ist eine perfekte Urlaubsfrage. Als Lotus-Benutzer seit 15 Jahren erkenne ich Bills Methode als die klassische Methode zur "schnellen und schmutzigen" Datenmanipulation aus den guten alten Zeiten von Lotus Release 2.1 an. Dies ist eine Zeit, um unseren Segen zu zählen. Wenn Sie über diese Frage nachdenken, stellen Sie fest, dass die Mitarbeiter von Microsoft uns im Laufe der Jahre eine Reihe von Tools zur Verfügung gestellt haben. Wenn Sie Excel 97 verwenden, gibt es mindestens fünf Methoden für diese Aufgabe, die alle viel einfacher sind als die von Bill beschriebene klassische Methode. Ich werde diese Woche ein Tutorial zu den fünf Methoden anbieten.
Mein vereinfachter Datensatz enthält Kontonummern in Spalte A und Beträge in Spalte B. Die Daten laufen von A2: B100. Es ist am Anfang nicht sortiert.
Methode 1
Verwenden Sie kreative If-Anweisungen in Verbindung mit Einfügen spezieller Werte, um die Antwort zu finden.
Angesichts der neueren Tools von Excel empfehle ich diese Methode nicht mehr. Ich habe das oft benutzt, bevor bessere Dinge kamen, und es gibt immer noch Situationen, in denen es nützlich ist. Mein alternativer Name hierfür ist die Methode "The-Lotus-123-When-You-Were-Not-In-The-Mood-To-Use- @ DSUM". Hier sind die Schritte.
- Sortieren Sie die Daten nach Spalte A.
- Erfinden Sie eine Formel in Spalte C, die eine laufende Summe nach Konto enthält. Zelle C2 ist
=IF(A2=A1,C1+B2,B2). - Erfinde eine Formel in D, die den letzten Eintrag für ein bestimmtes Konto identifiziert. Zelle D2 ist
=IF(A2=A3,FALSE,TRUE). - Kopieren Sie C2: D2 in alle Ihre Zeilen.
- Kopie C2: D100. Führen Sie einen Edit - PasteSpecial - Values zurück auf C2: D100 aus, um die Formeln in Werte zu ändern.
- Sortieren nach Spalte D absteigend.
- Für die Zeilen mit TRUE in Spalte D haben Sie eine eindeutige Liste der Kontonummern in A und die endgültige laufende Summe in C.
Vorteile: Es ist schnell. Alles, was Sie brauchen, ist ein ausgeprägtes Gespür für das Schreiben von IF-Anweisungen.
Nachteile: Es gibt bessere Möglichkeiten.
Methode 2
Verwenden Sie Datenfilter - Erweiterter Filter, um die Liste der eindeutigen Konten abzurufen.
Bills Frage war wirklich, wie man eine eindeutige Liste von Kontonummern erhält, damit er CSE-Formeln verwenden kann, um die Gesamtsummen zu erhalten. Dies ist eine Methode zum Abrufen einer Liste der eindeutigen Kontonummern.
- Markieren Sie A1: A100
- Wählen Sie im Menü Daten, Filter, Erweiterter Filter
- Klicken Sie auf das Optionsfeld für "An einen anderen Ort kopieren".
- Aktivieren Sie das Kontrollkästchen für "Nur eindeutige Datensätze".
- Wählen Sie einen leeren Abschnitt des Arbeitsblatts aus, in dem die eindeutige Liste angezeigt werden soll. Geben Sie dies in das Feld "Kopieren nach:" ein. (Beachten Sie, dass dieses Feld ausgegraut ist, bis Sie "An einen anderen Ort kopieren" auswählen.
- OK klicken. Die eindeutigen Kontonummern werden in F1 angezeigt.
- Geben Sie alle Downline-Manipulationen, Array-Formeln usw. ein, um Ihre Ergebnisse zu erhalten.
Vorteile: Schneller als Methode 1. Keine Sortierung erforderlich.
Nachteile: Die danach erforderlichen CSE-Formeln lassen Ihren Kopf drehen.
Methode 3
Verwenden Sie Data Consolidate.
Meine Lebensqualität verbesserte sich, als Excel Data Consolidate anbot. Das war GROSS! Das Einrichten dauert 30 Sekunden, für DSUMs und andere Methoden bedeutet dies jedoch den Tod. Ihre Kontonummer muss sich links von den numerischen Feldern befinden, die Sie summieren möchten. Über jeder Spalte müssen Überschriften stehen. Sie müssen dem rechteckigen Zellenblock einen Bereichsnamen zuweisen, der die Kontonummern in der linken Spalte und die Überschriften oben enthält. In diesem Fall ist dieser Bereich A1: B100.
- Markieren Sie A1: B100
- Weisen Sie diesem Bereich einen Bereichsnamen zu, indem Sie in das Namensfeld (links neben der Formelleiste) klicken und einen Namen wie "TotalMe" eingeben. (Alternativ können Sie Insert - Name verwenden.)
- Platzieren Sie den Zellenzeiger in einem leeren Bereich des Arbeitsblatts.
- Daten auswählen - Konsolidieren
- Geben Sie im Referenzfeld den Bereichsnamen (TotalMe) ein.
- Aktivieren Sie im Abschnitt Beschriftungen verwenden in sowohl die obere Zeile als auch die linke Spalte.
- OK klicken
Vorteile: Dies ist meine Lieblingsmethode. Keine Sortierung erforderlich. Die Verknüpfung ist alt-D N (Rangenname) alt-T alt-L eingeben. Es ist leicht skalierbar. Wenn Ihr Bereich 12 monatliche Spalten enthält, enthält die Antwort Summen für jeden Monat.
Nachteile: Wenn Sie eine andere Datenkonsolidierung auf demselben Blatt durchführen, müssen Sie den alten Bereichsnamen aus dem Feld Alle Referenzen mit der Schaltfläche Löschen löschen. Die Kontonummer muss links von Ihren numerischen Daten stehen. Es ist etwas langsamer als Pivot-Tabellen, was sich bei Datensätzen mit mehr als 10.000 Datensätzen bemerkbar macht.
Methode 4
Verwenden Sie Zwischensummen für Daten.
Dies ist eine coole Funktion. Da es seltsam ist, mit den resultierenden Daten zu arbeiten, verwende ich sie seltener als Data Consolidate.
- Sortieren nach Spalte A aufsteigend.
- Wählen Sie eine beliebige Zelle im Datenbereich aus.
- Wählen Sie im Menü Daten - Zwischensummen.
- Standardmäßig bietet Excel die Zwischensumme der letzten Spalte Ihrer Daten an. Dies funktioniert in diesem Beispiel, aber Sie müssen häufig durch die Liste "Zwischensumme hinzufügen zu:" scrollen, um die richtigen Felder auszuwählen.
- OK klicken. Excel fügt bei jeder Änderung der Kontonummer eine neue Zeile mit einer Gesamtsumme ein.
Nachdem Sie die Zwischensummen eingegeben haben, wird unter dem Namensfeld eine kleine 123 angezeigt. Klicken Sie auf 2, um nur eine Zeile pro Konto mit den Gesamtsummen anzuzeigen. Unter Excel-Zwischensummen kopieren finden Sie eine Erläuterung der speziellen Schritte, die zum Kopieren dieser an einen neuen Speicherort erforderlich sind. Klicken Sie auf 3, um alle Zeilen anzuzeigen. Vorteile: Coole Funktion. Ideal zum Drucken von Berichten mit Summen und Seitenumbrüchen nach jedem Abschnitt.
Nachteile: Die Daten müssen zuerst sortiert werden. Langsam für viele Daten. Sie müssen Goto-Special-VisbileCellsOnly verwenden, um die Gesamtsummen an anderer Stelle abzurufen. Sie müssen Data-Subtotals-RemoveAll verwenden, um zu Ihren ursprünglichen Daten zurückzukehren.
Methode 5
Verwenden Sie eine Pivot-Tabelle.
Pivot-Tische sind die vielseitigsten von allen. Ihre Daten müssen nicht sortiert werden. Die numerischen Spalten können sich links oder rechts von der Kontonummer befinden. Sie können die Kontonummern leicht nach unten oder über die Seite verschieben.
- Wählen Sie eine beliebige Zelle im Datenbereich aus.
- Wählen Sie im Menü Daten - PivotTable.
- Übernehmen Sie die Standardeinstellungen in Schritt 1
- Stellen Sie sicher, dass der Datenbereich in Schritt 2 korrekt ist (normalerweise).
- Wenn Sie Excel 2000 verwenden, klicken Sie in Schritt 3 auf die Schaltfläche Layout. Benutzer von Excel 95 und 97 wechseln automatisch als Schritt 3 zum Layout.
- Ziehen Sie im Layoutdialog die Schaltfläche Konto von der rechten Seite des Dialogfelds und legen Sie sie im Zeilenbereich ab.
- Ziehen Sie die Schaltfläche Betrag von der rechten Seite des Dialogfelds und legen Sie sie im Datenbereich ab.
- Excel 2000-Benutzer klicken auf OK, Excel 95/97-Benutzer auf Weiter.
- Geben Sie an, ob die Ergebnisse in einem neuen Blatt oder in einem bestimmten Abschnitt eines vorhandenen Blattes angezeigt werden sollen. Weitere Informationen zu Pivot-Tabellen finden Sie unter Erweiterte Tricks für Excel-Pivot-Tabellen.
- Pivot-Tabellen bieten eine unglaubliche Funktionalität und machen diese Aufgabe zum Kinderspiel. Um die Ergebnisse der Pivot-Tabelle zu kopieren, müssen Sie Edit-PasteSpecial-Values ausführen. Andernfalls können Sie in Excel keine Zeilen usw. einfügen.
Vorteile: Schnell, flexibel, leistungsstark. Schnell, auch für viele Daten.
Nachteile: Etwas einschüchternd.
Bill hat jetzt vier neue Methoden, um die redundanten Daten zu eliminieren. Obwohl diese Methoden seit jeher nicht mehr verfügbar waren, waren sowohl Lotus als auch Excel großartige Innovatoren, um uns schnellere Wege zur Erfüllung dieser alltäglichen Aufgabe zu bieten.
