返回列表 上一主題 發帖

[發問] 隨意參照之運費計算

[發問] 隨意參照之運費計算

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


KAI~22.rar (8.82 KB)

回復  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)

這篇前面有提到,如果是用「,」,則非數值的部分會被視為是0
要注意的是,true或是false的邏輯值也會被視為是0
所以如果只有一個條件如
  1. =SUMPRODUCT((A2:A10="男")*C2:C10)
  2. =SUMPRODUCT(A2:A10="男",C2:C10)
複製代碼
前者會是正確的,但是後者會是0,因為A2:A10="男"回傳的是邏輯值而不是數字
(A2:A10="男")*(B2:B10="中級職稱")之所以會回傳正確的答案,是因為兩個邏輯值間用了一個「*」,所以自動轉換為數值
而上述的例子,如果是用「,」的話,也需要經過轉換,如:
  1. =SUMPRODUCT(--(A2:A10="男"),C2:C10)
  2. =SUMPRODUCT(1*(A2:A10="男"),C2:C10)
複製代碼
方會轉為數字,回傳正確的答案

TOP

回復 21# aer
  1. =INDEX(區域費率!A:H,MATCH(E4,區域費率!B:B,0),MATCH(E3,區域費率!3:3,0))
複製代碼
範圍應該從A欄開始才對,多謝指正!
學海無涯_不恥下問

TOP

回復  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)

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

TOP

回復 16# freeffly


    SUMPRODUCT 用法真多
kuoliang

TOP

回復 17# c_c_lai


    F1沒有版主他們講解來的詳細跟厲害
   所以多跟他們學習吧
字典兩各字 還真難理解

TOP

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

TOP

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

TOP

回復 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))
字典兩各字 還真難理解

TOP

回復 14# c_c_lai


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


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

TOP

        靜思自在 : 有願放在心裡,沒有身體力行,正如耕田不播種,皆是空過因緣。
返回列表 上一主題