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

Helfe beim Thema Mehrere Suchergebnisse ausgeben (mit AGGREGATE) in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo zusammen, Ich habe in Spalte A verschiedene Attribute stehen, die für verschiedene Produkte gelten. Die Produkte sind gebündelt in den Spalten... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von ertaa, 23. Februar 2019.

  1. ertaa Erfahrener User

    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
     
  2. Exl121150 Erfahrener User
    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).
     
    Exl121150, 23. Februar 2019
    #2
  3. ertaa Erfahrener User
    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.
     
  4. Exl121150 Erfahrener User

    Mehrere Suchergebnisse ausgeben (mit AGGREGATE)

    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).
     
    Exl121150, 25. Februar 2019
    #4
  5. ertaa Erfahrener User
    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.
     
  6. Exl121150 Erfahrener User
    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)));"")
     
    Exl121150, 28. Februar 2019
    #6
  7. ertaa Erfahrener User
    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 ;)
     
    ertaa, 4. März 2019
    #7
Thema:

Mehrere Suchergebnisse ausgeben (mit AGGREGATE)

Die Seite wird geladen...
  1. Mehrere Suchergebnisse ausgeben (mit AGGREGATE) - Similar Threads - Suchergebnisse ausgeben AGGREGATE

  2. Einschränken der Suchergebnisse

    in Microsoft Outlook Tutorials
    Einschränken der Suchergebnisse: https://eus-streaming-video-rt-microsoft-com.akamaized.net/6a32387a-c1e8-4d34-ac1d-12db187e6e84/6819f6a7-2c1a-4ce4-97f5-e1e9b872_3400.mp4 Probieren Sie es aus!...
  3. Bei der Suche mit der Option "Alle Postfächer" werden keine Suchergebnisse gefunden

    in Microsoft Outlook Tutorials
    Bei der Suche mit der Option "Alle Postfächer" werden keine Suchergebnisse gefunden: Bei der Suche mit der Option "Alle Postfächer" werden keine Suchergebnisse gefunden Outlook 2016 Mehr... Weniger...
  4. Keine Suchergebnisse für POP-, IMAP- oder Exchange-Konten

    in Microsoft Outlook Tutorials
    Keine Suchergebnisse für POP-, IMAP- oder Exchange-Konten: Keine Suchergebnisse für POP-, IMAP- oder Exchange-Konten Outlook 2016 Mehr... Weniger Letzte...
  5. Lernen Sie, wie Sie Ihre Suchkriterien einschränken, um bessere Suchergebnisse in Outlook zu ...

    in Microsoft Outlook Tutorials
    Lernen Sie, wie Sie Ihre Suchkriterien einschränken, um bessere Suchergebnisse in Outlook zu ...: Lernen Sie, wie Sie Ihre Suchkriterien einschränken, um bessere Suchergebnisse in Outlook zu erzielen Outlook für Microsoft 365 Outlook 2019 Outlook 2016 Outlook 2013...
  6. Suchergebnisse

    in Microsoft Word Hilfe
    Suchergebnisse: Hallo zusammen, da ich mit meinem Problem leider nicht weiterkomme (und auch die Suche im Internet erfolglos war), versuche ich es hier mal... Folgendes: Ich habe eine Dokumentenvorlage...
  7. Aktualisierung Werte nach Suche mit mehreren Suchergebnissen

    in Microsoft Excel Hilfe
    Aktualisierung Werte nach Suche mit mehreren Suchergebnissen: Hallo, zur Arbeitserleichterung versuche ich mir gerade in einer Exceltabelle (Beispiel im Anhang) folgende Logik abzubilden: - Zellen in Spalte F (Status Gesamt) sollen die Formel...
  8. [Excel 2003] erstellen einer suche / Suchergebnisse kopieren

    in Microsoft Excel Hilfe
    [Excel 2003] erstellen einer suche / Suchergebnisse kopieren: Hallo an Alle! Ich hab folgendes Problem! Ich habe einen Ausdruck aus SAP in einer Excel Tabelle, diese ist aber leider verunreinigt! Nun möchte die Tabelle mit R=*(z.b....

Users found this page by searching for:

  1. excel mehrere suchergebnisse ausgeben

  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden