EXCEL 公式運算

至於在工作表 5上要計算累積會員人數(原工作表 4)，假設在A1輸入「今月」年份(例如2015)或用公式「=YEAR(TODAY())」、在A2輸入「今月」月份(例如5) 或用公式「=MONTH(TODAY())」，亦假設儲存格B1、C1合併成B1，儲存格D1、E1合併成D1。各公式以三條件操作如下：
A3上月累積(a) ：
B3 =COUNTIFS(工作表 1!F2:F5001,B$2,H2:H5001,LEFT(B$1,4),J2:J5001,"<"&DATE($A$1,$A$2,1))
C3 =COUNTIFS(工作表 1!F2:F5001,C$2,H2:H5001,LEFT(B$1,4),J2:J5001,"<"&DATE($A$1,$A$2,1))
D3 =COUNTIFS(工作表 1!F2:F5001,D$2,H2:H5001,LEFT(D$1,4),J2:J5001,"<"&DATE($A$1,$A$2,1))
E3 =COUNTIFS(工作表 1!F2:F5001,E$2,H2:H5001,LEFT(D$1,4),J2:J5001,"<"&DATE($A$1,$A$2,1))

A4今月新增(b)：
B4 =COUNTIFS(工作表 1!F2:F5001,B$2,H2:H5001,LEFT(B$1,4),J2:J5001,">="&DATE($A$1,$A$2,1))
C4 =COUNTIFS(工作表 1!F2:F5001,C$2,H2:H5001,LEFT(B$1,4),J2:J5001,">="&DATE($A$1,$A$2,1))
D4 =COUNTIFS(工作表 1!F2:F5001,D$2,H2:H5001,LEFT(D$1,4),J2:J5001,">="&DATE($A$1,$A$2,1))
E4 =COUNTIFS(工作表 1!F2:F5001,E$2,H2:H5001,LEFT(D$1,4),J2:J5001,">="&DATE($A$1,$A$2,1))

A5今月續會(c)：
B5 =COUNTIFS(工作表 1!F2:F5001,B$2,H2:H5001,LEFT(B$1,4),M2:M5001,">="&DATE($A$1,$A$2,1))
C5 =COUNTIFS(工作表 1!F2:F5001,C$2,H2:H5001,LEFT(B$1,4),M2:M5001,">="&DATE($A$1,$A$2,1))
D5 =COUNTIFS(工作表 1!F2:F5001,D$2,H2:H5001,LEFT(D$1,4),M2:M5001,">="&DATE($A$1,$A$2,1))
E5 =COUNTIFS(工作表 1!F2:F5001,E$2,H2:H5001,LEFT(D$1,4),M2:M5001,">="&DATE($A$1,$A$2,1))

A6今月刪除(d)：
B6 =COUNTIFS(工作表 1!F2:F5001,B$2,H2:H5001,LEFT(B$1,4),P2:P5001,">="&DATE($A$1,$A$2,1))
C6 =COUNTIFS(工作表 1!F2:F5001,C$2,H2:H5001,LEFT(B$1,4),P2:P5001,">="&DATE($A$1,$A$2,1))
D6 =COUNTIFS(工作表 1!F2:F5001,D$2,H2:H5001,LEFT(D$1,4),P2:P5001,">="&DATE($A$1,$A$2,1))
E6 =COUNTIFS(工作表 1!F2:F5001,E$2,H2:H5001,LEFT(D$1,4),P2:P5001,">="&DATE($A$1,$A$2,1))

A7今月到期(e)：
B4 =COUNTIFS(工作表 1!F2:F5001,B$2,H2:H5001,LEFT(B$1,4),K2:K5001,">="&DATE($A$1,$A$2,1))
C4 =COUNTIFS(工作表 1!F2:F5001,C$2,H2:H5001,LEFT(B$1,4),K2:K5001,">="&DATE($A$1,$A$2,1))
D4 =COUNTIFS(工作表 1!F2:F5001,D$2,H2:H5001,LEFT(D$1,4),K2:K5001,">="&DATE($A$1,$A$2,1))
E4 =COUNTIFS(工作表 1!F2:F5001,E$2,H2:H5001,LEFT(D$1,4),K2:K5001,">="&DATE($A$1,$A$2,1))

2015-05-12 10:34:52 補充：
A8小計(a)+(b)+(c)-(d)-(e) ：
B8 =B3+B4+B5-B6-B7
C8 =C3+C4+C5-C6-C7
D8 =D3+D4+D5-D6-D7
E8 =E3+E4+E5-E6-E7

A9總數：假設B9、C9合併成B9, D9、E9合併成D9
B9 =B8+C8
D9 =D8+E8

由於公式數目較多，應該用一些簡單數字測試公式答案是否在預計之中，尤其是在第8列加減公式經估量修改，與原意或有出入。

2015-05-12 16:26:42 補充：
「A7今月到期(e)」下面的四條公式應該是放到B7:E7才對。

鑑於「今月」的定義不一定是真正實時的「今月」，在有「今月」的公式可加入多一條件「下月之前」，如果想查看以前月份(例如2015年2月)也可以：
K2:K5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1)
舉例B7公式修改如下：

2015-05-12 16:26:51 補充：
B7 =COUNTIFS(工作表 1!F2:F5001,B$2,H2:H5001,LEFT(B$1,4),K2:K5001,">="&DATE($A$1,$A$2,1), K2:K5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))