返回列表 上一主題 發帖

[發問] 多條件計算數值

[發問] 多條件計算數值

多條件計算數值
有三種條件,來計算符合條件的有幾個,如下圖
請問有什麼函數可以應用
問題.png
2018-7-18 20:53

回復 1# mark761222


    請問你要問的是不是C欄<14天,B欄=種類1且號碼不重複的總數?
B欄條件可能為種類2.3.4....等?
可是你圖片顯示結果是3?
1001<14天且種類1=1
1002>14=0
1003種類2=0
1004種類2=0
1005<14且種類1=1
我怎麼算結果都是2,還是...不是這樣算?
一分努力,一分收穫。
發問題前可以先搜索內文是否有相關範例。

TOP

回復 1# mark761222

參考看看
  1. =SUMPRODUCT(($C$2:$C$10<14)*($B$2:$B$10=1)/(COUNTIF($A$2:$A$10,$A$2:$A$10)))
複製代碼

TOP

SUMPRODUCT((MATCH(A2:A10,A2:A10,)=ROW(A2:A10)-1)*(B2:B10=1)*(C2:C10<14))
隨意窩 "EXCEL迷"  blog  或 http://blog.xuite.net/hcm19522/twblog
已收集5000篇 EXCEL函數

TOP

回復 2# faye59


    對! 是2 下面兩位大大的函數可以,感謝幫忙回答

TOP

回復 4# hcm19522


    SUMPRODUCT((MATCH(A2:A10,A2:A10,)=ROW(A2:A10)-1)*(B2:B10=1)*(C2:C10<14))

可以幫忙說明函數都用意嗎? 例如ROW(A2:A10)-1)  為啥要-1 取這個值可以做什麼?

TOP

回復 4# hcm19522



MATCH(A2:A10,A2:A10,)
範圍值對應範圍值的用意是為什麼?

TOP

回復 7# mark761222

先說結論好了,
以這條函數來反饋結果會有一點問題,
因為函數本身僅反應出首次出現編號條件是否符合,
若我做以下調整,整個計算就會產生錯誤。
Match_問題.GIF
2018-7-31 11:31


hcm19522大大函數的原理如下
1. MATCH(搜尋目標,搜尋範圍,搜尋方法種類) → 目標在範圍中的位置
因為號碼只能重複算一次,
所以使用Match將各項號碼在範圍中第一次出現的序數回饋出來,
如圖所示。
Match.GIF
2018-7-31 11:18


2. ROW(儲存格) → 儲存格列數
如同公式字面上的意思,
回傳儲存格的列數,
例如 ROW(C21) → 21

3. MATCH(A2:A10,A2:A10,)=ROW(A2:A10)-1)
分成兩個部分解釋,
首先是「=」的部分,
為了僅讓首次出現的號碼生效 (為了達成每個號碼只能重複算一次),
故設下條件「若Match的次序與行列相同才算有效」。
但因為實際的範圍是「A2:A10」,
意即順序第一個列數是2、順序第二個列數是3、順序第三個列數是4....的差別,
所以才補上「-1」來調整這個差別。
BTW,若以我示範圖中的範圍,則需要改成「-15」,因為第一順位的列數是16。

TOP

回復 7# mark761222
  1. = SUMPRODUCT((MATCH(A2:A10,A2:A10,)=ROW(A2:A10)-1)*(COUNTIFS($A$2:$A$10,$A$2:$A$10,$B$2:$B$10,1,$C$2:$C$10,"<14")))
複製代碼
感謝以上兩位大大的分享,
我才有辦法推導出這個函式。
主要分成兩個部分,
第一個部分我在上一篇回文有解說過,
  1. = MATCH(A2:A10,A2:A10,)=ROW(A2:A10)-1)
複製代碼
總結就是反饋該範圍「是否為首次出現的編號」的陣列。
如果將該函式分配至儲存格中,效果如下。
Match_01.GIF
2018-7-31 13:11
  1. =COUNTIFS($A$2:$A$10,$A$2:$A$10,$B$2:$B$10,1,$C$2:$C$10,"<14")
複製代碼
而此函數在計算在範圍內同時滿足相同編碼、種類為「1」且天數未滿14天三個條件的個數。
也是一樣回傳該範圍計算結果的陣列,
如果將該函式分配至儲存格中,效果如下。
Match_02.GIF
2018-7-31 13:13


所以綜合以上兩點,
即可得到該編號在範圍中符合條件的個數,
最後再用SUMPRODUCT將陣列中的結果相加。

TOP

回復 9# a5007185
大大:照著你的方式做了一遍,試著了解,但我出現的好像跟你不一樣

fefesfs.jpg
2018-8-4 22:14

TOP

        靜思自在 : 每天無所事事,是人生的消費者,積極、有用才是人生的創造者。
返回列表 上一主題