1、3.5 综合案例3.5.1 案例分析本节通过建立一个工资表的 Excel 电子表格，使读者进一步掌握电子表格的输入技巧，如何查看数据量大的表格，了解数据透视表的使用。设计要求本电子表格共有四个工作表，其中，一个工作表为 9 月工资表，如图 3.88 所示；一个工作表为 9 月水电读数；一个工作表为应发工资统计图，如图 3.89 所示；一个工作表为数据透视表，如图 3.90 所示。图 3.88 9 月工资表图 3.89 应发工资统计图 图 3.90 数据透视表3.5.2 设计步骤打开电子表格文件“工资表原始” ，执行以下操作。1 工作表管理（1）修改工作表的名称具体要求 将 sheet1 工作表
2、改名为“9 月工资表”操作步骤 在“ Sheet1”标签上双击， “Sheet1”处于反白状态，输入新的工作表名称： “9月工资表” ，按回车键确认。（2）删除工作表具体要求 删除 Sheet2 工作表操作步骤 在“Sheet2”工作表标签上单击鼠标右键，选择快捷菜单中的“删除”命令。（3）复制工作表具体要求 将“水电表原始”工作簿的“9 月水电读数”工作表复制到本工作簿文件中。 步骤 1 打开 “水电表原始 ”工作簿，切换至“9 月水电读数 ”工作表，选择“编辑”|“移动或复制工作表”命令，打开“移动或复制工作簿”对话框。步骤 2 在“ 移动或复制工作簿 ”对话框中，如图 3.91 所示。在
3、“工作簿”下拉列表中选择“工资表原始” ，在“下列选定工作表之前” 选择“移至最后” ，选中“建立副本”复选框。单击“确定”按钮后，在本工作簿的最后，新增了一个“9 月水电读数”工作表。图 3.91“移动或复制”工作表对话框图 3.92 录入数据的快捷菜单图 3.93 从列表中选择数据2 编辑“工资表”数据单击“工资表”标签，切换到“工资表”工作表。（1）插入行具体要求 在第 11 行前插入一行：林致远，1974-2-8，长沙，财会部，科级， 1430。操作步骤 选中 11 行（姓名张志峰）的任一单元格作为活动单元格，选择“插入”|“行”命令，则 11 行的前面插入了空行。在空行中输入数据：林
4、致远，1974-2-8，长沙，财会部，科级， 1430。技巧 输入数据在输入分公司、部门、职务等级这几列的数据时，选中单元格后，单击鼠标右键，在快捷菜单中选择“从下拉列表中选择” ，如图 3.91 所示。单元格的下面出现列表，显示出上面的行中曾输入的数据，如图 3.92 所示。用户可直接从列表中选择需要输入的数据。（2）填充等差序列具体要求 在 A3 至 A32 单元格中填充工号：1 到 30。操作步骤 工号 1-30 是等差序列，可采取下列方式填充。 在 A3 单元格输入 1。将鼠标放在 A3 单元格的填充柄上，按住 Ctrl 键，鼠标指针变为带有加号的实心十字架 。向下拖曳鼠标，填充到 A
5、32 单元格。 在 A3 单元格输入 1，在 A4 单元格输入 2。选中 A3 到 A4 单元格，鼠标指向 A4 单元格的填充柄，向下拖曳鼠标，填充到 A32 单元格。 在 A3 单元格输入 1，选择 “编辑”|“填充”|“序列”命令，打开“序列”对话框。如图 3.94 所示。在“序列产生在”选择“列”单选按钮，在“类型”选择“等差序列”单选按钮，在“步长值”文本框中输入 1，在“终止值”文本框中输入 30。 图 3.94 “序列”对话框 图 3.95“数据有效性”对话框的“输入信息”选项卡（3）在多个单元格中输入相同的数据具体要求 在 H3 至 H32 单元格中输入相同的生活补贴： 220。
6、操作步骤 在多个单元格中输入相同的数据，可采取下列方式： 在 H3 单元格输入 220，鼠标指向 H3 单元格的填充柄，向下拖曳鼠标，填充到 H32 单元格。 选中 H3 到 H32 单元格，输入数据 220，再按 Ctrl+Enter 键。（4）设置数据有效性具体要求 在 G3 到 G32 单元格设置数据有效性：1000 到 4000。步骤 1 选中 G3 到 G32 单元格区域，选择“数据”|“有效性”命令，打开“数据有效性”对话框。步骤 2 在“ 数据有效性” 对话框中，选择“设置”选项卡，在 “允许”下拉列表中选择“小数” ，在“数据”下拉列表中选择“介于” ，在 “最小值”文本框中输
7、入“1000”，在“最大值”文本框中输入“4000” 。步骤 3 选择 “输入信息” 选项卡，如图 3.95 所示，在 “输入信息”下拉列表中输入“1000-4000”。步骤 4 选择 “出错警告” 选项卡，在“错误信息”下拉列表中输入 “基本工资最低1000，最高 4000”。当选中基本工资列的单元格为活动单元格时，下面出现标签显示设置的输入信息。若输入的数据小于 1000 或大于 4000，将会打开出错警告窗口，显示所设置的出错信息。3 自定义序列具体要求 将 9 月工资表的 B3 到 B32 单元格中的数据导入为自定义序列，在 “9 月水电读数”的 A2 到 A31 单元格中填充此序列。
8、输入数据时，用户可以填充“星期一、星期二、星期三”或“甲、乙、丙”等序列，这些都是系统预先设置的序列。通过“工具”|“选项” ，用户可根据自己的需要来定义序列。步骤 1 选择“工具”|“选项 ”命令，打开“选项”对话框。步骤 2 在“选项”对话框中，选择“自定义序列”选项卡，如图 3.96 所示。单击“从单元格中导入序列”文本框右边的按钮 ，选定单元格区域 B3 到B32，单击“导入”按钮。各位员工的姓名“陈源、邓小玲”被定义为一个序列。图 3.96 导入自定义序列步骤 3 切换到 “9 月水电读数 ”工作表，在 A2 单元格中输入“陈源” ，鼠标指向单元格的填充柄，向下拖曳到第 31 行，将
9、其他各位员工的名字将被填充出来。4 编辑公式首先，通过公式数计算第一位职工的岗位津贴、应发工资、水费、电费、个人所得税、扣款合计和实发工资，再将公式填充到其他行。（1）计算岗位津贴具体要求 根据职务等级计算岗位津贴：厅级职务津贴为 3000；处级职务津贴为 2000；科级职务津贴为 1000；办事员职务津贴为 500。操作步骤 选中 I3 单元格，输入公式=IF(F3=“厅 级 “,3000, IF(F3=“处 级 “,2000,IF(F3=“科 级 “,1000, 500)（2）计算应发工资具体要求 计算应发工资，应发工资为基本工资、岗位津贴和生活补贴的和。操作步骤 选中 J3 单元格，输入
10、公式=G3+H3+I3 或=SUM(G3:I3)（3）计算水费具体要求 计算水费，水费为用水度数与水费单价的乘积。操作步骤 选中 K3 单元格，输入公式 =9 月水电读数!B2*9 月水电读数!F$2 由于输入公式所在的工作表是“9 月工资表” ，需要引用的是“9 月水电读数”工作表的单元格，须在单元格名称前加上表名和！。另外，向下填充公式时，所引用的水费单价 F 2 单元格的名称不应变化，须在 2 前要加上$,表示对行号的绝对引用。（4）计算电费具体要求 计算电费，电费为用电度数与电费单价的乘积。操作步骤 选中 L3 单元格，输入公式=9 月水电读数!C2*水电表读数!F$3（5）计算个人所
11、得税具体要求 根据应发工资计算个人所得税，1000 元以下不扣税， 10002000 元之间扣税5%，2000 以上扣税 10%。操作步骤 选中 M3 单元格，输入公式=IF（ J31000,0,IF(J32000,(J3-1000)*0.05,1000*0.05+(J3-2000)*0.1)（6）计算扣款合计具体要求 计算扣款合计，扣款合计为水费、电费和个人所得税的和。操作步骤 选中 N3 单元格，输入公式 =K3+L3+M3 或=SUM(K3:M3)（7）计算实发工资具体要求 计算实发工资，实发工资为应发工资减去扣款合计。操作步骤 选中 O3 单元格，在编辑栏中输入公式 =J3-N3（8）
12、填充公式具体要求 将公式填充到其他行。操作步骤 选中 I3 到 O3 单元格区域，鼠标指向选定单元格区域右下角的填充柄，填充到第 32 行，释放鼠标。5 设置表格格式（1） 设置单元格的数字格式具体要求 设置 C3 到 C32 单元格的格式为“年月日” ，J3 到 N32 单元格保留小数点后一位。步骤 1 选中 C3 到 C32 单元格，单击鼠标右键，在快捷菜单中选择“设置单元格格式”命令，打开“单元格格式”对话框。选择“数字”选项卡，如图 3.97 所示。在“分类 ”列表框中选择“日期” ，在“类型”列表框中选择“2001 年 3 月 14 日” 。步骤 2 选中 J3 到 N32 单元格，
13、同样，打开“单元格格式”对话框。选择“数字”选项卡，如图 3.98 所示，在“分类 ”的列表框中选择“数字”, 在“小数位数”的数值框中输入“1” 。图 3.97 “单元格格式”对话框的“数字”选 图 3.98 “单元格格式”对话框的“数字”选项卡 项卡（2） 格式化单元格具体要求 合并 A1 到 O1 单元格，设置单元格对齐方式为居中，字体为华文彩云，字号为24，字形加粗，字体颜色为红色。步骤 1 选中 A1 到 O1 单元格，单击“格式”工具栏的“合并及居中”按钮 。步骤 2 在“ 格式”工具栏的 “字体”下拉列表中选择“ 华文彩云” ， “字号”下拉列表中选择 24，单击“加粗”按钮 ，
14、单击“字体颜色 ”按钮 右边的小三角形，在其菜单中选择“红色” 。（3） 设置单元格填充颜色具体要求 设置 C3 到 F32 及 K3 到 N32 的填充颜色为浅绿色， G3 到 J32 及 O3 到 O32 单元格的填充颜色为浅黄色操作步骤 选中 C3 到 F32 单元格，按住 CTRL 键，再选中 K3 到 N32 单元格，单击“格式”工具栏的“填充颜色”按钮 右边的小三角形，在下拉菜单中选择“浅绿色” 。选中 G3 到 J32 单元格，按住 CTRL 键不动，再选中 O3 到 O32 单元格，在“填充颜色”按钮的下拉菜单中选择“浅黄色” 。（4） 复制格式具体要求 选中 A2 到 O2
15、单元格，设置居中对齐，字形加粗，填充颜色为蓝色，字体颜色为白色。并将此格式复制到 A3 到 B32 单元格。步骤 1 选中 A2 到 O2 单元格，单击“格式”工具栏的“居中”按钮 ， “加粗”按钮 ，单击“填充颜色”按钮 右边的小三角形，在其下拉菜单中选择 “深蓝色” 。单击“字体颜色”按钮 右边的小三角形，在其下拉菜单中选择 “白色” 。步骤 2 选中 A2 单元格，选择“编辑”| “复制”命令。步骤 3 选中 A3 到 B32 单元格，选择 “编辑”|“选择性粘贴”命令，打开“选择性粘贴”对话框。如图 3.99 所示，选中“粘贴”下的“ 格式”单选按钮。复制格式后，A2 到 B32 单元
16、格的格式为与 A2 单元格的格式一致，而数据并无变化。图 3.99 “选择性粘贴”对话框 图 3.100“单元格格式”对话框的“边框”选项卡（5）设置边框具体要求 设置表格的外边框为粗线，内边框为细线。操作步骤 选中 A2 到 O32 单元格区域，选择“格式”|“单元格”命令，打开“单元格格式”对话框。选择“边框”选项卡，如图 3.100 所示。在“线条样式”中选择一种粗线，单击“预置”的“外边框”按钮；在“线条样式”中选择一种细线，单击“预置”的“内边框”按钮。（6）设置列宽具体要求 设置表格各列为最适合的列宽。操作步骤 鼠标指向 A 列的列号处，鼠标指针变为 。按住鼠标左键不动，向右拖曳鼠
17、标，直到 O 列的列号，选中 A 到 O 列。选择“格式”|“列” |“最适合的列宽”命令，系统根据各列的内容自动调整各列的宽度。设置完成后，9 月工资表如图 3.101 所示：图 3.101 格式化后的表格6.查看数据当用户要处理数据量大的工作表时，可以通过改变显示比例，暂时地隐藏列，冻结窗格来查看工作表。（1）改变显示比例具体要求 改变工作表的显示比例。操作步骤 在“常用”工具栏的“显示比例”下拉列表框中 选择或输入显示比例，可放大或缩小数据表。（2）隐藏列具体要求 隐藏 K 列到 M 列，再取消隐藏。操作步骤 鼠标指向 K 列的列号处，鼠标指针变为 。按住鼠标左键不动，向右拖曳鼠标，直到
18、 M 列的列号，选中 K 列到 M 列。在列号上单击鼠标右键，在快捷菜单中选择“隐藏列”命令，或选择“格式”|“列” |“隐藏 ”命令，这几列暂时不会显示，被隐藏起来。当需要查看隐藏列的时候，选中跨越了隐藏列的 J 列到 N 列，在列号上单击鼠标右键，在快捷菜单中选择“取消隐藏”命令，或选择“格式”|“列”| “取消隐藏”命令，这几列又显示出来。（3）冻结窗格具体要求 将工作表的 1、2 行和 A、B 列冻结起来。操作步骤 选中 C3 单元格，选择“窗口”|“冻结窗格”命令，则 C3 单元格上面的 1、2行和左边的 A、B 列被冻结起来。当窗口向下滚动的时候，第 1、2 行不会滚动；当窗口向右
19、滚动的时候，第A、B 列不会滚动。选择“窗口”|“取消冻结窗格 ”命令，用户可取消窗口的冻结。7.数据处理（1）数据排序具体要求 数据首先按职务等级从低到高排列，职务等级相同的按基本工资从高到低排列。默认情况下，汉字是按拼音顺序排列，职务等级将按“办事员、处级、科级、厅级”的顺序排列。如果要求按职务高低来排列数据，首先，要将“办事员、科级、处级、厅级”定义为一个序列；然后，在排序时设置按照自己定义的序列来排列。步骤 1 选择 “工具”|“选项”命令，打开“选项”对话框。选择“自定义序列”选项卡，如图 3.102 所示。单击 “自定义序列”列表框的“新序列” ，在“输入序列”编辑框中输入序列中的
20、各项值，单击 “添加”按钮，将“办事员、科级、处级、厅级”定义为一个序列。图 3.102 添加自定义序列步骤 2 将工资表数据区域中任一单元格作为活动单元格，选择“数据”|“排序”命令，打开“排序”对话框。步骤 3 在“ 排序”对话框中，如图 3.103 所示。在“主要关键字 ”下拉列表中选择“职务等级” ；在“次要关键字”选择“基本工资” ，再选择 “降序”单选按钮。步骤 4 单击 “排序”对话框的 “选项”按钮，打开“排序选项 ”对话框，如图 3.104 所示。在“自定义排序次序”的下拉列表中选择自定义的序列 “办事员、科级、处级、厅级” 。图 3.103“排序”对话框 图 3.104“排
21、序选项”对话框（2）自动筛选具体要求 筛选出实发工资最高的 10 名职工，再取消筛选。筛选出所有姓王和姓杨的职工，按姓名的升序排列。步骤 1 将工资表数据区域中任一单元格作为活动单元格，选择“数据”|“筛选”|“自动筛选”命令，在每列的列名上出现小三角形。步骤 2 单击 “实发工资” 列名的三角形，打开下拉菜单，选择 “前 10 个”命令，打开“自动筛选前 10 个”对话框，如图 3.105 所示。单击“确定”按钮，筛选出实发工资最高的 10 名员工。步骤 3 在“ 实发工资”列名的下拉菜单中选择 “全部显示 ”命令，则取消了筛选。图 3.105“自动筛选前 10 个”对话框步骤 4 单击 “
22、姓名”列名的三角形，在下拉菜单中选择“自定义”命令，打开“自定义自动筛选方式”对话框。步骤 5 在“ 自定义自动筛选方式 ”对话框中，如图 3.106 所示。在符号下拉列表中选择“始于” ，在条件下拉列表中输入 “王” 。单击“或”单选按钮，在下一行的符号下拉列表中选择“始于” ，在条件下拉列表中输入 “杨” 。在“姓名”列的下拉菜单中选择“升序排序”命令，筛选的数据如图 3.107 所示。图 3.106“自定义自动筛选方式”对话框 图 3.107 按姓名筛选的数据 （3）高级筛选具体要求 筛选出沙和北京的所有员工及所有职务为厅级的员工，取消筛选。步骤 1 在工资表数据区域外的单元格输入筛选条
23、件，如图 3.108 所示。第一行为列名，下面分别设置对该列数据要筛选的条件。 步骤 2 定位到工资表数据区域中任一单元格，选择“数据 ”|“筛选”|“高级筛选”命令，打开“高级筛选”对话框。步骤 3 在“ 高级筛选”对话框中，如图 3.109 所示，系统自动设置了列表区域。单击条件区域右边的按钮 ，选定刚才输入条件的单元格区域。单击“确定”按钮，筛选出的数据如图 3.110 所示步骤 4 选择 “数据”|“筛选”|“全部显示”命令，取消筛选。图 3.108 自定义筛选条件图 3.109“高级筛选”对话框 图 3.110 按高级筛选筛选出来的数据（4）数据汇总具体要求 统计出各个分公司的人数，应发工资和实发工资的和步骤 1 在数据汇总以前，首先要按分类字段进行排序。定位到分公司所在区域（D2 到 D32）的任一单元格，单击“常用”工具栏的“升序排序”按钮，同一分公司的员工排列在一起。步骤 2 选择 “数据”|“分类汇总”命令，打开“分类汇总”对话框。如图 3.111 所示，在“分类字段”的下拉列表中选择 “分公司” ，在“汇总方式”下拉列表中选择“求和” ，在“选定汇总项”列表框中，选中需要统计的 “应发工资” 、 “实发工资”字段前的复选框。
