比较两个具有可变范围的列 [英] Comparing two columns with variable ranges
问题描述
我在excel中有一个参考列(假设Col:A).新数据正在C列中输入.我希望每当用户在将C列中的每个单元格都离开后在C列中输入数据时,代码都会检查该值.
I have a reference Column in excel(suppose Col:A). the new data is entering in Column C. I want whenever a user enter data in Column C after leaving each cell in Column C the code check the value.
Column A Column C
------------------------------------
1001 2000
2000 1005
1005 2000
如果值为A列,则背景变为蓝色(此处为1005和2000)
if the value is Column A the background changes to blue (here 1005 and 2000)
如果该值在C列中重复,则背景变为红色.(此处为2000)
if the value is duplicate in Column C the background changes to red. (here 2000)
我知道我可以在像这样的范围内存在的单元格中获取一个值
I know I can get a value in a cell exists in range like this
=COUNTIF(A1:A140;C1)
但是我不想对每个单元格都重复一次,因为A列和C列中的单元格数都没有区别.
but I don't want to repeat it for every cell , because neither the number of cell in column A is distinguish, nor in column C.
推荐答案
首先选择列C,然后使用主页"►条件格式"►新规则".打开 New Formatting Rule 对话框后,选择使用公式来确定要格式化的单元格,并为 Format值提供以下内容,其中该公式为true:文本框.
First select column C then use Home ► Conditional Formatting ► New Rule. Once the New Formatting Rule dialog is open, choose Use a formula to determine which cells to format and supply the following for the Format values where this formula is true: text box.
=countif($a:$a; $c1)
点击格式,然后选择蓝色的填充.单击确定一次以接受新格式,然后再次单击确定以创建新规则.
Click Format and select a blue Fill. Click OK once to accept the new format then OK again to create the new rule.
在所有C列仍处于选中状态的情况下,重复执行 = countif($ c $ 1:$ c1; $ c1)> 1
和红色的 Fill .
With all of column C still selected, repeat with =countif($c$1:$c1; $c1)>1
and a red Fill.
您的结果应类似于以下内容.
Your results should resemble the following.
这篇关于比较两个具有可变范围的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!