excel不同行数自动统计最大最小值的写法

chengsijing

假设要统计B/C/D列 的最大最小值（数值从第4行开始，末行每次刷新不固定,这次是14行，因为第4行开始，所以实际行数是17），
我们一般写公式 =MAX(B4:B17)，但由于数据刷新，下次不一定是14行，所以公式要修改。为了通用，我E1单元格写了公式：=COUNTA(A:A) ，用于自动统计有值的单元格行数数量，然后求最大值的那里代码改为：=MAX(INDIRECT("b4:b"&$E$1)) 。这样当行数变化的时候，max都可以自动统计范围内的值。

新的问题来了，当我想C/D列都这样的时候，我从B2列拖动公式到C2/D2,里面的公式值不变，还是=MAX(INDIRECT("b4:b"&$E$1))和=MAX(INDIRECT("b4:b"&$E$1))。我要手动改为=MAX(INDIRECT("C4:C"&$E$1))及=MAX(INDIRECT("D4:D"&$E$1))。因为双引号把"B5:B"当作字符了，拖动的时候不会自动改变。
请问有更好的方法自动根据行数的变化，max函数自动有效吗？
我有很多这样的列，我想写一个公式，拉动的时候自动更新，不用我手工去修改代码

不知道为什么

无需那么复杂把，插入表格就可以实现。效果如图：

准提部林

B1//下拉一格//右拉
=SUBTOTAL(6-ROW(A1),B$4:INDEX(B:B,MATCH(9E+307,$A:$A)))

MissMist

=MAX(B4:INDIRECT(ADDRESS($E$1,COLUMN(),4)))

chengsijing

十分感谢，我马上试试

chengsijing

MissMist 发表于 2023-4-5 23:37
=MAX(B4:INDIRECT(ADDRESS($E$1,COLUMN(),4)))

=MAX(B4:INDIRECT(ADDRESS($E$1,COLUMN(),4)))
=ADDRESS($E$1,COLUMN(),4) 是取得 该列字母及$E$1的值。
INDIRECT()转成字母。
这个容易理解。十分感谢

chengsijing

MissMist 发表于 2023-4-5 23:37
=MAX(B4:INDIRECT(ADDRESS($E$1,COLUMN(),4)))

=MAX(B4:INDIRECT(ADDRESS($E$1,COLUMN(),4)))  是基于我有一个单元格记录该行最大行数的公式写法。
有没有更进一步，公式不需要这个算出来的最大值，直接全部用函数算出来？

chengsijing

准提部林 发表于 2023-4-4 12:07
B1//下拉一格//右拉
=SUBTOTAL(6-ROW(A1),B$4:INDEX(B:B,MATCH(9E+307,$A:$A)))

同样十分感谢。
右拉变成：=SUBTOTAL(6-ROW(B1),C$4:INDEX(C:C,MATCH(9E+307,$A:$A)))
下拉变成：=SUBTOTAL(6-ROW(A2),B$4:INDEX(B:B,MATCH(9E+307,$A:$A)))
功能都成功，就是我不太理解。好像这个牛B在于不需要预设最大行数。但是对相对位置有要求，不然回出错

chengsijing

我结合2位高手的写法，写出自己理解，不需要最大值辅助的动态行数，统计公式的又可以随意拉动的写法。
=MIN(B7:INDEX(B:B,MATCH(9E+307,$B:$B)))
INDEX(B:B,MATCH(9E+307,$B:$B)) 的意思是求得该列最后有效值的最大行数（前面行数都清空都不影响）
即如果在B列17行有一个非空值，那么INDEX(B:B,MATCH(9E+307,$B:$B)) =B17
=MIN(B4:INDEX(B:B,MATCH(9E+307,$B:$B))) = =MIN(B4:B17)的效果，支持左右拉，该列任意位置都有效。而且不需要先计算有最大行数值辅助。
不知道是否还有更简便，尤其是计算速度最快的写法。

chengsijing

准提部林 发表于 2023-4-4 12:07
B1//下拉一格//右拉
=SUBTOTAL(6-ROW(A1),B$4:INDEX(B:B,MATCH(9E+307,$A:$A)))

=SUBTOTAL(6-ROW(A1),B$4:INDEX(B:B,MATCH(9E+307,$A:$A)))
不知道我理解的对不对。
INDEX(B:B,MATCH(9E+307,$A:$A))  相当于求得该列字母+A列最大行数
SUBTOTAL(5,B$4:b17)  的第一个参数5是求第二个参数的最小值。6-ROW(A2)=6-1=5
SUBTOTAL(4,B$4:b17)  的第一个参数4是求第二个参数的最大值。6-ROW(A2)=6-2=4