請問sumproduct如何不包含隱藏

帖子: 472
主題: 5
精華: 0
積分: 485
點名: 0
作業系統: Windows
軟體版本: MS Office
閱讀權限: 100
性別: 男
來自: 香港
註冊時間: 2010-7-4
最後登錄: 2014-12-28

論壇貢獻勳章

2^#

發表於 2010-8-14 22:00 | 只看該作者

本帖最後由 kimbal 於 2010-8-14 22:02 編輯

回復 1# amu1129

sumproduct+subtotal
F11公式: 假設條件在 F8和F9
=SUMPRODUCT(--(B2:B6=F8),--(A2:A6=F9),SUBTOTAL(109,OFFSET(C2:C6,ROW(C2:C6)-ROW(C2),0,1)),(D2:D6))

t20100814sumproductsubtotal.zip (4.71 KB)

但是...還是建議多用樞紐表

懂得發問,答案就會在其中

今日の一秒は明日にない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

中學生 Rank: 2

帖子: 27
主題: 12
精華: 0
積分: 318
點名: 287
作業系統: Windows
軟體版本: 7
閱讀權限: 20
性別: 男
註冊時間: 2010-7-20
最後登錄: 2024-11-23

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章

3^#

發表於 2010-8-14 23:08 | 只看該作者

謝謝版主~版主好厲害
不過我對這函數還不是很懂
subtotal(109)是忽略隱藏的加總
而sumproduct的乘積應該是以最後兩個範圍算的吧
這樣不就變成C欄總和 x D欄?
還有小弟對offset的函數不是很懂
可以請版主稍微講解嗎

另外請問這個公式一定需要假設條件嗎
不能直接像sumproduct用滑鼠選取範圍即可嗎
煩請為小弟不才指點一下，感謝

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

帖子: 472
主題: 5
精華: 0
積分: 485
點名: 0
作業系統: Windows
軟體版本: MS Office
閱讀權限: 100
性別: 男
來自: 香港
註冊時間: 2010-7-4
最後登錄: 2014-12-28

論壇貢獻勳章

4^#

發表於 2010-8-15 11:12 | 只看該作者

本帖最後由 kimbal 於 2010-8-15 11:13 編輯

回復 3# amu1129

想分別欄位的話,這樣就可以了
c:
=SUMPRODUCT(--(B2:B6=F8),--(A2:A6=F9),SUBTOTAL(109,OFFSET(C2:C6,ROW(C2:C6)-ROW(C2),0,1)))
d:
=SUMPRODUCT(--(B2:B6=F8),--(A2:A6=F9),SUBTOTAL(109,OFFSET(D2:D6,ROW(D2:D6)-ROW(D2),0,1)))

這個要用上OFFSET了, 直接用SUBTOTAL(109,D2:D6) 會變成實數,不是陣列

懂得發問,答案就會在其中

今日の一秒は明日にない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

中學生 Rank: 2

帖子: 27
主題: 12
精華: 0
積分: 318
點名: 287
作業系統: Windows
軟體版本: 7
閱讀權限: 20
性別: 男
註冊時間: 2010-7-20
最後登錄: 2024-11-23

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章

5^#

發表於 2010-8-15 11:55 | 只看該作者

謝謝~我大致上了解用法了
只是還有一個地方不太懂
ROW(C2:C6)-ROW(C2)
為什麼要減ROW(2)啊?
但是我將減ROW(2)拿掉的話
數值又不對
可以麻煩版主告訴我這個動作是幹嘛用的嗎?

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

帖子: 472
主題: 5
精華: 0
積分: 485
點名: 0
作業系統: Windows
軟體版本: MS Office
閱讀權限: 100
性別: 男
來自: 香港
註冊時間: 2010-7-4
最後登錄: 2014-12-28

論壇貢獻勳章

6^#

發表於 2010-8-15 21:33 | 只看該作者

回復 5# amu1129

公式可以寫成這個樣子:
=SUMPRODUCT(--(B2:B6=F8),--(A2:A6=F9),SUBTOTAL(109,OFFSET(C2,ROW(C2:C6)-2,0)))

還原基本步, 前半步的理解嗎?
=SUMPRODUCT(--(B2:B6=F8),--(A2:A6=F9))

這句可以解釋為
--(B2=F8) * --(A2=F9)
+ --(B3=F8) * --(A3=F9)
+ ....
+ --(B6=F8) * --(A6=F9)

-- 用來把判斷條件換成1 / 0, 1就是B2=F8 的意思

加了 " ,SUBTOTAL(109,OFFSET(C2,ROW(C2:C6)-2,0)) " 後

--(B2=F8) * --(A2=F9) * SUBTOTAL(109,C2)
+--(B3=F8) * --(A3=F9) * SUBTOTAL(109,C3)
...
+--(B6=F8) * --(A6=F9) * SUBTOTAL(109,C6)

回上一步
--(B2=F8) * --(A2=F9) * SUBTOTAL(109,OFFSET(C2,0,0))
+--(B3=F8) * --(A3=F9) * SUBTOTAL(109,OFFSET(C2,1,0))
...
+--(B6=F8) * --(A6=F9) * SUBTOTAL(109,OFFSET(C2,4,0))

再回上一步
--(B2=F8) * --(A2=F9) * SUBTOTAL(109,OFFSET(C2,ROW(C2)-2,0))
+--(B3=F8) * --(A3=F9) * SUBTOTAL(109,OFFSET(C2,ROW(C3)-2,0))
...
+--(B6=F8) * --(A6=F9) * SUBTOTAL(109,OFFSET(C2,ROW(C6)-2,0))

懂得發問,答案就會在其中

今日の一秒は明日にない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

中學生 Rank: 2

帖子: 27
主題: 12
精華: 0
積分: 318
點名: 287
作業系統: Windows
軟體版本: 7
閱讀權限: 20
性別: 男
註冊時間: 2010-7-20
最後登錄: 2024-11-23

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章

7^#

發表於 2010-8-15 23:58 | 只看該作者

回復 6# kimbal

謝謝版主~
小弟大致上都懂了
但小弟資質愚鈍
對ROW(C2:C6)-2還是不懂耶
可以針對ROW(C2:C6)-2講解嗎
我知道ROW(C2:C6)是陣列
但-2這步實在不懂
麻煩請為小弟再指點一下，謝謝

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

帖子: 472
主題: 5
精華: 0
積分: 485
點名: 0
作業系統: Windows
軟體版本: MS Office
閱讀權限: 100
性別: 男
來自: 香港
註冊時間: 2010-7-4
最後登錄: 2014-12-28

論壇貢獻勳章

8^#

發表於 2010-8-16 22:22 | 只看該作者

回復 7# amu1129

ROW(C2:C6)-2
回傳出來的是 2-2,3-2,4-2,5-2,6-2 即 0,1,2,3,4
配合offset來用,
OFFSET(C2,0,0) 回傳 C2 自己
OFFSET(C2,1,0) 回傳 C3
OFFSET(C2,2,0) 回傳 C4
OFFSET(C2,3,0) 回傳 C5
OFFSET(C2,4,0) 回傳 C6

懂得發問,答案就會在其中

今日の一秒は明日にない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com