数据处理技巧：几个有用的Excel函数

工欲善其事，必先利其器。

今天我们轻松一下，分享Excel数据分析处理中几个非常有用的函数：INDEX，OFFSET，以及配合使用的MATCH。

背景知识：一维表和二维表

我们大多数人日常工作所接触到的数据库数据，其实都是一维表。

一维表的表头，就是字段名称；一维表的每行数据，就是一条记录。表头字段不允许有缺失，但是各条记录却允许部分字段内容空缺。

从数据库中导出的原始记录，在Excel中呈现为类似于下图：

常用的数据透视表功能，就是基于一维表。

但是当我们呈现数据时，大多数情况下使用的是二维表。例如，通过数据透视表功能，将上图中的原始记录处理为下图：

这个二维表所要展现的，就是第二年各个产品的按月销售数据。

首先，并不是所有情况下你都可以拿到一维表原始记录；其次，就算是你有原始记录，很多情况也要求在不改变数据透视表格式的前提下仍然能够灵活处理。这个时候，功能强大的Excel函数就是必须的。

本文所要介绍的，就是用于二维表数据处理的几个常用Excel函数。

INDEX函数

INDEX函数，用于在给定区域中查找给定位置(第几行第几列)的数据。用法如下：

图中输入的函数，就相当于是在前图“第二年各个产品的按月销售数据”区域中，查询第5行第7列的数据，也即，产品Mat_5在7月的销售数据，查询结果为143。

当然，这里的“第几行第几列”是可以输入变量或者其它函数的。(否则就太傻了 ^_^)

所以，还需要介绍另外一个经常辅助使用的MATCH函数。MATCH函数，用于确定数据在给定序列中的位置。用法如下：

可以看到，INDEX函数所需要的，正好是MATCH函数所专门提供的。(这真是一个伟大的巧合 ^_^)

所以，对于下图中的问题，我们借助“INDEX+MATCH”就实现了可以自动填充的计算公式。

        请注意示例中，为了实现自动填充过程正确，使用了不同的"$"锁定方式。

扩展功能：

除了上述常规用法之外，这俩函数还有其它一些有意思也有用的使用方式。例如：

（1）INDEX可以返回给定数据区域的整行或整列数据。当参数中的列数要求为0时，将返回整行，也即一个数组；反之，亦然。结合SUM、MAX等常用函数，会有更方便的使用效果。例如下图示例，查询几个物料的最大单月销售数据：

（2）MATCH的第三个参数，其实有-1,0,1三种用法。其中，0表示精确查找，而1表示查找小于或等于给定值的最大数据，并返回其位置，-1反之。并且，1才是MATCH的默认参数。例如，我们借助MATCH这一用法，可以非常便捷地将月份转换为季度，如下图示例：

        注意：这个示例中，用了数组来直接表示一个给定序列。

                此外，使用参数1时，给定的序列一定要按升序排列；参数-1则需要降序排列。否则，返回结果就可能不会是你想象的那样。                  

OFFSET函数

熟悉VBA的朋友，会知道这个函数在VBA中是多么有用。不过，在EXCEL函数中也有这么一个异曲同工的OFFSET。

OFFSET函数，用于查找给定位置的一个数据，或者，一组数据区域(以数组形式)。

看起来功能与INDEX类似，但是要比INDEX更加灵活。绝大多数情况下，INDEX所能实现的，OFFSET都可以；反之，却未必。相应地，OFFSET使用起来也会稍微复杂。

INDEX需要给定数据区域，并且在数据区域内查找；而OFFSET则只需要给定一个起始位置，然后，按照要求“漫游”到指定位置，最后返回数据或者要求的数据区域。

OFFSET函数的用法如下：

        图中输入的函数功能，与前面INDEX函数说明的示例一样，查询产品Mat_5在7月的销售数据。

具体含义是：从单元格“A5"出发，先向下移动5行，然后向右移动7列，然后查找高度为1行、宽度为1列（也即一个单元格）的数据区域里的数据。

OFFSET函数用法说明：

    参数Reference: 表示起始位置，不能缺省。可以为一个单元格，也可以是一个数据区域。

    参数Rows：表示移动几行，不能缺省。正数为向下移动，负数为向上移动。

    参数Cols：表示移动几列，不能缺省。正数为向右移动，负数为向左移动。

    参数Height和Width：表示在移动到达的位置，需要多少行多少列的数据。正数表示向下和向右，负数相反。如果缺省，则采用与Reference起始区域一样的行数和列数。

所以，上图示例中的参数Height和Width，其实是可以缺省的。

另外，由于OFFSET可以返回一个数据区域，因此它也可以用作INDEX的第一个参数Array，以及，它自己的第一个参数Reference。

当然，MATCH函数，也一样可以像配合INDEX那样配合OFFSET，所有第2到第5这四个参数都可以。

我们先看一下前述INDEX示例可以怎样用OFFSET实现。

（1）查找给定产品、给定月份的销售数据：

        请注意示例中缺省了后两个参数。MATCH函数的用法与前面INDEX中完全一样。

（2）查找给定产品的最大单月销售额：

我们再来看一下，OFFSET可以怎样更好用。

（3）计算给定产品的连续滚动三个月平均销售：

        请注意，这里对”连续三个月“的计算处理方式，以及，为了自动填充的正确性对于”$"的用法。

        当然，这个示例也可以用INDEX勉强实现。只不过，由于"$"的局限，需要逐行设计公式。

（4）计算给定产品各个月份的客户数量：

        请注意，这里使用两次OFFSET函数。

        第一次使用，是为了定位给定产品在给定月份的销售数据区域。 

        第二次使用，是为了给MATCH函数提供数据区域。这里的MATCH函数，是为了确定给定产品的相关客户数据到底有几行，所以，需要OFFSET函数提供一个主要是起始位置（给定产品的下一行）的数据区域，然后由MATCH函数查找其中第一个非空数据，于是就可以得到需要的结果。

（本篇完）