Excel大拿的养成：六脉神剑

前言

之前的一篇《三大绝招，助你笑傲Excel江湖》好像还蛮多人喜欢的，所以今天又根据以前的一点经验整理了另外6种也是很有用的，同时也挺简单的Excel用法，希望大家喜欢。文章末尾有文中举例用的Excel源文件，已通过百度网盘分享，有需要的话请自行下载哟！

六脉神剑

话不多说，直入正题吧！

【1】相对引用和绝对引用

默认情况下，单元格引用是相对的。

包含相对单元格引用的公式会因为你将它从一个单元格复制到另一个而发生改变。所以，我们经常利用这一特性来复制公式，快速计算使用相同算式的不同行或列的数据。

不过，也不是任何时候我们都想有用相对引用的。比如下面的例子中，C4单元格中的公式为

=B1*B4

它包含了2个相对引用：（B1）和（B4），当把C4单元格拷贝到下面的单元格C5时，公式变为了=B5*B2

很明显这不是我们想要的结果。

相对引用和绝对引用1

所以，如果复制公式时希望保持原始单元格引用，需要在单元格的行和列引用前都加上美元符号 ($) 来将其“锁定”。【每个单元格都是通过1个行引用和1个列引用来确定的，所以要锁定单元格，必须同时在其行引用和列引用前都加上美元符号（$）哦！】

让我们来修改一下上面的公式。把C4单元格里的公式修改为

=$B$1*B4

它包含1个绝对引用（$B$1）和1个相对引用（B4）。

同样地，复制C4单元格到C5单元格，或者鼠标拖动C4单元格右下角黑色十字到C5单元格，这时C5单元格的结果就是我们所希望得到的了。

相对引用和绝对引用2

一个快捷操作小技巧：

把光标放置在想要改变引用类型（相对、绝对）的单元格引用后面，比如这里就把光标放在“B1”后面，然后按一次“F4”键，看到了吗？“B1”变成了“$B$1”。方便吧！

延伸1：如果你继续按“F4”键的话，你会看到如下的变化：

“$B$1”→“B$1”→“$B1”→“B1”。

具体什么情况下用呢？聪明的你也已经看出来了吧？我们在复制或者拖动公式的时候，除了沿着纵向列，也可以沿着横向行的方向操作哦！

延伸2：Excel 2016 for Mac还可以按“command + T”组合键来代替F4。（其他版本Excel未验证，请查看Excel帮助文档哟）

【2】“&”符号

“&”（Ampersand）符号，是Excel中的连接符，它可以看作是进行文本运算的一种特殊运算符号。就好比，+—*/是算术运算符，^是幂运算符，它们的运算原理都是利用了Excel中的数据类型自动转换（日期、文本形式的数字、货币在参与算术运算时都自动转换为数字，而其他多数类型的数据则在参与文本运算时都自动转换为文本）来实现的。

“&”符号可以把多个单元格的内容连接在一起，放到另一个单元格里面。例如：

&符号

试想一下，某些情况下我们需要快速地获得多个单元格的内容合并在一起的结果，如果是先复制所有单元格，再一个一个地摆在一起的话，那得多费劲啊！我之前汇总类似上图中的客户信息并发送给其他相关人员时，就是用一个简单的&符号就轻松搞定了！加班的计划也顺势改成了聚餐Happy，哈哈！

（和这种情况相反的情形，我们可以使用Excel的另一种便捷功能：拆分。这里就不展开了。）

【3】条件格式

条件格式，可以根据单元格的内容来使单元格呈现特定的颜色，从而起到提示、提醒作用。（在之前的文章《三大绝招，助你笑傲Excel江湖》中已有介绍与Len函数结合的用法，在此再简单地介绍一下。因为条件格式真的很简单很好用！）

比如，制作一个简单的“个人月度预算表”，提醒自己哪些项目超支了：（数据纯属虚构）

条件格式

上图所示的条件格式的设定是：当差额大于0时，单元格显示为淡红色。（表示超支了）

当然，条件格式还有很多用法、很多适合的场景，请众简友们发挥自己的聪明才智和无尽想象力尽情地发挥吧！嘻嘻！

【4】VLOOKUP函数

在表格或区域中按行查找内容，可以使用VLOOKUP函数。

它是一个“查找和引用”函数，也就是说，它会在指定的范围里查找有没有与条件相匹配的数据，然后返回一个被引用的数据。简单地说，就好比是按照“门牌号”找到房屋的“主人”。

我们来看下面这个简单的例子：

图中右边两列的“月份”和“工资”是我们的数据库，左边的“三月”、“四月”、“五月”是我们的“门牌号”，现在如何从数据库中拿到四月的工资信息呢？在C4单元格中输入如图所示的公式即可：

VLOOKUP函数

现在来看看到底VLOOKUP函数括号里面的各个参数都代表什么含义吧！

第一个是LOOKUP_VALUE，表示需要查找的内容或值，即“门牌号”；

第二个是TABLE_ARRAY，表示去哪里查找，即“门牌号”和“房子”；

第三个是COL_INDEX_NUM，表示返回的引用在数据库的第几列；

第四个是RANGE_LOOKUP，值分别是False或True，其中False表示精确查找、匹配，而True或缺省表示模糊查找、匹配。

小技巧：

我们还可以利用“公式生成器”来更便捷地录入公式哦！首先把鼠标放在准备录入公式的单元格上，然后点击上方公式输入框上的“fx”，这时会在表格右边弹出“公式生成器”来，我们根据提示一个一个地录入相关内容就可以了。非常轻松哦！

【5】IFERROR函数

使用IFERROR函数可以捕获和处理公式中的错误。如果公式的计算结果有错误，则返回指定的值；否则正常返回公式的结果。

我们在使用公式时，有时难免会遇到计算结果是一些错误的情况，比如

“#N/A”、“#REF”、“#DIV/0!”等。

这些错误会阻止我们进一步地使用这些单元格，或者再参与另外的一些计算等等。那么有什么好方法能让结果不这么难看、单元格也正常一些吗？有的，那就是IFERROR函数。

具体举例来看，比如当我们用B4（100）除以C4（0）时，单元格结果显示“#DIV/0!”，

IFERROR函数1

那我们就把公式改一改，写成这样：

=B4/C4 改写成 =IFERROR(B4/C4,0)

IFERROR函数2

这就是告诉Excel，首先计算B4/C4，如果计算结果有错的话，那就在单元格中显示IFERROR函数括号里的第2个参数，这里是“0”。这样结果就正常多了！

当然，第2个参数的值，只要你喜欢，想写什么都可以哦！

另外，如果第1个或第2个参数是空的单元格，那么IFERROR会将其视为空字符串值 ("”)。

【6】COUNTIF函数

COUNTIF 是一个统计函数，用于统计满足某个条件的单元格的数量。当我们需要持续了解某种条件的值或者得分的数量情况时，这个函数非常有用！

比如，统计我几个月以来的保龄球得分情况，

COUNTIF函数

我想知道有多少局的得分超过了200分，于是在C14单元格里写下公式：

=COUNTIF(B4:D10,">200")

这个公式告诉Excel，在B4:D10的范围内进行搜索，然后根据条件">200"统计满足的数据个数。

同样的道理，想知道有多少局的得分低于150分，就用公式：=COUNTIF(B4:D10,"<150")。

提醒

从Excel复制到简书的公式，里面的英文半角双引号老是被简书自动改成了汉语的双引号，上面的两个公式是我刚刚手动订正了的。上一次的文章就是因为这个问题，导致一位简友直接复制文章中的公式到Excel之后，Excel给报错了！我只想说我真的是无辜的。

结语

Microsoft Excel博大精深，但正如我之前说的，我们没有必要为了使用工具而使用工具，我们的目标是解决问题和完成任务！（当然，还有提升自我。）所以，我们并不需要了解和掌握Microsoft Excel的所有相关知识，而只需要掌握那些我们有必要掌握的就可以了！衷心希望今天的这篇小文章能给大家提供些许参考和帮助，让大家使用Excel处理业务和问题时更加得心应手！

举例所用Excel源文件

【https://pan.baidu.com/s/1bpJxVDd】