MS Excel 函数初探 (坑2已填了一半)

jasonliu234. · 发表于 2014-6-22 09:09:16

Excel在职场的重要性不言而喻，面试的时候，中介会要求做Excel的测试。在某些需要处理大量数据的行业，Excel已经成为吃饭的家伙。
而函数是Excel的核心功能之一，熟练掌握和运用Excel的函数，能起到事半功倍的效果。（评点：本段纯属凑字数，骗稿费，请直接跳到下一段。）
Excel的函数，据不完全统计有400多个，很难每个都用到。偶也只能提提几个常用的函数，起到抛砖引玉的作用。
这么多函数在偶眼中只有两类，
1.查找类函数
2.运算类函数
函数是对特定几个单元格中的内容，进行运算。也就是分两大步，先要找到满足条件的单元格，其次对找到单元格中的内容，进行运算。这两大函数，就如人类分为男女两种，唯有阴阳调和，方能修出正果（变性人忽略不计哈）。
偶用的Excel 是Office 2010版本。（评点：终于切入主题了。其实上面的内容都是作者多年装B的后遗症。以后要养成先看评点的好习惯，能节省很多时间的说。）
[ 本帖最后由 jasonliu234 于 2011-4-3 19:59 编辑 ]

jasonliu234. · 发表于 2014-6-22 10:48:15

不得不提到Lookup函数，这个属于明星产品，也是忽悠中介的利器之一。Lookup函数本身用到的人不多，因为有个致命伤，数据必须升序排列。取而代之的就有V-lookup和H-lookup。
记得曾几何时，和lookup 第一次亲密接触，我就被其妖艳的外表，和强大的功能，深深折服。当我一片一片撕下其繁琐的华服，直捣黄龙之时，偶Excel的水平也同时达到了高潮。（点评：又犯病了，拖出去，阉了）
先看看vlookup长得如何得妖艳
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
第一次看到，一定会被糊弄住。别怕，其实这和查字典是一样的，要找到一个生字的意思，第一步在字典里面找到这个词，第二步找到这个字对应的意思。Vlookup也是如此，比如我有两张表格，一张是我要处理的表格，另外一张表格相当于字典。我要在另外一个表格Table array（字典）中，找到本表格内设定的值lookup value, 把字典中对应的意思，也就是同一行中后面某列col index number的数值，返回到原先表格。
Vlookup之所以经典，是因为具有一定的典型性，参透之后，能够触类旁通，迅速掌握其他的函数，Excel大部分函数的模式，都是这样的：
1.       设定查找的范围
2.       在范围内，设定查找的条件
3.       找到后，对其进行处理
总而言之，分两大步，1.找，2.处理。这和泡妞是一样的，广泛撒网，重点培养。
先举个简单的例子，我有本葵花宝典，在F和G列，内容如下：
我要处理另外一张表格，需要查询出，宝典里面 “我是”所对应的称谓，在把这个称谓，返回到这个表格内的B2单元格内。
函数怎么写呢？
查询的内容：是A2内的值
查询的表格：是偶的葵花宝典所在的两列，F和G。
宝典的列号：是宝典中，距离第一列的列数，比如宝典的第一列是F，那么G列就是第二列。所以这个列号，最小就是1。
精确还是模糊查找：一般都是选择精确，因为一旦模糊查找，excel会找到个差不多的“阿猫阿狗”滥竽充数，所以必须强迫excel敬业一点，就算没找到，宁愿显示“#N/A”，也比出来个假李逵要强。
[range_lookup],这个[]表示可选，如果不填，默认是模糊查找。填false 或者数字0，都表示精确，反之，true或者数字1则是模糊查找，当然模糊查找不是一无是处的，另有妙用，之后会提到。
接着就来说说vlookup要注意的事项，也是这个函数难点之一，因为很多人把公式理解了，依样画葫芦，就是得不到结果。我们一个一个环节来
1.       查找的内容：
如果是精确查询，查的内容，和宝典中的内容必须是一样的，怎么个一样？如果两份表格，查找的内容，都是“ABC”，但是一个是“ABC”而另一个是“ABC ”（就是ABC后加了一个空格），vlookup就找不到了，所以需要用到另外一个函数Trim，把多余的空格去掉，有时候两张表格的lookup value都要Trim一下，才能找到。其他要注意的还有单引号和双引号，不过大小写无所谓ABC 和 abc是一样的。

2.       “字典”表格怎么选：
首先要选取整列，不要只选有内容的部分，比如上面的例子，选的是F:G，而不是F1:G3。因为在B2的表格写上公式后，还要往下拖拽到B3，如果只是选了表格F1:G3，那么拖拽到B3的时候，表格table array就自动变成F2:G4，除非写公式的时候，加上了的绝对引用，$F$1: $G$3，table array才不会随着公式的复制，而自动改变位置。但这样毕竟麻烦，选择整列，第一方便，第二又避免了相对引用导致的错误。
其次， table array的第一列，是查找lookup value的所在列。用上面那个例子说明，如果要在葵花宝典中找“我是”这个字段，选择的时候，就要选F为起始列。
这里又有两个讲究，首先，起始列不必非要在A列，如果要找的内容出现在C列，而A列是其他不想关的内容，忽略就行，选择的表格范围，直接从C列开始。
另一个是，所谓的起始列，是相对的位置必须在最左边，也就是说，在葵花宝典中，“我是”这个字段的位置，必须在“猪头三”的左边。如果lookup value查找值，在返回的值的右边，那么必须把查找值（lookup value）所在整列挪到返回值的左边去。

3.       Column index，列号最小值必须为1，不接受负值，不接受向左查。也就是为什么上面一定要挪位置的原因。
4.       精确还是模糊查找
这虽然是可选项，但是如果选择错误，返回出来的值就会大相径庭。
a.       如果选精确查找，也就是false 或者数字0，表格的内容不必升序排列。如果找不到，不会将就，直接输出“#N/A”（表示没找到，并不表示函数本身有语法错误）。如果查找值在表格内多次出现，只返回第一个找到的数值，就停止查找。所谓的第一个，是从上到下第一个，找的顺序是从上至下。
b.       模糊查询，则要复杂得多，一个不注意就会出错，所以为了避免节外生枝，许多人只用精确查找。模糊查找，必须是升序排列，在升序排列的情况下先找有没有一摸一样，能够完全匹配的，如果没有一样的，会适当得放下身段，去找下一个比lookup value小的次大值。这里要注意，如果没有精确匹配到，会继续找下一个较小的值，而不是找更大的值。这和找老公的原理是一样的，理想的老公一旦找不到，只能不断得放下身段，委曲求全。不过也有人因为非要精确查找，而成为无敌圣斗士的。

可能大家发现了，偶在不断强调升序排列。在偶多年的浸淫中，发现了模糊查找的惊人秘密：

模糊查找的顺序是从下向上查找

如何找？把lookup value不断和第一列的数值比较，先和第一列最下面那个值去比，如果发现最下面的值，比lookup value要大，再向上，找下一个，直到，找到一样的值，就停下。一旦没有找到相同的值，只要发现当下的值，要比lookup value小，就不在继续，把这个值对应的，返回出来。
再次强调，模糊查找，从下到上顺序，一旦发现第一列的某个值，小于或者等于要查的值，搜寻就结束了。不再继续。即便上面有一摸一样的，也不会被找到。下面就是一个很好例子：
我要找数字7，在葵花宝典所对应的字母，肉眼一看就知道答案是b，但是excel返还的却是c，也就是5所对应的字母。Excel这个木瓜，从9开始查，和7比较，发现9比7大，那么继续往上，是5，而5比7要小，就直接停止查询，把5对应的c，返还了出来。即使5的上面就是7，近在咫尺，却咫尺天涯了。（评点：有时真爱就在转角，我们也许只是差了继续寻找的勇气）
所以模糊查询的“字典”表格，必须要以升序的方式排列，利用这个原理，我们就可以去找，某个范围之中的数字，比如下面的例子
字典表格是年收入范围和对应的税率：年收入在0-5000的交1%，5000到15000交3% 以此类推。。左边的表格有人名和相应的年收入，比如第一个人Palmer同志,他的年收入是41639，我要找到他应该交的税率。这个时候用精确查找是不可能找到的，而模糊查找就可以大显身手了，但是一定切记，TaxTable必须升序排列！
同学们可能已经发现，在模糊匹配下，vlookup就和lookup一样了。而且vlookup的确是陷阱重重，一个不小心，就驾鹤西游去鸟.
如何避免这重重的限制？下一个重要的查找类函数，就此横空出世，
留待下回分解。。。。
[ 本帖最后由 jasonliu234 于 2011-4-3 20:39 编辑 ]

jasonliu234. · 发表于 2014-6-22 12:05:37

在讨论下一个函数之前，不如先探讨一下GPS（导航仪）的使用（评点：有种不祥的预感）。
当我们在GPS中，设定目的地的时候，通常有两种方法，一种是使用POI（兴趣点），比如我要去Westfield买块豆腐，让GPS直接搜索关键字Westfield，就能把附近的几个westfield直接搜出来。我并不在乎westfield的具体地址，只要那里有豆腐给我吃就行。vlookup正是这样，我只关心能否搜到那个数值，并不在意这个数值处于葵花宝典的哪个位置。
另一种设定目的地的方式，是输入地址，门牌号码。
第一种方式，是基于内容搜索。而我们将要展示的是，后一种，location based，基于位置搜索的函数：Index – Match。（评点：终于绕回来了！还敢更绕一点吗？）
Index-Match是两个函数，乃一套组合拳，一刚一柔，一外一内，一阳一阴，一。。。（打住！）。好吧，为了能够对他们个个击破，偶不得不充当一次小三，把刚柔并济，内外兼修，阴阳失调的Index-Match夫妇，暂时拆散。。。
先说一下秀外慧中的Match的函数：MATCH(lookup_value, lookup_array, [match_type])
Match函数是干哈的呢？很简单，她只给我们一个数字，这个数字代表着一个位置，这个位置是表格内，距离起始行（列）的相对位置。比如，下面这三个苍劲有力的笔体，“神”这个字处于“偶的神”字段的第几位，答案在此就不公布了，有兴趣的同学，可以作为回家作业。
Match函数是先找到“神”，再把“神”和列中第一个“偶”字作比较，得出“神”的相对列数。她乍一看长的酷似vlookup，但是要简单得多，大家都已经历过Vlookup，均已达到“除却巫山”的至高境界，不过如果没有摸透match的脾气，也会死的很惨。看看Match的函数有什么特性：

Match得出的数值，是lookup value所在相对的位置，不是value本身。

Lookup value不分大小写，即便精确匹配，也是大小写色盲。

数列的起始位置至关重要，起始位置都处于最左边或者最上面的位置。比如上面的那个例子，“神”在C1的位置，如果数列起始位置是A1，和起始位置在B1，得出的结果就不一样。

数列本身是一个一维数组，说白了，要么一行，要么一列，如果要定位多行多列的二维数组，就不得不用两次Match函数，后面会提到。

[match type]，看到这种带括号的，就知道不是善茬。虽然是可选项，给人一种可有可无的错觉。然而一旦选错了，必将后悔终生。Match类型只接受三种类型：1,0和-1。

a.首先说0，这和vlookup一样，是精确匹配。从上到下，从左到右，只有一摸一样的，才能入选，如果找不到，就返回#N/A。
b.其次说1这个类型。1或者不填，是一样的。查找原理是，在数组中，找到小于或等于lookup value的最大值。需要升序排列。
c.最后是-1这个类型，和1刚好相反。数组中，如果有数值大于或者等于lookupvalue，就停下。要降序排列。
老实说，我就只用过0 —精确查找。其他两种，太搞，当然有志于成为Excel的大师，一定要有“我不入地狱，谁入地狱”，视死如归的革命决心。别跟我似的，只会偷懒。
Match函数是个比较简单的函数，返回的数值代表处于某个数组的一个相对位置。
现在来说说她老公Index函数：INDEX(array, row_num, [column_num])
Index就是之前所谓的，基于位置来定位的函数。我们知道Excel表格，有纵列和横行组成。说白了，就如一个坐标的第四象限，有纵坐标（行数）和横坐标（列数）组成。微软向来喜欢搞自己一套标准，象限中定位一个点，传统上，喜欢把横坐标x，放在纵坐标y之前，然而Excel中，都是先指定纵坐标的行数（row num），在写横坐标的列数（Column num）。
先举个简单的例子：
我要找到“女2”和“女B”交集的单元格内容是啥，公式是 =INDEX(C4: D5,2,2) 。
表格的范围是C4: D5; 处于B5位置的“女2”，相对于C4: D5所在的范围，是第二行。而处于D3位置的“女B”，相对于C4: D5，是第二列。
之所以有意这么写，是为了突出，公式中的行号和列号，是个相对值，相对于选取的表格范围来说，并非Excel工作簿的绝对位置。既然行号和列号是跟着表格走的，表格的选取就变得至关重要。
如何选取表格数组？
最关键的是设定好表格的起始位置，就是表格左上角的第一个单元格。因为之后的相对位置，都取决于第一个格子所在的位置。所以选的时候要特别小心。选取的方法，又因定位方式的不同，分2大类：

通过行+列的方式来定位。就比如上面的例子，表格数组的起始位置在C4，而要查找位置是D5，D5相较于C4，既不在同一行，也不在同一列。公式中，行号和列号均不能忽略。

通过单行/单列的方式定位。换言之，起始的单元格和要寻找的单元格处于同一行或者同一列。依旧用上面的例子，公式还可以写成=INDEX(C5: D5,,2)，一样可以返回出“Les”这个字段。设定的起始位置在C5，要找的是D5，处于同一行，这样写公式的时候，行号就可以忽略，连打两个逗号，最后填上列号2。

这两种方法殊途同归，唯一的区别在于起始单元格和目标单元格的相对位置。在实际操作中，可以根据表格的结构，因地制宜，灵活决定使用哪种定位方式。稍后，会给出实际例子来说明。。。。。什么？？已经被Match小姐complain了，夫妻分开太久，已经相思成灾了？好吧，偶不想成为罪人，是时候让他们再次见面，我们也可以瞻仰一下Match-Index是如何练就玉女心经，刚柔相济，干柴烈火的。。。
再次请出Match-Index贤伉俪：
MATCH(lookup_value, lookup_array, [match_type])
INDEX(array, row_num, [column_num])
Match函数能给出，相对位置。而Index能基于相对位置，找到内容。把Match 和 Index函数结合起来，就可以自动找到我们要的东东了。所以把Index公式中，row_number用一个match函数取代，column number用另一个match函数取代。最后Match和Index的全家福就是：
INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), MATCH(lookup_value, lookup_array, [match_type]))
看着晕菜了吧，晕着，晕着就习惯了。。。
来，给个例子，就明白了
数列中，纵坐标是月份，横坐标是电脑配件，中间的是销售额。A3单元格是四月份，A4中是DVD光驱。我要找出四月份DVD光驱卖了多少台。乍一看可以用vlookup解决，但是A2和A3的内容是个变值，可以随时改变成June-Flash Drives之类的，vlookup的 column Index这一项就是个变量，必须要嵌套一个match函数，也能解决。但不如Match-Index来的直观。
首先观察表格结构，发现查找的内容和起始单元格，大部分都不可能是同一列或者同一行，先老老实实选取整个表格F3：I14作为Index的array。（莫非还有不老实的？？一个字回答：恩！）。我们一步一步来。
众所周知，match只能找一维数组的中单元格的位置。所以先来index中的纵坐标所在的列—月份，row number= MATCH(A3,E3:E14,0)；横坐标的电脑产品column number =MATCH(A4,F2:J2,0)。所以最后Index函数，就是这样的：
然而，但是，不过，有时候，遇到大型表格，行数和列数恶多，电脑屏幕有限，当前的屏幕装不下全部的表格，这给选取整个表格添加了难度。此时就是灵活选择起始单元格的时候了。。。当一个表格满屏显示不全的时候，最好的选取方式，就是选择整列或整行。
Match函数的选取的array是整列和整行。April四月份处于E列的第六位，Dvd drives处于2行的第8位，这个时候相对位置，已经变成了Excel工作簿的绝对位置了。此时的起始单元格在哪里？没错，起始的单元格，就是A1。
那么Index的公式要改成这样了：=INDEX(1:1048576,MATCH(A3,E:E,0),MATCH(A4,2:2,0))
这里有2个问题要注意

“1:1048576”怎么来的？是直接点击左上角的灰色箭头得到的。1048576 是2的20次方，是Excel 2007和2010的行数的极限。1:1048576，意味着全选整张work sheet。（我擦，第一次这么佩服自己，如此有学识，连这么变态的东西都知道。。。。画外音：还不是刚google来的）

第二点就相当关键，Match-Index练习玉女心经的练门就在于此：起始单元格。两个函数的起始单元格必须一致，否则就会走火入魔（注意：从来没有加粗字体过，初次就这么献出去了）。这和跳交谊舞是一样的，起步很重要，双方要同时跨出第一步，一旦起步的时间有先后，之后每一步就错位了。

下面的例子将展示Match-Index 较之Vlookup优越的一面:向左查询，敬请期待。。。。
[ 本帖最后由 jasonliu234 于 2011-4-3 21:11 编辑 ]