E戰(zhàn)到底特訓(xùn)營
函數(shù)
統(tǒng)計(jì)函數(shù) subtotal不是“一個(gè)函數(shù)”敏释,而是“一群函數(shù)”;是Excel中唯一一個(gè)能統(tǒng)計(jì)用戶可見單元格的函數(shù);
Subtotal(1,ref1,…) 平均值 1-average
Subtotal(2,ref1,…)統(tǒng)計(jì)個(gè)數(shù)2-count
Subtotal(3,ref1,…)非空單元格數(shù)量3-counta
Subtotal(4,ref1,…)最大值4-max
Subtotal(5,ref1,…)最小值5-min
Subtotal(6,ref1,…)括號(hào)內(nèi)數(shù)據(jù)的乘積6-product
Subtotal(7,ref1,…)標(biāo)準(zhǔn)偏差7-stdev.s
Subtotal(8,ref1,…)標(biāo)準(zhǔn)偏差8-stdevp.p
Subtotal(9,ref1,…)求和9-sum……….
SUBTOTAL求和列:=SUBTOTAL(9,B2:B8) =SUM(B2:B8)
Subtotal與sum的區(qū)別袭艺,前者只對篩選數(shù)據(jù)結(jié)果數(shù)據(jù)進(jìn)行求和使鹅,后者不受篩選結(jié)果影響挫鸽,會(huì)對所有數(shù)據(jù)進(jìn)行求和昧碉;
SUBTOTAL計(jì)數(shù):=SUBTOTAL(2,B2:B8) =SUBTOTAL(3,B2:B8)
2-count 返回包含數(shù)字以及包含參數(shù)列表中的數(shù)字的單元格的個(gè)數(shù);
3-counta返回參數(shù)列表中非空值的單元格個(gè)數(shù)诗赌;
利用=SUBTOTAL(3,$B$2:B2)汗茄,可以進(jìn)行篩選后填充序列,原理與counta類似铭若;
小計(jì)合計(jì)用=SUBTOTAL(9,B2:B9)洪碳,可以避開小計(jì)行計(jì)算合計(jì);SUBTOTAL函數(shù)遇到同類就避開了叼屠,就是不會(huì)統(tǒng)計(jì)由SUBTOTAL計(jì)算出來的數(shù)值瞳腌。
統(tǒng)計(jì)函數(shù)(Countif、Countifs)
基本用法:count: 計(jì)算參數(shù)列表中的數(shù)字項(xiàng)的個(gè)數(shù);
CountA:計(jì)算參數(shù)列表中非空的項(xiàng)個(gè)數(shù);
Countblank:計(jì)算參數(shù)列表中空單元格的個(gè)數(shù)
Countif的基本用法:=COUNTIF(判斷區(qū)域,計(jì)數(shù)條件)
countifs的基本用法:COUNTIFS函數(shù)將條件應(yīng)用于跨多個(gè)區(qū)域的單元格镜雨,并計(jì)算符合所有條件的次數(shù)嫂侍。即多條件計(jì)數(shù)。
=COUNTIFS(判斷區(qū)域1,計(jì)數(shù)條件1,判斷區(qū)域2,計(jì)數(shù)條件2)
1荚坞、 如果條件為文本挑宠,需要使用雙引號(hào)引起來;
2颓影、 2各淀、如果條件為數(shù)字,則無需使用雙引號(hào)诡挂。
實(shí)例:
統(tǒng)計(jì)文本單元格個(gè)數(shù):=COUNTIF($A$2:$A$10,"*")碎浇,* 代替任意多個(gè)字符临谱;
統(tǒng)計(jì)非空數(shù)據(jù)個(gè)數(shù):=COUNTIF($A$2:$A$10,"<>"),<> 代表非空數(shù)據(jù)奴璃;
統(tǒng)計(jì)真空數(shù)據(jù)個(gè)數(shù):=COUNTIF($A$2:$A$10,"=")悉默,= 代表真空數(shù)據(jù);
按產(chǎn)品統(tǒng)計(jì)序號(hào):=C2&COUNTIF($C$2:C2,C2)
COUNTIF函數(shù)的統(tǒng)計(jì)區(qū)域是$C$2:C2溺健,第一個(gè)C2是行絕對引用麦牺,第二個(gè)C2钮蛛,是相對引用鞭缭。當(dāng)公式向下復(fù)制時(shí),就會(huì)變成$C$2:C3魏颓、$C$2:C4……一個(gè)不斷擴(kuò)展的區(qū)域岭辣,從這個(gè)動(dòng)態(tài)區(qū)域中統(tǒng)計(jì)C列產(chǎn)品的個(gè)數(shù)。
Countif函數(shù)+Vlookup函數(shù)一對多查詢甸饱;=VLOOKUP($F$2&ROW(A1),$A$1:$D$15,COLUMN(B1),0)
利用輔助列=C2&COUNTIF($C$2:C2,C2)
數(shù)據(jù)有效性:利用=COUNTIF($B$2:$B$21,B2)=1在數(shù)據(jù)有效性設(shè)置公式沦童,實(shí)現(xiàn)不能重復(fù)錄入內(nèi)容;=COUNTIF(F2,"A*")=1在數(shù)據(jù)有效性設(shè)置公式叹话,實(shí)現(xiàn)只能輸入以A開頭的文本偷遗;