Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
SVERWEIS/WAHL mit mehreren Ergebnissen
16.03.2016 22:19:10
Thadeus53
Werte Damen und Herren
Nach langem Suchen und Pröbeln bin ich am Ende meines Lateins. Es geht um folgendes:
Ich möchte eine dynamische, formelbasierte Eventübersicht erstellen (Tabelle1-Übersicht), welche auf einer Datentabelle (Tabelle2-Datensatz) basiert. Ziel ist es, dass der Enduser neue Events komfortabel auf einer neuen Zeile der Datentabelle erfassen kann (geschieht mittels UserForm->ist aber nicht in der Beispieldatei) und die Übersicht (Tabelle1-Übersicht) lediglich für die einfache grafische Darstellung verwenden kann.
Die Suchkriterien sind Jahr (B1), Kalenderwoche (C1), Ort (A4) und Art des Events (C3). Die entsprechenden Suchspalten beziehen sich auf die formatierte Tabelle5 des Datensatzes (Tabelle2-Datensatz). Rauskommen sollen nun alle Eventnamen, die den Suchkriterien entsprechen. Diese Eventnamen sollen dann in den entsprechenden Zellen der Übersicht angezeigt werden. Auf der Übersicht sollen nur noch das Jahr und die KW durch den Enduser eingegeben werden müssen.
Formel Nr. 1:
{=WENNFEHLER(SVERWEIS($B$1&$C$1&$A4&C$3;WAHL({1\2}; Tabelle5[Jahr]&Tabelle5[Kalenderwoche]&Tabelle5[Ort]&Tabelle5[Art des Events]; Tabelle5[Eventname]);2; FALSCH);"") }
Formel Nr. 1 funktioniert wunderbar, aber nur solange es ein einzelnes Ergebnis gibt. Wenn es mehrere Ergebnisse gibt, wird immer nur das erste angezeigt. In der Beispieldatei gibt es zwei Events, die im selben Jahr, in derselben KW, am selben Ort stattfinden und auch zur gleichen Eventart gehören.
Ich habe eine Lösung für einen SVERWEIS mit mehreren Ergebnissen gefunden (Bezüge nicht angepasst):
Formel Nr. 2:
{=WENNFEHLER(INDEX($B$2:$B$14;KGRÖSSTE(($A$2:$A$14=$E$1)*(ZEILE($A$2:$A$14)-1);ZÄHLENWENN($A$2:$A$14; $E$1)+1-ZEILE(A1)));"") }
Formel Nr. 2 sucht jedoch nur nach den Werten für E1 und nicht nach mehreren Suchkriterien. Was ich nun getan habe, ist im Datensatz (Tabelle2-Datensatz) für jede Zeile einen Schlüssel (=Jahr&KW&Ort&Art des Events) generiert und dann mit der INDEX/KGRÖSSTE-Funktion gearbeitet (Suchkriterium = Schlüssel; nicht in der Datei). Das funktioniert soweit auch, ist aber eine sehr umständliche Lösung und ich befürchte auch fehleranfällig.
Ich suche nun nach einer Möglichkeit, wie ich Formel Nr. 1 adaptieren kann, damit alle Suchergebnisse für die benannten Suchkriterien ausgegeben werden. Es gibt i.d.R. nicht mehr als 5 Ergebnisse.
Vielen Dank schon im Voraus für eure Hilfe!
Beste Grüsse
Thadeus53
https://www.herber.de/bbs/user/104398.xlsx _ a>

1
Beitrag zum Forumthread
Beitrag zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: zwei AGGREGAT()-Formeln ...
17.03.2016 12:26:01
...
Hallo Thadeus,
... die Formel A4 nach unten und die Formel B4 nach rechts und unten ziehend kopieren.
Damit die Formeln nicht künstlich sehr lang werden, hab ich mir erlaubt, Deinen 2. Tabellenblattnamen auf "Dat" zu kürzen.
Übersicht

 ABCDEF
1Eventübersicht2016KW11   
2      
3OrtKonzertShowShowTheaterTheater
4Düsseldorf   "Das kl. Licht bin ich." 
5KarlsruheSunrise Avenue    
6      

Formeln der Tabelle
ZelleFormel
A4=WENNFEHLER(INDEX(Dat!D:D;AGGREGAT(15;6;ZEILE(Dat!E$1:E$99)/(Dat!G$1:G$99=B$1)/(Dat!F$1:F$99=C$1)/ISTNV(VERGLEICH(Dat!D$1:D$99;A$3:A3;)); 1)); "")
B4=WENNFEHLER(INDEX(Dat!$C:$C;AGGREGAT(15;6;ZEILE(Dat!$C$2:$C$99)/(Dat!$D$2:$D$99=$A4)/(Dat!$A$2:$A$99=B$3)/(Dat!$G$2:$G$99=$B$1)/(Dat!$F$2:$F$99=$C$1); ZÄHLENWENN($A$3:B$3;B$3))); "")


Dat

 ABCDEFG
1Art des EventsVeranstalterEventnameOrtDatumKalenderwocheJahr
2ShowBest EventsDie SchneeköniginBerlinDi, 08.03.2016KW102016
3ShowBest EventsDer SchneeprinzBerlinDi, 08.03.2016KW102016
4ShowBest EventsDie SchneeköniginHamburgDi, 08.03.2016KW102016
5ShowBest EventsDer SchneeprinzHamburgDi, 08.03.2016KW102016
6TheaterTheatre 1"Das kl. Licht bin ich."DüsseldorfDi, 15.03.2016KW112016
7KonzertConcept 1Sunrise AvenueKarlsruheDi, 15.03.2016KW112016
8       

Formeln der Tabelle
ZelleFormel
F2=KALENDERWOCHE([@Datum];21)
F3=KALENDERWOCHE([@Datum];21)
F4=KALENDERWOCHE([@Datum];21)
F5=KALENDERWOCHE([@Datum];21)
F6=KALENDERWOCHE([@Datum];21)
F7=KALENDERWOCHE([@Datum];21)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige

348 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige