Office: (Office 2016) Index + KKLeinste ("SVERWEIS"-Ersatz mit mehreren Ergebnissen)

Hallo,

ich komme hier nicht ganz weiter.
Ich habe eine Tabelle 1 mit Personaldaten und möchte in einem anderen Tabellenblatt einige Daten dazu zu einem bestimmten Monat auslesen. Den Monat möchte ich per Dropdown wählen und dann die Daten ablesen können.

Meine Quelltabelle schaut so aus:


In der Zieltabelle gibt es zunächst ein Dropdown zu Monatsauswahl. Basierend auf der Zahl des Monats in Spalte 2 sollen dann in der dritten Spalte die jeweilige Personalnummern erscheinen (Ändern sich monatlich, darum will ich die nicht festlegen).
Die aktuelle Formel ist:

{=WENNFEHLER(INDEX(Tabelle1[PersNr];KKLEINSTE(WENN(Tabelle1[Monat]=$B$4;Tabelle1[PersNr]);A4));"99999")}

Spalte A ist nur dazu da, den k-Wert für "kKleinste" festzulegen.



Wie man sieht, funktioniert das so aber noch nicht und ich komm einfach nicht dahinter.
Kann mir hier jemand bitte weiterhelfen?

Noch eine Frage am Rande: In diversen Anleitungen hatte ich gelesen (und berücksichtigt), man muss die Formel mit "Shift+STRG+Enter" bestätigen. Was bewirkt das?

 

Hallo,

du musst nur deine Formel verkürzen auf:
{=WENNFEHLER(KKLEINSTE(WENN(Tabelle1[Monat]=$B$4;Tabelle1[PersNr]);A4);99999)}

Rein optisch bewirkt das, dass die Formel in geschweiften Klammern eingeschlossen ist (so wie bei dir).
Eine solche Formel wird MATRIX-Formel oder auch ARRAY-Formel genannt.
Inhaltlich/Funktional reagiert die Formel plötzlich anders.
Ohne MATRIX/ARRAY-Funktionalität würde die in der KKLEINSTE(…)-Funktion enthaltene WENN()-Funktion der vorgenannte Formel intern einen #WERT!-Fehler erzeugen,
=KKLEINSTE(WENN(#WERT!=1;#WERT!);1)
sodass die umgebende Funktion WENNFEHLER(…;99999) steht den Wert 99999 zurück liefern würde.

Als MATRIX/ARRAY-Funktion produziert die in der KKLEINSTE()-Funktion enthaltene WENN()-Funktion intern ARRAYS, wie zB.
{=KKLEINSTE(WENN({1;1;1;2}=1;{1001;1002;1003;1001});1)}
sodass von der KKLEINSTE(…;1)-Funktion der kleinste Wert (=1001) zurück geliefert wird.

 

Sehr schön, vielen lieben Dank! Funktioniert :)

Eine kleine Sache noch: es befinden sich leider für jeden Monat auch leere Zeilen in der Tabelle. Gibt es eine Möglichkeit, diese in der Formel zu irgnorieren? Dort eine "Wenn(XX>0;y)" - Abfrage scheint da so nicht zu funktionieren.

EDIT:
Es zeigt sich noch ein Problem.
Wir haben nun die richtigen Personalnummern und Monate identifiziert. Jetzt gibt es natürlich weiter Angaben in anderne Spalten, die in dieser identifizierten Zeile ausgegeben werden müssen. Ein klassischer Index liefert logischerweise wieder kein eindeutiges Ergebnis.

Nun haben wir die Zeile über die Personalnummer+Monat mit "kkleinste" identifiziert und ich wollte diese "FormelPersonalnummer" einfach in die Zeilenangabe von Index schreiben.
Quasi =index(Matrix;FormelPersonalnummer;SpaltennameMatrix)

Die Formel wäre:
=WENNFEHLER(INDEX(Tabelle1[[Monat]:[Grundgehalt]];KKLEINSTE(WENN(Tabelle1[Monat]=$B$4;Tabelle1[PersNr]);A4);Tabelle1[Grundgehalt]);"Fehler")

Leider bekomme ich auch hier wieder einen Fehler!? Was mache ich hier falsch?

EDIT 2:
Ich habe das Problem der weiteren Spalten jetzt mit einem "Wahl"-Konstrukt gelöst:

{=SVERWEIS(B4&C4;WAHL({1.2};Tabelle1[Monat]&Tabelle1[PersNr];Tabelle1[Grundgehalt]);2;0)}