不同分頁的資料，如何根據特定條件(客戶)加總並回寫至總表中的報表呢？
今天分享SUMIF+INDIRECT的函數搭配，能蒐集到不同分頁不同月份，還有不同客戶別的金額總計。
前提是，每個月份的格式都要一致才能將資料回寫到總表喔。
函數類別：查閱與參照
[函數] =SUMIF(INDIRECT(B$3&"!A:A"),總表!$A4,INDIRECT(B$3&"!C:C"))
△ 題目：根據不同月份的分頁，分別帶出不同客戶別每月的金額總計。
步驟一：選取函數
*請參考：2019/04/13 Excel技巧-001_選取函數
步驟二：函數引數
● 先從SUMIF的函數開始操作
🡺 Range：選取分頁「1月」裡的A2:A18
🡺 Criteria：選取分頁「總表」裡的A4
🡺 Sum_range：選取分頁「1月」裡的C2:C18
註：SUMIF函數「2019/03/31 Excel函數003_sumif-數學與三角函數」
步驟三：加入INDIRECT函數
● 將Range和Sum_range加入INDIRECT函數
(概念：加入新函數，並且將範圍的數列數拿掉，只留下英文整列欄位)
🡺 原本函數「=SUMIF('1月'!A2:A18,總表!A4,'1月'!C2:C18)」
🡺 增加函數「=SUMIF(INDIRECT(B3&"!A:A"),總表!A4,INDIRECT(B3&"!C:C"))」
註：indirect函數「2019/12/18 Excel函數042_indirect-查閱與參照1/2」
步驟四：將欄位鎖定
● 加入上鎖符號「$」
(概念：鎖定欄位位置，在複製公式時，才不會走位，造成資料回寫錯誤)
🡺 原本函數「=SUMIF(INDIRECT(B3&"!A:A"),總表!A4,INDIRECT(B3&"!C:C"))」
🡺 增加上鎖「=SUMIF(INDIRECT(B$3&"!A:A"),總表!$A4,INDIRECT(B$3&"!C:C"))」
註：欄與列上鎖「2019/04/13 Excel技巧002_欄與列上鎖」
● 公式向下填充
🡺 Ctrl+C(複製)B4儲存格 >>Shift+向下鍵(反白經過的儲存格)至B6 >>Enter(貼上)
● 檢查確認：利用樞紐分析表來確認
🡺 在分頁「1月」插入一個樞紐分析表，確認與總表的金額是否相符。
註：樞紐分析表「2019/07/28 Excel活用005_樞紐分析表」
步驟五：將1~12月的欄位放入公式
● 公式向右填充
🡺 Ctrl+C(複製)B4:B6儲存格(1月) >>Shift+向右鍵(反白經過的儲存格)至M4:M6(12月) >>Enter(貼上)
● 檢查確認：利用樞紐分析表來確認金額是否正確
註：樞紐分析表「2019/07/28 Excel活用005_樞紐分析表」
完成：資料都正確，就完成啦。
KNB碎碎唸：
除了這個方法以外，還有資料合併彙算、樞紐多重彙總資料，或者還有其他種方式。
總之，只要得出來的結果是一樣的，不管用哪個方法，小本認為只要是不會花太多時間的方式都是好方法。
這裡提供Excel供各位下載練習使用「1-函數/Excel-044 indirect 2-2」
延伸閱讀：
1. 2019/04/13 Excel技巧-001_選取函數
2. 2019/03/31 Excel函數-003_sumif-數學與三角函數
3. 2019/12/18 Excel函數-042_indirect-查閱與參照1/2
4. 2019/04/13 Excel技巧-002_欄與列上鎖
5. 2019/07/28 Excel活用-005_樞紐分析表
6. 2019/12/29 Excel函數-045_hyperlink-查閱與參照
今天分享SUMIF+INDIRECT的函數搭配，能蒐集到不同分頁不同月份，還有不同客戶別的金額總計。
前提是，每個月份的格式都要一致才能將資料回寫到總表喔。
函數類別：查閱與參照
[函數] =SUMIF(INDIRECT(B$3&"!A:A"),總表!$A4,INDIRECT(B$3&"!C:C"))
△ 題目：根據不同月份的分頁，分別帶出不同客戶別每月的金額總計。
步驟一：選取函數
*請參考：2019/04/13 Excel技巧-001_選取函數
步驟二：函數引數
● 先從SUMIF的函數開始操作
🡺 Range：選取分頁「1月」裡的A2:A18
🡺 Criteria：選取分頁「總表」裡的A4
🡺 Sum_range：選取分頁「1月」裡的C2:C18
註：SUMIF函數「2019/03/31 Excel函數003_sumif-數學與三角函數」
步驟三：加入INDIRECT函數
● 將Range和Sum_range加入INDIRECT函數
(概念：加入新函數，並且將範圍的數列數拿掉，只留下英文整列欄位)
🡺 原本函數「=SUMIF('1月'!A2:A18,總表!A4,'1月'!C2:C18)」
🡺 增加函數「=SUMIF(INDIRECT(B3&"!A:A"),總表!A4,INDIRECT(B3&"!C:C"))」
註：indirect函數「2019/12/18 Excel函數042_indirect-查閱與參照1/2」
步驟四：將欄位鎖定
● 加入上鎖符號「$」
(概念：鎖定欄位位置，在複製公式時，才不會走位，造成資料回寫錯誤)
🡺 原本函數「=SUMIF(INDIRECT(B3&"!A:A"),總表!A4,INDIRECT(B3&"!C:C"))」
🡺 增加上鎖「=SUMIF(INDIRECT(B$3&"!A:A"),總表!$A4,INDIRECT(B$3&"!C:C"))」
註：欄與列上鎖「2019/04/13 Excel技巧002_欄與列上鎖」
● 公式向下填充
🡺 Ctrl+C(複製)B4儲存格 >>Shift+向下鍵(反白經過的儲存格)至B6 >>Enter(貼上)
● 檢查確認：利用樞紐分析表來確認
🡺 在分頁「1月」插入一個樞紐分析表，確認與總表的金額是否相符。
註：樞紐分析表「2019/07/28 Excel活用005_樞紐分析表」
步驟五：將1~12月的欄位放入公式
● 公式向右填充
🡺 Ctrl+C(複製)B4:B6儲存格(1月) >>Shift+向右鍵(反白經過的儲存格)至M4:M6(12月) >>Enter(貼上)
● 檢查確認：利用樞紐分析表來確認金額是否正確
註：樞紐分析表「2019/07/28 Excel活用005_樞紐分析表」
完成：資料都正確，就完成啦。
KNB碎碎唸：
除了這個方法以外，還有資料合併彙算、樞紐多重彙總資料，或者還有其他種方式。
總之，只要得出來的結果是一樣的，不管用哪個方法，小本認為只要是不會花太多時間的方式都是好方法。
這裡提供Excel供各位下載練習使用「1-函數/Excel-044 indirect 2-2」
延伸閱讀：
1. 2019/04/13 Excel技巧-001_選取函數
2. 2019/03/31 Excel函數-003_sumif-數學與三角函數
3. 2019/12/18 Excel函數-042_indirect-查閱與參照1/2
4. 2019/04/13 Excel技巧-002_欄與列上鎖
5. 2019/07/28 Excel活用-005_樞紐分析表
6. 2019/12/29 Excel函數-045_hyperlink-查閱與參照
留言
張貼留言