按指定数据个数进行统计计算

WYS67

按指定数据个数进行各种统计.zip (63.01 KB, 下载次数: 36)

2018-8-4 09:23 上传

点击文件名下载附件

    求B2:G4给定的条件对A列数据进行统计的函数公式。

WYS67

希望有老师写出公式。

准提部林

Ｂ５／數組公式：右拉Ｃ５
=IF(($A5="")+MOD(COUNT($A$4:$A5),B$2),"",VLOOKUP(B$2,SUBTOTAL({2,9,1},OFFSET($A5,,,-ROW($1:$19))),COLUMN(B$1),))
　
Ｄ５／數組：
=IF((A5="")+MOD(SUBTOTAL(2,A$4:A5),D$2),"",MOD(MODE(OFFSET(A5,,,-MATCH(D$2,SUBTOTAL(2,OFFSET(A5,,,-ROW($1:$19))),)),-1,-1),4))
　
Ｅ５／數組：右拉Ｇ５
=IF(($A5="")+MOD(COUNT($A$4:$A5),$E$2),"",VLOOKUP($E$2,COUNTIF(OFFSET($A5,,,-ROW($1:$19)),IF({1,0},">=0",E$4)),2,))

ROW($1:$19)　若不夠用，自行加大，
遞增式簵圍，資料多，卡～～
　
　

WYS67

准提部林 发表于 2018-8-4 11:47
Ｂ５／數組公式：右拉Ｃ５
=IF(($A5="")+MOD(COUNT($A$4:$A5),B$2),"",VLOOKUP(B$2,SUBTOTAL({2,9,1},OFFS ...

公式的计算结果非常正确。谢谢老师！

WYS67

准提部林 发表于 2018-8-4 11:47
Ｂ５／數組公式：右拉Ｃ５
=IF(($A5="")+MOD(COUNT($A$4:$A5),B$2),"",VLOOKUP(B$2,SUBTOTAL({2,9,1},OFFS ...

老师：我试着写了好多次，都没有写出H:I列的公式。请看截图：   

   H列是符合H2指定数据个数的最大值；

   I列是符合H3指定数据个数的间隔行数【从I5开始算起】；

恳请老师写出能够得出如H:I列显示的模拟结果的公式。

按指定数据个数进行各种统计.zip (212.25 KB, 下载次数: 5)

2018-10-1 19:24 上传

点击文件名下载附件

micch

i5

1. =IF(A5="","",IF(MOD(COUNT(A$5:A5),3),"",ROW()-IFERROR(LOOKUP(11,I$4:I4,ROW($4:4)),4)))

复制代码

H5

1. =IF(I5="","",MAX(OFFSET(A5,,,-I5)))

复制代码

WYS67

micch 发表于 2018-10-1 20:08
i5H5

老师：H2:I2的指定数据个数呢？必须按H2:I2的指定数据个数写公式，这样当H2:I2指定数据个数的条件发生变化时，公式始终都能够显示正确的统计结果。

WYS67

micch 发表于 2018-10-1 20:08
i5H5

另外，尽量不要用=IF(A5="","",IF(MOD(COUNT(A$5:A5),3),"",ROW()-IFERROR(LOOKUP(11,I$4:I4,ROW($4:4)),4)))  这类递增范围越来越大的参数和函数，数据量大，会卡卡的。

WYS67

希望有老师写出5楼需要的两个公式

micch

H5

1. =IF((A5="")+MOD(COUNT(A$5:A5),H$2),"",VLOOKUP(H$2,SUBTOTAL({2,4},OFFSET(A5,,,-ROW($1:$22))),2,))

复制代码

I5

1. =IF((A5="")+MOD(COUNT(A$5:A5),I$2),"",MIN(MATCH(I$2,COUNTIF(OFFSET(A5,,,-ROW($1:$22)),">=0")),ROW()-4))

复制代码

不用count(A$5:A5)  真不好判断间隔行数，会复杂很多，lookup到是可以不用；公式中22可以改大一点，避免连续22行数字不足间隔行数的要求