决胜职场的必备函数
2016-08-26 11:19阅读：
最近呢有款手机游戏特别火，一个从《部落冲突》演化而来的卡牌游戏，经常能在等车、等人的时候看到有朋友会拿出手机玩上几局，游戏的开发公司最近还被腾讯给收购了。
没错，这款游戏就叫做《皇室战争》，大家有玩过吗？我也是玩家之一，零氪金，最高打到过2900多杯，离传奇竞技场只剩一步之遥。玩过这款游戏的朋友最津津乐道的，恐怕就是讨论和研究各种卡牌的搭配方案，让自己能够打败更多的对手，站上更高级的竞技场。
如果我们把工作职场也当作一个Excel水平的竞技场，大家觉得凭借自己已经掌握的这些函数卡牌可以站上哪一级呢？有没有哪些函数或者函数的组合是在某个层级上的必备之选呢？那么接下来的时间呢，我就来为大家描绘一下职场当中的Excel水平层级划分，同时呢根据各个层级为大家推荐一些最有必要了解和掌握的函数和公式技巧。
我们首先看到的是1级竞技场，也就是最初级的水平。在这个层级当中的需求呢，通常是一些最基本的统计需求，例如求和、统计个数、求平均值、最大值、最小值等等；有时候还需要在统计的基础上对结果的精度做一些调整，例如四舍五入并保留几位小数，或者直接保留整数结果等等。
那么在这里需要用到的就是跟这些统计需求相关的函数，比如SUM求和函数和COUNT/COUNTA计数函数，就是使用频率很高的函数，就好像哥布林两兄弟一样。
这几个函数都非常简单易用，掌握这些函数几乎不需要具备什么背景知识或技巧，使用Excel的自动求和功能甚至可以帮你自动生成这些函数公式。
这里唯一需要提醒的，就是要注意COUNT函数和COUNTA函数的区别。这两个函数都是用来统计个数，但前者的统计对象只有数值，而后者会把其他类型的数据单元格也包含其中，只要不是空白单元格，就都会统计进去。
例如上面这张图当中，同样都是对7个单元格进行统计，但COUNT函数的眼里只有那三个数字，剩下的几个名字都被忽略了；而COUNTA函数就把包含数字和名字的这几个单元格一股脑儿统计在内，只把空白单元格排除在外。COUNTA函数名称当中的字母A可以看作是英文“All”全部、所有的意思。
接下来进入2级竞技场。从这一级开始，才算是真正踏上函数公式的道路了。在这里，你需要了解一些有关于函数参数的知识，学会读懂函数的语法（没错，函数公式就像遣词造句一样，也有语法规则），了解每个参数的具体含义是什么，以及参数的不同设置会对结果产生什么样的影响。
在这一级别当中，你需要掌握一些简单的文本处理公式技巧；
了解和掌握多个函数嵌套组合的使用方法，函数就像卡牌，组合起来使用才更具威力；
学会使用IF函数进行一些简单的逻辑判断；
除了获取最大值和最小值之外，还会使用LARGE或SMALL函数提取任意排名中的数据；
除此之外，如果需要进行排班、抽签等涉及公平性、随机性方面的事务，你还有必要了解一下随机函数RAND。
野猪骑士是游戏中很多人喜欢使用的一张卡牌，进攻非常犀利。类似的，在对文本字符串进行拆分处理的一些问题当中，LEFT、MID和RIGHT这几个函数也是出场频率最高的函数，简单而实用。但在一些现实工作当中，各种情况复杂多变，光靠前面这三个函数往往无法做到灵活处理，还需要搭配LEN函数、FIND函数等一些函数来进行配合使用。这就好比野猪同样也需要搭配闪电或冰冻法术来使用一样。
来举两个例子。
第一个例子，有B列这样一列文本，每个单元格的内容是两个站点的名称，需要将其中的两个名称分别提取出来，生成C列和D列这样的结果。这里由于每个站点名称的长度都不尽相同，如果单纯使用LEFT或RIGHT函数很难找到一个统一的第二参数一次性得到全部对象的提取结果。
因此更科学高效的方案是利用每两个站点名称当中出现的标志性字符“至”，比如“新天地至南京西路”中间的“至”、“上海图书馆至静安寺”中间的“至”，利用这个字的分隔性作用，借助FIND函数来找到这个字的所在位置，再根据这个位置进行一些调整，这样就能够确定LEFT或RIGHT函数所需要的第二参数的具体取值了。我们来看一下下面这张图：
先通过FIND函数先查找“至”字所在的位置，将这个位置减去1，就是左侧站点名称的长度；类似的，如果将整个字符串的长度减去“至”的位置，就可以确定右侧站点名称的长度。有了这两个长度结果，最后再使用LEFT和RIGHT函数就可以分别提取出左侧站点和右侧站点的名称了。
所以最终的解决方案可以是下面图中这个样子的，C列使用第12行中所显示的公式，D列的公式显示在第13行当中：
再来看第二个例子：
第二个例子，B列当中有中文和英文数字混排的字符串，比较有规律的地方在于中文字符都在左侧，而字母和数字都出现在右侧，没有相互混杂的情况，现在需要把这两部分分别提取出来，生成C列和D列这样的结果。这个问题应该如何处理呢？
需要说明的是，到目前为止，Excel当中还没有能够自动识别中文还是英文的这样一个函数，但是中文字符和英文字符以及数字之间，存在一个比较隐蔽的差异，就是中文字符都是全角字符，每个字符包含两个字节；而普通的英文数字都是半角字符，每个字符只包含一个字节。利用这个特性，我们可以使用LEN函数和LENB函数分别测量出目标字符串中的字符个数和字节个数，两者对比产生的差异，就可以反映出其中中文字符的个数了。
具体的原理可以看一下下面这张图：
使用LENB函数可以获取整个字符串当中所包含的字节数，比如图上的这些A就代表了字节；而使用LEN函数可以获取整个字符串当中的字符个数，这张图上的这些B就代表了字符；可以很明显的看出来，每个汉字会多出一个A，因此A的总数量会比B的数量多出4个，也就是其中汉字的个数。所以，汉字的个数就等于字节数减去字符数。
所以最终的解决方案可以是上面图中的这个样子，通过LENB和LEN函数分别获取字符串中的字节个数和字符个数，通过两者的差值得到汉字个数，再用LEFT函数将其提取出来；另一方面，英文和数字的个数就等于总字符个数减去汉字个数，做一下数学换算可以知道实际就等价于两倍的字符个数减去字节个数，获取到这个结果以后就可以使用RIGHT函数提取出右边的这些字母和数字，这样就能实现中英文的分离处理了。
所以通过上面的两个例子，我们可以了解到对于一些复杂的字符串处理问题，通常都需要多种不同功能的文本处理函数一起协同工作、配合使用，才能有效的达到目的。
接下来进入到3级竞技场，到了这一层级，就有必要掌握一些有关于日期时间数据的处理方法了。在工作当中很多数据都是跟日期相关的，比如每天的进销存数据、每天每个时刻的用户访问数据、项目计划的时间安排等等，要对这类数据进行有效处理分析，就有必要了解日期的相关背景知识。其中包括日期和数值之间的转换关系、日期的规范化处理以及日期相关的运算方法等等。
其实，在Excel当中，日期的实质就是从1900年1月1日这天开始每天累计递增的一个数字，了解了这个本质特性之后，日期的常规运算都可以转化成数学上简单的算术运算。要处理一些更复杂的日期换算呢就需要用到图上所显示的这些常用的日期函数了，但这些日期函数在使用上也都算不上复杂，只有一些简单的参数设置。
YEAR/MONTH/DAY函数可以从日期当中分别拆分出年/月/日信息；
TODAY和NOW函数可以自动获取系统当前的日期和时间，可以用于建立一些具备到期提醒功能的自动化模型；WEEKDAY和WEEKNUM可以处理与星期相关的问题；
而WORKDAY和NETWORKDAYS函数则主要进行跟工作日有关的运算。
这里我选择了一些有时间限制的游戏卡牌作为他们的象征。
其中值得特别一提的是WORKDAY函数，它可以用来推算若干个工作日以后的具体日期，在一些项目管理的场景中应用较多。在常规的用法当中，这个函数对工作日的定义就是一周当中排除掉周六和周日以后的其他几天。比如下面图中所显示的这个例子：
2016年4月25日之后的第10个工作日的日期，使用WORKDAY函数得到的结果是2016年5月9日，实际上就是把这段日期当中所包含的四个周六和周日都排除在外了，可以看一下下面这张图的示意：
但是除了常规的周六周日之外，有时候也会有一些法定假日不能算在工作日之内，比如今年的五一节，除了30号和1号之外，五月二号礼拜一也是安排为假日。在这种情况下，如何可以把法定假日也排除在外，正确的推算工作日日期呢？
在这种情况下就可以利用WORKDAY函数隐含的第三个参数，来为函数指定一些需要特殊处理的非周末假期。具体操作方法是将这些非周末假期罗列在表格当中，然后使用WORKDAY函数时将第三参数引用这个罗列了假期的单元格区域，就可以正确计算了。具体公式可以看下面这张图。与此类似，NETWORKDAYS函数也可以在计算工作日天数时排除一些特定的假期。
下面这张图就示意了定义过特殊假期以后，WORKDAY函数的实际运算方式：
从这个案例当中，可以了解到，有些函数会包含一些比较隐蔽的参数，这些参数在平常函数的使用中可以不参与不出现，所以往往容易被人忽略，但有一些时候这些参数却能起到非常重要的作用，除了上面提到的WORKDAY、NETWORKDAYS函数之外，类似的情况还有RANK函数的第3个参数、FIND函数的第3个参数、SUBSTITUTE函数的第4个参数等等。
下面图片当中简单罗列了这些需要注意的函数：
图上这些函数当中都包含了比较隐蔽的参数，这些参数平时可以忽略掉，也就是不去使用，但是不应该忽视他们的作用，说不定什么时候就用得上了。
再往上就可以进入到4级竞技场了。
到了这个层级，很多人见到了熟悉的VLOOKUP函数。这个函数是许多朋友对于函数公式的初恋，很多人都是通过这个函数开始认识和了解Excel函数公式的，也有很多人从这里开始体会到函数公式的强大。因为熟悉，所以很多人觉得VLOOKUP函数比较简单，但要把这个函数真正理解吃透还是需要下点功夫。
这就好比游戏当中的巨人卡牌，这张牌从一开始就可以使用，非常强大也非常好用，但是越到后面才越体会到这张牌用得好不好还是有很大差别的；
HLOOKUP函数和VLOOKUP用法几乎一样，所以经常会放到一起来介绍；
接触到这些查询函数之后，难免有时候会出现查找不到目标的情况，因此有时还要对查找产生的错误值进行一些优化处理，IFERROR函数就是这类需求当中一个比较好的选择；
到了这个层级之后，许多公式往往需要进行大范围的填充复制来发挥更大的作用，在这种情况下就必须清楚的理解单元格地址的绝对引用和相对引用这两个概念了；
除此之外，ROW函数和COLUMN函数是表格中的高级计数器，可以对函数公式的大范围应用起到非常重要的辅助作用，要让公式从跑变飞，往往少不了它俩的出手相助，就好比游戏里的狂暴药水一样，非常有用的一种辅助类法术；
COUNTIF函数和SUMIF函数，可以把单一的个数统计和求和运算，变成具备一定筛选功能的统计，这就比原先的统计方式更加提升了一步；
最后一个RANK函数可以在不改变数据原有排列顺序的情况下得到每个数的排名，比排序操作来的更加灵活方便。
以上这些就是这个层级当中这些函数的一个基本介绍。
接下来，我也举一个具体的例子，来详细讲一讲VLOOKUP函数的用法。
很多时候我们都会整理出一些信息表，比如员工信息表、商品信息表、材料明细表等等，有时候我们会需要根据某些关键信息去这些表里面查询与之相关的内容，比如根据员工的姓名去信息表中查询他的学历，根据某个商品编号去信息表中查询它的进货价格等等，在这类需求当中，如果依靠人工查找的话既费时又费力，而使用VLOOKUP函数则可以很轻松的实现大批量的自动查询，因此也有朋友把VLOOKUP函数称为“查表函数”。
比如在上面这张图当中，F列到J列提供了一份培训信息库，里面包含了许多员工参加培训的一些信息。现在B列当中提供了一些待查询的员工姓名，需要根据这些提供的名单，找出他们参加的培训课程名称以及取得的学分。这个问题就是适合使用VLOOKUP函数来处理的一个典型问题。
下面先来看一下VLOOKUP函数的语法规则：
VLOOKUP函数需要设置四个参数来进行具体的查询，第一个参数是查询依据，也就是用于查询的关键信息，例如待查询的某个姓名或是某个商品编号等等。
第二个参数是信息源，也就是需要给出存放具体信息的信息库所在的单元格范围。这里需要注意的一个问题是，不管信息库本身有多少列，必须用可以找到关键信息的那一列作为查询范围的首列。比如假定用姓名作为关键信息进行查询的话，这个单元格范围就必须以信息库当中的姓名列作为首列，而是要编号进行查询的话，就必须以编号列作为首列。
下面这个图上就给出了一个具体的例子，比如我希望根据姓名作为关键信息来查询学历，那么第二参数选择信息库范围时就应该从姓名列开始一直包含到学历列；而如果希望根据编号来查询学历，那么第二参数的选择范围就要从编号列开始选取一直包含到学历列为止。
第三个参数要给出目标位置，也就是想要查询的具体信息在信息源范围当中的相对列序号，这句话听上去比较拗口，实际操作的时候可以这样来确定：假定希望根据姓名找出学历信息，那么在信息库当中以姓名作为首列，从姓名列开始一直到学历所在的列，总共包含了多少列，就是这个参数的取值。
比如在前面的图中可以看到，如果用姓名查学历的话，那么第三参数就是5，从姓名列到学历列一共包含了5列；如果用编号来查询学历的话，那么第三参数就应该取6，其中一共包含了6列。
第四个参数用来设定VLOOKUP函数的工作模式，通常取值为0表示工作模式为精确查找。
搞清楚这四个参数的具体含义和设置规则之后，就不难写出整个VLOOKUP公式了。在前面那个案例当中使用这个公式，就可以完成几个人的信息查询，具体可以看一下下面这张图：
这张图上，查找课程名称和查找学分使用了两个不同的VLOOKUP公式，其中的区别就在于第三参数的设置上，需要根据具体信息存放的位置来调整，一个设成2，另一个设成4。
在这个结果当中，我们看到庄伟、王美芬、黄桂晶这几个人的相关信息都用公式查找到了，但是周爱晖的查询结果显示了两个错误值#N/A，这是因为在F列提供的培训信息库名单当中没有这位员工的相关信息。因此VLOOKUP函数会得到一个错误值，以此来表示没有找到目标的这么一个结果。与此类似的，LOOKUP、MATCH等其他一些查找函数也会在查询不到目标的情况下返回这样的错误值。
有些朋友觉得错误值太扎眼，希望进行一些优化处理，不让这些错误值显示出来，而是使用一些更加友好的提示信息来替代，比如“信息不存在”等等。这类需求就可以派IFERROR函数来登场了。
IFERROR函数和IF函数的用法有些相似，但也有不同。它只需要两个参数，第一个参数是需要判断是否出现错误值的对象，通常是另外的某个公式，比如我们上面所使用的VLOOKUP查询公式，如果这个对象没有出现错误，就直接得到它的结果，如果出现错误，那就得到第二参数中设定的另外一个结果。因此，我们把这个函数用上，就可以让前面案例中的结果显得更好看一些：
从这张图上可以看到，我们把VLOOKUP函数这部分作为IFERROR函数的第一个参数来使用，如果VLOOKUP函数能够正常查找的话，就直接显示结果了；如果找不到目标产生错误值呢，那么就用IFERROR函数第二个参数中所设定的结果来替代。所以周爱晖的查询结果就会显示“无”这个字表示没有找到。
更进一步就来到了5级竞技场。
在这一级别当中，你应该了解更多的查询引用函数例如MATCH函数和INDEX函数，来处理一些更加复杂的查询问题，例如交叉查询。这两个函数经常配合起来一起使用，就好比游戏当中白王子和黑王子两张卡牌；
与他们关系密切的还有OF
