帖子: 13
主題: 4
精華: 0
積分: 18
點名: 0
作業系統: OSX10.8
軟體版本: excel2010
閱讀權限: 10
性別: 男
註冊時間: 2013-3-14
最後登錄: 2018-3-16

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 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")))}

複製代碼

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

樓主熱帖

hi

Bodhidharma

中學生 Rank: 2

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

2^#

發表於 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})))}

複製代碼

TOP

zz5151353

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 206
主題: 3
精華: 0
積分: 218
點名: 0
作業系統: WIN10
軟體版本: OFFICE 365
閱讀權限: 100
性別: 男
來自: 台灣台北
註冊時間: 2010-5-15
最後登錄: 2023-6-18

3^#

發表於 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,)))}

300 字節以內
不支持自定義 Discuz! 代碼

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

4^#

發表於 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三鍵齊按輸入方法)

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

01300607

小學生 Rank: 1

帖子: 13
主題: 4
精華: 0
積分: 18
點名: 0
作業系統: OSX10.8
軟體版本: excel2010
閱讀權限: 10
性別: 男
註冊時間: 2013-3-14
最後登錄: 2018-3-16

5^#

發表於 2013-5-25 15:08 | 只看該作者

回復 4# ML089

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

hi

TOP

01300607

小學生 Rank: 1

帖子: 13
主題: 4
精華: 0
積分: 18
點名: 0
作業系統: OSX10.8
軟體版本: excel2010
閱讀權限: 10
性別: 男
註冊時間: 2013-3-14
最後登錄: 2018-3-16

6^#

發表於 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欄資料加總
我試了一下好像無法加總符合條件但不同的欄位，謝謝大家

hi

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

7^#

發表於 2013-5-25 16:45 | 只看該作者

回復 5# 01300607

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

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

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2833
主題: 10
精華: 0
積分: 2889
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-12

8^#

發表於 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"),)))

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

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2833
主題: 10
精華: 0
積分: 2889
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-12

9^#

發表於 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 為儲存格〔ＩＶ１〕，一般為沒用到的〔閒置空格〕，當符合條件者不足四筆時，會以〔ＩＶ１〕為加總對像，以避免產生錯誤值！

TOP

01300607

小學生 Rank: 1

帖子: 13
主題: 4
精華: 0
積分: 18
點名: 0
作業系統: OSX10.8
軟體版本: excel2010
閱讀權限: 10
性別: 男
註冊時間: 2013-3-14
最後登錄: 2018-3-16

10^#

發表於 2013-5-25 23:53 | 只看該作者

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

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

hi

TOP