EINZIGARTIG Von nicht benachbarten Spalten - Excel-Tipps

Inhaltsverzeichnis

Schau Video

Neulich wollte ich eine eindeutige Kombination aus zwei nicht benachbarten Spalten in Excel erstellen. Normalerweise mache ich das mit Duplikaten entfernen oder mit erweitertem Filter, aber ich dachte, ich würde versuchen, es mit der neuen UNIQUE-Funktion zu tun, die 2019 in Office 365 verfügbar ist. Ich habe mehrere Ideen ausprobiert und keine würde funktionieren. Also ging ich zum Meister der dynamischen Arrays, Joe McDaid, um Hilfe. Die Antwort ist ziemlich cool und ich bin sicher, dass ich sie vergessen werde, also dokumentiere ich sie für Sie und für mich. Ich bin sicher, in zwei Jahren werde ich Google zeigen, wie das geht, und feststellen: "Oh, schau! Ich bin derjenige, der den Artikel darüber geschrieben hat!"

Bevor Sie zur UNIQUE-Funktion gelangen, schauen Sie sich an, was ich versuche. Ich möchte jede eindeutige Kombination aus Vertriebsmitarbeiter aus Spalte B und Produkt aus Spalte C. Normalerweise würde ich die folgenden Schritte ausführen:

Kopieren Sie die Überschriften von B1 und D1 in einen leeren Bereich des Arbeitsblatts
Wählen Sie in B1 Daten, Filter, Erweitert
Wählen Sie im Dialogfeld "Erweiterter Filter" die Option "An einen neuen Speicherort kopieren"
Geben Sie die Überschriften aus Schritt 1 als Ausgabebereich an
Aktivieren Sie das Kontrollkästchen Nur eindeutige Werte
OK klicken

Kopieren Sie die beiden Überschriften in einen leeren Bereich, der zum Ausgabebereich wird

Das Ergebnis ist jede eindeutige Kombination der beiden Felder. Beachten Sie, dass der erweiterte Filter die Elemente nicht sortiert - sie werden in der ursprünglichen Reihenfolge angezeigt.

Das Abrufen einer eindeutigen Liste ist eine meiner Lieblingsanwendungen für den erweiterten Filter

Dieser Vorgang wurde in Excel 2010 dank des Befehls "Duplikate entfernen" auf der Registerkarte "Daten" der Multifunktionsleiste einfacher. Folge diesen Schritten:

Wählen Sie B1: D227 und Strg + C zum Kopieren
Einfügen in einen leeren Abschnitt des Arbeitsblatts.

Erstellen Sie eine Kopie der Daten, da das Entfernen von Duplikaten destruktiv ist
Wählen Sie Daten, Duplikate entfernen
Deaktivieren Sie im Dialogfeld "Duplikate entfernen" die Option "Datum". Dies weist Excel an, nur Rep und Produkt zu betrachten.
OK klicken

Weisen Sie Duplikate entfernen an, nur Wiederholung und Datum zu berücksichtigen

Die Ergebnisse sind nahezu perfekt - Sie müssen nur die Spalte Datum löschen.

Löschen Sie die zusätzliche Spalte

Die Frage: Gibt es eine Möglichkeit, dass die UNIQUE-Funktion nur die Spalten B & D betrachtet? (Wenn Sie die neue UNIQUE-Funktion noch nicht gesehen haben, lesen Sie: UNIQUE-Funktion in Excel.)

Wenn =UNIQUE(B2:D227)Sie danach fragen , erhalten Sie jede einzigartige Kombination aus Repräsentant, Datum und Produkt, nach der wir nicht suchen.

Wie können wir zwei nicht benachbarte Spalten an die UNIQUE-Funktion übergeben?

Als im September dynamische Arrays eingeführt wurden, sagte ich, wir müssten uns nie mehr um die Komplexität der Formeln Strg + Umschalt + Eingabetaste kümmern. Um dieses Problem zu lösen, verwenden Sie ein Konzept namens Heben. Hoffentlich haben Sie jetzt mein E-Book "Excel Dynamic Arrays Straight To The Point" heruntergeladen. Auf den Seiten 31-33 finden Sie eine vollständige Erklärung zum Heben.

In meinem Buch finden Sie eine vollständige Erklärung zum Heben (und später, wenn Sie die Ergebnisse sortieren, zum paarweisen Heben).

Nehmen Sie eine Excel-Funktion, die einen einzelnen Wert erwartet. Zum Beispiel =CHOOSE(Z1,"Apple","Banana")würde entweder Apple oder Banana zurückgegeben, je nachdem, ob Z1 1 (für Apple) oder 2 (für Banane) enthält. Die CHOOSE-Funktion erwartet als erstes Argument einen Skalar.

Stattdessen übergeben Sie eine Array-Konstante von (1,2) als erstes Argument an CHOOSE. Excel führt den Hebevorgang durch und berechnet CHOOSE zweimal. Für den Wert 1 möchten Sie die Vertriebsmitarbeiter in B2: B227. Für den Wert 2 möchten Sie die Produkte in D2: D227.

Sagen Sie CHOOSE, dass sie zwei Antworten zurückgeben sollen

Normalerweise hätte in altem Excel eine implizite Schnittmenge die Ergebnisse vermasselt. Da Excel nun Ergebnisse auf viele Zellen übertragen kann, gibt die obige Formel erfolgreich ein Array aller Antworten in B und D zurück:

Erfolg! Von hier geht es bergab

Ich habe das Gefühl, ich würde Ihre Intelligenz beleidigen, um den Rest des Artikels zu schreiben, denn von hier aus ist es super einfach.

Wickeln Sie die Formel aus dem vorherigen Screenshot in UNIQUE ein und Sie erhalten nur die einzigartigen Kombinationen von Vertriebsmitarbeiter und Produkt mit =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Immer noch nicht sortiert

Um Ihr Verständnis zu überprüfen, versuchen Sie, die obige Formel zu ändern, um alle eindeutigen Kombinationen von drei Spalten zurückzugeben: Vertriebsmitarbeiter, Produkt, Farbe.

Ändern Sie zunächst die Array-Konstante in Bezug auf (1,2,3).

Fügen Sie dann CHOOSE ein viertes Argument hinzu, um die Farbe von E2: E227: zurückzugeben =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Geben Sie die eindeutige Kombination aus drei Spalten zurück

Es wäre schön, diese Ergebnisse zu sortieren, also wenden wir uns dem Sortieren mit einer Formel mit SORT und SORTBY zu.

Normalerweise wäre die Funktion, nach der aufsteigenden ersten Spalte zu sortieren, =SORT(Array)oder =SORT(Array,1,1).

Um nach drei Spalten zu sortieren, müssen Sie paarweise anheben =SORT(Array,(1,2,3),(1,1,1)). Wenn Sie in dieser Formel zum zweiten Argument von SORT gelangen, möchte Excel wissen, nach welcher Spalte sortiert werden soll. Senden Sie anstelle eines einzelnen Werts drei Spalten innerhalb einer Array-Konstante: (1,2,3). Wenn Sie zum dritten Argument gelangen, in dem 1 für Aufsteigend oder -1 für Absteigend angegeben ist, senden Sie eine Array-Konstante mit drei Einsen, um Aufsteigend, Aufsteigend, Aufsteigend anzuzeigen. Der folgende Screenshot zeigt =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Weitere Informationen zum paarweisen Anheben finden Sie auf Seite 34 von Excel Dynamic Arrays Straight to the Point.

Zumindest bis Ende 2018 können Sie das Excel Dynamic Arrays-Buch kostenlos über den Link unten auf dieser Seite herunterladen.

Ich bin ermutigt festzustellen, dass die Antwort auf die heutige Frage etwas kompliziert ist. Als Dynamic Arrays herauskamen, dachte ich sofort an all die erstaunlichen Formeln, die Aladin Akyurek und andere im Message Board veröffentlicht hatten, und daran, wie diese Formeln im neuen Excel viel einfacher werden würden. Das heutige Beispiel zeigt jedoch, dass Formelgenies immer noch neue Methoden zur Verwendung der dynamischen Arrays entwickeln müssen.