☰

第四项的条件格式？

tag-icon • tag-icon microsoft-excel microsoft-excel-2016 conditional-formatting
第四项的条件格式？

我知道如何更改重复条目的单元格颜色，但如何/可以更改每 4 个条目的颜色？值将是一个未知的数字和字母组合，希望每 4 次输入相同的组合时突出显示一次。

嘿，大家好，谢谢你们的耐心，我已经上传了我希望完成的表格的样子的示例，链接如下。

我已手动突出显示字母/数字组合的第四次重复 C020、G020、B004 和 F028

如您所见，重复不一定发生在同一行或 4 列之后。

http://s000.tinyupload.com/?file_id=56226468952646159686

答案1

我不明白确切地您想要的，因为您提供的示例电子表格似乎与您的问题没有任何关系。您说“相关数据在灰色列中”，但我没有在灰色列中看到任何重复的值。您是指“RETURN”、“RETURN/DC”、“TOTAL”和“TOTAL/DC”吗？它们在非灰色的列？

但是您以（令人困惑/不清楚的）叙述形式呈现的数据给了我一些可供参考的东西。我假设数字在第 1 行。从检测重复条目的技术开始：

=COUNTIF($A1:B1,B1)

计算这单元格在该行中出现的时间，最多包括当前单元格。第一次出现值时，该值为 1，重复出现时，该值为 2 或更多。但您不想测试该计数是否大于 1；您想测试它是否是 4 的倍数。因此测试

=MOD(COUNTIF($A1:B1,B1),4)=0

只需将上述公式用于条件格式即可， 从第二个单元格开始。

下面，

  • 第 1 行是您的数据（来自问题），根据上面的第二个公式进行条件格式化，
  • 第 2 行是上面的第一个公式，并且
  • 第 3 行是上面的第二个公式。

包含 OP 数据的电子表格

因此，第 2 行显示第 1 行的重复次数，第 3 行显示第 2 行是 4 的倍数的列（这些列是第 1 行着色的列）。

答案2

根据我的理解，我建议使用一点 VBA UDF 和辅助列的解决方案。

下面给出了一个略微简化的表格示例。相关数据位于 C、E、G 和 I 列中。每列右侧都有一个辅助列，您可以根据需要将其隐藏。

在此处输入图片描述

首先，在工作表中按ALT+F11访问 VBA 编辑器。从插入菜单插入一个模块，并将以下 UDF（用户定义函数）代码粘贴到其中。

Function prmarr(ParamArray arg()) As Variant

Dim arr1
cnt = 0

For i = LBound(arg) To UBound(arg)

cnt = cnt + arg(i).Rows.Count ' get total rows from all ranges

Next i

ReDim arr1(cnt)  ' re dim the array for those many total rows

cnt = 0  ' reuse the counter now

'create a one dimentional list of array from all of the above ranges
For i = LBound(arg) To UBound(arg)
    For Each cell In arg(i)
        arr1(cnt) = cell.Value
        cnt = cnt + 1
    Next cell
Next i

prmarr = arr1  ' pass this array as return parameter
End Function

请注意，这是非常基本的 VBA 代码，并且代码中没有任何验证或错误检查。如果您传递水平数组、重叠数组或多维数组，它可能会失败。假设您只将列数组传递给它才能正常工作。

此函数接受可变数量的列数组范围并返回一个一维数组，该数组包含其中的所有单元格值，我们将使用该数组计算自第一列数据的起始单元格以来当前值的总出现次数。

由于您的 Excel 中有 VBA 代码，因此您需要将文件保存为 .XLSM 启用宏的 Excel 工作表。

在 D1 中输入以下公式并将其向下拖至预期的行。

=COUNTIF($C$1:C1,C1)

现在，随着您逐步完成后续的辅助列。每个辅助列都需要对公式进行轻微修改。虽然结构保持不变，但参数数量会增加。

在 F2 中输入以下公式，然后按公式栏中的CTRL+ SHIFT+ENTER创建数组公式。Excel 现在将公式括在花括号中，以表明它是一个数组公式。此步骤需要创建数组公式，否则将产生错误的结果。

=SUM(IF(prmarr(C$1:C$9,E$1:E1)=E1,1,0))

理解这个公式。您将 C1:C9 和 E$1:E1 作为参数传递给 UDF，即前一列 + 当前列的第一个值直到测试条件值，并检查是否与当前单元格匹配。如果是，SUM 将产生自第一列开始以来该值的总计数。将其向下拖动到预期的行。

同样，现在 H1 中的数组公式变为

=SUM(IF(prmarr(C$1:C$9,E$1:E$9,G$1:G1)=G1,1,0))

等等。

对所有列完成此操作。

现在进入条件格式部分。

选择第一个单元格，在本例中即 C1。转到条件格式 --> 新规则 --> 使用公式确定要格式化的单元格。

现在在规则中输入以下公式

=MOD(D1,4)=0

选择所需的背景颜色，然后单击“确定”将格式应用于单元格 C1。

现在，在选择 C1 的同时，双击格式刷并将此格式涂抹到所有适用的数据列。

在此处输入图片描述

注意。

  • Excel 可能对可以传递给 UDF 的参数数量有限制。我不太确定如果将其声明为ParamArray as Variant
  • 我建议您首先在测试工作表中使用模拟各种条件的样本数据对其进行测试，以确认其是否按预期工作，然后再将其应用到生产表。
  • 如果您仍然遇到任何问题或存在任何错误，请在此处更新，如果时间允许，我会尝试修复它。

相关内容