編按：函數(shù)學(xué)得少，所以就把勁往數(shù)據(jù)透視表上使。數(shù)據(jù)透視表也沒(méi)辜負(fù)人，總有一些小東西可以解決統(tǒng)計(jì)上的大問(wèn)題。這里的6條偏方就是這樣的。

所謂偏方，就是指平時(shí)少見(jiàn)，但是對(duì)于特定情況有特效的方子。我們今天跟大家分享數(shù)據(jù)透視表六個(gè)“偏方”。

偏方一：空值處理

我們?cè)趯?duì)一組數(shù)據(jù)進(jìn)行數(shù)據(jù)透視時(shí)經(jīng)常會(huì)遇到值區(qū)域中某個(gè)字段對(duì)應(yīng)數(shù)據(jù)為空白的情況。以往很多伙伴都是手動(dòng)修改，其實(shí)可以通過(guò)數(shù)據(jù)透視表自定義空白顯示為0。（注：只針對(duì)值區(qū)域中的空白?。?/p>

舉例：

第一季度中的屏幕300*220項(xiàng)目購(gòu)買(mǎi)數(shù)量為空白，現(xiàn)在需要將數(shù)據(jù)進(jìn)行透視匯總處理。

完成數(shù)據(jù)透視后我們看到C13單元格為空白。

單擊數(shù)據(jù)透視表右擊鼠標(biāo)，選擇【數(shù)據(jù)透視表選項(xiàng)】。

打開(kāi)【數(shù)據(jù)透視表選項(xiàng)】對(duì)話(huà)框，勾選【布局和格式】中的【對(duì)于空白單元跟，顯示】，同時(shí)在右側(cè)的編輯欄中輸入“無(wú)數(shù)據(jù)”。

單擊確定后數(shù)據(jù)透視表中所有的空白將填充“無(wú)數(shù)據(jù)”字符。

注意：這里我們可以將空白通過(guò)定義填充為任意文本、數(shù)字或者符號(hào)。

偏方二：排名

日常工作中經(jīng)常需要將完成數(shù)據(jù)透視后的數(shù)據(jù)進(jìn)行排名，很多伙伴都是通過(guò)rank函數(shù)進(jìn)行排名。其實(shí)數(shù)據(jù)透視表自帶排名功能，根本無(wú)需排序、函數(shù)。

還是以采購(gòu)數(shù)據(jù)為例，現(xiàn)在我們完成了數(shù)據(jù)透視。

選中數(shù)據(jù)透視表右擊鼠標(biāo)，選擇【值顯示方式】，在子菜單中選擇【降序排序】。

選擇以項(xiàng)目為基本字段進(jìn)行排序，單擊【確定】。

最后我們看到原本的購(gòu)買(mǎi)數(shù)據(jù)信息變成了排名信息。

如果我們需要同時(shí)保留購(gòu)買(mǎi)數(shù)據(jù)以及排名信息，只需要在值字段中再次添加購(gòu)買(mǎi)數(shù)量即可。

偏方三：批量創(chuàng)建工作表

批量創(chuàng)建是日常經(jīng)常碰到的工作，比如創(chuàng)建分公司、月份、季度等工作表。如果數(shù)量少，我們可以通過(guò)手工逐一創(chuàng)建，如果數(shù)量很多該怎么辦呢？其實(shí)可以通過(guò)數(shù)據(jù)透視表批量創(chuàng)建工作表。

舉例：現(xiàn)在我們需要?jiǎng)?chuàng)建4個(gè)季度的工作表。

首先在表中輸入表頭季度，以及四個(gè)季度名稱(chēng)。

然后選中A列數(shù)據(jù)，單擊【插入】選項(xiàng)卡中的【數(shù)據(jù)透視表】。

在打開(kāi)的【創(chuàng)建數(shù)據(jù)透視表】對(duì)話(huà)框中，選擇數(shù)據(jù)透視表的位置為現(xiàn)有工作表。

確定后將【季度】字段拖至篩選框內(nèi)。

單擊數(shù)據(jù)透視表，然后單擊【分析】選項(xiàng)卡中【選項(xiàng)】-【顯示報(bào)表篩選頁(yè)】。

出現(xiàn)【顯示報(bào)表篩選頁(yè)】對(duì)話(huà)框，直接單擊確定，我們就可以看到批量創(chuàng)建的工作表。

選中所創(chuàng)建的所有工作表，然后在任意一個(gè)工作表中選中表格中不需要的數(shù)據(jù)，選擇“開(kāi)始”-“清除”-“全部清除”，即可完成工作表的批量創(chuàng)建。

是不是很簡(jiǎn)單？

注：批量創(chuàng)建的工作表是自動(dòng)按工作表名稱(chēng)排序的。譬如這里的第一到第四季度，創(chuàng)建出來(lái)的工作表依次是第二、第三、第四、第一季度。如果想按季度順序創(chuàng)建工作表，則輸入時(shí)改成阿拉伯?dāng)?shù)字，如第1、第2、第3、第4等季度。如果想按自己輸入的名稱(chēng)順序創(chuàng)建工作表，有一個(gè)簡(jiǎn)易方法，就是在輸入時(shí)每個(gè)名稱(chēng)前依次添加阿拉伯?dāng)?shù)字1、2、3等，則工作表按輸入順序創(chuàng)建。

偏方四：多表求和

將同一工作簿中的多個(gè)同格式工作表匯總求和，也可以用數(shù)據(jù)透視表完成。具體請(qǐng)看教程《別瞎忙乎了，多表求和用這個(gè)方法就是分分鐘的事……》。

偏方五：按新增字段分組統(tǒng)計(jì)

將數(shù)據(jù)按新增字段分組進(jìn)行統(tǒng)計(jì)，也是經(jīng)常做的一件事。譬如，數(shù)據(jù)中沒(méi)有月份、季度，但領(lǐng)導(dǎo)要求你按月、按季度統(tǒng)計(jì)；數(shù)據(jù)中沒(méi)有一等品、二等品、三等品，但領(lǐng)導(dǎo)要求你按一、二、三等品進(jìn)行統(tǒng)計(jì)。對(duì)于這類(lèi)把原始數(shù)據(jù)按新指定字段進(jìn)行統(tǒng)計(jì)的，利用透視表可以非常簡(jiǎn)便的實(shí)現(xiàn)。

例舉兩例。

例1：按日期分組統(tǒng)計(jì)

數(shù)據(jù)源是按日登記的銷(xiāo)售額?，F(xiàn)在要按月、季度分組統(tǒng)計(jì)銷(xiāo)售額。

（1）選中所有數(shù)據(jù)，插入數(shù)據(jù)透視表。

（2）將“銷(xiāo)售日期”字段拖入行區(qū)域中，Excel會(huì)自動(dòng)增加一個(gè)“月”字段（需要是2016版本），右側(cè)透視表中行標(biāo)簽按月顯示。（注：如果用的低版本，則需要按下方設(shè)置“季度”字段的方式進(jìn)行設(shè)置，增加“月”字段后才能按月統(tǒng)計(jì)。）然后將“銷(xiāo)量”拖入值區(qū)域中。

（3）下面我們通過(guò)分組設(shè)置，實(shí)現(xiàn)季度統(tǒng)計(jì)。在透視表行標(biāo)簽下任意一個(gè)數(shù)據(jù)上右擊，選擇“組合”命令（也可以單擊【分析】-【分組字段】或【分組選擇】）打開(kāi)【組合】對(duì)話(huà)框?？梢钥吹疆?dāng)前已經(jīng)選中了兩個(gè)步長(zhǎng)“日”和“月”。

起始于、終止于數(shù)據(jù)會(huì)自動(dòng)根據(jù)數(shù)據(jù)源生成，不用管它。

（4）單擊“季度”，然后確定。

（5）可以看到數(shù)據(jù)透視表字段中增加了“季度”字段。在左側(cè)的透視表中，單擊

符號(hào)把數(shù)據(jù)折疊，就實(shí)現(xiàn)了按季度統(tǒng)計(jì)。

例2：分?jǐn)?shù)分階段統(tǒng)計(jì)

下表是某班的數(shù)學(xué)成績(jī)，只有姓名和成績(jī)兩個(gè)字段。現(xiàn)在我們需要統(tǒng)計(jì)

（1）一樣的，首先建立透視表。

（2）把“成績(jī)”字段拖入行區(qū)域中。這時(shí)左側(cè)透視表的行標(biāo)簽下方出現(xiàn)一列分?jǐn)?shù)值。

（3）在透視表行標(biāo)簽下任意一個(gè)分?jǐn)?shù)上右擊，選擇“組合”命令，打開(kāi)組合對(duì)話(huà)框。

（4）現(xiàn)在按需要修改起始值和終止值、步長(zhǎng)。設(shè)置起始于60，終止于100，步長(zhǎng)20，如下。

（5）單擊“確定”后，行標(biāo)簽變成了我們需要的三個(gè)分?jǐn)?shù)段。

（6）將“成績(jī)”字段拖到值區(qū)域中，實(shí)現(xiàn)了人數(shù)統(tǒng)計(jì)，如不及格的有11人。

（7）如果想進(jìn)一步看到各階段的姓名，則可以把“姓名”字段拖入行區(qū)域中。

如果想更自由分段，不受步長(zhǎng)限制，那可以在第（3）步的時(shí)候改變做法。譬如選中0-59，右擊，選擇“組合”，生成“數(shù)據(jù)組1”，選中“數(shù)據(jù)組1”，在編輯欄中輸入“D”，把“數(shù)據(jù)組1”改成“D”，這就是成績(jī)D階段；選中60-79，右擊組合后改成“C”；選中80-90，右擊組合后改成“B”；選中90以上的，右擊組合后改成“A”。如此就把成績(jī)分成了ABCD四個(gè)階段進(jìn)行統(tǒng)計(jì)。

偏方六：非重復(fù)計(jì)數(shù)

從原始數(shù)據(jù)中統(tǒng)計(jì)機(jī)構(gòu)數(shù)量、產(chǎn)品種數(shù)、經(jīng)銷(xiāo)商數(shù)量、供貨商數(shù)量等等，屬于典型的非重復(fù)計(jì)數(shù)。同樣可以利用透視表完成。具體的完成方式見(jiàn)《圍觀數(shù)據(jù)透視表新功能：小東西，大作用》

總結(jié)：今天跟大家分享了6個(gè)數(shù)據(jù)透視表功能實(shí)用“偏方”。這些偏方都很高效，可以取代復(fù)雜的函數(shù)工作，提高效率。大家在平時(shí)工作中多留意一些功能和選項(xiàng)，多一些思考，就會(huì)多挖掘一個(gè)技巧，讓Excel運(yùn)行更由心。

＊＊＊＊部落窩教育－excel透視表技巧＊＊＊＊

原創(chuàng)：龔春光、小雅/部落窩教育（未經(jīng)同意，請(qǐng)勿轉(zhuǎn)載）