☰

如何使用 Excel 标记具有非空、空白、非空模式的行

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 array
如何使用 Excel 标记具有非空、空白、非空模式的行

我希望你能帮我解决过去两天一直在想办法解决的一个问题。

我有包含 1000 多行和多列的数据。我想插入一个带有（数组）公式的列，该公式标记所有有“空洞”的行；即在非空列之间有一个或多个空列的任何行，例如以下示例：

在此处输入图片描述

我的实际数据集可能有 3 到 7 列。对于上面的示例（4 列），我尝试了以下公式：

IF(OR(AND(SUM(B13:C13)=0,COUNT(A13,D13)>1),AND(B13=0,COUNT(A13,C13)>1),AND(C13=0,COUNT(B13,D13)>1)),"Issue","OK")

它似乎有效，但我需要能够处理更多列、文本或数字数据的东西，并考虑到数据周围可能存在其他非目标列。我不确定我能否识别所有可能的模式（排列），然后准确地修改这个公式。我需要一种更强大的方法来做到这一点。

答案1

这是一个通用的解决方案。我特意将其放置在工作表的中间，以说明范围之前或之后的空白列的处理。

截屏

我使用了一些辅助列，使解决方案更易于查看和排除故障。如果您尝试在一个公式中完成所有操作，公式会变得笨拙，尽管如果您喜欢自讨苦吃，您可以将其合并为一个公式。

我没有重新输入您的数据，而是使用了四列（C 到 F）的数字 1 到 4，并在行中使用不同的缺失元素模式来验证各种情况。

输入数据

第一个和最后一个数据列作为变量输入，因此您可以使用任意数量的列，并将其放置在任何位置。我任意选择了 I1 和 K1 来存储第一个和最后一个列字母。

确定第一个填充列

对于每一行，您需要找到第一个和最后一个实际值，因为“空洞”只计算在它们之间。我使用 H 列来标识该行中第一列的值。我的数据从第 3 行开始，因此 H3 包含：

=MATCH(TRUE,LEN(C3:F3)>0,0)+COLUMN(INDIRECT($I$1&":"&$I$1))-1

编辑：请注意，我测试了这是 LO Calc，它将此公式中的 TRUE 更改为1。在 Calc 中，此公式变为：

=MATCH(1,LEN(C3:F3)>0,0)+COLUMN(INDIRECT($I$1&":"&$I$1))-1

这是本答案中的原始公式，结果发现它1让 Excel 消化不良。对于 Excel，您将需要第一个公式。

这是一个数组公式，因此需要使用Ctrl+ Shift+输入Enter。

在 LEN 函数中嵌入 INDIRECT 函数不起作用，所以我不得不对范围进行硬编码。如果使用不同的列数，则需要修改列范围。

COLUMN 函数并-1调整范围的起始位置。

如果没有孔，则值的数量

第 I 列是从第一个值到最后一个值的列数（如果没有空洞，则可能的值数）。单元格 I3 包含：

=LOOKUP(9.99E+307,INDIRECT($I$1&ROW()&":"&$K$1&ROW()))+COLUMN(INDIRECT($I$1&":"&$I$1))-H3

LOOKUP 函数识别包含值的最后一列。同样，它会对工作表上数据范围的位置进行调整。它会从最后一列中减去第一列填充的列数。

填充值的数量

J 列包含填充范围内的值的数量。单元格 J3 包含：

=COUNT(INDIRECT($I$1&ROW()&":"&$K$1&ROW()))

这为您提供了识别漏洞所需的信息。

识别有孔的行

所需的结果是 G 列中的内容。单元格 G3 包含：

=IF(I3>J3,"hole","")

编辑：我进行了一些合并，并将其变成了一个公式。您可以在单元格 G3 中使用它：

=LOOKUP(9.99E+307,INDIRECT($I$1&ROW()&":"&$K$1&ROW()))
 +COLUMN(INDIRECT($I$1&":"&$I$1))-MATCH(TRUE,LEN(C3:F3)>0,0)
 -COLUMN(INDIRECT($I$1&":"&$I$1))+1
 >COUNT(INDIRECT($I$1&ROW()&":"&$K$1&ROW()))

我添加了换行符以提高可读性。如果您要复制和粘贴，则需要删除换行符和多余的空格。

Ctrl这是一个数组公式，因此您需要使用+ Shift+输入它Enter。结果只是 TRUE 或 FALSE，表示是否存在漏洞：

截屏

答案2

我想出了一个新公式，它比我原来想出的公式效果更好。这是新公式：

SUMPRODUCT(--NOT(ISBLANK(OFFSET(B2:E2,0,1))),--ISBLANK(B2:E2),--NOT(ISBLANK(OFFSET(B2:E2,0,-1))))+SUMPRODUCT(--(SUM(C2:D2)=0),--(SUM(B2,E2)>0))

在此处输入图片描述

我更有信心，当使用 4 列时，这会拾取所有非空白、空白{、空白}、非空白模式。但这里有两个主要问题：

  • 它仍然是针对特定数量的列的特定解决方案，而不是可以适用于任意数量的列的解决方案
  • 我必须添加空白列才能使用偏移函数。在本例中，我在范围前后添加了一个空白列。这可能是一个问题，因为我通常在范围前后有其他与手头问题无关的列。

我想到的新公式并不是一个理想的解决方案，但与原始公式相比，它至少提供了一个可以更容易调整和扩展以容纳更多列的框架。

我分享这个解决方案是希望激励其他人想出更理想的解决方案。

相关内容