Office: (Office 2016) Mehrere Suchergebnisse ausgeben (mit AGGREGATE)

Hallo zusammen,

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 betroffenen Produkte hintereinander angezeigt werden, also alle, deren Bewertung auf "schlecht" steht.
Da mir nichts bekannt ist, wie sowas in einer Zelle zusammengefasst werden kann (ohne VBA), sollen alle Treffer in Zellen nebeneinander geschrieben und diese dann abschließend mit VERKETTEN in einer Zelle zusammengefasst werden.
Den Anhang liste_ohf.xlsx betrachten

Meine bisher getesteten Formeln funktionieren nicht wie sie sollen und alleine komme ich leider nicht weiter.
=INDEX($F$3:$BA$5;3;KGRÖSSTE(($F$5:$BA$5="nein")*(SPALTE($F$5:$BA$5)-6);ZÄHLENWENN($F$5:$BA$5;"nein")+6-SPALTE(5:5)))
=WENNFEHLER(INDEX($A$5:$BA$5;5;AGGREGAT(15;6;ZEILE($A$5:$BA$5)/($A$5:$BA$5="nein");ZEILE(A1)));"")
=WENNFEHLER(INDEX($A$5:$BA$5;;AGGREGAT(15;6;SPALTE($A$5:$BA$5)-6/($A$5:$BA$5="nein");SPALTE(5:5)));"")

Die Versuche mit KKleinste und KGrößte scheitern wohl daran, dass sie nur mit Zahlen funktionieren, nicht mit Text. Mit AGGREGAT habe ich bisher nie gearbeitet, denke aber, dass ich damit der Lösung näher komme, bekomme allerdings einen Knoten im Kopf bei den Spalten & Zeilen.

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

 

Hallo,

dazu gibst du in Zelle BD5 folgende Matrixformel ein:
{=WENNFEHLER(INDEX($F$3:$BA$3;1;KKLEINSTE( WENN(MTRANS($F5:$BA5)="schlecht";ZEILE($1:$48));SPALTE(A1)));"")}

1. Anmerkung: Matrixformel heißt:
   1) Du gibst die Formel für Zelle BD5 in die Formeleditierzeile ohne der beiden geschweiften Klammern ein
   2) Zum Abschluss drückst du nicht die EINGABE-Taste, sondern die Tastenkombination Strg+Umschalt+EINGABE
   3) Daraufhin erscheint die eingegebene Formel in geschweiften Klammern eingeschlossen, wie vorhin angegeben.)
   
2. Formelkopieren: Diese so in Zelle BD5 eingegebene Formel kannst du dann in Zeile 5 bis zur Zelle BK5 kopieren (durch Ziehen am Zellkopierpunkt der rechten unteren Zellecke)
   und anschließend den Zellbereich BD5:BK5 hinunterkopieren bis zur Zeile 10.

Die entscheidende Passage in obiger Formel ist MTRANS($F5:$BA5), denn nur dann liefert die WENN-Funktion die Matrix, mit der die KKLEINSTE-Funktion etwas anfangen kann (wegen der Spaltenorientierung dieser Funktion anstelle der Zeilenorientierung).

 

Super vielen Dank! Das funktioniert soweit wie gewünscht! :)


Wenn ich jetzt in Spalte AD eine Trennspalte einfügen möchte, weil bspw. Produkte 1-4 und Produkte 5-8 zu unterschiedlichen Batches gehören, reicht es, wenn ich im Beispiel einfach in AD eine neue Spalte einfüge und der hintere Bereich passt sich automatisch entsprechend an.
In meiner Originaldatei kann ich dies aufgrund bedingter Formatierung nicht so einfach einfügen.
Wie kann ich die Formel anpassen, dass sie auch im hinteren Bereich noch funktioniert?

Und gibt es vielleicht auch eine Lösung ohne Matrixfunktion? Da es in der Originaldatei hunderte Datensätze werden, wird das Ganze arg langsam.

 

Hallo,

Das stimmt nur teilweise: Die beiden Zellbereiche (nach INDEX und nach MTRANS passen sich automatisch an, indem sie um 1 Spalte größer werden, aber es müsste auch noch das Zeilennummern-Array von ZEILE($1:$48) auf ZEILE($1:$49) geändert werden - da aber die Zustandsbeurteilung jeweils in der 1. Zelle (=linksten Zelle) der 6 Zellen für 1 Produkt enthalten ist, müsste auch noch der der alte Wert ZEILE($1:$48) funktionieren.

Nein, die gibt es nicht. Denn die Alternative, die es tatsächlich gäbe, wäre VBA. Diese Möglichkeit wurde aber von dir im 1. Posting ausgeschlossen.
Aber ich habe eine kleine Vereinfachung entdeckt - sie dürfte sich, wenn man viele Formeln dieser Art einsetzt, etwas auswirken:
Setzt man statt der KKLEINSTE-Funktion diese Funktion nur indirekt über die AGGREGAT(15;6;.....)-Funktion ein, kann man sich die zusätzliche MTRANS-Funktion ersparen:
also man kann statt der um 1 Spalte vergrößerten bisherigen Funktion:
{=WENNFEHLER(INDEX($F$3:$BB$3;1;KKLEINSTE(WENN(MTRANS($F5:$BB5)="schlecht";ZEILE($1:$49));SPALTE(A1)));"")}
folgende Funktion einsetzen:
{=WENNFEHLER(INDEX($F$3:$BB$3;1;AGGREGAT(15;6;WENN($F5:$BB5="schlecht";SPALTE($1:$49));SPALTE(A1)));"")}
Dabei muss man natürlich beachten, dass wegen der entfallenen MTRANS-Funktion, man jetzt SPALTE($1:$49) einsetzen muss statt ZEILE($1:$49).

 

Vielen lieben Dank für die Erklärung und deine Antwort! Konnte jetzt alles so anpassen, dass es funktioniert wie gedacht.

Die 17 Sekunden Wartezeit für Eingaben im Bereich fallen fast gar nicht auf ;)
Werde das Dokument noch etwas verschlanken müssen und vielleicht fällt mir auch noch ein, wie man das ohne Matrix hinbekommt.

 

Hallo,

wendet man die Divisionsmethode an, kann man auf die Matrixklammern bei der Formel verzichten, aber ich weiß natürlich nicht, wie/ob sich das auf die Performance bei vielen Formeln dieser Art auswirkt.
Hier die Formel für Zelle BE5, diesmal ohne Matrixklammern:
=WENNFEHLER(INDEX($F$3:$BB$3;1;AGGREGAT(15;6; SPALTE($F5:$BB5)-5/($F5:$BB5="schlecht");SPALTE(A1)));"")

 

Oh super! Vielen vielen Dank, das war meine Rettung!

In meiner Originaldatei steht die Formel in 56610 Zellen und bei Eingaben im abgefragten Bereich wird bei der Matrix immer alles berechnet wenn eine Eingabe abgeschlossen wird. Bei der Formel ohne Matrix nur immer die entsprechende Zelle, wodurch die Wartezeit von 17-18 Sekunden nach jeder Eingabe komplett eliminiert wurde und man jetzt tatsächlich mit dem Dokument arbeiten kann!

Dein nächstes Bier geht auf mich ;)