Tutorials

Excel-Tutorial: Leere Zellen statt Nullwerte bei SVERWEIS, XVERWEIS & INDEX/VERGLEICH

Melden

Hier ist ein ausführliches Tutorial, wie du in Excel verhinderst, dass Nullwerte (0) oder das Datum 00.01.1900 angezeigt werden, wenn eine Suchfunktion eine leere Zelle findet.

So vermeidest du die Anzeige von "0" oder "01.01.1900" bei leeren Suchergebnissen

Wenn du in Excel mit Suchfunktionen arbeitest, stößt du oft auf ein Problem: Wenn die Quelldatei eine leere Zelle enthält, gibt Excel als Ergebnis eine 0 aus. Wenn diese Zelle dann auch noch als Datum formatiert ist, erscheint das berüchtigte Datum 00.01.1900 (oder 01.01.1900).

In diesem Tutorial lernst du die vier besten Methoden kennen, um stattdessen eine echte "Leere" (Blank) anzuzeigen.

Methode 1: Der "&"-Trick (Die schnellste Lösung für Text)

Dies ist die einfachste Methode, wenn dein Ergebnis als Text behandelt werden kann. Du hängst einfach eine leere Zeichenfolge (&"") an das Ende deiner Formel.

  • SVERWEIS: =SVERWEIS(A2;D:E;2;0)&""
  • XVERWEIS: =XVERWEIS(A2;D:D;E:E)&""
  • INDEX/VERGLEICH: =INDEX(E:E;VERGLEICH(A2;D:D;0))&""

Vorteil: Extrem kurz und effizient. Nachteil: Verwandelt Zahlen in Text. Du kannst mit dem Ergebnis nicht mehr ohne Weiteres rechnen (Summen bilden etc.).

Methode 2: Die WENN-Funktion (Der Klassiker)

Diese Methode prüft, ob das Ergebnis der Suche leer ist. Wenn ja, wird nichts ("") ausgegeben, ansonsten das eigentliche Ergebnis.

Beispiel mit SVERWEIS:

=WENN(SVERWEIS(A2;D:E;2;0)=""; ""; SVERWEIS(A2;D:E;2;0))

Beispiel mit XVERWEIS:

=WENN(XVERWEIS(A2;D:D;E:E)=""; ""; XVERWEIS(A2;D:D;E:E))

Vorteil: Funktioniert in allen Excel-Versionen und bewahrt den Datentyp (Zahl bleibt Zahl). Nachteil: Die Formel wird lang, da die Suche zweimal durchgeführt wird (Performance-Verlust bei sehr großen Tabellen).

Methode 3: Die LET-Funktion (Elegant & Performant)

Wenn du Office 365 oder Excel 2021 besitzt, ist LET die beste Wahl. Hierbei wird das Suchergebnis einer Variable zugewiesen, sodass Excel die Suche nur einmal berechnen muss.

Formel:

=LET(Ergebnis; XVERWEIS(A2;D:D;E:E); WENN(Ergebnis=""; ""; Ergebnis))

Erklärung:

  1. Ergebnis ist der Name der Variable.
  2. XVERWEIS(...) ist die Berechnung, die in der Variable gespeichert wird.
  3. Die WENN-Prüfung am Ende nutzt nur noch den gespeicherten Wert.

Methode 4: Benutzerdefiniertes Zahlenformat (Nur visuell)

Wenn du die 0 in der Zelle behalten möchtest (um damit zu rechnen), sie aber unsichtbar machen willst, ist das Formatieren der Zellen die sauberste Lösung.

  1. Markiere deine Ergebniszellen.
  2. Drücke Strg + 1 (Zellen formatieren).
  3. Wähle unter Kategorie den Punkt Benutzerdefiniert.
  4. Gib in das Feld "Typ" folgendes ein:

Für allgemeine Zahlen: 0;-0;;@ (Erklärung: Positive Zahlen; Negative Zahlen; Nullwerte ausblenden; Text)

Für Datumsangaben (vermeidet 00.01.1900): TT.MM.JJJJ;; (Die zwei Semikolons am Ende sagen Excel: "Zeige nichts an, wenn der Wert Null ist".)

Zusammenfassung: Welche Methode wann?

Situation Empfohlene Methode
Schnelle Lösung für Texte &"" (Trick 1)
Du hast eine alte Excel-Version WENN (Trick 2)
Du hast Excel 365/2021 & große Datenmengen LET (Trick 3)
Du willst mit den Zahlen später rechnen Zahlenformat (Trick 4)

Wichtiger Hinweis zum XVERWEIS: Lass dich nicht vom vierten Argument des XVERWEIS ([wenn_nicht_gefunden]) täuschen. Dieses Argument greift nur, wenn der Suchwert gar nicht existiert. Wenn der Wert gefunden wird, die Zelle daneben aber leer ist, gibt auch der XVERWEIS eine 0 zurück. Hier helfen nur die oben genannten Methoden.

0