返回列表 上一主題 發帖

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

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
1# 跳轉到 »
發表於 2013-6-5 15:04 | 顯示全部帖子
  1. =SUMPRODUCT((B2:B7="總公司")*INT(C2:C7/VLOOKUP(A2:A7,E2:F8,2,))*10) + SUMPRODUCT((B2:B7="總公司")*MOD(C2:C7,VLOOKUP(A2:A7,E2:F8,2,))*2)
複製代碼
前段為整數箱費用
後段為零散數費用
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
2#
發表於 2013-6-5 15:05 | 顯示全部帖子
  1. =SUMPRODUCT((B2:B7="總公司")*INT(C2:C7/VLOOKUP(A2:A7,E2:F8,2,))*10) + SUMPRODUCT((B2:B7="總公司")*MOD(C2:C7,VLOOKUP(A2:A7,E2:F8,2,))*2)
複製代碼
前段為整數箱費用
後段為零散數費用
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
3#
發表於 2013-6-5 23:44 | 顯示全部帖子
回復 9# home1913


    =SUMPRODUCT((B2:B7="總公司")*(INT(C2:C7/VLOOKUP(A2:A7,E2:F8,2,))*10+MOD(C2:C7,VLOOKUP(A2:A7,E2:F8,2,))*2))
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
4#
發表於 2013-6-6 11:25 | 顯示全部帖子
回復 13# home1913

OFFICE2003可行

先測試1~2資料看看
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
5#
發表於 2013-6-6 15:17 | 顯示全部帖子
回復 15# home1913

請給我G欄的計算公式，那些數字我看不出來如何計算的
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
6#
發表於 2013-6-6 22:25 | 顯示全部帖子
回復 19# home1913
  1. =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

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
7#
發表於 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

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
8#
發表於 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())用法，範列如下
  1. =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

        靜思自在 : 站在半路，比走到目標更辛苦。
返回列表 上一主題