帖子: 22
主題: 5
精華: 0
積分: 36
點名: 0
作業系統: XP
軟體版本: office 2003
閱讀權限: 10
註冊時間: 2011-2-15
最後登錄: 2014-6-28

1^# 跳轉到 » 正序看帖

打印

字體大小: tT

發表於 2013-6-5 10:13 | 只看該作者

[發問] 計算類別條件加總

各位大哥，小弟想從附件表格中按照有"總公司"字樣計算出所需金額，懇請大大指點。

Book2.rar (1.8 KB)

Book2.rar (1.8 KB)

樓主熱帖

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

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

24^#

發表於 2013-6-9 21:57 | 只看該作者

本帖最後由 ML089 於 2013-6-9 21:58 編輯

回復 23# home1913

想要深入了解，前面提的一些網頁資料可以細細品嘗，若還不了解這也是理所當然，畢竟這些都是比較高階的用法，先了解用法就好。
一般比較常用是 N(OFFSET())用法，你的表格中箱入數是文字格式，須採用T(OFFSET())用法，
若是文數字格式混用就須採用 INDEX(範圍, N(IF({1} )))或VLOOKUP(T(IF({1})))用法

採用T(OFFSET())用法，範列如下

=SUMPRODUCT((B2:B7="總公司")*(INT(C2:C7/T(OFFSET(F1,MATCH(A2:A7,E2:E8,),)))*10+MOD(C2:C7,--T(OFFSET(F1,MATCH(A2:A7,E2:E8,),)))*2))

複製代碼

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

TOP

home1913

小學生 Rank: 1

帖子: 22
主題: 5
精華: 0
積分: 36
點名: 0
作業系統: XP
軟體版本: office 2003
閱讀權限: 10
註冊時間: 2011-2-15
最後登錄: 2014-6-28

23^#

發表於 2013-6-7 09:52 | 只看該作者

感謝2位的幫忙~
=SUM((B2:B7="總公司")*(INT(C2:C7/VLOOKUP(T(IF({1},A2:A7)),E2:F8,2,))*10+MOD(C2:C7,VLOOKUP(T(IF({1},A2:A7)),E2:F8,2,))*2))
這個公式算出的是正確了~
但是不懂如何計算出的~

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猴年全勤獎章

22^#

發表於 2013-6-7 09:25 | 只看該作者

回復 21# Bodhidharma

那兩篇多維引用我也看過算經典，學陣列(大陸叫組數?還是數組?)必看之文章。
這題我自己也掉到陷阱裡，INDEX及VLOOKUP在多維引用上不容易，一般都是使用 N(OFFSET(...))、T(OFFSET(...))採降維的方式來處理。
這一題一開始在儲存格上測試VLOOUP式OK的，但在陣列公式(組數公式)時無法組成內存組數，我自己竟然沒有發現這個錯誤。
我自己習慣寫完陣列公式會將陣列值寫到對應儲存格，INDEX、VLOOKUP在這方面都會呈現是對的，但用F9去觀察公式的計算值又呈現不出內存組數，所以用F9來觀察比較正確。但F9有查看數量的限制及不易觀看的困擾。
如果要使用INDEX、VLOOKUP去組陣列就必須採用 INDEX(N(IF(...、VLOOKUP(N(IF(...的方式，這是PINY大師很重要的發現。
有興趣去可以看一下 http://club.excelhome.net/forum.php?mod=viewthread&tid=681243

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

TOP

Bodhidharma

中學生 Rank: 2

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

21^#

發表於 2013-6-7 00:32 | 只看該作者

回復 20# ML089

hmmm...似乎又是多維引用和N()、T()函數的問題
看來我得好好研究一下，到底excel是如何判斷什麼時候是陣列，什麼時候不是
不然這種問題的原理老是搞不懂……

http://club.excelhome.net/thread-98081-1-1.html
http://club.excelhome.net/forum.php?mod=viewthread&tid=145062

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猴年全勤獎章

20^#

發表於 2013-6-6 22:25 | 只看該作者

回復 19# home1913

=SUM((B2:B7="總公司")*(INT(C2:C7/VLOOKUP(T(IF({1},A2:A7)),E2:F8,2,))*10+MOD(C2:C7,VLOOKUP(T(IF({1},A2:A7)),E2:F8,2,))*2))

複製代碼

此式輸入方式不可以用ENTER輸入，需用三鍵(CTRL+SHIFT+ENTER)齊按方式輸入公式

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

TOP

home1913

小學生 Rank: 1

帖子: 22
主題: 5
精華: 0
積分: 36
點名: 0
作業系統: XP
軟體版本: office 2003
閱讀權限: 10
註冊時間: 2011-2-15
最後登錄: 2014-6-28

19^#

發表於 2013-6-6 17:45 | 只看該作者

本帖最後由 home1913 於 2013-6-6 17:46 編輯

目前用這3個公式出來的值都是80
={SUMPRODUCT((B2:B7="總公司")*(INT(C2:C7/VLOOKUP(A2:A7,E2:F8,2,))*10+MOD(C2:C7,VLOOKUP(A2:A7,E2:F8,2,))*2))}
={SUM(IF(B2:B7="總公司",INT(C2:C7/VLOOKUP(A2:A7,E2:F8,2,))*10+MOD(C2:C7,VLOOKUP(A2:A7,E2:F8,2,))*2))}
={SUMPRODUCT(ISNUMBER(FIND("總公司",$B$2:$B$7))*(INT($C$2:$C$7/VLOOKUP($A$2:$A$7,$E$2:$F$8,2,0))*10+MOD($C$2:$C$7,VLOOKUP($A$2:$A$7,$E$2:$F$8,2,0))*2))}

TOP

home1913

小學生 Rank: 1

帖子: 22
主題: 5
精華: 0
積分: 36
點名: 0
作業系統: XP
軟體版本: office 2003
閱讀權限: 10
註冊時間: 2011-2-15
最後登錄: 2014-6-28

18^#

發表於 2013-6-6 17:15 | 只看該作者

回復 16# ML089
如Hsieh的公式，只是我用手算的， Hsieh那樣才對

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 254
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-11-23

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章