請問sumproduct如何不包含隱藏

amu1129

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

kimbal

回復 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))

下載 (82.34 KB)

2010-8-14 21:59

t20100814sumproductsubtotal.zip (4.71 KB)

下載次數: 15

2010-8-14 22:00

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

amu1129

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

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

kimbal

回復 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

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

kimbal

回復 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

回復 6# kimbal

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

kimbal

回復 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

回復 8# kimbal

又學會一樣了 謝謝版主!

lumark1976

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