excle求统计空值的函数

jisijie630

=MOD(SMALL(IFERROR(TEXT(FREQUENCY(IF(C$2:C$25,"",ROW($2:$25)),IF(C$2:C$25,ROW($2:$25))),"[=] ;"&ROW($1:$9)&0)/1,""),ROW(B1)),10)

xf小云

广翠斋·阿成

冬不暖 发表于 2023-11-16 13:37

这个函数好用，而且不会增加计算速度，但是下拉超出计算范围会报#NUM!，请问怎么改成下拉超出范围显示为空，而不报错

广翠斋·阿成

冬不暖 发表于 2023-11-16 13:37

=MOD(AGGREGATE(15,6,TEXT(FREQUENCY(IF(B$2:B$25="",ROW($2:$25)),IF(B$2:B$25<>"",ROW($2:$25))),"0;;;")+ROW($2:$25)/1%%,ROW(A1)),100)


这个函数好用，而且不会增加计算速度，但是下拉超出计算范围会报#NUM!，请问怎么改成下拉超出范围显示为空，而不报错

冬不暖

广翠斋·阿成 发表于 2023-11-16 16:00
=MOD(AGGREGATE(15,6,TEXT(FREQUENCY(IF(B$2:B$25="",ROW($2:$25)),IF(B$2:B$25"",ROW($2:$25))),"0;;;") ...

=IFERROR(MOD(AGGREGATE(15,6,TEXT(FREQUENCY(IF(B$2:B$25="",ROW($2:$25)),IF(B$2:B$25<>"",ROW($2:$25))),"0;;;")+ROW($2:$25)/1%%,ROW(A1)),100),"")
外面加个容错函数就好

广翠斋·阿成

冬不暖 发表于 2023-11-16 16:05
=IFERROR(MOD(AGGREGATE(15,6,TEXT(FREQUENCY(IF(B$2:B$25="",ROW($2:$25)),IF(B$2:B$25"",ROW($2:$25))) ...

我是小白，麻烦大师了，非常感谢