答案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 提供了正确的格式,如下所示: