加薪不加班：174个Excel函数案例闯职场最新章节_曾贤志著

1.7 Excel公式运算符

在Excel中编写公式时，经常会使用运算符。运算符分为算术运算符、比较运算符、连接运算符、引用运算符。接下来一一详解这些运算符在公式中的使用方法。

1.7.1 算术运算符

图1-11列出了常见的算术运算符，并介绍了简单的应用实例。如果文本型数值和逻辑值遇到这些算术运算符，那么它们将会变成数字型数值。后面章节将有非常多这种应用。

图1-11

实例2 使用SUM函数计算每个销售员的业绩在总业绩中的占比

问题描述： 图1-12中列出了2017年1月份各个销售员的销售业绩，现在需要统计一下每个销售员的业绩在他们总业绩中的占比是多少。

图1-12

解决思路： 首先要知道计算占比的公式是：个人占比=个人业绩/总业绩。个人业绩已经知道了，那总业绩如何获取呢？自然是将每个人的业绩加起来，就得到总业绩。那用什么函数来实现呢？当然是SUM函数。

操作步骤： 如图1-13所示，以C3单元格为基准，首先在该单元格中输入公式：=B3/SUM($B$3:$B$6)，然后向下填充公式完成计算，下面分析一下公式的运算过程。

图1-13

步骤1： 公式=B3/SUM（$B$3:$B$6）中的SUM函数引用的“$B$3:$B$6”中使用了美元符号（$）来固定这个区域，目的是为了后续向下填充公式时，求和的区域不变。后面的章节中会详细介绍这些技术细节。

步骤2： B3单元格中的78为罗娟的个人业绩，函数SUM($B$3:$B$6)的求和结果283为4个人的总业绩，直接相除=78/283，即得到罗娟的业绩在总业绩中的占比。向下填充公式即可得到其他销售员的占比情况。

步骤3： 如果发现步骤2计算出来的值是小数，而不是百分比，则在“设置单元格格式”对话框中将数字格式设置为“百分比”格式。

1.7.2 比较运算符

比较运算符是用于比较大小的符号，如图1-14所示，我们印象中通常只能比较数字的大小。但在Excel公式中，文本也可以进行比较，按从小到大的顺序排列为：数字、字母、汉字。

比较运算的结果一定是个逻辑值（TRUE和FALSE），如果比较结果成立，则返回值为TRUE，不成立则为FALSE。TRUE相当于1，FALSE相当于0。有时在编写公式时，需要将TRUE和FALSE转为对应的数字。如何转换呢？只需要将逻辑值做不改变原值的算术运算即可。图1-15中列出了常见的将逻辑值转为1和0的方法。

图1-14

图1-15

通过图1-15的转换方法也证明了TRUE相当于1，FALSE相当于0。当然，逻辑值也不是一定只能转换为对应的1和0，也可以将逻辑值和其他数字进行算术运算，关键看公式需求。在运算过程中始终把TRUE和FALSE看作1和0就对了。

除此之外，公式中的逻辑值之间也经常做算术运算，特别是相乘和相加的运算。图1-16所示为逻辑值之间的相乘、相加运算的结果，从中可以发现以下规律。

● 相乘运算：必须所有逻辑值为TRUE，结果才能为1，否则为0。相当于逻辑“与”运算，只有全部为真时才为真。

● 相加运算：必须所有逻辑值为 FALSE，结果才能为 0，否则为非 0。相当于逻辑或运算，只有全部为假时才为假。

图1-16

实例3 两科成绩同时大于或等于90分的学生奖励100元

问题描述： 图1-17显示的是一张学生成绩表，包含科目有语文和数学。现在需要判断，若某个学生两个科目的分数都同时大于或等于90分，则发放奖金100元，否则奖金为0。

图1-17

解决思路： 先分别用比较运算符判断“语文”和“数学”是否大于或等于90，再对判断出来的两个逻辑值进行相乘运算，结果为1则证明两个条件同时成立，结果为0则不同时成立。然后将得到的这个数字乘以100，同时成立则是1í100，结果为100，不同时成立则是0í100，结果为0。

操作步骤： 如图1-18所示，以D3单元格为基准，输入公式=(B3>=90)*(C3>=90)*100，向下填充公式完成计算，下面分析一下公式的运算过程。

图1-18

步骤1： 先判断语文（B3>=90），结果为TRUE，再判断数学（C3>=90），结果为TRUE，然后将两个分数判断出来的逻辑值相乘，TRUE*TRUE，结果为1。

步骤2： 将步骤1得到的值1乘以100，得到100，便计算出了第1条记录的奖金为100元。后面的记录也按此思路进行计算。

1.7.3 连接运算符

Excel公式中的&是连接运算符，连接运算符可以直接连接数字、文本、单元格。不同的是数字与单元格的连接可以直接连接，而在连接文本时，被连接的文本必须加双引号。无论是怎样的连接方式，最后返回的数据类型都是文本。很多初学者搞不清楚数字、单元格、文本之间到底该怎么连接？下面用图1-19所示的一张表罗列出来。

图1-19

实例4 上半年和下半年业绩均为优的员工奖金为100 000元，其他奖金为80000元

问题描述： 图1-20的表格中是2016年上半年和下半年每个员工的业绩等级情况。现在需要判断，如果某员工上半年和下半年的等级都同时等于“优”，那么该员工的奖金为100000元，否则为80000元。

图1-20

解决思路： 本实例和实例3的要求很相似，也可以用实例3的解决思路来完成，但本实例的目的是应用连接运算符解题。在判断上、下半年是否等于“优”时，他们的判断条件是相同的，所以可以将两个条件合并到一起进行判断，而不用分别判断。然后根据判断后返回的逻辑值再计算出对应的奖金。

操作步骤： 如图1-21所示，以D3单元格为基准，输入公式=((B3&C3="优优")*2+8)/1%%，向下填充公式，完成计算。接下来分析一下运算过程。

图1-21

步骤1： 先合并B3&C3的值，再与条件〝优优〝做等于比较。公式写法为：（B3&C3=〝优优〝）。

步骤2： 如果（B3&C3=〝优优〝）比较结果为TRUE，则TRUE*2+8，等于10；如果比较结果为FALSE，则FALSE*2+8，等于8。

步骤3： 将步骤2计算的结果除以1%%，相当于除以0.0001，本质就是乘以10000。最终公式是：（（B3&C3=〝优优〝）*2+8）/1%%。

1.7.4 引用运算符

在Excel工作表中写公式时，引用单元格的操作非常频繁，引用单个单元格的表示方法读者应该比较熟悉，但引用各种单元格区域的表示方法却不一定了解。引用单元格区域通常分为区域引用、联合引用、交叉引用。

● 区域引用：单元格区域用冒号（：）连接两个对角单元格地址，图1-22所示区域表示成A1：C5、C5：A1、C1：A5、A5：C1均可。

● 联合引用：将两个及以上单元格区域同时引用时，可以使用逗号（，）连接，图1-23所示的区域表示为A1：B3，B5：D8。

● 交叉引用：引用两个及以上单元格区域的重叠区域时，可以使用空格连接，图1-24所示A1：C5 B3：D6区域，最终引用区域是B3：C5。

图1-22

图1-23

图1-24

1.7.5 运算符优先顺序

学习完常用的运算符之后，还要熟悉各种运算符之间的先后运算顺序，否则编写公式时很容易出错，表1-1列出了各种运算符之间的优先运算顺序。

表1-1

注意要想提高运算符的优先级，则可以使用小括号（），公式中的括号可以使用多组括号嵌套。运算的优先级顺序则是由内向外的，也就是内层括号的优先级高于外层括号。

1.8 单元格引用

什么是单元格引用？比如，就是在B1单元格中的公式引用了A1单元格，那么B1与A1就有产生了对应关系。可产生相对引用、绝对引用、混合引用3种对应关系。单元格引用在Excel公式的编写中是非常重要的知识点，下面讲解这3种引用关系。

1.8.1 相对引用

相对引用，就是保持引用单元格和被引用单元格的相对位置始终不变。相对引用也是默认的引用关系。如图1-25所示，在C2单元格中将B2单元格的工资，再加200。C2单元格在向下填充时，公式中B2单元格的行号也会跟着相对变化，如图1-26所示。如果单元格横向填充，则相对变化的是列号。总结成一句话：将有公式的单元格向上或向下填充时，行号变化，向左或向右填充时列号变化，但引用单元格和被引用单元格的位置关系始终不变，这就是相对引用。

图1-25

图1-26

1.8.2 绝对引用

绝对引用，就是将被引用单元格的行号和列号固定，不让其在填充时发生变化，这样引用单元格和被引用单元格的位置关系则在不断变化。如将A1单元格表示成绝对引用为：$A$1（分别在行号和列号前面添加$符号）。如图1-27所示，在C4单元格写入公式=B4*$C$2，再将公式向下填充。B列为不同人的业绩，需要相对变化，$C$2单元格是提成点数，需要固定不能让其相对变化，如图1-28所示。