Hallo,
ich möchte die Anzahl der anwesenden Tage von Personen berechnen,
welche innerhalb eines Zeitraumes liegen und diese gleich automatisch addieren?
Es gibt für jede Person ein Anreise- und Abreisedatum:
Personen Einzugsdatum Auszugsdatum
Person 1: 22.12.14 - 13.05.15
Person 2: 22.03.15 - 01.05.15
Person 3: 22.01.14 - 04.02.15
Person 4: 01.01.14
Und einen vorgegebenen Zeitraum welcher für die Tage relevant ist: 01.01.2015-28.02.2015
In diesem Fall würden also die Tage von:
Person 1: mit 1.1.2015-28.2.2015
Person 2: 0
Person 3: mit 1.1.2015-04.02.2015
Person 4: mit 1.1.2015-28.2.2015
gerechnet werden.
Mit SUMMENPRODUKT und DATEDIF erscheint mir das recht kompliziert, da ich recht viele Abfragen machen muss, oder?
Hat jemand eine Idee, ob man das irgendwie einfach gestalten kann?
Ich habe leider noch nicht so richtig den Durchblick bei komplexeren Formeln in Excel und wäre für jede Hilfe dankbar...
(16.07.2015, 05:42)shift-del schrieb: [ -> ]Meinst du es so?
Vielen Dank erstmal für die Antwort!
Ja, im Grunde genommen schon, nur sollte das Ergebnis nicht als einzelne Einträge sondern gleich alles zusammengerechnet als ein Wert sein.
D.h. in dem Falle würde nur die Zahl 150 rauskommen.
Ich habe es gerade mit
=SUMMENPRODUKT((B2:B5<>"")*(MAX(0;(MIN(C2:C5;$G$2)+1)-MAX(B2:B5;$F$2))))
probiert, da kommt allerdings nur 0 raus.
Naja, gut, wenn ich mir das so anschaue, kann es nicht klappen, da er in meiner abgeänderten Formel ja nur jeweils MIN & MAX einer Spalte (B bzw. C) berechnet und nicht die Formel für jede Zeile einzeln, richtig?
Wie ist denn dafür der Lösungsansatz, wenn das mit MIN & MAX nicht als array klappt?
Zitat:nur sollte das Ergebnis nicht als einzelne Einträge sondern gleich alles zusammengerechnet als ein Wert sein.
D.h. in dem Falle würde nur die Zahl 150 rauskommen.
Tja, warum auch den einfachen Weg nehmen.
Geht bestimmt auch kürzer.
Code:
{=SUMME(WENN(WENN(C2:C5<=$G$2;WENN(C2:C5="";$G$2;C2:C5);$G$2)<WENN(B2:B5>=$F$2;B2:B5;$F$2);0;WENN(C2:C5<=$G$2;WENN(C2:C5="";$G$2;C2:C5);$G$2)-WENN(B2:B5>=$F$2;B2:B5;$F$2)))}
Zitat:Geht bestimmt auch kürzer.
Länge halbiert.
Code:
=SUMMENPRODUKT((C2:C5>F2)*(B2:B5<F2)*(G2-F2-((G2-C2:C5)>0)*(G2-C2:C5)))
Voraussetzung ist dass es in Spalte C keine leeren Zellen geben darf; d.h. man trägt dort 31.12.9999 ein.
Hallo,
eine etwas längere Formel, die aber mit Leerzellen in C umgehen kann:
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| | A | B | C | D | E | F | G |
| 1 | | | | | | Startdatum | 01.01.2015 |
| 2 | | | | | | Endedatum | 28.02.2015 |
| 3 | Person | Einzug | Auszug | Tage | | | |
| 4 | Person 1 | 22.12.2014 | 13.05.2015 | 59 | | 116 | |
| 5 | Person 2 | 22.03.2015 | 01.05.2015 | 0 | | | |
| 6 | Person 3 | 22.01.2014 | 30.01.2014 | 35 | | | |
| 7 | Person 4 | 01.01.2014 | | 59 | | | |
| Zelle | Formel |
| F4 | {=SUMME(WENN(C4:C7="";G2;WENN(C4:C7>G2;G2;WENN(C4:C7<G1;0;C4:C7)))-WENN((C4:C7<G1)*(C4:C7<>"");0;WENN(B4:B7<G1;G1;WENN(B4:B7>G2;G2;B4:B7))))} |
| Achtung, Matrixformel enthalten! |
| Die geschweiften Klammern{} werden nicht eingegeben. |
| Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine. |
| Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg |
(17.07.2015, 04:29)shift-del schrieb: [ -> ]Zitat:Geht bestimmt auch kürzer.
Länge halbiert.
Code:
=SUMMENPRODUKT((C2:C5>F2)*(B2:B5<F2)*(G2-F2-((G2-C2:C5)>0)*(G2-C2:C5)))
Das ist mal übersichtlich! Danke!
Ich schaue mir mal beide Formeln an, und versuche die zu verstehen. Allerdings werde ich wahrscheinlich doch die längere benutzen, da Spalte C auf jeden Fall leere Zellen enthalten wird. Die Excel Tabelle ist nicht für mich, und der Übersicht halber und aus anderen Gründen gibt es leider die leeren Zellen. Aber vielen Dank für deine Hilfe! Ist auch für mich zum Verstehen und Lernen wichtig.
Was allerdings bei den Formeln merkwürdig ist,
Bei einer kommt:
1) C 22.01.2014 - 04.02.2015 : 34 Tage
und bei der anderen:
2) Person 3 22.01.2014-30.01.2014: 35 Tage
raus.
Bei 2) müssten es eigentlich 0 Tage sein, da es sich um 2014 handelt, bei 1) müssten es 14 Tage sein, oder irre ich mich?
Ich schau nochmal....