Beitrag aus SmartTools Excel Weekly
Textvermerk in Excel-Tabelle bei Übereinstimmung mit externer Liste
Excel 365 2021 2019 2016 2013 2010
FRAGE Ich habe eine große Excel-Tabelle mit ca. 10.000 Datensätzen/Zeilen, – ich nenne Sie mal "Substanz-Tabelle". Diese enthält eine Spalte mit Akten-IDs in Form von Zahlenwerten. Zudem habe ich eine zweite Tabelle mit etwa 1.000 Datensätzen, – sagen wir: "Kontroll-Tabelle", welche ebenfalls eine Spalte mit Akten-IDs enthält, hier aber mit eindeutigen, duplikatfreien Zahlenwerten. Die Akten-IDs aus der Kontroll-Tabelle kommen auch in der Substanz-Tabelle vor, unter Umständen sogar mehrfach. Nun möchte ich in einer separaten Spalte der Substanz-Tabelle einen Vermerk anzeigen lassen, wenn eine Akten-ID aus der Kontroll-Tabelle gefunden wird. Es soll in der Zeile dann zum Beispiel der Text "löschen" oder "Problem-ID" erscheinen. Haben Sie dafür eine Lösung?
M. Fechner
ANTWORT Das lässt sich mit einer Suchfunktion recht einfach umsetzen. In einer Spalte der Substanz-Tabelle müssen Sie dafür Formeln nach folgendem Schema einsetzen:
=WENN(ISTNV(VERGLEICH(<Akten-ID der Zeile>;
<Name des Kontroll-Tabellenblatts>!$
<Bereich der Akten-IDs im Kontroll-Tabellenblatt>;0));
"";"<Vermerktext>")
Gehen wir für ein konkretes Beispiel von einer kleineren Datenmenge aus. Die Kontroll-Tabelle enthält hier nur 9 Datensätze mit den Akten-IDs "1111", "2222" bis "9999", die im Bereich A2:A10 stehen. Das Tabellenblatt hat den Namen "Kontrolle".
Die Substanz-Tabelle im Tabellenblatt "Substanz" enthält mehr Datensätze. In Spalte A stehen ebenfalls Akten-IDs, wobei die erste ID in Zelle A2 steht. Neben den IDs aus der Kontroll-Tabelle finden sich hier aber auch andere Nummern.
Nun soll in Spalte C der Vermerk "Problem" ausgegeben werden, wenn die Akten-ID der aktuellen Zeile in der Kontroll-Tabelle enthalten ist.
Dazu geben Sie in Zelle C2 (also in der ersten Zeile mit Daten) folgende Formel ein:
=WENN(ISTNV(VERGLEICH(A2;Kontrolle!$A$2:$A$10;0));"";"Problem")
Anschließend kopieren Sie die Formel nach unten bis zum letzten Datensatz.
Mittels VERGLEICH-Funktion sucht die Formel im Bereich der Akten-IDs aus der Kontroll-Tabelle nach der Akten-ID aus der aktuellen Zeile der Substanz-Tabelle. Beachten Sie, dass der Suchbereich in der Kontroll-Tabelle als absoluter Bezug eingegeben wurde ($A$2:$A$10). So lässt sich die Formel problemlos kopieren, ohne dass sich der Suchbereich ändert.
Wenn die VERGLEICH-Funktion die Akten-ID findet, liefert sie die Zeilennummer im Suchbereich. Ansonsten gibt sie den Fehlerwert #NV aus. Deshalb überprüfen Sie in der Suchformel mit Hilfe der Funktion ISTNV, ob das Ergebnis der VERGLEICH-Funktion ein #NV-Fehler ist. In diesem Fall wird durch die WENN-Funktion nichts (die leere Zeichenfolge "") ausgegeben. Aber sofern ein Treffer vorliegt und VERGLEICH einen Zahlenwert liefert, gibt die WENN-Funktion den Text "Problem" aus.
Was in diesem Beispiel mit einer kleinen Datenmenge funktioniert, führt natürlich auch in Ihren großen Tabellen zu den gewünschten Ergebnissen. Sie müssen dafür nur die Adresse des Suchbereichs anpassen.