小夥伴們好啊,今天老祝和大家一起來學習一個多區間判斷的技巧。
先來看一下題目:
A1單元格是考核成績,要在B1單元格計算相應的等級。
等級劃分標準如下:
A1小於60的時候,等級為“F”。
大於或等於60而小於70時,等級為“E”。
大於或等於70而小於80時,等級為“D”。
大於或等於80而小於90時,等級為“C”。
大於或等於90而小於100時,等級為“B”。
大於或等於100時,等級為“A”。
求公式——
有多種方法可以完成這樣的彙總要求,下面咱們就一起看一看。
一、IF函數剝洋蔥
=IF(A1>=100,"A",IF(A1>=90,"B",IF(A1>=80,"C",IF(A1>=70,"D",IF(A1>=60,"E","F")))))
通過IF函數嵌套,像剝洋蔥一樣逐層判斷A1數值所在的區間,並返回對應的結果。
大於等於100,返回“A”,大於等於90,返回“B”,大於等於80,返回“C”……
這個公式的優點是易於理解,缺點是如果有多個判斷條件,公式會變成非常冗長了。
二、LOOKUP牛哄哄
=LOOKUP(A1,{0,60,70,80,90,100},{"F","E","D","C","B","A"})
這種寫法是多區間判斷並返回對應值的模式化公式,是IF函數逐層判斷的升級版。
注意,LOOKUP第二參數要升序處理{0,60,70,80,90,100}。
以A1為查找值,LOOKUP函數會返回第二參數中小於等於A1的最大數值,實現模糊匹配的效果,並最終返回與之相對應的第三參數{"F","E","D","C","B","A"}中的字符串。
三、建立對照很輕鬆
先來建立一個對照表,然後使用VLOOKUP函數模糊查詢:
=VLOOKUP(A1,D2:E7,2)
這裡有兩點需要注意:
一是對照表中的首列使用升序排序;
二是VLOOKUP函數省略第四參數,返回精確匹配值或近似匹配值。如果找不到精確匹配值,則返回小於待查詢內容(A1)的最大值。
相對於其他公式,建立對照表的方法看似繁瑣,但是在實際應用中更便於修改標準,而不必重新編輯公式。
四、MATCH函數顯神通
=MID("FEDCBA",MATCH(A1,{0,60,70,80,90,100}),1)
本例中MATCH函數第三參數省略,會返回第二參數{0,60,70,80,90,100}中,小於或等於第一參數的最大值所在的位置,返回的結果用作MID函數的第二參數。
MID 函數返回文本字符串"FEDCBA"中,從指定位置(MATCH函數計算結果)開始的特定數目(1)的字符。
這個公式也是比較常用的模糊匹配寫法。
最後給大家留一個題目:
如下圖所示,A列是一組文本格式的編號,要求實現B列所示的6位編號效果,不足6位的以0補齊:
秀出你的方法吧——
圖文製作:祝洪忠
閱讀更多 Excel之家 的文章