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

今天我們輕松一下，分享Excel數(shù)據(jù)分析處理中幾個(gè)非常有用的函數(shù)：INDEX，OFFSET，以及配合使用的MATCH。

背景知識(shí)：一維表和二維表

我們大多數(shù)人日常工作所接觸到的數(shù)據(jù)庫數(shù)據(jù)，其實(shí)都是一維表。

一維表的表頭，就是字段名稱；一維表的每行數(shù)據(jù)，就是一條記錄。表頭字段不允許有缺失，但是各條記錄卻允許部分字段內(nèi)容空缺。

從數(shù)據(jù)庫中導(dǎo)出的原始記錄，在Excel中呈現(xiàn)為類似于下圖：

常用的數(shù)據(jù)透視表功能，就是基于一維表。

但是當(dāng)我們呈現(xiàn)數(shù)據(jù)時(shí)，大多數(shù)情況下使用的是二維表。例如，通過數(shù)據(jù)透視表功能，將上圖中的原始記錄處理為下圖：

這個(gè)二維表所要展現(xiàn)的，就是第二年各個(gè)產(chǎn)品的按月銷售數(shù)據(jù)。

首先，并不是所有情況下你都可以拿到一維表原始記錄；其次，就算是你有原始記錄，很多情況也要求在不改變數(shù)據(jù)透視表格式的前提下仍然能夠靈活處理。這個(gè)時(shí)候，功能強(qiáng)大的Excel函數(shù)就是必須的。

本文所要介紹的，就是用于二維表數(shù)據(jù)處理的幾個(gè)常用Excel函數(shù)。

INDEX函數(shù)

INDEX函數(shù)，用于在給定區(qū)域中查找給定位置(第幾行第幾列)的數(shù)據(jù)。用法如下：

圖中輸入的函數(shù)，就相當(dāng)于是在前圖“第二年各個(gè)產(chǎn)品的按月銷售數(shù)據(jù)”區(qū)域中，查詢第5行第7列的數(shù)據(jù)，也即，產(chǎn)品Mat_5在7月的銷售數(shù)據(jù)，查詢結(jié)果為143。

當(dāng)然，這里的“第幾行第幾列”是可以輸入變量或者其它函數(shù)的。(否則就太傻了 ^_^)

所以，還需要介紹另外一個(gè)經(jīng)常輔助使用的MATCH函數(shù)。MATCH函數(shù)，用于確定數(shù)據(jù)在給定序列中的位置。用法如下：

可以看到，INDEX函數(shù)所需要的，正好是MATCH函數(shù)所專門提供的。(這真是一個(gè)偉大的巧合 ^_^)

所以，對(duì)于下圖中的問題，我們借助“INDEX+MATCH”就實(shí)現(xiàn)了可以自動(dòng)填充的計(jì)算公式。

        請(qǐng)注意示例中，為了實(shí)現(xiàn)自動(dòng)填充過程正確，使用了不同的"$"鎖定方式。

擴(kuò)展功能：

除了上述常規(guī)用法之外，這倆函數(shù)還有其它一些有意思也有用的使用方式。例如：

（1）INDEX可以返回給定數(shù)據(jù)區(qū)域的整行或整列數(shù)據(jù)。當(dāng)參數(shù)中的列數(shù)要求為0時(shí)，將返回整行，也即一個(gè)數(shù)組；反之，亦然。結(jié)合SUM、MAX等常用函數(shù)，會(huì)有更方便的使用效果。例如下圖示例，查詢幾個(gè)物料的最大單月銷售數(shù)據(jù)：

（2）MATCH的第三個(gè)參數(shù)，其實(shí)有-1,0,1三種用法。其中，0表示精確查找，而1表示查找小于或等于給定值的最大數(shù)據(jù)，并返回其位置，-1反之。并且，1才是MATCH的默認(rèn)參數(shù)。例如，我們借助MATCH這一用法，可以非常便捷地將月份轉(zhuǎn)換為季度，如下圖示例：

        注意：這個(gè)示例中，用了數(shù)組來直接表示一個(gè)給定序列。

                此外，使用參數(shù)1時(shí)，給定的序列一定要按升序排列；參數(shù)-1則需要降序排列。否則，返回結(jié)果就可能不會(huì)是你想象的那樣。                  

OFFSET函數(shù)

熟悉VBA的朋友，會(huì)知道這個(gè)函數(shù)在VBA中是多么有用。不過，在EXCEL函數(shù)中也有這么一個(gè)異曲同工的OFFSET。

OFFSET函數(shù)，用于查找給定位置的一個(gè)數(shù)據(jù)，或者，一組數(shù)據(jù)區(qū)域(以數(shù)組形式)。

看起來功能與INDEX類似，但是要比INDEX更加靈活。絕大多數(shù)情況下，INDEX所能實(shí)現(xiàn)的，OFFSET都可以；反之，卻未必。相應(yīng)地，OFFSET使用起來也會(huì)稍微復(fù)雜。

INDEX需要給定數(shù)據(jù)區(qū)域，并且在數(shù)據(jù)區(qū)域內(nèi)查找；而OFFSET則只需要給定一個(gè)起始位置，然后，按照要求“漫游”到指定位置，最后返回?cái)?shù)據(jù)或者要求的數(shù)據(jù)區(qū)域。

OFFSET函數(shù)的用法如下：

        圖中輸入的函數(shù)功能，與前面INDEX函數(shù)說明的示例一樣，查詢產(chǎn)品Mat_5在7月的銷售數(shù)據(jù)。

具體含義是：從單元格“A5"出發(fā)，先向下移動(dòng)5行，然后向右移動(dòng)7列，然后查找高度為1行、寬度為1列（也即一個(gè)單元格）的數(shù)據(jù)區(qū)域里的數(shù)據(jù)。

OFFSET函數(shù)用法說明：

    參數(shù)Reference: 表示起始位置，不能缺省?？梢詾橐粋€(gè)單元格，也可以是一個(gè)數(shù)據(jù)區(qū)域。

    參數(shù)Rows：表示移動(dòng)幾行，不能缺省。正數(shù)為向下移動(dòng)，負(fù)數(shù)為向上移動(dòng)。

    參數(shù)Cols：表示移動(dòng)幾列，不能缺省。正數(shù)為向右移動(dòng)，負(fù)數(shù)為向左移動(dòng)。

    參數(shù)Height和Width：表示在移動(dòng)到達(dá)的位置，需要多少行多少列的數(shù)據(jù)。正數(shù)表示向下和向右，負(fù)數(shù)相反。如果缺省，則采用與Reference起始區(qū)域一樣的行數(shù)和列數(shù)。

所以，上圖示例中的參數(shù)Height和Width，其實(shí)是可以缺省的。

另外，由于OFFSET可以返回一個(gè)數(shù)據(jù)區(qū)域，因此它也可以用作INDEX的第一個(gè)參數(shù)Array，以及，它自己的第一個(gè)參數(shù)Reference。

當(dāng)然，MATCH函數(shù)，也一樣可以像配合INDEX那樣配合OFFSET，所有第2到第5這四個(gè)參數(shù)都可以。

我們先看一下前述INDEX示例可以怎樣用OFFSET實(shí)現(xiàn)。

（1）查找給定產(chǎn)品、給定月份的銷售數(shù)據(jù)：

        請(qǐng)注意示例中缺省了后兩個(gè)參數(shù)。MATCH函數(shù)的用法與前面INDEX中完全一樣。

（2）查找給定產(chǎn)品的最大單月銷售額：

我們?cè)賮砜匆幌?，OFFSET可以怎樣更好用。

（3）計(jì)算給定產(chǎn)品的連續(xù)滾動(dòng)三個(gè)月平均銷售：

        請(qǐng)注意，這里對(duì)”連續(xù)三個(gè)月“的計(jì)算處理方式，以及，為了自動(dòng)填充的正確性對(duì)于”$"的用法。

        當(dāng)然，這個(gè)示例也可以用INDEX勉強(qiáng)實(shí)現(xiàn)。只不過，由于"$"的局限，需要逐行設(shè)計(jì)公式。

（4）計(jì)算給定產(chǎn)品各個(gè)月份的客戶數(shù)量：

        請(qǐng)注意，這里使用兩次OFFSET函數(shù)。

        第一次使用，是為了定位給定產(chǎn)品在給定月份的銷售數(shù)據(jù)區(qū)域。 

        第二次使用，是為了給MATCH函數(shù)提供數(shù)據(jù)區(qū)域。這里的MATCH函數(shù)，是為了確定給定產(chǎn)品的相關(guān)客戶數(shù)據(jù)到底有幾行，所以，需要OFFSET函數(shù)提供一個(gè)主要是起始位置（給定產(chǎn)品的下一行）的數(shù)據(jù)區(qū)域，然后由MATCH函數(shù)查找其中第一個(gè)非空數(shù)據(jù)，于是就可以得到需要的結(jié)果。

（本篇完）