贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

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

創作者介紹

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • hi
  • 請問你,
    名字 組別
    Alice A組 B組
    Peter C 組 A組
    Jay B 組 C組
    Susan A組
    John D組
    ---------------------
    如果我想做一個清單
    能一選 A組,就顯示只屬於A組人員的清單嗎? 謝謝


  • 請參考:http://isvincent.pixnet.net/blog/post/46691317

    vincent 於 2016/09/27 16:46 回覆

找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼