Excel 计算基础.doc

1、Excel 计算基础第一节 表达式及基本运算一、公式概述公式是 Excel 工作表中进行各种计算的公式，由操作数、运算符、函数、括号等组成。公式存储于单元格中时必须以等号（=）开始，表示紧随等号之后的各种符号、数据等，是需要进行计算的要素（操作数，运算符等） 。公式的计算结果就存储于公式所在的单元格中，并在相应位置显示出来。例：=3+5=3+A5=3+（A3+A5）/2+SUM(B1:B5)二、Excel 的运算Excel 可以做各种算术运算、字符运算和运算运算，不同的运算，运算符不，书写格式不同，运算结果也不同。下面将分别加以叙述。1、数值数据与算术运算Excel 的数据值主要：整数、实数、

2、日期、时间等。在 excel 中日期数据在输入时一般以减号（- ）作为年月日的分隔符，且认为日期是一个整数，日期与整数的对应规则是：1900 年 1 月 1 日对应整数 1，1900 年 1 月 2 日对应整数2，余此类推。比 1900 年 1 月 1 日更早的日期 excel 就不处理了。如下表所示：日期 对应的整数1900 年 1 月 1 日 11900 年 1 月 2 日 21900 年 1 月 3 日 31900 年 1 月 4 日 41900 年 1 月 5 日 52012 年 3 月 1 日 40969在 excel 中时间数据中 “时分秒 ”的分隔符为英文的冒号“：” ，且认为时

3、间是一个 01之间的纯小数，时间与小数的对应规则为：“0：0：0”对数据值 0， “12：0：0”对应0.5， “23：59：59”对应 0.999988日期数据、时间数据在参与计算时将自动转换为对应的整数或小数。在显示时，可通过单元格格式设置，将日期转换成整数或将整数（0）转换成日期进行显示，同样，时间数据与纯小数（01 之间）也可通过格式显示进行转换。如下是日期数据转换的例子：转换前转换后参与算术运算的数据一般是数值类型，其计算结果也是数值数据，excel 的算术运算如下表所示：Excel 公式中的算术运算符算术运算符 含义 示例+ 加法 8+2-减法负号5-3-7* 乘法 9*3/ 除法

4、 7/3 乘方 23（2 的 3 次方）% 百分比 34%例：销售利润率的计算公式为：销售利润率=（销售收入-销售成本）/销售收入在“销售利润率 1”的计算公式中，直接用数据进行计算，在“销售利率 2”的计算公式中，则是用销售收入及销售成本数据存放的单元格的坐标说明要用哪些数据进行计算。单元格坐标用在计算公式中，称为“单元格引用” 。这种在计算公式中用单元格的坐标说明要用哪个单元格的坐标参与计算的方法更好，因为，如果销售数据改变了，可以直接修改单元格中的数据，excel 就可自动重新计算，而不需要修改计算公式。例：设有函数 ，计算当 x=2.5 时，函数 f(x)的值。5.3)(xexf计算

5、可以用 excel 的函数 EXP（） ，计算时，运算符不能省略，例如 中，3 与 X 之xe 5.x间的乘号就不能省略。计算时，可以直接用 X 的值代入函数表达式中进行计算，也可以将X 的值存入单元格中（例如 B2 单元格） ，而在单元格中用 X 的值所在单元格的坐标代替X 的位置，后一种方法是普遍使用的。例：在复利计算规则下，如果年利率为 i，那么现在存入本金 PV，在第 n 年末的本息和FV 可用下列公式计算： niPVF)1(试计算年利率为 i=6.5%，本金 PV=10000 元，计算第 10 年末的本息和。例：设 B68 单元格存放的日期数据为： “2003-10-8”，E68 单

6、元格存放的日期数据为：“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：2 314:34:0

7、0 之后的 12:12:23 的时间是26:46:23，减去 24 小时就是 2:46:23=B82-E82 2：21：3 7 14:34:00 与 12:12:23 之间相差 2:21:37注意：在日期和时间计算中，公式的日期和时间数据不能用常量，只能用单元格引用例：某汽车租赁公司 5 座位小汽车每天的租金为 198 元，一辆车租金的计算公式为： )(198198租 车 日 期还 车 日 期租 车 天 数租 金 总 额 用 excel 编制一个计算表用于计算该公司一辆车一次应收的租金总额。3、文本运算Excel 的文本运算仅有一个， “ & ”，其作用是将两个文本连接成一个文本。文本运算的基

8、本格式： 文本 1 & 文本 2当文本运算符运用到计算公式时，excel 自动将文本运算符两边单元格中的数据认为是文本数据，也可在运算符两边使用文本常量，文本常量要用英文的双引号“ “ ”括起来。如下所示：4、比较运算excel 的逻辑值有两个：FALSE（逻辑假）和 TRUE(逻辑真) 。Excel 可以进行多种关系运算，运算结果为逻辑值，有关的比较运算符及运算规则如下表所示：比较运算符 格式 含义= 数据 1=数据 2 判断 2 个数据是否相等，如果相等，则结果为 TRUE，否则为 FALSE 数据 1数据 2 判断数据 1 是否大于数据 2，如果是，则结果为 TRUE，否则为 FALSE

9、= 数据 1=数据 2 判断数据 1 是否大于或等于数据 2，如果是，则结果为 TRUE，否则为 FALSE数据 14 TRUE“33“4“ FALSE“4“33“ TRUE“ACD“=“acd“ TRUE77 比较运算符 后运算四、excel 的运算错误信息如果 Excel 根据单元格中公式进行计算得到一个正确的结果，就将该单元格的数据显示在该单元格中。如果不能得到一个正确结果，则在公式所在单元格显示一个错误信息。Excel 的错误信息表错误信息 含义 例#DIV/0! 这个公式试图使用 0作为除数如果公式中使用一个空单元格作为除数时，就可能会出现这个信息#NAME?公式使用了一个Excel

10、 不能识别的名称。如果删除了公式中使用的名称或误输入某个函数名时，就可能出现这个信息。#N/A公式中引用的（直接或间接）单元格中使用 NA 函数所标识的不能使用的数据。如果查找函数没有找到匹配数据时，就可能出现这个信息。#NULL!公式使用了一种不允许出现交叉但交叉在一起的两个区域#NUM! 使用的数据有问题 例如，在应该使用正值的 情况下，使用了负数。#REF! 公式引用了一个无效 的单元格如果单元格从工作表中删除了，则经常会出现这种信息。#VALUE!公式包括了错误形式的变量或运算（使用错误的参数或运算数的类型错误）一个运算对象引用一个值或单元格引用，但是它们中却需要使用公式计算结果。#!

11、单元格要显示的数据太长，单元格现在的宽度不能完整显示这个数据第二节 单元格引用及单元格区域引用一、单元格引用的概念及基本格式在 excel 的计算公式中，用单元格的坐标来说明在计算公式中要使用某个单元格的数据，或说明要使用某个单元格区域的数据来进行计算。所以将计算公式中的单元格坐标叫做单元格引用，如果这些坐标代表的是一个区域，则叫做单元格区域引用。1、单元格引用单元格引用的基本格式： 列名行号单元格引用代表一个单元格。例：A1代表工作表左上角的第一个单元格，即第一行第一列的单元格B4代表工作表第 2 列第 4 行的单元格2、单元格区域引用单元格区域引用的基本格式： 单元格引用 1：单元格引用

12、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 行的所有单元格

13、组成的单元格区域，如图所示：4、由一列或若干列中所有单元格组成的单元格区域基本格式：列名 1：列名 2代表由“列名 1”至“列名 2”所有列组成的单元格区域，如图所示：例： C：D 表示由第 C 列和第 D 列的所有单元格组成的单元格区域，如图所示。5、不同工作表之间单元格数据的引用格式：工作表名！单元格引用例：Sheet1!A1表示工作表“Sheet1”A1 单元格的引用职工信息表！C3表示 “职工信息表”中 C3 单元格的引用6、三维引用用一个区域引用表示连续的若干张工作表中的同一个单元格的集合。格式 I： 工作表 1：工作表 2！单元格引用作用：代表由“工作表 1”至“工作表 2”连续的

14、若干张工作表中的同一单元格格式 II：工作表 1：工作表 2！单元格区域引用作用：代表由“工作表 1”至“工作表 2”连续的若干张工作表中的同一区域中的所有单元格例：1 月份工资表:3 月份工资表 !C3 代表 1 月份工资表、2 月份工资表及 3 月份工资中的C3 单元格，也就是说这个单元格引用代表了 3 个单元格，如下图所示：根据上图，假设每月工资表格式相同，且张千一在这 3 个月的工资表中都的位置都相同，那么，计算公式：=SUM(1 月份工资表:3 月份工资表 !C3)表示求张千一这个职工 13月份的基本工资合计。7、相对引用、绝对引用、混合引用Excel 将单元格引用分为相对引用、绝对

15、引用和混合引用，相对引用是如前面介绍的这些引用的格式均为相对引用，绝对引用则是在列坐标和行坐标的前面加上美元符号“$” ，混合引用则仅在列坐标前美元符号“$”或在行号前美元符号“$” ，如下所示：A1相对引用$A$1绝对引用$A1混合引用A$1混合引用这四种引用，都代表工作表中的同一个单元格 A1 单元格（第 1 行第 1 列所对应的单元格） 。excel 的计算公式中使用这四种单元格引用，对于计算的作用是相同的，例如： A1+B1，A1+$B1，$A$1+$B$1 ，其效果是相同的，都是将 A1 单元格的数据与 B1单元格中的数据相加，计算结果也是相同的。那么在 excel 中使用这么多种引

16、用有什么作用呢？这四种引用的不同之处在于，当一个单元格中的计算公式复制到另一个单元格中时，公式中的单元格引用是否发生变化以及如何发生变化。如果是计算公式中使用的是相对引用，那么当某个单元格中的公式复制到这个单元格的上方单元格中时，公式中单元格引用的行号将减少，新单元格如果在原单元格上方 n 行，则单元格引用中的行号将减少 n，而单元格中计算公式向下方的单元格复制时，则公式中的单元格引用将增加，新单元格如果在原单元格下方 n 行，则单元格引用中的行号将增加n。同理，左右复制时，则计算公式中的单元格引用的列坐标将发生相应的变化，向左复制，列坐标减少，向右复制时，列坐标增加，减少或增加的规律与行坐标

17、变化的规律相同。在绝对引用、或混合引用中，坐标前面的美元符号“$”是使计算公式在复制时，计算公式中的单元格引用中的相应的坐标不发生变化，如果列坐标前加了美元符号“$” ，则在左右复制时，列坐标不变；如果在行号前加了美元符号“$” ，则在上下复制时，行坐标不发生变化。例如：D5 单元格中如果有计算公式： “=E7+F8”，那么，将该公式复制到 B1 单元格时，B1 单元格中的计算公式将变成： “=C3+D4”，这是因为计算公式向左复制到了 2 列，所以，计算公式中的相对引用的列坐标就相应减少 2 列，从原来的 E 列变为 C 列，从原来的 F 列变成 D 列；而计算公式又向上复制了 4 行，所以

18、计算公式中的相对引用的行坐标就相应减少 4 行，从原来的 7 行将减少为 3 行，以及从原来的 8 行减少为 5 行。而如果 D5 单元格中的计算公式是： “=$E$7+$F$8”，则计算公式复制到 B1 单元格，还是原来的计算公式，并不发生改变。如果 D5 单元格中的计算公式是： “=$E7+F$8”，则计算公式复制到 B1 单元格后，公式变为：=$E3+D$8，因为列坐标 E 前面加了美元符号“$” ，和行号 8 前面加了美元符号“$”，所以复制后，这两个坐标没有发生变化。当在 excel 的工作表中有大量相似或相同的计算公式时，灵活运用，可以极大地减少输入计算公式的工作量。单元格引用应用

19、举例例 1：引用同一工作表中的数据进行计算工资计算在该表中， “缺勤应扣工资”的计算公式为： )5.2/(基 本 工 资缺 勤 天 数 每 工 作 日 平 均 工 资缺 勤 天 数缺 勤 应 扣 工 资 “应发工资”的计算公式为： 缺 勤 应 扣 工 资书 报 费津 贴基 本 工 资应 发 工 资 （1）因为张千一的“基本工资”在 C3 单元格， “津贴”在 D3 单元格， “书报费”在E3 单元格， “缺勤天数”在 F3 单元格， “缺勤应扣工资”在 G3 单元格，所以，“缺勤应扣工资” （G3 单元格）中的计算公式为： =F3*（C3/22.5 ）“应发工资” （H3 单元格）中的计算公式

20、为： =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张车车

21、 =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 相对引用在公式复制时的特点，可以只输入张千一的“缺勤应扣工资”和“应发工资”计算公式，然后通过“复

22、制” 、 “粘贴”操作就可得到其余人员的“缺勤应扣工资”和“应发工资”计算公式。这样可以大量节省录入计算公式的时间。例 2：计算函数 在 x=0，0.1，0.2，的值，并列成一个函数5.3)(xexf值表。从上表可以看出：（1）计算函数值时，计算公式中用自变量 x 的值所在单元格的引用代替代替函数表达式中的自变量 x 就可计算函数的值。（2）从函数值计算公式列表中可以看出，由于自变量 x 的每一个值在不同的单元格，所以计算公式中代替自变量 x 的单元格引用也不同。（3）由于自变量 x 的值存放是有规律变化的，所以计算公式中，代替自变量 x 的单元格引用也是有规律的变化的，所以利用 excel

23、的相对引用，只需输入计算 f(x)函数值的第1 个公式，然后通过“复制” 、 “粘贴”命令就可得到其余函数值的计算公式。例 3：增长率计算（环比增长率相对应用和定比增长率绝对引用） 。（1）计算“比 2006 年第 1 季度增长（%） ”的计算公式： %10206)1( 季 度 税 收 额年 第 季 度 税 收 额年 第本 季 度 税 收 额本 季 度 增 长 率（2）计算“比上一季度的增长（%） ”的计算公式： 10上 季 度 的 税 收 额上 季 度 的 税 收 额本 季 度 的 税 收 额比 上 一 季 度 的 增 长 率根据上述计算公式，可以得到表中各个增长率的计算公式如下表：税收增长

24、率计算公式季度 比 2006 年第 1 季度增长（%） 比上一季度 增长（%）2006 年第 1 季度 2006 年第 2 季度 =(C4-C3)/C3 =(C4-C3)/C32006 年第 3 季度 =(C5-C3)/C3 =(C5-C4)/C42006 年第 4 季度 =(C6-C3)/C3 =(C6-C5)/C52007 年第 1 季度 =(C7-C3)/C3 =(C7-C6)/C62007 年第 2 季度 =(C8-C3)/C3 =(C8-C7)/C72007 年第 3 季度 =(C9-C3)/C3 =(C9-C8)/C82007 年第 4 季度 =(C10-C3)/C3 =(C10-

25、C9)/C92008 年第 1 季度 =(C11-C3)/C3 =(C11-C10)/C102008 年第 2 季度 =(C12-C3)/C3 =(C12-C11)/C112008 年第 3 季度 =(C13-C3)/C3 =(C13-C12)/C122008 年第 4 季度 =(C14-C3)/C3 =(C14-C13)/C132009 年第 1 季度 =(C15-C3)/C3 =(C15-C14)/C142009 年第 2 季度 =(C16-C3)/C3 =(C16-C15)/C152009 年第 3 季度 =(C17-C3)/C3 =(C17-C16)/C162009 年第 4 季度 =

26、(C18-C3)/C3 =(C18-C17)/C172010 年第 1 季度 =(C19-C3)/C3 =(C19-C18)/C182010 年第 2 季度 =(C20-C3)/C3 =(C20-C19)/C192010 年第 3 季度 =(C21-C3)/C3 =(C21-C20)/C202010 年第 4 季度 =(C22-C3)/C3 =(C22-C21)/C212011 年第 1 季度 =(C23-C3)/C3 =(C23-C22)/C222011 年第 2 季度 =(C24-C3)/C3 =(C24-C23)/C232011 年第 3 季度 =(C25-C3)/C3 =(C25-C2

27、4)/C242011 年第 4 季度 =(C26-C3)/C3 =(C26-C25)/C252012 年第 1 季度 =(C27-C3)/C3 =(C27-C26)/C262012 年第 2 季度 =(C28-C3)/C3 =(C28-C27)/C27（1）从上表中可以看出， “比上一季度增长（%） ”的计算公式中，上一计算公式中单元格引用的行号在下一行对应位置增长 1，所以可以用相对引用输入这一列中的第 1 个计算公式，然后通过“复制” 、 “粘贴”操作得到本列的其余计算公式。（2） “比 2006 年第 1 季度增长（%） ”列的计算公式中，只有第 1 个单元格引用为有规律的变化，而第 2

28、 个、第 3 个单元格引用均为 C3，为了使 “复制” 、 “粘贴”操作不改变计算公式中的单元格引用 C3，则 C3 使用绝对引用，使该列的第 1 个计算公式变为：=(C4-$C$3)/$C$3在输入这一列的第 1 个计算公式后，在通过“复制” 、 “粘贴”操作就可得到这一列的其余计算公式。如下图所示：图：输入计算增长率的第 1 行的计算公式图：输入计算公式后的计算结果图：复制第 1 行并粘贴其余行后的效果例 4：按学分加权的平均成绩的计算（绝对引用与相对引用）计算方法 门 课 程 学 分第门 课 程 学 分第门 课 程 学 分第 门 课 程 学 分第门 课 程 成 绩第门 课 程 学 分第门

29、 课 程 成 绩第平 均 成 绩学 分 加 权 n 21 *根据成绩统计表的布局，可以得到各个学生的学分加权平均成绩的计算公式如下表所示：姓名 学分加权平均成绩计算公式陈曦阳 =(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

30、)李平国 =(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，而学分的

31、单元格引用不变。所以在输入第 1 个学生的“学分加权平均成绩计算公式”时，代表成绩的单元格引用使用相对引用，而代表学分的单元格引用使用绝对引用（因为在复制公式时，相对引用会发生有规律的变化，而绝对引用不发生变化），然后使用“复制”、“粘贴”操作就可得到其余学生的计算公式。修改后第 1 个学生的“学分加权平均成绩计算公式”如下：=(B4*$C$3+D4*$E$3+F4*$G$3+H4*$I$3)/($C$3+$E$3+$G$3+$I$3)图：输入第 1 个学生平均成绩计算公式后的计算结果将第 1 个学生平均成绩计算公式（J4 单元格）复制到其他行后的计算结果例 5：银行存款在不同利率情况下，每一年末的本息和计算（混合引用） 。例 6：不同工作表数据的引用（多年的利润表每年一张利润表，汇总到一张表上）计算公式：主营业务收入、主营业务利润、营业利润、所得税、净利润与原表相同主营业务成本及税金=主营业务成本+主营业务税金及附加经营费用=营业费用+ 管理费用+ 财务费用例 7：根据资产负债表（在一张工作表上）和损益表（在另一张表）计算财务指标（在第3 张表）