1_1_04 數據來源之工作簿(智能提取匯總指定文件夾下的工作簿數據)

Urin的自习室
19粉絲
2024年3月10日 #Excel #PowerQuery
大家好那麼這節課
我們把這個範圍再擴大一點
前面一節課
我們講的是
獲取所工作簿下面的
所有工作表的數據
那麼這節課
我們講的是
獲取
文件夾下面的所有工作簿的數據
也就是我可以獲取什麼呀
多個工作簿啊
多個工作簿裏面的數據
不僅僅只能獲取一個
所有工作指定文件夾下面
所有工作簿裏面的所有工作表的數據
我都可以第一把獲取出來是吧
如果平常我們要實現這個功能的話
啊如果你不編寫代碼比登天還難
就算可以的話也哎
這個操作起來步驟也非常的複雜
而這個呢
就變得很簡單了
好
我們先來看一下我們做的視力數據
等一下我要獲取年份
這個文件夾下面
這個文件夾下面的數據
這個文件夾下面它有兩個文件夾
也就說它有子文件夾
那他就算有子文件夾
子文件夾裏面的數據
同樣可以獲取到啊
比方說這個年份
這個呃文件夾下面
如果就在這個裏面的工作簿也可以
在子文件夾的工作簿也可以
那麼現在這些工作部的話
1314年是吧
然後呢我隨便寫了幾個部門
這些部門的數據的結構
肯定你首先要保持一致啊
纔可以
我們現在來打開一個
來看一下它記錄的是什麼
記錄的是每個工作簿
打開之後
比方後勤部打開哇
你會發現打開之後的話
它顯示了每個月的工資收入情況
是吧 1-12個月
那麼這是哪一年的
2014年 2014年的
等一下
我們把這些數據全部啊導入轉換
到合併到一起之後
你要做任何的數據分析
那就太簡單了
易如反掌是吧
易如反掌
好那麼這是這個樣子
這是這個樣
那當然如果你要有他獲等一下的話
他獲取不到這個文件夾的名字
但是如果說
你說你想把文件那個年份
也可以獲取上去的話
你只能在上面給他標記一下啊
標記一下是哪一年的是吧
比方說2013年啊
可以這樣來財務部是吧
然後下面的也是不小心打開了
我們啊
你不想標記的話也沒有關係啊
就不標記
OK好
我們看怎麼樣來獲取啊打開
啊
等一下我就獲取到這個工作簿裏面
獲取的這數據源就是年份
這個文件夾下面的所有的工作簿
下面的所有的工作表啊
好我們打開之後呢
可以先複製一下這個數據
然後呢在這邊選擇好數據源是吧
你就不要選擇工作簿了
而是選擇從文件夾
從文件夾當中呢
來獲取
好然後呢
你可以去瀏覽是吧
但是你也可以像我這樣子
直接把那個位置給它複製過來哎
應該還有一層是吧
在這個裏面
OK了
導出
粘貼就是指的這個文件夾下面
點擊確定
那麼再加載
旋轉轉啊轉轉轉
啊
正在建立這個鏈接
OK你看剛纔的話
他在加載
實際上就是在在找
我指定的這個上面的
這個目錄下的所有的文件
以及文件夾是不是
以及他的路徑這些都給我們找出來了
看到沒路徑這些都找出來了
哎哎
不過現在看不到是吧
但是我們可以看
等一下加載進來就可以看得到
但是我可以知道
它識別到哪些文件夾
識別到哪些工作簿
這些工作簿的啊
一些信息啊
啊比方說新建的一個日期啊等等
它屬性啊
修改日期啊
那些都有
在這個裏面
是不是它路徑啊
folder pass然後我們點擊編輯
好就進入到了這個編輯查詢
編輯查詢編輯器的
後面是指的他的信息
你點一下可以看到
是不是這個是指的這個文件夾
然後這個呢
啊那個這個是指的這個工作簿
這個工作簿
這個工作簿
然後你你可不可以什麼呀
雙擊把它打開呢
雙擊打不開
那怎麼辦呢
你這樣獲取沒有意義啊
是吧我們需要把它
就是你獲取到指定目錄下的
指定文件夾下的所有工作簿之後
我希望能夠把這個裏面的數據啊
每個獲取到的每個工作簿下面的數據
全部什麼呀
拆開是吧
全部進行打開
你看就像這個一樣
我剛纔導入的是
看一下啊
剛纔導入的是2013年銷售部
這個工作簿下面的數據
這個工作簿打開
這問題就是我怎樣去打開呢
你不能像我現在一個一個去打開
對不對
怎樣去打開好
我們先把它什麼呀
呃上載來看一下啊
先創建成一個
鏈接也可以啊
弄成一個表也行
好
我們就先弄成一個表給大家看一下吧
加載
好你看這些信息都有了
是不是最關鍵的
怎麼樣去打開這些東西
打開這些東西
那麼你說打開工作簿怎麼怎麼寫呢
其實打開工作簿的話呃
你可以參照我們向學規則的話
我們暫時可以先先清除一些
我們不想要的啊
一些不想要的
比方說這個暫時不想要是吧
這些這些
這些擴展名啊
什麼創建日期這些
刪除然後這個是他的路徑啊
看嗯
看我們要不要這個
這個也不要
這是他的路徑
拉長一點
我們看一下
是不是哎
這裏
其實我們最後其實可以提取它的年份
提取提取它的年份來
來作爲它的數據也可以是吧
也可以哎
現在我們就來先做這件事情啊
我們把這個年份給取下來
怎麼樣取這個年份呢
可以這樣來啊
可以這樣來
那麼提取的話
你看現在它有一個長度
就是提取的一個範圍啊
我們我們暫時用這種方式吧
起始搜索
也就是這個
這個數據的話
那麼從哪裏開始呢
從哪裏開始
我就可以提取到2013年 哎
這個這個數據
我們可以這樣啊
先回去先回來
來數一下
來數一下
怎麼樣數數這個長度
好像是一個比較麻煩的事情
是不是其實也不也也不麻煩
先把它列寬設置一下
好就這樣吧
我在下面我們輸一個LEN
這個函數可以來識別到
比方說現在我要一一百個199 98979695是吧
哎也就說從95開始啊
從95開始提
那麼好我們再回過頭來哎
好我們選擇這一列
之後我們來進行一個提取啊提取
然後呢範圍
從95開始
題題的題的那個長度
2013年 2014年那個年份是不是
那個年份的話就是四個字
我們來看一下對不對啊
S
是不是
年份也有
好這個問題就解決了
呃然後呢
它的長度爲5
好對吧
好完成
你看
這些我們想要的數據都都OK了
然後這邊你可以現在也處理一下
怎麼處理呢
就是這個什麼後勤部財務部這些點
x l s x這種
有擴展名
因爲等下彙總的時候
我不想要這個擴展名
不想要顯示出來
你也可以用我們的查找替換啊
因爲在這個裏面有查找替換
能夠把它去除出去嗎
啊能夠去除出去嗎
啊你說來看一下有沒有有替換值
那麼查找的值是什麼點兒
邊對不對
去除了我們想要的都給去啊
我們想要的都保留下了
並且處理好了
處理好之後接下來幹什麼
就是去打開每一個
就是相當於是擴展
去打開那麼怎麼去打開呢
哎
剛纔我們可以在通過這個開始選項卡
下面有個新建數據源
是不是跟我們外層看到的一樣
你可以直接去打開一個工作簿來看看
打開直接打開一個工作簿來看看
它的代碼是什麼比方說
我隨便點開一個財務部
然後隨便選擇一個表
12月
我們哎
這是我們之前做的處理
就是寫的什麼呀
Excel點兒Workbook
就是在Excel當中Workbook的方式
這種是一個函數
可以說是吧
那麼就相當於去獲取一個工作簿
的意思
相當於Excel當中的工作簿去獲取
這個裏面它指定了什麼呀
這個工作簿的
你獲取的這個工作簿的什麼呀
路徑是什麼
對不對那你說哎
剛纔那個
路徑也有啊
對我可以通過剛纔那個路徑來獲取
但是比較麻煩
那麼我們可以直接在這個路徑裏面寫
另外一個
就是寫他
因爲這個裏面已經有存在這個對象了
你何必再去獲取那個路徑呢
那麼長
好那麼怎麼樣來獲取
好現在我就增
啊就就這個吧
先保持然後呢
我們就輸入剛纔獲取的
就是Excel這個函數
是不是
Excel點兒叫Workbook
括號然後在這個裏面
請注意
這是英文狀態下輸入的符號以及字母
然後把這個什麼呀
第一個content
這個就是每一個工作簿
因爲之前我們點
是不是可以看到它有個工作簿在裏面
點到裏面去
點插入放在括號裏面
點擊確定
好大家可以看一下是不是
那麼這個的話
就相當於
你看現在是不是我獲取了它這個content
這個列下面的每一條記錄
這個記錄其實就是它的這個對象
你看雙擊打開
然
可以看到這個工作簿下面的
所有工作表了
是不是啊工作表
OK
然後這裏要值得注意的就是
剛纔我在寫的時候啊
要注意啊
大小寫很關鍵啊
其實我剛纔寫的是這
再加的是這一句是吧
加的是這一句
Excel點Workbook
Excel的e是大寫
Workbook的w是大寫
就是大寫
它是區分大小寫
大小寫很敏感的
那麼剛纔是這個樣子是吧
我添加了一列
在添加列這裏
添加了一列
那麼它就變成了這個狀態
然後接下來
我獲取了每個工作簿下面的工作表
所有的工作
表獲取了
獲取了之後我要把它擴展開
是不是所以你要擴展擴展
你要選擇哪些數
啊點擊來看一下
看一下是不是name要
data要因爲data是指的這個醫院
這個工作表下面的數據
然後後面這個item這個只要這些可以
不要就是隻要前面兩個都可以是吧
因爲這一列和這一列是重複的是吧
是一樣的
OK這個時候所以呢
你就先取消所有的選擇name
data這個兩列的數據
然後呢點擊確定
看到沒是不是就把你看後勤部
後勤部所有的數據
一一直到12月都給拿出來了
並且以表的形式來呈現開了
然後你想把每個工作表現在
是不是就更細化到了每個工
只是因爲表裏面只有兩類數據嘛
是吧姓名和工資這兩類數據都需要
所以呢點擊又把它怎麼樣
再次給擴展開了
擴展開之後
是不是我們把一些不需要的啊
不需要的給去掉就行了
其實這一點是不是不需要
但是呢我們在這邊添加列的時候
由於要使用它要說明
所以我們把它留下來
其實你在最早在刪除的時候
在這裏刪除列的時候就可以把它清除
都可以但是我們爲了啊
後面就是講解的時候好說
所以把它留下來啊
但是到現在來說
已經對我們來說沒有意義了
沒有意義的你把它刪除掉
然後呢
這邊是不是應該應該做下篩選
因爲每個工作每個表裏面
每個工作表裏面它都有表頭
那麼是不是又收回來了
現在你可
以看一下是不是我們要整理的數據
所有工作簿下面的所有工作表
應該叫做所有文件夾下面的所有
工作簿下面的所有
月份的所有的記錄
按照這種層次啊
全部弄好了
那這個關係的話你可以理一下
先是文件夾
文件夾下面的工作部
工作部下面的工作表
工作表下面的這些記錄
是不是按照這種層次
最後的話
你命命名一下
名字年份
然後這邊呢
部門這些部門
就是爲什麼剛纔我要做處理的原因啊
因爲我想把它作爲彙總
你如果你還把它擴展
你留着不行是吧
年份也是
爲什麼我要從路徑裏面提出它的
提取出它的年
回車然後這邊姓名
回車
然後這個呢
哎工資回車是不是
然後順便點一下哎
把它設置爲貨幣也可以是吧
因爲它是錢
那麼大家看一下有多少條記錄了
那麼這麼多工作表是不是啊
這麼多工作簿下面
工作表你看200多條
全部收攏在這裏面了
接下來最後的事情
你把這些數據進行整理出來之後
要做後續的分析彙總
是不是就簡單很多了
其實這節課非常好的體現了
關於power query
它的作用
就是把你想要的數據全部進行導入
清洗處理
是吧整理成非常
啊進行彙總
這些放在數據透視表
或者其他的其他的組件
因爲這個RQ的話
是屬於RBI裏面的組件之一
是吧
你放個其他的組件來進行後續的處理
也是可以
他就是先鋒
他把數據弄好之後扔給後面的人
是吧扔
扔給後面的工具
所以說他是至關重要的
然後呢我們關閉並上載至
是不是比如說現在OK
我自己來把它創建一個鏈接啊
僅鏈接
就是它在更新
他在更新
好就更新了
然後這邊哎
你可以在在這邊再次處理一下把
不需要的
剛纔這個只是做演示
我來看一下代碼
把它取消
然後這個的話
你
啊大家可以看到
這就是我們的這個數據
這些數據好
比方說現在我要彙總了
好彙總啊
我要看一下
每一年每個月我們的總工資
是不是啊
你可以不用這裏的數據
這這裏只是我以記錄的方式來呈現
在工作表裏面
你可以直接在我們前面講的數據獲取
外部數據現有連接這個地方
來選擇啊來選擇
哎是不是選擇這個表
就是剛纔這個鏈接
然後做成數據透視表
啊他
他相他複製了一個啊
他複製了一個
就是相當於把上面那個表
再複製這個表下來
重新來完成
一樣的兩份
放到行標籤
然後把工資放到值字段
我們就知道每一年每個月發了多少
是不是發了多少
嗯
好
下來一點
那麼是不是非常好
非常好那你說哎
我們現在來做一下測試
做一下測試
我如果要增加一個年份
比方說增加了一個2015年的這個文件夾
它是否會跟着更新呢
好我們來做一個測試
嗯
在這邊
好年份下面
我再增加一個2015年
你不一定是財務部啊
比方說其他的啊
就就財務部
行啊不搞不改
然後呢
好
在這個裏面
每個月的工
資是不是他有所變化啊
有所變化
比方說我修改一下1月份的工資是呃
1,0002,000
3,000 好
假如這樣
第二合起來是6,000啊
其他的位置的不變啊
我只是爲了做一下測試
然後呢
我們來看一下它會不會增加一個2015年
刷新2015年
那麼因爲我是複製的前面的
是不是然後大家可以看一下
這裏是不是有一個6,000啊
6,000 OK了
是不是
那麼是不是它會增加
它會增加
你打開圖
好現在再回過頭來看一下
刷新是不是又去掉了
你看多製冷
多製冷
那麼也就是說這個是一勞永逸的事情
剛纔你可能看到我做的好像步驟很多
其實不多
因爲我要給你講清楚
基本上它大致的意思
真正操作起來其實挺快速的
是吧啊
就算你覺得很複雜很麻煩
但是它是一個一勞永逸的事情
就是
你只要剛纔這幾步把它弄好了之後
你就不用管它了
它
每次你在數據源這邊修改數據的時候
那麼一刷新的時候
它就會用powerquery這個這些步驟
來重新來把我們的數據過一遍
一直過到這裏
步驟第一次的時候
按照這個你的思路
來處理一下這個數據
後續的數據的話
他會完全按照你這個思路來哎
這個步驟這個思路來處理數據
所以你看到最後呈現出來的數據
他爲什麼一下會會能夠正確的更新呢
原因就在在於這裏好吧
OK這是這節課
關於這一個叫做智能提取指定啊
智能提取彙總
只能智能提取指定啊
提取彙總
或者是指定文件
夾下面的工作簿的數據
它是不是一層一層的來摳
先對每個文件夾
文件夾下面的什麼呀
工作簿工作簿下面的什麼呀
工作表工作表下面的記錄爲止
然後呢你可以把這些整理好的記錄
爲我們所用
OK這節課我們就到這裏吧
同學們再見
