錦子老師

周鑫鑫：錦子老師您好，我要將代銷公司的代銷金額計算佣金，依下列規則處理：

1000000(含)：佣金費率千分之7。

1000001-10000000(含)：佣金費率千分之4。

10000001-50000000(含)：佣金費率千分之1.2。

50000001-100000000(含)：佣金費率千分之0.8。

100000001-1000000000(含)上：佣金費率千分之0.15。

1000000001以上：佣金費率千分之0.1。

不知要如何撰寫公式才可以。謝謝！

錦子老師：這個問題，解決方法如下：

步驟1：點取B11儲存格輸入公式：

=ROUND(MAX(SUMPRODUCT(MMULT({-1,1},SUBTOTAL(9,OFFSET($C$1,{-1;0}+{1,2,3,4, 5,6},)))*TEXT(B10%%%-{0,1,10,50,100,1000},"[<]!0"))*1000,2000),2)

再按CTRL+SHIFT+ENTER鍵完成陣列公式輸入。

【公式說明】

=ROUND(MAX(SUMPRODUCT(MMULT({-1,1},SUBTOTAL(9,OFFSET($C$1,{-1;0}+{1,2,3,4, 5,6},)))*TEXT(B10%%%-{0,1,10,50,100,1000},"[<]!0"))*1000,2000),2)

公式1：TEXT(B10%%%-{0,1,10,50,100,1000},"[<]!0")

將B10儲存格內數值除以100萬，依序減0,1,10,50,100,1000，設定格式為數值若大於0保持原值，若小於0則強制為0。

=TEXT(數值,"正;負;零;文字")

公式2：OFFSET($C$1,{-1;0}+{1,2,3,4, 5,6},)

從C1儲存格移動N({-1;0}+{1,2,3,4, 5,6}運算結果)列。

公式3：SUBTOTAL(9,公式23)

計算公式2運算結果的總和。

公式4：MMULT({-1,1},公式3)

將公式3運算結果與{-1,1}相乘積。

公式5：SUMPRODUCT(公式4*公式1)

計算公式4運算結果與公式3運算結果相乘總和。

公式6：MAX(公式5*1000,2000)

傳回公式5運算結果乘1000與2000的最大值。

公式7：ROUND(公式6,2)

將公式6運算結果將四捨五入到小數位數第2位。

以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

更多相關影片教學：請點我

更多相關文章：請點我