年底, 各部門報過來的資料, 如何合併到一個檔裡?
過去, 我們只能使用VBA或編寫SQL語句。
現在, 我們只需點擊幾次滑鼠, 書寫一個公式。
6個工作簿, 資料結構都是一致的, 我們需要把她們合併到一個工作簿裡。
【資料>新建查詢>從檔>從資料夾】。
【流覽】, 找到需要合併的資料夾。
資料夾下每一個工作簿都被合併在一起。 首列“內容”顯示【Binary】, 是二進位資料的意思。
最後一列顯示這些工作簿的位址。 中間幾列分別表示工作簿名稱、尾碼名、存取時間、修改時間、創建時間和檔案屬性。
點擊【編輯】, 進入【查詢編輯器】, 中間那幾列無用,
如果此時直接點擊二進位首列的"展開按鈕", 會出現錯誤提示。
這是因為, 二進位資料無法直接提取。 我們需要書寫一條公式。
在【查詢編輯器】點擊【添加列>添加自訂列】。
在【添加自訂列】對話方塊,保留默認的【新列名】,在【自訂公式】清單方塊錄入公式:
=Exel.Workbook([Content],true)
注意,公式函數嚴格區分大小寫(首字母大寫)。
函數的第一個參數是需要轉換的二進位欄位,這個欄位可以在右側清單方塊按兩下選擇,不必手工錄入。
函數的第二個參數是邏輯值,如果原資料有標題行,這裡應該添寫true。
點擊【確定】後,【查詢編輯器】新增一列,資料類型顯示為【Table】,右側的【應用步驟】清單顯示了剛剛進行的步驟。
隨便選擇【Table】資料的一個儲存格,下方預覽區會顯示這個表的結構。
點擊新增列標籤右側“展開按鈕”,選擇【擴展】。
每一個【Table】表會按列方向展開。其中Data資料類型仍然顯示【Table】。
我們再次點擊【Table】資料列標籤右側的“展開按鈕”。
展開的資料已經將資料夾下所有工作簿合併在一起。
刪除一些不需要的列。
只留有效資料列,點擊【開始>關閉並上載】返回Excel。
所有資料都已經合併到一個工作簿中。
得到的合併資料實際上是一個【查詢】,按右鍵可以【刷新】資料。
當資料夾下原工作簿內容變更,合併工作簿只要【刷新】一次,即可更新資料。
展開【Table】資料時,如果選擇【聚合】,得到的資料會將同類項求和或計數。
怎麼樣,是不是比VBA要簡單的多啊。
在【查詢編輯器】點擊【添加列>添加自訂列】。
在【添加自訂列】對話方塊,保留默認的【新列名】,在【自訂公式】清單方塊錄入公式:
=Exel.Workbook([Content],true)
注意,公式函數嚴格區分大小寫(首字母大寫)。
函數的第一個參數是需要轉換的二進位欄位,這個欄位可以在右側清單方塊按兩下選擇,不必手工錄入。
函數的第二個參數是邏輯值,如果原資料有標題行,這裡應該添寫true。
點擊【確定】後,【查詢編輯器】新增一列,資料類型顯示為【Table】,右側的【應用步驟】清單顯示了剛剛進行的步驟。
隨便選擇【Table】資料的一個儲存格,下方預覽區會顯示這個表的結構。
點擊新增列標籤右側“展開按鈕”,選擇【擴展】。
每一個【Table】表會按列方向展開。其中Data資料類型仍然顯示【Table】。
我們再次點擊【Table】資料列標籤右側的“展開按鈕”。
展開的資料已經將資料夾下所有工作簿合併在一起。
刪除一些不需要的列。
只留有效資料列,點擊【開始>關閉並上載】返回Excel。
所有資料都已經合併到一個工作簿中。
得到的合併資料實際上是一個【查詢】,按右鍵可以【刷新】資料。
當資料夾下原工作簿內容變更,合併工作簿只要【刷新】一次,即可更新資料。
展開【Table】資料時,如果選擇【聚合】,得到的資料會將同類項求和或計數。
怎麼樣,是不是比VBA要簡單的多啊。