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

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

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

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

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

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

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

圖2 復(fù)制工作表

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

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

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

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

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

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

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

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

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

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

圖7 添加透視表字段

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

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

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

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

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

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

10、再點(diǎn)“數(shù)據(jù)透視表工具”菜單中的“設(shè)計(jì)”-“報(bào)表布局”-“重復(fù)所有項(xiàng)目標(biāo)簽”：

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

完成后的效果見(jiàn)圖12。

11、逐一右擊所有項(xiàng)目的任意單元格，去掉“分類(lèi)匯總…”前的勾：

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

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

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

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

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

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

圖15 計(jì)算差異

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

圖16 刪除無(wú)差異行

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

圖17 差異結(jié)果

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