Sverweis für die nächste Zelle nutzen
Schritt-für-Schritt-Anleitung
Um alle zugehörigen Namen für eine Kostenstelle in Excel anzuzeigen, kannst Du die folgende Matrixformel verwenden. Dabei wird angenommen, dass die Namen in Spalte A und die Kostenstellen in Spalte B stehen, während der Suchbegriff (die Kostenstelle) in Zelle E1 steht.
-
Wähle die Zelle aus, in der die Ergebnisse beginnen sollen (z.B. C1).
-
Gib die folgende Formel ein:
{=WENN(ZEILE()>ZÄHLENWENN($A$1:$A$8;E$1);"";INDEX($B$1:$B$8;KKLEINSTE(WENN((A$1:A$8=E$1);ZEILE($1:$8));ZEILE())))}
-
Bestätige die Eingabe mit Strg + Shift + Enter, um die Formel als Array-Formel einzugeben.
-
Ziehe die Formel nach unten, um die nächsten Ergebnisse zu erhalten.
Diese Formel sucht in der angegebenen Datenreihe nach der Kostenstelle und gibt die dazugehörigen Namen in aufeinanderfolgender Reihenfolge aus.
Häufige Fehler und Lösungen
-
Fehler: Die Formel gibt nur leere Zellen zurück.
- Lösung: Stelle sicher, dass das Suchkriterium in der Zelle E1 korrekt ist und dass die entsprechenden Werte in A und B vorhanden sind.
-
Fehler: "FALSCH" wird angezeigt.
- Lösung: Überprüfe den Bereich der Formel (z.B.
$1:$8), um sicherzustellen, dass die Daten in diesen Zeilen vorhanden sind.
-
Fehler: Die Formel funktioniert nicht nach dem Kopieren.
- Lösung: Achte darauf, dass die Formel in der ersten Zeile beginnt. Verwende die Funktion
ZEILEN($1:1), um die Zeilen dynamisch anzupassen.
Alternative Methoden
Eine alternative Methode, um die gleichen Informationen in Excel zu extrahieren, ist die Verwendung von Power Query:
- Lade Deine Tabelle in Power Query.
- Verwende die Funktion "Gruppieren nach", um die Namen nach Kostenstellen zu gruppieren.
- Lade die Ergebnisse zurück in Excel.
Diese Methode ist besonders nützlich für größere Datensätze und bietet eine benutzerfreundliche Oberfläche.
Praktische Beispiele
Angenommen, Du hast folgende Daten:
| A (Namen) |
B (Kostenstelle) |
| Max |
1000 |
| Lisa |
1000 |
| Tom |
2000 |
| Anna |
1000 |
Wenn Du in Zelle E1 die Kostenstelle 1000 eingibst und die obige Formel in C1 verwendest, bekommst Du folgendes Ergebnis:
Wenn Du die Formel nach unten ziehst, wird die nächste Zelle für weitere Namen angezeigt.
Tipps für Profis
-
Nutze WENNFEHLER, um die Handhabung von Fehlern zu verbessern:
{=WENNFEHLER(WENN(ZEILE()>ZÄHLENWENN($A$1:$A$8;E$1);"";INDEX($B$1:$B$8;KKLEINSTE(WENN((A$1:A$8=E$1);ZEILE($1:$8));ZEILE()))); "")}
-
Experimentiere mit benutzerdefinierten Datenbereichen, um die Flexibilität zu erhöhen.
-
Halte Deine Daten gut strukturiert, um die Fehleranfälligkeit zu minimieren.
FAQ: Häufige Fragen
1. Wie funktioniert die KKLEINSTE-Funktion in der Formel?
Die KKLEINSTE-Funktion gibt den n-kleinsten Wert aus einem bestimmten Bereich zurück. In dieser Formel wird sie verwendet, um die Zeilennummern der übereinstimmenden Kostenstellen zu ermitteln.
2. Muss ich die Formel in der ersten Zeile eingeben?
Ja, die Formel sollte in der ersten Zeile der Ergebnisse stehen, um korrekt zu funktionieren. Andernfalls musst Du die Bezugnahmen in der Formel manuell anpassen.