麻辣家族討論版版 - Powered by Discuz! Board

標題: 加總符合條件的近四筆資料 [打印本頁]

作者: 01300607 時間: 2013-5-24 19:37 標題: 加總符合條件的近四筆資料

我有一組資料，總共有A,B,C欄，我要想在A和B欄找出符合條件資料，然後加總C欄的資料
假設符合條件的資料有十筆，我想要加總近四筆資料，也就是列號前四高的那四筆資料，該怎麼做呢?
試了一些陣列方式都無法

={SUM(LARGE(IF((A2:A60='XX")*(B2:B60="KK"),ROW(C2:C60),""),INDIRECT("1:4")))}

複製代碼

上面是我初步構想，但是只能加總列號，無法再把列號轉成相對應儲存格進而加總
不知道有沒有大大有什麼好方法
謝謝

作者: Bodhidharma 時間: 2013-5-24 20:21

回復 1# 01300607

再加個index試試

={SUM(INDEX(C2:C60,LARGE(IF((A2:A60='XX")*(B2:B60="KK"),ROW(C2:C60),""),{1,2,3,4})))}

複製代碼

作者: zz5151353 時間: 2013-5-24 21:51

猜一猜

{=SUM(SUBTOTAL(9,OFFSET(C1,LARGE(IF((A2:A60="XX")*(B2:B60="KK"),ROW(2:60)),ROW(1:4))-1,)))}

作者: ML089 時間: 2013-5-25 14:55

本帖最後由 ML089 於 2013-5-25 14:57 編輯

回復 2# Bodhidharma

{}=SUM(INDEX(C2:C60,LARGE(IF((A2:A60="XX")*(B2:B60="KK"),ROW(C2:C60),""),{1,2,3,4})))

複製代碼

原式(上式)應該是不能執行的吧? INDEX不能這樣產生組數

一般用 N(OFFSET(...)) 的方式比較多

{}=SUM(N(OFFSET(C1,LARGE(IF((A2:A60="XX")*(B2:B60="KK"),ROW(C2:C60)),{1,2,3,4})-1,)))

複製代碼

如要用INDEX，可以參考 EXCELHOME PINY大師的 INDEX(...,N(IF({1},...)))

{}=SUM(INDEX(C:C,N(IF({1},LARGE(IF((A2:A60="XX")*(B2:B60="KK"),ROW(C2:C60)),{1,2,3,4})))))

複製代碼

{}=... 表示公式需用陣列輸入(CTRL+SHIFT+ENTER三鍵齊按輸入方法)

作者: 01300607 時間: 2013-5-25 15:08

回復 4# ML089

請問上述公式中的N是什麼意思？
謝謝

作者: 01300607 時間: 2013-5-25 16:12

猜一猜

{=SUM(SUBTOTAL(9,OFFSET(C1,LARGE(IF((A2:A60="XX")*(B2:B60="KK"),ROW(2:60)),ROW(1:4))-1,))) ...
zz5151353 發表於 2013-5-24 21:51

板主的方式我試了之後可行，謝謝板主還有各位板友的幫忙。

不好意思，小弟的問題又有進一步的延伸，不過我覺得可能要用VBA才可能辦到，不過還是想問問大家意見
如果我的一組資料，共有A,,B,C,D四個欄位，我想設定條件的欄位是A和B，一樣是想加總符合條件的近四筆資料
若A符合條件則取C欄，若B符合條件則取D，A跟B的條件是互斥的，不會同時發生，想把符合條件的近四筆CD欄資料加總
我試了一下好像無法加總符合條件但不同的欄位，謝謝大家

作者: ML089 時間: 2013-5-25 16:45

回復 5# 01300607

好像不容易講清楚 N/T 函數，
先看看這篇說明 http://club.excelhome.net/forum.php?mod=viewthread&tid=145062
我以前也查詢研究過，後來又忘記了也不知道怎麼說明，反正就是這幾招先學起來，原理再慢慢研究吧

有興趣自行再多查查相關資料。

作者: 准提部林 時間: 2013-5-25 19:53

計算準則：Ａ欄符合"C"，取Ｃ欄；Ｂ欄符合"X"，取Ｄ欄，符合這２項者，取最後四筆加總。
請將以下文字貼至Ａ１～Ａ２０，再以文字剖析為ＡＢＣＤ欄，
B,X,21,41
A,Y,22,42
C,Z,23,43
B,Y,24,44
B,Z,25,45
C,Z,26,46
A,X,27,47
B,Y,28,48
C,Z,29,49
A,Z,30,50
B,Y,31,51
A,Z,32,52
B,X,33,53
A,Z,34,54
B,Y,35,55
B,Y,36,56
C,Z,37,57
A,Y,38,58
B,Z,39,59
A,Y,40,60

公式．陣列輸入：
=SUM(N(INDIRECT(TEXT(LARGE(((A1:A20="C")+(B1:B20="X"))*ROW(1:20)*100+3+(B1:B20="X"),{1,2,3,4}),"[=3]!R1C256;!r00c00"),)))

試試是否適用，公式不太好說明，僅供參考！

作者: 准提部林 時間: 2013-5-25 20:02

公式解析：請以下公式貼入儲存格，雙按滑鼠左鍵進入編輯狀態，按Ｆ９查看各陣列值
=((A1:A20="C")+(B1:B20="X"))*ROW(1:20)*100+3+(B1:B20="X")
=LARGE(((A1:A20="C")+(B1:B20="X"))*ROW(1:20)*100+3+(B1:B20="X"),{1,2,3,4})
=TEXT(LARGE(((A1:A20="C")+(B1:B20="X"))*ROW(1:20)*100+3+(B1:B20="X"),{1,2,3,4}),"[=3]!R1C256;!r00c00")
=N(INDIRECT(TEXT(LARGE(((A1:A20="C")+(B1:B20="X"))*ROW(1:20)*100+3+(B1:B20="X"),{1,2,3,4}),"[=3]!R1C256;!r00c00"),))

R1C256 為儲存格〔ＩＶ１〕，一般為沒用到的〔閒置空格〕，當符合條件者不足四筆時，會以〔ＩＶ１〕為加總對像，以避免產生錯誤值！

作者: 01300607 時間: 2013-5-25 23:53

公式解析：請以下公式貼入儲存格，雙按滑鼠左鍵進入編輯狀態，按Ｆ９查看各陣列值
=((A1:A20="C")+(B1:B20 ...
准提部林發表於 2013-5-25 20:02

大哥的公式真是讓我大開眼界，不過我試了一下在第四個公式出現REF!錯誤
依我目前的功力要理解這個公式還要一段時間
不過還是謝謝大哥提供那麼好的解釋和解答

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)