Excel中怎么利用PowerPivot實(shí)現(xiàn)跨表的的關(guān)聯(lián)查詢?

發(fā)布時(shí)間：2016-03-23 16:52:39 作者：佚名

Excel中怎么利用PowerPivot實(shí)現(xiàn)跨表的的關(guān)聯(lián)查詢？在做倉(cāng)庫(kù)進(jìn)銷存的是偶需要用到這個(gè)功能，今天我們就來看看Excel中PowerPivot的詳細(xì)使用方法，需要的朋友可以參考下

本文重點(diǎn)介紹如何應(yīng)用Excel的PowerPivot實(shí)現(xiàn)跨表的關(guān)聯(lián)查詢，實(shí)際應(yīng)用于淘寶后臺(tái)數(shù)據(jù)、ERP數(shù)據(jù)導(dǎo)出后如何進(jìn)行匹配篩選。自從Excel2013以來，安裝時(shí)自動(dòng)增加了PowerPivot這組應(yīng)用程序和服務(wù)，強(qiáng)大的分析功能可以取代Access數(shù)據(jù)庫(kù)的一些基本功能，也簡(jiǎn)化了很多運(yùn)算。關(guān)聯(lián)表的操作，通過兩表間的關(guān)聯(lián)字段匹配進(jìn)行全字段的輸出，都有了應(yīng)用。以前還用函數(shù)lookup()（含Hlookup和vlookup）來查找匹配，總碰到重復(fù)項(xiàng)的處理和邏輯判斷的問題，現(xiàn)在用數(shù)據(jù)模型可以簡(jiǎn)化解決了。

應(yīng)用場(chǎng)景描述：管理員小云負(fù)責(zé)售后跟蹤，每天都會(huì)有一個(gè)有特殊要求或者需要特別跟蹤的訂單號(hào)列表，要根據(jù)每個(gè)訂單號(hào)提取該訂單全部信息。他發(fā)現(xiàn)電商后臺(tái)的訂單信息沒有那么完善，于是就將自己系統(tǒng)所有訂單都導(dǎo)出來，與跟蹤訂單號(hào)列表進(jìn)行匹配查詢，篩選所需內(nèi)容。

需求分析：①整理訂單列表和訂單信息表；②匹配查詢，篩選信息。

學(xué)習(xí)了本文后，有興趣的可以看excel數(shù)據(jù)模型制作進(jìn)銷存的實(shí)例。

規(guī)范銷售出庫(kù)表——補(bǔ)值。ERP或者電商系統(tǒng)導(dǎo)出來的數(shù)據(jù)表格，有可能存有空白格，需要填齊，這樣才能用數(shù)據(jù)模型和數(shù)據(jù)透視表。補(bǔ)值也很簡(jiǎn)單，空白格就是用上一行的值填補(bǔ)即可。

1、選擇銷售出庫(kù)表的數(shù)據(jù)區(qū)域（可整列整列選擇）→菜單開始→查找和選擇→定位條件→空值→這時(shí)，活躍單元格框出現(xiàn)在最左上角的空白處，鍵盤輸入“=”→按上箭頭或鼠標(biāo)選擇上一個(gè)單元格→按著Ctrl回車（結(jié)束公式編輯狀態(tài)）。這樣，就完成了數(shù)據(jù)補(bǔ)值。

2、規(guī)范訂單號(hào)列表→按列排序。經(jīng)理給的訂單號(hào)列表是一個(gè)單元格存儲(chǔ)了很多個(gè)單號(hào)，這樣是無法進(jìn)行匹配查詢的。先將它用逗號(hào)分列成橫表，再?gòu)?fù)制選擇性粘貼→轉(zhuǎn)置粘貼成豎表。

3、添加表建立數(shù)據(jù)模型。在銷售出庫(kù)表，整列整列地選擇表，點(diǎn)選菜單→PowerPivot→添加到數(shù)據(jù)模型→勾選我的表含有標(biāo)題，確認(rèn)。第一次啟動(dòng)PowerPivot會(huì)很慢，要耐心等待。同樣道理，將跟蹤的訂單號(hào)也添加進(jìn)去。

4、在數(shù)據(jù)模型里面建立關(guān)系。“關(guān)系”是關(guān)系型數(shù)據(jù)庫(kù)里面一個(gè)很重要的概念，這里不展開，有興趣可自己上網(wǎng)查。這里應(yīng)用“關(guān)系”，起到數(shù)據(jù)從一個(gè)表傳遞到另一個(gè)表的作用。回到PowerPivot界面，點(diǎn)選菜單的開始→在查看組里選擇關(guān)系視圖。將“表2”的訂單號(hào)拖到“表1”的單據(jù)編號(hào)，這樣，就建立了1個(gè)一對(duì)多的關(guān)系。

5、用數(shù)據(jù)模型建數(shù)據(jù)透視表。菜單插入→數(shù)據(jù)透視表→選擇“使用此工作表的數(shù)據(jù)模型”，由于之前建立了數(shù)據(jù)模型，所以這個(gè)選項(xiàng)沒有致灰→位置選現(xiàn)有工作表，跟蹤的訂單號(hào)!D5，其他內(nèi)容不需要改，確認(rèn)。將表2的訂單號(hào)拖到“行”，將表1的單據(jù)編號(hào)、產(chǎn)品名稱拖到“行”，將表1的實(shí)發(fā)數(shù)量、銷售金額拖到“值”。

6、調(diào)整數(shù)據(jù)透視表格式。菜單設(shè)計(jì)→分類匯總，不顯示匯總→報(bào)表布局，已表格形式顯示。這樣，累贅的排列變得清爽了。如果把產(chǎn)品名稱從行刪除，就更加清爽，容易得出結(jié)果，匹配出2條訂單（出現(xiàn)在上面），匹配不出的訂單在下方。此外，根據(jù)實(shí)際查詢需要，可以將更多字段拖入“行”，這里略。