excel综合案例

实用文档

. 3.5 综合案例

案例分析

本节通过建立一个工资表的Excel电子表格，使读者进一步掌握电子表格的输入技巧，如何查看数据量大的表格，了解数据透视表的使用。

设计要求

本电子表格共有四个工作表，其中，一个工作表为9月工资表，如图3.88所示；一个工作表为9月水电读数；一个工作表为应发工资统计图，如图3.89所示；一个工作表为数据透视表，如图3.90所示。

图3.88 9月工资表

图3.89 应发工资统计图 图3.90 数据透视表 实用文档 . 3.5.2设计步骤

翻开电子表格文件“工资表原始〞，执行以下操作。

1． 工作表管理

〔1〕修改工作表的名称

具体要求 将sheet1工作表改名为“9月工资表〞

操作步骤 在“Sheet1〞标签上双击，“Sheet1〞处于反白状态，输入新的工作表名称：“9月工资表〞，按回车键确认。

〔2〕删除工作表

具体要求 删除Sheet2工作表

操作步骤 在“Sheet2〞工作表标签上单击鼠标右键，选择快捷菜单中的“删除〞命令。

〔3〕复制工作表

具体要求 将“水电表原始〞工作簿的“9月水电读数〞工作表复制到本工作簿文件中。

步骤1 翻开“水电表原始〞工作簿，切换至“9月水电读数〞工作表，选择“编辑〞|“移动或复制工作表〞命令，翻开“移动或复制工作簿〞对话框。

步骤2 在“移动或复制工作簿〞对话框中，如图3.91所示。在“工作簿〞下拉列表中选择“工资表原始〞，在“以下选定工作表之前〞选择“移至最后〞，选中“建立副本〞复选框。

单击“确定〞按钮后，在本工作簿的最后，新增了一个“9月水电读数〞工作表。

图3.91“移动或复制〞工作表对话框 图3.92录入数据的快捷菜单 图3.93 从列表中选择数据

2． 编辑“工资表〞数据

单击“工资表〞标签，切换到“工资表〞工作表。

〔1〕插入行

具体要求 在第11行前插入一行：林致远，1974-2-8，长沙，财会部，科级，1430。

操作步骤 选中11行〔姓名张志峰〕的任一单元格作为活动单元格，选择“插入〞|“行〞命令，那么11行的前面插入了空行。

在空行中输入数据：林致远，1974-2-8，长沙，财会部，科级，1430。

技巧 输入数据

在输入分公司、部门、职务等级这几列的数据时，选中单元格后，单击鼠标右键，在快捷菜单中选择“从下拉列表中选择〞，如图3.91所示。单元格的下面出现列表，显示出上面的行中曾输入的数据，如图3.92所示。用户可直接从列表中选择需要输入的数据。 实用文档

. 〔2〕填充等差序列

具体要求 在A3至A32单元格中填充工号：1到30。

操作步骤 工号1-30是等差序列，可采取以下方式填充。

➢ 在A3单元格输入1。将鼠标放在A3单元格的填充柄上，按住Ctrl键，鼠标指针变为带有加号的实心十字架。向下拖曳鼠标，填充到A32单元格。

➢ 在A3单元格输入1，在A4单元格输入2。选中A3到A4单元格，鼠标指向A4单元格的填充柄，向下拖曳鼠标，填充到A32单元格。

➢ 在A3单元格输入1，选择“编辑〞|“填充〞|“序列〞命令，翻开“序列〞对话框。如图3.94所示。在“序列产生在〞选择“列〞单项选择按钮，在“类型〞选择“等差序列〞单项选择按钮，在“步长值〞文本框中输入1，在“终止值〞文本框中输入30。

图3.94 “序列〞对话框 图3.95“数据有效性〞对话框的“输入信息〞选项卡

〔3〕在多个单元格中输入相同的数据

具体要求 在H3至H32单元格中输入相同的生活补贴：220。

操作步骤 在多个单元格中输入相同的数据，可采取以下方式：

➢ 在H3单元格输入220，鼠标指向H3单元格的填充柄，向下拖曳鼠标，填充到H32单元格。

➢ 选中H3到H32单元格，输入数据220，再按Ctrl+Enter键。

〔4〕设置数据有效性

具体要求 在G3到G32单元格设置数据有效性：1000到4000。

步骤1 选中G3到G32单元格区域，选择“数据〞|“有效性〞命令，翻开“数据有效性〞对话框。

步骤2 在“数据有效性〞对话框中，选择“设置〞选项卡，在“允许〞下拉列表中选择“小数〞，在“数据〞下拉列表中选择“介于〞，在“最小值〞文本框中输入“1000〞，在“最大值〞文本框中输入“4000〞。

步骤3 选择“输入信息〞选项卡，如图3.95所示，在“输入信息〞下拉列表中输入“1000-4000〞。

步骤4 选择“出错警告〞选项卡，在“错误信息〞下拉列表中输入“根本工资最低1000，最高4000〞。

中选中根本工资列的单元格为活动单元格时，下面出现标签显示设置的输入信息。

假设输入的数据小于1000或大于4000，将会翻开出错警告窗口，显示所设置的出错信息。

3． 自定义序列

具体要求 将9月工资表的B3到B32单元格中的数据导入为自定义序列，在“9月水电读数〞的A2到A31单元格中填充此序列。 实用文档

. 输入数据时，用户可以填充“星期一、星期二、星期三……〞或“甲、乙、丙……〞等序列，这些都是系统预先设置的序列。

通过“工具〞|“选项〞，用户可根据自己的需要来定义序列。 步骤1 选择“工具〞|“选项〞命令，翻开“选项〞对话框。

步骤2 在“选项〞对话框中，选择“自定义序列〞选项卡，如图3.96所示。

单击“从单元格中导入序列〞文本框右边的按钮，选定单元格区域B3到B32，单击“导入〞按钮。

各位员工的姓名“陈源、邓小玲……〞被定义为一个序列。

图3.96 导入自定义序列

步骤3 切换到“9月水电读数〞工作表，在A2单元格中输入“陈源〞，鼠标指向单元格的填充柄，向下拖曳到第31行，将其他各位员工的名字将被填充出来。

4． 编辑公式

首先，通过公式数计算第一位职工的岗位津贴、应发工资、水费、电费、个人所得税、扣款合计和实发工资，再将公式填充到其他行。

〔1〕计算岗位津贴

具体要求 根据职务等级计算岗位津贴：厅级职务津贴为3000；处级职务津贴为2000；科级职务津贴为1000；办事员职务津贴为500。

操作步骤 选中I3单元格，输入公式

=IF(F3="厅级",3000, IF(F3="处级",2000,IF(F3="科级",1000, 500)))

〔2〕计算应发工资

具体要求 计算应发工资，应发工资为根本工资、岗位津贴和生活补贴的和。

操作步骤 选中J3单元格，输入公式=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〕计算个人所得税

具体要求 根据应发工资计算个人所得税，1000元以下不扣税，1000～2000元之间扣税5%，2000以上扣税10%。

操作步骤 选中M3单元格，输入公式

=IF〔J3<1000,0,IF(J3<2000,(J3-1000)*0.05,1000*0.05+(J3-2000)*0.1))

〔6〕计算扣款合计

具体要求 计算扣款合计，扣款合计为水费、电费和个人所得税的和。

操作步骤 选中N3单元格，输入公式=K3+L3+M3或=SUM(K3:M3)

〔7〕计算实发工资

具体要求 计算实发工资，实发工资为应发工资减去扣款合计。

操作步骤 选中O3单元格，在编辑栏中输入公式=J3-N3

〔8〕填充公式

具体要求 将公式填充到其他行。

操作步骤 选中I3到O3单元格区域，鼠标指向选定单元格区域右下角的填充柄，填充到第32行，释放鼠标。

5． 设置表格格式

（1） 设置单元格的数字格式

具体要求 设置C3到C32单元格的格式为“年月日〞，J3到N32单元格保存小数点后一位。

步骤1 选中C3到C32单元格，单击鼠标右键，在快捷菜单中选择“设置单元格格式〞命令，翻开“单元格格式〞对话框。

选择“数字〞选项卡，如图3.97所示。在“分类〞列表框中选择“日期〞，在“类型〞列表框中选择“2001年3月14日〞。

步骤2 选中J3到N32单元格，同样，翻开“单元格格式〞对话框。

选择“数字〞选项卡，如图3.98所示，在“分类〞的列表框中选择“数字〞, 在“小数位数〞的数值框中输入“1〞。

图3.97 “单元格格式〞对话框的“数字〞选项卡 图3.98 “单元格格式〞对话框的“数字〞选项卡 实用文档

. （2） 格式化单元格

具体要求 合并A1到O1单元格，设置单元格对齐方式为居中，字体为华文彩云，字号为24，字形加粗，字体颜色为红色。

步骤1 选中A1到O1单元格，单击“格式〞工具栏的“合并及居中〞按钮。

步骤2 在“格式〞工具栏的“字体〞下拉列表中选择“华文彩云〞，“字号〞下拉列表中选择24，单击“加粗〞按钮，单击“字体颜色〞按钮右边的小三角形，在其菜单中选择“红色〞。

（3） 设置单元格填充颜色

具体要求 设置C3到F32及K3到N32的填充颜色为浅绿色，G3到J32及O3到O32单元格的填充颜色为浅黄色

操作步骤 选中C3到F32单元格，按住CTRL键，再选中K3到N32单元格，单击“格式〞工具栏的“填充颜色〞按钮右边的小三角形，在下拉菜单中选择“浅绿色〞。

选中G3到J32单元格，按住CTRL键不动，再选中O3到O32单元格，在“填充颜色〞按钮的下拉菜单中选择“浅黄色〞。

（4） 复制格式

具体要求 选中A2到O2单元格，设置居中对齐，字形加粗，填充颜色为蓝色，字体颜色为白色。并将此格式复制到A3到B32单元格。

步骤1 选中A2到O2单元格，单击“格式〞工具栏的“居中〞按钮，“加粗〞按钮，单击“填充颜色〞按钮右边的小三角形，在其下拉菜单中选择“深蓝色〞。

单击“字体颜色〞按钮右边的小三角形，在其下拉菜单中选择“白色〞。

步骤2 选中A2单元格，选择“编辑〞|“复制〞命令。 步骤3 选中A3到B32单元格，选择“编辑〞|“选择性粘贴〞命令，翻开“选择性粘贴〞对话框。如图3.99所示，选中“粘贴〞下的“格式〞单项选择按钮。

复制格式后，A2到B32单元格的格式为与A2单元格的格式一致，而数据并无变化。

图3.99 “选择性粘贴〞对话框 图3.100“单元格格式〞对话框的“边框〞选项卡

〔5〕设置边框

具体要求 设置表格的外边框为粗线，内边框为细线。

操作步骤 选中A2到O32单元格区域，选择“格式〞|“单元格〞命令，翻开“单元格格式〞对话框。选择“边框〞选项卡，如图3.100所示。在“线条样式〞中选择一种粗线，单击“预置〞的“外边框〞按钮；在“线条样式〞中选择一种细线，