Sumif / Sumifs 條件設定疑難

先來附圖
d.jpg
2019-2-4 11:53



如何設定
1. 按某一條件(C欄)
2. 指定日期(A欄)﹐但唔係整個月﹐而係某一日班某一日(eg. 3號至8號)
*實際應用﹐我會橫跨2個月的﹐即今個月14號至下個月13號。

把金額(B欄)符合上述2個要求加起總數?

基本上﹐我係唔知點做條件2(指定日期範圍)﹐點去打條式出來。

本帖最後由 clio 於 2019-2-4 16:38 編輯

依老夫所見, 施主只欠一個將A Column 分類的formula. 之後用pivot table 一屈... 其實已水到渠成.

首先, 在 Sheet2 建立以下. 注意 MM.DD Column 下的日期必需是 兩位的 MM 和 DD (不要被Excel當成普通數字!):

MM.DD        Grouping
01.01        Capricorn
01.20        Aquarius
02.19        Pisces
03.21        Aries
04.20        Taurus
05.21        Gemini
06.21        Cancer
07.23        Leo
08.23        Virgo
09.23        Libra
10.23        Scorpio
11.22        Sagittarius
12.22        Capricorn


回到你的...假設叫Sheet1!

D3 格...
=+VLOOKUP(TEXT(A3, "mm.dd"), Sheet2!A:B,2,TRUE)

核對結果正確, 就可複製到整個D Column (下稱"Grouping" Column)!


然後pivot table很簡單了...

指住有內容的任何一格, "Insert" -> "Pivot Table" -> "OK"

然後拉:
"Grouping" 去 ROWS;
"條件" 去 COLUMNS;
"金額" 去 VALUES


~完!~

[Edit] 完得太早...囧...

備註:
#1. MM.DD 是Grouping 的開始日, 這裏假設了你的Grouping 沒空檔期
#2. 01.01 和 12.22 雖然都是同一Grouping, 但為了要VLOOKUP可Handle這 "跨年" Grouping, 必需在頂頂有01.01 那行
#3. 這裏運(利)用了: "日期當String辦" 和 "VLookup(true) 的求其比行你" 特性

TOP

依老夫所見, 施主只欠一個將A Column 分類的formula. 之後用pivot table 一屈... 其實已水到渠成.

首先,  ...
clio 發表於 2019-2-4 16:19



    sorry﹐我睇完都唔明﹐但先多謝你出手。

EXCEL 是不是沒有直接辦法去計算 某一個range (例如 : 1月4日至1月13日)?


其實我個日期range係非固定的。
開始日及完結日﹐我係會自己在某一格手打的﹐每次要睇的日期範圍都唔同。


突然想到一個方法﹐可唔可以先將大過 "開始日" 全部加起來﹐再減去 "完結日" 之後的總數。
好似做到我想要的放果。


即係 sumifs (金額[加數範圍]﹐條件 [sumifs 條件1], 開始日 [sumifs條件2]) - sumifs (金額[加數範圍]﹐條件 [sumifs 條件1], 完結日 [sumifs條件2]


又好似做到我想要的效果﹐趁未來幾日我自己摸索一下。

TOP

You won't understand just by reading, it'll be more clear once you get it working on a spreadsheet.

Here, see if this helps:
https://drive.google.com/open?id ... NNGmESjLVsMlni_LoUk

TOP

回覆 1# peter_chan

咁樣?
條件A
=SUMIFS(B:B,C:C,"A",A:A,">=2019-1-3",A:A,"<=2019-1-8")

條件B
=SUMIFS(B:B,C:C,"B",A:A,">=2019-1-3",A:A,"<=2019-1-8")

條件C
=SUMIFS(B:B,C:C,"C",A:A,">=2019-1-3",A:A,"<=2019-1-8")

TOP

回覆 5# rocketgor


  我都覺得用sumifs 應該得

TOP

本帖最後由 peter_chan 於 2019-2-4 23:19 編輯
回覆  peter_chan

咁樣?
條件A
=SUMIFS(B:B,C:C,"A",A:A,">=2019-1-3",A:A,"=2019-1-3",A:A,"=2019-1-3", ...
rocketgor 發表於 2019-2-4 20:23





由於我這個file唔係用來office搵食用﹐只係自己做一些少少複雜的計算。
個file做得幾差﹐做多左多餘野都無所謂﹐反正自己睇得明﹐自己日後有需要時識修改就可以。

所以﹐我就用了上面#3提及的方法﹐加了2行做輔助計算。
第1行用sumifs 去做開始日打後 (大過及=) + 符合指定要求的 sumifs 總數
第2行用sumifs 去做完結日打後 (大過) + 符合指定要求的 sumifs 總數

第3行就2行相減﹐得出來就係指定期間 + 符合指定要求的總數。
其實我要這個數﹐再去做些少計算的。

做完驗證之後﹐就將2行輔助計算行 hide 左去﹐無眼屎乾淨。
(註: 有心機係可以將2行輔助計算行的公式﹐併埋做一條公式﹐就唔需要再2行做輔助計劃﹐做得靚仔D﹐打工搵食用就睇下 PRO 一些)


******************************************************************

另外﹐我試過在openoffice做話testing﹐發現有上少問題的。

日期的一行﹐在sumifs 的情況下﹐根據一個條件去做sum係可以的。

但係﹐同一行有2個條件去做sum﹐就會出現問題﹐結果會等於0的。
所以﹐你的做法 A行要check 2個條件﹐應該係唔work的。

TOP

本帖最後由 rocketgor 於 2019-2-5 13:03 編輯

你好,
襯未去拜年,我都試左一下Openoffice(第一次用)

Sumifs都整到,但公式同Excel係有分別既(逗號變了分號, 公式不能選整欄)

Image 1.png
2019-2-5 13:02

TOP