Verwenden Sie IFNA um eine verschüttete Suche, um deren #N/A-Ergebnisse mithilfe einer zweiten Referenztabelle zu ergänzen. Dieses Beispiel für Excel für Mac gleicht Asset-Bezeichnungen ab, behält bereits in der primären Tabelle gefundene Mengen bei und kennzeichnet eindeutig Bezeichnungen, die in beiden Tabellen fehlen.

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

Verwenden Sie Excel für Microsoft 365 für Mac, Excel 2024 für Mac oder Excel 2021 für Mac. Microsoft führt diese Editionen sowohl für XLOOKUP als auch für IFNA auf. In der Dokumentation zu XLOOKUP werden Excel 2016 und Excel 2019 ausdrücklich ausgeschlossen, obwohl diese Editionen in der umfassenderen Liste der unterstützten Versionen auf der Seite aufgeführt sind. Die folgenden Formeln sind für die unterstützten Mac-Editionen vorgesehen. XLOOKUP-Funktion, IFNA-Funktion

Die Aufgabe ist klar umrissen: Sie haben eine Spalte mit angeforderten Asset-Bezeichnungen und möchten für jede Bezeichnung eine Menge. Die primäre Referenz hat Vorrang; die zweite Referenz liefert nur dann Mengen, wenn die primäre Suche #N/A ergibt. Alle Bezeichnungen und Mengen im Folgenden sind fiktiv.

Zwei Referenztabellen und eine Liste angeforderter Assets einrichten

Beginnen Sie in einer Arbeitsmappe mit einem leeren Arbeitsblatt. Verwenden Sie die unten angegebenen Zellbereiche, damit die Formeln ohne Änderungen an den Bezügen funktionieren. Diese kleinen Referenztabellen sind gewöhnliche Arbeitsblattbereiche; Sie müssen sie nicht in Excel-Tabellenobjekte umwandeln.

Geben Sie die primäre Referenz in D1:E4 ein:

Zelle in Spalte D Asset-Bezeichnung Zelle in Spalte E Menge
D1 Asset E1 Menge
D2 Blattkontur E2 8
D3 Punkttextur E3 0
D4 Kachelmuster E4 12

Geben Sie die zweite Referenz in G1:H5 ein:

Zelle in Spalte G Asset-Bezeichnung Zelle in Spalte H Menge
G1 Asset H1 Menge
G2 Punkttextur H2 9
G3 Kurvenskizze H3 5
G4 Körnungstextur H4 3
G5 Blattkontur H5 10

Geben Sie Angefordertes Asset in J1 ein und tragen Sie dann diese fünf Bezeichnungen in J2:J6 ein, eine pro Zelle und in dieser Reihenfolge:

  1. Blattkontur
  2. Punkttextur
  3. Kurvenskizze
  4. Körnungstextur
  5. Bogenmotiv

Geben Sie Mengen als Zahlen ein und füllen Sie jede Mengenzelle in den beiden Referenzen aus. In diesem Beispiel bedeutet null eine erfasste Menge von null. Es bedeutet nicht, dass das Asset fehlt.

Die verschüttete Bezeichnungsliste erstellen

Geben Sie die Überschriften Asset, Primäre Menge und Ergänzte Menge in A1, B1 und C1 ein. Lassen Sie A2:C20 leer und geben Sie dann diese Formel in A2 ein:

=FILTER(J2:J20,J2:J20<>"","")

Damit werden leere Zellen in der angeforderten Liste herausgefiltert. Mit den bereitgestellten Daten werden die fünf Asset-Bezeichnungen in A2:A6 ausgegeben. Microsoft dokumentiert FILTER für die drei oben aufgeführten Mac-Editionen und erklärt, dass das letzte Argument ein Ergebnis liefert, wenn keine Zeilen die Bedingung erfüllen. Hier ist dieses Ergebnis eine leere Zeichenfolge. FILTER-Funktion

Die späteren Formeln verwenden A2#, um auf den gesamten Ausgabebereich zu verweisen. Der Operator # folgt der Größe des Ausgabebereichs, sodass der aktuelle Endpunkt nicht fest als A6 codiert werden muss. Microsoft beschreibt dieses Verhalten in der Dokumentation zum Operator für verschüttete Bereiche.

Die fehlenden Mengen aus der zweiten Referenz ergänzen

Erstellen Sie zunächst die primäre Suche in B2:

=XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4)

Damit wird die primäre Asset-Spalte durchsucht und die jeweils zugehörige Menge zurückgegeben. XLOOKUP verwendet standardmäßig einen exakten Abgleich und gibt #N/A zurück, wenn keine Übereinstimmung gefunden und kein alternatives Ergebnis angegeben wird. XLOOKUP-Funktion

Für das Beispiel sollte B2:B6 8, 0, #N/A, #N/A und #N/A enthalten. Lassen Sie diese Fehler für fehlende Übereinstimmungen für den nächsten Schritt bestehen.

Geben Sie nun die Ergänzungsformel in C2 ein:

=IFNA(B2#,XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed"))

IFNA prüft den primären Ausgabebereich. An den Positionen mit #N/A liefert die Formel das entsprechende Ergebnis aus der zweiten Suche. Alle anderen Ergebnisse der primären Suche bleiben unverändert. Microsoft dokumentiert ausdrücklich, dass IFNA ein Ergebnisarray zurückgibt, wenn das erste Argument eine Arrayformel ist. IFNA-Funktion

Das erwartete Ergebnis auf dem Arbeitsblatt lautet:

Asset in Spalte A Primäre Menge in Spalte B Ergänzte Menge in Spalte C
Blattkontur 8 8
Punkttextur 0 0
Kurvenskizze #N/A 5
Körnungstextur #N/A 3
Bogenmotiv #N/A Nicht aufgeführt

Diese Zeilen prüfen die Vorrangregel. Blattkontur bleibt 8, obwohl die zweite Referenz 10 enthält. Punkttextur bleibt 0, obwohl die zweite Referenz 9 enthält. Kurvenskizze und Körnungstextur erhalten ihre sekundären Mengen. Bogenmotiv bleibt sichtbar ungelöst, weil es in keiner der beiden Referenzen vorkommt.

Beide Arrays müssen dieselben angeforderten Assets in derselben Reihenfolge beschreiben. In dieser Einrichtung stammen beide aus A2#. Wenn Sie eine vorhandene Arbeitsmappe anpassen, kombinieren Sie kein primäres Ergebnis, das auf eine Weise sortiert ist, mit einer Ersatzsuche, die auf einer anders sortierten Bezeichnungsliste beruht.

Eine Formel verwenden, wenn Sie keine Zwischenspalte benötigen

Um die primäre Suche und die Ergänzung zusammenzufassen, ersetzen Sie die Formel in C2 durch diese vollständige Alternative:

=IFNA(
    XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4),
    XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed")
)

Mit denselben Beispieldaten sollte damit dasselbe Ergebnis mit den fünf ergänzten Mengen entstehen. Spalte B wird für die Berechnung dann nicht mehr benötigt. Wenn Sie die Arbeitsmappe einrichten, lässt sich anhand der zweistufigen Variante leichter erkennen, aus welcher Referenz das jeweilige Ergebnis stammt.

Feststellen, ob ein Ergebnis tatsächlich fehlt

Prüfen Sie vor dem Anpassen der Formel, was die erste Suche zurückgibt. Eine fehlende Bezeichnung, ein leer wirkendes Ergebnis und eine Menge von null erfordern unterschiedliche Entscheidungen.

Primäres Ergebnis Was diese Ergänzungsformel tut Was zu prüfen ist
Numerische Menge, einschließlich 0 Behält sie bei Prüfen Sie, ob dies die gewünschte primäre Menge ist.
#N/A Verwendet die sekundäre Suche Prüfen Sie, ob der Fehler eine nicht vorhandene Bezeichnung bedeutet.
Leere Zeichenfolge, "" Behält sie bei Prüfen Sie, ob die primäre Formel fehlende Übereinstimmungen ausblendet.
Ein anderer Fehler Lässt ihn sichtbar Korrigieren Sie die zugrunde liegende Formel oder Referenz.

Wenn Ihre bestehende primäre Suche beispielsweise "" als Argument if_not_found verwendet, kann IFNA diese leere Zeichenfolge nicht als #N/A erkennen. Entfernen Sie dieses Argument aus der primären Suche, wie in B2 gezeigt, oder verwenden Sie die kombinierte Formel oben. Die Ergänzungslogik setzt voraus, dass ein erkennbares Ergebnis für eine fehlende Übereinstimmung erhalten bleibt. XLOOKUP-Funktion, IFNA-Funktion

Prüfen Sie auch die Mengen in den Referenzen selbst. IFNA reagiert auf einen Fehlerwert; die Funktion prüft nicht, ob eine Mengenzelle ausgefüllt wurde. Wenn für einen gefundenen primären Datensatz die Menge noch nicht vorliegt, vervollständigen Sie diesen Datensatz oder legen Sie eine separate Regel für noch nicht ausgefüllte Mengen fest, bevor Sie diese Vorgehensweise anwenden.

Es gibt noch einen weiteren Unterschied: Ein vorhandener #N/A-Fehler in den primären Mengendaten löst ebenfalls diese Ersetzung aus. Für dieses Beispiel müssen die Mengenspalten saubere Zahlen enthalten, wobei #N/A nur durch eine erfolglose Suche entsteht. Prüfen Sie diese Voraussetzung, bevor Sie jede Ersetzung als fehlendes Asset einstufen.

Vorrang prüfen und den Ausgabebereich funktionsfähig halten

Verwenden Sie die fünf Zeilen des Beispiels als kurze Abnahmeprüfung, bevor Sie eigene Asset-Bezeichnungen einsetzen. Zusammen prüfen sie eine primäre Übereinstimmung, einen gültigen Nullwert, zwei sekundäre Übereinstimmungen und eine Bezeichnung, die in beiden Referenzen fehlt.

Ändern Sie für eine zusätzliche Prüfung vorübergehend die sekundäre Menge für Blattkontur von 10 in 14. Die ergänzte Menge sollte 8 bleiben. Ändern Sie dann die sekundäre Menge für Kurvenskizze von 5 in 6; die ergänzte Menge sollte sich auf 6 ändern. Stellen Sie anschließend die ursprünglichen Werte wieder her. Dies sind beispielhafte Prüfungen für Ihre Arbeitsmappe und keine berichteten Excel-Testergebnisse.

Verwenden Sie in jeder Referenz pro Asset-Bezeichnung nur einen Datensatz. XLOOKUP durchsucht normalerweise vom ersten Eintrag an und gibt den ersten Treffer zurück. Daher sollten Sie nicht erwarten, dass wiederholte Bezeichnungen ihre Mengen zusammenführen. Klären Sie zuerst, was doppelte Bezeichnungen bedeuten, bevor Sie sich auf diese Suchmethode verlassen. XLOOKUP-Funktion

Einen blockierten Ausgabebereich freigeben

Geben Sie jede Formel mit dynamischer Arrayausgabe nur in ihrer Startzelle ein. Excel füllt die angrenzenden Zellen automatisch aus, und nur die Zelle oben links im Ausgabebereich lässt sich bearbeiten. Platzieren Sie die Formeln außerhalb von Excel-Tabellenobjekten. Microsoft unterstützt Tabellen als Quelldaten, aber keine Formeln mit dynamischer Arrayausgabe innerhalb solcher Tabellen. Formeln mit dynamischen Arrays und Verhalten verschütteter Arrays

Wenn #SPILL! angezeigt wird, wählen Sie die Formelzelle aus, um die vorgesehene Grenze des Ausgabebereichs einzublenden. Verwenden Sie nach Möglichkeit in der Fehlerbenachrichtigung die Option Blockierende Zellen auswählen und verschieben Sie vorhandene Einträge, die das Ergebnis blockieren. Auch verbundene Zellen können die Ausgabe verhindern. Verschieben Sie die Ausgabe oder heben Sie die Verbindung der betroffenen Zellen auf. So beheben Sie einen #SPILL!-Fehler

Eingabebereiche gezielt erweitern

Die Formel für die angeforderte Liste untersucht J2:J20. Innerhalb dieses Bereichs hinzugefügte Bezeichnungen werden automatisch einbezogen; bei Bezeichnungen darunter müssen Sie den Bereich in der Formel erweitern. Ebenso verwenden die Referenzsuchen feste Bereiche, die in den Zeilen 4 und 5 enden. Erweitern Sie beim Hinzufügen von Datensätzen jeweils den Asset-Bereich und den zugehörigen Mengenbereich gemeinsam.

Bei größeren Arbeitsblättern können Excel-Tabellen mit strukturierten Verweisen dafür sorgen, dass sich Quellbereiche beim Hinzufügen oder Entfernen von Zeilen automatisch anpassen. Platzieren Sie die Formeln für die Ergebnisse außerhalb dieser Tabellen im Arbeitsblatt. Formeln mit dynamischen Arrays und Verhalten verschütteter Arrays

Lassen Sie die Eingaben dieses Beispiels in derselben Arbeitsmappe. Microsoft dokumentiert eine eingeschränkte Unterstützung dynamischer Arrays zwischen Arbeitsmappen: Verknüpfte dynamische Arrays erfordern, dass beide Arbeitsmappen geöffnet bleiben. Wird nach dem Schließen der Quelldatei aktualisiert, kann #REF! auftreten. Formeln mit dynamischen Arrays und Verhalten verschütteter Arrays

Sobald die erwarteten Zeilen übereinstimmen, verwenden Sie die ergänzte Spalte als Ergebnis für die Asset-Mengen. Behalten Sie die Regel bei, dass die primäre Referenz Vorrang hat, erhalten Sie aussagekräftige Nullwerte und kennzeichnen Sie Bezeichnungen, die in beiden Referenzen fehlen, weiterhin mit Nicht aufgeführt, damit Sie die Referenzdaten gezielt vervollständigen können.

Quellen