[求助] EXCEL的IF函數
 

強者我朋友問我.....但我也是EXCEL低手

=ROUND(IF(AND(AND(E3>=80%,I3>=80%),J3>=N3),E3*(IF($G$1="Q1",(ad3),IF($G$1="Q2",(ae3),IF($G$1="Q3",(af3),IF($G$1="Q4",(ag3)))))),IF(AND(AND(E3>=60%,I3>=80%),J3>=N3),E3*$K$1*(IF($G$1="Q1",(ad3),IF($G$1="Q2",(ae3),IF($G$1="Q3",(af3),IF($G$1="Q4",(ag3))))))),IF(AND(AND(E3>=80%,I3>=80%),((H3*J3)/(G3*N3))>=$N$1),(IF($G$1="Q1",(ad3),IF($G$1="Q2",(ae3),IF($G$1="Q3",(af3),IF($G$1="Q4",(ag3))))))*$K$1*(H3*J3)/(G3*N3)),IF(AND(AND(E3>=60%,I3>=80%),((H3*J3)/(G3*N3)>=$N$1)),$K$1*E3*((H3*J3)/(G3*N3))*(IF($G$1="Q1",(ad3),IF($G$1="Q2",(ae3),IF($G$1="Q3",(af3),IF($G$1="Q4",(ag3)))))))),0)



朋友說, EXCEL裡回應 "您己輸入此函數的太多引數"

請問有高手會嗎? :ase

強者我朋友自己試出來...


=IF(AND(AND(E3>=80%,I3>=80%),J3>=N3),E3*(IF($G$1="Q1",(AD3),IF($G$1="Q2",(AE3),IF($G$1="Q3",(AF3),IF($G$1="Q4",(AG3)))))),IF(AND(AND(E3>=60%,I3>=80%),J3>=N3),E3*$K$1*(IF($G$1="Q1",(AD3),IF($G$1="Q2",(AE3),IF($G$1="Q3",(AF3),IF($G$1="Q4",(AG3)))))),IF(AND(AND(E3>=80%,I3>=80%),(H3*J3)/(G3*N3)>=$N$1),(IF($G$1="Q1",(AD3),IF($G$1="Q2",(AE3),IF($G$1="Q3",(AF3),IF($G$1="Q4",(AG3))))))*($K$1*(H3*J3)/(G3*N3)),IF(AND(AND((E3>=60%)*(E3<80%),I3>=80%),(H3*J3)/(G3*N3)>=$N$1),$K$1*E3*((H3*J3)/(G3*N3))*(IF($G$1="Q1",(AD3),IF($G$1="Q2",(AE3),IF($G$1="Q3",(AF3),IF($G$1="Q4",(AG3))))))))))




=ROUND(IF($G$1<>"Q4",0,IF($G$1="Q4",IF(AND(AND(E3>=80%,I3>=80%),F3>=N3),E3*(O3+P3+Q3+R3+S3+T3+U3+V3+W3+X3+Y3+Z3),IF(AND(OR(E3<80%,I3<80%),F3<N3),0,IF(AND(AND(E3>=0.8,I3>=80%),((D3*F3)/(C3*N3)>=$N$1)),(O3+P3+Q3+R3+S3+T3+U3+V3+W3+X3+Y3+Z3)*$K$1*(D3*F3)/(C3*N3)))))),0)

https://support.microsoft.com/zh-tw...2b-4a39bd951d45

樓主沒說 Excel 版本。

許多好用的函式都在 2019 之後或 o365 後才出。

我再請朋友看... :ase



了解, 後來他自已有搞定一部份, 所以應該是沒問題了 :p

AI修改得看能不能用


=ROUND(
IF(
AND(AND(E3>=80%,I3>=80%),J3>=N3),
E3*(IF($G$1="Q1",(ad3),IF($G$1="Q2",(ae3),IF($G$1="Q3",(af3),IF($G$1="Q4",(ag3),"")))),
IF(
AND(AND(E3>=60%,I3>=80%),J3>=N3),
E3*$K$1*(IF($G$1="Q1",(ad3),IF($G$1="Q2",(ae3),IF($G$1="Q3",(af3),IF($G$1="Q4",(ag3),"")))),
IF(
AND(AND(E3>=80%,I3>=80%),((H3*J3)/(G3*N3))>=$N$1),
(IF($G$1="Q1",(ad3),IF($G$1="Q2",(ae3),IF($G$1="Q3",(af3),IF($G$1="Q4",(ag3),""))))*$K$1*(H3*J3)/(G3*N3),
IF(
AND(AND(E3>=60%,I3>=80%),((H3*J3)/(G3*N3)>=$N$1),
$K$1*E3*((H3*J3)/(G3*N3))*(IF($G$1="Q1",(ad3),IF($G$1="Q2",(ae3),IF($G$1="Q3",(af3),IF($G$1="Q4",(ag3),"")))),
0
)
)
)
)
)