 OFFSET函数是EXCEL中最常用的函数之一。我之前有一篇帖子offset函数的斗转星移神功简单介绍了这个函数的语法结构和基本用法，大家可以参考。 在实际工作中，OFFSET函数的应用主要在以下几个方面： - 基本运算 - 和多个函数配合的拓展应用 - 制作动态图表 我们将按以上由简到繁的思路来想大家介绍一下这个函数强大的功能。 01 查找某一数值 OFFSET函数最基本的用法就是以指定的引用为参照系，通过给定的偏移量得到新的引用。  在单元格A9中输入“=OFFSET(A1,3,3)”即可。 思路： - 这里以A1为参照系，分别向下和向右各移动了三行，取得了新值“郭靖”  TIPs: 如果上例中以D5为参照系，第二项和第三项参数值为-3，则意味这向上和向左偏移 02 偏移后区域求和 下表是某公司商品的区域销售统计。我们可以一次性统计商品“彩电”的销售总量。  在单元格A10中输入“=SUM(OFFSET(A1,3,1,1,6))”即可。 思路： - OFFSET函数第四和第五个参数分别是“1”和“6”，表示偏移后新的区域包含有1行和6列 - 利用SUM函数对这样的一个区域进行求和，返回结果为“2834”  TIPs: 这里可以和OFFSET函数配合使用的还有AVERAGE函数，COUNT函数，MAX/MIN函数等等，可以灵活运用。 03 区域偏移 上例中，求洗衣机的最大销售量。  在单元格A10中输入“=MAX(OFFSET($B$1:$G$1,4,0))”即可。 思路： - OFFSET函数以单元格区域$B$1:$G$1为基准，向下偏移4行，得到一个新的区域 - MAX函数求得这个区域中的最大值 04反向查找 OFFSET函数配合MATCH函数也可以达到反向查找的目的。  在单元格E2中输入“=OFFSET($A$1,MATCH(D2,$B$2:$B$10,0),0)”并按CTRL+SHIFT+ENTER回车即可。 思路： - 利用MATCH函数首先判断查找值在数列中的位置 - 将此位置的值作为OFFSET函数向下移位的参数  TIPs：都有哪些函数（组合）可以实现反向查找？ - VLOOKUP+IF - INDEX+MATCH - LOOKUP - OFFSET+MATCH 05 提取不重复清单 同样地，OFFSET函数和MATCH函数配合也可以完成“提取不重复清单“的功能。  TIPS：INDEX函数也可以完成“提取不重复清单”这样一个功能 在单元格C2中输入“=OFFSET($A$1,SMALL(IF(MATCH($A$2:$A$11,$A$2:$A$11,0)=ROW($A$2:$A$11)-1,ROW($A$2:$A$11)-1),ROW()-1),0)”并按CTRL+SHIFT+ENTER回车即可。 思路： - 首先利用MATCH函数查找区域内数据的位置，并和ROW()函数对比，再利用if函数返回不重复值所对应的在当前区域中的行号 - 利用SMALL函数来提取第1小，第2小等等，并作为OFFSET函数的参数 - OFFSET函数分别返回不重复的清单 - 最后可以利用IFERROR函数屏蔽错误值。本例中没有配套使用这个函数 06 OFFSET函数和MATCH函数组合动态查找 在实际工作中，很多时候我们是希望在一张数据表上依据关键字来动态查询或统计相关的数据。这种情况下，使用MATCH函数和OFFSET函数的组合就可以轻松地解决问题。  在单元格B12中输入“=SUM(OFFSET($B$1,MATCH($A$12,$A$2:$A$7,0),0,1,6))”并按CTRL+SHIFT+ENTER回车即可。 思路： - 利用MATCH函数确定单元格A12中的产品在清单中的第几行 - 以单元格B1位基准，利用OFFSET函数进行位移。MATCH函数返回的值为向下移动的行数，0表示没有左右移动；移动完成后的区域为一行、六列 - SUM函数求和 以上例子中提到的的MATCH函数具体的用法，请参看帖子总结篇-MATCH函数使用终极帖，那里有更详细的介绍。 07 OFFSET函数和SUBTOTAL函数组合动态查找 OFFSET函数的偏移量除常量外，还可以是公式生成的数组，从而形成三维引用，最终实现动态统计需求。 我们仍以上篇中的例子为例来演示具体过程。  在单元格B12中输入“=MAX(SUBTOTAL(1,OFFSET($B$1,ROW($1:$6),0,1,6)))”并按CTRL+SHIFT+ENTER回车即可。 思路： - 要求出最大的平均销售量，首先要求出平均值。因此首先要用SUBTOTAL函数取得所有产品的平均销售量，再用MAX函数取得结果 - OFFSET函数的偏移量由ROW函数生成的数组{1;2;3;4;5;6}决定 - SUBTOTAL函数对OFFSET函数返回的三维引用进行分类计算，分别求出每一种产品的平均销售量 - 最后由MAX函数取得最大值 08 OFFSET函数和COUNTA函数配合形成动态数据区域 和COUNTA配合制作动态数据区域，多使用于创建动态图表。在下面的图标中，随着A列数据的增加，图表数据源也随着增加。 具体的公式为：“=OFFSET($A$1,COUNTA(A:A)-1,0)。建立一个名称，将这个公式写在名称中即可。 TIPs：EXCEL自带的表格也具有动态区域的功能。在创建动态图时，也可以利用表格来创建动态数据区域。 由此例又拓展出了一系列实际应用： - 查询最后一个数据 - 查询最后某几个数据 - 计算最近的一个数据区域的总和、平均值、最大/最小值 .... 由于查询最后一个/最后几个数据比较简单，下面仅以求最后三行销售数据的总和为例。 在单元格A11中输入“=SUM(OFFSET($A$1,COUNTA($A$2:$A$7)-2,1,3,6))”并按CTRL+SHIFT+ENTER回车即可。 思路： - 利用COUNTA函数求出区域中非空单元格的个数 - 最后三行意味着要向上偏移2行，因此要剪掉2 - 最后利用OFFSET函数配套SUM函数求出总和 09 OFFSET函数重复录入数据 这个功能需要配合INT函数和ROW函数。 在单元格A10中输入“=OFFSET(A$2,INT((ROW(A1)-1)/3),0)”并向下拖曳即可。 思路： - ROW函数取得行号。由于起始位置为单元格A2，因此需要ROW(A1)-1以保证A2单元格也能被重复录入 - INT函数为向下取整到最接近的整数。由于是重复3次，取整后每3行一组分别返回0, 1, 2, 3等等，并作为OFFSET函数的参数 - 如果想要重复录入4次，将公式中的“3”改为“4”即可 10 动态图表 最常用的制作动图的方法是利用名称和控件。下面我们就用OFFSET函数来演示一下如何制作。 同时选中单元格区域B10:D10，输入“=OFFSET($B$1:$D$1,MATCH(A10,$A$2:$A$7,0),0)”并 按CTRL+SHIFT+ENTER回车即可。 思路： - MATCH函数确定偏移量 - OFFSET函数取值 最终，做成动态饼图如下： OFFSET函数还有非常多的实际应用，限于篇幅，就不再展示了。本篇后半部分可能对于一些朋友有些难度，不要紧，先收藏起来，今后在使用过程中慢慢消化理解！ 文章推荐理由： 本篇文章比较全面地介绍了OFFSET函数的在使用过程中的方法和例子，可以为日常操作提供参考和借鉴。 -END- 长按下方二维码关注EXCEL应用之家 面对EXCEL操作问题时不再迷茫无助 |
|
|
来自： 阿白mvo3hep7cv > 《Excel攻略》
