大学计算机基础课件项目八 计算和分析Excel数据.pptx

大学计算机基础,项目八 计算和分析Excel数据,课堂学习目标 制作产品销售测评表 统计分析员工绩效表 制作销售分析表,任务一 制作产品销售测评表,图8-1 “产品销售测评表”工作簿效果,（一）公式运算符和语法 在Excel中使用公式前，首先需要大致了解公式中的运算符和公式的语法，下面分别对其进行简单介绍。 1．运算符 运算符即公式中的运算符号，用于对公式中的元素进行特定计算。运算符主要用于连接数字并产生相应的计算结果。运算符有算术运算符（如加、减、乘、除）、比较运算符（如逻辑值FALSE与TRUE）、文本运算符（如&）、引用运算符（如冒号与空格）和括号运算符（如()）5种，当一个公式中包含了这5种运算符时，应遵循从高到低的优先级进行计算；若公式中包含括号运算符，则一定要注意每个左括号必须配一个右括号。,2．语法 Excel中的公式是按照特定的顺序进行数值运算的，这一特定顺序即为语法。Excel中的公式遵循特定的语法：最前面是等号，后面是参与计算的元素和运算符。如果公式中同时用到了多个运算符，则需按照运算符的优先级别进行运算，如果公式中包含了相同优先级别的运算符，则先进行括号里面的运算，再从左到右依次计算。,（二）单元格引用和单元格引用分类 在使用公式计算数据前要了解单元格引用和单元格引用分类的基础知识。 1．单元格引用 Excel是通过单元格的地址来引用单元格的，单元格地址是指单元格的行号与列标的组合。例如，“=193800+123140+146520+152300”，数据“193800”位于B3单元格，其他数据依次位于C3、D3和E3单元格中，通过单元格引用，将公式输入为“=B3+C3+D3+E3”，同样可以获得相同的计算结果。,2．单元格引用分类 在计算数据表中的数据时，通常会通过复制或移动公式来实现快速计算，因此会涉及不同的单元格引用方式。Excel中包括相对引用、绝对引用和混合引用3种引用方法，不同的引用方式，得到的计算结果也不相同。,相对引用。相对引用是指输入公式时直接通过单元格地址来引用单元格。相对引用单元格后，如果复制或剪切公式到其他单元格，那么公式中引用的单元格地址会根据复制或剪切的位置而发生相应改变。 绝对引用。绝对引用是指无论引用单元格的公式的位置如何改变，所引用的单元格均不会发生变化。绝对引用的形式是在单元格的行列号前加上符号“＄”。,混合引用。混合引用包含了相对引用和绝对引用。混合引用有两种形式，一种是行绝对、列相对，如“B＄2”表示行不发生变化，但是列会随着新的位置发生变化；另一种是行相对、列绝对，如“＄B2”表示列保持不变，但是行会随着新的位置而发生变化。,（三）使用公式计算数据 Excel中的公式是对工作表中的数据进行计算的等式，它以“=（等号）”开始，其后是公式的表达式。公式的表达式可包含运算符、常量数值、单元格引用和单元格区域引用。,1．输入公式 在Excel中输入公式的方法与输入数据的方法类似，只需将公式输入相应的单元格中，即可计算出结果。输入公式的方法为选择要输入公式的单元格，在单元格或编辑栏中输入“=”，接着输入公式内容，完成后按【Enter】键或单击编辑栏上的“输入”按钮 即可。,在单元格中输入公式后，按【Enter】键可在计算出公式结果的同时选择同列的下一个单元格；按【Tab】键可在计算出公式结果的同时选择同行的下一个单元格；按【Ctrl+Enter】组合键则在计算出公式结果后，仍保持当前单元格的选择状态。,2．编辑公式 编辑公式与编辑数据的方法相同。选择含有公式的单元格，将插入点定位在编辑栏或单元格中需要修改的位置，按【Backspace】键删除多余或错误的内容，再输入正确的内容。完成后按【Enter】键即可完成公式的编辑，Excel会自动计算新公式。,3．复制公式 在Excel中复制公式是快速计算数据的最佳方法，因为在复制公式的过程中，Excel会自动改变引用单元格的地址，可避免手动输入公式的麻烦，提高工作效率。通常使用“常用”工具栏或菜单进行复制粘贴；也可以拖动控制柄进行复制；还可选择添加了公式的单元格，按【Ctrl+C】组合键进行复制，然后将插入点定位到要复制到的单元格，按【Ctrl+V】组合键进行粘贴就可完成公式的复制。,（四）Excel中的常用函数 Excel 2010中提供了多种函数，每个函数的功能、语法结构及其参数的含义各不相同， 常用的函数有SUM函数、AVERAGE函数、RANK函数、INDEX函数、IF函数、MAX/MIN函数、COUNT函数、SIN函数、PMT函数和SUMIF函数等。 SUM函数。SUM函数的功能是对选择的单元格或单元格区域进行求和计算，其语法结构为SUM（number1,number2,.），number1, number2,.表示若干个需要求和的参数。填写参数时，可以使用单元格地址（如E6,E7,E8），也可以使用单元格区域（如E6:E8），甚至混合输入（如E6,E7:E8）。,AVERAGE函数。AVERAGE函数的功能是求平均值，计算方法是：将选择的单元格或单元格区域中的数据先相加，再除以单元格个数，其语法结构为AVERAGE（number1,number2,.），其中，number1,number2,…表示需要计算的若干个参数的平均值。 RANK函数。RANK函数是排名函数，RANK函数最常用的是求某一个数值在某一区域内的排名，其语法结构为rank(number,ref,[order])。 partition_by_clause 将from子句生成的结果集划分为应用到RANK函数的分区；order_by_clause确定将RANK值应用到分区中的行时所使用的顺序。,INDEX函数。INDEX函数是返回表或区域中的值或对值的引用。函数INDEX()有两种形式：数组形式和引用形式。数组形式通常返回数值或数值数组；引用形式通常返回引用。其语法结构为INDEX(array， row_num，column_num)返回数组中指定的单元格或单元格数组的数值；INDEX(reference，row_num，column_num，area_num)返回引用中指定单元格或单元格区域的引用。,IF函数。IF函数是一种常用的条件函数，它能判断真假值，并根据逻辑计算的真假值返回不同的结果，其语法结构为IF（logical_test, value_if_true,value_if_false），其中，logical_test表示计算结果为true或false的任意值或表达式；value_if_true表示logical_ test为true时要返回的值，可以是任意数据；value_if_ false表示logical_test为false时要返回的值，也可以是任意数据。,MAX/MIN函数。MAX函数的功能是返回所选单元格区域中所有数值的最大值，MIN函数则用来返回所选单元格区域中所有数值的最小值。其语法结构为MAX/MIN（number1,number2,.），其中number1,number2,…表示要筛选的若干个数值或引用。 COUNT函数。COUNT函数的功能是返回包含数字及包含参数列表中的数字的单元格的个数，通常利用它来计算单元格区域或数字数组中数字字段的输入项个数，其语法结构为COUNT（value1, value2,.），value1, value2, .为包含或引用各种类型数据的参数（1～30个），但只有数字类型的数据才被计算。,SIN函数。SIN函数的功能是返回给定角度的正弦值，其语法结构为SIN(number)，number为需要计算正弦的角度，以弧度表示。 PMT函数。PMT函数的功能是基于固定利率及等额分期付款方式，返回贷款的每期付款额，其语法结构为PMT（rate,nper,pv,fv,type），其中，rate为贷款利率；nper为该项贷款的付款总数；pv为现值，或一系列未来付款的当前值的累积和，也称为本金；fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零，也就是一笔贷款的未来值为零；type为数字0或1，用以指定各期的付款时间是在期初还是期末。,SUMIF函数。SUMIF函数的功能是根据指定条件对若干单元格求和，其语法结构为SUMIF（range,criteria,sum_range），其中，range为用于条件判断的单元格区域；criteria为确定哪些单元格将被作为相加求和的条件，其形式可以为数字、表达式或文本；sum_range为需要求和的实际单元格。,任务实现,（一）使用求和函数SUM计算营业总额 求和函数主要用于计算某一单元格区域中所有数字之和。,图8-2 插入求和函数 图8-3 自动填充营业额,（二）使用平均值函数AVERAGE计算月平均营业额 AVERAGE函数用来计算某一单元格区域中的数据平均值，即先将单元格区域中的数据相加再除以单元格个数。,图8-4 更改函数参数 图8-5 自动填充月平均营业额,（三）使用最大值函数MAX和最小值函数MIN计算营业额 MAX函数和MIN函数用于返回一组数据中的最大值或最小值。,图8-6 选择“最大值”选项 图8-7 插入最大值函数,图8-8 插入最小值 图8-9 自动填充月最低营业额,（四）使用排名函数RANK计算名次 RANK函数用来返回某个数字在数字列表中的排位。,图8-10 选择RANK函数 图8-11 设置函数参数,（五）使用IF嵌套函数计算等级 嵌套函数IF用于判断数据表中的某个数据是否满足指定条件，如果满足则返回特定值，不满足则返回其他值。,图8-12 选择LF函数 图8-13 设置判断条件和返回逻辑值,（六）使用INDEX函数查询营业额 INDEX函数用于返回表或区域中的值或对值的引用。,图8-14 确认函数的应用 图8-15 选择参数,任务二 统计分析员工绩效表,图8-16 “员工绩效表”工作簿效果,（一）数据排序 数据排序是统计工作中的一项重要内容，在Excel中可将数据按照指定的顺序规律进行排序。一般情况下，数据排序分为以下3种情况。 单列数据排序。单列数据排序是指在工作表中以一列单元格中的数据为依据，对工作表中的所有数据进行排序。 ,多列数据排序。在对多列数据进行排序时，需要按某个数据进行排列，该数据则称为“关键字”。以关键字进行排序，其他列中的单元格数据将随之发生变化。对多列数据进行排序时，首先需要选择多列数据对应的单元格区域，然后选择关键字，排序时就会自动以该关键字进行排序，未选择的单元格区域将不参与排序。 自定义排序。使用自定义排序可以设置多个关键字对数据进行排序，并可以通过其他关键字对相同的数据进行排序。,（二）数据筛选 数据筛选功能是对数据进行分析时常用的操作之一。数据排序分为以下3种情况。 自动筛选。自动筛选数据即根据用户设定的筛选条件，自动将表格中符合条件的数据显示出来，而表格中的其他数据将隐藏。 自定义筛选。自定义筛选是在自动筛选的基础上进行操作的，即单击自动筛选后需自定义的字段名称右侧的下拉按钮，在打开的下拉列表中选择相应的选项确定筛选条件，然后在打开的“自定义筛选方式”对话框中进行相应的设置。 ,高级筛选。若需要根据自己设置的筛选条件对数据进行筛选，则应使用高级筛选功能。高级筛选功能可以筛选出同时满足两个或两个以上约束条件的记录。,任务实现,（一）排序员工绩效表数据 使用Excel中的数据排序功能对数据进行排序，有助于快速直观地显示并理解、组织和查找所需的数据。,图8-17 设置主要排序条件 图8-18 查看排序结果,图8-19 设置自定义序列 图8-20 查看自定义序列排序的效果,（二）筛选员工绩效表数据 Excel筛选数据功能可根据需要显示满足某一个或某几个条件的数据，而隐藏其他的数据。 1．自动筛选 自动筛选可以快速在数据表中显示指定字段的记录并隐藏其他记录。,2．自定义筛选 自定义筛选多用于筛选数值数据，设定筛选条件可以将满足指定条件的数据筛选出来，而将其他数据隐藏。,图8-21 自定义筛选,3．高级筛选 通过高级筛选功能，可以自定义筛选条件，在不影响当前数据表的情况下显示出筛选结果，而对于较复杂的筛选，可以使用高级筛选来进行。 4．使用条件格式 条件格式用于将数据表中满足指定条件的数据以特定的格式显示出来，从而便于直观查看与区分数据。,图8-22 设置条件格式 图8-23 应用条件格式,（三）对数据进行分类汇总 运用Excel的分类汇总功能可对表格中同一类数据进行统计运算，使工作表中的数据变得更加清晰直观。,图8-24 设置分类汇总,图8-25 查看嵌套分类汇总结果,（四）创建并编辑数据透视表 数据透视表是一种交互式的数据报表，可以快速汇总大量的数据，同时对汇总结果进行各种筛选，以查看源数据的不同统计结果。,图8-26 设置数据透视表的放置位置,图8-27 添加字段 图8-28 对汇总结果进行筛选,（五）创建数据透视图 通过数据透视表分析数据后，为了直观地查看数据情况，还可以根据数据透视表制作数据透视图。,图8-29 创建数据透视图,图8-30 筛选数据透视图,任务三 制作销售分析表,图8-31 “销售分析表”工作簿效果,（一）图表的类型 图表是Excel重要的数据分析工具，Excel提供了多种图表类型，包括柱形图、条形图、折线图、饼图和面积图等，用户可根据不同的情况选用不同类型的图表。下面介绍5种常用的图表类型及其适用情况。 柱形图。柱形图常用于几个项目之间数据的对比。 条形图。条形图与柱形图的用法相似，但数据位于y 轴，值位于x 轴，位置与柱形图相反。,折线图。折线图多用于显示等时间间隔数据的变化趋势，它强调的是数据的时间性和变动率。 饼图。饼图用于显示一个数据系列中各项的大小与各项总和的比例。 面积图。面积图用于显示每个数值的变化量，强调数据随时间变化的幅度，还能直观地体现整体和部分的关系。,（二）使用图表的注意事项 制作的图表除了要具备必要的图表元素外，还需让人一目了然，在制作图表前应该注意以下6点。 在制作图表前如需先制作表格，应根据前期收集的数据制作出相应的电子表格，并对表格进行一定的美化。 根据表格中某些数据项或所有数据项创建相应形式的图表。选择电子表格中的数据时，可根据图表的需要视情况而定。 检查创建的图表中的数据有无遗漏，及时对数据进行添加或删除，然后对图表形状样式和布局等内容进行相应的设置，完成图表的创建与修改。,不同的图表类型能够进行的操作可能不同，如二维图表和三维图表就具有不同的格式设置。 图表中的数据较多时，应该尽量将所有数据都显示出来，所以一些非重点的部分，如图表标题、坐标轴标题和数据表格等都可以省略。 办公文件讲究简单明了，对于图表的格式和布局等，最好使用Excel自带的格式，除非有特定的要求，否则没有必要设置复杂的格式影响图表的阅读。,任务实现,（一）创建图表 图表可以将数据表以图例的方式展现出来。创建图表时，首先需要创建或打开数据表，然后根据数据表创建图表。,图8-32 插入图表效果 图8-33 移动图表效果,（二）编辑图表 编辑图表包括修改图表数据、修改图表类型、设置图表样式、调整图表布局、设置图表格式、调整图表对象的显示以及分布和使用趋势线等操作。,图8-34 选择数据源 图8-35 修改图表数据后的效果,图8-36 选择图表类型 图8-37 修改图表类型后的效果,图8-38 更改图表布局 图8-39 设置图表样式,图8-40 选择坐标轴标题的显示位置 图8-41 设置图例的显示位置,（三）使用趋势线 趋势线用于标识图表数据的分布与规律，从而使用户能够直观地了解数据的变化趋势，或对数据进行预测分析。,图8-42 更改图表类型 图8-43 添加趋势线,（四）插入迷你图 迷你图不但简洁美观，而且可以清晰展现数据的变化趋势，并且占用空间也很小，因此为数据分析工作提供了极大的便利。,图8-44 创建迷你图,图8-45 设置高点和低点 图8-46 快速创建迷你图,