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

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

1^# 跳轉到 » 正序看帖

字體大小: tT

發表於 2010-8-14 20:38 | 只看該作者

請問sumproduct如何不包含隱藏

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

樓主熱帖

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

cck8x8

中學生 Rank: 2

帖子: 14
主題: 2
精華: 0
積分: 69
點名: 0
作業系統: XP
軟體版本: 2003
閱讀權限: 20
性別: 男
註冊時間: 2010-12-19
最後登錄: 2016-6-29

11^#

發表於 2010-12-21 14:51 | 只看該作者

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

☆～超嫩新手～☆
目前只會SUM,IF,COUNTIF..
~~努力學習中~~

TOP

lumark1976

中學生 Rank: 2

帖子: 25
主題: 2
精華: 0
積分: 56
點名: 0
作業系統: XP
軟體版本: 2002
閱讀權限: 20
性別: 男
註冊時間: 2010-12-18
最後登錄: 2022-12-6

10^#

發表於 2010-12-21 00:57 | 只看該作者

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

marklu

TOP

amu1129

中學生 Rank: 2

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

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

9^#

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

回復 8# kimbal

又學會一樣了謝謝版主!

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

TOP

kimbal

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 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

TOP

amu1129

中學生 Rank: 2

帖子: 27
主題: 12
精華: 0
積分: 318
點名: 286
作業系統: 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! 代碼

TOP

kimbal

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 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

TOP

amu1129

中學生 Rank: 2

帖子: 27
主題: 12
精華: 0
積分: 318
點名: 286
作業系統: 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! 代碼

TOP

kimbal

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 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

TOP

amu1129

中學生 Rank: 2

帖子: 27
主題: 12
精華: 0
積分: 318
點名: 286
作業系統: 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! 代碼

TOP