Excel函數多重條件

2012-09-13 7:13 am
我想在Excel輸入一個公式,但我搞不定,請各位達人幫忙了!問題如下:

A1代表原始單價;B1代表採購數量;C1代表底下各種條件換算出來的數值,函數就是要設在C1裡。

C1敘述:
採購量(B1)若小於6000,大於等於4000,則原始單價(A1)乘以2倍;採購量(B1)若小於4000,大於等於2000,則原始單價(A1)乘以3倍;採購量(B1)若小於2000,且款式只有1~3種,則原始單價(A1)乘以5倍;採購量(B1)若小於2000,且款式有4~8種,則原始單價(A1)乘以4倍。

請問這個函數該怎麼下呢?

感謝大大的解答了,謝謝。
更新1:

忘了說還要一個欄位是“款式”設於D1!謝謝囉。

更新2:

Daniel大師, 剛剛試過了,是可以的!可是我想跟你請教一下,可以請你幫我解說一下你的函數寫法嗎?願意的話,請你詳細一點的解說,因為我笨笨的,呵呵!當然,若不方便在這裡告訴我,請你mail給我,謝謝囉。

更新3:

To:Daniel大師, 謝謝您的指點,但如果我這個地方要改為: 採購量(B1)若小於2000,且款式只有1~3種,則原始單價(A1)乘以2.5倍;採購量(B1)若小於2000,且款式有4~8種,則原始單價(A1)乘以3倍。 那我要怎麼修改您的公式呢? 麻煩您再指點了,謝謝。

更新4:

是這樣嗎? 4-SUM((B2%/20>={1,2,3})*0.5)

更新5:

Daniel 大師, 對不起,別理會我上面的補充,以這次的為主!相關條件若修改為下: C1敘述: 採購量(B1)若小於6000,大於等於4000,則原始單價(A1)乘以1.4倍;採購量(B1)若小於4000,大於等於2000,則原始單價(A1)乘以2倍;採購量(B1)若小於2000,且款式只有1~3種,則原始單價(A1)乘以2.5倍;採購量(B1)若小於2000,且款式有4~8種,則原始單價(A1)乘以3倍。 公式怎修改呢? 因為有些倍數會加以修改調整,所以可以的話,您可以幫忙寫成倍數可以修改的公式嗎? 謝謝您了。

回答 (6)

2012-09-13 7:27 pm
✔ 最佳答案
假設款式在D欄,則C2公式:
=A2*IF(B2%<20,4+(D2<4),4-SUM((B2%/20>={1,2,3})*1))

試試看!

2012-09-13 11:27:56 補充:

C2公式:
=A2*IF(B2%<20,4+(D2<4),4-SUM((B2%/20>={1,2,3})*1)) 因版大的需求,是以2000的等差級數,首先先將B2/2000,亦可寫為B2%/20,意味著先除以100再除以20,只是字元數較少,公式看起來較短。再來就是在等差的各級作分別,每一級計算後要增減多少的計算,在此除了B2<2000時要判斷 4 或 5,其餘的是3,2,1的順序,因此則以4為基準,每增一級就減1,即 4-SUM((B2%/20>={1,2,3})*1) ,亦可寫為 4-INT(B2%/20)) ,因此公式為:
=A2*IF(B2%<20,4+(D2<4),4-INT(B2%/20)) 此式亦可簡化為PINY大的公式:
=A2*(4+(B2%<20)*(D2<4)-INT(B2%/20)) 若B2的數值有可能大於6000,最小值為1的話,可加上MAX函數,寫為鬍大的公式:
=A2*MAX(4+(B2%<20)*(D2<4)-INT(B2%/20),1)


2012-09-16 16:42:36 補充:
因版大原來的條件,所得結果是等差級數,可較為簡化,新的條件如下:
>=2000,<4000,2
>=4000,<6000,1.4,較2差0.6
SUM((B2%/20>={1,2})*{2,-0.6})

CHOOSE(INT(B2%/20),2,1.4)

以上條件未提及6000以上,若假設>=6000時等於1,較1.4差0.4,因此
SUM((B2%/20>={1,2,3})*{2,-0.6,-0.4})

CHOOSE(INT(B2%/20),2,1.4,1)

2012-09-16 16:42:50 補充:
以上公式,若以3為基準,亦可寫為:
3-SUM((B2%/20>={1,2,3})*{1,0.6,0.4})

3-CHOOSE(INT(B2%/20),1,1.6,2)

<2000:
3-(D2<4)*0.5
相當於
3-(D2<4)/2



將所有條合併而得:
=A2*(3-(D2<4)/2-SUM((B2%/20>={1,2})*{1,0.6}))


在此雖choose函數寫出來較短,但choose有限制,當B2<2000時,INT(B2%/20)等於0,會形成錯誤,當B2>=6000時,若未下結果的指令,亦會形成錯誤。

2012-09-17 12:36:08 補充:
回答處少加一個條件,公式請修正為:
=A2*(3-(B2%<20)*(D2<4)/2-SUM((B2%/20>={1,2})*{1,0.6}))
2014-08-02 4:32 pm
所以如果你想婚的念頭已經確定
就去找一家評價好一點的婚友社!
行動吧!
這是我之前參加的婚友社我覺得不錯

或搜尋 紅娘李姐
2012-09-13 5:52 pm
假設b1>0且<6000

=A1*(4-INT(B1%/20)+(D1<4)*(B1%<20))
2012-09-13 5:51 pm
C2
=LOOKUP(B2,{0,2,4,6}*1000,{4,3,2,1}+(D2<4)*(B2<2000))*A2
2012-09-13 4:18 pm
C1=A1*MAX(4-INT(B1%/20)+(B1%<20)*(D1<4),1)
2012-09-13 7:35 am
款式只有1~3種;款式有4~8種
從那裡知道款式的種類數


收錄日期: 2021-04-27 19:55:40
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20120912000016KK08717

檢視 Wayback Machine 備份