Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Listen/Datensätze

Beitrag: datumsabhängige Mietpreise

Aufgabe
Je nach Saison (Schulferien/Feiertage) variieren die Mieten pro Tag z.B. für ein Ferienhaus.
Die Gesamtkosten sollen datumsabhängig ermittelt werden.

       A               B               C               D               E               F               G               H       
1 Preise  gültig ab    Ankunft  Abreise       
2 70,00  03.01.2003    22.12.2003  05.01.2004       
3 75,00  01.04.2003             
4 100,00  01.06.2003             
5 150,00  16.07.2003    vom  bis  Preis  Tage   
6 100,00  01.09.2003    22.12.2003  23.12.2003  70,00  2  140,00  
7 70,00  16.09.2003    24.12.2003  02.01.2004  120,00  10  1.200,00  
8 120,00  24.12.2003    03.01.2004  05.01.2004  95,00  3  285,00  
9 95,00  03.01.2004             
10 80,00  01.04.2004          15  1.625,00  
11 110,00  01.06.2004             
12 160,00  16.07.2004             
13   01.01.2005             


Lösung
Ab A2 stehen die Tagesmieten, ab B2 die Gültigkeitstermine, der erste Tag in D2 und der letzte in E2.

in D6 steht =D2
in D7:
=WENN(ODER(E6=$E$2;D6="");"";E6+1)

in E6:
{=WENN(ODER(E5="";E5=$E$2);"";MIN(MIN(WENN(B$2:B$50>D6;B$2:B$50))-1;E$2))}

in F6:
=WENN(E6="";"";INDEX(A:A;VERGLEICH(E6;B:B;1)))

in G6:
=WENN(E4=E$2;SUMME(G$5:G5);WENN(F6="";"";E6-D6+1))

in H6:
=WENN(E4=E$2;SUMME(H$5:H5);WENN(G6="";"";F6*G6))

D7 und E6:H6 runterkopieren


Download


Erläuterung

Sollen nicht Tage sondern Nächte berechnet werden (der Ankunftstag ist keine Nacht), dann in G6:
=WENN(E4=E$2;SUMME(G$5:G5);WENN(F6="";"";E6-D6+(D6<>D$2)))