excel - 计算出现在具有其他值的单元格之间的具有某些值的单元格

Question

我在 Excel 中有一行带有这样的数字模式(每个数字在不同的单元格中)。

1 0 0 0 2 0 0 1 0 0 0 0 0 3 0 0 0 0 0 0 0 0 2 0 0 0 1

(我的行有 300 个单元格)。

定义:

我将非零值之间的零序列称为“0序列”。

我将标记 0 序列开始和结束的非零值称为“标记值”

我想计算:

对 0 序列计数 0 的个数。在上面的示例中，结果为:3、2、5、8、3

确定最小的 0 序列。在上面的例子中，它是 2

确定最大的 0 序列。在上面的例子中，它是 8

确定每个 0 序列的频率。在上面的例子中，它是:

长度为 1 的整行的 0 序列数:0

整行长度为 2 的 0 序列个数:1

整行长度为 3 的 0 序列个数:2

整行长度为 4 的 0 序列数:0

整行长度为 5 的 0 序列个数:1

整行长度为 6 的 0 序列个数:0

整行长度为 7 的 0 序列数:0

整行长度为 8 的 0 序列个数:1

我尝试了不同的 Excel 公式，例如:

=ABS(MATCH(D13, B2:B11, 0)-MATCH(D14, B2:B11, 0))-1

此公式不适用于具有相同值的标记值(“D13”、“D14”)。它也不会为多个 0 序列计算它。

任何提示，最好没有 VBA，将不胜感激。

Answer 1

假设您的值在 A1:AA1 中外部值为 <> 0:

获得正确频率的核心:

=FREQUENCY(IF(B1:Z1=0,COLUMN(B1:Z1)),IF(B1:Z1=0,"",COLUMN(B1:Z1)))

这将评估为一组数字 > 3,2,5,8,3

序列计数(在我的示例中返回 B3):

=TEXTJOIN(",",1,FREQUENCY(IF(B1:Z1=0,COLUMN(B1:Z1)),IF(B1:Z1=0,"",COLUMN(B1:Z1)))

最小序列(返回 B4 ):

=MIN(FREQUENCY(IF(B1:Z1=0,COLUMN(B1:Z1)),IF(B1:Z1=0,"",COLUMN(B1:Z1))))

最大序列(返回 B5 ):

=MAX(FREQUENCY(IF(B1:Z1=0,COLUMN(B1:Z1)),IF(B1:Z1=0,"",COLUMN(B1:Z1))))

每个频率的计数(返回 B5:B13 ):

=COUNT(FILTERXML("<t><s>"&SUBSTITUTE(B$3,",","</s><s>")&"</s></t>","//s[.='"&ROW(A1)&"']"))

或者如果您没有选择使用 TEXTJOIN对于 B3 :

=SUM(IF(FREQUENCY(IF(B$1:Z$1=0,COLUMN(B$1:Z$1)),IF(B$1:Z$1=0,"",COLUMN(B$1:Z$1)))=ROW(A1),1,0))

拖累...

注:所有这些公式都是通过 CtrlShiftEnter 输入的数组



如您所见，我通过设置返回值的方式对您的查询进行了一些修改。

如果您愿意等待一段时间，我相信有人可以想出更简单的东西(也许使用 Excel O365 及其 DA-Functions)