Excel函数公式的问题？

Question

Excel中在A1至Z1中有一组随机（只有0和1）的数，例如：010000001000000000100000100请教2个公式：1）从右数起，第1个“1”和第2个“1”之间的“0”的个数（答案为5）2）所有“1”之间的0的个数的平均数（答案为（6+9+5）/3=6.67)

Answer 1

看上图。

【首先说明一点】：

本方法需要引用左侧相邻的单元格，因此，像上面图片上的A列那样，在左边多放一列，A1、A2、A3、A4，这几个单元格空着也行，写上几个标题也行，总之不要写数字。

【下面是公式】：

B2单元格的公式为：

=IF(OR(A1=1,B1=0),"",COUNTIF($B1:B1,0)-SUM($A2:A2))

此公式向右拉，填充右边的区域，

第2行的结果是指：第1行的数字1，它的左边有几个零。

B3单元格的公式为：

=IF(COUNT($B2:B2)>1,B2,"")

此公式向右拉，填充右边的区域，

第3行的结果是指：对第2行结果去除了第一个数，因为第1个数统计的不是夹在两个1中间的零。

B4单元格的公式为：

=AVERAGE(B3:AA3)

B4的结果是对第3行的数据计算出 算术平均数。

追问

你好，谢谢，但是需要的是2个公式，还差1个公式。另外，这个的数组我还有很多行，能不能不用辅助列，直接在一个单元格内算出答案？

追答

你这个问题，如果不用辅助列，我感觉用现有的公式函数是很难解决的。
可以考虑用VBA自行编写自定义函数，或许能达到目的。

Answer 2

可以使用一些Excel内置函数来实现这些公式。假设随机数的范围是A1至Z1：
1）从右数起，第1个“1”和第2个“1”之间的“0”的个数可以使用以下公式实现：
=SEARCH("1",A1,SEARCH("1",A1)+1)-SEARCH("1",A1)-1
该公式通过 SEARCH 函数在A1中搜索第一个 "1"，然后在第一个 "1" 的位置上再次使用 SEARCH 函数搜索第二个 "1"，然后计算两个 "1" 之间的 "0" 的数量。由于该公式只考虑了从右数起的第一个和第二个 "1" 之间的 "0" 的数量，因此如果有多个 "1"，那么这个公式只计算最右边的两个 "1" 之间的 "0" 的数量。
2）所有“1”之间的0的个数的平均数可以使用以下公式实现：
=AVERAGE(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="0",IF(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1)="1",ROW(INDIRECT("1:"&LEN(A1)))))-IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="1",ROW(INDIRECT("1:"&LEN(A1)))))
该公式使用 MID 函数在每个位置上检查 A1 中的数字是否为 "0" 或 "1"。如果当前位置上的数字是 "0"，那么它会检查下一个数字是否为 "1"。如果是，它将在另一个数组中记录当前位置，否则它将跳过该数字。公式使用 ROW 函数生成一个数组，该数组从 1 到字符串长度，并将该数组作为 INDIRECT 函数的参数。通过这种方式，公式可以创建包含所有数字位置的数组。最后，公式计算每个 "1" 之间的 "0" 的数量，并计算这些数量的平均值。

Answer 3

这两个问题都比较简单，做法也有很多，我先说下我的思路：
1、先用IF函数判断，如果等于1则返回对应的列号，再用LARGE函数对最大列号与第二大列号作差，结果减去1,就是最后两个1之间0的个数。公式如下（数组公式需三键输出）：
=LARGE(IF(A1:Z1=1,COLUMN(A1:Z1),""),1)-LARGE(IF(A1:Z1=1,COLUMN(A1:Z1),""),2)-1
2、先用IF函数进行判断，如果等于1则返回对应的列号，用最大列号减去最小列号加1就是有效字段总数(目的：排除第一个不是1的无效字段个数)，减去1的总个数，就是0的总个数，最后除以1的总个数减去1，就得到最后结果。公式如下（数组公式需三键输出）：
=(LARGE(IF(A1:Z1=1,COLUMN(A1:Z1),""),1)-SMALL(IF(A1:Z1=1,COLUMN(A1:Z1),""),1)+1-COUNTIF(1:1,1))/(COUNTIF(1:1,1)-1)