Verwenden Sie FILTER, um alle Mengen zu einer Grafik-Asset-Bezeichnung in Excel für Mac zurückzugeben. Das resultierende Array kann in Arbeitsblattzellen überlaufen, in eine weitere Berechnung einfließen oder summiert werden, ohne dass wiederholte passende Einträge verloren gehen.

Diese Anleitung verwendet synthetische Bezeichnungen und Mengen für Grafik-Assets. Ziel ist es, eine Bezeichnung auszuwählen, alle zugehörigen Mengen abzurufen und diese einzelnen Zahlen in einer Berechnung weiterzuverwenden. Microsoft dokumentiert FILTER als Funktion, die anhand angegebener Kriterien ein Array zurückgibt. FILTER-Funktion.

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

In der aktuellen Funktionsdokumentation von Microsoft werden Excel für Microsoft 365 für Mac, Excel 2024 für Mac und Excel 2021 für Mac ausdrücklich als Versionen aufgeführt, die FILTER unterstützen. Verwenden Sie für die folgenden Formeln eine dieser Editionen. FILTER-Funktion.

Um Ihre Installation zu identifizieren, öffnen Sie Excel und wählen Sie Excel → Über Excel. Microsoft dokumentiert diesen Menüpfad auf dem Mac. In der Anleitung zur Versionsbestimmung wird erklärt, dass im Dialogfeld „Über“ der Mac-App die Versionsnummer und der Lizenztyp angezeigt werden. Prüfen Sie neben der Buildnummer auch die Edition. Dokumente mit anderen Versionen von Office für Mac teilen; Über Office: Welche Office-Version verwende ich?.

Excel 2019 für Mac und Excel 2016 für Mac gehören nicht zu den Versionen, für die FILTER dokumentiert ist. Wenn Sie eine dieser Editionen verwenden, besteht der unterstützte Weg für diesen dynamischen Array-Workflow darin, die Arbeitsmappe in einer der aufgeführten Mac-Editionen zu öffnen. Die folgenden Anweisungen setzen diese Voraussetzung voraus; sie sind keine Ausweichlösung mit einer älteren Matrixformel.

Ein kleines Beispiel für Asset-Mengen einrichten

Geben Sie die folgenden Daten in einem leeren Arbeitsblatt in A1:B7 ein. Die erste Zeile enthält Überschriften. Geben Sie die Mengen als Zahlen ein, einschließlich der Null in B6.

Arbeitsblattzeile Spalte A: Asset Spalte B: Menge
1 Asset Menge
2 Tile A 4
3 Arc B 7
4 Tile A 2
5 Dot C 5
6 Tile A 0
7 Tile A 4

Geben Sie Ausgewähltes Asset in D1 und Tile A in D2 ein. Geben Sie Passende Mengen in F1 ein. Lassen Sie die Zellen unter F2 leer, damit das Ergebnis genügend Platz zum Ausweiten hat.

Dieses Beispiel enthält absichtlich zwei Mengen mit dem Wert vier und eine Menge mit dem Wert null für dieselbe Bezeichnung. Ein korrektes Ergebnis muss alle vier passenden Zeilen beibehalten: 4, 2, 0 und 4. Wiederholte Werte zu entfernen oder null auszuschließen, würde das angeforderte Ergebnis verändern.

Mit FILTER alle passenden Mengen zurückgeben

Wählen Sie F2, geben Sie die folgende Formel ein und drücken Sie Return:

=FILTER(B2:B7,A2:A7=D2)

Die Formel verwendet B2:B7 als Mengenarray und A2:A7=D2 als Einschlussbedingung. Diese Bereiche umfassen dieselben sechs Quelldatenzeilen und erfüllen damit die von Microsoft angegebene Anforderung, dass das Einschlussarray eine passende Höhe oder Breite hat. FILTER-Funktion.

Bei den Beispieldaten belegt das Ergebnis den Bereich F2:F5:

Zelle Ergebnis
F2 4
F3 2
F4 0
F5 4

Geben Sie die Formel nur in F2 ein; Excel trägt die zusätzlichen Ergebnisse automatisch in benachbarte Zellen ein. Microsoft bezeichnet dieses Verhalten als Überlaufen. Um die Formel später zu ändern, bearbeiten Sie die Zelle oben links im Ergebnisbereich, hier also F2. Dynamische Arrayformeln und Verhalten überlaufender Arrays.

Die beiden Vorkommen der Vier stehen für separate passende Quellzeilen. Behalten Sie beide bei, wenn Ihre nächste Berechnung jedes Vorkommen des ausgewählten Assets berücksichtigen muss.

Warum XLOOKUP diese vier Zeilen nicht abruft

Microsoft beschreibt XLOOKUP als Funktion, die das Element zurückgibt, das dem ersten gefundenen Treffer entspricht. Sie kann mehrere Spalten aus einer passenden Zeile zurückgeben, listet damit aber nicht alle Zeilen auf, die dieselbe Bezeichnung enthalten. XLOOKUP-Funktion.

Die folgende Formel gibt beispielsweise nur 4 aus dem Beispiel zurück:

=XLOOKUP(D2,A2:A7,B2:B7)

Dieses Ergebnis ist passend, wenn eine passende Zeile die Antwort liefert. Bei dieser Aufgabe liefern vier separate passende Zeilen Mengen, daher verwenden Sie das Ergebnis von FILTER.

Das Array in weiteren Berechnungen wiederverwenden

Verweisen Sie mit F2# auf das gesamte Ergebnis. Microsoft dokumentiert das Rautezeichen als Operator für überlaufende Bereiche: Es verweist auf das gesamte Array, das von der Startzelle ausgegeben wird. So muss eine nachgelagerte Formel nicht auf den aktuellen Bereich mit vier Zellen festgelegt werden. Richtlinien und Beispiele für Matrixformeln.

Platzieren Sie nachgelagerte Formeln neben dem Ergebnis und nicht direkt darunter, damit Platz für weitere passende Mengen bleibt. Die folgenden Beispiele verwenden die Spalten H bis J.

Alle passenden Mengen addieren

Geben Sie Gesamtmenge in H1 ein und anschließend diese Formel in H2:

=SUM(F2#)

Das beispielhafte Ergebnis ist 10: vier plus zwei plus null plus vier. SUM verarbeitet Bereiche und addiert deren Zahlenwerte. SUM-Funktion.

Wenn Sie nur die Summe benötigen, können Sie sie stattdessen direkt in einer anderen leeren Zelle berechnen:

=SUM(FILTER(B2:B7,A2:A7=D2))

Wählen Sie das sichtbare Array, wenn Sie die einzelnen Mengen prüfen oder weiterverwenden möchten. Verwenden Sie die verschachtelte Formel, wenn das Arbeitsblatt nur die Gesamtsumme benötigt.

Jede Menge mit einem gemeinsamen Faktor multiplizieren

Angenommen, für die Illustration sind drei Kopien jeder passenden Menge erforderlich. Geben Sie Kopien in I1 und die Zahl 3 in I2 ein. Geben Sie Angepasste Mengen in J1 ein und anschließend diese Formel in J2:

=F2#*$I$2

Die erwarteten Ergebnisse in J2:J5 sind 12, 6, 0 und 12. Microsoft dokumentiert Matrixberechnungen, die Werte multiplizieren und mehrere Ergebnisse zurückgeben. Im Mac-Rechnerleitfaden wird das Sternchen als Multiplikationsoperator angegeben. Richtlinien und Beispiele für Matrixformeln; Excel für Mac als Taschenrechner verwenden.

Für eine einzelne angepasste Summe geben Sie Folgendes in H4 ein:

=SUM(F2#*$I$2)

Die erwartete Summe beträgt 30. Dies sind beispielhafte Berechnungen mit den bereitgestellten synthetischen Daten. Die Berechnungen wurden unabhängig überprüft; die Arbeitsmappenformeln wurden mit der Dokumentation von Microsoft abgeglichen, aber nicht in Excel für Mac ausgeführt.

Festlegen, was bei keinen Treffern geschehen soll

Ändern Sie D2 in Wave D, eine Bezeichnung, die im Beispiel nicht vorkommt. Die ursprüngliche Formel FILTER mit zwei Argumenten gibt #CALC! zurück, weil Excel kein leeres Array zurückgeben kann. Microsoft dokumentiert das optionale dritte Argument als Möglichkeit, einen Wert für den Fall ohne Ergebnis anzugeben. So beheben Sie einen #CALC!-Fehler.

Wählen Sie den Wert danach aus, was die nächste Berechnung benötigt. Die folgende Entscheidungshilfe gilt für dieses Arbeitsblatt:

Anforderung Formel oder Vorgehensweise Folge
Fehlende Treffer sollen bei Berechnungen sichtbar bleiben =FILTER(B2:B7,A2:A7=D2) beibehalten Keine Treffer führen zu #CALC! und damit zu einer Überprüfung.
Das Ergebnis soll vor allem als Liste lesbar sein =FILTER(B2:B7,A2:A7=D2,"No matches") verwenden Bei keinen Treffern wird eine Textmeldung zurückgegeben.
Eine nicht vorhandene Bezeichnung soll bei der Summierung als null eingehen =SUM(FILTER(B2:B7,A2:A7=D2,0)) verwenden Bei keinen Treffern ergibt die Summe null.

Ein Ersatzwert von null ist eine bewusste Berechnungsregel. Er bedeutet nicht, dass eine passende Quellzeile mit der Menge null vorhanden ist. In diesem Beispiel hat Tile A tatsächlich die Menge null; für Wave D gibt es keine Quellzeilen. Bewahren Sie diesen Unterschied, wann immer es darauf ankommt, ob ein Asset vorhanden ist.

Auch bei einer Textmeldung ist Vorsicht geboten: Microsoft dokumentiert, dass SUM Text in referenzierten Zellen ignoriert. Wenn Sie daher ein Ergebnis, das nur Keine Treffer enthält, summieren, kann null herauskommen, wodurch das Fehlen passender Zeilen verborgen wird. Verwenden Sie die sichtbare Fehlermeldung, wenn nachgelagerte Berechnungen bei einer fehlenden Bezeichnung angehalten werden sollen. SUM-Funktion.

Setzen Sie D2 wieder auf Tile A, bevor Sie mit den ursprünglichen Zahlenbeispielen fortfahren.

Eine zweite Bedingung nur hinzufügen, wenn die Aufgabe es erfordert

Wenn eine spätere Aufgabe ausdrücklich positive Mengen verlangt, verwenden Sie sowohl die Bezeichnungsprüfung als auch eine Prüfung auf positive Mengen:

=FILTER(B2:B7,(A2:A7=D2)*(B2:B7>0))

Für das Beispiel gibt diese Formel 4, 2 und 4 zurück. Microsoft dokumentiert die Multiplikation von Bedingungen in FILTER als UND-Prüfung. Diese Variante schließt die passende Null absichtlich aus. Behalten Sie daher die ursprüngliche Formel bei, wenn Sie jede passende Menge benötigen. FILTER-Funktion.

Ergebnis überprüfen und neue Zeilen einbeziehen

Wenn die Formel nicht die erwarteten vier Mengen zurückgibt, prüfen Sie das Arbeitsblatt in dieser Reihenfolge:

  1. Ausgabebereich prüfen. Ein blockierter Ergebnisbereich führt zu #SPILL!. Verschieben Sie benötigte Inhalte aus dem vorgesehenen Überlaufbereich und versuchen Sie es erneut.
  2. Formelposition prüfen. Überlaufende Arrayformeln müssen im Arbeitsblatt außerhalb einer Excel-Tabelle stehen.
  3. Arbeitsmappenabhängigkeiten prüfen. Microsoft unterstützt verknüpfte dynamische Arrays zwischen Arbeitsmappen nur, solange beide Arbeitsmappen geöffnet sind. Wird die Quelldatei geschlossen, kann eine Aktualisierung zu #REF! führen.

Diese Einschränkungen sind unter Dynamische Arrayformeln und Verhalten überlaufender Arrays dokumentiert.

Prüfen Sie als Nächstes die Quellbezeichnungen und Mengen. Vergleichen Sie D2 mit den vorgesehenen Assetzellen und kontrollieren Sie, ob die Mengen als Zahlen eingegeben wurden. Eine plausible Summe allein reicht zur Überprüfung nicht aus, da SUM Textwerte ignorieren kann. SUM-Funktion.

Die Beispielformeln verweisen auf die Quellzeilen 2 bis 7. Wenn Sie Daten in Zeile 8 anfügen, bearbeiten Sie beide Quellbereiche, damit sie die neue Zeile einschließen. Geben Sie beispielsweise Tile A in A8 und die numerische Menge 3 in B8 ein und ersetzen Sie die Formel in F2 durch:

=FILTER(B2:B8,A2:A8=D2)

Das erwartete Array lautet dann 4, 2, 0, 4, 3, und die Summe beträgt 13. Der Bezug F2# folgt dem erweiterten Ergebnis, kann aber nicht dafür sorgen, dass die ursprünglichen Quellbereiche eine ausgelassene Zeile einbeziehen.

Für eine Liste, die häufig wächst, empfiehlt Microsoft eine Excel-Tabelle mit strukturierten Verweisen, die sich beim Hinzufügen oder Entfernen von Tabellenzeilen anpassen. Platzieren Sie die überlaufende Ausgabe außerhalb dieser Tabelle. Dynamische Arrayformeln und Verhalten überlaufender Arrays.

Prüfen Sie zum Abschluss die einzelnen Ergebnisse, bevor Sie sich auf die Summe verlassen: Bei den ursprünglichen sechs Quellzeilen sollte die Auswahl von Tile A vier Mengen zurückgeben, darunter beide Vierer und die Null. Sobald das stimmt, verwenden Sie F2# für Berechnungen und wählen Sie eine ausdrückliche Regel für den Fall ohne Treffer, die zum Zweck des Arbeitsblatts passt.

Quellen