SVerweis mit doppelten Werten und "Wertsuche"

[Juxhine]

Hallo liebe Community,

diese mal ein, ich denke, etwas schwierigeres Ding. Nur wirklich erklärbar über die Beispieldatei.
Insgesamt geht es darum, dass ein SVERWEIS für eine bestimmte Personalnummer das aktuellste Datum bezogen auf Elternzeit oder Mutterschutz (nicht Krankheit) aus einer Liste vieler doppelter Werte ziehen soll.
So hat ein Mitarbeiter zum Beispiel in der Abwesenheitsliste mehrere Einträge bezogen auf Krankheiten und Elternzeit. Nun soll der Sverweis die Einträge des Mitarbeiters solange durchgehen bis er das aktuellste Datum nur bezogen auf Elternzeit oder Mutterschutz findet. Dabei soll er in Von-Datum und Bis-Datum nachschauen (Je nachdem welches älter ist). Wenn jedoch das Bis-Datum (ältere Datum) über das aktuelle Datum hinaus geht, soll er das Von-Datum ziehen. 
Also: 
Wir haben den 31.10.2020
Mitarbeiter 1 ist in Elternzeit vom 01.08.2020 bis 01.09.2020  => Hier soll er das Datum Bis also 01.09.2020 ziehen.
Mitarbeiter 2 ist in Elternzeit vom 01.08.2020 bis 01.02.2021  => Hier soll er das Datum Von also 01.08.2020 ziehen.

Mitarbeiter 3 hat:
Krankheit vom 01.01.2020 bis 01.02.2020
Mutterschutz vom 01.05.2020 bis 01.07.2020
Elternzeit vom 02.07.2020 bis 01.09.2020
Krankheit vom 17.09.2020 bis 23.09.2020

Hier soll er dann Alle Krankheiten durchgehen, Erkennen, dass Elternzeit das älteste Datum hat (Da Krankheit nicht zählt) und anschließend den 01.09.2020 ausgeben.

Ich hoffe das war irgendwie verständlich und die Beispieldatei hilft.

Es darf an der Datenbasis nichts geändert werden. Die Filter Funktion fällt aufgrund von keinem 365 leider aus.

Gibt es da eine Möglichkeit?


Vielen lieben Dank, falls sich irgendeiner diesem Problem annehmen kann.

Beste Grüße

Julian

[shift-del]

Moin

M-Code

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Personalnummer", Int64.Type}, {"Abwesenheitsart", type text}, {"Von-Datum", type datetime}, {"Bis-Datum", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Abwesenheitsart] <> "Krank")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Abwesenheitsart"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Personalnummer"}, "Attribut", "Wert"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Wert", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Benutzerdefiniert", each DateTime.Date(DateTime.LocalNow())),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Benutzerdefiniert", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Benutzerdefiniert.1", each [Wert]<=[Benutzerdefiniert]),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Benutzerdefiniert.1] = true)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Personalnummer"}, {{"Datum", each List.Max([Wert]), type nullable date}})
in
    #"Grouped Rows"

[Juxhine]

Hey shift-del,

vielen liebe Dank für deine Mühe bisher. :)
Ich bin mir leider nicht sicher inwieweit wir vba anwenden dürfen. Bringe ich natürlich in Erfahrung.
Gibt es denn einen Weg, dass ganze nur über Formeln zu lösen? Mit Index Bildung oder ähnlichem?

Auf jeden Fall schonmal vielen lieben Dank, dass du dir überhaupt die Mühe machst.

Liebe Grüße

Julian

[shift-del]

Julian, wie kommst du auf VBA wenn ich M-Code darüber schreibe?

[LCohen]

Mit 365 nimmt man FILTER() statt SVERWEIS(). Probiere es doch mal.

[Juxhine]

Hey Shift-Del,

ich habe noch nie M-Codes verwendet. Werde mich mal informieren.

Lieber LCohen,

leider komme ich auch beim SVERWEIS nicht auf die richtige Formel für diese Suche. Alleine weil der SVERWEIS bei der ersten Personalnummer aufhören würde zu suchen.

[LCohen]

Du sollst Dich mit =FILTER() auseinandersetzen und nicht bezüglich =SVERWEIS() Unzulänglichkeiten wiederholen. 

EDIT: Du hast 365, aber es darf nicht verwendet werden. OK. Dann nicht.

Es geht auch mit KGRÖSSTE oder anderen Funktionen.

[Juxhine]

Also mit dem SVerweis kann ich ja nicht arbeiten, da der nach dem ersten gefunden Wert aufhört zu suchen, oder? Also müsste ich über Index und Vergleich arbeiten und darein ein Kgrößtes bringen?

[Juxhine]

Ich habe es mal mit der KGrösste Formel versucht, so in etwa:

=KGRÖSSTE(INDEX(Abwesenheiten!C:C;VERGLEICH(Übersicht!A5;Abwesenheiten!A:A;0);1);1)

Aber auch hier gibt der mir nur einen Wert zurück. 
Theoretisch will ich ja, dass ein Index aus allen erstellt wird, die die gleiche Personalnummer haben und daraus der Größte Wert gezogen wird. (Ich wollte das hier nur erst einmal im Groben hinbekommen, dass er sich überhaupt die Größte Zahl zieht, an die Bedingungen hätte ich mich später ran gemacht) 
Mir fehlt das Wissen, wie ich es schaffe, dass Excel mehrere Daten durchgeht und nicht bei der ersten Übereinstimmung stopp macht. 
Die Filter Funktion fällt da ja leider weg. :/

Liebe Grüße

Julian

[steve1da]

Hola,

das könnte man z.B. mit folgender Matrixformel erreichen:

Code:

=max(wenn(Abwesenheiten!A2:A100=Übersicht!A5;Abwesenheiten!A2:A100))

Die Formel wird mit Strg-Shift-Enter abgeschlossen.

Gruß,
steve1da