Excel 计算基础.docx
《Excel 计算基础.docx》由会员分享，可在线阅读，更多相关《Excel 计算基础.docx（27页珍藏版）》请在冰豆网上搜索。
Excel计算基础
Excel计算基础
第一节表达式及基本运算
一、公式概述
公式是Excel工作表中进行各种计算的公式，由操作数、运算符、函数、括号等组成。
公式存储于单元格中时必须以等号（=）开始，表示紧随等号之后的各种符号、数据等，是需要进行计算的要素（操作数，运算符等）。
公式的计算结果就存储于公式所在的单元格中，并在相应位置显示出来。
例：
=3+5
=3+A5
=3+（A3+A5）/2+SUM（B1:
B5）
二、Excel的运算
Excel可以做各种算术运算、字符运算和运算运算，不同的运算，运算符不，书写格式不同，运算结果也不同。
下面将分别加以叙述。
1、数值数据与算术运算
Excel的数据值主要：
整数、实数、日期、时间等。
在excel中日期数据在输入时一般以减号（-）作为年月日的分隔符，且认为日期是一个整数，日期与整数的对应规则是：
1900年1月1日对应整数1，1900年1月2日对应整数2，……，余此类推。
比1900年1月1日更早的日期excel就不处理了。
如下表所示：
日期
对应的整数
1900年1月1日
1
1900年1月2日
2
1900年1月3日
3
1900年1月4日
4
1900年1月5日
5
2012年3月1日
40969
在excel中时间数据中“时分秒”的分隔符为英文的冒号“：
”，且认为时间是一个0—1之间的纯小数，时间与小数的对应规则为：
“0：
0：
0”对数据值0，“12：
0：
0”对应0.5，“23：
59：
59”对应0.999988
日期数据、时间数据在参与计算时将自动转换为对应的整数或小数。
在显示时，可通过单元格格式设置，将日期转换成整数或将整数（>0）转换成日期进行显示，同样，时间数据与纯小数（0—1之间）也可通过格式显示进行转换。
如下是日期数据转换的例子：
转换前
转换后
参与算术运算的数据一般是数值类型，其计算结果也是数值数据，excel的算术运算如下表所示：
Excel公式中的算术运算符
算术运算符
含义
示例
+
加法
8+2
-
减法
负号
5-3
-7
*
乘法
9*3
/
除法
7/3
^
乘方
2^3（2的3次方）
%
百分比
34%
例：
销售利润率的计算公式为：
销售利润率=（销售收入-销售成本）/销售收入
在“销售利润率1”的计算公式中，直接用数据进行计算，在“销售利率2”的计算公式中，则是用销售收入及销售成本数据存放的单元格的坐标说明要用哪些数据进行计算。
单元格坐标用在计算公式中，称为“单元格引用”。
这种在计算公式中用单元格的坐标说明要用哪个单元格的坐标参与计算的方法更好，因为，如果销售数据改变了，可以直接修改单元格中的数据，excel就可自动重新计算，而不需要修改计算公式。
例：
设有函数
，计算当x=2.5时，函数f（x）的值。
计算
可以用excel的函数EXP（），计算时，运算符不能省略，例如
中，3与X之间的乘号就不能省略。
计算时，可以直接用X的值代入函数表达式中进行计算，也可以将X的值存入单元格中（例如B2单元格），而在单元格中用X的值所在单元格的坐标代替X的位置，后一种方法是普遍使用的。
例：
在复利计算规则下，如果年利率为i，那么现在存入本金PV，在第n年末的本息和FV可用下列公式计算：
试计算年利率为i=6.5%，本金PV=10000元，计算第10年末的本息和。
例：
设B68单元格存放的日期数据为：
“2003-10-8”，E68单元格存放的日期数据为：
“2003-9-29”，则下列计算的结果为
计算公式
结果
含义
=B68-E68
9
2003年10月8日比2003年9月29日晚9天
=E68-B68
-9
2003年10月8日比2003年9月29日早9天
=B68+7
2003-10-15
2003年10月8日后的第7天是2003年10月15日
例：
设B82单元格中存放了时间数据：
“14:
34:
00”，E82单元格中存放了时间数据“12:
12:
23”，则下计算的结果为：
计算公式
运算结果
含义
=B82+E82
2：
46：
23
14:
34:
00之后的12:
12:
23的时间是26:
46:
23，
减去24小时就是2:
46:
23
=B82-E82
2：
21：
37
14:
34:
00与12:
12:
23之间相差2:
21:
37
注意：
在日期和时间计算中，公式的日期和时间数据不能用常量，只能用单元格引用
例：
某汽车租赁公司5座位小汽车每天的租金为198元，一辆车租金的计算公式为：
用excel编制一个计算表用于计算该公司一辆车一次应收的租金总额。
3、文本运算
Excel的文本运算仅有一个，“&”，其作用是将两个文本连接成一个文本。
文本运算的基本格式：
文本1&文本2
当文本运算符运用到计算公式时，excel自动将文本运算符两边单元格中的数据认为是文本数据，也可在运算符两边使用文本常量，文本常量要用英文的双引号““”括起来。
如下所示：
4、比较运算
excel的逻辑值有两个：
FALSE（逻辑假）和TRUE（逻辑真）。
Excel可以进行多种关系运算，运算结果为逻辑值，有关的比较运算符及运算规则如下表所示：
比较运算符
格式
含义
=
数据1=数据2
判断2个数据是否相等，如果相等，则结果为TRUE，否则为FALSE
>
数据1>数据2
判断数据1是否大于数据2，如果是，则结果为TRUE，否则为FALSE
>=
数据1>=数据2
判断数据1是否大于或等于数据2，如果是，则结果为TRUE，否则为FALSE
<
数据1<数据2
判断数据1是否小于数据2，如果是，则结果为TRUE，否则为FALSE
<=
数据1<=数据2
判断数据1是否小于或等于数据2，如果是，则结果为TRUE，否则为FALSE
<>
数据1<>数据2
判断2个数据是否相等，如果不相等，则结果为TRUE，否则为FALSE
例
运算结果
33>4
TRUE
"33">"4"
FALSE
"4">"33"
TRUE
"ACD"="acd"
TRUE
77<>99
TRUE
77=99
FALSE
"ACD"="ABC"
FALSE
"ACD"="ACD123"
FALSE
"ABC"="ABCD"
FALSE
"ABC"="ABC"
TRUE
注：
ASCII字符的比较是按字典顺序进行，且不分大小写。
例：
根据下表，写出
（1）判断“张重庆”的性别是否是“男”的关系表达式；
（2）判断“张重庆”的基本工资是否低于4000元的关系表达式；
（3）判断“张重庆”身份证号的前两位是否是“43”的关系表达式。
解：
（1）判断“张重庆”的性别是否是“男”的关系表达式：
C90=”男”
注：
excel中文本常量的两端要用英文的双引号作为分界符。
（2）判断“张重庆”的基本工资是否低于4000元的关系表达式：
F90<=4000
（3）判断“张重庆”身份证号的前两位是否是“43”的关系表达式：
LEFT（G90,2）=”43”
注：
函数LEFT（文本，子文本长度）的功能是从“文本”的左端开始取指定长度的一个子文本。
因为身份证号是文本，所以LEFT（）得到的是一个子文本，要判断一个文本是否等于43，则43也是一个文本，所以在表达式中要将43的两端要加英文的双引号
Excel没有逻辑运算符，但有逻辑函数，有关函数在后面介绍。
三、运算符的优先级
如果一个公式中同时用到多个运算符，Excel将按下表所示的顺序进行运算。
如果公式中包含相同优先级的运算符，则按从左到右的顺序进行计算。
Excel的运算次序表
运算符
说明
运算顺序
：
（冒号）
引用运算符
先运算
（单个空格）
，（逗号）
-
负号运算符
%
百分比
^
乘幂
*和/
乘和除
+和-
加和减
&
字符串连接
=<><=>=<>
比较运算符
后运算
四、excel的运算错误信息
如果Excel根据单元格中公式进行计算得到一个正确的结果，就将该单元格的数据显示在该单元格中。
如果不能得到一个正确结果，则在公式所在单元格显示一个错误信息。
Excel的错误信息表
错误信息
含义
例
#DIV/0!
这个公式试图使用0作为除数
如果公式中使用一个空单元格作为除数时，就可能会出现这个信息
#NAME?
公式使用了一个Excel不能识别的名称。
如果删除了公式中使用的名称或误输入某个函数名时，就可能出现这个信息。
#N/A
公式中引用的（直接或间接）单元格中使用NA函数所标识的不能使用的数据。
如果查找函数没有找到匹配数据时，就可能出现这个信息。
#NULL!
公式使用了一种不允许出现交叉但交叉在一起的两个区域
#NUM!
使用的数据有问题
例如，在应该使用正值的情况下，使用了负数。
#REF!
公式引用了一个无效的单元格
如果单元格从工作表中删除了，则经常会出现这种信息。
#VALUE!
公式包括了错误形式的变量或运算（使用错误的参数或运算数的类型错误）
一个运算对象引用一个值或单元格引用，但是它们中却需要使用公式计算结果。
######!
单元格要显示的数据太长，单元格现在的宽度不能完整显示这个数据
第二节单元格引用及单元格区域引用
一、单元格引用的概念及基本格式
在excel的计算公式中，用单元格的坐标来说明在计算公式中要使用某个单元格的数据，或说明要使用某个单元格区域的数据来进行计算。
所以将计算公式中的单元格坐标叫做单元格引用，如果这些坐标代表的是一个区域，则叫做单元格区域引用。
1、单元格引用
单元格引用的基本格式：
列名行号
单元格引用代表一个单元格。
例：
A1——代表工作表左上角的第一个单元格，即第一行第一列的单元格
B4——代表工作表第2列第4行的单元格
2、单元格区域引用
单元格区域引用的基本格式：
单元格引用1：
单元格引用2
作用：
代表以“单元格引用1”为左上角，以“单元格引用2”为右下角的一个矩形单元格区域。
例如：
A2:
C6代表A2、A3、A4、A5、A6、B2、B3、B4、B5、B6、C2、C3、C4、C5、C6这些单元格所组成的一个矩形区域。
如图所示的灰色区域。
3、由一行或若干行中所有单元格组成的区域的引用
基本格式：
行号1：
行号2
代表由“行号1”到“行号2”所在区域的所有单元格组成的区域，“行号1”与“行号2”可以相同，这时则说明是由一行组成的单元格区域。
例如：
3：
5表示由第3行至第5行的所有单元格组成单元格区域，如图所示：
例如：
3：
3表示由第3行的所有单元格组成的单元格区域，如图所示：
4、由一列或若干列中所有单元格组成的单元格区域
基本格式：
列名1：
列名2
代表由“列名1”至“列名2”所有列组成的单元格区域，如图所示：
例：
C：
D表示由第C列和第D列的所有单元格组成的单元格区域，如图所示。
5、不同工作表之间单元格数据的引用
格式：
工作表名！
单元格引用
例：
Sheet1!
A1——表示工作表“Sheet1”A1单元格的引用
职工信息表！
C3——表示“职工信息表”中C3单元格的引用
6、三维引用
用一个区域引用表示连续的若干张工作表中的同一个单元格的集合。
格式I：
‘工作表1：
工作表2’！
单元格引用
作用：
代表由“工作表1”至“工作表2”连续的若干张工作表中的同一单元格
格式II：
‘工作表1：
工作表2’！
单元格区域引用
作用：
代表由“工作表1”至“工作表2”连续的若干张工作表中的同一区域中的所有单元格
例：
'1月份工资表:
3月份工资表'!
C3代表1月份工资表、2月份工资表及3月份工资中的C3单元格，也就是说这个单元格引用代表了3个单元格，如下图所示：
根据上图，假设每月工资表格式相同，且张千一在这3个月的工资表中都的位置都相同，那么，计算公式：
=SUM（'1月份工资表:
3月份工资表'!
C3）表示求张千一这个职工1~3月份的基本工资合计。
7、相对引用、绝对引用、混合引用
Excel将单元格引用分为相对引用、绝对引用和混合引用，相对引用是如前面介绍的这些引用的格式均为相对引用，绝对引用则是在列坐标和行坐标的前面加上美元符号“$”，混合引用则仅在列坐标前美元符号“$”或在行号前美元符号“$”，如下所示：
A1——相对引用
$A$1——绝对引用
$A1——混合引用
A$1——混合引用
这四种引用，都代表工作表中的同一个单元格A1单元格（第1行第1列所对应的单元格）。
excel的计算公式中使用这四种单元格引用，对于计算的作用是相同的，例如：
A1+B1，A1+$B1，$A$1+$B$1，……，其效果是相同的，都是将A1单元格的数据与B1单元格中的数据相加，计算结果也是相同的。
那么在excel中使用这么多种引用有什么作用呢？
这四种引用的不同之处在于，当一个单元格中的计算公式复制到另一个单元格中时，公式中的单元格引用是否发生变化以及如何发生变化。
如果是计算公式中使用的是相对引用，那么当某个单元格中的公式复制到这个单元格的上方单元格中时，公式中单元格引用的行号将减少，新单元格如果在原单元格上方n行，则单元格引用中的行号将减少n，而单元格中计算公式向下方的单元格复制时，则公式中的单元格引用将增加，新单元格如果在原单元格下方n行，则单元格引用中的行号将增加n。
同理，左右复制时，则计算公式中的单元格引用的列坐标将发生相应的变化，向左复制，列坐标减少，向右复制时，列坐标增加，减少或增加的规律与行坐标变化的规律相同。
在绝对引用、或混合引用中，坐标前面的美元符号“$”是使计算公式在复制时，计算公式中的单元格引用中的相应的坐标不发生变化，如果列坐标前加了美元符号“$”，则在左右复制时，列坐标不变；如果在行号前加了美元符号“$”，则在上下复制时，行坐标不发生变化。
例如：
D5单元格中如果有计算公式：
“=E7+F8”，那么，将该公式复制到B1单元格时，B1单元格中的计算公式将变成：
“=C3+D4”，这是因为计算公式向左复制到了2列，所以，计算公式中的相对引用的列坐标就相应减少2列，从原来的E列变为C列，从原来的F列变成D列；而计算公式又向上复制了4行，所以计算公式中的相对引用的行坐标就相应减少4行，从原来的7行将减少为3行，以及从原来的8行减少为5行。
而如果D5单元格中的计算公式是：
“=$E$7+$F$8”，则计算公式复制到B1单元格，还是原来的计算公式，并不发生改变。
如果D5单元格中的计算公式是：
“=$E7+F$8”，则计算公式复制到B1单元格后，公式变为：
=$E3+D$8，因为列坐标E前面加了美元符号“$”，和行号8前面加了美元符号“$”，所以复制后，这两个坐标没有发生变化。
当在excel的工作表中有大量相似或相同的计算公式时，灵活运用，可以极大地减少输入计算公式的工作量。
单元格引用应用举例
例1：
引用同一工作表中的数据进行计算——工资计算
在该表中，“缺勤应扣工资”的计算公式为：
“应发工资”的计算公式为：
（1）因为张千一的“基本工资”在C3单元格，“津贴”在D3单元格，“书报费”在E3单元格，“缺勤天数”在F3单元格，“缺勤应扣工资”在G3单元格，所以，
“缺勤应扣工资”（G3单元格）中的计算公式为：
=F3*（C3/22.5）
“应发工资”（H3单元格）中的计算公式为：
=C3+D3+E3-G3
（2）同理，因为李东的“基本工资”在C4单元格，“津贴”在D4单元格，“书报费”在E4单元格，“缺勤天数”在F4单元格，“缺勤应扣工资”在G4单元格，所以，
“缺勤应扣工资”（G3单元格）中的计算公式为：
=F4*（C4/22.5）
“应发工资”（H3单元格）中的计算公式为：
=C4+D4+E4-G4
（3）根据上述规律，其余人的“缺勤应扣工资”和“应发工资”栏的计算公式如下表所示：
姓名
缺勤应
扣工资
应发工资
张千一
=F3*（C3/22.5）
=C3+D3+E3-G3
李东
=F4*（C4/22.5）
=C4+D4+E4-G4
张车车
=F5*（C5/22.5）
=C5+D5+E5-G5
王万国
=F6*（C6/22.5）
=C6+D6+E6-G6
陈山
=F7*（C7/22.5）
=C7+D7+E7-G7
张东风
=F8*（C8/22.5）
=C8+D8+E8-G8
李四喜
=F9*（C9/22.5）
=C9+D9+E9-G9
刘爱舞
=F10*（C10/22.5）
=C10+D10+E10-G10
陈琪
=F11*（C11/22.5）
=C11+D11+E11-G11
从上表中可以看出，计算公式从上往下是很有规律变化，根据excel相对引用在公式复制时的特点，可以只输入张千一的“缺勤应扣工资”和“应发工资”计算公式，然后通过“复制”、“粘贴”操作就可得到其余人员的“缺勤应扣工资”和“应发工资”计算公式。
这样可以大量节省录入计算公式的时间。
例2：
计算函数
在x=0，0.1，0.2，……，的值，并列成一个函数值表。
从上表可以看出：
（1）计算函数值时，计算公式中用自变量x的值所在单元格的引用代替代替函数表达式中的自变量x就可计算函数的值。
（2）从函数值计算公式列表中可以看出，由于自变量x的每一个值在不同的单元格，所以计算公式中代替自变量x的单元格引用也不同。
（3）由于自变量x的值存放是有规律变化的，所以计算公式中，代替自变量x的单元格引用也是有规律的变化的，所以利用excel的相对引用，只需输入计算f（x）函数值的第1个公式，然后通过“复制”、“粘贴”命令就可得到其余函数值的计算公式。
例3：
增长率计算（环比增长率——相对应用和定比增长率——绝对引用）。
（1）计算“比2006年第1季度增长（%）”的计算公式：
（2）计算“比上一季度的增长（%）”的计算公式：
根据上述计算公式，可以得到表中各个增长率的计算公式如下表：
税收增长率计算公式
季度
比2006年
第1季度增长（%）
比上一季度
增长（%）
2006年第1季度
2006年第2季度
=（C4-C3）/C3
=（C4-C3）/C3
2006年第3季度
=（C5-C3）/C3
=（C5-C4）/C4
2006年第4季度
=（C6-C3）/C3
=（C6-C5）/C5
2007年第1季度
=（C7-C3）/C3
=（C7-C6）/C6
2007年第2季度
=（C8-C3）/C3
=（C8-C7）/C7
2007年第3季度
=（C9-C3）/C3
=（C9-C8）/C8
2007年第4季度
=（C10-C3）/C3
=（C10-C9）/C9
2008年第1季度
=（C11-C3）/C3
=（C11-C10）/C10
2008年第2季度
=（C12-C3）/C3
=（C12-C11）/C11
2008年第3季度
=（C13-C3）/C3
=（C13-C12）/C12
2008年第4季度
=（C14-C3）/C3
=（C14-C13）/C13
2009年第1季度
=（C15-C3）/C3
=（C15-C14）/C14
2009年第2季度
=（C16-C3）/C3
=（C16-C15）/C15
2009年第3季度
=（C17-C3）/C3
=（C17-C16）/C16
2009年第4季度
=（C18-C3）/C3
=（C18-C17）/C17
2010年第1季度
=（C19-C3）/C3
=（C19-C18）/C18
2010年第2季度
=（C20-C3）/C3
=（C20-C19）/C19
2010年第3季度
=（C21-C3）/C3
=（C21-C20）/C20
2010年第4季度
=（C22-C3）/C3
=（C22-C21）/C21
2011年第1季度
=（C23-C3）/C3
=（C23-C22）/C22
2011年第2季度
=（C24-C3）/C3
=（C24-C23）/C23
2011年第3季度
=（C25-C3）/C3
=（C25-C24）/C24
2011年第4季度
=（C26-C3）/C3
=（C26-C25）/C25
2012年第1季度
=（C27-C3）/C3
=（C27-C26）/C26
2012年第2季度
=（C28-C3）/C3
=（C28-C27）/C27
（1）从上表中可以看出，“比上一季度增长（%）”的计算公式中，上一计算公式中单元格引用的行号在下一行对应位置增长1，所以可以用相对引用输入这一列中的第1个计算公式，然后通过“复制”、“粘贴”操作得到本列的其余计算公式。
（2）“比2006年第1季度增长（%）”列的计算公式中，只有第1个单元格引用为有规律的变化，而第2个、第3个单元格引用均为C3，为了使“复制”、“粘贴”操作不改变计算公式中的单元格引用C3，则C3使用绝对引用，使该列的第1个计算公式变为：
=（C4-$C$3）/$C$3
在输入这一列的第1个计算公式后，在通过“复制”、“粘贴”操作就可得到这一列的其余计算公式。
如下图所示：
图：
输入计算增长率的第1行的计算公式
图：
输入计算公式后的计算结果
图：
复制第1行并粘贴其余行后的效果
例4：
按学分加权的平均成绩的计算（绝对引用与相对引用）
计算方法
根据成绩统计表的布局，可以得到各个学生的学分加权平均成绩的计算公式如下表所示：
姓名
学分加权平均成绩计算公式
陈曦阳
=（B4*C3+D4*E3+F4*G3+H4*I3）/（C3+E3+G3+I3）
李朝阳
=（B5*C3+D5*E3+F5*G3+H5*I3）/（C3+E3+G3+I3）
陈永
=（B6*C3+D6*E3+F6*G3+H6*I3）/（C3+E3+G3+I3）
李莉
=（B7*C3+D7*E3+F7*G3+H7*I3）/（C3+E3+G3+I3）
张剑
=（B8*C3+D8*E3+F8*G3+H8*I3）/（C3+E3+G3+I3）
李平国
=（B9*C3+D9*E3+F9*G3+H9*I3）/（C3+E3+G3+I3）
刘竹
=（B10*C3+D10*E3+F10*G3+H10*I3）/（C3+E3+G3+I3）
邓小飞
=（B11*C3+D11*E3+F11*G3+H11*I3）/（C3+E3+G3+I3）
郭荣
=（B12*C3+D12*E3+F12*G3+H12*I3）/（C3+E3+G3+I3）
封婷
=（B13*C3+D13*E3+F13*G3+H13*I3）/（C3+E3+G3+I3）
从述计算公式中可以看出，计算公式中，从上往下每一个学生的成绩的单元格引用是有规律变化的，单元格引用的行号在增加，每次加1，而学分的单元格引用不变。
所以在输入第1个学生的“学分加权平均成绩计算公式”时，代表成绩的单元格引用使用相对引用，而代表学分的单元格引用使用绝对引用（因为在复制公式时，相对引用会发生有规律的变化，而绝对引用不发生变化），然后使用“复制”、“粘贴”操作就可得到其余学生的计算公式。
修改后第1个学生的“学分加权平均成绩计算公式”如下：
=（B4*$C$3+D4*$E
升级会员 