计算具有某些值的单元格出现在具有其他值的单元格之间 [英] 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 序列开始和结束的非零值称为标记值"
我要计算:
- Count for 0 sequence 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中返回):
=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:
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))
下拉...
注意:所有这些公式都是通过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屋！
