Microsoft Excel

Herbers Excel/VBA-Archiv

Zwei Werte Suchen und bestimmten Wert ausgeben


Betrifft: Zwei Werte Suchen und bestimmten Wert ausgeben von: DirkK
Geschrieben am: 04.07.2018 18:13:12

Hallo zusammen.

Ich suche nach einer Lösung, wie ich in verschiedenen Arbeitsmappen nach zwei bestimmten Werten suchen kann, wo mir bei einem Treffer dann eine bestimmte Zeller der entsprechenden Arbeitsmappe ausgegeben wird.

Im Detail:
In unserer Exceldatei sind 25 Arbeitsblätter, die in der gedruckten Version die Dienstblätter für unser Fahrpersonal sind. Dort befinden sich die Linienumläufe, die ein Fahrer an einem Tag fahren soll. Soweit das Vorwissen.
Nun kam die Frage auf, ob es eine Möglichkeit gibt, wenn zwei Werte vorhanden sind, dann Excel das entsprechende Dienstblatt raussucht und die Dienstnummer wiedergibt.

Es soll nach Linie und Kurs gesucht werden. Jede Linie mit dem entsprechendem Kurs kommt nur einmal in den ganzen Arbeitsblättern vor. Zum Beispiel Linie 803 Kurs 2 wird gefahren vom Dienst 102. Ein anderes Beispiel, Linie 802 Kurs 25 wird gefahren vom Dienst 101.

Alle von uns versuchten Formeln haben nicht den gewünschten Erfolg geliefert. Vielmehr gab es nur Fehlermeldungen.

Vielleicht gibt es ja doch eine Lösung, daher erstelle ich hier die Frage.

Zu unserer Grundidee hier die Vorstellung:
In der Arbeitsmappe "Suche" soll in C1 die Linie und in C6 der Kurs eingegeben werden. Excel soll dann die Arbeitsmappen 101, 102, usw. durchsuchen, wo die beiden gesuchten Werte nebeneinander in der gleichen Zeile stehen.
In der Zelle G5 in der Arbeitsmappe "Suche" soll dann die Zelle C4 der Arbeitsmappe ausgegeben werden, wo der Treffer ist.
Beispiel: C1 = 803, C6 = 2
Ausgabe: G5 = C4 aus Arbeitsmappe "102"

Gerne kann das auch per Makro gelöst werden, wenn sich dies überhaupt realisieren lässt.

Danke

https://www.herber.de/bbs/user/122482.xlsx

  

Betrifft: AW: anzunehmen wäre an sich, ... von: neopa C
Geschrieben am: 04.07.2018 19:47:56

Hallo Dirk,

... dass Die Ausgaben in den Tabellenblätter der hier eingestellten Datei nur eine Ergebnisausgabe aus einer Datenbank ist und deshalb diese "abgefragt" werden sollte. Dann wäre die angestrebte Auswertung ganz leicht.

Solltest Du jedoch keinen Zugriff auf eine solche Basisdatenbank haben, dann könnte unter folgenden zwingenden Bedingungen:
- es gibt immer nur eine einzige (!) Kombination aus Linie-Nr und Kursnummer in allen Tabellenblättern.
- die gesuchten Datenwerte stehen immer in den selben Spalten G:H
- die Dienstnummer ist identisch der Bezeichnung der Schichttabellenblattnamen

die Dienstnummer mit einer Formel ermittelt werden. Für diese hab ich angenommen, dass in jedem Tabellenblatt max 99 Datenzeilen auszuwerten wären (andernfalls Formel entsprechend anpassen). Nachfolgend hab ich diese auch nur für die vorhanden zwei Schichttabellenblätteraufgestellt.

=MAX(ISTZAHL(VERWEIS(9;1/('101'!G1:G99=C4)/('101'!H1:H99=C6)))*101+ISTZAHL(VERWEIS(9;1/('102'!G1:G99=C4)/('102'!H1:H99=C6)))*102)

Für die restlichen Schichtblätter ist die Formel entsprechend analog zu erweitern. Würde dann eine sehr lange Formel, weshalb es vielleicht ratsam wäre, z.B. 5 Hilfsformeln für nur je 5 Schichttabellenblätter aufzustellen und dann von diesen das MAX() zu ermitteln, was dann die gesuchte Dienstnummer wäre.

Ein Ergebniswert 0 würde darauf hinweisen, dass es die gesuchte Kombination nicht gibt.

Gruß Werner
.. , - ...


  

Betrifft: AW: anzunehmen wäre an sich, ... von: DirkK
Geschrieben am: 04.07.2018 20:03:04

Hallo Werner,

Vielen, vielen lieben Dank!!!! Ja, die zwingenden Vorgaben sind alle gegeben. Alle Schichtblätter sind identisch aufgebaut und die Linie/Kurs Kombination gibt es in allen Schichtblättern nur einmal.

Habe es getestet und es funktioniert einwandfrei.

Bin echt begeistert. Danke!!!!


  

Betrifft: AW: anzunehmen wäre an sich, ... von: DirkK
Geschrieben am: 05.07.2018 19:31:56

Hallo Werner,

Nun habe ich doch noch eine Frage. Ich habe deine Formel entsprechend erweitert und bin auf die Idee gekommen, noch eine weitere Abfrage zu erstellen, wo nach Abfahrt-Ort, Abfahrt-Zeit und Linie gesucht werden soll. Das funktioniert auch wunderbar, das entsprechende Schichtblatt wird ausgegeben. Jedoch stehe ich nun vor dem Problem, dass der Abfahrt-Ort teilweise mit Zusatzinfos für die Fahrer versehen ist, wie zum Beispiel Krankenhaus [A], damit der Fahrer erkennt, welche Haltestelle denn nun gemeint ist, oder wie der Umlauf zu fahren ist (verschiedene Umläufe für eine Linie). Wenn ich nun in das Suchfeld nur Krankenhaus eingeben, wird natürlich kein Wert ausgegeben, da die gesuchte Kombination natürlich so nicht vorhanden ist. Meine Frage ist daher, ob man eine =suche() einfügen kann, die es ermöglicht, dass halt nur der Haltestellen-Name reicht, ohne die Zusatzinfos für die Fahrer die teilweise hinter oder auch vor dem Haltestellen-Name vorhanden sind. (siehe Dienst 101, Fahrt 7:22 oder 6:28). Es muss auch nur in der Spalte C gesucht werden, denn bei den Abfahrtszeiten sowie die Linie sind keinerlei zusätzliche Informationen enthalten.

Habe meine Testdatei nochmal angehängt: https://www.herber.de/bbs/user/122499.xlsx


  

Betrifft: AW: die dafür angepasste Formel von: neopa C
Geschrieben am: 05.07.2018 20:18:45

Hallo Dirk,

... in G12 dann so:

=MAX(ISTZAHL(VERWEIS(9;1/ISTZAHL(SUCHEN(C11;'101'!C8:C106))/('101'!G8:G106=C12) /('101'!D8:D106=C13))) *101
+ISTZAHL(VERWEIS(9;1/ISTZAHL(SUCHEN(C11;'102'!C8:C106))/('102'!G8:G106=C12)/('102'!D8:D106=C13)))*102)

Gruß Werner
.. , - ...


  

Betrifft: AW: die dafür angepasste Formel von: DirkK
Geschrieben am: 05.07.2018 20:35:37

Hallo Werner,

Jetzt hat es mich wirklich aus dem Sitz gehaun. So ähnlich hatte ich das auch versucht, jedoch habe ich es so geschrieben :=MAX(ISTZAHL(VERWEIS(9;1/(suchen('101'!C8:C106;C11)=c11), was letztlich nicht zum Erfolg führte. :D War der Gedankengang wohl zu kompliziert...

Vielen lieben Dank


  

Betrifft: AW: nein, dieser war nur unvollendet, ... von: neopa C
Geschrieben am: 06.07.2018 10:52:59

Hallo Dirk,

... denn SUCHEN() kann nur eine Zahl ("Fundstelle") oder einen Fehlerwert (wenn SUCHWERT nicht gefunden wird) ermitteln und somit niemals den SUCHWERT ausgeben. Außerdem hast Du die Argumente für SUCHEN() vertauscht gehabt.

Gruß Werner
.. , - ...


Beiträge aus dem Excel-Forum zum Thema "Zwei Werte Suchen und bestimmten Wert ausgeben"