SVERWEIS mit Sichtbarkeit: So funktioniert's
Schritt-für-Schritt-Anleitung
Um eine SVERWEIS-Suchmatrix in Excel nur auf sichtbare Zellen anzuwenden, kannst du eine Array-Formel verwenden. Diese Formel ist ab Excel 2003 (XL2003) verfügbar. Hier ist, wie du es machst:
-
Angenommen, du hast in Spalte A die Suchkriterien und in Spalte B die Rückgabewerte.
-
Der Suchbegriff befindet sich in Zelle H1.
-
Füge die folgende Formel in die Zelle ein, in der du das Ergebnis sehen möchtest:
{=INDEX(B$1:B$100;MIN(9999;WENN(TEILERGEBNIS(103;INDIREKT("A"&ZEILE($1:$100)))*(A$1:A$100=H1); ZEILE($1:$100))))}
-
Drücke Strg + Shift + Enter, um die Formel als Array-Formel einzugeben.
Diese Methode stellt sicher, dass nur sichtbare, gefilterte Daten in der Suchmatrix berücksichtigt werden.
Häufige Fehler und Lösungen
-
Formel wird nicht als Array erkannt: Stelle sicher, dass du die Formel mit Strg + Shift + Enter eingibst. Wenn du nur Enter drückst, wird die Formel nicht korrekt ausgeführt.
-
Ergebnisse zeigen alte Daten an: Überprüfe, ob du die richtigen Spalten in der Formel angegeben hast. Möglicherweise musst du die Suchmatrix überarbeiten, um sicherzustellen, dass sie nur aktuelle Daten enthält.
-
Kein #NV-Wert bei nicht gefundenen Daten: Du kannst die Formel anpassen, um einen Fehlerwert zurückzugeben, wenn das gesuchte Element nicht vorhanden ist. Zum Beispiel:
{=WENNFEHLER(INDEX(...); "Nicht gefunden")}
Alternative Methoden
Eine andere Möglichkeit, um mit gefilterten oder ausgeblendeten Zeilen zu arbeiten, ist die Verwendung der FILTER-Funktion (ab Excel 365):
=FILTER(B2:B100; (A2:A100 = H1) * (TEILERGEBNIS(103; A2:A100)))
Diese Funktion gibt die Werte in Spalte B zurück, die den Kriterien in Spalte A entsprechen und nur sichtbare Zellen berücksichtigen.
Praktische Beispiele
Angenommen, du hast folgende Daten:
| A |
B |
| Apfel |
1,50 € |
| Banane |
0,80 € |
| Kirsche |
2,00 € |
Wenn du nach "Banane" suchst und die Zeile für "Kirsche" ausgeblendet ist, würde die Formel:
{=INDEX(B$1:B$3;MIN(9999;WENN(TEILERGEBNIS(103;INDIREKT("A"&ZEILE($1:$3)))*(A$1:A$3="Banane"); ZEILE($1:$3))))}
das Ergebnis "0,80 €" zurückgeben, da nur sichtbare Daten berücksichtigt werden.
Tipps für Profis
- Verwende Namen für Bereiche, um deine Formeln übersichtlicher zu gestalten. Anstatt B$1:B$100 zu schreiben, könntest du diesen Bereich als "Rückgabewerte" definieren.
- Nutze die DATENFILTER-Funktion, um deine Daten vor der Anwendung der SVERWEIS-Formel zu filtern.
- Experimentiere mit TEILERGEBNIS und anderen Funktionen, um deine Suchmatrix weiter zu optimieren.
FAQ: Häufige Fragen
1. Funktioniert die Array-Formel in allen Excel-Versionen?
Nein, die Array-Formel funktioniert ab Excel 2003. Ältere Versionen unterstützen diese Funktion möglicherweise nicht.
2. Kann ich die Formel auch in einer Tabelle verwenden?
Ja, die Formel kann in Excel-Tabellen verwendet werden, allerdings musst du die Bereichsreferenzen entsprechend anpassen.
3. Was passiert, wenn keine sichtbaren Daten vorhanden sind?
Wenn keine sichtbaren Daten vorhanden sind, gibt die Formel den nächstgelegenen Wert zurück oder ein definiertes Fehlerfeld, je nach Anpassung der Formel.