前段時(shí)間在一家銀行企業(yè)培訓(xùn)結(jié)束后，學(xué)員小C咨詢了工作中的一個(gè)問題：“如何在Excel中實(shí)現(xiàn)數(shù)據(jù)的雙向匹配？”
在《如何在Excel中批量匹配數(shù)據(jù)？》一文中我們曾經(jīng)介紹過如何利用VLOOKUP和COUNTIFS函數(shù)如何對比兩張表格的數(shù)據(jù)差異。
比如下圖所示的《培訓(xùn)報(bào)名人員名單》和《培訓(xùn)簽到人員名單》兩張數(shù)據(jù)表，我們可以在左側(cè)的表格中用VLOOKUP或COUNTIFS函數(shù)查詢出來有哪些人報(bào)了名卻沒有來參加培訓(xùn)的。
但假如在《培訓(xùn)簽到人員名單》中有同學(xué)沒有報(bào)名卻來參加了培訓(xùn)，我們又必須在右側(cè)的表格中重新再寫一次VLOOKUP或COUNTIFS函數(shù)來進(jìn)行比較。
由于比較的結(jié)果分別放在兩張表中，導(dǎo)致我們無法直觀的同時(shí)看到兩張表的差異。今天我們就來介紹利用Power Query實(shí)現(xiàn)數(shù)據(jù)的雙向比較，可以讓我們在一個(gè)表中就可以觀察到兩張表的差異，無需編寫任何一個(gè)函數(shù)。
Power Query是一種數(shù)據(jù)連接技術(shù)，可用于發(fā)現(xiàn)、連接、合并和優(yōu)化數(shù)據(jù)源以滿足分析需要，是 Power BI商業(yè)智能分析工具的組件之一。在Excel 2016的版本中已經(jīng)包含了Power Query，如果你使用的是早期版本，可以到微軟的官方網(wǎng)站下載對應(yīng)版本的Power Query。
具體操作步驟如下：
1、光標(biāo)放在表格中，無需全選數(shù)據(jù)，點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡中的“從表格”功能，即可將數(shù)據(jù)加載到Power Query中。
2、表格加載到Power Query之后，可以在左側(cè)修改一下表名稱，以便于后面分析的時(shí)候能對應(yīng)到數(shù)據(jù)。
3、導(dǎo)入了第一張表格之后，點(diǎn)擊頂部的“關(guān)閉并上載-關(guān)閉并上載至”功能，可以返回到Excel中繼續(xù)導(dǎo)入第二張表格。
4、導(dǎo)入了要對比的兩張表格之后，點(diǎn)擊“合并查詢-將查詢合并為新查詢”功能。
5、在彈出來的合并對話框中，選擇匹配字段，比如按“工號”合并這兩張表，類似于VLOOKUP函數(shù)第一個(gè)參數(shù)的作用。在底部的聯(lián)結(jié)種類中選擇“完全外部”，這種聯(lián)結(jié)方式可以涵蓋兩張表中的全部數(shù)據(jù)。
6、在合并后的新查詢中，點(diǎn)擊下圖所示的圖標(biāo)，展開第二張表的內(nèi)容。
7、這樣我們就可以看到兩張表雙向?qū)Ρ鹊慕Y(jié)果了。左側(cè)的兩列是報(bào)名表中的數(shù)據(jù)，最后一行null代表空的數(shù)據(jù)，意味著A010的張三，沒有報(bào)名卻參加了培訓(xùn)。右側(cè)的兩列是簽到表中的數(shù)據(jù)，同樣為null的數(shù)據(jù)，意味著報(bào)了名卻沒有來參加培訓(xùn)的人員數(shù)據(jù)。
8、最后點(diǎn)擊“關(guān)閉并上載”功能，就可以將數(shù)據(jù)加載到Excel中了。這樣完成的數(shù)據(jù)對比結(jié)果，如果報(bào)名表或簽到表的數(shù)據(jù)更新了，我們只需要在查詢結(jié)果中點(diǎn)擊鼠標(biāo)右鍵的“刷新”功能，就可以獲得最新的結(jié)果，一勞永逸。
聯(lián)系客服
