麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 隨意參照之運費計算 [打印本頁]

作者: kai6929 時間: 2012-11-5 11:39 標題: 隨意參照之運費計算

各位大師們好!
能否請教如何設定如:
E3:=香港
E4=臺北縣時
E6:會=區域費率的相對單價
[attach]13036[/attach]
[attach]13037[/attach]
[attach]13038[/attach]

作者: Hsieh 時間: 2012-11-5 12:12

回復 1# kai6929

=INDEX(區域費率!B:H,MATCH(E4,區域費率!B:B,0),MATCH(E3,區域費率!3:3,0))

作者: kai6929 時間: 2012-11-5 12:31

謝謝了這位大師,以解決

作者: c_c_lai 時間: 2012-11-5 12:41

回復 kai6929

=INDEX(區域費率!B:H,MATCH(E4,區域費率!B:B,0),MATCH(E3,區域費率!3:3,0))
Hsieh 發表於 2012-11-5 12:12

請問 Hsieh 版大，以上敘述之語法如果是使用 VBA ，那麼
不知該如何撰寫？謝謝您！

作者: freeffly 時間: 2012-11-5 13:12

回復 4# c_c_lai

"=INDEX(區域費率!C[-3]:C[3],MATCH(R[-2]C,區域費率!C[-3],0),MATCH(R[-3]C,區域費率!R[-3],0))"
是要這各嗎

作者: c_c_lai 時間: 2012-11-5 13:42

回復 5# freeffly
是的！
以 VBA 語法角度應如何撰寫？

作者: c_c_lai 時間: 2012-11-5 14:57

回復 5# freeffly
我試著寫了一個測試模組，如下：

Sub Test()
[D6] = Sheets("區域費率").[B3].Offset(WorksheetFunction.Match([C3], Sheets("區域費率").Range("B4:B28"), 0), _
WorksheetFunction.Match([D3], Sheets("區域費率").Range("C3:H3"), 0))
MsgBox Sheets("區域費率").[B3].Offset(WorksheetFunction.Match([C3], Sheets("區域費率").Range("B4:B28"), 0), _
WorksheetFunction.Match([D3], Sheets("區域費率").Range("C3:H3"), 0)).Address
End Sub

複製代碼

有沒有更佳的方式表達？

作者: JBY 時間: 2012-11-5 21:42

回復 1# kai6929

=INDEX(區域費率!B3:H100,MATCH(E4,區域費率!B3:B100,0),MATCH(E3,區域費率!B3:H3,0))

作者: JBY 時間: 2012-11-5 21:43

回復 1# kai6929

或者 :

=VLOOKUP(E4,區域費率!B3:H100,MATCH(E3,區域費率!B3:H3,0),0)

作者: c_c_lai 時間: 2012-11-5 21:49

回復 9# JBY
不好意思，我是指如果使用 VBA 程式語言，
有否更好的表達方式，謝謝！

作者: Hsieh 時間: 2012-11-5 23:04

回復 7# c_c_lai
利用Sheet1的事件程序

Private Sub Worksheet_Change(ByVal Target As Range)
With Me
If Intersect(Target, .[E3:E4]) Is Nothing Then Exit Sub
a = .[E3]
b = .[E4]
With Sheet2
c = .Range("B3").CurrentRegion.Find(a, lookat:=xlWhole).Column
r = .Range("B3").CurrentRegion.Find(b, lookat:=xlWhole).Row
MsgBox .Cells(r, c)
End With
End With
End Sub

複製代碼

作者: c_c_lai 時間: 2012-11-6 08:09

回復 11# Hsieh
非常感謝您！
經測試一切 OK！

作者: freeffly 時間: 2012-11-6 08:42

回復 1# kai6929

=SUMPRODUCT((目的地=E3)*(出發點=E4),區域費率!$C$4:$H$28)
這各可以嗎
可以得到答案

作者: c_c_lai 時間: 2012-11-6 09:18

回復 kai6929

=SUMPRODUCT((目的地=E3)*(出發點=E4),區域費率!$C$4H$28)
這各可以嗎
...
freeffly 發表於 2012-11-6 08:42

語法的可讀性蠻高的，到目前為止我還沒使用 SUMPRODUCT 的指令，
請問它的內涵為何、通常是應用在哪裡較適宜？
譬如在此處，您為何使用它來禪釋？

作者: freeffly 時間: 2012-11-6 13:34

回復 14# c_c_lai

請問它的內涵為何、通常是應用在哪裡較適宜？
內涵?這你就考倒我，我同常用它來搜尋多各條件就像樓主提供的格式，你如果在論壇上找也可找到很多使用這各函數的例子

譬如在此處，您為何使用它來禪釋？
禪釋?因為附件本身就有作定義名稱，所已函數用中文解釋→目的地=e3 且出發點=E4時,加總區域費率!$C$4:$H$28這區域裏面符合那兩各條件的資料。大致上應該是這樣，建議你去看看版主他們的解釋

作者: freeffly 時間: 2012-11-6 14:01

回復 14# c_c_lai
以下資料是從excelhome找到的希望對你有幫助

Excel多條件求和 &  SUMPRODUCT函數用法詳解

龍逸凡

日常工作中，我們經常要用到多條件求和，方法有多種，第一類：使用基本功能來實現。主要有：篩選、分類匯總、數據透視表、多條件求和向導；第二類：使用公式來實現方法。主要有：使用SUM函數編寫的數組公式、聯用SUMIF和輔助列（將多條件變爲單條件）、使用SUMPRODUCT函數、使用SUMIFS函數(限于Excel2007及以上的版本)，方法千差萬別、效果各有千秋。本人更喜歡用SUMPRODUCT函數。由于Excel幫助對SUMPRODUCT函數的解釋太簡短了，與SUMPRODUCT函數的作用相比實在不匹配，爲了更好地掌握該函數，特將其整理如下。

龍逸凡注：歡迎轉貼，但請注明作者及出處。

一、       基本用法

在給定的幾組數組中，將數組間對應的元素相乘，並返回乘積之和。

語法：

SUMPRODUCT(array1,array2,array3, ...)

Array1, array2, array3, ... 爲 2 到 30 個數組，其相應元素需要進行相乘並求和。

公式：=SUMPRODUCT(A2:B4, C2:D4)

      A       B       C       D
1       Array 1       Array 1       Array 2       Array 2
2       3       4       2       7
3       8       6       6       7
4       1       9       5       3


公式解釋：兩個數組的所有元素對應相乘，然後把乘積相加，即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3。計算結果爲156

二、       擴展用法

1、使用SUMPRODUCT進行多條件計數

語法：

＝SUMPRODUCT((條件1)*(條件2)*(條件3)* …(條件n))

作用：

統計同時滿足條件1、條件2到條件n的記錄的個數。

實例：

=SUMPRODUCT((A2:A10="男")*(B2:B10="中級職稱"))

公式解釋：

統計性別爲男性且職稱爲中級職稱的職工的人數

2、使用SUMPRODUCT進行多條件求和

語法：

＝SUMPRODUCT((條件1)*(條件2)* (條件3) *…(條件n)*某區域)

作用：

匯總同時滿足條件1、條件2到條件n的記錄指定區域的匯總金額。

實例：

=SUMPRODUCT((A2:A10="男")*(B2:B10="中級職稱")*C2:C10)

公式解釋：

統計性別爲男性且職稱爲中級職稱的職工的工資總和（假設C列爲工資）

三、       注意事項

1、數組參數必須具有相同的維數，否則，函數 SUMPRODUCT 將返回錯誤值 #VALUE!。

2、SUMPRODUCT函數將非數值型的數組元素作爲 0 處理。

3、在SUMPRODUCT中，2003及以下版本不支持整列（行）引用，必須指明範圍，不可在SUMPRODUCT函數使用A:A、B:B，Excel2007及以上版本可以整列（列）引用，但並不建議如此使用，公式計算速度慢。

4、SUMPRODUCT函數不支持“*”和“？”通配符

SUMPRODUCT函數不能象SUMIF、COUNTIF等函數一樣使用“*”和“？”等通配符，要實現此功能可以用變通的方法，如使用LEFT、RIGHT、ISNUMBER(FIND())或ISNUMBER(SEARCH())等函數來實現通配符的功能。如：

=SUMPRODUCT((A2:A10="男")*(B2:B10="中級職稱")*(LEFT(D2:D10,1)="龍")*C2:C10)

=SUMPRODUCT((A2:A10="男")*(B2:B10="中級職稱")*((ISNUMBER(FIND("龍逸凡",D2:D10)))*C2:C10))

注：以上公式假設D列爲職工姓名。ISNUMBER(FIND())、ISNUMBER(SEARCH())作用是實現“*”的通配功能，只是前者區分大小寫，後者不區分大小寫。

5、SUMPRODUCT函數多條件求和時使用“，”和“*”的區別：當擬求和的區域中無文本時兩者無區別，當有文本時，使用“*”時會出錯，返回錯誤值 #VALUE!，而使用“，”時SUMPRODUCT函數會將非數值型的數組元素作爲 0 處理，故不會報錯。也就是說：

公式1：=SUMPRODUCT((A2:A10="男")*(B2:B10="中級職稱")*C2:C10)

公式2：=SUMPRODUCT((A2:A10="男")*(B2:B10="中級職稱")，C2:C10)

當C2:C10中全爲數值時，兩者計算結果一樣，當C2:C10中有文本時公式1會返回錯誤值 #VALUE!，而公式2會返回忽略文本以後的結果。

四、       網友們的精彩實例

1、求指定區域的奇數列的數值之和

=SUMPRODUCT(MOD(COLUMN(A1:F1),2)*A1:F1)

2、求指定區域的偶數行的數值之和

=SUMPRODUCT(((MOD(ROW(A1:A22),2))-1)*A1:A22)*(-1)

3、求指定行中列號能被4整除的列的數值之和

=SUMPRODUCT((MOD(COLUMN(A1:P1),4)=0)*A1:P1)

4、.求某數值列前三名分數之和

＝SUMPRODUCT(LARGE(B1:B16,ROW(1:3)))

5、統計指定區域不重複記錄的個數

＝SUMPRODUCT(1/COUNTIF(V11:V15,V11:V15))

作者: c_c_lai 時間: 2012-11-6 14:34

回復 16# freeffly
誠摯地感謝您提供這麼寶貴的資訊，因為我按 F1 總是
沒能得到任何有用的資訊，有時還真懷疑 HELP 到底是
有沒有用。我會好好地去領會，再次謝謝您！

作者: kai6929 時間: 2012-11-6 14:37

感謝各位大師的協助,獲益良多

作者: freeffly 時間: 2012-11-6 14:58

回復 17# c_c_lai

F1沒有版主他們講解來的詳細跟厲害
所以多跟他們學習吧

作者: kuoliang123 時間: 2012-11-7 21:24

回復 16# freeffly

SUMPRODUCT 用法真多

作者: aer 時間: 2013-4-27 10:00

回復 kai6929

=INDEX(區域費率!B:H,MATCH(E4,區域費率!B:B,0),MATCH(E3,區域費率!3:3,0))
Hsieh 發表於 2012-11-5 12:12

請問一下版主當我套用此公式，只要目的國選擇"日本"，不論出發地是那一縣市，E6結果皆出現錯誤值 #REF!。如果把公式修改如下式，則結果正常
=INDEX(區域費率!B:H,MATCH(E4,區域費率!B:B,0),MATCH(E3,區域費率!3:3,0)-1)

請問這是什麼問題?為什麼會這樣呢?謝謝指導

作者: Hsieh 時間: 2013-4-27 21:52

回復 21# aer

=INDEX(區域費率!A:H,MATCH(E4,區域費率!B:B,0),MATCH(E3,區域費率!3:3,0))

複製代碼

範圍應該從A欄開始才對，多謝指正!

作者: Bodhidharma 時間: 2013-4-28 21:47

回復 c_c_lai
以下資料是從excelhome找到的希望對你有幫助

Excel多條件求和 & SUMPRODUCT函數用 ...
freeffly 發表於 2012-11-6 14:01

這篇寫的還滿不錯的！有一個地方稍微補充一下：

5、SUMPRODUCT函數多條件求和時使用“，”和“*”的區別：當擬求和的區域中無文本時兩者無區別，當有文本時，使用“*”時會出錯，返回錯誤值 #VALUE!，而使用“，”時SUMPRODUCT函數會將非數值型的數組元素作爲 0 處理，故不會報錯。也就是說：

公式1：=SUMPRODUCT((A2:A10="男")*(B2:B10="中級職稱")*C2:C10)

公式2：=SUMPRODUCT((A2:A10="男")*(B2:B10="中級職稱")，C2:C10)

這篇前面有提到，如果是用「,」，則非數值的部分會被視為是０
要注意的是，true或是false的邏輯值也會被視為是０
所以如果只有一個條件如

=SUMPRODUCT((A2:A10="男")*C2:C10)
=SUMPRODUCT(A2:A10="男",C2:C10)

複製代碼

前者會是正確的，但是後者會是0，因為A2:A10="男"回傳的是邏輯值而不是數字
(A2:A10="男")*(B2:B10="中級職稱")之所以會回傳正確的答案，是因為兩個邏輯值間用了一個「*」，所以自動轉換為數值
而上述的例子，如果是用「,」的話，也需要經過轉換，如：

=SUMPRODUCT(--(A2:A10="男"),C2:C10)
=SUMPRODUCT(1*(A2:A10="男"),C2:C10)

複製代碼

方會轉為數字，回傳正確的答案

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