Excel 2010 跨多个表设置条件格式

Excel 2010 跨多个表设置条件格式

我有两个表,A 列和 B 列中有值。对于这两个表,A 是数值,B 是文本值。示例如下。我想突出显示表 2-B 列中的单元格,其中表 2-B 列中的单元格与表 1-B 列中的任意单元格匹配,并且表 2-A 列中的单元格大于表 1-A 列中相应匹配的单元格。(因此,表 2 下方的单元格 B2、B4 和 B5 将被突出显示)。

桌子

这可能吗?

答案1

您可以在条件格式中使用公式来突出显示表 2 中 B 列的相应值以及 A 列同一行中的较高值:

  • 在表2的B列中选择数据
  • 条件格式
  • 新规则
  • 使用公式确定要格式化的单元格
  • 格式化此公式为 True 的值
  • =COUNTIFS(Sheet1!$B$1:$B$3,B1,Sheet1!$A$1:$A$3,"<"&A1)>0
  • 按你喜欢的格式

在此公式中:
=COUNTIFS(Sheet1!$B$1:$B$3,B1,Sheet1!$A$1:$A$3,"<"&A1)>0
Sheet1!$B$1:$B$3 是 Table1 中的 B 列,请将其更改为与您的引用相对应
B1 是 Table2 中 B 列数据的第一个单元格,如果您的数据从 B2 开始,则必须将其更改为 B2

答案2

是的,使用条件格式是可能的。

您已将上面的表 1 和表 2 显示为好像它们在同一张表上,但两个表似乎都从 A 列开始,除非它们位于不同的表上,否则这是不可能的。我将在下面展示这两种情况的解决方案。

在此处输入图片描述

从同一张表上的两个表格开始,选择 E1:E5,然后从“格式”菜单中选择“条件格式...”。单击加号以创建新规则,然后从“样式”下拉列表中选择“经典”。在另一个下拉列表中,选择“使用公式...”,然后在公式框中输入或粘贴以下内容:

=AND(COUNTIF(B$1:B$3,E1)>0,D1>IFERROR(OFFSET(B$1,MATCH(E1,B$1:B$3,0)-1,-1),0))

(请注意,您也可以将此公式粘贴到工作表的单元格中进行测试。当分别粘贴到第 1 至第 5 行时,它应该返回 FALSE、TRUE、FALSE、TRUE、FALSE)。

其工作原理如下:如果 E 列中的值出现在 B1:B3 中,则 COUNTIF()... > 0 返回 TRUE。MATCH() 查找 E 列中的值在 B1:B3 范围内的位置,OFFFSET() 查找 A 列中的对应值,IFERROR() 删除 #N/A 值。如果 D1 大于 A 列中的数字,则公式的这一部分返回 TRUE,如果两个部分都为真,则 AND() 返回 TRUE:E 列中的值必须位于 B1:B3 中,并且 D 列中的值必须大于 A 列中与 B 列中的值相对应的值,该值与 E 列中的值相同。

此处,这会导致 E2 和 E4 被突出显示。

现在,如果表 2 位于不同的工作表上,我们必须使用 INDIRECT() 函数以及引号中的工作表/单元格引用,如下所示:

=AND(COUNTIF(INDIRECT("Sheet1!B$1:B$3"),B1)>0,A1>IFERROR(OFFSET(INDIRECT("Sheet1!B$1"),MATCH(B1,INDIRECT("Sheet1!B$1:B$3"),0)-1,-1),0))

编辑:这可能取决于 Excel 的版本(我使用的是 Mac 版 Excel 2011),但事实证明 INDIRECT() 引用不是必需的:

=AND(COUNTIF(Sheet22!B$1:B$3,B1)>0,A1>IFERROR(OFFSET(Sheet22!B$1,MATCH(B1,Sheet22!B$1:B$3,0)-1,-1),0))

这为位于另一张表的 A 列和 B 列中的表 2 提供了正确的格式,如下所示:

在此处输入图片描述

相关内容