09 第5章 办公自动化软件之Excel

办公自动化第第第第5555章章章章办公自动化软件之办公自动化软件之办公自动化软件之办公自动化软件之办公自动化软件之办公自动化软件之办公自动化软件之办公自动化软件之ExcelExcelExcelExcelExcelExcelExcelExcel2办公自动化【本章内容介绍本章内容介绍本章内容介绍本章内容介绍本章内容介绍本章内容介绍本章内容介绍本章内容介绍】�在办公业务实践中，经常需要对大量的业务数据进行分析和处理。如职工工资表中工资的计算、汇总、分析，公司产品销售表中销售额的统计、汇总、合并计算以及人事考勤表中职工请假、旷工、出勤等情况的分析统计等。�数据的排序、筛选、分类汇总�数据透视�图表制作�公式与函数运用3办公自动化【本章主要知识点本章主要知识点本章主要知识点本章主要知识点本章主要知识点本章主要知识点本章主要知识点本章主要知识点】�Excel表格中内容填充的常用技巧�Excel数据库表格的特殊格式设置�Excel表格中数据有效性的设置�Excel数据库表格中的排序与筛选�Excel数据库表格中的分类汇总操作�Excel数据库表格中的数据透视分析�Excel中的数据图表制作�Excel表格中公式和函数的使用�Excel中的关联表格操作4办公自动化�在办公业务中，除了文字和表格之外，还需要经常进行有关数据处理，有时候还需要将处理的数据结果用一种图表来进行表现。5.1EXCEL5.1EXCEL5.1EXCEL5.1EXCEL5.1EXCEL5.1EXCEL5.1EXCEL5.1EXCEL数据分析与处理概述数据分析与处理概述5.1.15.1.15.1.15.1.15.1.15.1.15.1.15.1.1ExcelExcelExcelExcelExcelExcelExcelExcel中的数据库表格简介中的数据库表格简介�数据库就是与特定主题相联系的信息集合。�在Excel中，数据库实际上就是工作表中的一个区域，是一个二维表。�单元格、工作表、工作簿�Excel中的行列数�单元格引用的三种方式5办公自动化5.1.25.1.25.1.25.1.25.1.25.1.25.1.25.1.2ExcelExcel中的数据分析与处理中的数据分析与处理�Excel中有很多可以进行的数据分析和数据处理功能，包括数据排序、数据筛选、分类汇总、数据透视、合并计算、关联表格处理等。�Excel中的数据类型：文本、数值、公式�数值型数据与日期、时间数据�数据的格式化�数值型数据转换为文本�函数示例：sum、now�快捷键：ctrl+分号，ctrl+shift+分号6办公自动化5.1.35.1.35.1.35.1.35.1.35.1.35.1.35.1.3ExcelExcel中的数据图表制作中的数据图表制作�利用Excel，可以轻松、快捷地制作各种数据图表。�这些数据图表可将枯燥的数字形象化，并且通过数据图表可以了解到数据之间的相互关系和变化趋势。�数据图表的类型很多，在办公实践中，使用较多的主要有柱形图、条形图、折线图、饼图、散点图等。7办公自动化�建立“报名汇总表”数据库框架�“编号”列的自动序列填充�仅后几位值不同时数据输入的快捷方法�定义单元格数据的有效性�使用数据清单添加记录�打印设置：边框、分页、顶端标题行、页眉页脚、�文档加密5.25.25.25.25.25.25.25.2““““““““报名信息汇总表报名信息汇总表””””””””数据库的建立数据库的建立8办公自动化制作效果制作效果9办公自动化�本节介绍利用Excel中的有关功能，对“报名信息汇总表”进行数据分析的操作方法，主要包括数据的排序、筛选、分类汇总与数据透视等功能。5.3.15.3.15.3.15.3.15.3.15.3.15.3.15.3.1对对““““““““报名信息汇总表报名信息汇总表””””””””内容进行排序内容进行排序�排序的依据�按单个字段排序�按多级字段排序�特殊排序方式5.35.35.35.35.35.35.35.3对对““““““““报名汇总表报名汇总表””””””””进行数据分析进行数据分析10办公自动化5.3.25.3.25.3.25.3.25.3.25.3.25.3.25.3.2对对““报名信息汇总表报名信息汇总表””内容进行筛选内容进行筛选�自动筛选�高级筛选�高级筛选时的注意事项11办公自动化5.3.25.3.25.3.25.3.25.3.25.3.25.3.25.3.2对对““““““““报名信息汇总表报名信息汇总表””””””””进行数据分类汇总进行数据分类汇总�使用分类汇总，能够在数据库适当位置加上统计结果，使数据库变得清晰易懂。�对于本例，根据需要，可以对“性别”数据进行分类汇总，以便了解不同性别报名情况；也可以按照“学历”进行分类汇总，以便了解不同学历的报名人数；可以对“报考职位”进行汇总，以便了解各个不同职位的报名情况。�在执行分类汇总命令之前，首先应该对数据库按照待汇总字段进行排序。12办公自动化5.3.25.3.25.3.25.3.25.3.25.3.25.3.25.3.2对对““““““““报名信息汇总表报名信息汇总表””””””””进行数据透视分析进行数据透视分析�数据透视表（图）的概念�数据透视表的创建�创建数据透视图�数据透视表的更新�删除数据透视表13办公自动化�Excel数据图表能将工作表中的数据变为非常直观的图形格式，并且从图表上很容易看出结果，以及数据之间的关系和数据的发展趋势。5.45.45.45.45.45.45.45.4利用数据图表表现数据之间关系利用数据图表表现数据之间关系5.4.15.4.15.4.15.4.15.4.15.4.15.4.15.4.1利用图表向导创建条形图利用图表向导创建条形图�数据库就是与特定主题相联系的信息集合。�在Excel中，数据库实际上就是工作表中的一个区域，是一个二维表。14办公自动化5.4.25.4.25.4.25.4.25.4.25.4.25.4.25.4.2数据图表的格式设置与内容更新数据图表的格式设置与内容更新�图表的组成�图表的格式设置�数据图表的内容更新15办公自动化5.4.35.4.35.4.35.4.35.4.35.4.35.4.35.4.3利用快捷键建立单独的图表并进行格式设置利用快捷键建立单独的图表并进行格式设置�在上面的向导方法制作图表外的最后一个步骤中选择“作为新工作表插入”，即可制作一个单独的图表工作表。除此之外，Excel中还可以利用F11或Alt+F1功能键快速制作单独的柱状图表，然后再进行三维效果以及其他项目的格式设置和修饰。16办公自动化5.4.45.4.45.4.45.4.45.4.45.4.45.4.45.4.4选取不连续区域制作饼图选取不连续区域制作饼图�对不连续区域的数据，也可以制作图表，这在饼状图制作中非常普遍。17办公自动化�上面的报名情况经过审核、分析和汇总之后，下一个环节就是要对合格考生进行统一设置准考生编号，并举行统一考试，包括笔试和面试。在本节重点介绍考试成绩的处理方法，主要用到了Excel中的数据库表格设置、内容录入技巧、公式和函数使用以及关联表格操作等。5.55.55.55.55.55.55.55.5笔试成绩的数据处理笔试成绩的数据处理18办公自动化5.5.15.5.15.5.15.5.15.5.15.5.15.5.15.5.1笔试成绩处理表的框架建立笔试成绩处理表的框架建立�招录过程的第一步是笔试，为此先建立笔试成绩数据库表格框架。�建立好的数据库格式框架如下图所示，还需要进行数据输入和格式设置工作。19办公自动化5.5.25.5.25.5.25.5.25.5.25.5.25.5.25.5.2成绩处理表中原始数据的输入成绩处理表中原始数据的输入�表格框架建立后，下一步就是要输入原始数据，输入时候需要注意以下几点：�“编号”字段可利用自动序列输入，为了使A4单元格中显示出“001”效果，需要在输入时现以单引号（‘）开头，否则输入的“001”将只是显示为“1”。�“性别”字段可以设置有效性，或者利用列表选择输入。�三门考试课程对应的三个字段，需要先设置数据有效性（整数，范围0～100），然后再进行数据的输入，这样才能较好地避免数据输入时的错误。�批注20办公自动化5.5.35.5.35.5.35.5.35.5.35.5.35.5.35.5.3数据库表格的格式设置数据库表格的格式设置�数据库标题格式设置�字段标题格式设置�条件格式化设置�表格边框线设置�间隔底纹效果的设置21办公自动化5.5.45.5.45.5.45.5.45.5.45.5.45.5.45.5.4用公式计算笔试成绩用公式计算笔试成绩�笔试成绩的最后结果需要使用公式来进行运算。�公式的有关知识�公式是对工作表中的数据进行计算和分析的一种等式，它可以对工作表数值进行算术运算、关系运算和逻辑运算，同样也可以对字符数据以及其他数据进行运算。公式可以引用同一工作表中的其他单元格、同一工作簿不同工作表中的单元格，或者其他工作簿的工作表中的单元格。22办公自动化5.5.45.5.45.5.45.5.45.5.45.5.45.5.45.5.4用公式计算笔试成绩用公式计算笔试成绩�公式输入应该遵循的基本规则�公式输入时必须以“=”开头，公式中间不能包括空格。�公式中的单元格引用，可以在编辑区直接输入，也可以通过单击该单元格或拖动单元格区域从而实现自动填入。�用算术运算符号：+（加）、—（减）、*（乘）、/（除）、^（乘方），字符运算符号&(连接字符串)以及关系运算符号（大于）、（小于）、=（等于）等来说明公式的操作类型。23办公自动化5.5.45.5.45.5.45.5.45.5.45.5.45.5.45.5.4用公式计算笔试成绩用公式计算笔试成绩�公式输入应该遵循的基本规则�在公式中可以使用圆括号，也只能使用圆括号，并且允许圆括号嵌套，但是应该注意嵌套时前后括号的匹配关系，计算顺序是先内层再外层。�单元格中的公式内容显示在编辑栏中，计算结果则显示在单元格内，如果希望直接在单元格中显示为公式，可以使用Ctrl+~键（在Tab键的上面），再次键入，则取消公式显示，重新显示结果。24办公自动化5.5.45.5.45.5.45.5.45.5.45.5.45.5.45.5.4用公式计算笔试成绩用公式计算笔试成绩�相对引用�绝对引用�混合引用�文字连接符&�上下三角形乘法表自动生成25办公自动化5.5.55.5.55.5.55.5.55.5.55.5.55.5.55.5.5利用利用RANKRANKRANKRANKRANKRANKRANKRANK函数确定笔试名次函数确定笔试名次�在Excel中，名次的确认最好使用RANK函数，该函数是专门进行排名次的函数，其结果是返回一个数值在一组数值中的排位。�RANK函数的语法格式为：RANK(number,ref,order)。其中：Number为需要找到排位的数字；Ref为包含一组数字的数组或引用；Order为一数字，指明排位的方式，为0或省略，按降序排列排位，不为零，按升序排列进行排位。�对本例中笔试成绩的名次排列，使用RANK函数并进行函数拖动复制即可。同样先选择H4单元格，在编辑栏中输入公式“=RANK(G4,$G$4:$G$33)”，然后按回车键，即可得到第一个考生的名次“4”，采用与上面同样的方法拖动复制得到每个考生的名次。26办公自动化5.5.65.5.65.5.65.5.65.5.65.5.65.5.65.5.6利用利用IFIFIFIFIFIFIFIF函数确定考生是否通过笔试考试函数确定考生是否通过笔试考试�在Excel中，利用IF函数可以非常方便地进行条件判断。本实例中，名次确认完成后，根据考试规则要求，假设只能选取前40%（即前12名考生进入面试阶段。则“是否通过”一列可以按照如下步骤进行操作。�将鼠标定位到I4单元格。�在I4单元格中输入：=IF(H4=12,可以,)。�单击编辑栏上的确定按钮，则I4单元格中显示