Powerquery多表合并案例:一鍵完成多個(gè)sheet合并
?
作者:夏雪來(lái)源:部落窩教育發(fā)布時(shí)間:2018-08-14 15:23:37點(diǎn)擊:43902
小編有話說(shuō):小編看過(guò)作者上次的教程后,回到家里,馬不停蹄地給自己的excel安裝了Power Query插件,匯總數(shù)據(jù)太便利了。上次教程只是教大家合并固定數(shù)量的工作表,今天教給大家合并工作簿的方法,不管你是增加還是減少工作表,都可以實(shí)時(shí)更新,到底有多6,往下看你就知道了!
上次跟大家分享了Power Query合并工作表之后,很多小伙伴留言問(wèn)如果碰到工作表的增加和減少應(yīng)該怎么辦?今天就跟大家介紹下使用Power Query合并工作簿,這樣工作簿里不管是工作表變動(dòng)還是數(shù)據(jù)變動(dòng)都能一鍵刷新匯總了。趕快來(lái)學(xué)習(xí)吧!
上次的教程沒(méi)有學(xué)習(xí)的小伙伴先學(xué)習(xí)之前的喲,點(diǎn)擊可查看:《Excel一鍵生成報(bào)表教程:powerquery多表合并案例》
如圖,工作簿下面有四個(gè)工作表,分別列舉了四個(gè)城市的銷售數(shù)據(jù)。
首先新建一個(gè)工作簿,打開數(shù)據(jù)選項(xiàng)卡下[獲取與轉(zhuǎn)換]組,點(diǎn)擊新建查詢---從文件---從工作簿。
在導(dǎo)入數(shù)據(jù)窗口找到工作簿所在位置,選擇工作簿,點(diǎn)擊導(dǎo)入。
在導(dǎo)航器窗口勾選“選擇多項(xiàng)”,勾選下面的四個(gè)工作表。
也可以直接選中工作簿(后面的4表示工作簿有四個(gè)工作表),點(diǎn)擊編輯。
這樣就進(jìn)入了之前介紹過(guò)的Power Query編輯器界面。上方是菜單欄,中間是表格區(qū)域也是最后要返回到工作表的數(shù)據(jù),右側(cè)查詢?cè)O(shè)置窗口顯示的是Power Query的操作記錄。
中間顯示的表格區(qū)域顯示的是數(shù)據(jù)源工作簿的信息,“Name”這一列是該工作簿所包含工作表的名稱,其中“Data”這一列是綠色字體,這就代表每個(gè)單元格里包含了一個(gè)Table。點(diǎn)擊單元格可以預(yù)覽里面的內(nèi)容(注意:當(dāng)鼠標(biāo)放在文字上面變成手掌形狀的時(shí)候,是打開這個(gè)單元格里面的文件從而生成一個(gè)新的步驟,如果需要預(yù)覽,點(diǎn)擊單元格其他地方就可以了)。下方預(yù)覽窗格出現(xiàn)了這個(gè)Table的內(nèi)容,也就是我們要匯總的數(shù)據(jù)。
現(xiàn)在要做的就是把“Data”這一列所有的Table提取合并。
點(diǎn)擊Data右上角的展開按鈕,在下拉窗口選擇展開所有列,這個(gè)“使用原始列名作為前綴”一般都不用勾選,點(diǎn)擊確定。
這樣工作簿里面工作表的所有內(nèi)容就匯總到表里了。右側(cè)查詢?cè)O(shè)置窗口就出現(xiàn)了一個(gè)新的步驟。當(dāng)點(diǎn)擊步驟前面的叉號(hào)時(shí),這個(gè)步驟就刪除了,退回到上一步。這就是Power Query的步驟記錄器,當(dāng)我們某一步做錯(cuò)需要返回的時(shí)候可以選擇上面的任一步驟查看該步驟的結(jié)果,或者刪除新的步驟。
接下來(lái)刪除其他不需要的列。按住Ctrl選擇要?jiǎng)h除的列,點(diǎn)擊開始選項(xiàng)卡下,[管理列]組里“刪除列”。
把新列名修改成原來(lái)的列名,可以直接把第一行字段作為列名。點(diǎn)擊開始選項(xiàng)卡下,[轉(zhuǎn)換]組里的“將第一行用作標(biāo)題”。
由于合并的多個(gè)工作表有多個(gè)標(biāo)題,再把重復(fù)的標(biāo)題篩選掉。點(diǎn)擊業(yè)務(wù)員列右上方的篩選按鈕,把“業(yè)務(wù)員”勾選掉。
這就完成了我們要做的內(nèi)容。點(diǎn)擊開始選項(xiàng)卡下,[關(guān)閉]組里的“關(guān)閉并上載”。
關(guān)閉并上載是指把Power Query里做好的所有查詢都上載到各個(gè)工作表里,如果只有一個(gè)查詢可以使用這個(gè)方法。
關(guān)閉并上載至可以彈出窗口供大家選擇是上載到表里還是創(chuàng)建連接。而創(chuàng)建的連接可以直接用來(lái)生成數(shù)據(jù)透視表等,后面會(huì)給大家介紹。
這樣Power Query里面的內(nèi)容就直接上載到表格里了。為了方便匯總,我們把整理好的數(shù)據(jù)插入數(shù)據(jù)透視表。
這樣這個(gè)匯總查詢就完成了。我們嘗試在數(shù)據(jù)源工作簿里添加一個(gè)工作表。里面的字段名跟其他工作表一致。點(diǎn)擊保存。
然后再在匯總表里點(diǎn)擊數(shù)據(jù)選項(xiàng)卡下的[連接]組里的“全部刷新”。
這樣新的工作表就被添加進(jìn)來(lái)了。
同樣,當(dāng)工作表減少或者數(shù)據(jù)變動(dòng)都可以通過(guò)這個(gè)方式完成。通過(guò)這種方式就能很輕易的匯總工作簿里的數(shù)據(jù)了。
有的時(shí)候我們?cè)磾?shù)據(jù)可能比較多,全部數(shù)據(jù)上載到表里反應(yīng)會(huì)比較慢,那可以直接用連接生成數(shù)據(jù)透視表進(jìn)行分析。
工作表右側(cè)工作簿查詢窗口出現(xiàn)的是我們做好的查詢,選擇該查詢右鍵選擇“加載到”。
如果沒(méi)有這個(gè)工作簿查詢窗口,可以點(diǎn)擊數(shù)據(jù)選項(xiàng)卡[獲取和轉(zhuǎn)換]組里的“顯示查詢”就可以了。
在“加載到”這個(gè)窗口選擇 “僅創(chuàng)建連接”,點(diǎn)擊加載。
這樣做好的查詢就以連接的方式存儲(chǔ)在工作簿里。然后點(diǎn)擊插入選項(xiàng)卡下[表格]組的“數(shù)據(jù)透視表”。
之前創(chuàng)建數(shù)據(jù)透視表都是選擇第一個(gè)表或區(qū)域。我們選擇第二個(gè)“使用外部數(shù)據(jù)源”,點(diǎn)擊“選擇連接”。
在現(xiàn)有連接窗口,就能看到我們剛才創(chuàng)建的連接,選中點(diǎn)擊打開,然后確定插入數(shù)據(jù)透視表。
這樣就能根據(jù)看到根據(jù)這個(gè)連接生成的數(shù)據(jù)透視表了,跟我們剛才用表格創(chuàng)建的數(shù)據(jù)透視表是一樣的。
用這種方法一方面可以避免EXCEL數(shù)據(jù)過(guò)多造成文件過(guò)大,另一方面需要數(shù)據(jù)匯總的話也可以減少加載到表那一步。
Power Query 合并工作簿就介紹完了。但Power Query在數(shù)據(jù)匯總方面還有更多高級(jí)的技能。下一次給大家介紹Power Query合并文件夾,敬請(qǐng)期待哦!
本文配套的練習(xí)課件請(qǐng)加入QQ群:806440210下載。
如果您因工作所需使用到Excel,不妨關(guān)注部落窩教育的《一周Excel直通車》視頻課或《Excel極速貫通班》直播課系統(tǒng)學(xué)習(xí)。
如果您想要隨時(shí)隨地學(xué)習(xí)excel,掃下方二維碼,可關(guān)注公眾號(hào),每日為您推送優(yōu)質(zhì)excel教程:
Excel教程相關(guān)推薦:
《用GET.WORKBOOK函數(shù)實(shí)現(xiàn)excel批量生成帶超鏈接目錄且自動(dòng)更新》
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- 一個(gè)公式搞定空行的動(dòng)態(tài)插入
- 多功能銷售日歷模板制作
- 用正則函數(shù)快速拆分單元格中的同類數(shù)據(jù)
- 用免費(fèi)AI將圖片批量轉(zhuǎn)表格
- 先入先出出入庫(kù)模板中出庫(kù)批次的自動(dòng)匹配
- 用SUBSTITUTE替換法為產(chǎn)品找到最匹配的類別
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)