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

今天我們輕松一下，分享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函數查找其中第一個非空數據，于是就可以得到需要的結果。

（本篇完）