NIT考试excel作业设计

姓名： xxx

班级： xxxxx

身份证号:xxxxxxxxxxxxxxxxxx

§概要说明

完成作业环境：

硬件：CPU：Pentium4 1.7G内存：256MB 硬盘: 40G

显示器：17寸彩显键盘：104键盘

软件：操作系统：Microsoft Window XP Professional

字处理软件：Microsoft(R) Excel 2003

§作业内容：实现腾达公司职工的实发工资统计的电算操作

一：公式、函数的应用及图表建立

作业目的：在所编辑的报表中应用公式和函数、掌握各种地址的应用方法,并为报表生成图表。

1. 问题的引出

公司为其职工每月工资的支出数据管理是一项数据量和工作量均较大的工作，并且容易出错。对于这种情况建议使用电子表格（Excel）来实现公司月工资支出数据的自动化管理。本作业取自腾达公司各部门职工基本工资、奖金、住房基金、保险费、实发工资和工资总支出的数据记录，并通过Excel实现对各职工实发工资的自动统计。2．原始数据的规定与收集

（1）工资规定

保险费：按人数计，100元/人

住房基金：按基本工资大小计，130元/人（>=800元/人·月）

100元/人（<800元/人·月）

（2）原始数据

本数据取于腾达公司2004年1月份工资统计表，它们包括：部门、姓名、基本工资（元）、奖金（元）、住房基金（元）、保险费（元）、实发工资（元）、工资总支出（元）。

（3）结果数据

腾达公司2004年1月份各部门职工实际工资支出、工资总支出结果。

3．电子表格设计

表格分为标题行、各部门职工每月工资数据统计表，及图形输出表区。其各项内容如下：

（1）标题行

给出表格名称（腾达公司各部门工资统计表）及年月。

（2）各部门职工每月工资数据统计表

由部门、姓名、基本工资（元）、奖金（元）、住房基金（元）、保险费（元）、实发工资（元）等七个数据列组成，附：平均工资（元）、最高工资（元）、最低工资（元）、工资总支出（元）、基本工资大于等于800元的人数、基本工资小于800元的人数。

（3）图表输出区

为了让Excel软件包接受并处理上述数据，根据上述栏目最多的字数，可将整张工作表的显示宽度设置为10个字符宽，在表格的实际建立过程中，还可根据需要改变某些列的宽度。

4．期望的功能与输出

为了实现腾达公司职工的实发工资统计的电算操作，本作业期望的功能和输出为：（1）输入和修改原始数据方便、快捷；

（2）根据公司职工每月工资中的各项数据自动计算出实发工资及工资总支出结果，并分别置于相应单元格中；

（3）当工资各项数据有变动时，只需修改相应内容既可更新上述结果；

（4）显示或打印一张完整的腾达公司各部门工资统计表（含柱形统计图）。

5．数据的处理过程

在Windows XP中启动使用Excel软件包。用鼠标单击“开始”---“所有程序”---Microsoft Excel命令，启动Excel。

（1）在Excel的电子表格中输入数据（除表标题取20号隶书加粗、标题行取10号宋体加粗外，其余均取10号宋体）

标题行：表格标题输入A1单元并到G1单元合并居中，统计的年月输入G2单元居中排列。

各部门职工工资统计输出表：栏目说明输入A3:G3区域；中间及结果数据计算公式输入A4:G19区域。其步骤是先将部门、姓名、基本工资（元）、奖金（元）、保险费（元）原始数据分别输入A4:A19、B4: B19、C4: C19、D4: D19、F4: F19

区域各单元，以上数据部门居中排列、姓名左对齐、其它数据均右对齐排列；

再将住房基金（元）、实发工资（元）、平均工资（元）、最高工资（元）、

最低工资（元）、工资总支出（元）的计算公式分别输入E4:E19、G4:G19、G21、G22、G23、G24各区域单元（注：先只输入第4行各单元，然后进行拖放复制），其中：

E4：= IF(C4>=800,”130”,”100”)

采用IF条件函数，当基本工资>=800时，住房基金取130；当<800时，取

100。

G4：= C4+D4-E4-F4 （基本工资+奖金-住房基金-保险费）

G21：=AVERAGE(G4:G19) （平均工资）

G22：=MAX(G4:G19) （最高工资）

G23：=MIN(G4:G19) （最低工资）

G24：=SUM(G4:G19) （各部门职工实发工资总和）

人数统计：基本工资大于等于800元的人数;

D21：=COUNTIF(C4:C19,">=800") (基本工资>=800元)

基本工资小于800元的人数

D23：=COUNTIF(C4:C19,"<800") (基本工资<800元)

以上数据均右对齐排列。

（2）公式的复制操作

采用单元格区域内容拖放复制的方法完成下列公式的复制操作：

单元E4 →区域E4:E19

单元G4 →区域G4:G19

（3）列显示宽度的调整

将所有列调整为最适合宽度。

（4）数据显示格式的调整

腾达公司各部门工资统计表中的数据均取货币类型，并保留两位小数，右对齐排列。

（5）该公司各部门职工实发工资堆积柱形图

用鼠标选中数据区域A4:B19,G4:G19；

单击常用工具栏中的“图表向导”按钮，弹出“图表向导”对话框；

设置图表类型为“柱形图”，子图表类型为“堆积柱形图”；单击“下一步”，在“系列产生在”中选择列，“下一步”；各选项卡分别设置为：在“标题”

中输入“腾达公司各部门职工工资统计图”；“主坐标轴”设置为：分类(X)

轴，自动，数值(Y)轴；“网格线”选择数值(Y)轴的主要网格线；在“图例”

选项卡中选中“显示图例”复选框；“数据标志”设置为“无”；继续“下一

步”，选择“作为其中的对象插入：工作表”，单击“完成”。

（6）原始数据区域的解除保护和工作表的保护

解除区域A4:A19、B4:B19、C4:C19、F4:F19各单元的加锁保护。其步骤为：选中各区域，右击鼠标左键，选择“设置单元格格式”，在弹出对话框中的“保

护”选项卡中选中“解除锁定”复选框。

对工作表设置保护。其步骤为：选择菜单栏的“工具”---“保护”---“保护工作表”命令，弹出“保护工作表”对话框，设置需要保护的各选项和密码。（7）选择菜单栏的“文件”---“另存为”命令，将生成的电子表格（图表）保存命名为“表格数据”。

6、表格和图表的格式化

（1）表格格式的设置

选中A3:G3区域；

选择菜单栏的“格式”---“单元格”命令，弹出“单元格格式”对话框，在“图案”选项卡选择单元格底纹颜色为“灰色-25%”；

选中A3:G19区域；

选择菜单栏的“格式”菜单---“单元格”命令，弹出“单元格格式”对话框；

在“边框”选项卡中的“边框”区域设置需要的边框，在“样式”区域设置边框线条的样式，选择颜色为“自动”；

再选中区域A1:G21，重复第四、五步操作即可完成边框的添加。

（1）图表格式的设置

双击堆积柱形图区域背景；