计算具有某些值的单元格，这些单元格出现在具有其他值的单元格之间 [英] Count cells with certain values which appear between cells with other values

问题描述

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

I have one row in Excel with a number pattern (each number is in a different cell) like this.

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个单元格).

(My row has 300 cells).

定义:

• 我将非零值之间的零序列称为"0序列".
• 我将标记0序列的开始和结束的非零值称为标记值"

我要计算:

• 为0序列计数为0的数字.在上面的示例中，结果为:3、2、5、8、3
• 确定最小的0序列.在上面的示例中，该值为2
• 确定最大的0序列.在上面的示例中，它是8

• 确定每个0序列的频率.在上面的示例中，它是:

• Count for 0 sequence the number of 0. In this example above, the result is: 3, 2, 5, 8, 3
• Determine the smallest 0 sequence. In the example above, it is 2
• Determine the largest 0 sequence. In the example above, it is 8

• Determine the frequency of each 0 sequence. In the example above, it is:

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

• Number of 0 sequence for the entire row with length 1: 0

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

Number of 0 sequence for the entire row with length 2: 1

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

Number of 0 sequence for the entire row with length 3: 2

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

Number of 0 sequence for the entire row with length 4: 0

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

Number of 0 sequence for the entire row with length 5: 1

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

Number of 0 sequence for the entire row with length 6: 0

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

Number of 0 sequence for the entire row with length 7: 0

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

Number of 0 sequence for the entire row with length 8: 1

我尝试了像这样的不同Excel公式:

I tried different Excel formulas like this one:

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

该公式不适用于具有相同值的标记值("D13"，"D14").它也不会为多个0序列计算它.

This formula does not work for mark values ("D13", "D14") with the same value. It also does not calculate it for more than one 0 sequence.

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

Any hint, preferably without VBA, would be appreciated.

推荐答案

假定您的值在A1:AA1中，外部值在<> 0:

Assuming your values are in A1:AA1 and outer values are <> 0:

获得正确频率的核心:

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

这将得出数字数组> 3,2,5,8,3

This would evaluate to an array of numbers > 3,2,5,8,3

• 计数顺序(在我的示例中返回为B3):

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

最小序列(在B4中返回):

Smallest sequence (returned in 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)&"']"))

或者如果您未选择对B3使用TEXTJOIN:

Or if you have not chosen to use TEXTJOIN for 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))

向下拖动...

注意:所有这些公式都是通过 Ctrl Shift Enter

Note: All these formulas are array entered through CtrlShiftEnter

如您所见，我通过设置返回值的方式给您查询一些自己的想法.

As you can see I gave your query a little bit of my own twist through how I set up the returned values.

如果您愿意等待一段时间，我相信有人可以提出更简单的建议(也许使用Excel O365及其DA功能)

If you are willing to wait a while I'm sure someone can come up with something even simpler (maybe with Excel O365 and its DA-Functions)

这篇关于计算具有某些值的单元格，这些单元格出现在具有其他值的单元格之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！