Board logo

標題: [發問] 計算類別條件加總 [打印本頁]
作者: home1913    時間: 2013-6-5 10:13     標題: 計算類別條件加總

各位大哥，小弟想從附件 表格中 按照有"總公司"字樣計算出所需金額，懇請大大指點。[attach]15170[/attach]
作者: p212    時間: 2013-6-5 11:11

本帖最後由 p212 於 2013-6-5 11:12 編輯

回復 1# home1913
1.出貨量=SUMIF(B2:C7,"總公司",C2:C7)
2.再乘上貨量單價即可
請參考！
作者: home1913    時間: 2013-6-5 14:16

F列為箱入數，故貨號要對應到相同的箱入數做相除才有辦法乘上金額，目前卡在這邊
感謝您~
作者: p212    時間: 2013-6-5 15:04

本帖最後由 p212 於 2013-6-5 15:06 編輯

回復 3# home1913
1、請於D2儲存格輸入
=IF((C2/VLOOKUP(A2,$F$2:$G$8,2,0))<1,C2*2,(C2/VLOOKUP(A2,$F$2:$G$8,2,0))*10)
   續往下複製公式。
2、於您所要呈現結果的儲存格輸入
=SUMIF(B2:D7,"總公司",D2:D7)
   請參考！
作者: ML089    時間: 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)
複製代碼
前段為整數箱費用
後段為零散數費用
作者: ML089    時間: 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)
複製代碼
前段為整數箱費用
後段為零散數費用
作者: Bodhidharma    時間: 2013-6-5 17:42

回復 1# home1913

"總公司出貨金額為整箱10元不滿箱 1 PCS 2元；A1&B1不列入計算"
有點詭異…比方說貨號"120351755"箱入數為70
所以貨量70的話是10元，但是貨量69的話就變成138元？！
作者: home1913    時間: 2013-6-5 22:15

回復 7# Bodhidharma

是的~沒錯!
作者: home1913    時間: 2013-6-5 22:16

回復 6# ML089

請問 SUMPRODUCT有辦法寫在一起嗎?
作者: Bodhidharma    時間: 2013-6-5 23:22

回復 9# home1913

用sum+if? (陣列公式)
  1. =SUM(IF(B2:B7="總公司",INT(C2:C7/VLOOKUP(A2:A7,E2:F8,2,))*10+MOD(C2:C7,VLOOKUP(A2:A7,E2:F8,2,))*2))
複製代碼
作者: ML089    時間: 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))
作者: home1913    時間: 2013-6-6 10:24

回復 10# Bodhidharma
請問這在OFFICE2003是否可行，
因為公式帶入出來的值和記算的不同
作者: home1913    時間: 2013-6-6 10:27

回復 11# ML089
請問這在OFFICE2003是否可行，
因為公式帶入出來的值和記算的不同
作者: ML089    時間: 2013-6-6 11:25

回復 13# home1913

OFFICE2003可行

先測試1~2資料看看
作者: home1913    時間: 2013-6-6 13:39

回復 14# ML089

以下為使用計算及公式帶出的結果，計算出值為210公式算出為80
    [attach]15183[/attach]
作者: ML089    時間: 2013-6-6 15:17

回復 15# home1913

請給我G欄的計算公式，那些數字我看不出來如何計算的
作者: Hsieh    時間: 2013-6-6 15:34

回復 15# home1913


    輔助欄計算與你的差異
=INT(C2/VLOOKUP(A2,$E$2:$F$8,2,0))*10+MOD(C2,VLOOKUP(A2,$E$2:$F$8,2,0))*2
[attach]15184[/attach]
作者: home1913    時間: 2013-6-6 17:15

回復 16# ML089
  如Hsieh的公式，只是我用手算的， Hsieh那樣才對
作者: home1913    時間: 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))}
作者: ML089    時間: 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)齊按方式輸入公式
作者: Bodhidharma    時間: 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
作者: ML089    時間: 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
作者: home1913    時間: 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))
這個公式算出的是正確了~
但是不懂如何計算出的~
作者: ML089    時間: 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))
複製代碼




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