網友問到:在 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)

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(1) 人氣()