Rückgabe einer Matrix mittels SVERWEIS optimieren
Schritt-für-Schritt-Anleitung
-
Vorbereitung der Tabellen: Stelle sicher, dass die Daten in Tabelle 1 gut strukturiert sind. Deine gesuchte Information sollte in einer Spalte stehen, und die zu übertragenden Daten sollten in der gleichen Zeile vorhanden sein.
-
Hilfsspalte in Tabelle 2 einfügen: Gehe zu Tabelle 2 und füge eine Hilfsspalte ein, um die Indizes der gesuchten Werte zu speichern. Gebe in Zelle A1 der Hilfsspalte folgende Array-Formel ein:
{=KKLEINSTE(WENN(Tabelle1!A$1:A$1000=B1;ZEILE(A$1:A$1000));ZEILE())}
Drücke Strg + Umschalt + Enter, um die Formel als Array-Formel zu aktivieren.
-
Formeln für die Rückgabematrix: Verwende die Hilfsspalte, um die gewünschten Werte aus Tabelle 1 zurückzugeben. In Tabelle 2 kannst du für die Spalte A folgende Formel verwenden:
=WENN(ISTFEHLER($A1); ""; WENN(INDIREKT("Tabelle1!A"&$A1)=""; ""; INDIREKT("Tabelle1!A"&$A1)))
Passe die Formel für jede weitere Spalte entsprechend an.
-
Formel nach unten ziehen: Ziehe die Formeln nach unten, um die Werte für alle relevanten Zeilen zu extrahieren.
Häufige Fehler und Lösungen
-
Fehler bei der Formel: Wenn du eine Fehlermeldung erhältst, überprüfe, ob du Strg + Umschalt + Enter verwendet hast, um die Array-Formel einzugeben.
-
Doppelte Suchkriterien: Wenn dein Suchkriterium mehrmals in Tabelle 1 vorkommt, kann es sein, dass nur der erste Treffer zurückgegeben wird. Um mehrere Werte zurückzugeben, benötigst du eine komplexere Formel oder VBA.
-
Falsche Referenzen: Achte darauf, dass alle Zellreferenzen korrekt sind und keine Leerzeichen oder falschen Zeichen enthalten.
Alternative Methoden
Eine weitere Methode, um mehrere Werte aus einer Matrix zurückzugeben, ist die Verwendung von VBA. Mit einem einfachen VBA-Skript kannst du die Daten automatisiert extrahieren, ohne jede Spalte manuell zu bearbeiten.
Hier ein einfaches Beispiel eines VBA-Codes:
Sub RückgabeMatrix()
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = ThisWorkbook.Sheets("Tabelle1")
Set ws2 = ThisWorkbook.Sheets("Tabelle2")
Dim lastRow As Long, i As Long, j As Long
lastRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
For i = 1 To lastRow
If ws1.Cells(i, 1).Value = ws2.Cells(1, 1).Value Then
For j = 1 To ws1.Columns.Count
ws2.Cells(2, j).Value = ws1.Cells(i, j).Value
Next j
End If
Next i
End Sub
Praktische Beispiele
Angenommen, du hast folgende Daten in Tabelle 1:
| A |
B |
C |
| Name |
Alter |
Stadt |
| Max |
25 |
Berlin |
| Lisa |
30 |
München |
| Tom |
28 |
Köln |
Wenn du in Tabelle 2 den Namen "Max" eingibst, kannst du die obigen Formeln verwenden, um alle Daten von Max in Tabelle 2 zu extrahieren.
Tipps für Profis
- Vermeide unnötige Berechnungen: Halte deine Formeln so einfach wie möglich. Verwende Hilfsspalten, um die Berechnungszeit zu reduzieren.
- Nutze die Filterfunktion: Oft kann die Verwendung des Autofilters schneller und einfacher sein, um die gewünschten Daten zu extrahieren.
- VBA für Automatisierung: Wenn du häufig ähnliche Aufgaben hast, kann das Erstellen eines VBA-Skripts extrem zeitsparend sein.
FAQ: Häufige Fragen
1. Kann ich SVERWEIS verwenden, um mehrere Spalten zurückzugeben?
Nein, SVERWEIS gibt nur einen Wert aus einer Spalte zurück. Verwende stattdessen Kombinationen von INDEX und VERGLEICH oder VBA für mehrere Rückgaben.
2. Wie viele Werte kann ich mit einer Formel zurückgeben?
Mit einer einzelnen Formel kannst du nur einen Wert zurückgeben. Um mehrere Werte zurückzugeben, musst du mehrere Formeln verwenden oder auf VBA zurückgreifen.
3. Was mache ich, wenn mein Suchkriterium doppelt vorkommt?
In diesem Fall musst du eine Methode wählen, die dazu in der Lage ist, alle Instanzen zu erfassen, wie z.B. eine Array-Formel oder ein VBA-Skript.