Excel跨文件做透視表或者函數，你一定要用[現有連接]，數據量越大越要使用這個功能！
大家好
這一章我們來講跨文件來進行計算和內容提取
這一章會要講到兩個例子
都會關係到大家可能沒有用到過的現有連接
其實這個功能特別的好用
特別是在第二個例子當中
如果你的數據量比較大
就可以直接使用到這個功能
去跨表格的去進行數據透視表的輸出
特別強大
首先大家可能經常會這樣去做
這邊我已經打開了兩個excel表格
可能你會在這一張表格當中
對另外一個文件當中的內容
去進行提取或者是計算
比如說我們來計算一下河北
客戶地址爲河北
在這一列當中中間會包含一些省份了
包括這邊會有浙江
包括會有山西
然後我們來做總利潤的求和
好
河北它應該是存在於這一串文本中間
所以我們可以先來選擇到求和區域
來自於這一列
然後條件區域應該來自於客戶這一列
最後條件就應該是當前條件
前面和後面分別來加上通配服務
然後給到確定
然後再來做一個雙擊加拉
就可以得出最後的結果
但是這種做法它會有一個問題
它是跨文件去進行的內容提取
所以當我們如果是把原始的數據源
把它給做了關閉之後
好
然後我們再來選擇
可能你直接再給到了一個回車的話
中間就會出現錯誤
它只有在我們同時開啓兩張表格的情況
下面它纔不會出現錯誤
好
接下來我們來看一下現有連接
應該如何去進行應用
特別簡單
首先可以來創建一張新的表格
選擇到現有連接
選擇到瀏覽更多
選擇剛纔是在新建文件夾當中
然後選擇到剛纔的數據源
選擇打開
然後可以來選擇它包含標題
然後選擇到確定
然後可以把這個表格
讓它出現在我們當前的表格當中
好
這樣的話就把剛纔那張表格
整個把它給添加過來了
好
接下來我們是不是可以來做剛纔重複的操作
對不對
所以你直接可以把這邊做一個清空
我們可以來複習一下這個函數
等於SAMUIFS函數
選擇對總利潤來做求和
然後條件來自於客戶這一列當中
條件在哪個位置
雙引號通配符
然後再來連接選擇到這個單元格
然後再來連接後面再來加上一個通配符
客戶回來
大家注意客戶有問題
確定
然後再來做雙擊下拉
這樣的話你看它就不會有任何問題了
如果下一次當這張表格當中會有內容更新的時候
它其實就是剛纔我們所看到的
在這個文件當中
可能你更新了數據
直接可以在這張表格當中來選擇右鍵刷新一次
那這個位置它就能夠得到更新
大家需要注意一點
就是說如果這個表格它移動了這個位置
就是說你把它給丟出去了的話
這張表格它依然存在
但是它不會再次做更新了
看完第一個案例之後
我們再來看第二個案例
這個需求我覺得應該會比較廣泛
舉個例子
這張表格它總共會有30多兆
這張表格總共會有70多萬行的數據
我們先可以把這張表格整個把它給打開
大家可以先參考一下
光是打開可能就需要耗費一些時間
現在我已經打開了這張表格
這張表格總共會有70多萬行
所以如果你的表格特別大的話
你是否會選擇在當前表格當中
選擇插入選擇數據透視表
這樣的話你的表格就會特別的卡頓
你可以看我是怎麼來做的
首先可以把這份數據去做一個關閉
然後選擇打開一個新的一個次要表格
然後可以來選擇到數據
選擇到現有連接
然後選擇到瀏覽更多
找到剛纔的2018年的文件
然後確定選擇僅創建連接
如果你選擇表格
就把剛纔的數據整個把它給添加進來了
好吧 所以我們可以來選擇到僅創建連接
或者你可以來選擇做一個數據透視表
把它給放在哪個位置
放在某一個單元格當中
然後再來選擇到確定
大家注意這個位置選擇的時候
需要特別注意 確定
好 數據透視表就已經有了
我們可以把這個任期放在行的位置
銷售額放在值的位置
把產品可以放在列的位置
然後可以來選擇到當前的數據透視表格
插入選擇切片器
選擇負責人 確定
好 然後你可以來選擇某一個負責人
就可以看到它所有的情況
你會發現當我們在操作的時候
它會特別的快捷
如果你就是在剛纔的那份文件當中去做操作
就會特別的卡頓
同時它還會有一個優點
就有可能你想要把這份文件
把它給傳輸給其他的人
其他的人他是可以打開這份文件的
但是他看不到數據源
所以這樣你也可以來起到一個
保護你的數據的這樣一個目的
好吧 我舉個例子
在我自己的電腦當中
你看剛纔我們是在這個文件夾當中來進行操作
所鏈接的是2018 對不對
我把這份文件可以先把它給剪切放在外面
好 我們再來刷新一下
當前的數據透視表格
它會提示你 它是沒有辦法去進行刷新的
好 但是你是可以對當前的內容去進行操作的
當在篩選的時候
它也可以根據我們篩選的變化而發生相應的變化
它只有在你自己電腦當中的時候
在這份文件夾當中
它會有之前的2018年的原始檔的時候
當你在更新
這邊的數據它才能夠達到同步的更新
所以別人拿到你這份文件
它是看不到數據源的
包括大家在做數據看滿的時候
如果你的數據量比較大
就可以使用到剛纔我那種方法
同時看滿可能你會發送給其他的人
你同時也不想讓別人看到你的基礎數據部分
你不想讓別人去做一些修改
你直接就可以用剛纔的方法去做現有連接
就可以達到我們最後的目的
整個這一章還是比較簡單也比較實用的
希望大家能夠有所收穫