《Excel-2016数据处理与分析》教学课件04数据的简单计算

模块4数据的简单计算本模块有3个学习目标：一是了解公式基础并学会使用简单公式，为函数的运用打下基础；二是学会定义和调用单元格名称；三是学会合并计算，为批量的简单数据计算提供帮助。学习目标本模块的学习重点主要有2个：公式的定义和使用，单元格名称的运用。学习重点4.1

公式的基础知识公式是以等号“=”开始（应用格式见图4-1），其后由数值或字符串、函数及参数、运算符、单元格地址等元素构成，并自动得到结果的表达式。4.1.1

使用简单公式计算数据根据已有的数据，可以使用一些简单公式来计算出新的数据，图4-1所示为输入最简单的加法公式得出计算结果：在A1单元格中输入公式“=3+3”，按Enter键，会自动得到结果6。4.1.1使用简单公式计算数据【实例4-1】制作生产资料配置简单计算模型产品产量和成本资料的统计计算属于周期性操作，对Excel熟悉的老王决定制作一个简单计算模型一次性解决后期所有的计算操作，他的操作步骤如下。步骤1打开素材文件中的“素材\模块4\4.1\生产资料分析模型.xlsx”文件，选择F6单元格，输入“=（F3-”，如图4-2所示。4.1.1使用简单公式计算数据4.1.1使用简单公式计算数据步骤2接着输入“-F5）/（F2-F4）”，完善公式，按Ctrl+Enter组合键确认，如图4-3所示。步骤3重复步骤1、步骤2的操作，在F6、F7、F9单元格中分别输入对应的公式，如图4-4所示。4.1.1使用简单公式计算数据4.1.1使用简单公式计算数据步骤4在F2、F3、F4、F5单元格中输入对应的数值，对应的单元格中自动显示计算结果，如图4-5所示。支招：除了直接使用加减乘除对数据进行计算外，还可以使用“选择性粘贴”的方式计算数据，操作方法为：在除数据区域的任意单元格中输入“1.2”，如H5单元格，按Ctrl+C组合键复制H5单元格，选择F3：F10单元格区域，切换到“开始”选项卡，单击“剪贴板”组中的“粘贴”下拉按钮，选择“选择性粘贴”选项，打开“选择性粘贴”对话框，选中计算方式单选按钮，如选中“乘”单选按钮，单击“确定”按钮，如图4-6所示。4.1.1使用简单公式计算数据4.1.1使用简单公式计算数据实现步骤视频4.1.2

公式的审核在使用公式的过程中，难免会因为一些输入错误或参数设置错误而导致结果不正确，更麻烦的是找不到错误的位置，此时需对公式进行审核，以确保公式的正确性，同时能及时检查出错误的位置。【实例4-2】显示负债表中的计算公式打开公司的资产负债表后，为了保证表中数据计算的准确性，老李让所有的公式完全显示，然后检查核对单元格引用是否正确，他的操作方法如下。步骤1打开素材文件中的“素材\模块4\4.1\资产负债表.xlsx”文件，在“公式”选项卡下单击“公式审核”组中的“显示公式”按钮，如图4-7所示。步骤2在表格中可以看到公式全部显示，如图4-8所示。提示：单击“显示公式”按钮或按Ctrl+`组合键切换为显示计算结果。4.1.2公式的审核实现步骤视频4.1.2公式的审核【实例4-3】追踪销售表中的单元格引用情况助理小陈想检查审核统计销售表中数据的引用是否正确，正在逐一选择查看，正当他忙得不可开交时，同事小王建议他使用“追踪引用单元格”功能一次性全部显示审核，操作步骤如下。步骤1打开素材文件中的“素材\模块4\4.1\销售表.xlsx”文件，选择B10单元格，切换到“公式”选项卡，单击“追踪引用单元格”按钮，如图4-9所示。步骤2表格中自动用蓝色箭头标示B10单元格引用的数据计算单元格B3和B9，如图4-10所示。4.1.2公式的审核实现步骤视频4.1.2公式的审核支招：要想知道某个单元格中的数据参与了哪些单元格公式计算，直接用“追踪从属单元格”标识，方法为：选择目标数据单元格，单击“公式审核”组中的“追踪从属单元格”按钮（追踪从属单元格或是追踪引用单元格的箭头都无法保存，甚至只按Ctrl+S组合键保存都会消失，这一点读者一定要清楚），如图4-11所示。4.1.2公式的审核4.2

单元格名称的使用为了便于操作，用户可以为单元格或单元格区域定义一个名称，在公式中引用该单元格或单元格区域时，可以直接使用该名称来代替这些单元格或单元格区域。4.2.1

定义名称的优点Excel中的单元格可以起一个名字，即单元格名称。与函数一样，单元格名称也是一种特殊的公式，由“=”号开始，基本上都是由用户自定义。其中，名称可以由常量数据、数组、单元格引用、函数及公式等元素组成，同时每个单元格名称都具有一个唯一的标识。合理使用单元格名称，可以提高公式编写的正确性，同时定义名称还有以下几个优点。4.2.1定义名称的优点1.增强公式的可读性对于某些复杂的公式而言，使用单元格名称可以提高公式的可读性。例如，将存放在A4：A15单元格区域中的数学成绩数据定义为“数学”，使用以下两个公式都可以获得数学学科的平均成绩，但公式一明显比公式二更加容易理解。公式一：=AVERAGE（数学）。公式二：AVERAGE（B3：B12）。4.2.1定义名称的优点2.便于统一修改公式例如，在员工工资表中，有多个公式都使用2000作为基本工资乘以不同奖金系数计算员工的实际工资。当基本工资的数额发生改变时，需要依次对多个相关公式进行修改，这就显得较为烦琐。如果定义一个“基本工资”的名称并引用到公式中，则只需要修改名称，即可使所有公式发生改变。3.简化复杂的公式在一些比较复杂的公式中，可能需要重复使用相同的公式段来进行计算，从而使整个公式变得冗长，4.2.1定义名称的优点这不利于阅读与修改，如“=IF（SUM（$C2：$G2）=0，0，H2/SUM（$C2：$G2））”。此时，用户可以将公式中的“SUM（$C2：$G2）”修改为“总销售量”，则公式被简化为“=IF（总销售量=0，0，H2/总销售量）”。通过此方式来简化公式，在公式越长、越复杂的情况下效果越明显。4.2.1定义名称的优点4.替代单元格区域存储常量数据在Excel的部分查询计算中，常常会选择使用关系对应表作为查询的依据。此时，可以使用常量数组来定义名称，从而节约了单元格的存储空间，以避免删除或修改等操作导致关系对应表的变动与缺失。4.2.1定义名称的优点5.简化复杂的公式用户除了可以在单元格中直接使用数据外，还可以在数据有效性和条件格式的设置中使用公式。在数据有效性和条件格式中使用公式时，程序不允许直接使用常量数组或交叉引用，此时可以将常量数组或交叉引用部分定义为名称，然后在数据有效性和条件格式中对其进行调用。提示：在Excel97-2003中，不能在数据有效性和条件格式中直接引用其他工作表中的单元格区域，只能使用INDIRECT（）函数进行引用或将跨表区域定义为名称，然后在数据有效性和条件格式中对定义的名称进行调用，而在Excel2016中就不存在这个问题。4.2.2

定义单元格名称在Excel工作表中进行大量的数据分析时，通常会录入许多公式，在录入公式时懂得运用名称可以使工作更加轻松，以后对公式进行编辑也更加方便。【实例4-4】在试用员工成绩评定表中定义单元格名称在试用员工成绩评定表中，老李需要根据I列和J列中的总分数据评定哪些员工可以转正，为了使评定结果更加直观（便于审查评定人员查看公式），老李使用单元格名称，具体操作方法如下。步骤1打开“素材文件\模块4\4.2\试用员工成绩评定表.xlsx”文件。在工作表中选择I3：I12单元格区域，切换到“公式”选项卡，在“定义的名称”组中单击“定义名称”按钮，如图4-12所示。步骤2打开“新建名称”对话框，在“名称”文本框中输入“总分”，在“范围”下拉列表框中选择“工作簿”选项，单击“确定”按钮，如图4-13所示。4.2.2定义单元格名称4.2.2定义单元格名称提示：名称在相同的使用范围内必须保持唯一，但是在不同的使用范围中可以不用保持唯一，即在不同的范围中可以使用同一个名称。步骤3在工作表中选择J3：J12单元格区域，切换到“公式”选项卡，在“定义的名称”组中单击“定义名称”按钮，如图4-14所示。步骤4打开“新建名称”对话框，在“名称”文本框中输入“平均分”，在“范围”下拉列表框中选择“工作簿”选项，单击“确定”按钮，如图4-15所示。支招：为单元格命名的操作相对比较简单，只需要在选择目标单元格后，在编辑栏中的文本框中输入名称，然后按Enter键即可。4.2.2定义单元格名称实现步骤视频4.2.2定义单元格名称【实例4-5】在试用员工成绩评定表中批量定义单元格名称在试用员工成绩评定表中，老李需要根据I列和J列中的总分数据评定哪些员工可以转正，由于定义的单元格名称与标题行名称相同，老李使用批量定义方法，具体操作方法如下。步骤1打开“素材文件\模块4\4.2\试用员工成绩评定表1.xlsx”文件。在工作表中选择C2：H12单元格区域，切换到“公式”选项卡，在“定义的名称”组中单击“根据所选内容创建”按钮，如图4-16所示。步骤2打开“根据所选内容创建名称”对话框，选中“首行”复选框，单击“确定”按钮，如图4-17所示。4.2.2定义单元格名称4.2.2定义单元格名称步骤3在“名称管理器”对话框中看到批量定义单元格名称效果（在4.2.4中讲解打开名称管理器的操作），如图4-18所示。实现步骤视频4.2.2定义单元格名称支招：除了在“名称管理器”中查看单元格定义的名称外，还可以在名称框中查看，方法为：单击名称框下拉按钮，在弹出的下拉选项中可以直接看到，如图4-19所示。4.2.3

应用单元格名称在工作表中为单元格区域定义名称以后，就可以将其应用到公式中参与计算（这也是定义名称的作用和存在的意义）。【实例4-6】在试用员工成绩评定表中应用单元格名称老李在试用员工成绩评定表中定义了“总分”和“平均分”的单元格名称，分别引用了I3：I12和J3：J12单元格区域，下面需要在K3：K12单元格区域中调用名称参与到IF函数的判定中，操作方法如下。4.2.3应用单元格名称步骤1打开“素材文件\模块4\4.2\试用员工成绩评定表1.xlsx”文件，选中K3单元格，在编辑栏中输入公式的部分内容，这里输入“=IF（AND（”，如图4-20所示。步骤2保持文本插入点在编辑栏的公式后面，切换到“公式”选项卡，在“定义的名称”组中单击“用于公式”下拉按钮，在弹出的下拉列表中选择“总分”选项，如图4-21所示。4.2.3应用单元格名称4.2.3应用单元格名称4.2.3应用单元格名称步骤5K3单元格中将会显示计算结果，然后将其填充到K4：K12单元格区域中，如图4-24所示。4.2.3应用单元格名称支招：对于记忆力比较好的用户而言，可以在编辑栏中直接调用单元格名称，即输入单元格名称前面部分的关键字，程序将会自动显示出相关的单元格名称，然后在列表中选择目标单元格名称即可，如图4-25所示。实现步骤视频4.2.4管理定义的名称单元格名称定义后，用户可以根据实际需要对其进行管理，如删除单元格名称、修改单元格名称、查看单元格名称范围等。1.删除单元格名称对于工作簿或工作表中不再使用的单元格名称，用户可以将其删除，从而节省工作簿的内存，具体操作如下：在工作表中切换到“公式”选项卡，在“定义的名称”组中单击“名称管理器”按钮，打开“名称管理器”对话框，在其列表框中选择需要删除的单元格名称，单击“删除”按钮，然后单击“关闭”按钮即可完成操作，如图4-26所示。4.2.4管理定义的名称4.2.4管理定义的名称2.修改单元格名称对于已经定义好的单元格名称，如果用户觉得其名称不太合适，还可以通过更改的方式对其进行编辑，操作方法为：打开“名称管理器”对话框，在其列表框中选择需要编辑的单元格名称，单击“编辑”按钮，打开“编辑名称”对话框，在“名称”文本框中对名称进行修改，单击“确定”按钮，如图4-27所示。4.2.4管理定义的名称4.2.4管理定义的名称3.查看单元格名称范围在调用和引用数据单元格之前，如果不清楚单元格名称的数据作用范围，可以先进行查看，然后对其单元格名称进行操作，具体操作如下：在工作表编辑栏的文本框中单击右侧下拉按钮，在打开的下拉列表中选择目标单元格名称。此时，在编辑区中即可查看到目标单元格名称的范围被突出显示，如图4-28所示。4.2.4管理定义的名称4.3

数据的合并计算在Excel中，合并计算就是把两个或两个以上的表格中具有相同区域或相同类型的数据运用相关函数（如求和、计数、平均等）运算后，再将结果存放到另一个当前表格的指定区域或指定表格中。4.3数据的合并计算【实例4-7】将1~3月销售数据按位置合并计算文具店老板老王要统计1~3月的销售额和毛利总和，由于销售数据放在3张不同的工作表中，为了数据的正确和操作简便，老王使用合并计算功能，一次性统计出了1~3月的销售额和毛利总和，他的操作步骤如下。步骤1打开素材文件中的“素材\模块4\4.3\门店销售.xlsx”文件，选择“季度盘点”工作表，选择A1：C13单元格区域，在“数据”选项卡下，单击“数据工具”组中的“合并计算”按钮，如图4-29所示。步骤2打开“合并计算”对话框，将鼠标指针定位于“引用位置”文本框内，单击“1月”工作表标签，在表格中选择A1：C13单元格区域，单击“添加”按钮，如图4-30所示。4.3数据的合并计算4.3数据的合并计算步骤3重复步骤2的操作，分别将2月和3月表格中的A1：C13单元格区域添加到“所有引用位置”列表框中作为合并计算参数，单击“添加”按钮，选中“最左列”复选框，单击“确定”按钮，如图4-31所示。步骤4完成按照位置合并计算，将1~3月的销售额和毛利统计到一个数据工作表中，便于数据的查看和分析，如图4-32所示。4.3数据的合并计算实现步骤视频4.3数据的合并计算【实例4-8】将多个月份销售数据按类合并计算老王要统计出各个产品的销售数据总和，看着密密麻麻的数据和办公用品名称，手动计算特别麻烦，此时老王使用合并计算功能快速地计算出了各个类别文具的销售总和，他的操作步骤如下。步骤1打开“素材\模块4\4.3\门店销售1.xlsx”文件，选择工作表中除数据外的任意单元格（如F4单元格），在“数据”选项卡下，单击“数据工具”组中的“合并计算”按钮，如图4-33所示。4.3数据的合并计算4.3数据的合并计算步骤2打开“合并计算”对话框，将鼠标指针定位于“引用位置”文本框中，在表格中选择A1：C37单元格区域，选中“最左列”复选框，单击“确定”按钮，如图4-34所示。步骤3Excel自动将不同的办公用品按照不同类别合并计算，并自动将结果数据汇总到指定的单元格区域中，如图4-35所示。提示：创建指向源数据的链接，是数据被一个链接所表示，当我们通过Excel连接这个链接时，就能得到这个链接的数据。4.3数据的合并计算实现步骤视频4.4综合实例对于一个公司而言，通常具有多个部门，每个部门的工作情况不同，所以其工资划分情况也不同。作为公司的财务人员，每个月都需要在固定的时间内计算员工的工资，所以掌握员工工资数据的计算方法是非常有必要的，具体操作方法如下。4.4综合实例步骤1打开“素材文件\模块4\综合实例\员工工资分析表.xlsx”文件，选择目标数据单元格区域，切换到“公式”选项卡，在“定义的名称”组中单击“根据所选内容创建”按钮，打开“根据所选内容创建名称”对话框，