[µo°Ý] if·f°tmatch·d¤£©w

olisun

B2=¤½¥q¤H­û¡AÀ³µoª÷ÃB(D18)>23800¡A©Ò±oµ|(H12)=D18*0.05
B2=¬ù¸u¤H­û¡AÀ³µoª÷ÃB(D18)>20000¡A©Ò±oµ|(H12)=D18*0.1
B2=ÅU°Ý¾¤H­û¡AÀ³µoª÷ÃB(D18)>15000¡A©Ò±oµ|(H12)=D18*0.06

§Ú¥Î¤U­±¤½¦¡¡A¤£ª¾À³¦p¦ó­×¥¿
=IF(IF(MATCH($B$2<>¤½¥q­û¤u,D18>23800),$D$18*0.05,IF(MATCH($B$2<>¬ù¸u¤H­û,D18>20000),$D$18*0.1,IF(MATCH($B$2,ÅU°Ý¾¤H­û,D18>15000),$D$18*0.06,""))))
Á~¼úªíTEST.rar (12.19 KB)

¤U¸ü¦¸¼Æ: 6

2020-7-19 13:55

hcm19522

{=D18*CHOOSE(SUM((K2:M7=B2)*{1,2,3}),0.05,0.1,0.06)*(D18>CHOOSE(SUM((K2:M7=B2)*{1,2,3}),23800,20000,15000))

olisun

½Ð°Ý¬°¦ó¥Î{1,2,3}¡A¦Ó¤£¥Î©w¸q¦WºÙ(¤½¥q¤H­û¡A¸u¹µ¤H­û¡AÅU°Ý¾¤H­û)?

olisun

{=D18*CHOOSE(SUM((K2:M7=B2)*{1,2,3}),0.05,0.1,0.06)*(D18>CHOOSE(SUM((K2:M7=B2)*{1,2,3}),23800,20000, ...
hcm19522 µoªí©ó 2020-7-19 15:16

    1¡B½Ð°Ý¬°¦ó¥Î{1,2,3}¡A¦Ó¤£¥Î©w¸q¦WºÙ(¤½¥q¤H­û¡A¸u¹µ¤H­û¡AÅU°Ý¾¤H­û)?
    2¡B¦pªG¥Îif¡A¤£¥Î¼Æ²Õ¡A¨ºÀ³¸Ó«ç»ò³]?

­ã´£³¡ªL

=ROUND((B2<>"")*D18*SUMPRODUCT((B2=K2:M7)*(D18%>{238,200,150})*{5,10,6}%),)
=ROUND(D18*SUMPRODUCT(COUNTIF(B2,K2:M7)*(D18%>{238,200,150})*{5,10,6}%),)

olisun

=ROUND((B2"")*D18*SUMPRODUCT((B2=K2:M7)*(D18%>{238,200,150})*{5,10,6}%),)
=ROUND(D18*SUMPRODUCT(COU ...
­ã´£³¡ªL µoªí©ó 2020-7-19 16:08

    ÁÂÁ¡A­ì¨Ó¥i¥H¦³³o»ò¦hªº¥i¦æ©Ê

olisun

{=D18*CHOOSE(SUM((K2:M7=B2)*{1,2,3}),0.05,0.1,0.06)*(D18>CHOOSE(SUM((K2:M7=B2)*{1,2,3}),23800,20000, ...
hcm19522 µoªí©ó 2020-7-19 15:16

    ½Ð°Ý¦pªG±ø¥ó§ó§ï¬°
B2=¤½¥q¤H­û¡A©T©wÁ~¸ê(d5:d12)¤£¦©µ|¡A«D©T©wÁ~¸ê(d12:D17)>23800¡A©Ò±oµ|(H12)=D18*0.05
B2=¬ù¸u¤H­û¡AÀ³µoª÷ÃB(D18)>20000¡A©Ò±oµ|(H12)=D18*0.1
B2=ÅU°Ý¾¤H­û¡A©T©wÁ~¸ê(d5:d12)¤£¦©µ|¡A«D©T©wÁ~¸ê(d12:D17)>15000¡A©Ò±oµ|(H12)=D18*0.06
Á~¼úªíTEST-1.rar (12.45 KB)

¤U¸ü¦¸¼Æ: 9

2020-7-19 19:39

­ã´£³¡ªL

¦^´_ 7# olisun

°}¦C¤½¦¡:
=ROUND(IF(COUNTIF(M:M,B2),D18,SUM(D12:D17))*SUM(COUNTIF(B2,K2:M7)*(IF(COUNTIF(M:M,B2),D18,SUM(D12:D17))%>{238,200,150})*{5,10,6}%),)

olisun

¦^´_ 8# ­ã´£³¡ªL


    ¸U¤À·PÁ¡A©ú¥Õ¦U¨ç¼Æ¡A¥u¬O²Õ¦X°_¨Ó´N¤£¤Ó·|¹B¥Î¡A«¢

­ã´£³¡ªL

¦^´_ 9# olisun

¶¶§Ç§Ë¶Ã¤F:§ï¤U
=ROUND(SUM(COUNTIF(B2,K2:M7)*TEXT(CHOOSE({1,2,3},SUM(D12:D17),D18,SUM(D12:D17)),"[>"&{238,200,150}/1%&"]0;!0")*{5,10,6}%),)

©Î
=ROUND(SUM(COUNTIF(B2,K2:M7)*TEXT(SUM(D12:D17)*{1,0,1}+D18*{0,1,0},"[>"&{238,200,150}/1%&"]0;!0")*{5,10,6}%),)

©Î
=ROUND(SUM(COUNTIF(B2,K2:M7)*TEXT(IF({1,0,1},SUM(D12:D17),D18),"[>"&{238,200,150}/1%&"]0;!0")*{5,10,6}%),)