Um eine Mengenmatrix für Grafikelemente so aufzuteilen, dass jedes Element eine eigene Arbeitsblattzeile erhält, listen Sie zunächst jede Kombination aus Element und Format mit einer kumulierten Menge auf. Verwenden Sie anschließend SEQUENCE und XLOOKUP, um die Bezeichnungen entsprechend oft zu wiederholen. Diese Anleitung verwendet fiktive Bezeichnungen für Grafikelemente.

Die Aufgabe ist einfach: Wandeln Sie ein kompaktes Planungsraster in eine Artikelliste um. Bei einer Menge von zwei entstehen zwei Zeilen mit demselben Element und Format; bei null entsteht keine Zeile. Das folgende Beispiel zeigt die Zwischenschritte, damit Sie das Ergebnis prüfen können, bevor Sie die Methode anpassen.

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

Verwenden Sie Excel für Microsoft 365 für Mac, Excel 2024 für Mac oder Excel 2021 für Mac. In der Microsoft-Dokumentation sind diese Editionen sowohl für SEQUENCE als auch für XLOOKUP aufgeführt. Außerdem werden dynamische Arrays im Funktionsleitfaden für Excel 2021 für Mac ausdrücklich dokumentiert. SEQUENCE-Funktion, XLOOKUP-Funktion, Neuerungen in Excel 2021 für Mac.

Die Verfügbarkeit von Funktionen ist wichtig, wenn Sie Formeln aus anderen Anleitungen übernehmen. Microsoft führt beispielsweise VSTACK für Microsoft 365 und Excel 2024 auf dem Mac auf, aber nicht für Excel 2021. Die hier beschriebene Methode ist davon unabhängig. Microsoft weist außerdem ausdrücklich darauf hin, dass XLOOKUP in Excel 2016 und Excel 2019 nicht verfügbar ist. Diese Editionen können die folgenden Formeln daher nicht unverändert ausführen. VSTACK-Funktion, XLOOKUP-Funktion.

Mengenmatrix für Grafikelemente einrichten

Geben Sie die folgenden fiktiven Daten auf einem leeren Arbeitsblatt in A1:C4 ein. Die erste Spalte enthält die Bezeichnungen der Elemente, die Kopfzeile die Formate und die übrigen Zellen die Mengen.

Element Klein Groß
Bogenkachel 2 0
Punktkachel 1 0
Wellenkachel 2 1

Für dieses Beispiel muss jede Menge eine numerische ganze Zahl größer oder gleich null sein. Geben Sie explizit null ein, wenn kein Element benötigt wird. Bereinigen Sie leere Zellen, Textangaben als Mengen, negative Werte, Bruchzahlen und Zellfehler, bevor Sie fortfahren. Die Formeln setzen gültige Mengen voraus; sie prüfen oder runden diese nicht.

Das erwartete Ergebnis umfasst sechs Elementzeilen: zwei kleine Bogenkacheln, eine kleine Punktkachel, zwei kleine Wellenkacheln und eine große Wellenkachel. Die wiederholten Bezeichnungen sind beabsichtigt, da jede Zeile für ein einzelnes Element steht. Die Formeln erstellen Arbeitsblattdatensätze; sie erstellen oder duplizieren keine Grafikdateien.

Matrix in eine überprüfbare Hilfsliste umwandeln

Lassen Sie Spalte D leer. Geben Sie in E1:I1 die Überschriften Position, Element, Format, Menge und Letztes Element ein. Tragen Sie die Zahlen eins bis sechs der Reihe nach in E2:E7 ein. Die Hilfsliste enthält sechs Zeilen, da drei Elemente multipliziert mit zwei Formaten sechs Kombinationen ergeben.

Die Hilfsliste liest jede Quellzeile von links nach rechts, bevor sie zur nächsten Zeile wechselt: Bogenkachel Klein, Bogenkachel Groß, dann die beiden Punktkachel-Kombinationen und schließlich die beiden Wellenkachel-Kombinationen. Es ist wichtig, diese Reihenfolge beizubehalten.

Bezeichnungen und Menge für jede Kombination abrufen

Geben Sie diese Formel in F2 ein:

=INDEX($A$2:$A$4,INT((E2-1)/2)+1)

Geben Sie diese Formel in G2 ein:

=INDEX($B$1:$C$1,1,MOD(E2-1,2)+1)

Geben Sie diese Formel in H2 ein:

=INDEX($B$2:$C$4,INT((E2-1)/2)+1,MOD(E2-1,2)+1)

Kopieren Sie F2:H2 bis Zeile sieben nach unten. Dies sind gewöhnliche Zellformeln mit einem Ergebnis pro Zelle. INDEX ruft einen Wert in einer angegebenen Zeile und Spalte ab. Hier sorgt INT dafür, dass die Quellzeile nach jeweils zwei Positionen weitergeschaltet wird, während MOD zwischen den beiden Formaten wechselt. INDEX-Funktion, INT-Funktion, MOD-Funktion.

Die Nummer des letzten Elements für jede Kombination berechnen

Geben Sie in I2 die folgende Formel ein und kopieren Sie sie bis I7 nach unten:

=SUM($H$2:H2)

Der feste Anfangsbezug und der sich verschiebende Endbezug ergeben eine laufende Summe. SUM addiert die Mengen von der ersten Hilfszeile bis zur aktuellen Zeile. SUM-Funktion.

Die fertige Hilfsliste sollte so aussehen:

Position Element Format Menge Letztes Element
1 Bogenkachel Klein 2 2
2 Bogenkachel Groß 0 2
3 Punktkachel Klein 1 3
4 Punktkachel Groß 0 3
5 Wellenkachel Klein 2 5
6 Wellenkachel Groß 1 6

Interpretieren Sie Letztes Element als Grenze. Die erste Kombination belegt die Elementnummern eins und zwei. Die dritte Kombination belegt Elementnummer drei. Die fünfte belegt vier und fünf und die sechste belegt sechs. Bei einer Menge von null verschiebt sich die Grenze nicht weiter, daher werden ihr keine Elementnummern zugewiesen.

Eine Ausgabezeile pro Element erzeugen

Geben Sie in K1 Element und in L1 Format ein. Lassen Sie die Zellen unter beiden Überschriften leer. Platzieren Sie die Ausgabe in gewöhnlichen Arbeitsblattzellen außerhalb einer Excel-Tabelle: Laut Microsoft werden überlaufende Arrayformeln in Tabellen nicht unterstützt. Dynamische Arrayformeln und das Verhalten überlaufender Arrays.

Geben Sie diese vollständige Formel in K2 ein:

=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$F$2:$F$7,,1,1))

Geben Sie diese vollständige Formel in L2 ein:

=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$G$2:$G$7,,1,1))

Bestätigen Sie jede Formel mit Return. Kopieren Sie diese beiden Formeln nicht nach unten: Ihre Arrayergebnisse füllen die benachbarten Zeilen automatisch aus. Microsoft bezeichnet diese automatische Erweiterung als Überlaufen. Dynamische Arrayformeln und das Verhalten überlaufender Arrays.

SEQUENCE erzeugt die Elementnummern von eins bis zur Gesamtmenge. Für jede Nummer durchsucht XLOOKUP die kumulierten Grenzen. Die letzten beiden Argumente geben eine exakte Übereinstimmung oder den nächstgrößeren Wert an und legen die Suchrichtung von der ersten zur letzten Position fest. Bei dieser Suchrichtung bleibt bei wiederholten Summen infolge von Nullen die erste passende Grenze erhalten. SEQUENCE-Funktion, XLOOKUP-Funktion.

Das äußere IF gibt leeren Text zurück, wenn die Gesamtmenge null ist. In diesem Fall scheinen die beiden Ankerzellen leer zu sein, und es gibt keine Elementdatensätze. Dieser Fall für eine Gesamtmenge von null ist beabsichtigt; mit IF wird zwischen zwei Ergebnissen gewählt. IF-Funktion.

Für die angegebene Matrix sollte K1:L7 Folgendes enthalten:

Element Format
Bogenkachel Klein
Bogenkachel Klein
Punktkachel Klein
Wellenkachel Klein
Wellenkachel Klein
Wellenkachel Groß

Beide Ausgabespalten verwenden dieselben generierten Elementnummern und denselben Grenzbereich. Nur der Rückgabebereich ändert sich: Spalte F liefert die Elementbezeichnungen und Spalte G die Formate. Halten Sie diese Suchangaben beim Bearbeiten der Formeln aufeinander abgestimmt.

Ergebnis prüfen, bevor Sie die Quelle erweitern

Prüfen Sie die Beispieldaten auf drei Arten:

  1. Summen vergleichen: =SUM(B2:C4) und =SUM(H2:H7) müssen beide sechs ergeben.
  2. Kombinationen vergleichen: Die Ausgabe muss die vier Kombinationen mit Mengen größer als null in der angegebenen Häufigkeit enthalten.
  3. Ändern Sie B2 von zwei auf drei: Die erwartete Ausgabe umfasst dann sieben Zeilen mit drei Einträgen für Bogenkachel Klein. Setzen Sie den Wert anschließend wieder auf zwei zurück.

Die erste Prüfung erkennt beim Umwandeln fehlende oder doppelte Mengen. Die zweite deckt falsch zugeordnete Bezeichnungen auf, die anhand einer Summe allein nicht erkennbar wären. Mit der dritten wird geprüft, ob Änderungen über die Hilfsliste bis in die Ausgabe übernommen werden.

Weitere Elemente oder Formate gezielt hinzufügen

Änderungen an den Mengen in B2:C4 lassen sich mit der bestehenden Einrichtung vornehmen. Wenn Sie die Abmessungen der Matrix vergrößern, müssen Sie die Bezüge und die Länge der Hilfsliste anpassen.

Erweitern Sie für ein vollständiges Beispiel die Quelle in A1:D5 auf vier Elemente und drei Formate. Verschieben Sie den Hilfsbereich an eine andere Stelle oder lassen Sie ihn in E:I, und tragen Sie die Positionen eins bis zwölf in E2:E13 ein. Ersetzen Sie dann die Formeln in F2:H2 durch:

=INDEX($A$2:$A$5,INT((E2-1)/3)+1)

=INDEX($B$1:$D$1,1,MOD(E2-1,3)+1)

=INDEX($B$2:$D$5,INT((E2-1)/3)+1,MOD(E2-1,3)+1)

Kopieren Sie diese Formeln bis Zeile dreizehn nach unten und erweitern Sie die Formel für die laufende Summe in Spalte I bis zur selben Zeile. Ersetzen Sie in beiden Ausgabeformeln jeden Hilfsbereich, der in Zeile sieben endet, durch einen Bereich bis Zeile dreizehn. Der Divisor und die Basis für den Rest betragen nun drei, da jedes Element drei Formate hat. Prüfen Sie nach diesen Änderungen erneut die Quellensumme und die einzelnen Kombinationen.

Unvollständige oder blockierte Ausgabe beheben

Wenn Excel #SPILL! anzeigt, prüfen Sie den vorgesehenen Ausgabebereich auf vorhandene Einträge oder verbundene Zellen und vergewissern Sie sich, dass sich die Formel außerhalb einer Excel-Tabelle befindet. Ein Ergebnis, das über den Rand des Arbeitsblatts hinausreichen würde, kann ebenfalls nicht überlaufen. Microsoft dokumentiert alle diese Bedingungen. So beheben Sie einen #SPILL!-Fehler.

Wenn die Ausgabe zu wenige Zeilen enthält, prüfen Sie die Mengen, bevor Sie die Suchlogik ändern. Microsoft weist darauf hin, dass SUM Text in Bezugsbereichen ignoriert. Eine als Text gespeicherte Menge kann daher in der Summe fehlen. Korrigieren Sie den Quellwert und wiederholen Sie die Summenprüfung. SUM-Funktion.

Wenn die Bezeichnungen trotz korrekter Summe falsch sind, vergleichen Sie die Hilfsliste Zelle für Zelle mit der Matrix. Halten Sie Element, Format, Menge und kumulierte Grenze jeder Hilfszeile zusammen. Ordnen Sie nicht eigenständig eine Hilfsspalte um.

Sobald Summen und Kombinationen übereinstimmen, verwenden Sie die beiden Ausgabespalten als Artikelliste. Bewahren Sie die Matrix und die Hilfsberechnungen auf, damit sich spätere Mengenänderungen weiterhin zu den wiederholten Zeilen zurückverfolgen lassen.

Quellen