Zuletzt aktualisiert am 13. Juli 2023 von Lars
SVERWEIS ist eine der wichtigsten Funktionen in Excel. In diesem Tutorial schauen wir uns die Funktion SVERWEIS() an. Mit ihr kannst du anhand eines Wertes eine Zeile in einer bestehenden Tabelle auswählen.
Zusätzlich werfen wir auch einen Blick auf die Formel WENNFEHLER(), die du bei der Nutzung von SVERWEIS ebenso kennen solltest und lernen auch noch, wie wir die Nullwerte auf einem Excel-Blatt ausblenden.
Inhaltsverzeichnis
Die Excel-Funktion SVERWEIS()
Das Video wird von YouTube eingebettet und erst beim Klick auf den Play-Button geladen. Es gelten die Datenschutzerklärungen von Google.
Am einfachsten erlernst du die Funktion mit einem Beispiel. Erstelle dir die folgende Tabelle:
Stellen wir uns hierzu einen einfachen Campingplatz vor, der nur diese Positionen verrechnet.
Nun wäre es gut, wenn wir hier ein Formular machen könnten, indem wir durch Eingabe der Menge und der Artikelnummer eine Rechnung zusammenstellen können. Dazu müssten wir in dem Bereich A4:C8 nachschauen, welche Beschreibung und welcher Preis zu der Artikelnummer gehört. Und genau dazu können wir SVERWEIS() verwenden.
Für das einfachere Verständnis machen wir das auf dem gleichen Blatt. Mehr zu Formularen findest du in diesem Artikel: Excel Formulare erstellen [Tutorial mit Video].
Wir bauen nun die Rechnung. In der ersten Spalte kommt die Menge, in die zweite die Artikelnummer. In die dritte kommt unsere SVERWEIS() Formel.
Die Formel lautet also:
=SVERWEIS(F3;A4:C8;2)
F3 ist der Wert, anhand wir die passenden Wert aus unserer Tabelle (A4:C8) heraussuchen. A4:C8 ist die Tabelle, in der wir nachsehen. 2 ist die Spalte, aus der wir den Wert zurückgeben.
SVERWEIS() nimmt also 1001, den Wert in F3. Als Nächstes "schaut" es in der durch A4:C8 definierten Tabelle immer in der ersten Spalte nach. Findet es dort den Wert, liefert es in der gleichen Zeile die zweite Spalte zurück.
SVERWEIS() kennt noch einen vierten Parameter. Dieser ist optional, muss also nicht angegeben werden.
Verwenden kannst du
- WAHR oder 1 für die ungenaue Suche
- FALSCH oder 0 für die genaue Suche
Standard ist leider die ungenaue Suche, weswegen du am besten immer die 0 hinten dran hängst.
Damit lautet die Formel jetzt:
=SVERWEIS(F3;A4:C8;2;0)
In der nächsten Spalte brauchen wir auch noch den Preis. Vermeintlich funktioniert das ganze, wenn wir die Formel einfach eins nach rechts verschieben:
Auch wenn wir das ganze jetzt nach unten ziehen, scheint das zu klappen und wir können den Endpreis durch Eingabe einer Formale berechnen, mit der wir Menge mit Einzelpreis multiplizieren:
Doch sobald wir nun die Eingabe der Rechnung anpassen, geht etwas schief:
Was ist hier los?
Durch die Verschiebung der Formel hat es auch die Werte der ursprünglichen Quelltabelle verschoben. Und diese enthält nun nicht mehr alle nötigen Daten.
Wir bekommen das hin, indem wir die Formel in G3 so anpassen:
=SVERWEIS($F3;$A$4:$C$8;2;0)
Durch die $ werden die Werte auch bei einer Verschiebe-Aktion nicht mehr entsprechend angepasst. SVERWEIS() "schaut" also immer in der gleichen Tabelle an.
Auch vor dem F setzen wir ein $:
=SVERWEIS($F3;$A$4:$C$8;2;0)
Damit erreichen wir, dass die Artikelnummer in der Spalte F verwendet wird. Da vor der 3 kein $ ist, können wir aber die Formel nach unten ziehen.
In H3 müssen wir die Formel dann jedoch manuell eintragen oder zumindest anpassen, da Excel aus der 2 (vorletzter Parameter) keine 3 macht.
Dann können wir beide Ergebnisse nach unten ziehen.
Schön formatieren und alles sieht gut aus.
Bei SVERWEIS() musst du folgendes beachten:
- SVERWEIS kann nur in der äussersten linken Spalte der Matrix suchen.
- Die erste Spalte sortierst du am besten aufsteigend.
#NV mit WENNFEHLER() abfangen
Bezüglich SVERWEIS() sind wir an dieser Stelle eigentlich fertig. Allerdings tritt im Zusammenhang mit SVERWEIS() häufig dieses Problem auf.
Wenn du zum Beispiel die unterste Zeile nicht eingibst, erhältst du dieses komische "#NV".
Wie können wir das in den Griff bekommen?
Mit der Option WENNFEHLER(). Diese hat die Syntax...
=WENNFEHLER(Wert;Wert_falls_Fehler)
Was bedeutet das? Ist in der Zelle, in der du die Formel eingibst, kein Fehler, wird Wert zurückgegeben, ansonsten "Wert_falls_Fehler".
In Wert müssen wir unsere SVERWEIS-Formel aufnehmen
Wir fangen hier in G3 an, denn wir wollen die Formel wieder herunterziehen können. Wenn ein Fehler auftritt geben wir hier einfach einen leeren Text zurück. Das passt in Spalte H aber nicht mehr.
Hier verwenden wir aber dann:
=WENNFEHLER(SVERWEIS($F3;$A$4:$C$8;3;0);0)
Denn im Falle eines Fehlers müssen wir hier 0 zurückgeben, damit noch richtig gerechnet werden kann.
Wenn wir jetzt G3 und H3 nach unten ziehen, sieht das ganze so aus.
Jetzt stört noch die "0.00", die auf einem Rechnungsformular nicht so schön aussehen würde.
Nullwerte ausblenden in Excel
Um die Nullwerte in Excel auszublenden, gehen wir noch auf "Extras" > "Optionen" > "Erweitert"
Dort suchen wir die Rubrik: "Optionen für dieses Arbeitsblatt anzeigen".
Anschliessend entfernen wir den Haken "In Zellen mit Nullwert eine Null anzeigen".
Hallo, hier schreibt Lars. Dipl-Ing. Ingenieurinformatik (FH). Seit Jahrzehnten in der IT tätig. Geprüfter (und begeisterter) Webmaster. Ebenso begeisterter Windows-, Apple-, und Office-User. Ich schreibe über alle möglichen Themen rund um IT. Mehr über mich erfährst du hier: Über mich. Danke für deinen Besuch!