1.2 Excel公式与函数初步
Excel不仅是一个可在列或行中输入数字的网格,还可以使用Excel求出一列或一行数字的总和,也可根据自己插入的变量计算抵押贷款付款、解答数学或工程问题、找到最佳情况方案。
Excel公式是Excel工作表中进行数值计算的等式,Excel公式用于对工作表中的数据执行计算或其他操作。Excel公式的组成包括一个等号“=”和一个或者多个运算码。运算码包含下列所有内容或其中之一:函数、引用、运算符和常量,如图1-11所示。
图1-11 Excel公式
•函数:PI()函数返回PI值:3.14159……
•引用:A2返回单元格A2中的值。
•常量:直接输入公式中的数字或文本值,如2。
•运算符:*(星号)运算符表示数字的乘积,而(脱字号)运算符表示数字的乘方。
1.运算符
运算符可指定要对公式元素执行的计算类型。Excel遵循常规数学规则进行计算,即括号、指数、加减乘除,或首字母缩写PEMDAS(Please Excuse My Dear Aunt Sally)。可使用括号更改计算次序。
计算运算符分为4种不同类型:算术、比较、文本连接和引用。
(1)算术运算符
若要进行基本的数学运算(如加法、减法、乘法或除法)、合并数字以及生成数值结果,可使用表1-3所示的算术运算符。
表1-3 算术运算符
(2)比较运算符
可使用表1-4所示的运算符比较两个值。使用这些运算符比较两个值时,结果为逻辑值TRUE或FALSE。
表1-4 比较运算符
(3)文本连接运算符
可以使用与号(&)连接(联接)一个或多个文本字符串,以生成一段文本,如表1-5所示。
表1-5 文本连接运算符
(4)引用运算符
可以使用表1-6所示的引用运算符对单元格区域进行合并计算。
表1-6 引用运算符
2.公式
公式的录入有三种方法。
(1)直接输入
例如,要计算算式2×3+5,那么在相应单元格内输入“=2*3+5”,如图1-12所示。
图1-12 直接输入数据
输出单元格显示结果“11”,而编辑框内显示公式“=2*3+5”。
如果在公式中使用常量而不是对单元格的引用(例如=2*3+5),则仅在修改公式时结果才会变化。通常,为了轻松查找和更改常量,会将常量放置在指定单元格内,然后在公式中引用这些单元格。
(2)输入计算式
有时候我们需要对指定单元格的值进行计算,而不是具体的数值计算,这时计算结果会随着指定单元格值的变化而变化。
例1.1 在图1-13中,将单元格A1、B1和C1中的值相加。
图1-13 求和数据
要将单元格A1、B1和C1中的值相加,那么在单元格内输入“=A1+B1+C1”,如图1-14所示。
图1-14 输入计算式
输出单元格显示结果“145”,而编辑框内显示公式“=A1+B1+C1”。
(3)调用函数输入
例1.1也可以调用函数完成求和运算,在相应单元格中输入“=SUM(A1:C1)”,如图1-15所示。
图1-15 调用函数输入
3.函数
表1-7为最常用的10个Excel函数。
表1-7 Excel中常用的10个函数
下面以最常见的IF函数为例,对Excel中的函数进行介绍。IF函数主要的功能是对结果值和期待值进行逻辑比较,判断是否满足某个条件,如果满足该条件则返回一个值,如果不满足则返回另一个值。
IF函数语法为
IF(Logical_test,Value_i f_t rue,Value_i f_false)
IF函数参数如图1-16所示。
•Logical_test:必需,表示判断的条件。
•Value_if_true:必需,表示如果判断条件为真时显示的值。
•Value_if_false:必需,表示如果判断条件为假时显示的值。
因此,IF语句可能有两个结果。第一个结果是比较结果为True,第二个结果是比较结果为False。
图1-16 IF函数参数
例如“=IF(C2="Yes",1,2)”,该公式表示,如果单元格C2的值为“Yes”,则返回1,否则返回2。
IF函数可用于计算文本和数值,还可用于计算错误。不仅可以检查一项内容是否等于另一项内容并返回单个结果,还可以根据需要使用数学运算符并执行其他计算。此外,还可将多个IF函数嵌套在一起来执行多个比较。
注意:
①如果要在公式中使用文本,需要将文字用引号括起来(例如“Text”)。唯一的例外是使用TRUE和FALSE时,Excel能自动理解它们。
②IF函数中符号的输入法必须选择英文半角。
例如“=IF(C2＞B2,"Over Budget","Within Budget")”,该公式表示如果单元格C2的值大于单元格B2的值,则返回“Over Budget”,否则就返回“Within Budget”。
例如“=IF(C2＞B2,C2-B2,0)”,该公式表示如果单元格C2的值大于单元格B2的值,则返回单元格C2与B2的差,否则返回0。
例如“=IF(E7="Yes",F5*0.0825,0)”,该公式表示如果单元格E7的值为“Yes”,则计算单元格F5的值与8.25%的乘积,否则返回0。
通常来说,将文本常量(可能需要时不时进行更改的值)直接代入公式的做法不是很好,因为将来很难找到和更改这些常量。最好将常量放入自己的单元格,一目了然,也便于查找和更改。对于简单IF函数而言,只有两个结果True或False,而嵌套IF函数有3～64个结果。
例如“=IF(D2=1,"YES",IF(D2=2,"No","Maybe"))”,该公式表示如果单元格D2的值等于1,则返回文本“Yes”,如果单元格D2的值等于2,则返回文本“No”,如果都不满足的话,返回文本“Maybe”。
注意:公式的末尾有两个右括号。需要两个括号来完成两个IF函数,如果在输入公式时未使用两个右括号,Excel将尝试为你更正。
IF函数返回对一个条件的逻辑比较,当需要进行多个条件的逻辑比较时,IF函数不得不使用嵌套IF语句的方式解决。由于多个条件按正确顺序输入可能非常难构建、测试和更新,Excel 2019提供了一个新函数IFS,可以执行对多个条件的逻辑比较,取代多个嵌套IF语句,并且有多个条件时更方便阅读。
IFS函数检查是否满足一个或多个条件,且返回符合第一个TRUE条件的值。IFS函数语法具有下列参数:
IFS(Logical_test1,Value_i f_t rue1,[Logical_test2,Value_i f_t rue2],[Logical_test3,Value_i f_t rue3],…)
IFS函数参数如图1-17所示。
•Logical_test1:必需,计算结果为TRUE或FALSE的条件。
•Value_if_true1:必需,当Logical_test1的计算结果为TRUE时要返回结果。可以为空。
•Logical_test2…Logical_test127:可选,计算结果为TRUE或FALSE的条件。
•Value_if_true2…Value_if_true127:可选,当Logical_test N的计算结果为TRUE时要返回结果。每个Value_if_true N对应于一个条件Logical_test N。可以为空。
IFS语句可能有多个结果,不同的结果返回不同的值。
图1-17 IFS函数参数
注意:IFS函数允许测试最多127个不同的条件。IFS函数的目的在于取代IF的多次嵌套,因此不建议在IF或IFS语句中嵌套过多条件。
例如,如图1-18所示,单元格A2:A6的公式为
=IFS(A2＞89,"A",A2＞79,"B",A2＞69,"C",A2＞59,"D",TRUE,"F")
即如果A2大于89,则返回“A”,如果A2大于79,则返回“B”,依此类推,对于所有小于59的值,返回“F”。
图1-18 IFS函数
又例如,如图1-19所示,单元格G7中的公式是
=IFS(F2=1,D2,F2=2,D3,F2=3,D4,F2=4,D5,F2=5,D6,F2=6,D7,F2=7,D8)即如果单元格F2中的值等于1,则返回的值位于单元格D2,如果单元格F2中的值等于2,则返回的值位于单元格D3,依此类推,如果其他条件均不满足,则最后返回的值位于单元格D8。
图1-19 IFS函数
注意:
①若要指定默认结果,请对最后一个Logical_test参数输入TRUE。如果不满足其他任何条件,则将返回相应值。在图1-18中,行6和行7(成绩为58)展示了这一结果。
②如果提供了Logical_test参数,但未提供相应的Value_if_true,则此函数显示“你为此函数输入的参数过少”的错误消息。