Kombinieren Sie in Microsoft 365 für Mac oder Excel 2024 für Mac CHOOSECOLS, TOCOL, HSTACK und FILTER, um wiederholte Bezeichnungen und Mengen von Grafikelementen in Inventarzeilen umzuwandeln. Die Formel im Beispiel unten bewahrt die Zuordnung der Paare, Nullmengen und unvollständige Einträge.

Diese Anleitung richtet sich an Nutzer der Mac-Desktopversion, deren Arbeitsblatt abwechselnd eine Attributspalte und die zugehörige Mengenspalte enthält. Hier ist jedes Attribut eine Bezeichnung für ein Grafikelement, zum Beispiel Circle Tile. Ziel ist es, ein zweispaltiges Inventar zu erstellen und anschließend Mengen nachzuschlagen, ohne sie von ihren Bezeichnungen zu trennen.

Welche Excel-Versionen für Mac unterstützen diese Formel?

Microsoft führt Excel für Microsoft 365 für Mac und Excel 2024 für Mac ausdrücklich für alle drei Umwandlungsfunktionen auf: CHOOSECOLS, TOCOL und HSTACK. Verwenden Sie für die Hauptformel eine dieser Editionen.

Excel 2021 für Mac umfasst dynamische Arrays, FILTER, LET und XLOOKUP. Das belegt jedoch nicht, dass die neueren Umwandlungsfunktionen unterstützt werden. In Microsofts Dokumentation zu den Funktionen von Excel 2021 für Mac werden die verfügbaren Array- und Nachschlagefunktionen aufgeführt; die drei Funktionsseiten oben nennen Excel 2021 nicht.

Prüfen Sie Ihre Edition und Version im Dialogfeld Info. Microsoft erklärt in der Office-Versionsübersicht, dass das Dialogfeld auf dem Mac sowohl die Version als auch den Lizenztyp anzeigt. Falls Ihre Edition die Umwandlungsfunktionen nicht enthält, verwenden Sie für die automatische Methode Microsoft 365 für Mac oder Excel 2024 für Mac oder führen Sie die unten beschriebene manuelle Stapelung durch.

Ein vollständiges, synthetisches Beispiel einrichten

Geben Sie diese Daten auf einem leeren Arbeitsblatt in A1:F4 ein. Lassen Sie die beiden leeren Zellen in der Mitte von Zeile 3 tatsächlich leer. Alle Bezeichnungen und Mengen dienen nur der Veranschaulichung.

Attribut 1 Menge 1 Attribut 2 Menge 2 Attribut 3 Menge 3
Circle Tile 4 Line Texture 2 Dot Pattern 0
Arc Motif 3 Grid Texture 5
Circle Tile 7 Wave Pattern 6 Square Tile 1

Jedes benachbarte Paar steht für einen Inventareintrag. Die gewünschte Ausgabe liest jede Quellzeile von links nach rechts, bevor sie nach unten fortfährt. Circle Tile erscheint zweimal, weil es sich um separate Einträge handelt; gleiche Bezeichnungen zusammenzufassen wäre eine zusätzliche Aufgabe.

Diese Methode setzt voraus, dass auf jede Bezeichnung unmittelbar ihre Menge folgt und dass alle ausgewählten Spalten diesem wiederkehrenden Muster entsprechen. Falls Ihr Blatt auch einen Sammlungsnamen oder ein anderes Feld enthält, das zu jedem Eintrag gehören muss, wird dieses Feld in der zweispaltigen Ausgabe nicht übernommen. Legen Sie vor der Anwendung der Formel fest, welche Felder das Inventar enthalten soll.

Die wiederholten Paare in Inventarzeilen umwandeln

Geben Sie in H1 Asset und in I1 Quantity ein. Lassen Sie den Ausgabebereich unter diesen Überschriften leer. Fügen Sie die vollständige Formel in H2 ein und drücken Sie dann Return. Dynamische Arrayformeln werden einmal eingegeben und auf benachbarte Zellen ausgeweitet, wie im Leitfaden zu Arrayformeln von Microsoft beschrieben.

=LET(
    source,A2:F4,
    clean,IF(source="","",source),
    labels,TOCOL(CHOOSECOLS(clean,1,3,5),0,FALSE),
    quantities,TOCOL(CHOOSECOLS(clean,2,4,6),0,FALSE),
    keep,((labels<>"")+(quantities<>""))>0,
    FILTER(HSTACK(labels,quantities),keep,"No inventory entries")
)

Das erwartete Ergebnis belegt H2:I9:

Asset Menge
Circle Tile 4
Line Texture 2
Dot Pattern 0
Arc Motif 3
Grid Texture 5
Circle Tile 7
Wave Pattern 6
Square Tile 1

Die Reihenfolge der Paare im Beispiel, die acht Ausgabeeinträge, die beibehaltene Null und die Gesamtmenge von 28 wurden mit einer kleinen deterministischen Python-Berechnung überprüft. Die Excel-Formel wurde anhand der dokumentierten Funktionssyntax von Microsoft geprüft, aber nicht in Excel ausgeführt.

So hält die Formel die Paare zusammen

LET weist Zwischenergebnissen Namen zu, damit die Formel sie wiederverwenden kann. Die Namen source, clean, labels, quantities und keep gelten nur innerhalb dieser Formel. Sie müssen keine benannten Bereiche erstellen. Microsoft beschreibt dieses Verhalten in der Referenz zu LET.

CHOOSECOLS wählt die Bezeichnungsspalten 1, 3 und 5 sowie die Mengenspalten 2, 4 und 6 aus. Diese Zahlen geben die Positionen innerhalb des ausgewählten Quellbereichs an. Microsoft bestätigt in der Dokumentation zu CHOOSECOLS, dass die Funktion die angegebenen Spalten aus einem Array zurückgibt.

Beide Aufrufe von TOCOL verwenden 0, um alle Positionen beizubehalten, und FALSE, um die Zeilen zu durchlaufen. Da beide Aufrufe die übereinstimmenden Positionen in derselben Reihenfolge durchlaufen, bleibt jede Bezeichnung ihrer Menge zugeordnet. Microsoft dokumentiert beide Argumente in der Referenz zu TOCOL.

HSTACK setzt die beiden resultierenden Spalten nebeneinander. Beide enthalten vor dem Filtern neun Positionen, ihre Längen stimmen also überein. Microsoft erklärt, dass HSTACK Arrays horizontal anfügt und kürzere Arrays mit Fehlern auffüllt, wenn ihre Zeilenzahlen voneinander abweichen.

Schließlich wählt keep Positionen aus, die eine Bezeichnung, eine Menge oder beides enthalten. FILTER entfernt nur vollständig leere Paare. Das letzte Argument gibt eine Meldung aus, wenn keine Einträge übrig bleiben, entsprechend der von Microsoft dokumentierten Syntax von FILTER.

Leere Zellen und Nullen beibehalten, ohne unvollständige Einträge zu verbergen

Eine leere Menge und eine numerische Null enthalten unterschiedliche Informationen. Die Null ist eine eingegebene Anzahl; eine leere Zelle muss noch interpretiert werden. Die Beispiele von Microsoft zu TOCOL zeigen, dass leere Zellen ohne weitere Verarbeitung den Wert null zurückgeben können. Deshalb verwendet der Schritt clean IF, um für leere Quellzellen leeren Text zurückzugeben, bevor die Daten umgewandelt werden. Microsoft dokumentiert bedingte Rückgabewerte in der Referenz zu IF.

Die Formel lässt unvollständige Paare absichtlich sichtbar. Prüfen Sie folgende Fälle, bevor Sie sich auf das Ergebnis verlassen:

Quellpaar Verhalten in der Ausgabe Maßnahme
Bezeichnung und numerische Menge Behält beides bei Prüfen Sie, ob die Menge beabsichtigt ist
Bezeichnung und Null Behält die Null bei Beibehalten, wenn null eine beabsichtigte Anzahl ist
Beide Zellen leer Entfernt das Paar Keine Korrektur erforderlich
Bezeichnung mit leerer Menge Behält die Bezeichnung und die leere Menge bei Menge ergänzen
Leere Bezeichnung mit Menge Behält die leere Bezeichnung und die Menge bei Bezeichnung ergänzen

Entfernen Sie leere Zellen nicht unabhängig voneinander aus den Bezeichnungs- und Mengenarrays. Wenn an einer Position eine Bezeichnung und an einer anderen eine Menge fehlt, kann das separate Entfernen der leeren Zellen die Arrays verschieben und zu falschen Zuordnungen führen. Die Positionen bis nach dem Zusammenfügen beizubehalten, ist die entscheidende Gestaltungsentscheidung dieser Formel.

Eine scheinbar leere Zelle, die Leerzeichen enthält, wird von dieser Formel nicht als leer behandelt. Bereinigen Sie solche Einträge in der Quelle. Ebenso wandelt die Formel Mengen, die als Text eingegeben wurden, nicht automatisch um, sondern übernimmt sie unverändert. Prüfen Sie solche Werte, bevor Sie Summen berechnen.

Einen Eintrag nachschlagen oder alle passenden Einträge zurückgeben

Geben Sie Circle Tile in K2 ein. Um die erste zugehörige Menge abzurufen, geben Sie diese Formel in L2 ein:

=XLOOKUP(K2,CHOOSECOLS(H2#,1),CHOOSECOLS(H2#,2),"No matching asset",0)

H2# verweist auf das gesamte ausgegebene Ergebnis, das bei H2 beginnt. So wird die Suche an Änderungen der Ergebnisgröße angepasst. Microsoft erläutert diese Schreibweise in der Dokumentation zum Operator für ausgegebene Bereiche.

Das Ergebnis ist 4. Der explizite Übereinstimmungsmodus 0 verlangt eine exakte Übereinstimmung; bei der Standardsuche wird der erste passende Eintrag zurückgegeben. Die Referenz zu XLOOKUP von Microsoft dokumentiert beide Verhaltensweisen. Sie bestätigt außerdem, dass XLOOKUP in Excel 2016 und Excel 2019 nicht verfügbar ist.

Wenn wiederholte Bezeichnungen von Bedeutung sind, reicht eine Suche nach dem ersten Treffer nicht aus. Um alle Einträge für Circle Tile zurückzugeben, fügen Sie diese vollständige alternative Formel in N2 ein und lassen Sie in den Spalten N:O ausreichend Platz:

=FILTER(H2#,CHOOSECOLS(H2#,1)=K2,"No matching asset")

Damit werden zwei Zeilen zurückgegeben: Circle Tile mit 4 und Circle Tile mit 7. FILTER gibt die Zeilen zurück, die die angegebene Bedingung erfüllen, wie in der Referenz zu FILTER von Microsoft dokumentiert. Verwenden Sie die Formel für den ersten Treffer, wenn ein passender Eintrag Ihre Frage beantwortet. Verwenden Sie das gefilterte Ergebnis, wenn Sie jedes Vorkommen prüfen müssen.

Ergebnis überprüfen und Quellbereich anpassen

Prüfen Sie für das Beispiel, ob acht Ausgabezeilen vorhanden sind und Dot Pattern weiterhin die Menge null hat. Vergleichen Sie dann die Summe der Quellmengen mit der Summe der umgewandelten Mengen. Geben Sie diese Formeln in zwei freie Zellen ein:

=SUM(B2:B4,D2:D4,F2:F4)

=SUM(CHOOSECOLS(H2#,2))

Beide sollten 28 zurückgeben. Die Dokumentation zu SUM von Microsoft bestätigt, dass mehrere Bereiche addiert werden können, und erklärt, dass Textwerte ignoriert werden. Übereinstimmende Summen beweisen daher nicht, dass jede Menge numerisch ist, und sie können keine Mengen erkennen, die zwischen Bezeichnungen vertauscht wurden. Prüfen Sie auch die Zuordnung von Bezeichnungen und Mengen.

Bei zusätzlichen Quellzeilen ändern Sie in der Hauptformel A2:F4 in den tatsächlichen Bereich, zum Beispiel A2:F100. Zeilen außerhalb dieses Bezugs werden nicht berücksichtigt. Für vier wiederholte Paare ändern Sie die Quelle auf acht Spalten, zum Beispiel A2:H100, und aktualisieren die Positionen der Bezeichnungsspalten auf 1,3,5,7 und die Positionen der Mengenspalten auf 2,4,6,8.

Wenn Sie auf acht Quellspalten erweitern, verschieben Sie die Ausgabe in einen freien Bereich, zum Beispiel J2, und aktualisieren Sie nachgelagerte Verweise auf ausgegebene Bereiche zu J2#. Halten Sie Quelle und Ausgabe getrennt. Eine ungültige Spaltenposition erzeugt einen Fehler, wie unter Fehler bei CHOOSECOLS dokumentiert.

Blockierte Ausgabe beheben oder manuell stapeln

Wenn die Hauptformel #SPILL! zurückgibt, wählen Sie die Formelzelle aus und prüfen Sie den vorgesehenen Ausgabebereich. Verschieben Sie vorhandene Einträge aus diesem Bereich und prüfen Sie, ob Zellen verbunden sind. Platzieren Sie die Formel außerhalb einer Excel-Tabelle: Microsoft dokumentiert im Leitfaden zur Fehlerbehebung bei #SPILL!, dass ausgegebene Arrayformeln nicht innerhalb von Tabellen oder in verbundene Zellen ausgegeben werden können.

Falls die Quelle einen Fehler enthält, korrigieren Sie den entsprechenden Quelleintrag, bevor Sie das Inventar verwenden. Die Formel unterdrückt keine Fehler. Microsoft weist darauf hin, dass ein Fehler im Einschlussarray von FILTER dazu führt, dass die Funktion einen Fehler zurückgibt. Würde die gesamte Umwandlung in eine Fehlerunterdrückung eingeschlossen, könnte dadurch ein Eintrag verborgen werden, der korrigiert werden muss.

Bei einem kleinen Arbeitsblatt in Excel 2021 für Mac können Sie dieselbe zweispaltige Struktur manuell erstellen: Kopieren Sie A2:B4 nach H2:I4, kopieren Sie C2:D4 nach H5:I7 und kopieren Sie E2:F4 nach H8:I10. Entfernen Sie nur Zeilen, in denen beide Zellen leer sind. Dadurch werden die Einträge nach Paarblöcken gestapelt, statt wie bei der Hauptformel nach Quellzeilen geordnet zu werden. Bei Änderungen an der Quelle müssen Sie den Vorgang wiederholen.

Nach dieser manuellen Einrichtung kann Excel 2021 für Mac seine dokumentierte Funktion XLOOKUP mit festen Bereichen verwenden:

=XLOOKUP(K2,H2:H10,I2:I10,"No matching asset",0)

Ein brauchbares fertiges Inventar bewahrt alle relevanten Paare, macht unvollständige Einträge sichtbar und verwendet eine für wiederholte Bezeichnungen geeignete Suchmethode. Prüfen Sie diese Eigenschaften zuerst anhand des Beispiels und erweitern Sie dann den Quellbereich entsprechend Ihrem Arbeitsblatt.

Quellen