Office办公应用立体化教程课件

项目六 Excel数据计算与管理新形态立体化精品系列教材Office办公应用立体化教程（Office2016）（微课版）任务一计算“员工销售业绩奖金”工作簿任务二登记并管理“生产记录表”工作簿内容导航（一）使用公式计算数据（二）引用单元格 （三）使用函数计算数据 项目实训课后练习技巧提升任务三管理“日常费用统计表”工作簿一、任务描述每月月末，公司销售部门都需要总结出销售人员当月的销售业绩，而米拉的工作则是根据这些销售业绩，计算出对应的奖金，制作出“员工销售业绩奖金”表格。在公司普及自动化办公的当下，老洪要求米拉使用Excel表格代替计算器进行计算。经过不懈努力，米拉通过Excel自带的公式和函数功能实现了各类数据的计算，成功掌握了更高级的计算方法。本例完成后的参考效果如下图所示。二、任务实施Excel中的公式是对工作表中的数据进行计算和操作的等式，它以等号“=”开始，其后是公式的表达式，如“=A1+A2*A3/SUM(A3:A10)”。公式的表达式包含了运算符（如“+”“/”“&”“，”等）、数值或任意字符串，以及函数和单元格引用等元素。（一）使用公式计算数据（一）使用公式计算数据通常通过在单元格中输入实现计算功能。例如，在“销售数据统计”表中选择F3单元格，输入等号“=”，然后选择D3单元格，引用其中的数据，并输入运算符“*”，将其作为公式表达式中的部分元素，继续选择E3单元格，引用其中的数据。1.输入公式（一）使用公式计算数据复制与填充公式是快速计算同类数据的最佳方法，将鼠标光标移到该单元格右下角的填充柄上，当鼠标光标变成+形状时，按住鼠标左键不放拖动至目标单元格，即可快速填充公式。2.复制与填充公式（二）引用单元格绝对引用混合引用引用不同工作簿中的单元格引用同一工作簿中其他工作表的单元格引用单元格的主要方法指相对于公式单元格位于某一位置处的单元格引用。指把公式复制或移动到新位置后，公式中的单元格地址保持不变。指在一个单元格地址引用中，既有绝对引用，又有相对引用。工作簿中包含多个工作表，在其中一张工作表引用该工作簿中其他工作表中的数据。相对引用对不同工作簿中的单元格进行引用，可使用“‘工作簿存储地址[工作簿名称]工作表名称’!单元格地址”的方法来进行引用。（三）使用函数计算数据

当在工作表中对所使用的函数和参数类型都很熟悉时，可直接输入函数，在编辑栏中单击“插入函数”按钮，打开“插入函数”对话框，选择并插入所需函数。1.输入函数（三）使用函数计算数据函数嵌套的方法是将某一函数或公式作为另一个函数的参数来计算。例如，嵌套函数“=IF(B3>50000,IF(B3=MAX($B$3:$B$17),”2000“,”“),”“)”，表示使用逻辑函数IF和最大值函数MAX进行计算。2.嵌套函数（三）使用函数计算数据除了上面介绍的求和函数SUM，查找函数HLOOKUP，逻辑函数IF，还有一些在办公中经常使用的函数，如最大值函数MAX最小值函数MIN，平均值函数AVERAGE，统计函数COUNTIF函数。3.其他常用办公函数介绍任务一计算“员工销售业绩奖金”工作簿任务二登记并管理“生产记录表”工作簿内容导航（一）使用记录单输入数据（二）数据筛选项目实训课后练习技巧提升任务三管理“日常费用统计表”工作簿一、任务描述公司在产品生产数据的管理方面效率一直不太高，为了有效地整理并查找所需的产品生产数据，公司希望米拉找到方法快速登记并管理“生产记录表”，要求能快速并准确地输入数据，并根据公司的指定条件筛选数据。米拉正一筹莫展之际，老洪让她尝试使用Excel的记录单和数据筛选功能。米拉在Excel中不断地进行尝试，最终找到优化方法，高效率地完成了“生产记录表”数据的登记和管理，效果如下图所示。二、任务实施如果数据表比较庞大，数据记录条数较多，使用记录单输入数据就特别方便。（一）使用记录单输入数据（一）使用记录单输入数据记录单是用来详细记录所需资料的单据，它可以帮助用户在一个小窗口中完成输入数据的工作。使用之前可以打开“Excel选项”对话框，将“记录单”选项添加至快速访问工具栏中，以便于调用。（二）数据筛选自动筛选数据是根据用户设定的筛选条件，自动将表格中符合条件的数据显示出来，而将表格中的其他数据隐藏。单击“数据”选项卡，在“排序和筛选”组中单击“筛选”按钮，在工作表中每个表头数据对应的单元格右侧将出现下拉按钮，单击下拉按钮，选择或设置筛选条件。1.自动筛选（二）数据筛选自定义筛选即在自动筛选后的需自定义的字段名右侧单击按钮，在打开的下拉列表中选择相应的选项，确定筛选条件后，在打开的“自定义自动筛选方式”对话框中进行相应的设置。在要筛选字段名右侧单击下拉按钮，在弹出的列表中选择如“日期筛选”选项，在子列表中选择“自定义筛选”选项，在打开的“自定义自动筛选方式”对话框中设置参数。2.自定义筛选（二）数据筛选高级筛选功能可以筛选出同时满足两个或两个以上约束条件的记录。单击“数据”选项卡，在“排序和筛选”组中单击“高级”按钮，在打开的“高级筛选”对话框的“列表区域”文本框中将自动选择参与筛选的单元格区域。3.高级筛选任务一计算“员工销售业绩奖金”工作簿任务二登记并管理“生产记录表”工作簿内容导航（一）数据排序 （二）分类汇总 （三）定位选择与分列显示数据项目实训课后练习技巧提升任务三管理“日常费用统计表”工作簿一、任务描述随着公司的日益发展，日常办公开支项目和费用不断增多，公司决定从下半年开始制作费用统计表，记录日常办公费用的支出项目和经费。为了查看费用支出的总和等情况，老洪让米拉对“日常费用统计表”进行管理，要求她先对相应的数据进行排序，再根据排序结果分类汇总，然后定位选择数据，并分列显示数据。本例完成后的参考效果如下图所示。二、任务实施数据排序常用于统计工作中，在Excel中，数据排序是指根据存储在表格中数据的类型，将其按一定的方式重新排列。数据排序有助于用户快速直观地查看数据，更好地理解数据，更方便地组织并查找所需数据。数据排序的常用方法有自动排序和按关键字排序。（一）数据排序（一）数据排序自动排序是数据排序管理中最基本的一种排序方式，选择该方式系统将自动对数据进行识别并进行排序。方法是单击“数据”选项卡，在“排序和筛选”组中单击“升序”或“降序”按钮。1.自动排序（一）数据排序按关键字的方式排序，可根据指定的关键字对某个字段（列单元格）或多个字段对数据进行排序，通常可将该方式分为按单个关键字排序与按多个关键字排序。单击“数据”选项卡，在“排序和筛选”组中单击“排序”按钮，在打开的“排序”对话框中设置主要关键字和次要关键字。2.按关键字排序（二）分类汇总

Excel的数据分类汇总功能是用于将性质相同的数据汇总到一块，以便使表格的结构更清晰，使用户能更好地掌握表格中重要的信息。单击“数据”选项卡，在“分级显示”组中单击“分类汇总”按钮分类汇总”按钮，打开“分类汇总”对话框，设置汇总方式等参数。（三）定位选择与分列显示数据定位是一种选择单元格的方式，主要用来选择位置相对无规则但条件有规则的单元格或单元格区域。在“开始”选项卡的“编辑”组中单击“查找和选择”按钮，在弹出的列表中选择“定位条件”选项，在打开的“定位条件”对话框中选择条件。1.定位选择数据（三）定位选择与分列显示数据在一些特殊情况下需要使用Excel的分列功能快速将一列中的数据分列显示，如将日期以月与日分列显示、将姓名以姓与名分列显示等。单击“数据”选项卡，在“数据工具”组中单击“分列”按钮，在打开的“文本分列向导-第1步，共3步”对话框中根据提示进行操作。2.分列显示数据任务一计算“员工销售业绩奖金”工作簿任务二登记并管理“生产记录表”工作簿内容导航一、制作“员工工资表”工作簿二、管理“楼盘销售信息表”工作簿项目实训课后练习技巧提升任务三管理“日常费用统计表”工作簿一、制作“员工工资表”工作簿本实训的目标是制作员工工资表，需要在表格中使用公式计算实发工资和税后工资、使用自动求和功能计算应领工资和应扣工资，以及使用IF嵌套函数计算个人所得税。本实训的最终效果如下图所示。1.实训目标一、制作“员工工资表”工作簿员工工资通常分为固定工资、浮动工和福利3部分，其中固定工资是不变的，而浮动工资和福利会随着时间或员工表现而改变。不同的公司制定的员工工资管理制度不同，员工工资项目也不相同，因此应结合实际情况计算员工工资。2.专业背景一、制作“员工工资表”工作簿完成本实训首先在F5:F20和J5:J20单元格区域中使用SUM函数计算应领工资和应扣工资，然后使用公式计算实发工资，最后使用IF函数计算个人所得税并输入公式计算税后工资。3.操作思路二、管理“楼盘销售信息表”工作簿对楼盘数据进行排序、筛选开盘均价大于等于10000元的记录。。本实训的最终效果如下图所示。1.实训目标二、管理“楼盘销售信息表”工作簿楼盘销售信息表具有针对性、引导性和参考价值，在制作时要包括开发公司名称、楼盘位置、开盘价格以及销售状况等信息，从中可看出哪一类房产卖得好，受到客户的欢迎，从而制定有针对性销售策略。2.专业背景二、管理“楼盘销售信息表”工作簿完成本实训首先对“开发公司”按笔画降序排列，然后筛选出开盘均价大于等于10000元的记录，最后将记录按“开发公司”分类，并汇总“开盘均价”和“已售”项的最大值，其操作思路如下图所示。3.操作思路任务一计算“员工销售业绩奖金”工作簿任务二登记并管理“生产记录表”工作簿内容导航1.制作“学生成绩汇总表”工作簿 2.管理“区域销售汇总表”工作簿项目实训课后练习技巧提升任务三管理“日常费用统计表”工作簿1. 制作“学生成绩汇总表”工作簿打开素材文件“学生成绩汇总表.xlsx”工作簿，使用公式和函数计算相关成绩，完成后的效果如下图所示。2. 管理“区域销售汇总表”工作簿下面将打开“区域销售汇总表”工作簿，使用记录单输入相应的数据内容，然后对相应数据进行排序和汇总，参考效果如下图所示。任务一计算“员工销售业绩奖金”工作簿任务二登记并管理“生产记录表”工作簿内容导航1.用NOW函数显示当前日期和时间 2.用MID函数从身份证号码中提取出生日期3.用COUNT函数统计单元格数量4.用COUNTIFS函数按多条件进行统计5.相同数据排名 项目实训课后练习技巧提升任务三管理“日常费用统计表”工作簿技巧提升

NOW函数可以返回计算机系统内部时钟的当前日期和时间。其语法结构为：NOW()，没有参数，并且如果包含公式的单元格格式设置不同，则返回的日期和时间的格式也不相同。其方法为：在工作簿中选择目标单元格，输入“=NOW()”，按【Enter】键即可显示计算机系统当前的日期和时间。1.用NOW函数显示当前日期和时间技巧提升MID函数用于返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。其语法结构为：MID(text,start_num,num_chars)，各参数含义分别是，text是包含要提取字符的文本字符串；start_num是文本中要提取的第一个字符的位置，文本中第一个字符的start_num为1，以此类推；num_chars指定希望MID从文本中返回字符的个数。2.用MID函数从身份证号码中提取出生日期技巧提升COUNT函数用于只返回包含数字单元格的个数。同时还可以计算单元格区域或数字数组中数字字段的输入项个数，空白单元格或文本单元格将不计算在内。其语法结构为：COUNT(value1,value2,...)，其中参数value1，value2，...是可以包含或引用各种类型数据的1到255个参数，但只有数字类型的数据才计算在内。3.用COUNT函