![如何使用 Excel 标记具有非空、空白、非空模式的行](https://linux22.com/image/1473013/%E5%A6%82%E4%BD%95%E4%BD%BF%E7%94%A8%20Excel%20%E6%A0%87%E8%AE%B0%E5%85%B7%E6%9C%89%E9%9D%9E%E7%A9%BA%E3%80%81%E7%A9%BA%E7%99%BD%E3%80%81%E9%9D%9E%E7%A9%BA%E6%A8%A1%E5%BC%8F%E7%9A%84%E8%A1%8C.png)
我希望你能帮我解决过去两天一直在想办法解决的一个问题。
我有包含 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 列时,这会拾取所有非空白、空白{、空白}、非空白模式。但这里有两个主要问题:
- 它仍然是针对特定数量的列的特定解决方案,而不是可以适用于任意数量的列的解决方案
- 我必须添加空白列才能使用偏移函数。在本例中,我在范围前后添加了一个空白列。这可能是一个问题,因为我通常在范围前后有其他与手头问题无关的列。
我想到的新公式并不是一个理想的解决方案,但与原始公式相比,它至少提供了一个可以更容易调整和扩展以容纳更多列的框架。
我分享这个解决方案是希望激励其他人想出更理想的解决方案。