Excel – Elemente einer sortierten Liste ausgeben – WENN und INDIREKT

Das folgende Bild sagt eigentlich fast alles…

441_a

Abhängig von der Auswahl in Zelle D2 soll das entsprechende Team aus den Spalten A:B aufgelistet werden. Als Hilfsberechnungen zähle ich in Zelle F2, wie oft es den Eintrag gibt und in Zelle E2 wird ermittelt, in welcher Zelle zum ersten mal das in D2 ausgewählte Team auftritt.

Nun muss also der Bereich von Zeile E2 an eine Höhe von F2 Zellen haben. Im Beispiel läuft der Bereich von Zeile 2 bis Zeile 7, weil sich dort die Werte von Team 1 befinden.

=AGGREGAT(15;6;ZEILE(A:A)/(A:A=D2);1)
ermittelt dabei, welches die kleinste Zeilenzahl ist, in der zum ersten mal der in D2 eingestellte Wert steht.

Und nun die Herausforderung mit der Funktion INDIREKT. Mit INDIREKT können Zell- oder Bereichsbezüge konstruiert werden. Zum Beispiel in der Form =INDIREKT(„A“&ANZAHL2(A:A)).

Die Formel =WENN($F$2+4<ZEILE();"";INDIREKT("B"&($E$2+ZEILE()-5))) steht im Bereich E5:E18.

=WENN($F$2+4<ZEILE();"";... sorgt dafür, dass tatsächlich nur so viele Werte übernommen werden, wie in Zelle F2 ermittelt werden. Nämlich im Bereich der Zellen 5 bis 10 (hier 6 Werte). Alle übrigen Zellen sollen mit „“ leer bleiben.

Und nun wird mit ...;INDIREKT("B"&($E$2+ZEILE()-5))) dynamisch auf den Bereich verwiesen, der sich ab dem ersten Treffer aus E2 weiter nach unten befindet. Da der Bereich der Einträge hier erst in Zeile 5 startet, wird mit Zeile()-5 zunächst nichts hinzu addiert oder abgezogen. Bei Zeile 6 ergibt ZEILE() dann eine 6 und es wird 1 addiert usw.

Hier ist die Übungsdatei
Excel_441_Namen_auflisten_INDIREKT

Hier geht es zum Video

Ein Kommentar

  1. timogreen7

    Hallo,
    1. deine Videos sind Spitze! Aber ich habe dennoch eine Frage. Und zwar möchte ich mir die Elemente auch auflisten lassen, jedoch in einer Extramappe. Wenn ich so vorgehe wie von dir beschrieben, dann wird mir #Bezug ausgegeben. Ich denke das der Fehler bei Indirekt(“B”) liegt, da ich ja hier auf die andere Mappe verweisen muss. Muss ich daher Indirekt(“‘Mappe_1′!B:B”&($AI$2+ZEILE()-5)) schreiben? Eine hilfe wäre sehr lieb! Mfg