numbers表格怎麼自動填充整列函數(EXCELNumbers常用公式-)
2023-04-24 08:22:46
EXCEL/Numbers常用公式 - 經典案例
EXCEL/Numbers公式求和有多種「花式求和」,大家可以根據自己的需要選取。
1.隔列求和
Sumproduct公式實際=SUMPRODUCT((MOD(COLUMN($F54:$K54),2)=0)*($F54:$K54)) |
計劃=SUMPRODUCT((MOD(COLUMN($F54:$K54),2)0)*($F54:$K54)) |
案例:
sumif公式實際=SUMIF($F$53:$K$53,L$53,$F54:$K54) |
計劃=SUMIF($F$53:$K$53,M$53,$F54:$K54) |
=SUMPRODUCT(ISEVEN(COLUMN($F65:$K65))*($F65:$K65)) |
=SUMPRODUCT(ISODD(COLUMN($F65:$K65))*($F65:$K65)) |
Remark:
eg.
在C28單元格中輸入以下公式:
=SUMPRODUCT(ISEVEN(ROW(2:27))*C2:C27)
公式釋義:
ROW(2:27):提取2到27行的行號,結果為2到27的一組數字(2;3;4:.;27);
ISEVEN(.…):iseven 函數的作用是判斷上述數組中的數字是否為偶數,生成一組結果為true和false 的數組;
SUMPRODUCT(..*C2:C27):對上述數組和C2:C27區域的數值進行乘積求和;只有第一個數組中為true的值與C列數值相乘才保留原結果,false相乘為0:最後對乘積結果求和,也就是將所有偶數列的值相加.
說明:
如果 number 不是數字類型,則ISEVEN返回 #VALUE!錯誤值。
既然有iseven,那麼當然也有對應的 isodd 函數。與iseven相對,isodd的作用是判斷參數是否為奇數,如果是則返回true,不是則返回false.
2.自動挑選符合條件的數據求和
SUMIFS公式=SUMIFS($E$91:$E$99,I91,$G$91:$G$99) |
=SUMPRODUCT(($E$91:$E$99=I97)*($G$91:$G$99)) |
註:不只局限於2個條件的求和,3個4個5個···條件的求和也適用
3.單條件模糊求和
包含A的求和=SUMIF($E$103:$E$108,"*A*",$G$103:$G$108)或=SUMIF($E$103:$E$108,"*"&I102&"*",$G$103:$G$108) |
以A開頭的求和=SUMIF($E$103:$E$108,"A*",$G$103:$G$108)或=SUMIF($E$103:$E$108,I103&"*",$G$103:$G$108) |
以A結束的求和=SUMIF($E$103:$E$108,"*A",$G$103:$G$108))或=SUMIF($E$103:$E$108,"*"&I104,$G$103:$G$108) |
eg.
註:此處不能用sumproduct公式,因為不識別模糊格式「*」
4.多條件模糊求和
包含「電視」且是鄭州地區的求和= SUMIFS($G$113:$G$118,$E$113:$E$118,"*"&I113&"*",$F$113:$F$118,J113) |
註:如果是以「電視」開頭或以「電視」結尾的數量求和,請參照3
5.按日期和地區求和(多條件求和)
『= SUMPRODUCT((MONTH($E$123:$E$131)=J$122)*($F$123:$F$131=$I123)*($G$123:$G$131)) |
註: sumif公式在此處不能用。因為sumif不能嵌套矩陣公式,eg. month(E92:E100),sunif智能識別一個範圍eg.E92:E100。不同的是sumproduct卻可以識別嵌套的矩陣公式
,