Excel Problem: mehrere Suchkriterien?


29.09.2023, 07:12

Nachtrag zur Frage mit Bild und der Formel von @DanKirpan

GutenTag2003  28.09.2023, 15:05

Du hast in der Spalte A verbundene Zellen. Ist davon auszugehen, dass an einem Tag zwei Personen Zugriff auf die jeweiligen Kfz's hat?

RobinM66 
Beitragsersteller
 28.09.2023, 15:33

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

Vom Beitragsersteller als hilfreich ausgezeichnet

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

Bild zum Beitrag

 - (programmieren, Microsoft Excel, Excel-Formel)

RobinM66 
Beitragsersteller
 28.09.2023, 19:26

Ich werde es morgen mal versuchen und gebe Feedback dazu. Aber vorab schonmal vielen Dank

0
RobinM66 
Beitragsersteller
 29.09.2023, 07:17

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.

0
DanKirpan  29.09.2023, 11:47
@RobinM66

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)

1
RobinM66 
Beitragsersteller
 29.09.2023, 12:30
@DanKirpan

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

0
DanKirpan  29.09.2023, 12:37
@RobinM66

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.

1
RobinM66 
Beitragsersteller
 29.09.2023, 14:43
@DanKirpan

Als Matirxformel wird #NV ausgeworfen.

Es handelt sich um Microsoft Office Professional Plus 2019

0
DanKirpan  29.09.2023, 15:24
@RobinM66

:? 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?

1
RobinM66 
Beitragsersteller
 02.10.2023, 07:14
@DanKirpan

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)

0
DanKirpan  04.10.2023, 18:38
@RobinM66

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.

1
RobinM66 
Beitragsersteller
 05.10.2023, 07:11
@DanKirpan

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.

0
DanKirpan  05.10.2023, 09:19
@RobinM66

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.

1
RobinM66 
Beitragsersteller
 05.10.2023, 09:48
@DanKirpan

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).

0
DanKirpan  05.10.2023, 10:22
@RobinM66

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.

0
RobinM66 
Beitragsersteller
 05.10.2023, 10:30
@DanKirpan

Es tut mir so wahnsinnig leid, jetzt klappt alles. Vielen Dank für die Hilfe, Gute Erklärung und deiner Geduld.

0

z.B. so

Bild zum Beitrag

=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;""))))

 - (programmieren, Microsoft Excel, Excel-Formel)

RobinM66 
Beitragsersteller
 28.09.2023, 20:09

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

0