B1欄位的函數 能把A1欄位裡輸入的6項條件都寫成一條函數的判斷式子嗎?

2017-06-06 3:59 pm
B1欄位的函數 能把A1欄位裡輸入的6項條件都寫成一條函數的判斷式子嗎?

(1)
A1欄位數值為空白及0~499 B1欄位為空白
(2)
A1欄位數值為500~2999 B1欄位會顯示為1
(3)
A1欄位數值為3000~19999 B1欄位會顯示為2
(4)
A1欄位數值為20000~49999 B1欄位會顯示為3
(5)
A1欄位數值為50000~99999 B1欄位會顯示為4
(6)
A1欄位數值為100000~99999999 B1欄位會顯示為5


目前只寫出1項條件
=IF(A1="","",IF(A1<500,"",IF(AND(A1>=500,A1<=2999),1,1)))

回答 (2)

2017-06-06 5:10 pm
✔ 最佳答案
=LOOKUP(A1,{0,500,3000,20000,50000,100000},{"",1,2,3,4,5})
2017-06-06 5:16 pm
試試看
=INDEX({" ",1,2,3,4,5},MATCH(1,FREQUENCY(A1,{499;2999;19999;49999;99999}),FALSE))


收錄日期: 2021-05-04 02:03:22
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20170606075915AAZWeuI

檢視 Wayback Machine 備份