第4章 办公数据的处理

返回
3．自定义条件筛选
（1）在“工资表(筛选)”工作表中，选定数据区域A3:K18。 （2）选择“数据”菜单中的“筛选”命令的“自动筛选”子命 令，则每个字段名右侧出现一个下拉按钮。 （3）单击“应发工资”字段的下拉按钮 ，在弹出的下拉菜单中 选择“自定义”选项，打开“自定义自动筛选方式”对话框， 并作如 下图所示的设臵。
办 公 软件 篇
第四章 办公数据的处理 主 要 内 容
2.1 Excel数据库及其 数据计算 2.2 Excel数据库的数 据管理和分析
问题的产生和解决办法
在日常办公事务中，经常有大量的数据需要处理。 例如，职工工资表中数据的计算、汇总和分析；又 如分期付款方案中的数据计算和分析等。 Excel具有数据库管理、计算和分析功能。使用 Excel的数据库功能能够对数据清单进行查询、排序、 筛选和分类汇总等操作，从而实现对Excel工作表中 的数据进行高效加工和分析利用，并预测其发展趋 势。
1．执行分类汇总
（1）选定数据清单的数据 区域A3:K18 。 （2）选择“数据”菜单中 的“分类汇总”命令，打 开“分类汇总”对话框。 （3）设臵分类的关键字、 汇总方式和设臵汇总字段， 如右图所示。
返回
（4）单击“确定”按钮，Excel为每一个部门增加一行，以 存放分类汇总的值，并注明分类汇总的名称。在工作表左侧 插入分级显示控制栏，如图下所示。
返回
4.2
Excel数据库的数据管理和分析
提出任务
【实例 4－2】将“工资表(计算)”工作表中的数据清单复制到 “工资表(分类汇总)”工作表中，然后要求完成以下操作： 在“工资表(分类汇总)”工作表中，对数据清单进行排序。 要求以“部门”作为主要关键字，排序方式升序；以“籍贯” 为次要关键字，排序方式降序；以“应发工资”为第三关键 字，排序方式升序； 求各部门实发工资的平均值。 制作各部门实发工资的平均值的三维簇状柱形图。
2．数据清单的创建 数据清单与其他Excel工作表的创建方法相同， 只不过数据组织必须遵循数据清单的原则。其效果如 下图所示：
返回
4.1.2 输入数据清单的基本数据
1．数据清单的概念
（1）选定F4单元格，输入公式“=E4*0.2”。 （2）将鼠标指针指向F4单元格右下角的“填充柄”，待鼠标指 针呈“十”字形时，按住鼠标左键拖动到F18单元格。
返回
（4）单击“确定”按钮。数据清单中仅显示应发工资在 3000到3500之间的记录，如下图所示。
返回
4．高级筛选
（1）建立筛选条件区域 在数据清单的上方建立了高级筛选的条件区域，如下图所示。 若将条件写在同一个筛选条件行内，则条件之间是“与”的关系； 若将条件写在不同的筛选条件行中，则条件之间是“或”的关系。
数据透视表的概念
数据透视表是对数据清单进行分类汇总而建立的行列交 叉表，或说数据透视表是行列交叉的分类汇总表。它可以 转换行和列用，以不同的方式显示分类汇总的结果。数据 透视表是分析、组织复杂数据表的有力工具。
建立数据透视表并没有修改原有的数据清单，它只是对 数据清单中的原有数据进行重新组织，从而提供了新的数 据表示形式，以便用户对数据清单中的数据进行分析。
由若干个数据项（字段值）组成。
返回
提出任务
【实例4－1】制作工资表，要求作如下各项计算： 计算住房补贴。（住房补贴＝基本工资×20%） 计算应发工资。（应发工资＝基本工资＋住房补贴＋奖金） 计算所得税。 注：应纳所得税的计算公式如下： 每月应纳所得税额＝每月应纳税所得×适用税率－速算扣除数 速算扣除数＝前一级的最高所得额×(本级税率－前一级税率） ＋前级速算扣除数 每月应纳所得额 ＝月工资、薪金所得－2000元（即：应发工资－2000元） 计算实发工资。(实发工资＝应发工资－所得税－其他扣款)
返回
1．自动筛选
（1）在“工资表(筛选)”工作表中，选定数据区域A3:K18。 （2）选择“数据”菜单中的“筛选”命令的“自动筛选”子命 令，则每个字段名右侧出现一个下拉按钮。 （3）单击“籍贯”字段的下拉按钮 ，在下拉菜单中选择“厦 门”。 （4）单击“部门”字段的下拉按钮，在下拉菜单中选择“销 售”。 自动筛选的结果，如图下所示。
在“工资表(筛选)”工作表中，筛选出籍贯为厦门且在销 售部门工作的职工记录。
取消全部字段的筛选条件并退出自动筛选状态。 筛选出应发工资在3000至3500元之间（包括3000和3500 元）的职工记录。 筛选出籍贯是福州的且应发工资小于3500元或者在财务 部工作且奖金大于150元的职工记录。
返回
2．察看分类汇总的结果
数据清单分类汇总后，可以隐藏或显示不同级别的数据。 （1）隐藏明细数据符号 单击该符号，将隐藏符号所在组的所有记录。 （2）显示明细数据符号 单击该符号，将显示符号所在组的所有记录 。 （3）显示级别符号 1）单击符号 ，只显示分类汇总的总计结果。 2）单击符号 ,显示所有分类汇总的结果及总计结果,如图下所示。
返回
4.2.3 创建图表
1．选定图表的数据源
（1）在图4－7所示的分类汇总数据清单中，单击选定“财 务部 平均值”单元格C6。
（2）按住【Ctrl】键，分别单击“财务部 平均值”单元格 C6、“管理部 平均值”单元格C10、“开发部 平均值”单 元格C14、“生产部 平均值”单元格C18和“销售部 平均 值”单元格C23以及单元格K6、K10、K14、K18和K23， 共选定8个单元格作为数据源。
返回
2．自动筛选
（1）取消单个字段的筛选条件 要选择该字段筛选下拉菜单中的“全部”选项。 （2）取消全部字段的筛选条件
选择“数据”菜单中的“筛选”命令的“全部显示”子命令， 则一次性取消全部的筛选条件，显示全部记录。
（3）退出自动筛选状态
再次选择“数据”菜单中的“筛选”命令的“自动筛选”子命 令，使该命令前的“√”号消失。
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中显示“应发 工资”的合计数。 计算填充记录数据后 的数据清单如右图所示
返回
2．使用图表向导
（1）选择“插入”菜单中的“图表”命令，打开“图表向 导－4步骤之1－图表类型”对话框，并选择图表类型。 （2）单击“下一步”按钮，打开“图表向导－4步骤之2－ 图表源数据”对话框，并选用系列产生在“列”。 （3）单击“下一步”按钮，打开“图表向导－ 4步骤之3 － 图表选项”对话框，输入图表标题 ，分类轴和数值轴。
2．计算“应发工资”字段的数据
（1）选定单元格区域E4:H4。 （2）单击工具栏中的“自动求和”按钮 ,在编辑栏中显示单元格 H4的公式为=SUM(E4:G4)；在单元格H4中显示公式计算的结果。 （3）将鼠标指针指向H4单元格右下角的“填充柄”，待鼠标指 针呈“十”字形时，按住鼠标左键拖动到H18单元格。
1．数据清单的概念 数据清单是具有特定格式的数据表，其数据组织 必须遵循以下的原则： （1）在一张工作表中，不能建立多个数据清单。 （2）数据清单内不能出现空行。 （3）字段名应放在数据清单的第一行。不能用空行 将字段名与第一个记录行分开。 （4）数据清单中，同一列的数据应具有相同的数据 类型。
返回
返回
操作流程
制作工作表“工资表(计算)”的副本→将副本工作表 重命名为“工资表(分类汇总)”→对数据清单进行排序 →对数据清单进行分类汇总。
返回
4.2.1 数据记录的排序
1．单关键字排序
（1）数据清单没有与其他数据相连 1）在数据清单中，单击选定作为排序关键字段列中的任 意一个单元格。 2）单击“常用”工具栏中的“升序”按钮 或“降序”按 钮。 （2）数据清单与其他数据相连 1）选定数据清单的数据区域。 2）选择“数据”菜单中的“排序”命令，打开“排序” 对话框，进行排序。
返回
提出任务
分别对各部门的男 女职员的基本工资 平均汇总，求出每 个部门的基本工资 平均值，男职员和 女职员的基本工资 平均值，以及整个 公司的基本工资平 均值，并保留一位 小数。
【实例4－4】“新亚公司”工作簿中的“职 工简况表”工作表如下图所示，要求建立 名为“职工简况数据透视表”的数据透视 表。
返回
分析任务
本例的第1个问题可以用Excel自动筛选的功能来实现；第2 个问题是第1个问题逆操作；第3个问题是自定义条件筛选；第 4个问题是高级筛选。
操作流程
制作工作表“工资表(计算)”的副本→将副本工作表重命名为 “工资表(筛选)”→设臵并执行自动筛选→取消全部字段的筛选 条→退出自动筛选状态→执行自定义条件筛选→建立筛选条件 区域→执行高级筛选。
返回
2．多关键字排序
（1）选定数据清单的数据区域A3:K18。 （2）选择“数据”菜单中的“排序”命令,打开“排序”对话 框。 （3）设臵“主要关键字” 、“次要关键字”和“第三关键字” 及其排序方向，如图右下所示。 （4）单击“确定”按钮。排序的结果如左下图所示。返回4.2.2来自数据记录的分类汇总返回
3．计算“所得税”字段的数据
（1）选定I4单元格,根据应纳所得税的计算公式,输入如下公式： =IF(H4-2000<0,0,IF(H4-2000<500,0.05*(H4-2000),IF(H42000<2000,0.1*(H4-2000)-25,0.15*(H4-2000)-125))) （2）将鼠标指针指向I4单元格右下角的“填充柄”，待鼠标指针 呈“十”字形时，按住鼠标左键拖动到I18单元格。