Getagged: INDIREKT

SVERWEIS und INDIREKT – Dynamischer Zugriff

Ich möchte einen Kunden per Dropdown auswählen und dann passend zum Kunden einen Wert aus einer separaten Kundentabelle auslesen. Die Tabellen heißen tab_Bundesländer und pro Kunde dann entsprechend tab_NRW, tab_BRA, tab_BAY und tab_HES. Der Name des Tabellenblatts ist hier unwichtig.

 

Per Dropdown wird dann ein Kunde ausgewählt. In der Datenüberprüfung wird auf die Bundesländer verwiesen. Ein neuer Eintrag in der Kundenliste wird dann automatisch auch im Dropdown aufgeführt.

 

Anschließend kann lediglich ein Projekt zum Kunden in Spalte C ausgewählt werden.

 

Per SVERWEIS muss ich nun dynamisch auf eine der beliebig vielen Tabellen zugreifen. Ich setze den Tabellennamen immer aus dem Teil „tab_“ und dem Kundennamen zusammen. Erweiterungen innerhalb der Tabellen werden automatisch mit ausgewertet.

thehos_740_3

Ich nutze die Funktion WENNNV, um #NV beim SVERWEIS zu vermeiden (seit Excel 2013) und WENNFEHLER, um generell Fehler abzufangen. Zum Beispiel, wenn die Felder leer sein sollten.

=WENNFEHLER(WENNNV(SVERWEIS($C3;INDIREKT("tab_"&$B3);2;FALSCH);"kein passender Eintrag");"")

Viel Spaß beim Nachbasteln
Andreas

Die Übungsdatei: Excel 740 SVERWEIS und INDIREKT

Hier geht’s zum Video:

Videolink zu YouTube: https://youtu.be/82FalCURBYk

Excel – Summe aus Schnittmenge ermittelter Bereiche – Teil 3

Die Summe oder auch der Mittelwert können aus der Schnittmenge von zwei Bereichen ermittelt werden. In diesem dritten Teil geht es um die Ermittlung der passenden Spalten und Zeilen per Funktionen VERGLEICH und ZÄHLENWENN, sowie die korrekte Ansprache über die Funktion INDIREKT(Z1S1;FALSCH) und INDIREKT(A1;WAHR).

Weiter Text folgt noch… mehr gibt es im Video und in der Übungsdatei.

Hier ist die Übungsdatei:
Excel 454 Summe Schnittmengen 3

Hier ist das Video:

Videolink: http://youtu.be/n_sSAQXm5G4

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