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

標題: 請問sumproduct如何不包含隱藏 [打印本頁]

作者: amu1129 時間: 2010-8-14 20:38 標題: 請問sumproduct如何不包含隱藏

請問我有很多天商品的資料
A欄日期 B欄商品 C欄數量 D欄價格
我先使用自動篩選選取某一日
會出現某日所有的商品、數量、價格
我使用sumproduct選取數量和價格
但是他會將隱藏的數字也列入計算
要如何只算顯示表格的乘積加總??
麻煩大大指導

作者: kimbal 時間: 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))
[attach]2425[/attach]
[attach]2426[/attach]

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

作者: amu1129 時間: 2010-8-14 23:08

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

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

作者: kimbal 時間: 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) 會變成實數,不是陣列

作者: amu1129 時間: 2010-8-15 11:55

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

作者: kimbal 時間: 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))

作者: amu1129 時間: 2010-8-15 23:58

回復 6# kimbal

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

作者: kimbal 時間: 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

作者: amu1129 時間: 2010-8-16 22:37

回復 8# kimbal

又學會一樣了謝謝版主!

作者: lumark1976 時間: 2010-12-21 00:57

好深奧哦!!我的功力太淺，我要在加強跟各位先進學習，看了一知半解，哈哈

作者: cck8x8 時間: 2010-12-21 14:51

好複雜 ~"~
看來我要脫離嫩咖的稱號還需要走很長的一段路...
受教了

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