excel条件格式与数据(excel conditional formatting with data)

其他开发 IT屋
问 题

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?

解决方案
  1. Select the cells/column(s) you want the conditional format applied to (for this example I assume you use column B:B)
  2. Check which cell is active - the "white" cell in the blue selection. (I assume it's B1)
  3. Create a new conditional format Home -> Conditional formatting -> New Rule
  4. Select "Use a formula to determine which cells to format"
  5. 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!

  6. Set the format you wish in the Format dialog.

Done!

本文地址:IT屋 » 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)修正行或列。

    注意:公式假定您的单元格包含正确的数字 - 或数字和最后一个字母!



  • 完成!

  • $ b

    完成! b $ b

    本文地址:IT屋 » excel条件格式与数据