【SUBTOTAL函数】和【OFFSET函数】动态求最值

点击上方右侧“EXCEL应用之家”蓝字关注微信公众号

点击文章底部“阅读原文”可领取阅读红包；模板文档可免费获取

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

OFFSET函数的偏移量出来常量外，还可以是公式生成的数组，从而形成三维引用，最终实现动态统计需求。

我们仍以上篇中的例子为例来演示具体过程。

在单元格B12中输入

“=MAX(SUBTOTAL(1,OFFSET($B$1,ROW($1:$6),0,1,6)))”

思路：

  • 要求出最大的平均销售量，首先要求出平均值的最大值。因此首先要用SUBTOTAL函数取得所有产品的平均销售量，再用MAX函数取得结果

  • OFFSET函数的偏移量由ROW函数生成的数组{1;2;3;4;5;6}决定

  • SUBTOTAL函数对OFFSET函数返回的三维引用进行分类计算，分别求出每一种产品的平均销售量

  • 最后由MAX函数取得最大值

这里SUBTOTAL函数的语法结构是：

SUBTOTAL（function_number, value1,value1...）

其中function_number包含11个数字，1表示求平均值

看到这里可能有的朋友会问了，为什么不能使用AVERAGE函数，将公式写成“=MAX(AVERAGE(OFFSET($B$1,ROW($1:$6),0,1,6)))”？

原因就在于：这里OFFSET函数产生了多维引用。SUBTOTAL函数支持函数返回的三维引用。故能返回正确结果；AVERAGE函数不支持函数返回的三维引用，故不能使用。

-END-

欢迎关注【Excel应用之家】专栏，了解更多的Excel实际应用技能，尽在Excel应用之家！

版权声明：本文归Excel应用之家专栏（微信公众号Excel应用之家）原创撰写，未经允许不得转载。欢迎关注专栏/公众号。

赞 (0)

相关推荐