學不完．教不停．用不盡

網友問到：在 Excel 中如下圖的資料表中，如何在挑選日期後自動計算每日的數量總和？

參考下圖，當指定一個項目時，希望能計算各日的總和；當指定一個日期時，希望能計算所有項目的總和，該如何處理？

【公式設計與解析】

1. 為儲存格範圍定義名稱

選取儲存格A2:J11，按 Ctrl+Shift+F3 鍵，勾選「最左欄」，一次定義每個項目的儲存格範圍。

結果如下：

2. 設計下拉式清單

利用「資料驗證」功能來設計下拉式清單功能。

(1) 在儲存格L3的資料驗證準則中設定：

儲存格內允許：清單；來源：=$A$2:$A$11

(2) 在儲存格L5的資料驗證準則中設定：

儲存格內允許：清單；來源：=$B$1:$J$1

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 (注意位址的相對參照和絶對參照)

格式：紅色文字。

2. 選取儲存格A2:J11，進入設定格式化的條件編輯規則：

規則：=B$1=$L$4 (注意位址的相對參照和絶對參照)

格式：紅色文字。