excel条件格式与数据 [英] excel conditional formatting with data
问题描述
我试图根据两个条件来格式化Excel工作表的不同列:
如果单元格不包含字母u或字母r并且大于一个设定的数字。例如,如果我有一列数据,我会要0.3突出显示,因为它大于0.00834的设置数,不包含U或R.不希望0.0071U被突出或0.0056J。
有什么帮助吗? 选择您希望应用条件格式的单元格/列
到(对于这个例子,我假定你使用列 B:B
)
B1
)= AND(RIGHT(B1,1)< > U,RIGHT(B1,1)LT;> R,IF(ISNUMBER(B1),B1,VALUE(LEFT(B1,LEN(B1)-1)))> 0.00834)$ p $确保B1
是第2步中的活动单元格。还要确保它没有使用任何$
修正行或列。
注意:公式假定您的单元格包含正确的数字 - 或数字和最后一个字母!
完成!
$ b
完成! b $ b
I am trying to format different columns of my excel sheet based on two conditions: if the cell does not contain the letter "u" or the letter "r" and if it is greater than a set number.
For example, if I have a column of data, I would want 0.3 highlighted because it is greater than the set number of 0.00834 and does not contain U or R. I don't want 0.0071U to be highlighted or 0.0056J.
Any help?
- Select the cells/column(s) you want the conditional format applied
to (for this example I assume you use column
B:B
) - Check which cell is active - the "white" cell in the blue selection. (I assume it's
B1
) - Create a new conditional format Home -> Conditional formatting -> New Rule
- Select "Use a formula to determine which cells to format"
- Enter the following formula:
=AND(RIGHT(B1,1)<>"U",RIGHT(B1,1)<>"R",IF(ISNUMBER(B1),B1,VALUE(LEFT(B1,LEN(B1)-1)))>0.00834)
Make sure that the
B1
is your active cell from step 2. Also make sure that it is not using any$
to fix row or column.
Note: The formula assumes that your cells contain either proper numbers - or number and a single letter at the end!
- Set the format you wish in the Format dialog.
Done!
这篇关于excel条件格式与数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!