EXCEL表格中跨表資料獲取問題解決方法
不知道你有沒有遇到這樣的問題:從一大堆表格裡面採集某一特定的資料做到一個表格。
如果你只停留在把表格一個個打開,CTRL+C然後CTRL+V那就太浪費時間了,如果遇到不用多100左右的表格你就會崩潰的不要不要的。
如下面示例,採集各個表格中的公司名稱,匯總到一個表格。
小編今天來分享這類問題的正確處理方法,
第一步:備份來源資料表格(後面涉及修改表格名稱,有備無患),備份後把需要採集資料的表格放在一起,建議新建一個資料夾,
第二步:選中資料表格,全部選中後然後滑鼠右鍵重命名,隨便輸入1就好,你會發現所有表格都重命名為1(1),1(2),1(3)這樣的名字。
然後在匯總的表格裡面寫入下列函數然後下拉。此步的作用就是構造函數公式。
A1="="&"INDEX('[1 ("&ROW()&").xlsx]Sheet1'!$A$1:$I$12,SUMPRODUCT(('[1 ("&ROW()&").xlsx]Sheet1'!$A$1:$I$12="&"""所屬單位"""&")*ROW('[1 ("&ROW()&").xlsx]Sheet1'!$A$1:$I$12)),SUMPRODUCT(('[1 ("&ROW()&").xlsx]Sheet1'!$A$1:$I$12="&"""所屬單位"""&")*COLUMN('[1 ("&ROW()&").xlsx]Sheet1'!$A$1:$I$12))+1)"
第三步:複製完成的函數到資料夾新建的txt記事本文檔。如下圖
第四步:複製txt文檔到匯總的表格,粘貼選擇只粘貼文本。然後會出現下圖,至此三個表格中的公司名稱全部抓取過來,並沒有一個個打開。很簡單實用。
最後的檢驗結果如下圖
函數原理。
SUMPRODUCT與ROW和COLUMN函數來抓取各文檔中“所屬單位”的儲存格位置資訊,因“所屬單位”的儲存格與“公司名稱”儲存格行數相同,列數加上1而得到公司名稱儲存格位置資訊,在通過INDEX函數,結合位置資訊抓取公司名稱儲存格的值。
注意:小編這個公式基本是萬能公式,針對來源資料各個儲存格可能不同,位置資訊不同的情況。如果表格都是一樣的位置,就不用通過“所屬單位”去確認,直接引用各儲存格位置資訊就好。
如果你現在還沒有明白原理,不要緊。收藏本文,遇到直接粘貼我的公式,按照步驟,修改公式中的“所屬單位”資訊就可以達到相同的效果