C2:=N(A2)-N(B2)向下複製公式到C3:C14。
(各版本恉可)A1:=SUM(IF(B3:B23=B1,A3:A23,""))2019以前版本要按CTRL+SHIFT+ENTER鍵，201...
D8:=SUM(INDIRECT(ADDRESS(2,2,4)&":"&ADDRESS(3,3,4)))D8:=SUM(IN...
一、FILTER函數作法：(365,2021版本適合)C9:=SUM(FILTER(FILTER($C$2:$O$6,($B$2:$B$6=$B9) * ($A...
我用三色來識別，1為填滿空白，2為填滿黃色，空格為填滿紅色。點取「常用 > 條件式格式設定 > 新增規則」自己點取格式鈕設定背景及字型色彩。
(輔助欄)D2:=IF(SUBTOTAL(3,C2:C2)=1,1,0)向下複製公式到D2:D13。E15:=SUMPRODUCT(($B$2:$B$13=D1...
一般版本做法：E2:=SUMPRODUCT((LEFT(A2:A100)="A") * (C2:C100))E5:=SUMPRODUCT((...
a2:=INDEX($A$1:$A$12,SUMPRODUCT(($B$5:$G$12=LARGE($B$5:$G$12,ROW(A1)))*ROW($B$5:...
200最大值A1:=INDEX(A1:A22,MAX((A3:A22>200)*ROW(A3:A22)))將公式向右複製到B1:C1。<-200最...
ccenjor的鐵人檔案