第4章　办公数据的处理.ppt

2.1Excel数据库及其数据计算2.2Excel数据库的数据管理和分析,第四章办公数据的处理,办公软件篇,主要内容,在日常办公事务中，经常有大量的数据需要处理。例如，职工工资表中数据的计算、汇总和分析；又如分期付款方案中的数据计算和分析等。Excel具有数据库管理、计算和分析功能。使用Excel的数据库功能能够对数据清单进行查询、排序、筛选和分类汇总等操作，从而实现对Excel工作表中的数据进行高效加工和分析利用，并预测其发展趋势。,问题的产生和解决办法,返回,4.1Excel数据库及其数据计算,数据库基本概念,在关系数据库中，一个数据库通常由若干个互相关联的二维数据表构成。一个二维数据表就构成了一个最简单的数据库。二维数据表由若干个行和列组成。数据表中的每一列称为一个字段，每一列的第一行为字段名，以下是该字段的值，同一列的数据的类型必须相同；数据表中除了列标题行之外，其他行称为记录，一个记录由若干个数据项（字段值）组成。,返回,提出任务,【实例41】制作工资表，要求作如下各项计算：计算住房补贴。（住房补贴基本工资20%）计算应发工资。（应发工资基本工资住房补贴奖金）计算所得税。注：应纳所得税的计算公式如下：每月应纳所得税额每月应纳税所得适用税率速算扣除数速算扣除数前一级的最高所得额(本级税率前一级税率）前级速算扣除数每月应纳所得额月工资、薪金所得2000元（即：应发工资2000元）计算实发工资。(实发工资应发工资所得税其他扣款),返回,个人所得税速算扣除数表,返回,展示效果,返回,分析任务,“工资表”是二维行列表，即数据表，可将其作为Excel数据清单来处理。,创建Excel工作簿在Excel工作表中创建数据清单输入公式计算“住房补贴”、“应发工资”、“所得税”和“实发工资”字段的数据。,操作流程,返回,4.1.1输入数据清单的基本数据,1数据清单的概念数据清单是具有特定格式的数据表，其数据组织必须遵循以下的原则：（1）在一张工作表中，不能建立多个数据清单。（2）数据清单内不能出现空行。（3）字段名应放在数据清单的第一行。不能用空行将字段名与第一个记录行分开。（4）数据清单中，同一列的数据应具有相同的数据类型。,返回,2数据清单的创建数据清单与其他Excel工作表的创建方法相同，只不过数据组织必须遵循数据清单的原则。其效果如下图所示：,返回,4.1.2输入数据清单的基本数据,1数据清单的概念（1）选定F4单元格，输入公式“=E4*0.2”。（2）将鼠标指针指向F4单元格右下角的“填充柄”，待鼠标指针呈“十”字形时，按住鼠标左键拖动到F18单元格。2计算“应发工资”字段的数据（1）选定单元格区域E4:H4。（2）单击工具栏中的“自动求和”按钮,在编辑栏中显示单元格H4的公式为=SUM(E4:G4)；在单元格H4中显示公式计算的结果。（3）将鼠标指针指向H4单元格右下角的“填充柄”，待鼠标指针呈“十”字形时，按住鼠标左键拖动到H18单元格。,返回,3计算“所得税”字段的数据（1）选定I4单元格,根据应纳所得税的计算公式,输入如下公式：=IF(H4-20000,0,IF(H4-2000500,0.05*(H4-2000),IF(H4-20002000,0.1*(H4-2000)-25,0.15*(H4-2000)-125)（2）将鼠标指针指向I4单元格右下角的“填充柄”，待鼠标指针呈“十”字形时，按住鼠标左键拖动到I18单元格。4计算“实发工资”字段的数据（1）选定K4单元格，输入公式“=H4-I4-J4”。（2）将鼠标指针指向K4单元格右下角的“填充柄”，待鼠标指针呈“十”字形时，按住鼠标左键拖动到K18单元格。,返回,5计算“实发工资”的合计数据（1）选定要合并的单元格区域A19:B19。（2）单击“格式”工具栏中的“合并及居中”按钮。（3）输入：合计，并其格式化成“宋体”、“12”号、“加粗”。（4）选定单元格区域K4:K19。,（5）单击工具栏中的“自动求和”按钮，在编辑栏中显示单元格K19的公式为=SUM(K4:K18)；在单元格K19中显示“应发工资”的合计数。计算填充记录数据后的数据清单如右图所示,返回,4.2Excel数据库的数据管理和分析,【实例42】将“工资表(计算)”工作表中的数据清单复制到“工资表(分类汇总)”工作表中，然后要求完成以下操作：在“工资表(分类汇总)”工作表中，对数据清单进行排序。要求以“部门”作为主要关键字，排序方式升序；以“籍贯”为次要关键字，排序方式降序；以“应发工资”为第三关键字，排序方式升序；求各部门实发工资的平均值。制作各部门实发工资的平均值的三维簇状柱形图。,提出任务,返回,分析任务,数据清单中的数据记录是按照其创建时输入的先后顺序排列的。这种数据排列的顺序通常不能满足数据处理的需求。Excel排序功能则将数据清单中的记录按照指定条件重新排列，以便对工作表数据进行比较和分析。求各部门实发工资的平均值就是按“部门”对“实发工资”字段进行分类汇总，汇总的方式是求平均。分类汇总操作的要点是：要先对数据清单进行排序，然后才能进行分类汇总操作；排序所根据的关键字段与分类汇总所用的关键字段要一致。,返回,制作工作表“工资表(计算)”的副本将副本工作表重命名为“工资表(分类汇总)”对数据清单进行排序对数据清单进行分类汇总。,操作流程,返回,4.2.1数据记录的排序,1单关键字排序（1）数据清单没有与其他数据相连1）在数据清单中，单击选定作为排序关键字段列中的任意一个单元格。2）单击“常用”工具栏中的“升序”按钮或“降序”按钮。（2）数据清单与其他数据相连1）选定数据清单的数据区域。2）选择“数据”菜单中的“排序”命令，打开“排序”对话框，进行排序。,返回,2多关键字排序（1）选定数据清单的数据区域A3:K18。（2）选择“数据”菜单中的“排序”命令,打开“排序”对话框。（3）设置“主要关键字”、“次要关键字”和“第三关键字”及其排序方向，如图右下所示。（4）单击“确定”按钮。排序的结果如左下图所示。,返回,4.2.2数据记录的分类汇总,1执行分类汇总,（1）选定数据清单的数据区域A3:K18。（2）选择“数据”菜单中的“分类汇总”命令，打开“分类汇总”对话框。（3）设置分类的关键字、汇总方式和设置汇总字段，如右图所示。,返回,（4）单击“确定”按钮，Excel为每一个部门增加一行，以存放分类汇总的值，并注明分类汇总的名称。在工作表左侧插入分级显示控制栏，如图下所示。,返回,2察看分类汇总的结果数据清单分类汇总后，可以隐藏或显示不同级别的数据。（1）隐藏明细数据符号单击该符号，将隐藏符号所在组的所有记录。（2）显示明细数据符号单击该符号，将显示符号所在组的所有记录。（3）显示级别符号1）单击符号，只显示分类汇总的总计结果。2）单击符号,显示所有分类汇总的结果及总计结果,如图下所示。,返回,4.2.3创建图表,1选定图表的数据源（1）在图47所示的分类汇总数据清单中，单击选定“财务部平均值”单元格C6。（2）按住【Ctrl】键，分别单击“财务部平均值”单元格C6、“管理部平均值”单元格C10、“开发部平均值”单元格C14、“生产部平均值”单元格C18和“销售部平均值”单元格C23以及单元格K6、K10、K14、K18和K23，共选定8个单元格作为数据源。,返回,2使用图表向导（1）选择“插入”菜单中的“图表”命令，打开“图表向导4步骤之1图表类型”对话框，并选择图表类型。（2）单击“下一步”按钮，打开“图表向导4步骤之2图表源数据”对话框，并选用系列产生在“列”。（3）单击“下一步”按钮，打开“图表向导4步骤之3图表选项”对话框，输入图表标题，分类轴和数值轴。（4）单击“下一步”按钮，打开“图表向导-4步骤之4-图表位置”对话框，选用“作为其中的对象插入”选项。（5）单击“完成”按钮，即在工作表中插入图表，如下页图所示。,返回,返回,4.2.4数据记录的筛选,提出任务,【实例43】将“工资表(计算)”工作表中的数据清单复制到“工资表(筛选)”工作表中，然后要求完成以下操作：在“工资表(筛选)”工作表中，筛选出籍贯为厦门且在销售部门工作的职工记录。取消全部字段的筛选条件并退出自动筛选状态。筛选出应发工资在3000至3500元之间（包括3000和3500元）的职工记录。筛选出籍贯是福州的且应发工资小于3500元或者在财务部工作且奖金大于150元的职工记录。,返回,分析任务,本例的第1个问题可以用Excel自动筛选的功能来实现；第2个问题是第1个问题逆操作；第3个问题是自定义条件筛选；第4个问题是高级筛选。,制作工作表“工资表(计算)”的副本将副本工作表重命名为“工资表(筛选)”设置并执行自动筛选取消全部字段的筛选条退出自动筛选状态执行自定义条件筛选建立筛选条件区域执行高级筛选。,操作流程,返回,1自动筛选（1）在“工资表(筛选)”工作表中，选定数据区域A3:K18。（2）选择“数据”菜单中的“筛选”命令的“自动筛选”子命令，则每个字段名右侧出现一个下拉按钮。（3）单击“籍贯”字段的下拉按钮，在下拉菜单中选择“厦门”。（4）单击“部门”字段的下拉按钮，在下拉菜单中选择“销售”。自动筛选的结果，如图下所示。,返回,2自动筛选（1）取消单个字段的筛选条件要选择该字段筛选下拉菜单中的“全部”选项。（2）取消全部字段的筛选条件选择“数据”菜单中的“筛选”命令的“全部显示”子命令，则一次性取消全部的筛选条件，显示全部记录。（3）退出自动筛选状态再次选择“数据”菜单中的“筛选”命令的“自动筛选”子命令，使该命令前的“”号消失。,返回,3自定义条件筛选（1）在“工资表(筛选)”工作表中，选定数据区域A3:K18。（2）选择“数据”菜单中的“筛选”命令的“自动筛选”子命令，则每个字段名右侧出现一个下拉按钮。（3）单击“应发工资”字段的下拉按钮，在弹出的下拉菜单中选择“自定义”选项，打开“自定义自动筛选方式”对话框，并作如下图所示的设置。,返回,（4）单击“确定”按钮。数据清单中仅显示应发工资在3000到3500之间的记录，如下图所示。,返回,4高级筛选（1）建立筛选条件区域在数据清单的上方建立了高级筛选的条件区域，如下图所示。若将条件写在同一个筛选条件行内，则条件之间是“与”的关系；若将条件写在不同的筛选条件行中，则条件之间是“或”的关系。,返回,（2）使用高级筛选命令1）选定数据清单的数据区域。2）选择“数据”菜单中的“筛选”命令的“高级筛选”子命令，打开“高级筛选”对话框。3）设置筛选的方式、“条件区域”和“复制到”区域，如下图所示。,返回,5）单击“高级筛选”对话框中的“确定”按扭，筛选结果如下图所示。,返回,4.2.5数据透视表,数据透视表是对数据清单进行分类汇总而建立的行列交叉表，或说数据透视表是行列交叉的分类汇总表。它可以转换行和列用，以不同的方式显示分类汇总的结果。数据透视表是分析、组织复杂数据表的有力工具。建立数据透视表并没有修改原有的数据清单，它只是对数据清单中的原有数据进行重新组织，从而提供了新的数据表示形式，以便用户对数据清单中的数据进行分析。,数据透视表的概念,返回,提出任务,【实例44】“新亚公司”工作簿中的“职工简况表”工作表如下图所示，要求建立名为“职工简况数据透视表”的数据透视表。,分别对各部门的男女职员的基本工资平均汇总，求出每个部门的基本工资平均值，男职员和女职员的基本工资平均值，以及整个公司的基本工资平均值，并保留一位小数。,返回,分析任务,数据透视表是通过执行“数据”菜单中的“数据透视表和数据透视图”命令而建立的。执行“数据透视表和数据透视图”命令后，将在“数据透视表向导”的指引下完成步骤操作，从而生成数据透视表。,在数据清单中选定数据区域打开数据透视表向导打开数据透视表的布局对话框设置“分类”和“汇总”字段生成数据透视表重命名生成的数据透视表的工作表标签。,操作流程,返回,1建立数据透视表（1）在“职工简况表”工作表中，选定数据清单中的数据区域A2:H17。（2）选择“数据”菜单中的“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导”，按向导的提示建立如下图所示的数据透视表。,返回,2改变数据透视表中数据的位置改变数据透视表中数据的位置，可以从不同角度观察和分析数据。拖动数据透视表中行或列的按钮可改变数据透视表的数据位置。例如：,返回,4.2.6财务函数的使用,提出任务,只要在“基本信息”区输入房屋面积、房屋售价、首付金额和贷款年限，“贷款信息”区就自动计算出总金额、贷款金额、贷款月份、每月还款额、本息总额和总利息。,返回,【实例45】下图是在Excel工作表中制作的一个“房屋贷款计算器”，其主体由“银行利率”、“基本信息”、“贷款信息”和“使用说明”4个区组成。,分析任务,“银行利率”区和“基本信息”区中的房屋面积、房屋售价、首付金额和贷款年限的数据应设置保护，以保证数据的安全；“贷款信息”区的数据通过计算求得，Excel函数分为11类，共有上百个函数，本例宜选用“返回年金的定期付款额函数PMT”来计算每月还款额；“使用说明”区的信息是为了增加可读性。,返回,创建Excel工作簿和工作表输入标题建立“银行利率”区建立“基本信息”区建立“贷款信息”区美化单元格区域的外观输入基本数据和计算公式建立“使用说明”区。,操作流程,返回,1建立“房屋贷款计算器”工作簿和工作表2制作标题3建立“银行利率”区域4建立“基本信息”区域5建立“贷款信息”区域6建立“使用说明”区域7取消网格线,返回,操作步骤,1创建数据清单创建工作簿“学生成绩管理.xls”，在“302班”工作表中，建立如图445所示的数据清单。,返回,综合练习,2查找刻录数据使用“记录单”对话框，查找座号小于11，且语文成绩大于80的学生的信息。3单关键字排序将“学生成绩管理.xls”工作簿中的“302班”工作表中的记录，按英语成绩降序排列。4多关键字排序将图“学生成绩管理.xls”工作簿中的“302班”工作表中的记录，按总分降序排列，当总分相同时，按英语成绩降序排列。5自动筛选在“学生成绩管理.xls”工作簿中的“302班”工作表中，筛选出总分最