EXCEL 公式運算

原先公式中「工作表1」或「工作表2」部份在「1」或「2」前多了一個空格，所以導致出現#NAME？情況，此外所有公式中凡由第2列至5001列的儲存格範圍前面應該都要加上「工作表1!」或「工作表2!」才對，現修正公式如下：

1. 在工作表1上 M2:O2三道公式：
M2 =LOOKUP(2,1/(工作表2!$A$2:$A$75000=$D2),工作表2!$E$2:$E$75000)
N2 =LOOKUP(2,1/(工作表2!$A$2:$A$75000=$D2),工作表2!$F$2:$F$75000)
O2 =COUNTIF(工作表2!$A$2:$A$75000, $D2)

2. 在工作表 5上累計表公式：
A3上月累積(a) ：
B3 =COUNTIFS(工作表1!F2:F5001,B$2,工作表1!H2:H5001,LEFT(B$1,4),工作表1!J2:J5001,"<"&DATE($A$1,$A$2,1))
C3 =COUNTIFS(工作表1!F2:F5001,C$2,工作表1!H2:H5001,LEFT(B$1,4),工作表1!J2:J5001,"<"&DATE($A$1,$A$2,1))
D3 =COUNTIFS(工作表1!F2:F5001,D$2,工作表1!H2:H5001,LEFT(D$1,4),工作表1!J2:J5001,"<"&DATE($A$1,$A$2,1))
E3 =COUNTIFS(工作表1!F2:F5001,E$2,工作表1!H2:H5001,LEFT(D$1,4),工作表1!J2:J5001,"<"&DATE($A$1,$A$2,1))

A4今月新增(b)：
B4 =COUNTIFS(工作表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))
C4 =COUNTIFS(工作表1!F2:F5001,C$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))
D4 =COUNTIFS(工作表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))
E4 =COUNTIFS(工作表1!F2:F5001,E$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))

A5今月續會(c)：
B5 =COUNTIFS(工作表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))
C5 =COUNTIFS(工作表1!F2:F5001,C$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))
D5 =COUNTIFS(工作表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-13 10:31:54 補充：
E5 =COUNTIFS(工作表1!F2:F5001,E$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-13 10:32:22 補充：
A6今月刪除(d)：
B6 =COUNTIFS(工作表1!F2:F5001,B$2,工作表1!H2:H5001,LEFT(B$1,4),工作表1!P2:P5001,">="&DATE($A$1,$A$2,1),工作表1!P2:P5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-13 10:32:55 補充：
C6 =COUNTIFS(工作表1!F2:F5001,C$2,工作表1!H2:H5001,LEFT(B$1,4),工作表1!P2:P5001,">="&DATE($A$1,$A$2,1),工作表1!P2:P5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-13 10:33:24 補充：
D6 =COUNTIFS(工作表1!F2:F5001,D$2,工作表1!H2:H5001,LEFT(D$1,4),工作表1!P2:P5001,">="&DATE($A$1,$A$2,1),工作表1!P2:P5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-13 10:33:38 補充：
E6 =COUNTIFS(工作表1!F2:F5001,E$2,工作表1!H2:H5001,LEFT(D$1,4),工作表1!P2:P5001,">="&DATE($A$1,$A$2,1),工作表1!P2:P5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-13 10:34:04 補充：
A7今月到期(e)：
B7 =COUNTIFS(工作表1!F2:F5001,B$2,工作表1!H2:H5001,LEFT(B$1,4),工作表1!K2:K5001,">="&DATE($A$1,$A$2,1),工作表1!K2:K5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-13 10:34:33 補充：
C7 =COUNTIFS(工作表1!F2:F5001,C$2,工作表1!H2:H5001,LEFT(B$1,4),工作表1!K2:K5001,">="&DATE($A$1,$A$2,1),工作表1!K2:K5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-13 10:34:54 補充：
D7 =COUNTIFS(工作表1!F2:F5001,D$2,工作表1!H2:H5001,LEFT(D$1,4),工作表1!K2:K5001,">="&DATE($A$1,$A$2,1),工作表1!K2:K5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))

2015-05-13 10:35:00 補充：
E7 =COUNTIFS(工作表1!F2:F5001,E$2,工作表1!H2:H5001,LEFT(D$1,4),工作表1!K2:K5001,">="&DATE($A$1,$A$2,1),工作表1!K2:K5001,"<"&DATE(if($A$2=12,$A$1+1, $A$1),if($A$2=12,1,$A$2+1),1))