|
||
|
Elite Member
  
加入日期: Mar 2004
文章: 4,139
|
[求助] 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裡回應 "您己輸入此函數的太多引數" 請問有高手會嗎? 
__________________
 賣二手SMART智富雜誌225-304期, 共80期, 800元 舊舊的打字機(攜帶型)有人要嗎?  https://i.imgur.com/QEUBJSB.jpg https://i.imgur.com/yQAYNL2l.png https://i.imgur.com/zpfrBlwh.png https://pcdvd.com.tw/showpost.php?p=1086562230&postcount=68 https://www.youtube.com/watch?v=XStZk94w75k 一根毛毛出眉來  毛毛掉了, 等它再長出來 https://www.youtube.com/watch?v=NIPukkvHpAU |
|||||||
2024-05-21, 10:50 PM
#1
|
|
|
Elite Member
加入日期: Mar 2004
文章: 4,139
|
強者我朋友自己試出來...
=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)
__________________
賣二手SMART智富雜誌225-304期, 共80期, 800元 舊舊的打字機(攜帶型)有人要嗎? https://i.imgur.com/QEUBJSB.jpg https://i.imgur.com/yQAYNL2l.png https://i.imgur.com/zpfrBlwh.png https://pcdvd.com.tw/showpost.php?p=1086562230&postcount=68 https://www.youtube.com/watch?v=XStZk94w75k 一根毛毛出眉來 毛毛掉了, 等它再長出來 https://www.youtube.com/watch?v=NIPukkvHpAU 此文章於 2024-05-22 12:41 AM 被 polar168 編輯. |
||
|
2024-05-22, 12:39 AM
#2
|
|
|
Advance Member
加入日期: Dec 2001 您的住址: ddddddd
文章: 317
|
引用:
https://support.microsoft.com/zh-tw...2b-4a39bd951d45
__________________
ddddd |
|
|
2024-05-23, 12:52 PM
#3
|
|
|
Senior Member
 加入日期: Sep 2001 您的住址: 淡水
文章: 1,120
|
樓主沒說 Excel 版本。
許多好用的函式都在 2019 之後或 o365 後才出。 此文章於 2024-05-23 01:29 PM 被 ghostcode 編輯. |
|
2024-05-23, 01:16 PM
#4
|
|
|
Elite Member
加入日期: Mar 2004
文章: 4,139
|
引用:
我再請朋友看... 引用:
了解, 後來他自已有搞定一部份, 所以應該是沒問題了 
__________________
賣二手SMART智富雜誌225-304期, 共80期, 800元 舊舊的打字機(攜帶型)有人要嗎? https://i.imgur.com/QEUBJSB.jpg https://i.imgur.com/yQAYNL2l.png https://i.imgur.com/zpfrBlwh.png https://pcdvd.com.tw/showpost.php?p=1086562230&postcount=68 https://www.youtube.com/watch?v=XStZk94w75k 一根毛毛出眉來 毛毛掉了, 等它再長出來 https://www.youtube.com/watch?v=NIPukkvHpAU |
||
|
2024-05-23, 01:48 PM
#5
|
|
|
Advance Member
加入日期: Oct 2017 您的住址: 我會遵守版規,並保持禮貌
文章: 352
|
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 ) ) ) ) ) |
|
2024-05-23, 02:21 PM
#6
|
|
