Office: Index-Vergleich mehrere Ergebnisse ausgeben

Hi.

Habe folgendes Problem: 2 tabellen, die untereinander verknüpft sind. Aufbau wie folgt:

Tab.1
Spalte A: Name
Spalte B: Datum

Tab.2
Spalte A: Datum
Spalte B: Verknüpfung mit Tab.1 über folgende Funktion:

{=WENN(ISTFEHLER(INDEX(Tab.1!A$2:A$100;VERGLEICH(Tab.2!A2;Tab.1!B$2:B$100;0));"";INDEX(Tab.1!A$2:A$100;VERGLEICH(Tab.2!A2;Tab.1!B$2:B$100;0)))}

Das heisst also, das Tab.2 Spalte B nach Übereinstimmung mit Tab.1 Spalte B durchsucht wird. Bei Übereinstimmung wird die nebenliegende Zelle aus Tab.1 Spalte A ausgegeben. Ansonsten erscheint die Zelle leer.

Jetzt mein Problem: Tab.1 enthält in Spalte B einige Daten doppelt. Die zugehörigen Zellen in Spalte A sind jedoch unterschiedlich.
Ich möchte aber, dass in Tab.2 Spalte B SÄMTLICHE Zellen aus Tab-1 Spalte A stehen, die in Frage kommen. Bei fehlenden Übereinstimmungen, soll weiterhin ein leeres Feld erscheinen.

Wie geht das?

 

Damit begibst Du Dich in Richtung einer 1:n Beziehung, für die Excel nicht mehr ausgelegt ist und eine "richtige" Datenbank, wie z.B. Access, her muß.

Alternativ ließe sich die Liste per VBA durchsuchen, um alle Treffer als Ergebnis zurückzubekommen.

So oder so wäre dann auch noch die Frage, wie die Treffer "zusammengeknotet" werden sollen. Sollen sie mit ", " verbunden werden? Oder doch lieber " - "? Oder noch ganz anders?

Laß mich raten: Du möchtest die einzelnen Treffer in den Zellen nebeneinander, also nicht alle in einer Zelle?

Dann geht es per Formel schon gleich gar nicht, denn: Per Formel kannst Du nur das Ergebnis der "eigenen" Zelle berechnen, hast aber keinen Einfluß auf andere Zellen. Eine Formel in B2 kann also kein Ergebnis in C3 ausgeben. Das geht dann eh nur per VBA.

Gruß, Michael

 

Hallo!

Vielleicht hilft diese Seite weiter?
http://excelformeln.de/formeln.html?welcher=28

 

Auch wenn das nun schon fast zehn Jahre alt ist, möchte ich noch eine ordentliche Lösung für dieses Problem hier aufzeigen, denn die von miriki getätigte Aussage, das ginge nicht, stimmt so nicht.

Mithilfe des von Schatzi geposteten Links lässt sich eine Lösung konstruieren, bei der alle Datumstreffer nebeneinander in separaten Zellen angezeigt werden.

Die Formel lautet dann:

Code:

{=WENNFEHLER(INDEX(Tab.1_A;KKLEINSTE(WENN(Tab.1_B = $E2; ZEILE(Tab.1_B)-1);SPALTE(A:A)));"")}

Die geschweiften Klammern kennzeichnen die Formel als Matrix-Funktion. Sie werden nicht mit eingegeben, sondern entstehen automatisch, wenn die Funktion mit Strg+Shift+Enter abgeschlossen wird.

Tab.1_A und Tab.1_B sind von mir benannte Bereiche mit den Ausgangsdaten, deren genaue Ausdehnung auch aus der Grafik ersichtlich wird, hier sind genauso gut auch andere Bezüge denkbar, z.B. auf andere Tabellenblätter, wie im Beispiel des Threaderstellers/der Threaderstellerin.

in meinem Beispiel habe ich die Formel in F2 eingefügt und nach unten und rechts erweitert.

Das "-1" innerhalb des WENN[DANN] Bereichs muss beim Übertragen auf andere Arbeitsblätter entsprechend angepasst werden. Es ist der Zellen-Abstand des betrachteten Bereichs zum oberen Blattrand.

 

Hola,

und ab Excel 2010 geht das auch mit Aggregat() - dann kann man auf den Abschluss als Matrixformel verzichten.

Gruß,
steve1da

 

Hej steve1da,

Das ist ja spannend, die Aggregat() kannte ich noch gar nicht.

Habe sie mal in die Formel eingebaut, dass diese weiterhin den erwünschten Effekt erzielt.

Die Formel lautet dann mit Aggregat():

Code:

{=WENNFEHLER(INDEX(Tab.1_A;AGGREGAT(15; 6; WENN(Tab.1_B = $E2; ZEILE(Tab.1_B)-1);SPALTE(A:A)));"")}

Allerdings muss ich sie weiterhin als Matrix-Formel abschließen, sonst lässt sie sich nicht nach rechts ziehen, ohne Fehler zu werfen (oder in meinem Fall leere Zellen).

Hättest du AGGREGAT() hier anders eingesetzt?

 

Hola,

zum Einen ist "Spalte(A:A)" überflüssig - hier reicht "Spalte(A1)".
Das mit den leeren Zellen kann ich so nicht nachvollziehen. Hast du eine Excel-Beispieldatei (kein Bild) in der das deutlich wird?

Gruß,
steve1da

 

Klar.

Hier: Den Anhang AGGREGAT_Test.xlsx betrachten

Auf dem Blatt "Tabelle1" werden fast nur leere Zellen erzeugt, während auf "Tabelle2" die Formel so funktioniert, wie sie soll, also nebeneinander die verschiedenen Instanzen von Funden auflistet. Dabei ist der einzige Unterschied, dass in "Tabelle2" die Matrix-Version genutzt wird.

Wobei die leeren Zellen natürlich durch meine WENNFEHLER([Argument]; "") erzeugt werden. Aber es geht mir ja darum, dass es durchaus einen Unterschied macht, ob ich die AGGREGAT-Funktion im Matrix-Modus, oder im regulären Modus nutze.

 

Hola,

die korrekte Syntax lautet:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #006107}span.s2 {color: #0057d6}span.s3 {color: #ab30d6}span.s4 {color: #a54a29}</style>

Code:

=WENNFEHLER(INDEX(Tab.1_A;AGGREGAT(15;6;ZEILE(Tab.1_A)-1/(Tab.1_B=$E2);SPALTE(A1)));"")

Gurß,
steve1da

 

Genial!

Wieder etwas gelernt. Die Idee, durch einen Boolean-Wert zu teilen, finde ich großartig!

Danke für die Horizonterweiterung. ;)

 

Hallo zusammen,

in der Hoffnung, dass ich mich an das alte Thema noch anschließen kann, hier mein Problem:

Ich habe in Spalte A verschiedene Attribute stehen, die für verschiedene Produkte gelten. Die Produkte sind gebündelt in den Spalten F-K; L-Q, etc. (immer je 6 Spalten).

In den Spalten F, L, etc. (jede 6.) wird der Zustand mit "gut" oder "schlecht" bewertet und falls "schlecht" in den Spalten I, O, etc. mit einem Kommentar versehen.
Am Ende der Zeile sollen ab der Spalte BD sämtliche Kommentare hintereinander angezeigt werden.


Habe versucht Eure Formeln oben entsprechend anzupassen, aber habe es leider nicht geschafft.

Wäre super, wenn ihr mir da weiterhelfen könntet. Danke im Voraus

 

soweit ich das jetzt verstanden habe, arbeiten "kgrösste" und "kkleinste" nur mit Zahlen und nicht mit Text. Mit "Aggregat" habe ich noch nie gearbeitet und steige da auch (noch) nicht dahinter. Meine Versuche waren leider bisher erfolglos, sehen aber in etwa so aus:

=WENNFEHLER(INDEX($F$5:$BA$5;;AGGREGAT(15;6;SPALTE($F$5:$BA$5)-6/($F$5:$BA$5="schlecht");SPALTE(5:5)));"")

=WENNFEHLER(INDEX($F$5:$BA$5;5;AGGREGAT(15;6;ZEILE($F$5:$BA$5)/($F$5:$BA$5="schlecht");ZEILE(A1)));"")

 

Hola,

zeig doch bitte eine Beispieldatei (kein Bild).

Gruß,
steve1da

 

So in etwa sieht die Liste momentan aus:

Den Anhang liste_ohf.xlsx betrachten


Edit: Habe die Datei normal über die Anhangsverwaltung hier im Forum eingebunden. Link wurde aktualisiert, hoffentlich tut es jetzt. Danke fürs Bescheid geben.

 

...???? Ich kann das nicht runterladen, geschweige denn öffnen...