verwendete Formeln | |
Zelle | Formel | Bereich | R1C1 für Add In |
D1 | =D2 | | =R[1]C |
S1 | =DATUM(JAHR(C2)+3;MONAT(C2);TAG(C2)) | | =DATE(YEAR(R[1]C[-16])+3,MONTH(R[1]C[-16]),DAY(R[1]C[-16])) |
H2 | =D2 | | =RC[-4] |
L2 | =D2 | | =RC[-8] |
G2,K2,O2 | =C3+1 | | =R[1]C[-4]+1 |
P2 | =D2 | | =RC[-12] |
Q1:Q2 | =JAHR(C2) | | =YEAR(R[1]C[-14]) |
C3,G3 | =DATUM(JAHR(C2);MONAT(C2)+6;TAG(C2)-1) | | =DATE(YEAR(R[-1]C),MONTH(R[-1]C)+6,DAY(R[-1]C)-1) |
K3,O3 | =DATUM(JAHR(K2)+1;MONAT(K2);TAG(K2)-1) | | =DATE(YEAR(R[-1]C)+1,MONTH(R[-1]C),DAY(R[-1]C)-1) |
Q3 | =JAHR(C3)+1 | | =YEAR(RC[-14])+1 |
D4 | =WENN(A2=0;0;D2-D3) | | =IF(R[-2]C[-3]=0,0,R[-2]C-R[-1]C) |
G4 | =WENN(A2=0;0;H2-H3) | | =IF(R[-2]C[-6]=0,0,R[-2]C[1]-R[-1]C[1]) |
H4 | =WENN(A2=0;0;H2-H3) | | =IF(R[-2]C[-7]=0,0,R[-2]C-R[-1]C) |
K4 | =WENN(A2=0;0;L2-L3) | | =IF(R[-2]C[-10]=0,0,R[-2]C[1]-R[-1]C[1]) |
L4 | =WENN(A2=0;0;L2-L3) | | =IF(R[-2]C[-11]=0,0,R[-2]C-R[-1]C) |
O4 | =WENN(A2=0;0;P2-P3) | | =IF(R[-2]C[-14]=0,0,R[-2]C[1]-R[-1]C[1]) |
P4 | =WENN(A2=0;0;P2-P3) | | =IF(R[-2]C[-15]=0,0,R[-2]C-R[-1]C) |
Q4 | =JAHR(C2)+3 | | =YEAR(R[-2]C[-14])+3 |
R1:R4 | =SUMMEWENN($B$5:$B$6;Q1;$C$5:$C$6)+SUMMEWENN($F$5:$F$6;Q1;$G$5:$G$6)+SUMMEWENN($J$5:$J$6;Q1;$K$5:$K$6)+SUMMEWENN($N$5:$N$6;Q1;$O$5:$O$6) | | =SUMIF(R5C2:R6C2,RC[-1],R5C3:R6C3)+SUMIF(R5C6:R6C6,RC[-1],R5C7:R6C7)+SUMIF(R5C10:R6C10,RC[-1],R5C11:R6C11)+SUMIF(R5C14:R6C14,RC[-1],R5C15:R6C15) |
C5 | =AB2 | | =R[-3]C[25] |
G5 | =AQ2 | | =R[-3]C[36] |
K5 | =BF2 | | =R[-3]C[47] |
O5 | =BU2 | | =R[-3]C[58] |
Q5 | =C2 | | =R[-3]C[-14] |
R5 | =C7 | | =R[2]C[-15] |
C6 | =AJ2 | | =R[-4]C[33] |
G6 | =AY2 | | =R[-4]C[44] |
K6 | =BN2 | | =R[-4]C[55] |
B5:B6,F5:F6,J5:J6,N5:N6 | =JAHR(C2) | | =YEAR(R[-3]C[1]) |
O6 | =CC2 | | =R[-4]C[66] |
Q6 | =DATUM(JAHR(C2);MONAT(C2)+6;TAG(C2)) | | =DATE(YEAR(R[-4]C[-14]),MONTH(R[-4]C[-14])+6,DAY(R[-4]C[-14])) |
R6 | =C8+G7+K7 | | =R[2]C[-15]+R[1]C[-11]+R[1]C[-7] |
C7 | =AB3 | | =R[-4]C[25] |
G7 | =AQ3 | | =R[-4]C[36] |
K7 | =BF3 | | =R[-4]C[47] |
O7 | =BU3 | | =R[-4]C[58] |
Q7 | =DATUM(JAHR(C2)+1;MONAT(C2);TAG(C2)) | | =DATE(YEAR(R[-5]C[-14])+1,MONTH(R[-5]C[-14]),DAY(R[-5]C[-14])) |
R7 | =K8+O7 | | =R[1]C[-7]+RC[-3] |
C8 | =AJ3 | | =R[-5]C[33] |
G8 | =AY3 | | =R[-5]C[44] |
K8 | =BN3 | | =R[-5]C[55] |
B7:B8,F7:F8,J7:J8,N7:N8 | =C2 | | =R[-5]C[1] |
O8 | =CC3 | | =R[-5]C[66] |
Q8 | =DATUM(JAHR(C2)+2;MONAT(C2);TAG(C2)) | | =DATE(YEAR(R[-6]C[-14])+2,MONTH(R[-6]C[-14]),DAY(R[-6]C[-14])) |
R8 | =O8 | | =RC[-3] |
S8 | =DATUM(JAHR(C2)+3;MONAT(C2);TAG(C2)) | | =DATE(YEAR(R[-6]C[-16])+3,MONTH(R[-6]C[-16]),DAY(R[-6]C[-16])) |