Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1452to1456
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Sverweis mit mehreren Suchkriterien

Sverweis mit mehreren Suchkriterien
22.10.2015 08:46:09
Fabs
Hallo zusammen:)
Ich hoffe ihr könnt mir behilflich sein.
Ich habe 2 Excel-Dateien. Momentan sieht die Formel folgendermaßen aus:
=wenn(IstNV(Sverweis(M:M;'C:\User......!C:AP;40;Falsch));0;(SVerweis(M:M; 'C:\User......!C:AP;40; Falsch)))
Nun habe ich aber in den Spalten N und O evtl. folgen noch mehr, ebenfalls Suchkriterien die identisch mit denen aus M sein können.
Also kurz zur Verdeutlichung:

  • M N O
    100
    100
    102
    103 104
    103 105
    104
    104
    101 104 105

  • Also die Nummern die in M vorkommen, können auch in N oder O vorkommen.
    So und jetzt soll auch noch verglichen werden, ob diese Nummern in der Spalte C der ersten Datei vorhanden sind und wenn ja in Spalte Q der zweiten Datei den Wert aus Spalte AP der ersten Datei wiedergeben:/
    Mit einem Suchkriterium in einer Spalte ja leicht realisierbar.
    Nun ist es so, dass der Wert der AP Spalte entweder grün, rot oder ip ist. Einer von den drei Werten steht dann ja in der zweiten Datei in Spalte Q.
    Jetzt nochmal zurück zu den Suchkriterien die ja aus den Nummern bestehen, die in M,N,O stehen. Dabei ist es so das zu jeder Nummer ein Wert (grün,rot,ip) ausgegeben wird. Es muss also in M,N,O auch noch verglichen werden, ob alle drei grün sind, dann darf in Q grün stehen. Ob einer, zwei oder alle drei rot sind, dann rot, und ob einer von denen ip ist, dann NULL.
    Ich hoffe ich konnte mich halbwegs verständlich ausdrücken! Wenn Fragen bestehen, was vermutlich bestimmt der Fall ist, dann beantworte ich diese schnellstmöglich.
    VG Fabse

    18
    Beiträge zum Forumthread
    Beiträge zu diesem Forumthread

    Betreff
    Datum
    Anwender
    Anzeige
    AW: da wäre mE einiges noch zu klären ...
    22.10.2015 09:59:28
    ...
    Hallo Fabse,
    ... zu Deiner Formel äußere ich mich momentan gar nicht.
    Zunächst zu Detailfragen:
    a) es werden immer nur Zahlenwerte zu vergleichen sein?
    b) Die Farben sind das Hintergrundfarben oder Schriftfarben? Wie sind die erzeugt, mit normaler Formatierung oder mit bedingter Zellen- oder bei Schriftfarben mit benutzerdefinierter Formatierung? Wenn bedingt bzw. benutzerdefiniert nach welcher/n Definition/en?
    c) Deine Ergebnisspalte ist also in der 2 Datei die Spalte Q? In der sollen dann Ergebnis(zahlen-?)werte und eine entsprechende Ergebnis(farb-)formatierung stehen?
    d) Wie viele auszuwertende Datenzeilen stehen denn max.in den zwei Dateien?
    Was mir aber besonders interessiert ist, wozu wird derartiges benötigt? Vielleicht versteh ich dann ja Dein Anliegen auch besser.
    Unabhängig davon wird es notwendig sein, dass Du mal zwei entsprechend repräsentative Excel-Arbeitsmappen, natürlich mit stark reduzierter Datenmengen, hier hoch lädst. Darin kläre meine Fragen und zeige mind. 2-3 (händisch von Dir ermittelte) Zielergebnisse auf.
    Dann sehen wir weiter.
    Gruß Werner
    .. , - ...

    Anzeige
    AW: da wäre mE einiges noch zu klären ...
    22.10.2015 12:20:27
    Fabs
    Hallo Werner,
    danke schon mal dass du dir die Zeit nimmst!
    Es soll ein eine automatisierte Testauswertung werden. Die 5 stelligen Nummern sind Testanfoderungen und daher kommen bei manchen Tests mehrere Nummern vor. Und wenn eine Anforderung nicht erfüllt ist ist der gesamte test rot!
    In der Datei habe ich jetzt beide Mappen zusammengefügt!
    Ich hoffe ich konnte dir jetzt so ungefähr näher bringen, was passieren soll!
    https://www.herber.de/bbs/user/100973.xlsx

    AW: da wäre mE einiges noch zu klären ...
    22.10.2015 12:22:46
    Fabs
    Nachtrag
    Die Datenzeilen sind variabel, also es können durchaus bis zu 1000 Zeilen werden.

    Anzeige
    AW: da wäre mE einiges noch zu klären ...
    22.10.2015 12:22:47
    Fabs
    Nachtrag
    Die Datenzeilen sind variabel, also es können durchaus bis zu 1000 Zeilen werden.

    AW: daraus ergeben sich weitere Fragen ...
    22.10.2015 15:41:55
    ...
    Hallo Fabse,
    ... dazu:
    1) was Du in Statistik!H:H zu stehen hast, steht in der Originaldatei in Spalte AP. Richtig?
    2.) was ist aber mit den Daten in Statistik!I:I? Davon war bisher nicht die Rede.
    3.) Du schreibst: "... jedoch wenn man io,nio,ip einträgt, ändern sich automatisch die Farben"
    . D.h. für mich Du arbeitest in Deiner Originaldatei mit bed. Formatierung und damit ergibt sich "io" immer zu grün, "nio" immer zu rot und "ip" immer zu oker. Oder?
    4.) Was aber ist mit den Zahlenwerten 1 und 2 z.B. in H2:H5. Diese sind keinen ID_neu zugeordnet. Somit hier weiter nicht relevant. Oder?
    5.) in Statistik!H45 (für ID_neu= 1042) steht "io"! Weshalb also in Bewertung!Q103 als Ergebnis "nio? Auch die anderen maßgebenden Werte (aus N und O) sind "io".
    Gruß Werner
    .. , - ...

    Anzeige
    AW: daraus ergeben sich weitere Fragen ...
    23.10.2015 07:37:12
    Fabs
    Guten Morgen Werner,
    zu1: Ganz genau!
    zu2: Ignorieren, die tun nichts zu Sache!
    zu3: Auch hier trifft alles genau zu!
    zu4: Stimmt auch.
    zu5: Ja, der Fehler ist mir gestern abend auch eingefallen! Ich habe die ios und nios nicht aus der Statistik verwendent sonder händisch eingetragen! Ich wollte dir nur verdeutlichen wie ich es meine , wenn mehrere Anforderungen erfüllt werden müssen!
    VG Fabse

    AW: wenn Du nun noch bestätigst ...
    23.10.2015 08:26:59
    ...
    Hallo Fabse,
    ... dass in Deiner eingestellten Datei für die ID´s 10004, 10007, 10008, 1001 und 10021 möglicherweise auch Übertragungsfehler nach den Werten in Spalte H unterlaufen sein können und dazu noch beantwortest, was für nur einen ausgewiesenen Wert "ng" als Ergebnis ermittelt werden soll, dann kann ich über eine Formelauswertung nachdenken.
    Gruß Werner
    .. , - ...

    Anzeige
    AW: wenn Du nun noch bestätigst ...
    23.10.2015 08:43:02
    Fabs
    Hallo Werner,
    ja, also in die Ergebnisspalte Q habe ich mich nicht auf die Statistik bezogen! Also sind mit Sicherheit keine Pbertragungsfehler der Grund, sondern es liegt daran das ich einfach wahllos rot oder grün reingetackert habe.:)
    Wenn der Wert ng da steht wurde der Test noch nicht ausgeführt aber schon programmiert! Also da kann dann einfach ng übernommen werden.
    VG
    Fabse

    AW: mit einer doch recht komplexen Formel ...
    23.10.2015 14:23:43
    ...
    Hallo Fabse,
    ... und zwar als Matrixformel mit geschachtelten AGGREGAT() und einer Reihe weitere Funktionen lösbar. Dazu mit einer Hilfsspalte und bedingter Formatierung (die dafür recht simpel sind).
    Dies für ID-Werte sowohl nur in Spalte M als auch in Kombination mit ID-Werten in den Spalten N:P. Die Auswertungsformel könnte wieder vereinfacht werden, wenn gesichert wäre, dass 1.) in Bewertung!M:P immer nur ID-Werte stehen, die in Statistik!C:C auch gelistet sind und 2.) Die ID-Werte immer wirklich echte Zahlenwerte sind und keine anderen Textwerte in M2:P999 stehen.

    Nun mein Lösungsvorschlag:

    Die Hilfsspalte habe ich in Statistik!AT angelegt. Dort folgende Formel in AT2:
    =WENNFEHLER(WAHL(VERGLEICH(AP6;{"ip"."io"."ng"."nio"};);0;1;2;17);"") und die Formel bis unten ziehend kopieren.
    Die Auswertungsformel in Q20 ziehend nach oben und unten kopieren:
     Q
    217io

    Formeln der Tabelle
    ZelleFormel
    Q217{=WENNFEHLER(INDEX({"NULL"."io"."ng"."nio"};VERGLEICH(PRODUKT(AGGREGAT(15;6;Statistik!AT$1:AT$999/(Statistik!C$1:C$999=M217:INDEX(217:217;ANZAHL(M217:O217)+SPALTE(L$1))); SPALTE(A$1:INDEX($1:$1;ANZAHL(AGGREGAT(15;6;ZEILE(A$1:A$999)/(Statistik!C$1:C$999=M217:INDEX(217:217;ANZAHL(M217:O217)+SPALTE(L$1))); SPALTE(A$1:D$1))))))); {0.1.2.17})); "")}
    Enthält Matrixformel:
    Umrandende
    { } nicht miteingeben,
    sondern Formel mit STRG+SHIFT+RETURN abschließen!
    Matrix verstehen

    Bedingte Formatierungen der Tabelle
    ZelleNr.: / BedingungFormat
    Q2171. / Zellwert ist gleich ="NULL"Abc
    Q2172. / Zellwert ist gleich ="nio"Abc
    Q2173. / Zellwert ist gleich ="io"Abc


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

    Anzeige
    AW: mit einer doch recht komplexen Formel ...
    23.10.2015 14:36:49
    Fabs
    Hallo Werner,
    vielen danke!
    Ich versuche es mal so und gebe Rückmeldung:)
    Jedoch wahrscheinlich erst morgen oder Montag.
    Schönes WE und VG
    Fabse

    Zeitmangel
    26.10.2015 18:42:56
    Fabs
    Hallo Werner,
    der erste Teil klappt reibungslos!:D
    Was jedoch noch nicht funktioniert ist die zweite Formel. Hab sie so angepasst, dass Statistik! jetzt meinen Pfad enthält. Aber wenn ich versuche sie mit strg+shift+enter zu bestätigen, passiert gar nichts. Also theoretisch müsste dann ja ein Wert erscheinen aber die Formel bleibt weiter so stehen ohne mir einen Fehler anzuzeigen :/ Hab sie in Q217 eingefügt! Was mach ich falsch?
    Nette Grüße
    Fabs

    Anzeige
    AW:Upps
    26.10.2015 19:05:48
    Fabs
    Zeitmangel bitte einfach ignorieren!

    AW: ich geh nun davon aus ...
    27.10.2015 08:34:39
    aus
    Hallo Fabs,
    ... das sich die Frage inzwischen erledigt hat. Zumindest deute ich das so aus Deinem gestrigen Folgebeitrag, auch wenn der auch anders gedeutet werden könnte.
    Gruß Werner
    .. , - ...

    AW: ich geh nun davon aus ...
    27.10.2015 16:15:40
    aus
    Hallo Werner,
    leider nein. Falsch gedeutet:)
    Klappt immer noch nicht! Hab nur im Moment immer nur am späten NAchmittag ´Zeit rumzuprobieren!
    VG
    Fabse

    AW: iwenn dem so sein sollte ...
    27.10.2015 17:13:00
    ...
    Hallo Fabse,
    ... kann ich mit Deinen doch recht allgemeinen Aussagen nicht wirklich etwas anfangen. Lade dann doch dazu eine relevanten Ausschnitt Deiner Datei, in der Du die Formel integriert hast und wo Du kenntlich machst, was fehlerhaft ist und wie es richtig sein soll, hier hoch. Weiter dann meinerseits aber erst wieder Morgen.
    Gruß Werner
    .. , - ...

    Anzeige
    AW: iwenn dem so sein sollte ...
    27.10.2015 19:31:52
    Fabs
    HAllo Werner,
    hier erstmal ein Bildchen! Userbild
    Problem ist jetzt, dass er mir keinen Fehler ausspuckt aber die Formel auch nicht akzeptiert?! Also wenn ich sie versuche mit strg+shift+enter zu bestätigen, bleibt die Formel einfach so stehen!
    Und wenn er mir wenigstens einen Fehler ausgeben würde, könnte man ja danach googeln oder hier nachfragen, aber da passiert rein gar nichts:/

    AW: ein "Bildchen" kann hilfreich sein, doch ...
    28.10.2015 08:08:28
    ...
    Hallo Fabse,
    ... doch als Aussage zu Fragestellungen in Excelforen sind sie zumeist ungeeignet. Aus Deinem Bild erkenne ich nur, dass Du nun eine Dateiübergreifende Auswertung vorgenommen hast. Davon halte ich bei komplexeren Formelauswertungen (wie das hier der Fall ist) nicht viel. Funktionieren kann es zwar trotzdem. Aber wie soll ich jetzt prüfen, ob und wenn ja, was Du nicht korrekt realisiert hast?
    Noch eine Anmerkung. Morgen ist der thread inaktiv im Forumarchiv gelandet. Wenn Du dann dazu noch Fragen hast, müsstest Du dann einen neuen thread aufmachen und im thread eine entsprechenden Hinweis geben und vor allem im Beitrag den Link auf diesen thead mit angeben.
    Gruß Werner
    .. , - ...

    Anzeige
    AW: ein "Bildchen" kann hilfreich sein, doch ...
    28.10.2015 19:00:19
    Fabs
    Hi Werner,
    alles klar! Also ich versuche es jetzt nochmal so, dass die beiden in einer Datei sind.
    Falls es nicht klappt mach ich einen neuen Thread auf.
    VG
    Fabse

    346 Forumthreads zu ähnlichen Themen

    Anzeige
    Anzeige
    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige