Day17內(nèi)容：統(tǒng)計(jì)函數(shù)Subtotal煞肾、Countif和Countifs

一、回顧目標(biāo)：

1.早上聽(tīng)課并完成操作

2.上午完成思維導(dǎo)圖制作

3.下午簡(jiǎn)書(shū)輸出并打卡

4.晚上錄制翻轉(zhuǎn)課程

5.做到晚9點(diǎn)之前打卡啥辨。遲一次第二天加一次翻轉(zhuǎn)輸出监署。

6.提醒同桌打卡后分享、點(diǎn)贊敬矩、評(píng)論咐汞，拿到基礎(chǔ)50分盖呼。

二、評(píng)估結(jié)果：

1.12356完成

2.4暫未完成化撕，晚上根據(jù)到家翻轉(zhuǎn)几晤，先完成打卡為上。

三植阴、分析過(guò)程：

1.早晨起床聽(tīng)課蟹瘾，但未操作課件，上午利用空閑時(shí)間才操作課件掠手，今天內(nèi)容比較多憾朴，說(shuō)是三個(gè)統(tǒng)計(jì)函數(shù)，實(shí)際是N個(gè)函數(shù)喷鸽，一個(gè)Subtotal函數(shù)就有11種用法众雷，頂11個(gè)函數(shù)，還好常用的計(jì)數(shù)和求和比較簡(jiǎn)單魁衙，靈活運(yùn)用即可报腔，Countif和Countifs常規(guī)用法相對(duì)簡(jiǎn)單株搔，難點(diǎn)在于一對(duì)多查詢剖淀，要理解并運(yùn)用函數(shù)套用，需要多多操作纤房。

2.下午看到群里說(shuō)有作業(yè)纵隔，抽空完成上傳QQ，看到同桌還沒(méi)有做作業(yè)，發(fā)給同桌一份捌刮，提醒她今天需要完成作業(yè)碰煌。

3.下午抽空制作思維導(dǎo)圖，并完成簡(jiǎn)書(shū)的輸出绅作。

4.做到了9點(diǎn)之前打卡芦圾，并發(fā)給同桌提醒她及時(shí)打卡，昨天小打卡出問(wèn)題俄认，同桌打卡時(shí)顯示空白个少，還好打卡成功了，所以要提醒同桌早點(diǎn)打卡眯杏，以免晚上小打卡卡頓夜焦，但同桌說(shuō)在出差，只能晚上有時(shí)間岂贩。

5.晚上和朋友外出茫经，約了我好幾次了，都沒(méi)去成萎津，實(shí)在不好意思卸伞，今天陪她們逛逛。

四姜性、總結(jié)規(guī)律

1.先完成再完美瞪慧，團(tuán)隊(duì)第一。

2.利用好碎片化時(shí)間部念，時(shí)間會(huì)自然多出來(lái)藕各。

3.提前思維很重要，能為臨時(shí)任務(wù)騰出時(shí)間假哎。

五玷禽、明日目標(biāo)

1.早上聽(tīng)課并完成操作

2.上午完成思維導(dǎo)圖制作

3.下午簡(jiǎn)書(shū)輸出并打卡

4.晚上錄制翻轉(zhuǎn)課程

5.做到晚9點(diǎn)之前打卡。遲一次第二天加一次翻轉(zhuǎn)輸出乌询。

6.提醒同桌打卡后分享榜贴、點(diǎn)贊、評(píng)論妹田，拿到基礎(chǔ)50分唬党。

7.準(zhǔn)備分享稿

引言：越是碎片化時(shí)代，越需要系統(tǒng)性學(xué)習(xí)鬼佣，附上今天學(xué)習(xí)內(nèi)容：

今天我們來(lái)學(xué)習(xí)統(tǒng)計(jì)函數(shù)Subtotal驶拱，Countif和Countifs。

首先學(xué)習(xí)萬(wàn)能統(tǒng)計(jì)函數(shù)Subtotal

在Excel里是這樣說(shuō)明Subtotal的：返回列表或數(shù)據(jù)庫(kù)中的分類匯總晶衷。通常蓝纲，使用“數(shù)據(jù)”菜單中的“分類匯總”命令可以容易地創(chuàng)建帶有分類匯總的列表阴孟。一旦創(chuàng)建了分類匯總，就可以通過(guò)編輯 Subtotal 函數(shù)對(duì)該列表進(jìn)行修改税迷。

Subtotal有三個(gè)不同于其他函數(shù)的特點(diǎn)永丝，注定在Excel函數(shù)里，起著無(wú)法代替的作用箭养。

下面我們從6個(gè)方面來(lái)學(xué)習(xí)：

一慕嚷、Subtotal基本用法

Subtotal是Excel中唯一一個(gè)能統(tǒng)計(jì)用戶可見(jiàn)單元格的函數(shù)。和其他函數(shù)不同的是他不是“一個(gè)函數(shù)”毕泌，而是“一群函數(shù)”闯冷。

一個(gè)函數(shù)相當(dāng)于11個(gè)函數(shù)⌒复剩可用于統(tǒng)計(jì)平均值蛇耀、計(jì)數(shù)、最大值坎弯、最小值纺涤、乘積、標(biāo)準(zhǔn)偏差抠忘、求和撩炊、方差等，詳見(jiàn)下圖崎脉。

其基本語(yǔ)法為：Subtotal(Function_Num,Ref1,Ref2, ...)

1.Function_Num： 為 1~11（包含隱藏值）或 101~111（忽略隱藏值）之間的數(shù)字拧咳，指定使用何種函數(shù)在列表中進(jìn)行分類匯總計(jì)算。

2.Ref1……Refn：為要對(duì)其進(jìn)行分類匯總計(jì)算的第1至29個(gè)命名區(qū)域或引用囚灼。必須是對(duì)單元格區(qū)域的引用骆膝。

二、Subtotal隱藏值

注意：第一個(gè)參數(shù)使用111和使用101111的區(qū)別

使用1~11灶体，將包括手動(dòng)隱藏值阅签，如有隱藏行，結(jié)果包含隱藏行的值蝎抽；

使用101~111政钟，忽略手動(dòng)隱藏值，如有隱藏行樟结，始終排除隱藏行的值养交。

三、Subtotal與Sum的區(qū)別

Subtotal：只對(duì)篩選后的數(shù)據(jù)結(jié)果數(shù)據(jù)進(jìn)行求和

Sum：不受篩選結(jié)果影響瓢宦，會(huì)對(duì)所有數(shù)據(jù)進(jìn)行求和

四碎连、Subtotal計(jì)數(shù)：Count與Counta

Count：返回包含數(shù)字以及包含參數(shù)列表中的數(shù)字的單元格的個(gè)數(shù)

Counta：返回參數(shù)列表中非空值的單元格個(gè)數(shù)

=Subtotal(2,B2:B8) 統(tǒng)計(jì)包含數(shù)字單元格的個(gè)數(shù)

=Subtotal(3,B2:B8) 非空單元格數(shù)量

五、篩選后填充

Excel中經(jīng)常會(huì)用到篩選刁笙，篩選后的序號(hào)往往是不連續(xù)的破花，如果想篩選后生成連續(xù)序號(hào)，可以通過(guò)Subtotal實(shí)現(xiàn)疲吸。

=Subtotal(3,$B$2:B2)*1

第一參數(shù)使用3座每，表示使用Count函數(shù)的計(jì)算規(guī)則，統(tǒng)計(jì)B列單元格數(shù)量摘悴。

注意：直接使用Subtotal函數(shù)時(shí)峭梳，篩選狀態(tài)下Excel會(huì)默認(rèn)將末行當(dāng)做匯總行，公式中 *1蹂喻，就是為了避免篩選時(shí)出現(xiàn)末行葱椭。

六、避開(kāi)同類計(jì)算

Subtotal函數(shù)不會(huì)統(tǒng)計(jì)由Subtotal計(jì)算出來(lái)的數(shù)值口四，即遇到同類就避開(kāi)了孵运。

總結(jié)

Subtotal有三個(gè)特點(diǎn)：

1.如果在 Ref1, Ref2,… 中有其他的分類匯總（嵌套分類匯總），將忽略這些嵌套分類匯總蔓彩，以避免重復(fù)計(jì)算治笨。也就是在數(shù)據(jù)區(qū)域中有Subtotal獲得的結(jié)果將被忽略！

2.當(dāng) Function_Num 為從 1 到 11 的常數(shù)時(shí)赤嚼，Subtotal 函數(shù)將包括通過(guò)“格式”菜單的“行”子菜單下面的“隱藏”命令所隱藏的行中的值旷赖。當(dāng)您要分類匯總列表中的隱藏和非隱藏值時(shí)，請(qǐng)使用這些常數(shù)更卒。當(dāng) Function_Num 為從 101 到 111 的常數(shù)時(shí)等孵，Subtotal 函數(shù)將忽略通過(guò)“格式”菜單的“行”子菜單下面的“隱藏”命令所隱藏的行中的值。當(dāng)您只分類匯總列表中的非隱藏?cái)?shù)字時(shí)蹂空，使用這些常數(shù)俯萌。 但不論使用什么 Function_Num 值，Subtotal 函數(shù)都會(huì)忽略任何不包括在篩選結(jié)果中的行上枕。 而Subtotal 函數(shù)不適用于數(shù)據(jù)行或水平區(qū)域绳瘟。隱藏某一列不影響分類匯總。但是隱藏分類匯總的垂直區(qū)域中的某一行就會(huì)對(duì)其產(chǎn)生影響姿骏。

3.可以代替上面說(shuō)的11種函數(shù)糖声，當(dāng)有上面說(shuō)的兩種特點(diǎn)情況時(shí)，就可以使用Subtotal來(lái)完成分瘦。

所以在需要處理隱藏?cái)?shù)據(jù)相關(guān)的應(yīng)用時(shí)蘸泻，Subtotal是其它函數(shù)無(wú)法代替的，也是Subtotal最大最重要的特點(diǎn)嘲玫。

下面我們來(lái)學(xué)習(xí)統(tǒng)計(jì)函數(shù)Countif和Countifs.

一悦施、認(rèn)識(shí)函數(shù)Count、Counta去团、Countblank

1.Count: 計(jì)算參數(shù)列表中的數(shù)字項(xiàng)的個(gè)數(shù)

不能轉(zhuǎn)換為數(shù)字的文本抡诞、空白單元格穷蛹、邏輯值、錯(cuò)誤值都不計(jì)算在內(nèi)

①如果參數(shù)為數(shù)字昼汗、日期或者代表數(shù)字的文本肴熏，則將被計(jì)算在內(nèi)；

②邏輯值和直接鍵入到參數(shù)列表中代表數(shù)字的文本被計(jì)算在內(nèi)顷窒；

③如果參數(shù)為錯(cuò)誤值或不能轉(zhuǎn)換為數(shù)字的文本蛙吏，則不會(huì)被計(jì)算在內(nèi)；

④如果參數(shù)是一個(gè)數(shù)組或引用鞋吉，則只計(jì)算其中的數(shù)字鸦做。數(shù)組或引用中的空白單元格、邏輯值谓着、文本或錯(cuò)誤值將不計(jì)算在內(nèi)泼诱。

2.Counta:計(jì)算參數(shù)列表中非空的項(xiàng)個(gè)數(shù)

參數(shù)值可以是任何類型,包括空字符(""),但不包括空白單元格。

①參數(shù)值可以是任何類型,可以包括空字符(""),但不包括空白單元格赊锚；

②如果參數(shù)是數(shù)組或單元格引用,則數(shù)組或引用中的空白單元格將被忽略坷檩；

③如果不需要統(tǒng)計(jì)邏輯值、文字或錯(cuò)誤值,請(qǐng)使用函數(shù)COUNT改抡。

3.Countblank: 計(jì)算參數(shù)列表中空單元格的個(gè)數(shù)

空白單元格和空文本（""）會(huì)被計(jì)算在內(nèi)矢炼。

①包含返回 ""（空文本）的公式的單元格會(huì)計(jì)算在內(nèi)；

②包含零值的單元格不計(jì)算在內(nèi)阿纤。

二句灌、Countif基本用法

Countif：對(duì)區(qū)域中滿足單個(gè)指定條件的單元格進(jìn)行計(jì)數(shù)

基本語(yǔ)法為：Countif(Range,Ctrteria)

Range：必需。表示要統(tǒng)計(jì)數(shù)量的單元格欠拾，Range可以包含數(shù)字.數(shù)組.或數(shù)字的引用

Ctrteria：必需胰锌。用于決定要統(tǒng)計(jì)哪些單元格的數(shù)量的數(shù)字.表達(dá)式.單元格引用或文本字串符。

注意：

1.COUNTIF函數(shù)的第一參數(shù)絕對(duì)引用藐窄，是為了公式向下填充時(shí)资昧，保持引用范圍不變；

2.COUNTIF函數(shù)的第二參數(shù)直接使用相對(duì)引用待統(tǒng)計(jì)單元格荆忍，公式向下填充時(shí)格带，D2依次變?yōu)镈3、D4……

三刹枉、Countifs基本用法

Countifs函數(shù)的作用是對(duì)區(qū)域中滿足多個(gè)條件的單元格計(jì)數(shù)叽唱，即多條件計(jì)數(shù)。

其基本語(yǔ)法為：Countifs(Criteria_Range1,Criterial,[Criteria_Range2,Criterial2,]_)

COUNTIFS(條件區(qū)域1微宝，條件1棺亭，條件區(qū)域2，條件2…)

條件區(qū)域1：必需蟋软。在其中計(jì)算關(guān)聯(lián)條件的第一個(gè)區(qū)域镶摘。

條件1：必需嗽桩。要進(jìn)行計(jì)數(shù)的第一個(gè)條件。

條件區(qū)域2：可選凄敢。在其中計(jì)算關(guān)聯(lián)條件的第二個(gè)區(qū)域碌冶。

條件2：可選。要進(jìn)行計(jì)數(shù)的第二個(gè)條件贡未。

四、模糊條件計(jì)數(shù)

結(jié)合通配符“*”和“?”

如：統(tǒng)計(jì)“張”姓次數(shù)：=COUNTIF(明細(xì)表!C:C,"張*")

統(tǒng)計(jì)“張”姓A產(chǎn)品次數(shù)：=COUNTIFS(明細(xì)表!C:C,"張*",明細(xì)表!D:D,"A")

五蒙袍、文本.非空.真空數(shù)據(jù)個(gè)數(shù)

統(tǒng)計(jì)文本單元格個(gè)數(shù)=COUNTIF($A$2:$A$10,"*")

統(tǒng)計(jì)文本單元格個(gè)數(shù)=COUNTIF($A$2:$A$10,"<>")

統(tǒng)計(jì)真空數(shù)據(jù)個(gè)數(shù)=COUNTIF($A$2:$A$10,"=")

注意：

“*”代替任意多個(gè)字符

“<>”代表非空數(shù)據(jù)

“=”代表真空數(shù)據(jù)

六俊卤、按產(chǎn)品統(tǒng)計(jì)序號(hào)

1.使用Countif給產(chǎn)品排序

=COUNTIF($C$2:C2,C2)

注意：COUNTIF函數(shù)的統(tǒng)計(jì)區(qū)域是$C$2:C2，第一個(gè)C2是行絕對(duì)引用害幅，第二個(gè)C2消恍，是相對(duì)引用；當(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ù)

2.再使用名稱&序號(hào)來(lái)進(jìn)行連接，命名產(chǎn)品序號(hào)

=D2&COUNTIF($D$2:D2,D2)

即使產(chǎn)品的順序是打亂排列的邑遏，方法也一樣

七佣赖、Countif函數(shù)+Vlookup函數(shù)一對(duì)多查詢

操作步驟：

1.增加輔助列為產(chǎn)品進(jìn)行命名排序，使產(chǎn)品序號(hào)唯一记盒，方便進(jìn)行查找憎蛤；

C2&COUNTIF($C$2:C2,C2)

2.明確我們的查找值為”含有A*"的產(chǎn)品，需要生成以"A1.A2……"的連續(xù)序號(hào)去查找纪吮，可使用函數(shù)ROW（）來(lái)生成俩檬，表示現(xiàn)在所在的的單元格的所處的行

3.生成目標(biāo)產(chǎn)品連續(xù)序號(hào)后，使用Vlookup來(lái)進(jìn)行精確查找碾盟，此時(shí)向右拖動(dòng)數(shù)據(jù)時(shí)會(huì)出現(xiàn)”#N/A“棚辽，可嵌套使用函數(shù)Column（）列來(lái)調(diào)整

4.為了將錯(cuò)誤值屏蔽，最后我們使用IFERROR函數(shù)來(lái)進(jìn)行隱藏

=IFERROR(VLOOKUP($F$2&ROW(A1),$A$1:$D$15,COLUMN(B1),0),"")

解決一對(duì)多查找的關(guān)鍵點(diǎn)是把重復(fù)的值用Countif變成不同的冰肴，然后用Vlookup函數(shù)再進(jìn)行查找屈藐。

掌握這個(gè)思路很重要

七、數(shù)據(jù)驗(yàn)證

1.不能錄入重復(fù)

數(shù)據(jù)-數(shù)據(jù)驗(yàn)證-自定義- =COUNTIF($B$2:$B$21,B2)=1

2.只能輸入以某字段開(kāi)頭/結(jié)尾的文本

數(shù)據(jù)-數(shù)據(jù)驗(yàn)證-自定義=COUNTIF(F2,"A*")=1

以上就是今天學(xué)習(xí)的統(tǒng)計(jì)函數(shù)熙尉，比較多估盘，先慢慢消化，再學(xué)以致用骡尽，逐步走向高效辦公遣妥。