Excel – mehrere Suchkriterien – mehrere Treffer – AGGREGAT

Mehrere Suchkriterien und mehrere potentielle Treffer? Kein Problem für die Funktion AGGREGAT.

E360

Die Formel in Zelle H7 wird nach unten kopiert:
=WENNFEHLER(INDEX(tab_Afrika[Auftrag];AGGREGAT(15;6;ZEILE(tab_Afrika[Auftrag])/((tab_Afrika[Land]=$I$1)*(tab_Afrika[Kunde]=$I$2)*(tab_Afrika[Verkäufer]=$I$3));ZEILE()-6)-1;1);"")

Der Teile dieser Funktion:
((tab_Afrika[Land]=$I$1)*(tab_Afrika[Kunde]=$I$2)*(tab_Afrika[Verkäufer]=$I$3)) sorgt dafür, dass für jede einzelne Zeile geprüft wird, ob Land, Kund und Verkäufer den eingestellten Kriterien in den Zellen I1 bis I3 entsprechen. Falls alle drei Prüfungen stimmen, kommt überall ein WAHR heraus und somit ein 1*1*1, also eine 1. Stimmt nur eine Prüfung nicht, wir diese mit FALSCH beantwortet, und es kommt z.B. ein 0*1*1, ein 0*1*0 oder ein 1*1*0 heraus. Somit hätten wir einen Divisor von entweder Null oder Eins.

ZEILE()-6 ist wichtig, um die unterschiedlichen passenden Einträge zu finden. Gibt es mehrere Treffer – wird also oben durch 1 dividiert – möchte man den erstkleinsten, zweitkleinsten, drittkleinsten Wert haben. Da die Formel hier in Zeile 7 beginnt, wird mit ZEILE() die Zahl Sieben errechnet und dann einfach immer eine 6 abgezogen. Eine Zeile tiefer steht dann eine 8-6, also eine 2.

ZEILE(tab_Afrika[Auftrag]) ermittelt einfach nur, in welchen Zeilen die Daten der Auftragswerte stehen. Die Daten beginnen in Zeile 2 und laufen bis Zeile 320. Da aber diese Zeilennummern durch entweder 1 oder 0 geteilt werden, bleiben hier nur die Zeilennummer übrig, die den drei Kriterien entsprechen.

AGGREGAT(15;6;Array;k) sorgt dafür, dass der k-Kleinste Wert aus dem Array genommen wird. Da aber durch die Division durch Null viele Fehler erzeugt wurden und diese Zeilennummern nicht mitgezählt werden sollen, sorgt das zweite Argument mit dem Wert 6 dafür, dass wirklich nur der k-kleinste Wert aus den zutreffenden Zeilen gezogen wird.

Die Übungsdatei zum Download:
E360_Zwei_Suchkriterien

Das Video bei YouTube:

2 Kommentare

  1. LFX

    Hallo, erstmal Danke für die super Erklärung! Dein Blog finde echt klasse und man lernt einiges dazu.
    Zu dieser Funktion hätte ich mal ne Frage:
    Ich habe zwei Tabellenblätter. In Tabelle 2 habe ich in Spalte B ([@[SIGNE ID]]) das Suchkriterium. Nun möchte ich herausfinden ob das Suchkriterium in Tabelle 1 Spalte in A (ExportV453[IDGP/ASNR]=[@[) auch vorhanden ist.
    Falls Ja, soll es mir dann das Ergebnis was in Spalte AE steht (ExportV453[FA MG]) anzeigen.
    Zu einem Suchkriterium können mehrere Ergebnisse ausgewertet werden.
    Die Formel steht bei mir in der Tabelle 2 in der Zelle AW5:

    =WENNFEHLER(INDEX(ExportV453[FA MG];AGGREGAT(15;6;ZEILE(ExportV453[FA MG])/(ExportV453[IDGP/ASNR]=[@[SIGNE ID]]);ZEILE()-4)-1;1);““)

    Nun funktioniert bei mir das nicht richtig. Weißt du an was es liegen kann?

    Danke schon mal in Voraus.

    VG, LFC

  2. Marcel B

    Hallo Andreas
    In all deinen Tutorials, in welchen du aus einer intelligenten Tabelle dynamisch eine neue Tabelle erzeugst, arbeitest du mit einer fest vorgegebenen maximalenTabellengrösse. Die überzähligen Zellen beschreibst du danach mit einem Blank („“).
    Ich habe nun oft das Problem, dass ich auf der Basis dieser ersten Auswertung eine weitere fahren muss. Dies geschieht dann über PIVOT (nicht Power Pivot).
    Dies bedeutet, dass die dynamisch erzeugte Tabelle auch eine intelligente Tabelle sein muss.
    Manuelles Zutun ist nicht möglich, da der Vorgang nach dem Import der Basisdaten automatisch ablaufen soll.
    VBA möchte ich auch nicht verwenden, da dies von PAnwendern meist nicht weitergepflegt werden kann.
    Die Anzahl der Zeilen, die in dieser Tabelle erzeugt werden müssen, lässt sich meist recht einfach ermitteln.
    Wie kann ich aber einer intelligenten Tabellle die anzahl der Zeilen dynamisch übergeben?
    Ich habe schon vieles versucht, und noch niergens einen erfolgreichen Ansatz gefunden.
    Mit einem bennanten Bereich kann ich das machen, jedoch nicht mit einer inteligenten Tabelle.
    Ich denke, dass dieses Problem wohl auch bei vielen anderen vorhanden ist.
    Ist doch sicher ein interessante Thema für deine Tutorials.

    Für einen Tipp bin ich dir sehr dankbar

    Marcel B