EXCEL 公式運算

在工作表 7上各累計表公式加多一個「會員職業分佈」的篩選條件(括弧中第一項)，另外如刪除的會員紀錄不放在工作表1，便要修改B7:I7公式中「工作表1」為放置刪除會員紀錄的工作表名稱：
A4上月累積(a) ：
B4 =COUNTIFS(工作表1!V2:V5001,B$3,工作表1!F2:F5001,B$2,工作表1!H2:H5001,LEFT(B$1,4),工作表1!J2:J5001,"<"&DATE($A$1,$A$2,1))
C4 =COUNTIFS(工作表1!V2:V5001,C$3,工作表1!F2:F5001,C$2,工作表1!H2:H5001,LEFT(C$1,4),工作表1!J2:J5001,"<"&DATE($A$1,$A$2,1))
D4 =COUNTIFS(工作表1!V2:V5001,D$3,工作表1!F2:F5001,D$2,工作表1!H2:H5001,LEFT(D$1,4),工作表1!J2:J5001,"<"&DATE($A$1,$A$2,1))
E4 =COUNTIFS(工作表1!V2:V5001,E$3,工作表1!F2:F5001,E$2,工作表1!H2:H5001,LEFT(E$1,4),工作表1!J2:J5001,"<"&DATE($A$1,$A$2,1))
F4 =COUNTIFS(工作表1!V2:V5001,F$3,工作表1!F2:F5001,F$2,工作表1!H2:H5001,LEFT(F$1,4),工作表1!J2:J5001,"<"&DATE($A$1,$A$2,1))
G4 =COUNTIFS(工作表1!V2:V5001,G$3,工作表1!F2:F5001,G$2,工作表1!H2:H5001,LEFT(G$1,4),工作表1!J2:J5001,"<"&DATE($A$1,$A$2,1))
H4 =COUNTIFS(工作表1!V2:V5001,H$3,工作表1!F2:F5001,H$2,工作表1!H2:H5001,LEFT(H$1,4),工作表1!J2:J5001,"<"&DATE($A$1,$A$2,1))
I4 =COUNTIFS(工作表1!V2:V5001,I$3,工作表1!F2:F5001,I$2,工作表1!H2:H5001,LEFT(I$1,4),工作表1!J2:J5001,"<"&DATE($A$1,$A$2,1))

A5今月新增(b)：
B5 =COUNTIFS(工作表1!V2:V5001,B$3,工作表1!F2:F5001,B$2,工作表1!H2:H5001,LEFT(B$1,4),工作表1!J2:J5001,">="&DATE($A$1,$A$2,1),工作表1!J2:J5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))
C5 =COUNTIFS(工作表1!V2:V5001,C$3,工作表1!F2:F5001,C$2,工作表1!H2:H5001,LEFT(C$1,4),工作表1!J2:J5001,">="&DATE($A$1,$A$2,1),工作表1!J2:J5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))
D5 =COUNTIFS(工作表1!V2:V5001,D$3,工作表1!F2:F5001,D$2,工作表1!H2:H5001,LEFT(D$1,4),工作表1!J2:J5001,">="&DATE($A$1,$A$2,1),工作表1!J2:J5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))
E5 =COUNTIFS(工作表1!V2:V5001,E$3,工作表1!F2:F5001,E$2,工作表1!H2:H5001,LEFT(E$1,4),工作表1!J2:J5001,">="&DATE($A$1,$A$2,1),工作表1!J2:J5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))
F5 =COUNTIFS(工作表1!V2:V5001,F$3,工作表1!F2:F5001,F$2,工作表1!H2:H5001,LEFT(F$1,4),工作表1!J2:J5001,">="&DATE($A$1,$A$2,1),工作表1!J2:J5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-25 18:59:54 補充：
G5 =COUNTIFS(工作表1!V2:V5001,G$3,工作表1!F2:F5001,G$2,工作表1!H2:H5001,LEFT(G$1,4),工作表1!J2:J5001,">="&DATE($A$1,$A$2,1),工作表1!J2:J5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-25 19:00:06 補充：
H5 =COUNTIFS(工作表1!V2:V5001,H$3,工作表1!F2:F5001,H$2,工作表1!H2:H5001,LEFT(H$1,4),工作表1!J2:J5001,">="&DATE($A$1,$A$2,1),工作表1!J2:J5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-25 19:00:14 補充：
I5 =COUNTIFS(工作表1!V2:V5001,I$3,工作表1!F2:F5001,I$2,工作表1!H2:H5001,LEFT(I$1,4),工作表1!J2:J5001,">="&DATE($A$1,$A$2,1),工作表1!J2:J5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-25 19:00:49 補充：
A6今月續會(c)：
B6 =COUNTIFS(工作表1!V2:V5001,B$3,工作表1!F2:F5001,B$2,工作表1!H2:H5001,LEFT(B$1,4),工作表1!M2:M5001,">="&DATE($A$1,$A$2,1),工作表1!M2:M5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-25 19:00:56 補充：
C6 =COUNTIFS(工作表1!V2:V5001,C$3,工作表1!F2:F5001,C$2,工作表1!H2:H5001,LEFT(C$1,4),工作表1!M2:M5001,">="&DATE($A$1,$A$2,1),工作表1!M2:M5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-25 19:01:18 補充：
D6 =COUNTIFS(工作表1!V2:V5001,D$3,工作表1!F2:F5001,D$2,工作表1!H2:H5001,LEFT(D$1,4),工作表1!M2:M5001,">="&DATE($A$1,$A$2,1),工作表1!M2:M5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-25 19:01:38 補充：
E6 =COUNTIFS(工作表1!V2:V5001,E$3,工作表1!F2:F5001,E$2,工作表1!H2:H5001,LEFT(E$1,4),工作表1!M2:M5001,">="&DATE($A$1,$A$2,1),工作表1!M2:M5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-25 19:02:15 補充：
F6 =COUNTIFS(工作表1!V2:V5001,F$3,工作表1!F2:F5001,F$2,工作表1!H2:H5001,LEFT(F$1,4),工作表1!M2:M5001,">="&DATE($A$1,$A$2,1),工作表1!M2:M5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-25 19:02:35 補充：
G6 =COUNTIFS(工作表1!V2:V5001,G$3,工作表1!F2:F5001,G$2,工作表1!H2:H5001,LEFT(G$1,4),工作表1!M2:M5001,">="&DATE($A$1,$A$2,1),工作表1!M2:M5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-25 19:02:55 補充：
H6 =COUNTIFS(工作表1!V2:V5001,H$3,工作表1!F2:F5001,H$2,工作表1!H2:H5001,LEFT(H$1,4),工作表1!M2:M5001,">="&DATE($A$1,$A$2,1),工作表1!M2:M5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))