excel 如何運用數據透視表搜索數據差異完成技巧

數據透視表絕對是Excel的一大利器，用好它可以讓你分分鐘完成復雜的工作。本例就是利用數據透視表查找兩組復雜數據間最細微的差異，堪稱“明察秋毫的鷹眼”，可供財務對賬、統計查找差異參考。

例：某公司9月30日和10月15日累計收到襯衫訂單如下圖（原始數據成千上萬行）：

圖1 原始數據

兩個時點的數據結構都是一樣的，均有3個項目和2項數據，現在要求按同樣的數據結構列出前后的差異以供后續工作的開展。

在保證所有項目數據的格式對應相同的前提下，操作步驟如下：

1、單擊“0930”工作表名，按住Ctrl后水平向右移動，即可復制一張表，改名為“差異”，置于最末：

圖2 復制工作表

2、將“差異表”中的“數量”改為“0930數量”、“金額”改為“0930金額”。

3、拖選第2張表“1015”中的A～E列，并復制，粘到差異表I列（與第1組數據空數據項數+1=3列），并將“數量”改為“1015數量”、“金額”改為“10150金額”：

圖3 數據準備1

4、在第2組數據值項前（L列前）插入與數據項數相等（此處為2）的空列，這一步極為關鍵，目的在于經數據透視表匯總時不會與第1組數據混在一起，但相同項的數據會合計為一行：

圖4 數據準備2

5、將第2組數據中除標題外的所有數據剪切（單擊數據的最左上角單元格，使用Ctrl+Shift+光標向下，再向右、向右選擇完全部數據）拼接至第1組數據下一行，不要有空行，并將第2組數據中的數據項標題拼在第1組數據后邊，使兩組數據形成一個整體，但各數據項都是分開列的。再清除原來第2組數據的項目標題。為有效展示，下圖隱藏了若干中間行：

圖5 數據準備3

6、選中合并后的數據區中的任意一單元格，在本表的J1（或第1行的任意空白單元格）插入數據透視表：

圖6 插入數據透視表

7、將所有項目名拖動進透視表的“行”框、將所有數據項拖進透視表的“Σ值”框中：

圖7 添加透視表字段

8、點擊“Σ值”框中的每一個項目，點“值字段設置”，將匯總方式由“計數項”改為“求和項”：

圖8 透視匯總方式調整

9、單擊透視表的任意一單元格，在新境的“數據透視表工具”菜單的“分析”標簽里點 “選項”命令：

圖9 調整透視設置1

在彈出的界面里點“顯示”標簽項，勾選“經典數據透視表布局（啟用網絡格中的字段拖放）”并確定：

圖10 調整透視設置2

10、再點“數據透視表工具”菜單中的“設計”-“報表布局”-“重復所有項目標簽”：

圖11 調整透視設置3

完成后的效果見圖12。

11、逐一右擊所有項目的任意單元格，去掉“分類匯總…”前的勾：

圖12 調整透視設置4

完成后的數據透視表效果圖：

圖13 調整透視設置后的效果

12、選擇所有透視表列，復制全部數據并原位置選擇性粘貼數值，按Ctrl+H將“求和項：”替換為空，最后刪除透視表的數據源、刪除透視的最后一行“總計”：

圖14 整理后的數據

13、在標題末續兩列，標題名為“數量差”和“金額差”，在數量差下首個單元格寫入公式：用1015數量減0924數量，即在H3中寫入：=F3-D3，并回車；再選中H3，鼠標右移至右下角直至鼠標變為細黑十字向右拖動復制到金額差中；最后連選H3:I3，同樣鼠標右移至右下角直至鼠標變為細黑十字后雙擊，即自動完成向下填充公式。有多項數據的，以此類推：

圖15 計算差異

14、選擇所有標題，加上自動篩選，篩選數量差和金額差均為“0”的行并選擇篩選出的所有行號（點第1個篩選出的行，然后按Ctrl+Shift+光標向下鍵可一次選完）右鍵刪除：

圖16 刪除無差異行

15、最后取消自動篩選，差異結果就呈現在了你的眼前：

圖17 差異結果

本方法看似步驟繁多，實際操作起來幾分鐘就完成了，而且差異項級別隨心所欲一次實現，很有“萬軍從中取‘差異首級’如探囊取物”般容易的感覺。