一、前言：

• 這個(gè)博客的實(shí)戰(zhàn)場(chǎng)景：給了一組學(xué)生數(shù)據(jù)，這些數(shù)據(jù)中，有全市20個(gè)社區(qū)，1-9年級(jí)的學(xué)生各科成績(jī)。要求按照各社區(qū)統(tǒng)計(jì)1-9年級(jí)的所有學(xué)生各科平均值。下面首先介紹會(huì)用到的一些函數(shù)，然后再簡(jiǎn)單說明實(shí)戰(zhàn)過程。

---

二、項(xiàng)目實(shí)戰(zhàn)過程中學(xué)到的一些函數(shù)和功能：有些學(xué)習(xí)了沒有用到，也記錄了下來。

1、sumif（區(qū)域1，條件，區(qū)域2）函數(shù)：

• sumif（區(qū)域1，條件，區(qū)域2）函數(shù)中，區(qū)域1是條件對(duì)比時(shí)篩選的區(qū)域，條件是區(qū)域1中某個(gè)篩選項(xiàng)，區(qū)域2是求和區(qū)域，使用這個(gè)公式，會(huì)把區(qū)域1中與條件相同的行對(duì)應(yīng)在區(qū)域2中的項(xiàng)累加起來。應(yīng)用范例：a列是金額，b列是與a列對(duì)應(yīng)的金額類型，就可以通過該函數(shù)，算出不同金額類型的金額之和。

---

2、SUBTOTAL(function_num, ref1, [ref2], …)：

• 可以通過篩選生成匯總結(jié)果。其中第一個(gè)參數(shù)是功能參數(shù)，SUBTOTAL函數(shù)支持11種函數(shù)，分別為Average（平均值）、Count（非空值單元格計(jì)數(shù)）、CountA（非空值單元格計(jì)數(shù)，包括字母）、Max（最大值）、Min（最小值）、Product（乘積）、Stdev（標(biāo)準(zhǔn)偏差值，忽略邏輯值和文本）、Stdevp（標(biāo)準(zhǔn)偏差值，包括邏輯值和文本作為數(shù)字參與計(jì)算）、Sum（求和）、Var（給定樣本的方差，忽略邏輯值和文本）、Varp（給定樣本總體的方差，包括邏輯值和文本作為數(shù)字參與計(jì)算）。SUBTOTAL函數(shù)是Excel中一個(gè)非常實(shí)用且強(qiáng)大的函數(shù)，它可以幫助用戶輕松處理篩選和手動(dòng)隱藏后的數(shù)據(jù)，提高工作效率和數(shù)據(jù)處理能力。但是要注意功能代碼要給對(duì)，例如在一個(gè)包含10個(gè)數(shù)據(jù)的單元格區(qū)域中，假設(shè)初始總和為50。當(dāng)隱藏其中3個(gè)數(shù)據(jù)后，使用代碼“9”進(jìn)行求和，結(jié)果仍為50；而使用代碼“109”進(jìn)行求和，結(jié)果則變?yōu)榭梢姷?個(gè)數(shù)據(jù)的總和。

---

3、切片器：超級(jí)表的一種功能

• 切片器的使用前提（結(jié)構(gòu)化表格也叫做超級(jí)表）：在插入選項(xiàng)中有一個(gè)叫“表格”的功能，他的作用是將數(shù)據(jù)區(qū)域轉(zhuǎn)換為結(jié)構(gòu)化表格，結(jié)構(gòu)化表格的好處是
  1.向表格添加新行或列時(shí)，數(shù)據(jù)會(huì)自動(dòng)納入表格范圍，相關(guān)公式、格式和篩選會(huì)自動(dòng)應(yīng)用到新數(shù)據(jù)。
  2.統(tǒng)一表格格式，便于整體修改格式。
  3.表格標(biāo)題會(huì)自動(dòng)出現(xiàn)篩選剪頭，還能多列排序。
  4.通過“表格設(shè)計(jì)”添加匯總行，可以選擇匯總方式（求和，求平均等）快速匯總數(shù)據(jù)，注意要修改匯總方式，要在創(chuàng)建了最后一行匯總行之后，點(diǎn)到最后一行對(duì)應(yīng)表格上，然后出現(xiàn)下拉箭頭，自己選擇匯總方式。
• 只有創(chuàng)建好超級(jí)表的前提下，選中超級(jí)表區(qū)域，才能在插入中使用切片器。

---

4、數(shù)據(jù)清洗問題：

• 1.錯(cuò)誤數(shù)據(jù)篩選清除：在這個(gè)過程中我發(fā)現(xiàn)錯(cuò)誤項(xiàng)通過篩選工具顯示不出來，出現(xiàn)公式報(bào)錯(cuò)篩選的化可以選擇公式當(dāng)中的公式審核去定位錯(cuò)誤發(fā)生的位置。
  
• 2.文字?jǐn)?shù)據(jù)篩選常用find定位，mid提取。FIND(find_text, within_text, [start_num])：返回的是要查找目標(biāo)的位置，MID(text, start_num, num_chars)，獲取目標(biāo)中一部分內(nèi)容。

---

5、filter（區(qū)域，條件，否則）：

• 可以篩選生成數(shù)組，其中第一個(gè)參數(shù)作用是告訴函數(shù)你要從哪個(gè)區(qū)域篩選，第二個(gè)參數(shù)是說明你的篩選條件，多個(gè)條件的情況下and用*代替，or用+代替。最后一個(gè)參數(shù)是說如果篩選區(qū)域中沒有滿足篩選條件的時(shí)候就返回的結(jié)果。

---

二、實(shí)戰(zhàn)過程：

• 0、給了一組學(xué)生數(shù)據(jù)，這些數(shù)據(jù)中，有全市20個(gè)社區(qū)，1-9年級(jí)的學(xué)生各科成績(jī)。要求按照各社區(qū)統(tǒng)計(jì)1-9年級(jí)的所有學(xué)生各科平均值，如下所示是原始數(shù)據(jù)表：
  
• 2、制作目標(biāo)表格（確定要獲取的數(shù)據(jù)格式和規(guī)范），如下所示，將所有年級(jí)按照9門課程設(shè)計(jì)表格，至于有的年級(jí)沒有對(duì)應(yīng)課程，可以不用管，目的是為了公式能夠適用所有年級(jí)。
  
• 3、獲取“數(shù)據(jù)清洗表”（數(shù)據(jù)規(guī)則化），通過FIND，定位文字位置，通過MID，獲取對(duì)應(yīng)內(nèi)容，這樣子就能夠先把某些規(guī)則數(shù)據(jù)提取出來，首先讓篩選項(xiàng)變成可以統(tǒng)一篩選的規(guī)則數(shù)據(jù)，如下：
  
• 4、思路一：結(jié)合“目標(biāo)表格”，通過篩選函數(shù)快速篩選出需要的數(shù)組，然后對(duì)篩選出來的數(shù)組使用求平均值函數(shù)得到結(jié)果，如下，在水果社區(qū)對(duì)應(yīng)的語(yǔ)文數(shù)據(jù)中可以使用函數(shù)：=AVERAGE(FILTER(數(shù)據(jù)清洗表!E$4:E$7042,(數(shù)據(jù)清洗表!$B$4:$B$7042=目標(biāo)表格!$B$4)?(數(shù)據(jù)清洗表!$C$4:$C$7042=1),0))，其中FILTER(數(shù)據(jù)清洗表!E$4:E$7042,(數(shù)據(jù)清洗表!$B$4:$B$7042=目標(biāo)表格!$B$4)?(數(shù)據(jù)清洗表!$C$4:$C$7042=1),0)函數(shù)的意思是在數(shù)據(jù)清洗表中獲取語(yǔ)文成績(jī)所在的E列數(shù)據(jù)，然后第一個(gè)篩選條件是數(shù)據(jù)清晰表中社區(qū)和目標(biāo)表社區(qū)一致都是水果社區(qū)，并且是1年級(jí)的成績(jī)，篩選出來，不滿足刪選條件的用0代替，這樣就得到了一列滿足以上兩個(gè)條件的數(shù)據(jù)，然后用average函數(shù)求平均就好了。
  
• 5、思路二：通過subtotal動(dòng)態(tài)求平均，然后手動(dòng)篩選，一組一組的獲取，但效率太慢了。