☰

Excel 根据其他单元格进行条件突出显示

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
Excel 根据其他单元格进行条件突出显示

我对条件突出显示感到困扰。

场景：我有一个 ID 列表 (A1)，每个 ID 对应一个名称 (B2)。每个 ID 可能有一个或多个代码 (C2) 与之对应。因此，我想突出显示列出多个代码的每个 ID。在我提供的示例快照中，我会突出显示第 5 行或单元格 A5，因为它与 C5、C9、C13 和 C24 相关联。突出显示第 5 行或单元格 A5 后，我可以按单元格颜色进行筛选以评估 E 列和 F 列中的值。

请参阅更新后的图像以供参考，单元格未合并。

示例数据

ID  Name    Code    Name    #   Alpha
1234    Doe, Jane   WORK    WORK 1  5017    ABC
                11147   DEF
5678    Doe, John   OUT OUT 1   5014    JKL
                5017    MNO
                11147   PQRS
                16000   TUV
        OPEN    OPEN 1  5010    DEZ
                5017    ABC
                11123   CCC
                16000   TUV
        CLOSED  CLOSED 1    2665    BB
                5003    DD
                5004    EE
                5005    FF
                5006    GG
                5007    HH
                5008    II
                5009    JJ
                11052   JQR
                11121   RQJ
                11124   GIH
        LIFT    LIFT 1  5003    DD
                5004    EE
                5005    FF
                5006    GG
                5007    HH
                5008    II
                5009    JJ
                11052   JQR
                11121   RQJ
                11124   GIH
                15022   BLUE
8876    Blue, Jean  WORK    WORK 1  5017    ABC
                11147   DEF
                16000   GHI

答案1

仅供参考，有两个标题的列Name非常令人困惑。

我想不出一个简单的方法来突出显示R5行，因为B5与没有直接关系C9。这必须是一个混乱的公式，检查下面有多少个空白单元格。一个更简单的解决方案是突出显示任何其他Code条目，即不直接与相邻的条目Name： 条件格式示例

为了实现这一点，您需要将条件格式应用于列C（D如果您愿意，可以将其应用于整个表格宽度）。您选择一个highlight规则Use a formula to determine which cells to format（我在 Mac 上使用不同版本的 Excel，因此您的对话框可能看起来略有不同）： 条件格式

然后你应用规则：

=AND(ISBLANK($B1),NOT(ISBLANK($C1)))

这表示突出显示任何Bx空白的单元格和 Cx是非空白。美元符号很重要。

答案2

为了我自己的实践，我想看看是否/如何能够按照其初衷做到这一点。正如所料，这是一个可怕的混乱：

=AND(NOT(ISBLANK($B2)),SUMPRODUCT(--($C2:INDIRECT("$C"&MIN(IF(NOT(ISBLANK($B3:INDIRECT("$B"&ROW()+50))),ROW($B3:INDIRECT("$B"&ROW()+50))-1)))<>""))>1)

我会将其一点一点地分解。

对于 B 列中的每个值，我们希望进一步向下查找，直到找到另一个值。使用上面的示例，如果我们正在查看 B2，我们会进一步向下查找，直到在 B5 看到下一个值。我找不到 Excel 函数来“继续搜索直到找到某个值”，所以我们总是向下查找 50 行。

我们可以用以下公式生成该范围：

=$B2:INDIRECT("$B"&ROW()+50)

注意：如果直接输入到单个单元格中，此范围就没有意义。
INDIRECT 根据提供的文本生成对单元格的引用。

为了找到下一个填充的（非空白）单元格，我们必须使用数组。为了节省空间，我暂时将范围硬编码。我使用了示例代码这一页：

=MIN(IF(NOT(ISBLANK(B2:B52))), ROW(B2:B52))

请注意，在工作表单元格中输入包含数组的公式时，必须使用CTRL- SHIFT- ENTER（条件格式公式输入时不需要）。正常ENTER会产生不同的结果，并导致您抓狂。相信我的话。

因此，找到所提供范围内的所有单元格并检查它们是否不为空。如果任何单元格不为空，则返回该元素的最小行号。除此之外，这将包括B5，我们实际上想往后看 1 个单元格，因此ROW - 1：

=MIN(IF(NOT(ISBLANK(B2:B52))), ROW(B2:B52) - 1)

当然，我们真正的公式现在是：

=MIN(IF(NOT(ISBLANK(B2:INDIRECT("$B"&ROW()+50)))), ROW(B2:INDIRECT("$B"&ROW()+50)) - 1)

C2然后我们需要检查 C 列中在范围内是否有多个值C4。 这个巧妙的代码检查范围内的所有单元格是否为空白：

=SUMPRODUCT(--(range<>""))=0

range<>""检查范围内的每个单元格是否不等于""（空）。这将返回一个布尔值数组，例如：

{TRUE,FALSE,TRUE}

然后双连字符将 TRUE FALSE 值转换为一和零：

{1,0,1}

其中1，此数组中的 对应于非空白或空的单元格。SUMPRODUCT 将数组中的所有元素相加（求和），如果最终结果不为零，则表示有填充的单元格。

当然，我们期望 中有一个值与C2中的条目相对应B2，因此我们期望 1 个值。我们将其更改为：

=SUMPRODUCT(--(range<>""))>1

综合起来，我们得到：

=SUMPRODUCT(--($C2:INDIRECT("$C"&MIN(IF(NOT(ISBLANK($B3:INDIRECT("$B"&ROW()+50))),ROW($B3:INDIRECT("$B"&ROW()+50))-1)))<>""))>1

除此以外，这将返回 true，而不仅仅是包含名称/ID 的行。所以我们需要将其包装在一些额外的检查中：

=AND(NOT(ISBLANK($B2)), the rest)

这就是您的最终公式，按预期给出 TRUE 或 FALSE：

=AND(NOT(ISBLANK($B2)),SUMPRODUCT(--($C2:INDIRECT("$C"&MIN(IF(NOT(ISBLANK($B3:INDIRECT("$B"&ROW()+50))),ROW($B3:INDIRECT("$B"&ROW()+50))-1)))<>""))>1)

除非条件格式不接受它 条件格式无效

我必须添加另一列（可以隐藏）来存储结果，并以此为基础进行条件格式设置。 最后结果

参考：
间接
排
分钟
如果
不是
空
乘积和

相关内容