Excel

Fehlende Werte suchen und abrufen

Find Retrieve Missing Values

Excel-Formel: Suchen und Abrufen fehlender WerteAllgemeine Formel |_+_| Zusammenfassung

Um zwei Listen zu vergleichen und fehlende Werte von einer Liste in die andere zu ziehen, können Sie eine Matrixformel verwenden, die auf basiert INDEX und SPIEL . Im gezeigten Beispiel befindet sich der letzte Wert in Liste B in Zelle D11. Die nach unten kopierte Formel in D12 lautet:





= INDEX (complete, MATCH (TRUE, ISNA ( MATCH (complete, partial_expanding,0)),0))

wo 'vollständig' ist benannter Bereich B5: B15.

Hinweis: Dies ist ein Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.





Wenden Sie das Unterstreichungsformat für die doppelte Abrechnung auf die ausgewählten Zellen an.
Erläuterung

Von innen nach außen arbeitend, ist der Kern dieser Formel die innere MATCH-Ausdruck:

Excel-Prüfung, ob der Wert zwischen zwei Zahlen liegt
 
= INDEX (complete, MATCH (TRUE, ISNA ( MATCH (complete,$D:D11,0)),0))

Hier wird die MATCH-Funktion verwendet, um alle 'vollständigen' Werte mit der Teilliste zu vergleichen. Der benannte Bereich 'complete' wird für Lookup-Werte verwendet, und die partielle Liste wird als Lookup-Array verwendet. Beachten Sie jedoch, dass die Teilliste als ein . eingegeben wird Erweiterung des Sortiments das endet 'eine Zelle über' der Formelzelle. Dadurch kann die Teilliste erweitert werden, um neue Werte aufzunehmen, die unter der ursprünglichen Liste erscheinen.



Das Ergebnis von MATCH ist ein Array von Zahlen und #N/A-Fehlern, wobei Zahlen Werte in der vollständigen Liste darstellen, die in der Teilliste vorhanden sind, und Fehler fehlende Werte darstellen:

 
 ISNA ( MATCH (complete,$D:D11,0)

Die ISNA-Funktion wird verwendet, um diese Ergebnisse in ein Array von TRUE- und FALSE-Werten umzuwandeln. In diesem Array entspricht TRUE fehlenden Werten und FALSE entspricht vorhandenen Werten:

 
{1#N/A23#N/A456#N/A7#N/A}

Die ISNA-Funktion gibt dieses Array an die äußere MATCH als Lookup-Array. Die MATCH-Funktion gibt immer die erste gefundene Übereinstimmung zurück, also gibt match die Position (Zeile) des ersten gefundenen fehlenden Werts zurück. Dieses Ergebnis wird zurückgegeben an INDEX als Zeilennummer, wobei der benannte Bereich 'complete' als Array bereitgestellt wird.

In Zelle D12 ist der erste gefundene fehlende Wert 'kiwi' in Zeile 2, also haben wir:

Excel, wie man ein Streudiagramm macht
 
{FALSETRUEFALSEFALSETRUEFALSEFALSEFALSETRUEFALSETRUE}

In D13 ist „kiwi“ jetzt in der expandierenden Referenz enthalten, sodass der erste fehlende Wert „Birne“ ist:

 
= INDEX (complete,2) // returns 'kiwi'

Und so weiter. Sobald alle fehlenden Werte hinzugefügt wurden, gibt die Formel den Fehler #N/A zurück.

Autor Dave Bruns


^