Excel Problem: mehrere Suchkriterien?
Guten Tag,
ich habe ein kleines Excelproblem. Vielleicht hat jemand eine Idee.
Zum Problem:
Ich habe das Tabellenblatt „Dienstplan“ und das Tabellenblatt „Fahrzeugliste“. Im Dienstplan sollen die Leute; hier im Beispiel: „Name1-3“ das jeweilige Fahrzeug; hier im Beispiel: „KFZ1-3“ je Tag per Dropdown wählen können (funktioniert so weiter).
Entsprechend soll dann der Name der Person bei der richtigen Zeile (Tag) und richtigen Spalte (Fahrzeug) in der Kfz Liste stehen (funktioniert nicht).
Beispiel:
Person „Name2“ will am 02.01.2023 das Fahrzeug „KFZ1“ wählen.
Person „Name3“ will am 03.01.2023 das Fahrzeug „KFZ3“ wählen.
Wie muss nun die Formel in I4 aussehen, sodass dort „Name2“ steht?
Wie muss nun die Formel in K5 aussehen, sodass dort „Name3“ steht?
Prinzip sollte hoffentlich verstanden geworden sein.
Zu beachten ist noch, dass der Tag der im Dienstplan steht als verbunden Zelle ist (Tag 01.01.2023 = Zeile 3 und 4) (wie auf dem Bild) und bei der KfZ Liste die Tage einzeln (eine Zeile pro Tag).
Ich habe es schon eine Kombination aus sverweis und wverweis versucht sowie index und vergleich. Nur leider kam ich nicht auf das entsprechende Ergebnis.
Vielen Dank schonmal im Voraus.
Achja, Makros sind nicht möglich.
Nachtrag zur Frage mit Bild und der Formel von @DanKirpan
Du hast in der Spalte A verbundene Zellen. Ist davon auszugehen, dass an einem Tag zwei Personen Zugriff auf die jeweiligen Kfz's hat?
Die Tabellen sehen aus wie die auf dem Bild, nur größer hinsichtlich Personen und Fahrzeuge. Die Spalte A ist verbunden.
Es können täglich von 0- 12 Fahrzeuge genutzt werden.
2 Antworten
Hallo,
du kannst dir die Namen die das Fahrzeug gewählt haben in dieser Art auflisten lassen:
- =WECHSELN(WECHSELN(TEXTKETTE(WENN(INDIREKT("B"&VERGLEICH($H3;$A:$A;0)&":D"&1+VERGLEICH($H3;$A:$A;0);1)=I$2;$B$2:$D$2&", ";""))&"@";", @";"");"@";"")
- Die Bezüge entsprechen deinem Beispielbild, A1:D13 als Dienstplan und H1:K7 als Kfz Liste
Zunächst wird per Textverknüpfung der Bezug der für den gesuchten Tag relevanten Zeilen des Dienstplanes ermittelt und mittels Indirekt() in den tatsächlichen Bezug umgewandelt. Anschließend wird verglichen ob die Zellen das gesuchte Fahrzeug enthalten. Falls ja wird für die Zeile der Name aus Zeile 2 des Dienstplanes mit Komma übernommen, falls nein ein Leerwert. Textkette() fügt die Texte schließlich zusammen.
Das &"@" am Ende und die Wechseln() schneiden das Komma hinter dem letzten Namen ab, bin mir sicher das es dafür einen eleganteren Weg geben muss, aber gerade komm ich nicht drauf. Sollte es für jeden Wagen am Tag nur einen Namen geben können, könntest du es weglassen und nur den Textkette()-teil verwenden:
- =TEXTKETTE(WENN(INDIREKT("B"&VERGLEICH($H3;$A:$A;0)&":D"&1+VERGLEICH($H3;$A:$A;0);1)=I$2;$B$2:$D$2;""))
Edit: Bildergänzung zum Vergleich
Ich werde es morgen mal versuchen und gebe Feedback dazu. Aber vorab schonmal vielen Dank
Sehr merkwürdig, ich erkenne bei dir keinen Unterschied zu meiner Testtabelle in der es funktioniert und ich schaffe es auch nicht den #Wert-Fehler zu reproduzieren.:/ (Hab zum Vergleich ein Bild ergänzt, evtl, siehst du einen Unterschied?)
Würde dein Excel zu alt für Textkette() sein müsste es ein #Name()-Fehler sein und könnte es den Vergleich nicht zuordnen ein #NV-Fehler, daran sollte es also auch nicht liegen.
Könntest du bitte mal die Formel bei dir schrittweise durchspielen und sagen in welchem Schritt Excel den Fehlerwert erzeugt? (Registerkarte Formeln -> Abschnitt Formelüberwachung -> Formelauswertung)
Tatsächlich sehr merkwürdig, konnte jetzt auch keinen Fehler in der Formel entdecken.
Zellen sind alle als "Standard" formatiert und die Datumszellen als "Datum".
Die Fehlerauswertung ergab:
"Eine Funktion in dieser Formel bewirkt, dass sich das Ergebnis der Formel bei jeder Neuberechnung ändert. Der letzte Auswertungsschritt wird dem Ergebniswert in der Zelle entsprechend, Zwischenschritte können aber vor diesem Wert verschieden sein."
Beim durchklicken sieht man nur, wie die Formel zu
Wechseln(wechseln(textkette(wenn(#Wert!=I$2;..... wird. Wobei der Wert I2 die Konstante "Kfz 1" enthält
Oh, es macht bei dir also die Auswertung des Bezuges aus Indirekt() nicht. Funktioniert es wenn du sie als Matrixformel eingibst? (Mit Strg+Umschalt+Enter abschließen).
Wenn ja scheint Excel 2021 automatisch zu erkennen das sie eine sein müsste und behandelt sie bei mir entsprechend ohne es anzuzeigen.
Als Matirxformel wird #NV ausgeworfen.
Es handelt sich um Microsoft Office Professional Plus 2019
:? Vergleich() sollte eigentlich die einzige mögliche #NV-Quelle sein, aber das hatten wir ja bereits ausgeschlossen. Kannst du nur um sicherzugehen nochmal nachprüfen ob Excel sie als gleich erkennt? (Also einfach ob A3=H3 etc Ergebnis WAHR ausgeben)
Und wenn sie übereinstimmen nochmal die Formelauswertung durchklicken?
Sorry für die späte Antwort, hatte vorher keinen Zugriff auf die Datei gehabt.
Ja, A3=H3 Ergebnis: WAHR aber A4=H3 ist FALSCH (falls es relevant ist)
Ebenfalls Sorry für die späte Antwort, hab erst heute wieder nach meinem langem Wochenende reingesehen.
Dafür hab ich nun in unserem Such-den-Unterschied-Bild den Fehler gefunden: Du hast nur den Namensbereich von $B$2:$D$2 auf $B$2:$F$2 erweitert, aber nicht den Suchbereich (das "&":D"&" zwischen den beiden Vergleich() in Indirekt() müsste dafür ebenfalls zum "&":F"&" werden), wodurch Excel versucht je 5 Teilergebnisse auf 3 Zellen zu verteilen und daher bei Teilergebnis 4 & 5 einen #NV-Fehler ausgibt, der sich dann weiter durchzieht.
Vielen Dank. In meiner Testtabelle konnte ich es jetzt auch korrekt rekonstruieren. Wollte es jetzt mal auf die richtige Tabelle umformulieren und es klappt bedingt. Es wird kein Fehler oder der gleich angezeigt, lediglich bleibt die Zelle leer. Vielleicht fällt dir spontan der Fehler auf.
Änderungen:
Fahrzeuge stehen in: =E3:P3
Namen stehen in: =Dienstplan!H2:AV2
Tage aus dem Dienstplan: =Dienstplan!D5:D736
Tage aus der Fahrzeugliste: =D4:D369
=WECHSELN(WECHSELN(TEXTKETTE(WENN(INDIREKT("H"&VERGLEICH($D4;Dienstplan!$D$5:$D$736;0)&":AV"&1+VERGLEICH($D4;Dienstplan!$D$5:$D$736;0);1)=E$3;Dienstplan!$H$2:$AV$2&" ";""))&"@";", @";"");"@";"")
Zum Unterschied, ich hatte nochmal das Komma nach dem Namen entfernt, bei INDIREK("H"& geändert und bei &":AV"& geändert. Zudem entsprechend die neuen Quellen. Das ganze als Matrixformel.
Bitte :D,
Indirekt("H" müsste noch zu Indirekt("Dienstplan!H" werden
Momentan versucht es bei dir die Einträge in den Zellen aus "Fahrzeugliste" (bzw dem Blatt in dem die Formel steht) statt den entsprechenden in "Dienstplan" zu finden, wo sie natürlich nicht drin stehen und es daher keine Treffer gibt.
Es funktioniert jetzt mit den Namen anzeigen, vielen Dank. Nur jetzt ist das Problem, dass es nicht am richtigen Tag angezeigt wird. Person und Fahrzeug wird richtig angezeigt, aber zwei Tage später. 😁
Beispiel: Person A nimmt KfZ B am 02.01 lt. Dienstplan. In der Fahrzeugliste steht dann beim Kfz B der richtige Name aber an Tag 04.01 (zwei Zeilen tiefer).
Wir lassen aber wirklich nicht aus xD,
Der Versatz entsteht (mal wieder) weil Indirekt() den falschen Bereich nimmt, diesmal weil der Vergleich() bei dir in Zeile 5 startest. Entweder müsstest du das auf $D$1:$D$736 ändern oder (falls in D1:D4 Daten stehen, die Excel mit dem Datum verwechseln könnte) nach beiden Vergleichen +4 rechnen.
Mit der Suchmatrix D5:D736 steht der z.B 04.01 in Zeile 7 der Suchmatrix und Vergleich() gibt entsprechend 7 (statt 11) aus, wodurch im Bereich H7:AV8 nach Einträgen gesucht wird, der der Bereich des 02.01 statt des 04.01 im Dienstplan ist.
Es tut mir so wahnsinnig leid, jetzt klappt alles. Vielen Dank für die Hilfe, Gute Erklärung und deiner Geduld.
z.B. so
=WENN(SVERWEIS($H3;$A:$A;1;FALSCH);WENN(I$2=$B3;$B$2;WENN(I$2=$C3;$C$2;WENN(I$2=$D3;$D$2;""))))
Problem ist hier glaube bei der wenn-Formel, dass mein Bild vereinfacht dargestellt ist. Es gibt ~30 Namen und ~15 Fahrzeuge. Da würde dies so glaube nicht klappen
Ich habe es mal versucht, das Ergebnis ist #Wert!. Zudem habe ich mal probiert, ob es an den verbunden Zellen im "Dienstplan" liegt. Leider auch nicht zum gewünschten Ergebnis gekommen.
Oben in der Frage habe ich es noch einmal ein Screenshort angehangen.