getvalue参数计数不匹配_90%的人不知道的函数进阶姿势

本文是Excel的正确学习姿势系列的第四篇文章，要想了解该系列文章的目的，可以
参考第一篇文章Excel的正确学习姿势：从入门到巅峰

函数是Excel中的重头戏，高手们都会灵活使用函数，excel函数的介绍提供了基本用法，而到实际工作中，它的运用是千变万化的。

当你能把Excel函数运用到极致时，就是你“称王称霸”的时刻啦。

而90%的人都不会如何高效的学习、运用函数，其实你是没有掌握正确的学习姿势。

本篇文章会引导你一步步深入到函数的世界，探索函数的奥秘。

根据日常的工作内容，我把函数分成三大类型：数据清洗类、计算统计类和关联匹配类，学习难度也会依次增加。

在讨论三大函数类型之前，简单说下如何学习函数最高效。

大部分看到函数就头痛，因为有些公式很复杂，而且函数众多，比较难记忆。

而实际上最有效的学习函数的方式是分解函数——理解每个函数参数的意义——在实践中加以运用。

01 抛砖引玉的If和Vlookup函数

拿最常用的if函数为例：

if函数的基本功能就是条件判断，满足某个条件就返回一个值，反之就返回另一个值。

=if(logical_test,[value if true],[value if false])

它共有三个参数，逻辑判断、真值和假值，那明白每个参数的含义要运用函数就比较容易了，其实参数不用去死记硬背，能理解函数的作用，知道参数的基本含义就可以达到目的。

上述案例是比较简单，那我们换一个复杂一些的函数vlookup。

vlookup号称函数界的一枚查询神器，几乎无人不知，是提升工作效率的必备函数。

它的基本作用是搜索表区域首列满足条件的元素，确定好行序号，再返回单元格的值。

它的函数参数如下：

=vlookup(lookup_value,Table_array,col_num,type)

通俗来讲vlookup做了四件事情：

• 找哪个值
• 从哪个区域找
• 找到后返回什么值
• 返回时是精确查找还是模糊查找

我用下图的案例来解读vlookup函数，A5：D9区域是员工信息、年龄和工资，现要求查询小黄的年龄，如何做？

我们在G6单元格输入公式：

vlookup第一个参数是要查找的值，即F6单元格的“小黄”，第二个参数是查找区域，我们需要在B5:D9单元格区域内查找，第三个参数是匹配值的列序号，我们要返回年龄列的某个值。

而年龄列是从查找列开始往右数第二列，即填上数字2，第四个参数是精确匹配还是模糊匹配，这里我们选择精确匹配即可。

vlookup有一个要求，是必须查找的值在查找区域的第一列（从左往右数），上述案例中“姓名”列需在B5:D9区域的第一列，否则函数会报错。

当你很清楚函数的作用范围和参数使用方法时，你会发现函数不需要死记硬背，在理解的基础上学习函数是最佳的学习途径。

当然，上述案例可以使用上章节我们提到的“数据有效性”来做一个姓名工资查询器，如下图所示。

你是觉得Excel界的查询神器就这些简单的功能吗？

Too young too simple !

它有其他屌炸天的功能：

• 反向查找

vlookup默认要求查询值在查询范围的首列，因为它是从左向右查找的，那若我的查询值不在第一列怎么操作？

vlookup是可以反向查找的，把查询顺序变为“从右向左”查询，方法如下动图所示：

=VLOOKUP(H4,IF({1,0},D4:D9,C4:C9),2,0)

反向查找的关键在于if字段，IF({1,0},D4:D9,C4:C9)是实现反向查找的关键，相当于将数组D列和C列互换，这里1和0不是实际意义上的数字，而是1相关于TRUE，0相当于FALSE。

当然，有一种取巧的办法，是把查找列直接移动到最左边即可，简单快捷。

• 多条件查询

日常工作中查询值一般是一个字段，有时会有两个以上的字段出现，需要同时满足两个条件，才能获取到匹配值，vlookup也可以做到多条件的查询。

上图中，需要同时满足“华北”区域和“销售部2”部门两个条件，获取销售额。

具体做法可以参照下列动图：

整体公式如下：

{=VLOOKUP(H4&I4,IF({1,0},C4:C10&D4:D10,E4:E10),2,0)}

1. H4和I4是查询值，用“&”符号连接起来；
2. if条件查询，相当于组合了一个新的查询区域，把CD两列看成是一列，E列看成是第二列；
3. 公式首尾的大括号不是手动加上的，是Ctrl+Shift+Enter三个组合键联合起来的，是数组公式。

02 经典函数

vlookup函数只是Excel函数界的“开胃菜”，那下面两个函数可谓经典中的经典。

index函数

Index函数属于一种查找定位函数，它可以通过行号和列号定位具体值，它的语法如下：

INDEX(array,row_num,[column_num])

array是选择区域或者数组，row和column代表行和列，下面我举几个例子来说明函数的功能。

• 单行或者单列取数

下图中要获取小王的8月份销售额，使用以下公式：

= INDEX(5:5,9)

5:5代表第五行数据，9代表第9列（8月在第9列）

单列也是同样的原理，换一个行参数，省略列参数即可。

• 定位匹配

index的第一个参数既可以是数组，也可以是某个数据区域，相当于一个定位数据的功能。

D5:O7代表所选数据区域，2代表是小李在所选数据区域的第二行，9代表所选数据的第9列，相当于它把单元格的方框移动到你想要的位置。

Index函数简言之是给它一个位置序号，它就能锁定该位置的值。

match函数

match函数是index函数的好搭档，也是一种查找匹配函数，它主要作用是确定某个值的位置序号，返回该值的行序号或者列序号，语法如下：

MATCH(lookup_value, lookup_array, match_type)

lookup_value代表查找的值，它可以是数值、文本、单元格引用；

lookup_array是指查找的连续单元格区域；

match_type代表查询的方式，为数字-1、0、1，参数省略时默认是1，精确查找为0，其余是模糊查找。

直接看下图的案例，你会更加容易理解这个函数的功能。

案例想获取销售员小李的行号（在小王、小李、小郑这三个区域内），通过match函数即可返回小李的行号是2，代表小李在所在区域内的第二行。

index+match的黄金组合

index和match函数是一对形影不离的好搭档，功能比vlookup强大10倍。

一个是返回数据的行序号或者列序号，相当于知道了数据所在单元格的位置，另外一个是通过位置来精确匹配数据。

可以用下面的案例来深度解读下这个组合函数。

公式如下：

=INDEX(D5:O7,MATCH(C12,C5:C7,0),MATCH(D12,D4:O4,0))

第一个参数代表一个数据区域；

第二个参数是行号，用match函数返回销售员“小李”的行序号；

同理第三个参数是列序号，用match函数返回月份为“3月”的列序号，

通过上述两个函数的组合运用，就可以匹配到销售员小李3月的销售额了。

---

它与vlookup相比有以下优势：

• 不受数据区域的限制，即使插入行和列，也不会影响匹配结果
• 可以反向查找，不必从左到右查询数据
• 可以实现hlookup的功能
• 使用的是动态数据引用，可以制作动态数据图表

vlookup和index+match函数的介绍只是抛砖引玉，来说明学习函数时如何快速学习，并达到灵活运用的目的。

同时不要被函数的基本功能所限制，要在充分理解函数基本功能的基础上，熟练灵活的使用函数，同一个问题可能有多个函数的解法，关键是你能想到用哪一种方式快速解决问题。

03 函数的三大类型

通过vlookup等函数的示例，大家也能清楚看到函数的作用和适用范围，通过上述行之有效的学习方式，不管什么函数，你都能快速学习并运用。

接下来谈下常用函数的三大类型：数据清洗类、计算统计类和关联匹配类。

数据清洗类

• Trim

清除字符两边的空格

• Concatenate

合并单元格的内容，功能类似于“我”&“很”&“帅”=“我很帅”，相当于拼接单元格内容，可以指定拼接的符号，在多个拼接单元格时效率更佳。

（用Text函数也可以达到同样的效果）

• Replace

替换函数，可以指定从某个字符开始替换，替换几个字符，替换为什么字符。

• Substitute

也是替换函数，但是是全局替换，不区分字符的起始位置。

• Left/Right/Mid

截取字符串字符的函数，从左、右或者中间开始截取对应的字符串，截取多少位。

• Find

查找函数，查询某个字符串的出现位置，经常与left/right/mid函数结合起来使用，对数据清洗很有帮助。

• Text

将数值转化为指定的文本格式，常与其他函数结合使用。

总结：数据清洗类函数一般是替换、清除、修改、查找等功能，作用是把杂乱、不规则数据转化为干净的数据。

计算统计类

• Sum

最常用的求和函数。

• Sumif/Sumifs

属于sum和if函数的结合，简称条件求和，sumif和sumifs的区别在于一个是单个条件求和，一个是多个条件求和，是需重点掌握的求和函数。

• Count

最常用的计数函数。

• Countif/Countifs

属于count和if的结合，跟sumifs类似，属于条件计数，若满足指定的条件，则返回计数的值。

• Max/Min/Average

属于对某个区域的数据求最大值、最小值和平均值。

• Rank

排序函数，类似于排序功能，返回某个值在一个区域内的排名。

• Round

数据求整函数，对数据进行四舍五入变成整数。

• Mod

求余函数，返回两数相除的余数。

• Sumproduct

是跟统计求和相关的函数，若分别有销量和单价三行数据，不需要每行斗分别求和，直接使用sumproduct函数即可一键求和。

• Datedif

计算两个日期的差值，比如计算工龄、年龄等等，另外这个函数是一个隐藏函数，在公式的函数中是找不到的，在单元格中直接输入即可。

总结：计算统计类函数主要包括求和、取整、最值、平均值，计数等，它们是常用基础的数据统计功能，使用频率相当高。

关联匹配类

• Vlookup/Hlookup

都是最常用的关联匹配类函数，hlookup跟vlookup的区别在于匹配时方向不同而已，模式一样。

• Index+Match

属于位置查询函数，两者常常结合使用，制作动态图表时常使用这对组合来动态匹配数据。

• Row/Column

返回单元格的行号或者列序号的函数，一般嵌套在其他函数中使用，可用于动态引用行列。

• Lookup

这是一个被大多数人忽视的函数，它实际上比vlookup更加强大，功能更加灵活，之后可以写写这个函数的“来龙去脉”。

• Offset

高手的制作动态图表必备函数，相当于建立一个坐标系，通过单元格数据的位移返回某个单元格或者单元格区域。

总结：关联匹配的函数是函数中比较有难度的部分，参数多，使用场景也很多。而众多Excel高手对关联匹配函数都是运用自如的，同时把上述函数嵌套在一起实现多种需求。

函数的世界可谓“深不见底”，上述列举的函数只是冰山一角，本文提出的是一个学习函数的方法，你无需掌握400多个函数，你只需要掌握常用函数的基本原理，并在工作中不断实践即可。

一个工作难题也许有上百种函数解答方法，选择其中你擅长的函数来解答才是正道，不必纠结于使用多么炫酷的函数技巧。

通过对基础的函数的学习，拥有举一反三的能力是通向函数高手的必经之道。

另外，要学会搜索函数的用法，基本上所有函数都在下图的选项卡中，可自行学习查阅。

公式菜单栏

当然，还有其他类型的函数，比如date、weekday等函数，这些函数大部分“字如其名”，一看就能知晓函数的基本功能，使用起来也方便。

---

我们先解答下上篇文章的困惑。

上篇文章翻滚吧美队：Excel的五层修炼：入门篇（2）在后面提了一个带颜色的温度计问题，这次我们通过简单公式和条件格式组合运用来解答此题。

颜色温度计效果图

上图是我们的效果图，而制作方法的动图如下：

制作方法

原理就是通过比较当前温度和温度计的温度，以此作为条件，来形成带颜色的条件格式。

格式上可以选择三色刻度，颜色都可以依据个人喜好自由调整，上图设置当温度=35度时，颜色填充为白色。

---

老规矩，来一道运用函数的小考题：

下图中有三个省份和各自省份的地级市名称，要想办法做一个表格，达到选择某个省，那市就会自动匹配到对应省的级别下，比如说选择广东，就会出现广州、深圳等市可以选择，而不会出现湖南和湖北下面的地级市。

答案会在下一篇文章中揭晓。

据说点赞+关注的朋友今年会“猪”事顺利~~

欢迎关注公众号：蜂鸟数据。