excel 如何運用數(shù)據(jù)透視表搜索數(shù)據(jù)差異完成技巧

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

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

圖1 原始數(shù)據(jù)

兩個時點的數(shù)據(jù)結(jié)構(gòu)都是一樣的，均有3個項目和2項數(shù)據(jù)，現(xiàn)在要求按同樣的數(shù)據(jù)結(jié)構(gòu)列出前后的差異以供后續(xù)工作的開展。

在保證所有項目數(shù)據(jù)的格式對應(yīng)相同的前提下，操作步驟如下：

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

圖2 復(fù)制工作表

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

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

圖3 數(shù)據(jù)準備1

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

圖4 數(shù)據(jù)準備2

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

圖5 數(shù)據(jù)準備3

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

圖6 插入數(shù)據(jù)透視表

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

圖7 添加透視表字段

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

圖8 透視匯總方式調(diào)整

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

圖9 調(diào)整透視設(shè)置1

在彈出的界面里點“顯示”標簽項，勾選“經(jīng)典數(shù)據(jù)透視表布局（啟用網(wǎng)絡(luò)格中的字段拖放）”并確定：

圖10 調(diào)整透視設(shè)置2

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

圖11 調(diào)整透視設(shè)置3

完成后的效果見圖12。

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

圖12 調(diào)整透視設(shè)置4

完成后的數(shù)據(jù)透視表效果圖：

圖13 調(diào)整透視設(shè)置后的效果

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

圖14 整理后的數(shù)據(jù)

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

圖15 計算差異

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

圖16 刪除無差異行

15、最后取消自動篩選，差異結(jié)果就呈現(xiàn)在了你的眼前：

圖17 差異結(jié)果

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