如何根据遗漏次数及其个数统计遗漏号码？

楼主
lrlxxqxa

Q：如何根据遗漏次数及其个数统计遗漏号码？

A：定位P3单元格，定义名称d

=IF(Sheet1!$G$17:$L$21=Sheet1!$N3,Sheet1!$A$17:$F$21,"")

P4输入数组公式

=TEXT(INDEX(d,1,1),"00 ")
&TEXT(INDEX(d,1,2),"00 ")
&TEXT(INDEX(d,1,3),"00 ")
&TEXT(INDEX(d,1,4),"00 ")
&TEXT(INDEX(d,1,5),"00 ")
&TEXT(INDEX(d,1,6),"00 ")
&TEXT(INDEX(d,2,1),"00 ")
&TEXT(INDEX(d,2,2),"00 ")
&TEXT(INDEX(d,2,3),"00 ")
&TEXT(INDEX(d,2,4),"00 ")
&TEXT(INDEX(d,2,5),"00 ")
&TEXT(INDEX(d,2,6),"00 ")
&TEXT(INDEX(d,3,1),"00 ")
&TEXT(INDEX(d,3,2),"00 ")
&TEXT(INDEX(d,3,3),"00 ")
&TEXT(INDEX(d,3,4),"00 ")
&TEXT(INDEX(d,3,5),"00 ")
&TEXT(INDEX(d,3,6),"00 ")
&TEXT(INDEX(d,4,1),"00 ")
&TEXT(INDEX(d,4,2),"00 ")
&TEXT(INDEX(d,4,3),"00 ")
&TEXT(INDEX(d,4,4),"00 ")
&TEXT(INDEX(d,4,5),"00 ")
&TEXT(INDEX(d,4,6),"00 ")
&TEXT(INDEX(d,5,1),"00 ")
&TEXT(INDEX(d,5,2),"00 ")
&TEXT(INDEX(d,5,3),"00 ")
&TEXT(INDEX(d,5,4),"00 ")
&TEXT(INDEX(d,5,5),"00 ")
&TEXT(INDEX(d,5,6),"00 ")

另外，还可通过下法提取不超过9个号码的不重复数字

=TRIM(SUBSTITUTE(TEXT(SUM(SMALL((FREQUENCY(IF(G$17:L$21=N4,A$17:F$21),ROW($1:$98))>0)*ROW($1:$99),ROW($1:$99))*100^(99-ROW($1:$99))),REPT("00! ",O4-1)&"00"),"00",))

补充解法：

P4数组

=INDIRECT(TEXT(SMALL(IF(OFFSET(INDIRECT("A"&COUNTA($A:$A)),1-$O$1,6,$O$1,6)=$N4,(ROW(INDIRECT("1:"&$O$1))-$O$1+COUNTA($A:$A))*1000+COLUMN($A:$F)),COLUMN(A:A)),"R0C000"),)

从中间一点点分开来看：

1、INDIRECT("A"&COUNTA($A:$A))作为offset的第一参数，也就是将A列最下方一个单元格作为偏移的顶点；

2、OFFSET(INDIRECT("A"&COUNTA($A:$A)),1-$O$1,6,$O$1,6)=$N4利用offset产生一个动态区域，高度由“统计期数”也就是O18单元格决定，宽度是6，判断其是否等于N4的遗漏次数；

3、如果等于，返回(ROW(INDIRECT("1:"&$O$1))-$O$1+COUNTA($A:$A))*1000+COLUMN($A:$F)，其中ROW(INDIRECT("1:"&$O$1))-$O$1+COUNTA($A:$A)是号码的行信息，COLUMN($A:$F)是号码的列信息；

4、利用IF(OFFSET(INDIRECT("A"&COUNTA($A:$A)),1-$O$1,6,$O$1,6)=$N5,(ROW(INDIRECT("1:"&$O$1))-$O$1+COUNTA($A:$A))*1000+COLUMN($A:$F))返回包含号码行、列信息的数字和false组成的组数，如P5单元格返回的数组是{FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,20003,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,21005,FALSE}

5、利用small函数配合column，提取数组中的最小值；

6、利用text函数返回R1C1样式下号码所在单元格的引用；

7、最后利用indirect函数的第2参数为0时，使用R1C1样式来引用，返回该号码。

求公式lr.rar

求公式补充.rar

2楼
caoni1

版主，我还想做个下拉表，可以选择期数，也就是说从哪期开始统计。还有就是可以定义统计多少期。
不知道这个能不能实现呢？

3楼
水货

好东西，版主真厉害