第6章使用公式计算数据

1、Excel数据处理与分析实例教程（第3版）教案第6章 使用公式计算数据教学内容1. 公式的组成2. 运算符的种类及用法3. 公式的输入和编辑4. 单元格引用5. 合并计算6. 公式的审核教学要求【知识目标】1. 理解单元格引用的概念2. 了解公式的基本概念3. 理解公式审核的意义和方法【技能目标】1. 熟练掌握公式的使用方法2. 掌握合并计算的方法教学重点1. 单元格引用的概念2. 公式的输入3. 合并计算教学难点1. 单元格的引用2. 使用公式计算数据教学方法讲授法、案例法、演示法课时数6课时（讲授3课时，实验3课时）课程导入【思路】导入实例，提出问题，组织学生讨论。通过讨论，引出第6章要介

2、绍的使用公式计算数据的问题。【实例】计算和调整工资项（6.7实例）计算销售业绩奖金（6.8实例）【问题】1. 在Excel中，怎样计算数据？2. 在Excel中，计算数据的快捷方法是什么？ 3. 在Excel中， 可以输入公式吗？6.1 认识公式公式是Excel的核心，使用公式可以轻松而快速地进行计算处理。6.1.1 公式的概念公式是指以“=”开始，通过使用运算符将数据、函数等元素按一定顺序连接在一起，从而实现对工作表中的数据进行计算和处理的等式。公式的计算结果是数据值。6.1.2 公式的组成公式由前导符等号（=）、常量、单元格引用、区域名称、函数、括号及相应的运算符组成。其中，常量是指通过键

3、盘直接输入到工作表中的数字或文本，如115、“day”等。单元格引用是指通过使用一些固定的格式，引用单元格中的数据，如B5、$B$10、$A1:C$10等。区域名称是指直接引用为该区域定义的名称。假设将区域E1:F10命名为“day”，那么在计算时可以使用该名称代替此区域。例如，求该区域数据之和，可将计算公式写为：=sum(day)。函数是Excel提供的各种内置函数，使用时直接给出函数名及参数即可，如=sum(day)。括号是为了区分运算顺序而增加的一种符号。运算符是连接公式中基本运算量并完成特定计算的符号，如“+”“&”等。6.1.3 公式中的运算符运算符是公式中不可缺少的部分，主

4、要包括算术运算符、文本运算符和关系运算符。1算术运算符使用算术运算符可以实现基本的算术运算。算术运算符包括加（+）、减（）、乘（*）、除（/）、百分号（%）和乘方（）。由算术运算符、数值常量、值为数值的单元格引用以及数值函数等组成的表达式称为算术表达式。算术表达式运算的结果为数值型。2文本运算符使用文本运算符可以实现文本数据的连接运算。文本运算符只有1个，即连接符（&），其功能是将2个文本数据首尾连接在一起，形成1个新的文本数据。由文本运算符、文本型常量、值为文本的单元格引用，以及文本型函数等组成的表达式称为文本表达式。文本表达式运算结果为文本。3关系运算符使用关系运算符可以实现比较运

5、算。关系运算符包括大于（>）、大于等于（>=）、小于（<）、小于等于（<=）、等于（=）和不等于（<>）6种。关系运算用于比较2个数据的大小。由关系运算符、数值表达式、文本表达式等组成的表达式称为关系表达式。关系表达式运算的结果为一个逻辑值：TURE（真）或FALSE（假）。这里需要注意的是，关系运算符两侧的表达式应为同一种类型。4运算符的优先级如果一个表达式用到了多种运算符，那么这个表达式中的运算将按一定的顺序进行，这种顺序称为运算的优先级。运算符的优先级从高到低依次为：（乘方）（负号）%（百分比）*、/（乘或除）+、（加或减）&（文本连接）>

6、;、>=、<、<=、=、<>（比较）。6.2 输入公式6.2.1 手工输入手工输入就是完全通过键盘来输入整个公式。方法是先输入一个等号，然后依次输入公式中的各元素。6.2.2 单击单元格输入除手工输入公式外， Excel还提供了一种简捷、快速的输入公式方法，即通过单击单元格输入运算元素，手工输入运算符。6.3 编辑公式如果输入的公式有误，可以对其进行修改。修改公式的方法与修改单元格中数据的方法相同。此外，还可以对输入的公式进行复制、显示、删除等操作。6.3.1 复制公式可以使用自动填充的方法将公式复制到连续区域的单元格中，也可以使用“复制”命令实现不连续区域的公式

7、复制。6.3.2 显示公式默认情况下，含有公式的单元格中显示的数据是公式的计算结果。但是，如果希望显示公式，则可以按照书中的步骤进行操作。6.3.3 删除公式如果希望在含有公式的单元格中只显示和保留其中的数据，可以将公式删除。6.4 引用单元格Excel在处理数据时，几乎所有公式都要引用单元格或单元格区域，引用的作用相当于链接，指明公式中使用的数据位置。公式的计算结果取决于被引用的单元格中的值，并随着其值的变化发生相应的变化。在Excel中，单元格的引用方式主要有3种，分别是相对引用、绝对引用和混合引用。6.4.1 相对引用相对引用是指公式所在的单元格与公式中引用的单元格之间的相对位置。若公式

8、所在单元格的位置发生了变化，那么公式中引用的单元格的位置也将随之发生变化，这种变化是以公式所在单元格为基点的。6.4.2 绝对引用有时在公式中需要引用某个固定的单元格或单元格区域，无论将引用该单元格或单元格区域的公式复制或者填充到什么位置，都不希望它发生任何改变，这时就要使用单元格的绝对引用。6.4.3 混合引用有时希望公式中使用的单元格引用行号变化而列标不变，或者列标变化而行号不变，这时可以使用混合引用。6.4.4 外部引用在公式中引用单元格，不仅可以引用同一工作表中的单元格，而且可以引用同一工作簿其他工作表中的单元格，以及不同工作簿中的单元格。1引用同一工作簿不同工作表中的单元格2引用不同

9、工作簿中的单元格3三维引用6.5 合并计算在实际应用中，常常出现这样的情况：某公司分设几个分公司，各分公司已经分别建好了各自的年终报表，现在该公司要想得到总的年终报表，了解全局情况，就要将各分公司的数据合并汇总。此时就需要使用Excel的合并计算功能。目标工作表：存放合并计算结果的工作表，目标工作表应是当前工作表；目标区域：接收合并数据的区域，目标区域应是当前单元格区域；源工作表：被合并计算的各张工作表，源工作表可以是打开的，也可以是关闭的。来源区域：被合并计算数据的区域； Excel 提供了2种合并计算方式，即按位置合并计算和按分类合并计算。6.5.1 按位置合并计算最简单也是最常用的合并计

10、算是根据位置合并工作表。按位置合并工作表时，要求合并的各工作表格式必须相同。6.5.2 按分类合并计算如果要合并计算的各工作表格式不完全相同，则不能简单地使用按位置合并计算的方法汇总数据，而应该按分类进行合并。按分类合并计算的操作方法与按位置合并工作表的操作方法类似。6.6 审核公式“正确的数据”是Excel进行数据计算的基本条件之一。但是，如果工作表中的公式太多，就很难发现潜在的错误，这时可以使用公式审核工具来追踪引用单元格和从属单元格，以便了解某一单元格公式的来龙去脉。如果某单元格因为公式错误而出现错误信息，也可以通过公式审核工具找出公式错误的根源，找出引起错误的单元格。6.6.1 错误信

11、息处理错误信息是公式审核的基本功能之一。在使用公式或函数进行计算的过程中，如果使用不正确，Excel将在相应的单元格中显示一个错误值。6.6.2 追踪单元格当工作表中使用的公式非常复杂时，往往很难搞清公式与值之间的关联关系。例如，某单元格的公式引用了许多其他单元格，而该单元格又被其他单元格中的公式所引用。利用Excel提供的单元格追踪功能，可以有效地解决此类问题。Excel的审核工具追踪2种单元格，分别为引用单元格和从属单元格。1追踪引用单元格如果在选定的单元格中包含了一个公式或函数，在公式或函数中引用到的其他单元格称为引用单元格。2追踪从属单元格如果选定了一个单元格，而这个单元格又被一个公式

12、所引用，则被选定的单元格就是包含公式单元格的从属单元格。6.6.3 追踪错误如果某单元格因为公式错误而出现错误信息，如#VALUE!、#NULL!、#DIV/0！等，可以使用“公式审核”命令组中的“追踪错误”命令来追踪。6.7 应用实例计算和调整工资项Excel的功能之一是对工作表中已有数据进行计算处理。在第2章和第3章应用实例中，已经向“工资管理”工作簿的“工资计算”表和“人员清单”表输入了一些基础数据，但还有些工资项需要通过计算产生，如公积金、各类社保缴费（如医疗险、养老险和失业险）、应发工资、实发工资等。此外，在工资管理中，“基本工资”“职务工资”等是变动最多的数据项，有时是对所有员工按

13、相同幅度进行调整，有时是对所有员工按不同幅度进行调整，还有时是对个别员工进行调整。本节将介绍如何使用公式计算相关工资项，以及如何利用选择性粘贴、按位置合并计算和按分类合并计算等方法实现对工资项的调整。6.7.1 计算公积金假设住房公积金统一按基本工资和职务工资之和的12%计算，则“公积金”的计算公式为：公积金 = （基本工资+职务工资）×0.126.7.2 计算医疗险、养老险和失业险假设该公司“医疗险”“养老险”和“失业险”分别按基本工资和职务工资之和的2%、8%和0.5%扣除。公式分别为：医疗险 = （基本工资+职务工资）×0.02养老险 = （基本工资+职务工资）

14、15;0.08失业险 = （基本工资+职务工资）×0.0056.7.3 计算应纳税所得额应纳税所得额的计算公式为：应纳税所得额=应发工资-公积金-医疗险-养老险-失业险-专业附加扣除-50006.7.4 计算应发工资和实发工资应发工资和实发工资的计算公式分别为：应发工资 = 基本工资+职务工资+岗位津贴+工龄补贴+交通补贴+物价补贴+洗理费+书报费+奖金实发工资 = 应发工资-公积金-医疗险-养老险-失业险-所得税6.7.5 按相同幅度调整基本工资假设将所有员工的基本工资上调180元，可使用选择性粘贴实现。6.7.6 按不同幅度调整职务工资更多的情况是按照不同幅度调整工资。例如，高级

15、职称上调200元、中级职称上调100元、初级职称上调50元。这时可以根据特定要求或标准，先创建员工的“普调工资”表和“原始工资”表。“普调工资”表中存放需要调整的工资数据，“原始工资”表中存放“工资计算”表中的数据。然后将“普调工资”表与“原始工资”表作为源工作表，将“工资计算”表作为目标工作表，进行合并计算。6.7.7 调整个别岗位津贴还有的时候需要对部分员工的工资项进行调整。例如，要调整10名员工的岗位津贴，具体调整信息如图6-12所示。此时工作表内只包含需要调整的10名员工的信息。由于“工资计算”表和图6-12所示的工作表行数不一样，因此应按分类进行合并计算。6.8 应用实例计算销售业绩

16、奖金销售管理是企业信息管理系统的重要组成部分，销售管理的主要特点是需要及时对销售情况进行统计分析。例如，了解每名业务员的销售业绩，以便进行绩效考核。本节将应用Excel公式，对某公司业务员销售业绩的部分信息进行统计。6.8.1 销售信息简介假设该公司有业务员35名，有关2020年销售信息、2021年1月业绩奖金信息和奖金标准信息分别存放在名为“销售管理.xlsx”工作簿文件的“销售情况表”“1月业绩奖金表”和“奖金标准”3张工作表中。其中，图6-15所示表格为该公司2020年销售情况表的部分数据，图6-16所示表格为2021年1月业绩奖金表的部分数据。2月业务员业绩奖金表的工作表名为“2月业绩

17、奖金表”，其结构与“1月业绩奖金表”相同，目前只有序号和姓名2列数据。下面将根据“1月业绩奖金表”和“销售情况表”2张工作表，使用简单公式计算“2月业绩奖金表”中的总奖金和累计销售额。需要说明的是，“2月业绩奖金表”中只有总奖金和累计销售额2项数据可以通过简单公式计算获得。因此本节只介绍这2项数据的计算方法和操作步骤。6.8.2 计算总奖金总奖金计算方法为：总奖金=本月销售业绩×奖金百分比+奖励奖金计算第1名业务员“总奖金”的公式为：=E3*D3+F36.8.3 计算累计销售额累计销售额是到目前为止业务员销售业绩的总和，反映了业务员的总销售业绩。该指标可以帮助管理者了解每名业务员的销

18、售成绩和销售能力。累计销售额的计算方法是：累计销售额=上月累计销售额+本月销售业绩计算第1名业务员“累计销售额”的公式为：='1月业绩奖金表'!H3+E3本章主要介绍了数据计算的步骤和要点，包括公式的使用与审核、单元格的引用、数据的合并计算等。重点是通过计算工资管理工作簿中的工资数据和计算销售管理工作簿中业务员的销售业绩奖金，掌握应用Excel完成各种不同计算的方法。这部分是学习和应用Excel最重要也是最灵活的方面，是学习和掌握Excel的重点。一、选择题（见教材）二、填空题（见教材）三、问答题1单元格引用有哪些？各有什么特点？2在公式中如何引用其他工作簿中的单元格？3按位置合并计算与按分类合并计算有什么不同？4追踪引用单元格和从属单元格的目的是什么？5当单元格出现错误值时，如何判断错误产生的原因？1对第2章实训中所建“工资”表的相关工资项，按照以下规则完成计算。（1）加班工资：加班小时工资为50元，加班工资=加班小时工资*加班工时。（2）应发工资：基本工资、岗位津贴、行政工资、交通补助和加班工资等几项求和。（3）社保缴费：“养老保险”个人支付额为“基本工资”的8