Fügen Sie jeder Inventarzeile mit einer Suche nach exakter Übereinstimmung Asset-Metadaten hinzu, auch wenn Asset-IDs mehrfach vorkommen. In Excel für Mac können Sie mit HSTACK und XLOOKUP ein einziges überlaufendes Ergebnis erstellen oder in Excel 2021 separate Suchspalten verwenden.

Diese Anleitung richtet sich an Nutzer der Mac-Desktopversion, die viele Inventareinträge für Kunst-Assets und eine Metadatenzeile pro Asset haben. Die Aufgabe besteht darin, jedem Asset Titel, Format und Palette zuzuordnen und dabei die ursprüngliche Reihenfolge, Mengen und wiederholten Einträge des Inventars beizubehalten. Alle Beispieldaten unten sind synthetisch.

Welche Version von Excel für Mac unterstützt diesen Ansatz?

Prüfen Sie Ihre Installation über Excel → Über Microsoft Excel. In der Mac-Anleitung von Microsoft wird erklärt, dass das Dialogfeld „Über“ die Versionsnummer und den Lizenztyp der Anwendung anzeigt. Prüfen Sie beides, wenn Sie eine Formel auswählen. Informationen zu Office: Welche Office-Version verwende ich?

Mac-Edition Vorgehensweise in diesem Artikel
Excel für Microsoft 365 für Mac Ein gemeinsames Ergebnis mit HSTACK und XLOOKUP
Excel 2024 für Mac Ein gemeinsames Ergebnis mit HSTACK und XLOOKUP
Excel 2021 für Mac Drei separate Überlaufspalten mit XLOOKUP
Excel 2019 oder 2016 Diese Formeln erfordern eine neuere Edition

Microsoft führt Microsoft 365 für Mac und Excel 2024 für Mac in der Dokumentation zu HSTACK auf. Die Dokumentation zu den Funktionen von Excel 2021 für Mac bestätigt XLOOKUP und dynamische Arrays. In der Dokumentation zu XLOOKUP werden Excel 2016 und 2019 ausdrücklich ausgeschlossen.

Wenn Sie Excel 2021 haben, verwenden Sie die Methode mit separaten Spalten weiter unten. Sie führt zum selben angereicherten Ergebnis, ohne dass HSTACK erforderlich ist.

Inventar und Metadaten mit einer gemeinsamen Asset-ID einrichten

Erstellen Sie in derselben Arbeitsmappe zwei Arbeitsblätter mit den Namen Inventory und Metadata. Geben Sie die folgenden Inventardaten in Inventory!A1:B6 ein. Belassen Sie diese Zellen in diesem Beispiel als normalen Arbeitsblattbereich.

AssetID Quantity
ARC-01 3
TILE-02 5
ARC-01 2
GRID-03 4
TILE-02 1

Geben Sie die Metadaten in Metadata!A1:D4 ein:

AssetID Title Format Palette
ARC-01 Arc Study Vector Teal
TILE-02 Tile Study Raster Amber
GRID-03 Grid Study Vector Slate

Die wiederholten Inventar-IDs sind beabsichtigt: Zwei Einträge können separate Mengen desselben Assets beschreiben. Das Metadatenblatt enthält die gemeinsame Beschreibung nur einmal. Ihre Ausgabe sollte daher fünf Datenzeilen enthalten, obwohl die Metadaten nur drei Assets umfassen.

Verwenden Sie eine Asset-ID, die dasselbe Asset in beiden Blättern durchgehend eindeutig identifiziert. In diesem Beispiel sind alle IDs und Metadatenfelder ausgefüllt. Legen Sie in Ihrer eigenen Arbeitsmappe ausdrücklich fest, wie Sie ein unbekanntes Feld darstellen möchten, zum Beispiel mit dem Text Unknown, damit es von einem fehlenden Asset-Treffer unterschieden werden kann.

Prüfen, ob jede Inventar-ID genau einen Metadaten-Treffer hat

Geben Sie auf Inventory in C1 den Namen MetadataMatches ein. Geben Sie diese Formel in C2 ein und kopieren Sie sie anschließend bis C6:

=COUNTIF(Metadata!$A$2:$A$4,A2)

Werten Sie das Ergebnis aus, bevor Sie die Ausgabe erstellen:

  • 0: Die Asset-ID fehlt im Metadatenblatt.
  • 1: Für das Asset gibt es die eine Metadatenzeile, die dieser Arbeitsablauf voraussetzt.
  • Eine Zahl größer als 1: Die Metadaten enthalten doppelte IDs, die bereinigt werden müssen.

COUNTIF zählt Zellen, die ein Kriterium erfüllen. Bei den bereitgestellten Daten ergibt jede Prüfung 1. Verwenden Sie einfache IDs wie die hier gezeigten: COUNTIF behandelt Sternchen und Fragezeichen als Platzhalterzeichen. IDs, die diese Zeichen enthalten, erfordern daher eine zusätzliche Verarbeitung. Die Funktion COUNTIF in Microsoft Excel verwenden

Bereinigen Sie doppelte Metadaten-IDs, bevor Sie fortfahren. XLOOKUP gibt standardmäßig den ersten Treffer zurück. Ein ausgefülltes Ergebnis allein beweist daher nicht, dass der Metadatenschlüssel eindeutig ist. XLOOKUP-Funktion

Ein einzelnes angereichertes Array in Microsoft 365 oder Excel 2024 erstellen

Erstellen Sie ein Arbeitsblatt mit dem Namen Enriched. Geben Sie diese fünf Überschriften in A1:E1 ein: AssetID, Quantity, Title, Format, Palette. Lassen Sie A2:E6 leer.

Wählen Sie Enriched!A2 aus, geben Sie die vollständige Formel unten ein und drücken Sie Return:

=HSTACK(
  Inventory!A2:B6,
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)
)

Diese Formel kombiniert die beiden ursprünglichen Inventarspalten mit drei Spalten mit Suchergebnissen. Jede Suche verwendet dieselben fünf Inventar-IDs, sodass die hinzugefügten Felder an den ursprünglichen Einträgen ausgerichtet bleiben. HSTACK fügt die Arrays horizontal in der angegebenen Reihenfolge aneinander. HSTACK-Funktion

Das letzte 0 in jeder Suche fordert eine exakte Übereinstimmung an. Für eine ID ohne Treffer wird der Text MISSING METADATA zurückgegeben. So bleibt die Lücke sichtbar und Sie können unvollständige Treffer finden, statt ein unerklärtes leeres Feld zu erhalten. XLOOKUP-Funktion

Die erwartete Ausgabe belegt Enriched!A2:E6:

AssetID Quantity Title Format Palette
ARC-01 3 Arc Study Vector Teal
TILE-02 5 Tile Study Raster Amber
ARC-01 2 Arc Study Vector Teal
GRID-03 4 Grid Study Vector Slate
TILE-02 1 Tile Study Raster Amber

Beachten Sie, dass beide ARC-01-Einträge mit den Mengen 3 und 2 erhalten bleiben. Beide bekommen Arc Study, Vector und Teal. Die Metadaten haben diese Einträge angereichert, ohne sie zusammenzuführen.

Die Formel sucht bewusst jeweils ein Metadatenfeld einzeln und fügt die Ergebnisse anschließend zusammen. Dadurch ist die Form der Ausgabe eindeutig: fünf Inventarzeilen, zwei ursprüngliche Spalten und drei hinzugefügte Spalten.

Drei Suchspalten in Excel 2021 für Mac verwenden

Arbeiten Sie in Excel 2021 direkt neben dem ursprünglichen Inventar. Behalten Sie die Prüfung der Trefferanzahl in Spalte C bei und geben Sie dann Title, Format und Palette in D1:F1 ein. Lassen Sie D2:F6 leer.

Geben Sie diese Formel in Inventory!D2 ein:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0)

Geben Sie diese Formel in Inventory!E2 ein:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0)

Geben Sie diese Formel in Inventory!F2 ein:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)

Drücken Sie nach jeder Formel Return. Jedes Ergebnis füllt fünf Zeilen unterhalb der Startzelle aus. Ihre Asset-IDs und Mengen bleiben in den Spalten A:B, die Diagnose der Trefferanzahl bleibt in C, und die hinzugefügten Metadaten erscheinen in D:F. Excel 2021 für Mac bietet das dynamische Arrayverhalten, das für diesen Ansatz erforderlich ist. Neuigkeiten in Excel 2021 für Mac

Die Arbeitsmappe für mehr Inventarzeilen anpassen

In den Beispielen werden feste Bereiche verwendet, damit jeder Bezug sichtbar ist. Wenn Ihr Inventar wächst, erweitern Sie alle Inventarbezüge bis zur gleichen letzten Zeile. Endet das Inventar beispielsweise in Zeile 501, benötigt die kombinierte Formel Inventory!A2:B501 sowie drei Verweise auf Inventory!A2:A501. Erweitern Sie die Prüfung der Trefferanzahl bis zur gleichen Zeile.

Wenn die Metadaten wachsen, erweitern Sie alle Metadatenbezüge gemeinsam. Lautet die letzte Zeile nun 101, verwenden Sie Metadata!A2:A101 für die Schlüssel und die entsprechenden Rückgabebereiche B2:B101, C2:C101 und D2:D101. Fügen Sie tatsächliche Datensätze hinzu, statt einen großen Block ungenutzter Zeilen einzubeziehen.

Bei einer Arbeitsmappe, die regelmäßig wächst, können Sie die Quellbereiche in Excel-Tabellen umwandeln und strukturierte Verweise verwenden. Microsoft dokumentiert, dass diese Verweise angepasst werden, wenn Quellzeilen hinzugefügt oder entfernt werden. Platzieren Sie die überlaufende Ausgabe außerhalb einer Tabelle im Arbeitsblatt-Raster. Dynamische Arrayformeln und Verhalten überlaufender Arrays

Wenn Sie die Anreicherung in einer Excel-Tabelle benötigen, verwenden Sie für jede Metadatenspalte und Inventarzeile eine separate Formel, die ein einzelnes Ergebnis liefert. Tabellen unterstützen berechnete Spalten, in denen eine Formel auf die gesamte Spalte angewendet wird. Ein mehrzelliges Überlaufergebnis muss außerhalb der Tabelle liegen. Übersicht über Excel-Tabellen

Probleme beheben und das fertige Ergebnis überprüfen

Wenn Excel #SPILL! anzeigt, überprüfen Sie die vorgesehenen Zielzellen. Verschieben Sie vorhandene Inhalte, die Sie behalten möchten, und stellen Sie sicher, dass sich die Formel außerhalb einer Excel-Tabelle befindet. Nur die linke obere Zelle eines überlaufenden Ergebnisses lässt sich bearbeiten; ändern Sie die Formel dort. Microsoft dokumentiert diese Grenzen überlaufender Arrays und Fehler durch blockierte Zellen. Dynamische Arrayformeln und Verhalten überlaufender Arrays

Wenn in einer Zeile MISSING METADATA angezeigt wird, überprüfen Sie die Trefferanzahl. Eine Null bedeutet, dass Sie den fehlenden Metadatensatz hinzufügen oder die Asset-ID korrigieren müssen. Achten Sie auf versehentliche Leerzeichen am Anfang oder Ende, wenn sich optisch identische Bezeichnungen unerwartet verhalten. Microsoft nennt diese als eine Ursache für unerwartete Ergebnisse von COUNTIF. Die Funktion COUNTIF in Microsoft Excel verwenden

Belassen Sie für diese Einrichtung beide Quellarbeitsblätter in derselben Arbeitsmappe. Wenn Sie die Quelle später in eine andere Arbeitsmappe verschieben, berücksichtigen Sie die von Microsoft dokumentierte Einschränkung: Bei verknüpften dynamischen Arrays müssen beide Arbeitsmappen geöffnet sein; beim Aktualisieren mit geschlossener Quelldatei kann #REF! auftreten. Dynamische Arrayformeln und Verhalten überlaufender Arrays

Überprüfen Sie das synthetische Beispiel anhand dreier konkreter Kriterien:

  1. Es gibt fünf Ausgabezeilen, die der Reihenfolge des ursprünglichen Inventars entsprechen.
  2. Die Mengen bleiben 3, 5, 2, 4, 1 und ergeben zusammen 15.
  3. Wiederholte IDs erhalten identische Metadaten, und jede Prüfung der Trefferanzahl ergibt 1.

Diese Prüfungen bestätigen das gewünschte Ergebnis: Jeder Inventareintrag behält seinen Platz und seine Menge und erhält zugleich die Beschreibung seiner Asset-ID. Wiederholen Sie bei einer größeren Arbeitsmappe dieselben Prüfungen, nachdem Sie Bereiche erweitert oder Metadatensätze hinzugefügt haben.

Quellen