Office: (Office 2016) Ältestes Datum finden

Hallo

Ich habe noch zwei weitere Herausforderungen beim Erstellen meiner Liste erkannt. Ich möchte nun berechnen, ob es Unterbrüche in der Anwesenheit gegeben hat. Vielleicht könnt ihr mir weiterhelfen.

Personen können ein- und austreten. Manchmal tun sie das ohne Unterbruch und manchmal mit. In Spalte L habe ich berechnet, ob es einen Unterbruch gab, indem ich die Verweildauer vom ältesten Eintritt bis zum jüngsten Austritt (Spalte J) und die Anzahl Tage Verweildauer (Spalte K) berechnet habe. Anhand der Differenz (sprich: wenn keine Differenz, dann kein Unterbruch) kann ich erkennen, ob es einen Unterbruch gab. Diese Berechnung funktioniert, bis auf zwei Ausnahmen (und hier brauche ich eure Hilfe):

1. Grün markierte Zeilen (24 bis 25)
Diese Person ist noch nicht ausgetreten. Ich möchte gerne in Zelle I24 UND in Zelle I25 das heutige Datum anzeigen.

2. Rosa markierte Zeilen (35 bis 38)
Hier gab es einen zeitlichen Unterbruch, aber erst zwischen dem zweiten und dritten Datum. In Zeile 35 und Zeile 38 sollte "Ohne Unterbruch" angezeigt werden und in den Zeilen 36 und 37 "Mit Unterbruch".

 

Hallo,

zu 1.) in I2:
=MAX(HEUTE()*(AGGREGAT(14;6;$B$2:$B$40/($A$2:$A$40=$A2);1)>AGGREGAT(14;6;$C$2:$C$40/($A$2:$A$40=$A2);1));AGGREGAT(14;6;$C$2:$C$40/($A$2:$A$40=$A2);1))
und diese nach unten kopieren.

zu 2.) wie begründet sich Deine Zielvorstellung dafür?

 

Toll! Danke!

zu 2.: Ich möchte hier wissen, welcher Eintritt nach einem Unterbruch geschieht und welcher Eintritt ein "neuer" Eintritt ist.

 

Hallo,

mir erschließt sich noch immer nicht, warum bzw. woraus begründet L35 und L38 "ohne Unterbruch" ergeben solle und für L36 und L37 nicht. Begründe das doch mal für einen außen stehenden wie z.B. mich nachvollziehbar und vor allem so, dass es nicht im Widerspruch steht zu den anderen Ergebniswerten in Spalte J. Nur dann kann ich die Formel in J enstprechend definieren.

 

Meine Aufgabe ist es, (1) zu berechnen, wie lange ein Person im Programm anwesend war. Das kann ich anhand des Eintritts- und Austrittsdatums. Manchmal ändert sich für eine Person ein kleines Detail (zB Einsatzort), weshalb es zu einer neuen Zeile kommt (zB Zeile 35 und 36: die Person hat am 10.9.2019 mit dem Programm begonnen und am 1.11.2019 einen neuen Einsatzort erhalten und war bis zum 8.12.2019 dort. Das sind insgesamt 88 Tage.)

Zudem ist meine Aufgabe, (2) Unterbrüche zu erkennen. In Zeile 37 ist ersichtlich, dass die Person nach einer Unterbrechung (abwesend vom 8.12.2019 bis 2.11.2020) wieder in einem Programm war. Hier handelt es sich um einen NEUEN Eintritt. Hier ist nicht relevant, dass die Person schon mal hier war, sie wird wie eine "neue" Person behandelt, auch wenn die Personennummer (leider) dieselbe ist.

Ist es nun klarer?

 

Hallo,

für mich noch nicht wirklich. Weshalb soll denn in L38 "Ohne ..." ermittelt werden, wie Du vorgegeben hast?

 

Oh, das war ein Fehler von mir! Entschuldige!
Ich habe die Datei nochmals angehängt, so wie ich mir das Ergebnis vorstelle.

 

Hallo ???,

jetzt ergeben sich aber mE noch Differenzen in L6, L15 und L34. Ich würde dort jeweils auch "mit Unterbruch" als korrekt betrachten. Oder? Wenn das geklärt ist, kann ich Dir auch eine Formellösung aufzeigen.

 

Hallo

Ich habe meine Daten nochmals gründlich untersucht und erkannt, dass ich folgende Berechnungen brauche:

1. Spalte D "Echter Eintritt": Für jede Zeile soll das Eintrittsdatum angegeben werden. Ist eine Person mehrmals eingetreten ohne Unterbrechung (Spalte K) (wie in Zeile 6), soll das Eintrittsdatum leer bleiben (das wird hier anhand der verwendeten Formel bereits ausgewiesen). Ist die Person nach einer Unterbrechung eingetreten (zB Zeile 35), soll das neue Eintrittsdatum ausgewiesen werden (dafür muss die Formel ergänzt werden).

2. Spalte K "Erster Eintritt oder Eintritt nach Unterbruch": Hier wird angezeigt, ob es sich um den ersten Eintritt (Zeile 2) oder Eintritt nach Unterbruch handelt (Zeile 35) = ja. Oder ob es zu keinem zeitlichen Unterbruch gekommen ist (Zeile 6)=Nein.

3. Spalte I "Jüngster Austritt": Zeigt das Austrittsdatum. Bei mehreren Eintritten einer Person, wird das jüngste Austrittsdatum gezeigt (Zeile 5 und 6 oder 35). Wenn die Person noch nicht ausgetreten ist, das heutige Datum widergegeben (Zeile 22 und 23).

4. Spalte J "Anzahl Tage Aufenthalt": Hier soll die Anzahl an Tagen des Aufenthalts gezeigt werden.

Ich bin sehr froh, um eure Hilfe ;-)

 

Hi,

also, wenn eine Excel-Version MINWENNS nicht kennt, dann ich finde {=MIN(WENN($A$2:$A$1976=A2;$B$2:$B$1976;""))} (eingegeben als Matrix-Formel) wesentlich übersichtlicher als =AGGREGAT(15;6;$B$2:$B$1976/($A$2:$A$1976=$A2);1)

 

Hallo Helmut,

Deine Aussage "... wesentlich übersichtlicher..." teile ich nicht, denn dies ist mE Ansichtssache. Aber entscheidender ist, dass Deine Aussage zur noch offenen Problemstellung nicht beiträgt.

Hallo Nicole,

Deine neue Datei klärt nun zwar einige meiner Fragen, wirft dafür aber neue wieder auf.
In D39 liegt sicher ein Schreibfehler Deinerseits vor. In G33:G34 und G38:G39 sowie in I33: I36 und I38 ermittele ich mit meinen nachfolgenden Formeln, die ich Deinen verbalen Aussagen angepasst habe, andere Werte. Prüfe das.

Zunächst sollte von Dir eine wesentliche Vorbedingung bestätigt/realisiert werden. Die Datentabelle muss nach PNr. und dann nach Eintritt aufwärts sortiert vorliegen. Wenn das gewährleistet ist, dann sind verhältnismäßig sehr einfache Formeln notwendig. Und zwar:

in D2: =WENN((A2=A1)*(B2-1=C1);"";B2)
in H2: =WENN(A2=A1;H1;B2)
in I2: =WENN((A3=A2)*(C2>0);I3;C2+HEUTE()*(C2=""))
in K2: =WENN((A2<>A1)+(A2=A1)*(B2-WENN(ISTZAHL(C1);C1;0)<>1);"Ja";"Nein")

Nur in Spalte G ist die Formel komplizierter, momentan z.B. in G2 so:
=AGGREGAT(14;6;(C$2:C9+(C$2:C9=0)*(HEUTE()-1))/(A$2:A9=A2);1)-AGGREGAT(15;6;B$2:B9/(A$2:A9=A2);1)

Alle Formeln ziehend nach unten kopieren. Wie bereits geschrieben, ergeben diese dann in den benannten Zellen andere Ergebniswerte als Deine Vorgabewerte. Die Formelergebniswerte erscheinen mir allerdings auch logischer. Prüfe aber und solltest Du in den entsprechenden Zellen immer noch andere Ergebniswerte erwarten, dann begründe dies entsprechend.

 

Vielen Dank, Werner!

Genau, in D39 hat sich ein Tippfehler eingeschlichen!

Leider können die Daten nicht sortiert werden! Wäre dies der Fall, könnte ich mich wahrscheinlich ohne eure Hilfe durchschlagen ;-)

In den Zeilen 33 bis 36 handelt es sich um Sonderfälle (siehe meine Beschreibung oben). Trotz der gleichen Personenzahl wird der Eintritt neu berechnet, weil die Personen nach einem Unterbruch wieder eingestiegen ist. Mit deiner Formel wird nicht beachtet, dass es sich um einen Eintritt nach einem Unterbruch handelt.

Ich wäre superdankbar, wenn es eine Lösung ohne Sortieren gibt!!!

 

Hallo Nicole,

also Deine zuletzt eingestellte Datei ist doch entsprechend (vor)sortiert! Meine zuletzt eingestellten Formeln ergeben somit zumindest in Spalte D, H und K auch exakt die von Dir vorgegebenen Werte. Jedenfalls bei mir.

Was Du mit dem "Unterbruch" meintest, hab ich nun nachvollziehen können. Um diesen korrekt zu berücksichtigen, muss die Formel in Spalte I nur mit einer kleiner Ergänzungsbedingung erweitert werden (nachstehend fett markiert). Dafür wird die Formel in Spalte J nun auch wesentlich einfacher als zuvor.

In I2 nun: =WENN((A3=A2)*(C2>0)*(B3-C2=1);I3;C2+HEUTE()*(C2=""))
In J2 nun: =WENN((A1=A2)*(I1=I2);J1;I2-B2)

und auch diese beiden nach unten kopieren.

 

Hallo Werner

In meiner Tabelle sind die Zeilen sortiert, um einen besseren Überblick zu behalten und die Richtigkeit der Formeln nachvollziehen zu können. Ausserdem ist die Tabelle in Wirklichkeit deutlich umfangreicher.
Mit sortieren Zeilen passen deine Formeln perfekt!

Ich muss die Formeln auch im unsortierten Zustand anwenden können.

 

Hallo Nicole,

ich kann mir nicht vorstellen, welchen Grund es geben soll, Deine Quelldaten nicht nach PersNr. und dann nach Eintritt zu sortieren. Dies geht zumindest in einer Kopie, die mit der Power Query - Funktionalität von Excel aus Deinen Originaldaten erstellt werden können und dann dort entsprechend mit meinen Formeln ausgewertet werden können.

Wenn aber die Originaldaten unsortiert sind und diesbzgl. direkt ausgewertet werden sollen, dann stelle doch mal eine entsprechende Beispieldatei mit max ca 50 Datensätzen hier ein. Eine direkte Formelauswertung ist sicher auch dann möglich, Jedoch werden die Formel dann wieder erheblich aufwendiger.

Außerdem wäre es dafür auch noch gut zu wissen, wie viele Datensätze ca. max in Deiner Originaldatei auszuwerten sind?