分合相宜 Excel透視報表生成分頁和匯總報表

我們經常要利用Excel生成指定類型的報表，但是很多報表原始數據是混雜在一起，或者是分布在各個子表中。現在利用Excel透視報表的功能，我們可以快速將混雜的數據分離為分頁報表，或者將獨立子表整合為匯總報表。

簡單分頁，混雜報表變身分頁報表

為了方便統計，工作中很多報表數據都是歸集在一起。比如公司HR整理的員工考勤報表，所有員工是按照月份統計整合在一起（圖1）。

圖1 原始數據

現在老總需要查看每月和各部門考勤，因此需要將上述匯總數據按照月份和部門類別分別整理，這類操作藉助透視報表就可以輕鬆完成了。下面以月份整理為例。

點擊「插入數據透視表」，表區域選擇原始數據中的數據區域，設置透視表的位置選擇「新工作表」，這樣在新工作表會生成一個透視表（圖2）。

圖2 創建數據透視表

在出現數據透視表窗口，在數據透視表欄位，勾選「員工」，「曠工天數」和「月份」。接著使用滑鼠分別將「月份」拖到下方的篩選器，「員工」，「曠工天數」拖到「行」（圖3）。

圖3 透視表欄位設置

切換到「數據透視表選項顯示報表篩選頁」，在打開的窗口選擇按照「月份」進行篩選。這裡篩選是根據上述篩選欄位進行設置，因此如果是其他報表，如要對部門進行篩選，則在上述篩選欄位添加「部門」欄位即可（圖4）。

圖4 顯示報表篩選頁

完成上述操作後，點擊「確定」，這樣在當前工作簿下就會新增11月份、12月份兩個子表，切換到各自子表後，員工曠工數據就一目了然了。顯然利用自行定義欄位的方法，我們可以自行選擇需要的數據進行展示，並自動生成需要的報表（圖5）。

圖5 自動生成子表

快速整合，獨立子表融合為匯總表

上述介紹的是將總表的數據分離為特定數據所需的子表，在實際工作中還有很多反向操作，即將多個子表的內容匯總，匯總操作同樣可以藉助數據透視表完成。比如一份原始數據如下，銷售部業績已經按照月份做好統計，現在需要分別統計銷售一、二、三部的銷售額（圖6）。

圖6 銷售示例數據

按Alt D快捷鍵，接著繼續按下P鍵，彈出「數據透視表和數據透視圖嚮導」，選擇「多重合併計算區域」。繼續下一步選擇「創建單頁欄位」，在選定區域這步，按提示選擇「1月」工作表的「$C$1:$D$4」（即只要選擇C、D列內容即可），點擊「添加」將其添加到下方的列表。重複上述操作，依次將「2月」工作表、「3月」工作表相應的數據區域也添加到列表，最後點擊「完成」（圖7）。

圖7 選定區域數據

在打開的新建數據透視表，選中「頁1」將其更改為「銷售分部統計」，這樣在新的數據透視表就可以將每個銷售分部的統計數據自動列出。利用數據透視表可以非常方便按照指定的要求，將各個子表的特定數據列出並進行自動統計（圖8）。

圖8 統計多字表的數據透視表

當然這個透視表還可以根據我們實際需要，利用行標籤的篩選，靈活顯示數據，比如只要顯示銷售一部的數據，那麼只要在「行標籤」下拉列表勾選「銷售一部」，這裡顯示的就是銷售一部的統計數據了。此外，在數據透視表中我們還可以根據自己的實際需要，結合函數完成更多數據要求的展示。

比如在上述原始透視表數據中還包含回款金額，現在需要透視表中增加回款率指標（即回款額/銷售額）。切換到「數據透視表工具欄位、項目和集計算欄位」，在打開的增加欄位窗口，點擊將默認的欄位名改成「回款率」，在公式欄輸入「=」，依次選擇「回款額」並點擊插入欄位，在插入的回款額欄位後輸入除號（也就是/），然後選中「銷售額」並點擊「插入欄位」（圖9）。

圖9 插入欄位

這樣在原來透視表中就會新增一個回款率的欄位，這個欄位會自動根據上述的公式進行計算。為了方便顯示，可以將顯示單元格數據設置為百分比顯示（圖10）。

圖10 插入新的欄位

當然我們可以根據自己的實際需要插入更多的計算欄位。這樣即使原始數據中沒有相應的統計。通過自行插入欄位的方法，可以在透視表中顯示更多的數據。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！