請有知道怎麼寫或有經驗寫過這種東西的前輩們指導一下
批量 抽驗數量
........0~2 .......2
.....3~8.......... 3
....9~15 ........ 5
..16~25 ........8
...26~50 .....13
..51~90 ......20
..91~150 ...32
..151~280.. 50
.281~500... 80
.501~1200.. 125
1201~3200.. 200
3201~10000.. 315
問題我在欄位A1輸入90,欄位B1會帶出不對的數字？？2032？？
輸入91以上數字,欄位B1帶出數字又正確.請幫我看看我哪邊弄錯
*=IF(A1<2,"1",IF(A1<=2,"2",IF(A1<=8,"3",IF(A1<=15,"5",IF(A1<=25,"8",IF(A1<=50,"13",IF(A1<=90,"20","")))))))&IF(A1<=150,"32",IF(A1<=280,"50",IF(A1<=500,"80",IF(A1<=1200,"125",IF(A1<=3200,"200","315")))))
2007-03-17 20:12:16 · 5 個解答 · 發問者 番薯 2 in 電腦與網際網路 ➔ 軟體
函數裡的&造成你的問題囉~~~
因為你所呈述的條件裡...只要在90以前都滿足了A1<=150即選擇32，然後搭上妳&的符號，因此會顯示結果1&結果2的字串。
但是excel好像沒辦法判斷超過7層的檢驗。
所以90為界，分為兩大部分，在個別細分
應該寫成這樣
=IF(A1<=90,IF(A1<=2,"2",IF(AND(A1>=3,A1<=8),"3",IF(AND(A1>=9,A1<=15),"5",IF(AND(A1>=16,A1<=25),"8",IF(AND(A1>=26,A1<=50),"13",IF(AND(A1>=51,A1<=90),"20","32")))))),IF(AND(A1>=91,A1<=150),"32",IF(AND(A1>=151,A1<=280),"50",IF(AND(A1>=281,A1<=500),"80",IF(AND(A1>=501,A1<=1200),"125",IF(AND(A1>=1201,A1<=3200),"200","315"))))))
2007-03-18 02:35:59 補充：
這是我參考你的寫法，和大家提供的意見所改的
=IF(A1<=90,IF(A1<=2,"2",IF(A1<=8,"3",IF(A1<=15,"5",IF(A1<=25,"8",IF(A1<=50,"13",IF(A1<=90,"20","")))))),IF(A1<=150,"32",IF(A1<=280,"50",IF(A1<=500,"80",IF(A1<=1200,"125",IF(A1<=3200,"200","315"))))))
2007-03-17 22:20:25 · answer #1 · answered by 煒霖 5 · 0⤊ 0⤋
漂亮喔! ㄚ森修改後是對的. 不過所有 "" 可省略, 如下即可哦.
=IF(A1<=90,IF(A1<=2,2,IF(A1<=8,3,IF(A1<=15,5,IF(A1<=25,8,IF(A1<=50,13,IF(A1<=90,20,)))))),IF(A1<=150,32,IF(A1<=280,50,IF(A1<=500,80,IF(A1<=1200,125,IF(A1<=3200,200,315))))))
如此破解 IF 七層的限制了.
2007-03-18 12:16:43 補充：
Sorry, 應該是ㄚ霖
2007-03-18 08:03:23 · answer #2 · answered by Frank Chiou 7 · 0⤊ 0⤋
從&而後再加一層IF
&IF(A1<=90,””,IF(A1<=150,32．．．．
””＝"" 就是要改為半型
2007-03-18 00:59:53 補充：
測試看看不然整個公式看起來不像樣‧
2007-03-17 20:43:30 · answer #3 · answered by 顯栓 7 · 0⤊ 0⤋
漫漫糜鹿
理論上您的方法是對的，但是在EXCEL上是不可行的，因為他超過EXCEL的極限了....
IF函數做多只能用七層！
詳細請參考：
http://tw.myblog.yahoo.com/lyuan-716/article?mid=87&prev=-1&next=84
2007-03-18 00:34:54 補充：
番薯
因為你為了避免掉七層的限制，所以你用了＆，
但是這樣就造成了「A1<=90」、「A1<=150」同時成立的情形，所以才會出現2032
2007-03-18 00:43:43 補充：
您的這方法雖然不是最smart，但也是種解決方式，好一點的方式可以用VLOOKUP來做！但就您的公式，只要做個小修改，就可以沒有錯誤了，就是在＆後加一個IF來判斷是否大於90。
2007-03-18 00:46:21 補充：
applerot
真巧～又是相同想法～呵呵
我記得半形已經不會出錯了！
"""+++==='''---///^^^
2007-03-17 20:28:34 · answer #4 · answered by 飛 6 · 0⤊ 0⤋
首先，不能用“&”函數在裡面，所以 你的答案是出現“20”&“32”，在你的試子中，以&分界，前面的會出現20，而&之後，是32，正確的函數應該把a1<=90以後改寫，我以分行隔開，你在貼上去時，再合在一起
*=IF(A1<2,"1",IF(A1<=2,"2",IF(A1<=8,"3",IF(A1<=15,"5",IF(A1<=25,"8",IF(A1<=50,"13",IF(A1<=90,"20",
IF(A1<=150,"32",IF(A1<=280,"50",IF(A1<=500,"80",IF(A1<=1200,"125",IF(A1<=3200,"200","315",""))))))))))))
下來應該沒問題了吧@@
2007-03-17 20:22:51 · answer #5 · answered by ? 2 · 0⤊ 0⤋