贊助廠商

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

搜尋本部落格文章資料

網友問到:在 Excel 中有一個日期清單(如下圖左),如何依指定的月份計算加總(如下圖右)?

如下圖,由於想要計算的月份沒有規則,並且希望能使用下拉式清單來選取月份,直接得到小計結果,該如何處理?

Excel-利用下拉式清單計算多個月份的小計(SUMPRODUCT,VLOOKUP)


【公式設計與解析】

1. 建立下拉式清單

在儲存格G2:I7中先建立想要小計的月份,欄位有:月份名稱、起始月份、終止月份。

接著,選取儲存格D2,建立「資料驗證」,設定如下:

(1) 儲存格內允許:清單

(2) 來源:=$G$2:$G$7

Excel-利用下拉式清單計算多個月份的小計(SUMPRODUCT,VLOOKUP)


2. 設定公式

首先要設定儲存格名稱。選取儲存格A1:B26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、數值。

儲存格E2:=SUMPRODUCT((MONTH(日期)>=VLOOKUP(D2,G2:I7,2,FALSE))*
(MONTH(日期)<=VLOOKUP(D2,G2:I7,3,FALSE))*數值)

(1) VLOOKUP(D2,G2:I7,2,FALSE))

利用 VLOOKUP 函數求得儲存格D2(下拉式清單的選項)在儲存格G2:I7範圍中,查詢得到「起始月份」。

(2) VLOOKUP(D2,G2:I7,2,FALSE))

利用 VLOOKUP 函數求得儲存格D2(下拉式清單的選項)在儲存格G2:I7範圍中,查詢得到「終止月份」。

(3) MONTH(日期)>=VLOOKUP(D2,G2:I7,2,FALSE)

判斷日期陣列中每個日期的月份是否大於或等於起始月份,傳回 TRUE/FALSE 陣列。MONTH 函數可以傳回一個日期的月份。

(4) MONTH(日期)<=VLOOKUP(D2,G2:I7,3,FALSE)

判斷日期陣列中每個日期的月份是否小於或等於終止月份,傳回 TRUE/FALSE 陣列。

(5) SUMPRODUCT(第(3)式*第(4)式*數值)

SUMPRODUCT 函數中計算式的『*』,相當於執行邏輯 AND 運算,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。在 SUMPRODUCT 函數中執行二個條件和數值陣列的乘積和,結果即為所求。

如果如下:

Excel-利用下拉式清單計算多個月份的小計(SUMPRODUCT,VLOOKUP)

文章標籤
創作者介紹

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • 感謝Vincent老師(for《為重覆的單字編號》教學)
  • Vincent 老師您好:

    怕先前那篇的留言已經過了好幾天才回您看不到,
    這裡再致謝一次!

    真的是太感謝了,沒想到真的能獲得賜覆解答!!
    因為那天我很心急(而且又整理得很起勁~~),
    所以就自己靈機一動,用了很笨的方法,
    就是用資料篩選的方式,
    把最多重覆→次多→再次多…
    這樣一層一層地把重覆的項目剪貼到另一個工作表,
    最後再編號合併起來,就獲得同樣的效果了!

    不過這樣真的很笨很麻煩,
    而且如果資料量更龐大的話會容易出錯,
    excel果然有高明的解決方案,
    以後再整理類似資料都不用怕了,真的非常感謝老師!!
    老師您太偉大了!!>< ♥
找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼