解答網友提問:如何用最簡單的公式判斷單元格數值是否小于某值,或者等于文本?
案例:
將下圖 1 中所有 <100 的數值和文本單元格都轉化成“不達標”,其余爲“達標”,並且將“不達標”的所有單元格都高亮顯示。
效果如下圖 2 所示。
解決方案:
1. 在下方的空單元格處輸入以下公式 --> 向右向下拖動複制公式:
=IF(N(B2)<100,"不","")&"達標"
公式釋義:
N(B2)<100:
n 函數的作用是返回轉化爲數值後的值;下方是返回的值類型對照;
根據對照表,數值返回數值本身,文本返回 0;因此只要判斷結果 <100,就能將文本包含進去了;
=IF(...,"不",""):符合上述條件的,返回“不”;不符合的則返回空值;
...&"達標":將上述結果與“達標”連接起來,即可達到題目要求
插入題外話,如果不借助 n 函數,直接判斷文本是否 <100 爲什麽不行?這是因爲文本的機器代碼遠大于本案例中的數值。
用下方的 code 函數計算一下就能看到,這是一個 5 位數的代碼,顯然不可能 <100。
2. 選中 B2:F10 區域 --> 按 Ctrl+1
3. 在彈出的對話框中選擇“數字”選項卡 --> 選擇“自定義” --> 在“類型”區域輸入以下公式 --> 點擊“確定”:
[<100]不達標;;達標;不達標
自定義格式代碼釋義:
自定義格式的四個區段不僅表示:正數;負數;0;文本
也可以表示:
符合條件 1 時應用的格式;符合條件 2 時應用的格式;其他數值應用的格式;文本應用的格式
最多只能設置 2 個條件
本案例中我們只需要一個判斷條件,第二個條件就可以留空;該代碼表示 <100 顯示“不達標”,其他數值顯示“達標”;文本顯示“不達標”
接下來設置條件格式。
4. 保持選中上述區域 --> 選擇工具欄的“開始”-->“條件格式”-->“新建規則”
5. 在彈出的對話框中選擇“使用公式確定要設置格式的單元格”--> 輸入以下公式 --> 點擊“格式”按鈕:
=N(B2)<100
6. 在彈出的對話框中選擇“填充”選項卡 --> 選擇所需的填充色 --> 點擊“確定”
7. 點擊“確定”。
每種設置的結果都完全一致。