Sverweis Matrixbereich variabel halten
Schritt-für-Schritt-Anleitung
Um eine SVERWEIS-Funktion mit einem variablen Matrixbereich in Excel zu verwenden, befolge diese Schritte:
-
Definiere den Matrixbereich: Gehe zu der Zelle, in der du den Bereich definieren möchtest, z.B. B2. Schreibe hier den Text für den Bereich, z.B. Tabelle1!A1:C31.
-
Namen definieren:
- Drücke
Strg + F3, um das Fenster "Namen definieren" zu öffnen.
- Klicke auf "Neu".
- Gib einen Namen ein, z.B.
meinBereich1.
- Im Feld "Bezieht sich auf" gib ein:
=INDIREKT($B$2).
-
Verwende den definierten Namen: Ersetze in deiner SVERWEIS-Formel den direkten Bereich durch den Namen:
=SVERWEIS(A1; meinBereich1; 2; FALSCH)
Jetzt benutzt die SVERWEIS-Funktion den variablen Matrixbereich, der in Zelle B2 definiert ist.
Häufige Fehler und Lösungen
-
Fehler: #BEZUG!
Dieser Fehler tritt auf, wenn der Bereich, der in B2 definiert ist, nicht korrekt ist oder nicht existiert. Überprüfe die Eingabe in B2.
-
Fehler: #NAME?
Dieser Fehler tritt auf, wenn der Name meinBereich1 nicht korrekt definiert wurde. Stelle sicher, dass der Name korrekt eingegeben wurde und dass der Bezug auf INDIREKT korrekt ist.
-
Falsche Ergebnisse
Überprüfe, ob die Werte in der Matrix tatsächlich vorhanden sind. Wenn die Matrix leer ist oder nicht die erwarteten Werte enthält, wird auch das Ergebnis von SVERWEIS nicht korrekt sein.
Alternative Methoden
Eine weitere Möglichkeit, einen variablen Matrixbereich zu nutzen, ist die Verwendung der Funktion BEREICH.VERSCHIEBEN. Hier ein Beispiel:
-
Matrix definieren: Anstatt einen festen Bereich zu verwenden, kannst du BEREICH.VERSCHIEBEN so einsetzen:
=SVERWEIS(A1; BEREICH.VERSCHIEBEN(Tabelle1!A1; 0; 0; 31; 3); 2; FALSCH)
Dies verschiebt den Bezug dynamisch, basierend auf den Parametern, die du angibst.
-
INDIREKT verwenden: Ebenso kannst du INDIREKT verwenden, um den Bereich aus einer Zelle zu beziehen:
=SVERWEIS(A1; INDIREKT(B2; FALSCH); 2)
Praktische Beispiele
Hier sind einige praktische Beispiele, wie du die SVERWEIS-Funktion mit variablen Matrixbereichen verwenden kannst:
-
Beispiel mit festen Zeilen:
=SVERWEIS(A1; BEREICH.VERSCHIEBEN(Tabelle1!A1; 0; 0; 31; 3); 2; FALSCH)
-
Beispiel mit variablen Zeilen: Wenn die Anzahl der Zeilen variiert, kannst du diese auch in anderen Zellen definieren:
=SVERWEIS(A1; BEREICH.VERSCHIEBEN(Tabelle1!A1; 0; 0; ANZAHL2(Tabelle1!A:A); 3); 2; FALSCH)
Tipps für Profis
-
Namen für Bereiche: Nutze die Funktion "Namen definieren", um deine Matrixbereiche einfach zu benennen und in verschiedenen Formeln wiederzuverwenden.
-
Dynamische Berechnungen: Verwende ANZAHL2, ZÄHLENWENN oder VERGLEICH, um dynamische Zeilen- und Spaltenzahlen zu berechnen.
-
VBA für komplexe Anwendungen: Wenn du Excel VBA kennst, kannst du noch flexiblere Lösungen entwickeln, indem du die SVERWEIS-Funktion in Kombination mit Variablen in deinen Makros verwendest.
FAQ: Häufige Fragen
1. Kann ich auch mehrere SVERWEIS-Funktionen in einer Formel verwenden?
Ja, du kannst mehrere SVERWEIS-Funktionen in einer Formel kombinieren, um verschiedene Werte aus unterschiedlichen Bereichen abzurufen.
2. Was ist der Unterschied zwischen FALSCH und WAHR im SVERWEIS?
FALSCH sucht nach einer exakten Übereinstimmung, während WAHR nach einer ungefähren Übereinstimmung sucht. Für die meisten Anwendungen ist FALSCH zu empfehlen.
3. Wie kann ich einen Matrixbereich für die Verwendung in VBA definieren?
In VBA kannst du den SVERWEIS mit einer variablen Matrix so verwenden:
Application.WorksheetFunction.VLookup(Cells(1, 1), Range("meinBereich1"), 2, False)