Grundlegende Excel-Funktionen eignen sich gut für einfache Berechnungen, werden aber bei komplexen Datenanalysen schnell kompliziert. Sie erhalten verschachtelte Formeln, die schwer lesbar sind, mehrere Hilfsspalten, die Ihre Tabelle überladen, und Formeln, die bei Datenänderungen nicht mehr funktionieren. Hier kommen Matrixformeln in Excel ins Spiel.
Mit Arrayformeln können Sie Berechnungen über ganze Datenbereiche in einer einzigen Formel durchführen. Daher können Sie Führen Sie blitzschnelle Suchvorgänge durch, filtern und sortieren Sie mit einem leistungsstarken Ausdruck, anstatt separate Formeln für jede Zeile oder Spalte zu schreiben. Das ist bei Excel nichts Neues, aber manche Leute halten an alten Vorgehensweisen fest, obwohl diese Funktionen ihre Arbeit einfacher und effizienter machen können.
Schnelle Links
5. XLOOKUP
Übertrifft SVERWEIS jedes Mal.
XLOOKUP ist die Nachschlagefunktion, die es von Anfang an hätte geben sollen. Im Gegensatz zu SVERWEIS, das Sie zum Zählen von Spalten zwingt und nur nach rechts sucht, funktioniert XLOOKUP in jede Richtung und verwendet tatsächliche Spaltenreferenzen. Die Syntax lautet wie folgt:
=XLOOKUP(Suchwert, Sucharray, Rückgabearray, [wenn_nicht_gefunden], [Übereinstimmungsmodus], [Suchmodus])
Die einzelnen Parameter haben folgende Bedeutung:
- Lookup-Wert: Der spezifische Wert, nach dem Sie suchen. Dies kann eine Teilenummer, ein Produktcode oder eine beliebige Kennung in Ihrem Datensatz sein.
- lookup_array: Der Bereich, in dem Excel sucht Lookup-Wert Ihre. Dies ist normalerweise eine einzelne Spalte oder Zeile, die Ihre Suchkriterien enthält.
- Rückgabearray: Der Bereich, der die Werte enthält, die Sie abrufen möchten. Dies kann eine einzelne Spalte, mehrere Spalten oder sogar ein ganzer Tabellenabschnitt sein.
- if_not_found (optional): Benutzerdefinierter Text oder Wert, der angezeigt wird, wenn keine Übereinstimmung gefunden wird. Dadurch werden lästige #N/A-Fehler vermieden und stattdessen „Nicht gefunden“ oder „Teilenummer prüfen“ angezeigt.
- match_mode (optional): Steuert den Übereinstimmungstyp. Verwenden Sie 0 für exakte Übereinstimmung (Standard), -1 für die nächste exakte oder kleinere Übereinstimmung, 1 für die nächste exakte oder größere Übereinstimmung und 2 für Platzhalterübereinstimmung.
- Suchmodus (optional): Gibt die Suchrichtung an. Verwenden Sie 1 für eine Suche vom ersten zum letzten (Standard), -1 für eine Suche vom letzten zum ersten und 2 für eine binäre Suche in sortierten Daten.
Nehmen wir als Beispiel eine mechanische Inventartabelle. Die folgende Formel sucht innerhalb eines Bereichs von Teile-IDs nach der Teilenummer „BRG-002“ und gibt die entsprechenden Daten zurück. Ist das Teil nicht vorhanden, wird anstelle einer Fehlermeldung „Teil nicht gefunden“ angezeigt.
=XLOOKUP("BRG-002", A:A, A:H, "Teil nicht gefunden")
XLOOKUP ermöglicht Ihnen, Daten aus verschiedenen Spalten zu extrahieren, ohne die umständlichen Spaltenberechnungen, die in VLOOKUP zu finden sind, und ist damit eines der wichtigsten Excel-Funktionen zum schnellen Auffinden von Daten.
4. SUMMENPRODUKT
Kraftwerk für bedingte Berechnungen
SUMPRODUCT addiert nicht nur Zahlen, sondern multipliziert auch Matrizen und summiert die Ergebnisse. Dies macht es nützlich für komplexe bedingte Berechnungen, die mehrere Hilfsspalten erfordern.
Es hat die folgende Formel:
=SUMMENPRODUKT(Array1, [Array2], [Array3], ...)
Hier, array1 Es handelt sich um den ersten zu multiplizierenden Wertebereich – normalerweise Ihre primäre Datenspalte, beispielsweise Mengen oder Kosten. array2 Es handelt sich um einen optionalen zweiten Bereich für die Multiplikation, der häufig Kriterien oder bedingte Logik mit Vergleichsoperatoren enthält.
Sie werden nützlicher, wenn wir logische Operatoren innerhalb von Arrays verwenden. Wenn wir beispielsweise Bedingungen wie (Lieferant="Siemens") eingeben, konvertiert Excel WAHR/FALSCH-Ergebnisse in 1/0 und ermöglicht so Berechnungen.
Mit der folgenden Formel lässt sich beispielsweise der Gesamtbestandswert für Teile berechnen, die ausschließlich von Siemens geliefert werden. Die Formel multipliziert die Mengen mit den Stückkosten, allerdings nur für Zeilen, in denen der Lieferant die Kriterien erfüllt.
=SUMPRODUCT(D2:D100*H2:H100*(G2:G100="Siemens"))
In ähnlicher Weise lassen sich mit der folgenden Formel die Gesamtkosten eines Lagerbestands mit ausreichender Verfügbarkeit ermitteln:
=SUMPRODUCT((C2:C100="Bearings")*(D2:D100>=15)*H2:H100)
Zwei Bedingungen gelten gleichzeitig: Die Kategorie muss „Lager“ sein und der Lagerbestand muss mindestens 15 Einheiten betragen, damit wir Lagerkategorien mit ausreichender Lagerabdeckung identifizieren können.
Im Gegensatz zu herkömmlichen SUM-Funktionen mit mehreren Kriterien erfordert SUMPRODUCT keine komplexen verschachtelten Strukturen, da es mehrere Bedingungen in einer einzigen, lesbaren Formel verarbeitet. SUM-Funktionen in Excel, Wie SUMIF und SUMIFS eignen sie sich hervorragend für einfache bedingte Summationen, aber die Funktion SUMPRODUCT ist hervorragend geeignet, wenn Sie Werte vor der Summierung multiplizieren oder komplexere logische Operationen verarbeiten müssen.
3. FILTER
Vereinfacht die dynamische Datenextraktion
FILTER extrahiert Zeilen aus Ihrem Datensatz basierend auf den von Ihnen angegebenen Bedingungen. Im Gegensatz zur manuellen Filterung generiert diese Funktion dynamische Ergebnisse, die automatisch aktualisiert werden, wenn sich die Quelldaten ändern. Die FILTER-Syntax lautet wie folgt:
=FILTER(Array, Einschließen, [wenn_leer])
Jeder Eingang steuert Folgendes:
- Array (Bereich): Der gesamte Datenbereich, den Sie filtern möchten. Dies umfasst alle Spalten, die in Ihren Ergebnissen enthalten sein sollen, nicht nur die Kriterienspalte.
- enthalten: Logische Bedingung, die angibt, welche Zeilen zurückgegeben werden sollen – verwendet Vergleichsoperatoren, um TRUE/FALSE-Arrays für jede Zeile zu erstellen.
- if_empty (optional): Zeigt eine benutzerdefinierte Meldung an, wenn keine Zeile Ihren Kriterien entspricht. Verhindert #CALC!-Fehler und zeigt aussagekräftigen Text wie „Keine passenden Ergebnisse gefunden“ an.
Die Funktion wertet Ihre Bedingung für jede Zeile im Bereich aus. Wenn die Bedingung TRUE ergibt, wird die gesamte Zeile in den gefilterten Ergebnissen angezeigt. Hier ein Beispiel aus einer Tabelle zur mechanischen Bestandsaufnahme:
=FILTER(A2:H101, (C2:C101="Bearings")*(G2:G101="Timken"))
Diese Formel extrahiert alle Zeilen, in denen die Ressource „Timken“ und die Kategorie „Lager“ lautet. Das Sternchen (*) erstellt eine UND-Bedingung, indem die logischen Arrays miteinander multipliziert werden.
Wenn Sie Ihrem Quellbereich neue Daten hinzufügen, Verwenden der FILTER-Funktion in Excel Es ist sinnvoller als manuelles Sortieren und temporäre Tabellen, da gefilterte Ergebnisse automatisch aktualisiert werden. Dies macht es nützlich für die Erstellung von Live-Dashboards und Berichten.
2. EINZIGARTIG
Extrahieren Sie eindeutige Werte ohne Duplikate
UNIQUE zieht eindeutige Werte aus Ihrem Datenbereich und vermeidet automatisch Duplikate. Diese Funktion ist wichtig, wenn Sie Dropdown-Listen erstellen, Datenkategorien analysieren und zusammenfassende Berichte erstellen möchten. Die Formel lautet:
=UNIQUE(array, [by_col], [exactly_once])
So funktioniert jeder Eingang:
- Array (Bereich): Der Bereich, der die Daten enthält, aus denen Sie Duplikate entfernen möchten – es kann sich um eine einzelne Spalte, mehrere Spalten oder einen ganzen Abschnitt der Tabelle handeln.
- by_col (optional): FALSE vergleicht Zeilen, um die Eindeutigkeit zu bestimmen (Standard), während TRUE Spalten vergleicht. In den meisten Szenarien wird jedoch der Standardzeilenvergleich verwendet.
- genau_einmal (optional): FALSE gibt alle eindeutigen Werte zurück, auch solche, die mehrfach vorkommen (Standard), und TRUE gibt nur Werte zurück, die genau einmal im Datensatz vorkommen.
Die Funktion UNIQUE wertet jede Zeile bzw. jeden Wert in Ihrem Array aus und gibt nur das erste Vorkommen jedes eindeutigen Elements zurück. Die Reihenfolge entspricht der ursprünglichen Datensequenz. Hier ein Beispiel:
=UNIQUE(G2:G22)
Diese Formel extrahiert alle eindeutigen Lieferantennamen aus der Spalte „Lieferant“ (G) und erstellt eine saubere, duplikatsbasierte Liste. Ich verwende sie zum Erstellen von Lieferanten-Dropdownlisten oder zusammenfassenden Berichten.
Sie können es auch für die gesamte Tabelle verwenden, wie unten gezeigt:
=UNIQUE(A2:F100)
Gibt eindeutige Kombinationen über alle Spalten (A bis F) zurück und zeigt unterschiedliche Bestandsdatensätze an. Wenn zwei Teile in jeder Spalte identische Werte aufweisen, wird nur eines in den Ergebnissen angezeigt.
Bei der Arbeit mit großen Datensätzen macht UNIQUE das mühsame manuelle Entfernen von Duplikaten überflüssig. Dynamische Ergebnisse werden aktualisiert, sobald neue Daten eintreffen. Da UNIQUE Spillover-Matrizen erstellt, entfällt bei diesem Ansatz die mühsame Größenanpassung von Tabellen, da diese automatisch skaliert werden, um alle eindeutigen Werte zu berücksichtigen. Ich verwende es, um saubere Referenzlisten zu pflegen und zuverlässige Datenvalidierungsbereiche zu erstellen.
1. SORT und SORTBY
Organisieren Sie Ihre Daten, ohne das Original zu beeinträchtigen
Die Funktionen SORT und SORTBY organisieren Daten dynamisch und behalten dabei die Quelle bei. SORT übernimmt die grundlegende Sortierung nach Spaltenposition, während SORTBY nach Werten in verschiedenen Spalten sortiert – für mehr Flexibilität bei komplexen Sortiervorgängen.
SORT verwendet diese Struktur:
=SORTIEREN(Array, [sort_index], [sort_order], [by_col])
Jeder Parameter steuert Folgendes:
- Array: Der Datenbereich, den Sie sortieren möchten – umfasst alle Spalten, die in den sortierten Ergebnissen erscheinen sollen.
- sort_index (optional): Die Spaltennummer innerhalb des Arrays, nach der sortiert werden soll. Verwenden Sie 1 für die erste Spalte, 2 für die zweite Spalte usw. (Standard ist 1).
- sort_order (optional): Verwenden Sie 1 für aufsteigende Reihenfolge (Standard) und -1 für absteigende Reihenfolge.
- by_col (optional): FALSE zum Sortieren nach Zeilen (Standard), TRUE zum Sortieren nach Spalten – in den meisten Szenarien wird die Zeilensortierung verwendet.
Die Funktion SORTBY hat die folgende Form:
=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)
Zu seinen Transaktionen gehören:
- Array: Der zu sortierende Datenbereich – ähnlich wie die Funktion SORT enthält er alle Spalten, die Sie in den Ergebnissen haben möchten.
- nach_Array1: Der Bereich, der die Werte enthält, die die Sortierreihenfolge bestimmen, kann jede Spalte sein, auch außerhalb des Bereichs des Hauptarrays.
- sort_order1 (optional): 1 für aufsteigende Reihenfolge (Standard), -1 für absteigende Reihenfolge.
- by_array2, sort_order2 (optional): Zusätzliche Sortierkriterien für mehrstufige Sortierung.
Anhand eines Beispiels aus einer mechanischen Bestandstabelle können diese Funktionen Sortierszenarien aus der Praxis verarbeiten:
=SORT(A2:H22, 4, -1)
Dadurch wird der gesamte Bestand nach Lagerbestand in absteigender Reihenfolge sortiert, wobei die Artikel mit dem höchsten Lagerbestand zuerst angezeigt werden. Die Formel sortiert nach Spalte 4 (Lagerbestand), wobei alle Beziehungen zwischen den Zeilen erhalten bleiben.
Ich verwende die Funktion SORTBY. Anstelle von SORT können Sie es verwenden, um eine bessere Kontrolle über Sortierkriterien und mehrere Sortierebenen zu erhalten. Beispielsweise sortiert die folgende Formel zuerst alphabetisch nach Kategorie und dann nach Lagerbestand vom höchsten zum niedrigsten innerhalb jeder Kategorie.
=SORTIEREN NACH(A2:H22, C2:C22, 1, D2:D22, -1)
Organisierte Tabellen, intelligentere Ergebnisse
Array-Formeln beseitigen die Unordnung von Hilfsspalten und verschachtelten Funktionen, die die Pflege von Tabellenkalkulationen erschweren. Sie erhalten einzelne Formeln, die mehrere Operationen verarbeiten und so Arbeitsmappen übersichtlicher und professioneller gestalten.
Ein bemerkenswerter Vorteil sind dynamische Funktionen, bei denen die Ergebnisse automatisch aktualisiert werden, wenn sich die Quelldaten ändern. Dadurch entfallen manuelle Aktualisierungen oder fehlerhafte Formelzeichenfolgen, und Ihre Tabellenkalkulationen werden für laufende Analysen zuverlässiger.
Die Array-Funktionsbibliothek von Excel wird über diese grundlegenden Tools hinaus ständig erweitert. Wenn ich Daten aus mehreren Quellen kombinieren muss, verwende ich die Funktionen VSTACK und HSTACK, um Bereiche zu kombinieren. Zusammen ermöglichen diese Funktionen leistungsstarke Datenverarbeitungs-Workflows, die mit herkömmlichen Formeln nicht möglich wären.
