Excel資料透視表學習之最實用的4個透視表偏方

函數學得少，所以就把勁往資料透視表上使。數據透視表也沒辜負人，總有一些小東西可以解決統計上的大問題。這裡的4條偏方就是這樣的。

所謂偏方，就是指平常少見，但是對於特定情況有特效的方子。我們今天跟大家分享資料透視表4個「偏方」。

偏方一：空值處理

我們在對一組資料進行資料透視時經常會遇到值區域中某個欄位對應資料為空白的情況。以往很多夥伴都是手動修改，其實可以透過資料透視表自訂空白顯示為0。 （註：只針對值區域中的空白！）

舉例：

第一季中的螢幕300*220項目購買數量為空白，現在需要將資料進行透視匯總處理。

完成資料透視後我們看到C13單元格為空白。

點選資料透視表右鍵點選滑鼠，選擇【資料透視表選項】。

開啟【資料透視表選項】對話框，勾選【佈局和格式】中的【對於空白單元跟，顯示】，同時在右側的編輯欄中輸入“無數據”。

點擊確定後資料透視表中所有的空白將填入「無資料」字元。

注意：這裡我們可以將空白透過定義填入任意文字、數字或符號。

偏方二：排名

日常工作中經常需要將完成資料透視後的資料進行排名，許多夥伴都是透過rank函數進行排名。其實資料透視錶自備排名功能，根本不需要排序、函數。

還是以採購資料為例，現在我們完成了資料透視。

勾選資料透視表右鍵滑鼠，選擇【值顯示方式】，在子選單中選擇【降序排序】。

選擇以項目為基本欄位進行排序，點選【確定】。

最後我們看到原本的購買數據資訊變成了排名資訊。

如果我們需要同時保留購買資料以及排名信息，只需要在值欄位中再次新增購買數量。

偏方三：批次建立工作表

#批次建立是日常經常碰到的工作，例如建立分公司、月份、季度等工作表。如果數量少，我們可以透過手工逐一創建，如果數量很多該怎麼辦？其實可以透過資料透視表批次建立工作表。

範例：現在我們需要建立4個季度的工作表。

首先在表中輸入表頭季度，以及四個季度名稱。

然後選取A列數據，按一下【插入】標籤中的【資料透視表】。

在開啟的【建立資料透視表】對話方塊中，選擇資料透視表的位置為現有工作表。

確定後將【季度】欄位拖曳至篩選框內。

點選資料透視表，然後點選【分析】標籤中【選項】-【顯示報表篩選頁】。

出現【顯示報表篩選頁】對話框，直接按確定，我們就可以看到批次建立的工作表。

選取所建立的所有工作表，然後在任一工作表中選取表格中不需要的數據，選擇「開始」-“清除”-“全部清除”，即可完成工作表的批次建立。

是不是很簡單？

附註：批次建立的工作表是自動依工作表名稱排序的。譬如這裡的第一到第四季度，創建的工作表依序是第二、第三、第四、第一季。如果想按季度順序建立工作表，則輸入時改成阿拉伯數字，如第1、第2、第3、第4等季度。如果想依照自己輸入的名稱順序建立工作表，有一個簡易方法，就是在輸入時每個名稱前依序新增阿拉伯數字1、2、3等，則工作表會依照輸入順序建立。

偏方四：按新增欄位分組統計

將資料依新增欄位分組進行統計，也是常做的一件事。譬如，數據中沒有月份、季度，但領導要求你按月、按季度統計；數據中沒有一等品、二等品、三等品，但領導要求你按一、二、三等品進行統計。對於這類把原始資料依新指定欄位進行統計的，利用透視表可以非常簡單的實作。

例舉兩例。

範例1：依日期分組統計

資料來源是按日登記的銷售量。現在要按月、季分組統計銷售額。

（1）選取所有數據，插入資料透視表。

（2）將「銷售日期」欄位拖曳到行區域中，Excel會自動增加一個「月」欄位（需要是2016版本），右側透視表中行標籤按月顯示。 （註：如果使用的低版本，則需要按下方設置「季度」欄位的方式進行設置，增加「月」欄位後才能按月統計。）然後將「銷售量」拖入值區域中。

（3）下面我們透過分組設置，實現季度統計。在透視表格行標籤下任一資料上按右鍵，選擇「組合」指令（也可以點選【分析】-【分組欄位】或【分組選擇】）開啟【組合】對話方塊。可以看到目前已經選取了兩個步長「日」和「月」。

開始於、終止於資料會自動根據資料來源生成，不用管它。

（4）按一下“季度”，然後確定。

（5）可以看到資料透視表欄位中增加了「季度」欄位。在左側的透視表中，點選符號把資料折疊，就實現了按季度統計。

例2：分數分階段統計

#下表是某班的數學成績，只有姓名和成績兩個字段。現在我們需要統計、60-79、80-100各階段的人數。

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

（2）把「成績」欄位拖曳到行區域中。這時左側透視表的行標籤下方出現一列分數值。

（3）在透視表行標籤下任一個分數上右鍵，選擇「組合」指令，開啟組合對話方塊。

（4）現在就依需求修改起始值和終止值、步長。設定起始於60，終止於100，步長20，如下。

（5）按一下「確定」後，行標籤變成了我們需要的三個分數段。

（6）將「成績」欄位拖曳到值區域中，實現了人數統計，如不及格的有11人。

（7）如果想進一步看到各階段的姓名，則可以把「姓名」欄位拖曳到行區域中。

如果想更自由分段，不受步長限制，那可以在第（3）步的時候改變做法。譬如選取0-59，右鍵，選擇“組合”，產生“資料組1”，選取“資料組1”，在編輯欄中輸入“D”，把“資料組1”改成“D”，這就是成績D階段；選取60-79，右鍵組合後改成「C」；選取80-90，右鍵組合後改成「B」；選取90以上的，右鍵組合後改成「A」。如此就把成績分成了ABCD四個階段進行統計。

總結：

今天跟大家分享了4個資料透視表功能實用「偏方」。這些偏方都很有效率，可以取代複雜的函數工作，提高效率。大家在平常工作中多留意一些功能和選項，多一些思考，就會多挖掘一個技巧，讓Excel運作更由心。

相關學習推薦：excel教學

#

以上是Excel資料透視表學習之最實用的4個透視表偏方的詳細內容。更多資訊請關注PHP中文網其他相關文章！