2016年9月26日 星期一

Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)

網友問到:在 Excel 中如下圖的資料表中,如何在挑選日期後自動計算每日的數量總和?
參考下圖,當指定一個項目時,希望能計算各日的總和;當指定一個日期時,希望能計算所有項目的總和,該如何處理?
Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)

【公式設計與解析】
1. 為儲存格範圍定義名稱
選取儲存格A2:J11,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,一次定義每個項目的儲存格範圍。
Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)
結果如下:
Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)
2. 設計下拉式清單
利用「資料驗證」功能來設計下拉式清單功能。
Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)
(1) 在儲存格L3的資料驗證準則中設定:
儲存格內允許:清單;來源:=$A$2:$A$11
(2) 在儲存格L5的資料驗證準則中設定:
儲存格內允許:清單;來源:=$B$1:$J$1
Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)
3. 輸入公式
(1) 儲存格L2:=SUM(INDIRECT(L2))
INDIRECT(L2):將儲存格L2的內容(本例:己)利用 INDIRECT 函數轉換為位址。因先前已定義各個項目的儲存格範圍,本例結果為:儲存格B7:J7。
最後透過 SUM 函數將儲存格範圍予以加總。
(2)儲存格L5:=SUM(OFFSET(A1,1,MATCH(L4,B1:J1,0),10,1))
MATCH(L4,B1:J1,0):找尋儲存格L4的內容在儲存格B1:J1中的位置(本例傳回『6』)。
OFFSET(A1,1,MATCH(L4,B1:J1,0),10,1):利用 OFFSET 函數找出儲存格L4所屬的儲存格範圍(本例為:儲存格G2:G11)。
最後透過 SUM 函數將儲存格範圍予以加總。

【補充資料】
如何讓資料表中的欄和列儲對照存格L5和儲存格L2的內容以不同色彩文字顯示?
1. 選取儲存格A2:J11,進入設定格式化的條件編輯規則:
規則:=$A2=$L$2 (注意位址的相對參照和絶對參照)
格式:紅色文字。
Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)
2. 選取儲存格A2:J11,進入設定格式化的條件編輯規則:
規則:=B$1=$L$4 (注意位址的相對參照和絶對參照)
格式:紅色文字。
Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)

沒有留言:

張貼留言

檢視其他文章

好康東東