瞥一眼文化
Excel數據彙總中有這樣一個問題:在很多個工作表中,同一項數據都位於同一個單元格,比如:每個月份的銷量都位於每個月份工作表的B1單元格,而我們需要把每個月的銷量彙總到一個總表中,在該總表中,各個月的銷量分佈為同一列。
這種彙總情況如下動圖:
以上示例中,每個分工作表的命名是有規律的:從1到12月。但這種有規律的情況,只是個例,而普遍存在的是:每個分工作表的名稱是無規律可尋的。
比如,以下動圖,各個分表名稱是超市名,而超市名是沒有1——12等數字規律的:
上述兩種情況,需要不同的彙總方法。
韓老師一一講述:
分表名稱有規律
公式實現
在B2單元格輸入公式:
=IFERROR(INDIRECT(ROW(A1)&"月"&"!B1"),"")
公式向下填充,即得所有工作表B1單元格的數據。
公式解析
ROW(A1)&"月":
公式在B2單元格時,ROW(A1)返回1,即得工作表名稱1月,公式向下填充到B3單元格時,該部分變為ROW(A2),即得工作表名稱2月,再向下填充,得到其它月份工作表名稱。
ROW(A1)&"月"&"!B1":
連接工作表名稱與單元格,得到:1月B1,2月B1,……12月B1.
INDIRECT(ROW(A1)&"月"&"!B1"):
引用1月B1,2月B1,……12月B1的值。
IFERROR(INDIRECT(ROW(A1)&"月"&"!B1"),""):
如果引用有結果,返回正確值,否則返回空值。
分表名稱無規律
1、鼠標放在第一個超市名稱的單元格A2,【公式】——【定義名稱】:輸入名稱BM(此名稱可任意取),引用位置處輸入公式:
=INDEX(GET.WORKBOOK(1),ROW(A2))
GET.WORKBOOK(1)是宏表函數,取所有工作表的名稱。
2、在A2單元格輸入公式:
=IFERROR(BM,"")
向下填充,得到所有超市名稱:
IFERROR函數是容錯處理,如果沒有超市名稱,返回空值。
3、在B2單元格輸入公式:
=IFERROR(INDIRECT(A2&"!B1"),"")
公式向下填充,即得所有超市工作表B1單元格的數值:
4、如果不喜歡上圖中帶工作簿名稱的超市名,可以把公式改為:
=IFERROR(MID(BM,13,9),"")
因為工作簿名稱有12個字節,所有用公式MID(BM,13,9),從第13個字節開始提取超市名稱。其中9是隨意取的長度,根據超市名稱字符數的多少,該數值可靈活改變。
※特別注意:
工作表名稱無規律的情況,因為引用了宏表函數,所以文件保存時要保存成“啟用宏的工作簿.xlsm”。
韓老師講office
顯然使用工具效率最高,如果懂一些vba,開可以高度定製,要怎麼彙總都行
用我製作的Excel880全能合併工具,免費開源,9大自定義功能,自由填寫參數後可滿足90%的表格合併場景,已經2萬人下載使用! 詳見如下 獲取本工具只需私信回覆36793即可獲得
合併過程動畫如下
主要功能如下
單文件裡多個sheet可合併
單文件裡指定的幾個sheet名進行合併
同目錄下多個文件合併
多個文件裡指定的幾個或者一個sheet名進行合併
指定待合併表的表頭行數
指定是否按原格式(複製粘貼方式可帶格式),選擇否則只合並數據不帶格式
可選擇是否備註每行數據來源表名
帶合併進度條顯示
VBA代碼全部開源,可自有修改代碼以適合個性合併
主要代碼如下
本工具只需關注後私信回覆36793即可獲得,附使用說明視頻講
http://toutiao.com/item/6395918373531681281/