跨表提取數(shù)據(jù),函數(shù)高手被名不經(jīng)傳的Microsoft Query 直接KO
?
作者:夏雪來源:部落窩教育發(fā)布時間:2018-11-14 09:40:58點擊:9295
跨表提取數(shù)據(jù)很多伙伴第一反應就是函數(shù)如VLOOKUP,或者什么INDEX+SMALL+IF萬金油公式。其實,如果提取的是多列數(shù)據(jù),有一個被很多人丟在旮旯里許久許久的Microsoft Query才是王者!它不但操作簡易,輕易解決“一對多”,而且它生成的結果表可以與數(shù)據(jù)源形成動態(tài)鏈接,數(shù)據(jù)源變化了,結果也會動態(tài)更新!
今天給大家分享一個很少人用但有奇效的功能---Microsoft Query來幫助大家解決兩個表格“一對多”的數(shù)據(jù)提取,或者說解決用一個表去匹配另一個表生成特定數(shù)據(jù)的做法。
如下圖所示,同一個工作簿里有兩個工作表,“部門人員信息表”列出了各部門的員工姓名和對應的主管,“省份銷售數(shù)據(jù)表”列出了每個員工負責的多個省份以及對應省份的三個月銷售數(shù)據(jù)。現(xiàn)在要求把兩個表根據(jù)姓名這列匯總到一個表里。
原表
需要的結果
函數(shù)我們就不用了。在9月初的《打敗查找函數(shù),pq合并查詢一次搞定多表匹配》中,Power Query就打敗了函數(shù)實現(xiàn)多表匹配。這次Microsoft Query操作更簡單,甩函數(shù)幾條街~~~~~~
那使用Microsoft Query如何操作呢?
STEP 01 啟用Microsoft Query并加載數(shù)據(jù)
(1)新建一個工作簿,點擊【數(shù)據(jù)】選項卡下【獲取外部數(shù)據(jù)】組里“自其他來源”下拉菜單的“來自Microsoft Query”。
在【選擇數(shù)據(jù)源】窗口“數(shù)據(jù)庫”選項下點擊“Excel Files”,勾選下方的“使用[查詢向導]創(chuàng)建/編輯查詢” ,點擊確定。
在【選擇工作簿】窗口右側目錄里找到數(shù)據(jù)源所在的位置,在左側數(shù)據(jù)庫名找到文件,點擊確定。
(2)有時系統(tǒng)會提示如下窗口:“數(shù)據(jù)源中沒有包含可見的表格”,這個不用管,點擊確定。
進入下方左側的【查詢向導】窗口,點擊下面的“選項”按鈕,打開右側【表選項】窗口,勾選“系統(tǒng)表”點擊確定。
這樣【查詢向導】窗口就會出現(xiàn)數(shù)據(jù)源里的工作表了。這是由于Excel把自己的工作表叫做“系統(tǒng)表”,勾選了之后在查詢窗口就能看到了。
接下來選中兩個工作表分別點擊中間的“>”按鈕把左側的“可用的表和列”添加到右側的“查詢結果中的列”,點擊下一步。
這時又會彈出一個窗口,提示““查詢向導”無法繼續(xù),因為該表格無法鏈接到您的查詢中。您必須在Microsoft Query中的表格之間拖動字段,人工鏈接。”這個也不用管,點擊確定。
STEP 02 按需要項匹配數(shù)據(jù)
此時我們就進入Microsoft Query窗口,上方是類似EXCEL的菜單欄,中間是表區(qū)域,顯示了當前我們添加的兩個表以及對應的字段。下方的數(shù)據(jù)區(qū)域就是融合了兩個表的結果。
這時候數(shù)據(jù)區(qū)域的結果是雜亂無章的,原因是我們沒有給兩個表添加關系。兩個表里是通過姓名列來一一對應的。
(1)用鼠標選中左邊“部門人員信息表”中的“姓名”,將其拖曳到右表“省份銷售數(shù)據(jù)表”中的“姓名”上面,然后松開鼠標。這時在兩個表的“姓名”字段之間出現(xiàn)了一條兩端帶有細小節(jié)點的聯(lián)接線。下方數(shù)據(jù)區(qū)域就立即更新了。
(2)由于有兩列相同的姓名,我們選中其中一列,點擊菜單欄【記錄】下方的“刪除列”。
STEP 03 把結果數(shù)據(jù)返回到Excel工作表
最后要做的就是把結果返回到EXCEL。
(1)點擊菜單欄“SQL”左側的按鈕,將數(shù)據(jù)返回到Excel。
(2)在EXCEL中出現(xiàn)【導入數(shù)據(jù)】窗口,我們選擇顯示為“表”,位置放置在現(xiàn)有工作表。
返回結果如下:
到此簡單的3步我們完成了需要的數(shù)據(jù)匹配,生成了新的數(shù)據(jù)表。
額外之喜
我們發(fā)現(xiàn)Microsoft Query生成的數(shù)據(jù)就是一張超級表,也可以直接創(chuàng)建數(shù)據(jù)透視表或者數(shù)據(jù)透視圖。
同時,這張表是和數(shù)據(jù)源動態(tài)鏈接的。比如我們修改一下原數(shù)據(jù),點擊保存關閉。
在返回結果上右鍵點擊刷新。
這樣數(shù)據(jù)就同步過來了。
運用條件
需要注意的是,使用這種方法,必須要保證數(shù)據(jù)源的規(guī)范性。要求工作表不能存在與數(shù)據(jù)源無關的數(shù)據(jù),并且表格第一行為列標題。如果要實現(xiàn)動態(tài)鏈接,那么工作簿和工作表的名字和位置不能修改。
怎么樣,大家學會了嗎?是否比PQ簡單,比函數(shù)簡單?
歡迎加入QQ群:264539405交流學習下載素材。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關注公眾號,可隨時隨地學習Excel:
相關推薦:
跨表提取數(shù)據(jù)的另一種非函數(shù)方法《打敗查找函數(shù),pq合并查詢一次搞定多表匹配》
一對多提取數(shù)據(jù)萬金油函數(shù)法《Excel萬金油公式INDEX-SMALL-IF-ROW篩選函數(shù)公式解讀》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復,Excel卻判定數(shù)據(jù)重復,這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!