「搞定Office」是黑马公社全新的七大版块之一,每周三更新,教授Office等办公软件的各种应用技巧。 ◆ ◆ ◆ 引用 OFFSET 函数实现动态统计
BY:熊大大 ◆ ◆ ◆ 图文说明 1.使用方法 想要实现动态统计,我们首先一起来了解一下 OFFSET 函数的使用方法。 在这个表格中,我们可以看见 OFFSET 函数的语法格式有五种,分别是:参照单元格、行偏移量、列偏移量、返回几行、返回几列。 通常的情况下 OFFSET 函数需要和统计或者是求和放在一起才能使用,也就是说 OFFSET 函数无法单独使用。 我们以这个已经写好的函数:= OFFSET (A5,2,2,5,3) 简单的说明一下上述五种语法代表的意思。 那么第一个“A5”代表什么意思呢?首先 A5 所代表的的位置是A区第五行的这个单元格,这个位置就被称为基点。 这里的第一个 2 表示不包含 A5 向下移动两个单元格的位置。后面这个 2 代表在 A7 的基础上向右移动两个单元格(不包含A5),也就是 C7 这个位置,即偏移后的基点。这两个基点就被称为位移参数。 那么这儿的 5 是什么意思呢? 注意这里的 5 是在包含 C7(偏移后的基点)的基础上向下移动 5 个单元格。然后这里的1 2 3 4 5就会产生一个面积。那这里的3,也是在包含原单元格的基础上向右移动三个单元格,即是包含了这1 2 3整个这一块面积。由此我们就得出了一个高度是5宽度是3的面积。这就是最终 OFFSET 函数所表达的含义。 2.与其他函数搭配使用 如果我们将这4个参数进行更改,那么我们会发现这个偏移量和位置是一个动态的效果,所以这个函数往往和其他函数搭配使用,我们这里为大家举个简单的例子。 在这个表中,要求我们统计最后五天的总和。可以看到,这里最后五天的总和是289。我们来看一下如果要算最后这五天的总和,需要先找到一个基点。基点可随意选择,我这里就以 C2 为基点,这个时候我们先求和。在此单元格中输入 =SUM(OFFSET 。 然后在 OFFSET 函数中选择基点 C2,输入逗号,基点找到后我们需要选择最后五天,那么我们在这里呢就需要用到 COUNTA 函数去算出整个 C 列当中一共有多少个非空格天数。 我们输入 COUNTA 函数,括号,在括号中直接选择 C列,那么 C:C 就表示C列总共有多少非空单元格,然后我们需要再倒推5行,为什么要减5呢?因为我们是要求的最后五天的总和并且离最后一个底边有5行的位置。 那么现在的新基点就在了C11这个位置。新基点不包含自己向右偏移一个单元格,所以这个是时候参数点就是1,紧接着五天的总和,总和含自己,也就是5天。最后一个参数是1,因为这里只有1列总和。 最后我们把括号括好,回车,总和就这样被 OFFSET 函数算出来了。 所以我们就把这个称之为动态引用。那么这里需要注意的是 COUNTA 是一个统计函数,比方说我们要统计这张表中的 CC 有多少个非空单元格,只需输入 COUNTA 函数后选择CC 回车后就能告诉我们一共有14个非空单元格。我们如果在下面添加数据,那么这个总和呢也会跟着改变。 我们前期视频中介绍过,选择这几组数据后可以在 Excel 表格下面看到一些求值的结果是多少。可以看到这里求和的结果显示是182。同样的我们用 COUNTA 函数算出来的结果也是182。 别说难,不动动手你怎么知道你不会?! 「上期精彩评论」 黑马评论:还有多少人是裸奔的?举起手来,让我看到你们! |
|