当前位置:文档之家› excel综合案例

excel综合案例

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工作表改名为“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所示。

在“线条样式”中选择一种粗线,单击“预置”的“外边框”按钮;在“线条样式”中选择一种细线,单击“预置”的“内边框”按钮。

(6)设置列宽具体要求设置表格各列为最适合的列宽。

操作步骤鼠标指向A列的列号处,鼠标指针变为。

相关主题