Google表格 - 如何突出显示与其他单元格的值匹配的单元格？ [英] Google Sheets - How to highlight cells in a row that match values from other cells?

问题描述

• 我有一行值（比如1-10）
  
• 在行的开头，我有两个数字是根据其他单元格的日期生成的代表周数。 （例如，3和9）


• 我希望我的行突出显示来自周数号单元格的值之间的所有1-10数字。 （例如数字3,4,5,6,7,8和9的单元格会自动变成一种颜色（比如说绿色））
  
  
  

  我尝试了使用之间值的条件格式。
  它不起作用。

  
  
  

  如果我只是在格式框中输入数字，它会起作用，但问题是我需要它引用单元格中的值而不是数字I type因为日期可能会发生变化，这将影响周数单元格中显示的值，所以我需要它自动工作，并且不要求我为格式设置每行更改值。

  
  
  

  这甚至可能在床单上吗？

  
  
  

  在此先感谢，我希望我忽略了一些简单的内容。 / p>
  

  解决方案

  对条件格式使用自定义函数。例如，如果您描述的列在第2行中，则该函数将针对> = A2 和< = B2 ：

    = AND（C2> = $ A2，C2 <= $ B2）
    

  选择您希望为其创建条件格式的单元格范围; C2..L2 ，比方说。打开条件格式对话框，然后选择自定义公式。输入上面的公式;请注意，itr是指您希望将公式应用于该公式的范围的左上角 - 表格会自动调整该范围的剩余部分，这就是为前两列使用绝对引用非常重要的原因。

  
  
  
  
  
  • I have a row of values (say 1-10)
  • At the beginning of the rows I have 2 numbers generated from dates from other cells that represent week numbers. (eg, 3 and 9)
  • I want my row to highlight all the numbers from 1-10 that are between the values from the week number cells. (eg the cells with numbers 3,4,5,6,7,8 and 9 will automatically turn a colour (say green))

  I've tried the conditional format, using the "between" values. It doesn't work.

  If I just type the numbers into the format box, it will work, but the problem is I need it to refer to the value in the cell NOT a number I type in, because there is a likelihood that the dates could change, which will affect the value shown in week number cell, so I need it to work automatically and not require me to go through every single row changing values for the formatting.

  Is this even possible on sheets?

  Thanks in advance, I am hoping I've just overlooked something simple.

  解决方案

  Use a custom function for conditional formatting. For instance, if the columns you describe are in Row 2, this function will evaluate "TRUE" for numbers that are >= A2 and <= B2:

  =AND(C2>=$A2,C2<=$B2)
  

  Select the range of cells you wish to create a conditional format for; C2..L2, say. Open the conditional formatting dialog, and choose "Custom formula". Enter the formula above; note that itr refers to the top-left corner of the range you wish to apply the formula to - Sheets will automatically adjust it for the rest of the range, which is why it's important to use absolute references for the first two columns.

  这篇关于Google表格 - 如何突出显示与其他单元格的值匹配的单元格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！