SVERWEIS nur einmal ausführen in Excel
Schritt-für-Schritt-Anleitung
Um den SVERWEIS nur einmal auszuführen und den Wert danach beizubehalten, kannst Du die folgende Methode verwenden:
- Excel starten und das entsprechende Arbeitsblatt laden.
- Alt + F11 drücken, um die VBA-Umgebung zu öffnen.
- Im linken Fenster ("Projekt - VBA Projekt") die Tabelle doppelt anklicken, in der die Aktion ausgeführt werden soll.
- Den folgenden Code in das Codefenster einfügen:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' Fehler ignorieren
On Error Resume Next
' nur ausführen wenn
' - Wert in Spalte 3 verändert wurde
' - die Zelle in Spalte 3 nicht leer ist
' - die Zelle, in der der zu ermittelnde Wert steht, leer ist
If Target.Column = 3 And _
Not IsEmpty(Target) And _
IsEmpty(Cells(Target.Row, Target.Column + 1)) Then
' Schutz des Arbeitsblattes aufheben
Me.Unprotect
' SVERWEIS benutzen, um zugehörigen Wert zu finden
Cells(Target.Row, Target.Column + 1).Value = _
Application.WorksheetFunction.VLookup( _
Target, Range("$A$1:$B$3"), 2, False)
' Wenn Fehler aufgetreten, dann Schlüssel nicht gefunden
If Err = 1004 Then _
Cells(Target.Row, Target.Column + 1).Value = "** unbekannt **"
' Arbeitsblatt wieder schützen
Me.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub
- VBA-Editor schließen und zurück zu Excel wechseln.
Häufige Fehler und Lösungen
-
SVERWEIS wird nicht ausgeführt:
Wenn die Formel nicht das erwartete Ergebnis liefert, überprüfe, ob die Daten korrekt eingegeben wurden und ob die Suchwerte in der richtigen Spalte stehen.
-
Excel SVERWEIS nur wenn Wert vorhanden:
Stelle sicher, dass der gesuchte Wert wirklich in der Referenztabelle vorhanden ist. Ansonsten wird das Ergebnis #NV angezeigt.
-
VBA funktioniert nicht:
Überprüfe, ob die Makros in Excel aktiviert sind. Gehe dazu auf „Datei“ > „Optionen“ > „Trust Center“ > „Einstellungen für das Trust Center“ > „Makroeinstellungen“.
Alternative Methoden
Wenn Du keine VBA-Lösung nutzen möchtest, gibt es auch andere Möglichkeiten, den SVERWEIS nur einmal auszuführen:
-
Manuelles Einfügen von Werten:
Du kannst die Zelle mit der SVERWEIS-Formel kopieren und über „Inhalte einfügen“ > „Werte“ einfügen, um den Wert zu fixieren.
-
WENN-Funktion:
Eine Kombination aus SVERWEIS und WENN kann auch hilfreich sein, um zu prüfen, ob ein Wert existiert, bevor der SVERWEIS ausgeführt wird.
Praktische Beispiele
-
SVERWEIS mit WENN:
=WENN(ISTFEHLER(SVERWEIS(A6;E5:AM49;2;FALSCH));"Nicht gefunden";SVERWEIS(A6;E5:AM49;2;FALSCH))
Diese Formel gibt einen alternativen Text aus, falls der SVERWEIS nicht erfolgreich ist.
-
Einfache SVERWEIS-Anwendung:
Wenn in Zelle A6 eine Zahl steht und Du den korrespondierenden Text aus einer Tabelle abrufen möchtest, verwende:
=SVERWEIS(A6;$E$5:$AM$49;2;FALSCH)
Tipps für Profis
- Nutze den XSVERWEIS (verfügbar in neueren Excel-Versionen), um flexiblere Suchen und Rückgaben zu ermöglichen.
- Achte darauf, dass die Daten in den Spalten ordentlich formatiert sind, um Fehler bei der Ausführung des SVERWEIS zu vermeiden.
- Teste Deine Formeln immer in einer separaten Zelle, bevor Du sie in produktiven Blättern verwendest.
FAQ: Häufige Fragen
1. Warum wird mein SVERWEIS nicht ausgeführt?
Das kann verschiedene Gründe haben, wie z.B. falsche Zellreferenzen oder der gesuchte Wert ist nicht in der Tabelle vorhanden.
2. Kann ich SVERWEIS in geschützten Blättern verwenden?
Ja, aber Du musst sicherstellen, dass der Blattschutz vorübergehend aufgehoben wird, um die Werte zu ändern. Dies kann über VBA erfolgen, wie im obigen Beispiel gezeigt.