Um entsprechende Inventarkennzahlen in Excel für Mac zu kombinieren, addieren Sie übereinstimmende numerische Arrays mit +. Wenn die Asset-Zeilen unterschiedlich angeordnet sind, suchen Sie zuerst jedes Asset und addieren Sie dann die zurückgegebenen Mengen. So bleiben die Ergebnisse für jede Kennzahl getrennt.

Diese Anleitung verwendet synthetische Kunst-Assets mit zwei Kennzahlen: Druckexemplare und Farbmusterkarten. Ziel ist es, Mengen aus zwei Lagerorten zusammenzuführen und dabei jedes Asset und jede Kennzahl eindeutig zu halten. Alle Mengen dienen nur zur Veranschaulichung.

Wählen Sie die Formel danach aus, wie die Assets einander entsprechen

Klären Sie vor dem Schreiben einer Formel, wodurch zwei Werte einander entsprechen. Übereinstimmende Positionen funktionieren nur, wenn beide Eingabeblöcke dieselbe Asset-Reihenfolge und Kennzahlenreihenfolge haben. Stimmen die Asset-Bezeichnungen überein, können die Quellzeilen in unterschiedlicher Reihenfolge stehen.

Eingabebedingung Unterstützter Ansatz Ergebnis
Dieselben Assets, dieselbe Zeilenreihenfolge, dieselben Kennzahlenspalten Gleich große numerische Bereiche addieren Ein Ergebnis je Asset und Kennzahl
Eindeutige Asset-Bezeichnungen, unterschiedliche Zeilenreihenfolge Zwei mehrspaltige XLOOKUP-Ergebnisse addieren Die Kennzahlen eines Assets in getrennten Zellen
Wiederholte Asset-Bezeichnungen, die zusammengeführt werden sollen Für jede Kennzahl eine separate SUMIF-Berechnung verwenden Ein Ergebnis je angefordertem Asset und je Kennzahl

Die folgenden Beispiele leiten diese Ansätze aus den von Microsoft dokumentierten Arrayberechnungen, mehrspaltigen Suchergebnissen und bedingten Summierungen ab. Wählen Sie die Methode passend zu Ihrer Eingabestruktur, bevor Sie eine Formel kopieren.

Prüfen Sie Ihre Mac-Edition

Das Suchbeispiel gilt für Excel für Microsoft 365 für Mac, Excel 2024 für Mac und Excel 2021 für Mac. Microsoft weist ausdrücklich darauf hin, dass XLOOKUP in Excel 2016 und Excel 2019 nicht verfügbar ist, obwohl diese Editionen in der umfassenden Liste der unterstützten Versionen auf der Seite aufgeführt sind. Microsoft-Dokumentation zu XLOOKUP.

Auf einem Mac enthält das Menü einer Office-Anwendung den Dialog Über, in dem Version und Lizenztyp angezeigt werden. Prüfen Sie in Excel Excel → Über Excel. Ermitteln Sie vor der Formelauswahl sowohl die Produkt-Edition als auch die Versionsnummer. Informationen zu Office: Welche Office-Version verwende ich?.

Die optionale Formel HSTACK weiter unten in diesem Artikel erfordert Excel für Microsoft 365 für Mac oder Excel 2024 für Mac; Microsoft führt Excel 2021 für Mac für diese Funktion nicht auf. Für die Hauptberechnungen ist sie nicht erforderlich. Microsoft-Dokumentation zu HSTACK.

Richten Sie zwei kleine Inventare für Kunst-Assets ein

Verwenden Sie ein leeres Arbeitsblatt mit normalen Zellbereichen. Geben Sie das Inventar des ersten Lagerorts in A1:C4 ein:

Asset Druckexemplare Farbmusterkarten
Tile-A 8 3
Tile-B 5 7
Tile-C 6 2

Geben Sie das Inventar des zweiten Lagerorts in E1:G4 ein und lassen Sie Spalte D leer:

Asset Druckexemplare Farbmusterkarten
Tile-A 4 2
Tile-B 9 1
Tile-C 3 5

Beide Zahlenblöcke sind drei Zeilen hoch und zwei Spalten breit. Die erste Zahlenspalte zählt Druckexemplare, die zweite Farbmusterkarten. Die Bezeichnungen befinden sich außerhalb der Rechenbereiche.

Bereiten Sie in J1:L4 den folgenden Ausgabebereich vor. Geben Sie zunächst nur die Überschriften und Asset-Bezeichnungen ein; lassen Sie die Mengenzellen leer.

Asset Zusammengeführte Druckexemplare Zusammengeführte Farbmusterkarten
Tile-A
Tile-B
Tile-C

In diesen Beispielen ist jede Menge eine Zahl und jedes Asset kommt in jeder Quelle genau einmal vor. Ein fehlender Datensatz und ein erfasster Nullwert haben unterschiedliche Bedeutungen: Verwenden Sie null, wenn die Menge bekanntermaßen null beträgt, und klären Sie einen fehlenden Datensatz, bevor Sie ihn als null behandeln.

Addieren Sie ausgerichtete Arrays, damit jedes entsprechende Ergebnis erhalten bleibt

Geben Sie bei der gezeigten Reihenfolge der Quellzeilen diese vollständige Arbeitsblattformel in K2 ein:

=B2:C4+F2:G4

Bestätigen Sie die Formel mit Return. In Excel mit dynamischen Arrays belegt die Ausgabe K2:L4: drei Asset-Zeilen und zwei Kennzahlenspalten. Microsoft dokumentiert, dass Arrayformeln mehrere Ergebnisse zurückgeben können und dynamische Ergebnisse in benachbarte Zellen überlaufen. Richtlinien und Beispiele für Arrayformeln.

Das erwartete Ergebnis ist:

Asset Zusammengeführte Druckexemplare Zusammengeführte Farbmusterkarten
Tile-A 12 5
Tile-B 14 8
Tile-C 9 7

Die Berechnung folgt den Positionen: B2+F2 ergibt 12, während C2+G2 5 ergibt. Für jede übrige Position gilt dieselbe Regel. Aus den beiden Quellblöcken wird ein Ergebnisblock mit denselben Abmessungen.

Für ein Inventar mit nur einer Spalte würde die entsprechende Formel =B2:B4+F2:F4 lauten. Sie gibt drei Mengen zurück, eine je Zeile. Die Version mit zwei Spalten behält einfach eine zweite Kennzahl neben der ersten bei.

Warum eine äußere SUMME die falsche Form erzeugt

Diese Formeln berechnen eine Gesamtsumme:

=SUM(B2:C4,F2:G4)

=SUM(B2:C4+F2:G4)

Für dieses Beispiel gibt jede davon 55 zurück. Diese Zahl vermischt alle drei Assets und beide Kennzahlen. Microsoft dokumentiert, dass SUM die als Zahlen, Bezüge oder Bereiche übergebenen Werte addiert; wird das Ergebnisarray in SUM eingeschlossen, wird es daher auf einen Wert reduziert. Microsoft-Dokumentation zu SUM.

Verwenden Sie das oben gezeigte Sechs-Zellen-Ergebnis als Abgleich. Eine einzelne Zelle mit dem Wert 55 beantwortet eine andere Frage, auch wenn die Rechnung korrekt ist.

Ein versionsunabhängiges Layout für ausgerichtete Zeilen

Wenn Sie einzelne Zellformeln bevorzugen, geben Sie =B2+F2 in K2 ein. Ziehen Sie den Ausfüllpunkt nach rechts bis L2 und füllen Sie dann K2:L2 bis Zeile 4 nach unten aus. Relative Bezüge passen sich an, um jedes entsprechende Wertepaar zu berechnen. Microsoft dokumentiert dieses Verhalten des Ausfüllpunkts für Excel für Mac. Excel als Taschenrechner in Excel für Mac verwenden.

So erhalten Sie dieselben sechs Mengen ohne überlaufendes Ergebnis. Die Quellzeilen müssen weiterhin in derselben Reihenfolge stehen.

Gleichen Sie Asset-Bezeichnungen ab, wenn die Quellzeilen unterschiedlich angeordnet sind

Wenn Sie sich nicht auf die Zeilenpositionen verlassen können, löschen Sie zuerst das vorherige Ergebnis aus K2. Belassen Sie die Liste der angeforderten Assets in J2:J4. Geben Sie Folgendes in K2 ein:

=XLOOKUP($J2,$A$2:$A$4,$B$2:$C$4)+XLOOKUP($J2,$E$2:$E$4,$F$2:$G$4)

Jede Suche findet das Asset in J2 und gibt seine beiden Mengenspalten zurück. Beim Addieren der zurückgegebenen Zeilen entstehen 12 in K2 und 5 in L2. Microsoft dokumentiert sowohl die standardmäßige exakte Übereinstimmung als auch die Rückgabe mehrerer Elemente aus einem mehrspaltigen Rückgabebereich. Microsoft-Dokumentation zu XLOOKUP.

Kopieren Sie die Formel aus K2 nach K3:K4 und lassen Sie L2:L4 für die horizontalen Ergebnisse frei. Die Dollarzeichen fixieren die Quellbereiche; das angeforderte Asset ändert sich mit jeder Ausgabezeile.

Ordnen Sie als Diagnose die vollständigen Datensätze der zweiten Quelle neu an, sodass Tile-C an erster Stelle steht. Verschieben Sie jede Bezeichnung zusammen mit beiden Mengen. Die suchbasierte Ausgabe sollte die erwarteten Werte beibehalten, während die positionsbasierte Addition nun unterschiedliche Assets einander zuordnen würde.

Eine fehlende Bezeichnung gibt #N/A zurück, da die Formel keine optionale Ersetzung für fehlende Treffer enthält. Eine wiederholte Bezeichnung gibt den ersten Treffer zurück, statt die Datensätze zusammenzufassen. Dies sind dokumentierte Einschränkungen der Suche. Microsoft-Dokumentation zu XLOOKUP.

Die Ausgabe enthält nur die in J2:J4 aufgeführten Assets. Stellen Sie vor der Verwendung Ihrer eigenen Daten sicher, dass diese angeforderte Liste alle Assets enthält, die Sie aus einem der beiden Lagerorte ausweisen möchten.

Fassen Sie wiederholte Asset-Datensätze zusammen, jeweils eine Kennzahl auf einmal

Wenn mehrere Zeilen absichtlich zum selben Asset gehören, verwenden Sie eine bedingte Summierung. Geben Sie mit den ursprünglichen Beispielbereichen diese vollständige Formel in K2 ein:

=SUMIF($A$2:$A$4,$J2,B$2:B$4)+SUMIF($E$2:$E$4,$J2,F$2:F$4)

Kopieren Sie sie nach rechts in L2 und anschließend K2:L2 nach unten bis Zeile 4. Beim Verschieben nach rechts ändern sich die Mengenbereiche zu den Spalten für Farbmusterkarten, während die Bezeichnungsbereiche fixiert bleiben. Jede Ausgabezelle summiert nur ihr eigenes Asset und ihre eigene Kennzahl. Microsoft dokumentiert die Verwendung eines Bereichs für Kriterien und eines weiteren für die zugehörigen Mengen. Microsoft-Dokumentation zu SUMIF.

Fügen Sie zum Demonstrieren wiederholter Datensätze Tile-A, 2 und 1 in A5:C5 hinzu. Erweitern Sie vor der Neuberechnung die Kriterien- und Mengenbereiche der ersten Quelle bis Zeile 5:

=SUMIF($A$2:$A$5,$J2,B$2:B$5)+SUMIF($E$2:$E$4,$J2,F$2:F$4)

Kopieren Sie die überarbeitete Formel wie zuvor nach rechts und nach unten. Die veranschaulichte Ausgabe für Tile-A wird zu 14 Druckexemplaren und 6 Farbmusterkarten. Ein fester Bereich, der bei Zeile 4 endet, würde den hinzugefügten Datensatz auslassen; die Bereichspflege gehört daher zu dieser Methode.

Achten Sie darauf, dass jeder Kriterienbereich dieselbe Größe und Form wie sein Mengenbereich hat. Beachten Sie außerdem, dass SUMIF * und ? als Platzhalter behandelt; die synthetischen Bezeichnungen hier enthalten keines dieser Zeichen. Microsoft-Dokumentation zu SUMIF.

Beheben Sie Überlauffehler und prüfen Sie das fertige Inventar

Eine überlaufende Formel benötigt Platz für ihre gesamte Ausgabe. Platzieren Sie sie außerhalb einer Excel-Tabelle und bearbeiten oder ersetzen Sie sie über ihre Zelle oben links. Microsoft dokumentiert beide Einschränkungen. Dynamische Arrayformeln und das Verhalten überlaufender Arrays.

Wenn #SPILL! erscheint, prüfen Sie den vorgesehenen Zielbereich auf vorhandene Einträge und verbundene Zellen. Verschieben Sie blockierende Inhalte an eine andere Stelle oder wählen Sie einen leeren Ausgabebereich. Microsoft nennt blockierte Zielbereiche und verbundene Zellen als Ursachen für Überlauffehler. So beheben Sie einen #SPILL!-Fehler.

Prüfen Sie die Mengen auf Texteinträge, wenn die Addition #VALUE! zurückgibt. Microsoft weist darauf hin, dass direkte Addition bei nicht numerischem Text fehlschlagen kann, während SUM Text ignoriert. Wenn Sie die Formel durch SUM ersetzen, ändern sich daher sowohl die Form des Ergebnisses als auch der Umgang mit Text. Microsoft-Dokumentation zu SUM.

Für einen optionalen beschrifteten Ergebnisblock in Microsoft 365 für Mac oder Excel 2024 für Mac verwenden Sie das ursprüngliche ausgerichtete Beispiel in einem neuen, leeren Bereich:

=HSTACK(A2:A4,B2:C4+F2:G4)

Damit werden Asset-Bezeichnungen neben den berechneten Kennzahlen platziert. HSTACK fügt Spalten an; + innerhalb der Formel führt die Addition aus. Die Funktion gleicht keine Bezeichnungen ab und korrigiert keine abweichenden Quellzeilenreihenfolgen. Microsoft-Dokumentation zu HSTACK.

Prüfen Sie zum Abschluss drei Dinge: Die Ausgabe enthält alle vorgesehenen Assets, jede Kennzahl bleibt in einer eigenen Spalte und eine manuell geprüfte Zeile stimmt mit den Quellmengen überein. Verwenden Sie für ausgerichtete Eingaben die Addition von Bereichen. Addieren Sie bei eindeutigen Bezeichnungen in wechselnder Zeilenreihenfolge die Suchergebnisse. Summieren Sie bei wiederholten Datensätzen die Kennzahlen jedes Assets separat. Das fertige Inventar sollte die nötigen Details bewahren, damit erkennbar bleibt, wofür jede Menge steht.

Quellen