各位小伙伴们好,今天想和大家见面的是函数中的大哥——。
从复杂的数据汇总到高级动态图表,功能都离不开。 但该函数参数多,可变性强,不易灵活控制。
1.函数功能:
用于生成数据区的引用,然后将这个引用作为半成品,作为动态图表的数据源,或者作为其他功能的参数进行二次加工。
2.函数使用:
=(基点,偏移行数,偏移列数,[新引用的行数],[新引用的列数])
当第二个参数使用正数时,表示从基数向下偏移,负数表示向上偏移。
第三个参数使用正数时表示向右移动,使用负数时表示向左移动。
第四个和第五个参数是可选的。 如果省略这两个参数,则新引用的区域将与基点大小相同。
我们用下面的公式来理解函数的计算过程:
=(C3,4,2,4,3)
以C3为基点,向下偏移4行,向右偏移2列,新引用的行数为4行,新引用的列数为3列,最后引用E7:G10单元格区域获得。
让我们用更形象的方式来表达:
功能如鬼子小分队,从据点董家庄(C3)出动,沿大路南行4里(C7)
转弯向东走2里,即到马家河子(E7)
龟子队长说,我要从马家河子(E7)下手,再占一块地盘。 它有多大? 向南 4 英里,向东 3 英里。
在西边,结果是 E7:G10 单元格区域。
3.常用手势解锁:
1)行列转置
如下图,需要将A2:D7单元格中多行多列的名称转换为一列。
F2单元格中的公式为:
=($A$2,(ROW(A1)-1)/4,MOD(ROW(A1)-1,4))&""
函数的基点是 A2。
向下偏移的行数是 (ROW(A1)-1)/4。 这部分公式下拉时,可以得到从0开始递增0.25的序号,即0,0.25,0.5,0.75,1,1.25,1.5...函数自动将参数向下舍入带小数,向下移动的行数为0 0 0 0 1 1 1 1 2 2 2 2...即公式每次下拉四行,都会从数据向下移动一行来源。
向右偏移的列数是 MOD(ROW(A1)-1,4)。 当这部分公式往下拉时,可以得到一个循环序列 0 1 2 3 0 1 2 3...,即公式每往下拉一行,就右移一列从数据源中,下拉到第五行时,偏移列数又会从0开始。
偏移行数和偏移列数的组合最终形成了12 3 422 3 432 3 4....的偏移方式。
2)计算指定区间的销售额
如下图所示,需要计算从一月份到指定月份的累计销售额。
F4单元格中的公式为:
=SUM((B2,0,0,匹配(F2,A2:A13,0)))
MATCH(F2,A2:A13,0)部分,先用MATCH函数计算A2:A13中F2单元格的月份位置,结果为9。
函数以B2单元格为基点,向下偏移0行,向右偏移0列,将MATCH函数的计算结果作为新引用的行数,最终得到B2:B10单元格区域的引用,然后使用 SUM 函数计算此区域的总和,以获得从一月到指定月份的总销售额。
3)计算最近7天的平均销量
如下图,A列和B列是销售记录,要计算最近7天的平均销售量。
F4单元格中的公式为:
=((B1,计数(B:B),0,-7))
先用COUNT函数统计B列值的个数。
函数以B1为基点,将COUNT的结果作为下移的行数,即B列有多少个值,下移的行数。
这时相当于到达了B列最后一行的值,给定的偏移列号为0,新引用的行数为-7,所以从B列最后一行开始的动态值价值和上升 7 行区域。
如果B列的值增加,则COUNT函数的计数结果增加,函数的行偏移量参数随之变化。
相当于用扁担捅到底,然后有一个大鸡窝脖子,往上引7行,所以你得到的永远是引的最后7行。
最后,使用该函数计算该参考区域的平均值。
4)计算筛选后产品的总价
下图是各个食堂的进货记录,需要计算筛选后的产品总价。
单元格 G1 中的公式为:
=((3,(A1,ROW(1:9),0))*C2:C10*D2:D10)
要计算过滤后的内容,首先需要判断单元格是否处于显示状态。
让我们先看看 (A1,ROW(1:9),0) 部分。 该函数以A1单元格为基点,向下偏移的行数为ROW(1:9)的计算结果,即依次向下偏移1。 ~9 行,你最终得到 9 个引用范围,每个单元格范围由一个单元格组成。
这就涉及到多维度参考的知识点。 如果您感到困惑,可以先将其添加为书签。
接下来使用函数对函数得到的多个参考区域进行处理。 第一个参数为3,表示使用函数的计算规则,即依次统计A2~A9这9个单元格区域中非空单元格的个数。 .
如果单元格处于显示状态,则该单元格的统计结果为1,否则统计结果为0。
公式的这一部分产生类似于以下的效果:
{0;0;0;0;0;1;1;1;1}
然后将函数的结果乘以C列的单价和D列的数量,如果显示该单元格,则相当于1*数量*单价,否则相当于0*数量*单价。
最后用函数对产品进行求和,这样就得到筛选后产品的总价。
除了上面的常规用法,函数在动态图表的制作中也经常用到,后续我们会继续分享。
练习文件在这里:
好了,今天的内容就到这里。 后半部分对于大部分朋友来说可能有点难,不过不用着急,我们可以先收藏,再慢慢消化。
最后祝大家有个美好的一天!
图文制作:朱鸿忠