【原】菜鸟的Excel函数修炼手册 第1课 公式函数基础知识——公式三板斧第一式

Excel基础学习园地

公众号“Excel基础学习园地”是一个免费发布Excel基础知识、函数应用、操作技巧、学习方法等资讯的公众号，请点击上方“Excel基础学习园地”添加关注，方便我们每天向您推送精彩资讯。

1.1 一个概念和六个符号

所谓公式三板斧，是指要想用好公式函数，必须熟练使用的三项基本功。

第一式：是一个符号的熟练使用，这个符号就是$；

第二式：是两个值的熟练使用，这两个值是TRUE和FALSE；

第三式：是三种数列构造方法的熟练使用，这三种数列是等差数列、循环数列和重复数列。

第一个概念：什么是公式？

其实很简单，在Excel中，公式就是以等号（=）开头的可以得到一个结果的表达式。

例如，在单元格中输入=5+3，回车后就会看到8，结果如图1-1所示。

                    图1-1

六个符号：第一个符号在上面的例子中已经看到了，就是加号：“+”。

另外的五个符号是：减号（-）、乘号（*）、除号（/）、乘方（^）和百分号（%）。

在Excel的公式中，与数学算式不同的是，乘号（×）用星号（*）代替，除号（÷）用斜杠（/）代替；乘方运算用符号^，这个符号使用shift和6键即可输入。例如，10的5次方，数学式为：105，Excel中则是：=10^5。

这六个符号中，百分号（%）是一个比较有意思的符号，看一个例子：

在C2单元格输入一个数字1200，在D2单元格输入公式：=C2%，回车后可以看到计算结果为12，如图1-2所示。

  图1-2

这和公式=C2/100的效果相同，也就是说，一个%相当于将数字缩小100倍。那么问题来了，如果想把一个数字缩小一万倍，用%该怎么写公式？

想要学好函数，建立自信心是非常重要的，在图1-3所示的问题中，大胆的去验证自己的想法，这是你入门的第一步。

 图1-3

通过以上内容，我们知道了什么公式，还认识了六个算术运算符。

算术运算符的用法通过图1-4可以有更加直观的理解：

 图1-4

提示：在Excel公式中，数学算式里的中括号和大括号一律用小括号代替，中括号和大括号在Excel公式中有其他的含义，后文会有详细介绍；公式中的所有符号请在英文输入模式下完成。

在Excel中，除了算术运算符之外，还有一类非常重要的运算符：比较运算符。

下一节内容将告诉你：会做比较也是一门手艺。

本节要点：

1. 公式必须以等号开头；

2. 在输入公式时请关闭输入法，或者切换为英文状态；

3. 要敢于测试自己的想法，电脑不会因为公式错误而损坏，地球也不会因为公式错误就不转。

1.2 会做比较也是一门手艺

上一节我们知道了算术运算符一共有六个，非常巧合的是，比较运算符也是六个，分别是：=（等于）、>（大于） 、<（小于）>=（大于或等于） 、<=（小于或等于） 、<>（不等号）

这几个符号的意义很好理解，需要强调的是：Excel中，大于或等于是>=，不是≥，小于或等于是<=，不是≤，不等于是<>，不是≠。

例如：=(A1<=b1)，=(a1>B1)*10，=A1=”优秀”，这三个公式中用到了比较运算符，比较运算的结果是逻辑值，逻辑值的重要性在本书的开头就说了，那是三板斧的第二式。

以下通过两个实际案例说明比较运算的强大：

示例1：计算年假。

仅使用比较运算就可以计算出每个员工的年假天数，公式为：

=(C2="是")*(5+(B2="女")*3+(E2>=10)*5+(D2>=40)*2)，结果如图1-5所示。

 图1-5

示例2：天然气的阶梯计费计算。

阶梯计费也是生活中很常见的一类问题，同样只用比较运算就能完成阶梯计费的计算，公式为：

=D2*1.7+(D2>300)*(D2-300)*0.3+(D2>500)*(D2-500)*0.5，结果如图1-6所示。

 图1-6

由此可见，会做比较确实是一门手艺，关于比较运算和逻辑值的原理，在第2.1.1节“逻辑值不是从石头缝里蹦出来的”中会详细介绍，本节了解比较运算符的写法即可。

在Excel中，有一个专门用来连接数据的运算符&，这个符号使用shift和7键输入。

动手试一下，A11单元格中输入：学习，B11单元格中输入：公式函数，C11单元格中输入：=A11&B11，回车后可以看到，这个公式会把两个单元格的内容连接起来，如图1-7所示。

 图1-7

使用&得到的值是文本类型，例如，公式=3&5可以得到35，但这个35是文本类型的数字。关于数字类型的知识将在第6.6节“真假数字”[A1] 中详细讲解。

在Excel的公式中，还有一个非常重要的符号，下节内容将为你详细介绍这个Excel中最值钱的符号。

1.3 最值钱的一个符号

使用Shift键和4键可以得到一个符号：$（英文输入状态下操作）。这个符号涉及三个概念：相对引用、绝对引用和混合引用。

1.引用

下面介绍一个在公式中非常重要的概念：引用。

引用给Excel公式运算带来了极大的便利，在使用公式的过程中无须逐一输入数值，可以直接引用单元格或单元格区域进行计算，而且在更改单元格中的数值时，公式将自动根据更改后的数值更新计算结果。例如根据定价和降价比例计算折扣价的时候，公式为=B2*(1-C2)，B2和C2就是引用，如图1-8所示。

  图1-8

当定价或者降价比例变化时，我们只需要修改B2或者C2的内容，公式就会自动计算出最新的折扣价。例如，男式外衣定价从720调整到680，降价比例从25%调整到20%，折扣价会自动计算出结果，如图1-9所示。

 图1-9

2.相对引用

明白了什么是引用，再来看什么是相对引用。

在复制公式时，公式中引用的单元格会自动改变，而与复制到的单元格的相对位置保持不变，这种引用即为“相对引用”。

在D2单元格输入公式：=B2*(1-C2)，复制公式后，查看D5单元格的公式，可以发现公式中引用的单元格自动从B2、C2变成了B5、C5，如图1-10所示。

 图1-10

可以显示公式再查看变化，这样会更直观，如图1-11所示。

 图1-11

记得再次按下显示公式按钮，恢复正常状态。

3.绝对引用

在实际应用中，有时候需要公式中的引用位置一直保持不变，这就要用到绝对引用，在图1-12所示的例子中，所有商品的降价比例都要从B12单元格进行引用。

 图1-12

要在公式中使用绝对引用，可以按照以下步骤操作：

（1）在C15单元格输入：=B15*（1-B12，如图1-13所示。

 图1-13

（2）B12单元格要使用绝对引用，按一下F4键（一些键盘需要同时按fn键），B12会变成$B$12，再输入）后按回车键，结果如图1-14所示。

 图1-14

复制公式后，观察C18单元格，与C15单元格比较发现，公式中引用的B15单元格变成了B18，而$B$12保持不变，如图1-15所示。

 图1-15

4.混合引用

除了相对引用和绝对引用，还有一种“混合引用”。在输入公式时，选定公式中的单元格，重复按下F4键，即可切换绝对引用、行的绝对引用、列的绝对引用以及相对引用。

在图1-16所示的示例中，存在两种不同的降价比例，计算折扣价时就需要用到混合引用 。

 图1-16

要在公式中使用混合引用可以按照以下步骤操作：

（1）在C24单元格输入=B24，如图1-17所示。

 图1-17

混合引用有两种方式：固定行或者固定列，具体使用哪种方式对于新手来说是个难点。

要结合公式复制的方向来考虑，当公式向下复制的时候，会变成B25、B26……当公式向右复制的时候，会变成C24、D24。由此可知，需要固定的是列，确保公式在复制的时候，定价所在的列不会变化，因此需要在B前面添加$，可以按三次F4键，直到变成$B24，如图1-18所示。

 图1-18

（2）继续输入公式，=$B24*（1-C22，如图1-19所示。

 图1-19

C22又该使用哪种混合引用呢？向下复制就会变成C23，就不对了，向右复制变成D22这没问题，因此需要固定的是行，按两次F4，直到变成C$22，继续输入）后回车，如图1-20所示。

 图1-20

将公式复制到其他需要计算的单元格后，对比C24与D27可以看出混合引用的效果，如图1-21所示。

 图1-21

关于相对引用、绝对引用和混合引用，一共有四种形式，如果去死记硬背的话，很难！但是只要理解了$这个符号的意义，用很简单的两句话去记就好了：不管行还是列，给钱它就不动。

本节介绍了在编辑公式的时候直接用F4功能键切换引用方式，如果是修改已有的公式，可以根据实际需要在合适的位置按Shift和数字4输入$即可。

提示：千万不要去死记硬背，你可以按照自己的想法去写公式，然后横向和纵向复制公式后查看结果是否正确，如果不正确的话，再去判断需要固定的是行还是列，只要搞清楚这个点，一切都不是问题了。

1.4 再来一个概念和五个操作

1.4.1 最重要的概念来了

前3节学习了公式、运算符和引用的概念，本节要介绍的是函数。

在Excel中有大量预设的函数，每个函数在使用不同的参数后都可以得到各种结果，函数可以看作是Excel预置的公式，输入函数和参数后，Excel将自动进行一系列的运算，并得出最终结果。

例如：=SUM(A:A)，这个公式用到了SUM函数，可以实现求和功能。

新手最迫切想知道的问题通常是：函数在哪里？怎么输入函数？下面介绍三种输入函数的方法。

1.通过“插入函数”按钮

通过“插入函数”按钮输入函数，如图1-22所示。在Excel中单击fx按钮或者同时按下Shift键和F3功能键，就可以打开“插入函数”对话框，如图1-23所示。选择全部函数，找到需要的函数后，可以查看函数说明或者单击有关该函数的帮助。

 图1-22

 图1-23

2.通过菜单输入

通过“公式”选项卡，在函数库中选择并输入函数，如图1-24所示。

 图1-24

3.手工输入

初学阶段建议使用第一种方法，熟练后可以直接输入函数。

要学习和使用函数，就必须了解函数的结构特点。

函数的结构：函数名，一对括号，参数；每个函数的参数数量不尽相同，两个参数之间需要用逗号分开，如果仅有一个参数，就不需要逗号，也有的函数是不需要参数的。

例如：=NOW()，这个函数前面加等号，可以直接构成公式，得到当前日期和时间，是个不需要参数的函数。

=SUM(A:A,D:D)，这个函数有两个参数，即A:A和D:D，两个参数之间用逗号分开，公式得到A列和D列所有数字之和。4.公式和函数的关系

函数是公式的一部分，公式中使用不同的函数可以实现多种多样的功能。公式中除了函数，还可以使用运算符、引用和常量。常量是不用计算的值，如果在公式中使用常量而不是单元格的引用（例如：=30+70+110），则只有在自己更改公式中的常量时其结果才会更改；汉字、字母、数字、符号、日期、时间这些都可以作为常量。

在A1单元格输入一个1到12之间的数字，B1单元格输入一个1到20之间的数字，C1单元格输入公式： ="今天是"&A1&"月"&B1&"日" ，可以根据A1和B1的内容得到一句话，如图1-25所示。

 图1-25

这个公式里用了三个常量（都是汉字的）和两个引用（A1和B1），还用了文本连接符&。

提示：在公式里（包括函数里）使用常量时，只要不是数字，常量一律要加双引号（是英文状态的）。

1.4.2 公式操作第一式：批量创建公式

示例：需要计算出每名业务员四个季度的完成率，公式很简单，直接在D3单元格输入：=C3/B3即可，如图1-26所示。可是这么多地方都要用公式，怎么才能一次就创建所有的公式呢？

图1-26

下面讲解操作步骤：

（1）选择数据区域，如图1-27所示。

 图1-27

（2）按下Ctrl G 组合键（或者F5功能键），打开“定位”对话框，单击“定位条件”按钮，如图1-28所示。选择“空值”后单击“确定”按钮，如图1-29所示。

 图1-28

 图1-29

可以看到，所有需要填写公式的单元格都被选中了，接下来不要单击表格的任何位置，直接输入公式：=C3/B3。

见证奇迹的时刻到了，请你按住Ctrl键再按下回车键，所有的公式一次完成输入，如图1-30所示。

 图1-30

最后请你同时按住Ctrl和shift键，再按下数字5键，完成率就会以百分比的形式呈现，如图1-31所示。

 图1-31

1.4.3 公式操作第二式：快速复制公式

示例：要计算每个学生的总分和平均分，总分：=B2+C2+D2；平均分：=E2/3，如图1-32所示。

 图1-32

与上一节示例的区别在于，总分和平均分使用的公式不同，显然不能一次完成所有的公式创建，遇到这种情况，就需要掌握快速复制公式的技能了。准备好了就一起操作：

首先需要在每一列写出第一个公式，然后将鼠标放置于公式所在单元格的右下角，当光标变成“➕”时，双击鼠标左键就可以完成整列公式的复制，如图1-33所示。

 图1-33

用同样的方法可以完成平均分的计算，完成后的效果如图1-34所示。

图1-34

提示：还可以将总分与平均分的第一个公式写完后同时选中，双击填充柄，一次完成两列公式的复制。

1.4.4 公式操作第三式：批量修改公式

掌握了批量创建公式和快速复制公式的你是不是觉得很有成就感，先别急着开心，看看假如你遇到下面这种情形会如何去处理。

示例：图1-35是一张已经完成了公式编辑的表格，原定的佣金比例是18%，佣金的计算公式是=B2*18%。现在要把佣金比例调整为15%，这就需要将所有的佣金计算公式进行修改。接下来要学习如何批量修改公式的技能。

图1-35

首先要使用定位功能。按下Ctrl G组合键（或F5功能键）打开“定位”对话框，单击“定位条件”按钮，弹出“定位条件”对话框，选择“公式”单选项后单击“确定”按钮，如图1-36所示。

 图1-36

有公式的单元格都被选中了，不要点击任何位置，直接按下Ctrl h组合键打开查找替换对话框，在【查找内容】中输入18%，【替换为】中输入15%，点一下全部替换，如图1-37所示。

 图1-37

所有公式一次完成修改，结果如图1-38所示:

 图1-38

1.4.5 公式操作第四式：隐藏和保护公式

在使用公式的时候，会有显示公式和隐藏公式的需要，显示公式的方法在1.3节已经介绍了，本节重点介绍隐藏公式的方法。

要隐藏公式只需要三步就能实现，第一步：使用定位功能选中公式所在的单元格（具体操作步骤参阅1.4.4节内容），打开设置单元格格式，如图1-39所示。

 图1-39

第二步：在设置单元格格式对话框中，选中保护选项卡，锁定和隐藏两项前面都打上勾，点击确定，如图1-40所示。

 图1-40

第三步：打开审阅选项卡，点击保护工作表，根据自己的需要填写密码（不填也可），点击确定，如图1-41所示。

 图1-41

经过这三步设置，在编辑栏就无法看到公式了，效果如图1-42所示。

 图1-42

双击公式所在的单元格时会弹出对话框，禁止修改公式，效果如图1-43所示。

 图1-43

这样就实现了隐藏和保护公式的目的，如果需要修改公式是，点击撤销保护工作表按钮即可，如图1-44所示。

 图1-44

1.4.6 公式操作第五式：批量删除公式

删除公式也是很常见的一种操作，删除有两种形式：

1.仅删除公式但保留公式的计算结果，可以选择数据区域，复制，然后点右键，点击选择性粘贴数值选项即可，如图1-45所示。

 图1-45

2．删除公式，同时删除结果，可以使用定位功能选择公式所在单元格，然后按del键或者点右键，点击清除内容即可，如图1-46所示。

 图1-46

1.5 给你三件救命法宝

1.5.1 F9大法

在使用Excel操作复杂公式的时候，有时需要对公式的每一步进行计算，以便分析公式的原理或者排除公式中错误。此时可以使用F9功能键，Excel会将公式中的一部分结果显示出来。

例如公式=B2+C2+D2中，想要看到C2+D2这部分的结果，可以在编辑栏中选中这部分，可以看到选中的部分是被抹黑的效果，如图1-47所示。

 图1-47

此时按下F9键（部分键盘或者笔记本需要同时按下fn键），就可以看到抹黑部分的结果了，如图1-48所示。

 图1-48

若希望返回运算前的公式段状态，只需要按下Ctrl Z组合键来撤销或者按下ESC键取消操作即可。

F9键是必须掌握的功能键，在实际工作中应用的意义在于能提升函数公式的阅读能力，其功能类似于阅读外文资料时的翻译工具。只有在具备一定的函数公式阅读理解能力之后，才能将函数高手写的那些好思路运用到自己的工作中去。

1.5.2 公式求值

除了使用F9键，也可以通过公式求值这个功能了解公式每一步的计算结果，公式求值的位置如图1-49所示。

 图1-49

图1-50是一个完整的公式求值过程。

 图1-50

和F9的区别在于，使用公式求值是按照公式的计算顺序一步一步展示结果的，而F9只是对我们希望看到的部分才显示结果。

在某些情况中，执行计算的次序会影响公式的返回值，因此，了解如何确定计算次序以及如何更改次序以获得所需结果非常重要。

如果一个公式中有若干个运算符，Excel将按图1-51中的次序进行计算。如果一个公式中的若干个运算符具有相同的优先顺序（例如，如果一个公式中既有乘号又有除号），Excel 将从左到右进行计算。

 图1-51

若要更改计算顺序，就要把先计算的部分用括号括起来。

例如，公式=5+2*3的结果是11，因为Excel先进行乘法运算后进行加法运算。将2与3相乘，然后再加上5，即得到结果。

但是，如果用括号对该公式进行更改为=(5+2)*3，Excel将先求出5加2之和，再用结果乘以3得21。

1.5.3 错误检查

使用公式就难免出现错误，掌握检查错误的方法关键时刻真的能救命！

常见的错误分为两种情况：结果为错误值或者循环引用。

1.Excel公式错误值：

当表格中有公式错误值时，可以点击错误检查，检查结果中会显示错误值所在的单元格以及错误原因，效果如图1-52所示：

 图1-52

了解Excel中的错误值和产生的原因，有助于你迅速解决问题，图1-53列出了Excel中常见的错误值类型和解决方法：

 图1-53

2.循环引用：提示如图1-54所示

 图1-54

出现循环引用时，可以使用图1-55所示的方法进行检查：

 图1-55

可以看到，在E2单元格的公式中，用到了E2单元格进行计算，这就是出现循环引用的原因，修改公式即可解决。

下节预告