Excel综合实验.doc

Excel2003综合实验 一、实验目的： 1.掌握Excel文档的格式化 2.掌握数据清单的操作 3.掌握页面设置 4.掌握图表的制作和编辑 5.掌握函数和公式的使用 6.掌握简单的数据分析方法：条件格式、排序、筛选、分类汇总 二、实验内容： 打开“工资表.xls”文档，完成如下要求，满分100分： 1.设置数据格式，调整列宽和行高。（16分，一个知识点2分） 1）“出生日期”列的数据为“01-3-14”格式，“基本工资”列设置小数位数为2； 2）整个工作表的内容水平、垂直都居中对齐，字号为14磅，设置第一行字体为隶书、18磅，并使标题相对于表格居中。 3）设置所有行高为20，所有列宽为合适状态。 2.计算“应发工资”、“实发工资”及有关合计。（12分） 1）应发工资=基本工资+薪级工资+补贴+房贴（4分）； 2）在A22单元格输入“合计”，计算基本工资合计、薪级工资合计、请假天数合计、应发工资合计（8分）。 3.设计表格线和底纹（8分） 1）为A3：O22单元格区域设置全部边框；然后选定A2:O2单元格区域，通过“单元格格式”对话框中的“边框”选项卡将选定区域的下边界设置为双实线（4分）。 2）选定“合计”行，将单元格中内容水平居中，并填充为“灰色-25%”（4分）。 设置后的效果如下图所示。 4.编辑报表（数据清单）（10分） 1）增加列：在“姓名”前插入“职工编号”列，在“基本工资”列前面插入“部门名称”列。在“职工编号”列中填充101001～101018，在“部门名”列中输入上图中所示内容（思考如何在多个单元格内输入相同内容）。（8分） 2）冻结表格的前2行和前3列，并观察冻结后的效果（2分）。 5.报表页面设置（10分） 1）设置页边距：上下页边距为2.5cm，左右页边距为2.1cm（4分）。 2）设置页眉、页脚：设置页眉为“制表人：XXX班XXX”（例如建筑2012级1班王菲）和日期，页脚中间为当前页，右侧为总页数，（3分）。 3）设置顶端标题行(使打印时所有页都有标题行)：把表格的前两行设置为顶端标题行（3分）。 6.制作图表（10分） 1）根据前4名职工的基本工资和薪级工资创建柱形图（4分）。 2）在图表的薪级工资图形上增加数据标志，将基本工资用数据点折线图表示。设置后的结果如下图所示（6分）。 操作提示：①插图表前应选择数据区：C3:I7，如下图： 把基本工资和薪绩工资两列数据都按柱形图做在一个图表中，然后修改基本工资数据的图表类型为折线。②右击图表，选择“源数据”，修改分类（X）轴标志的数据区，将$C$4:$G$7改为$C$4:$C$7，即得到横轴标识赵三丰、王振才、马建明、王霞。③右击图表，选择“图表选项”，再选“数据标志”标签，在此勾选“值”。 7.使用函数和公式（14分） 1）计算“扣款”列。每请一天假扣补贴的1/30，请假天数超过15天扣发全部补贴。在N4单元格输入公式“=IF(M415,J4,M4*J4/30)”，填充该列中其他数据。（4分） 2）计算“实发工资”列。实发工资=应发工资-扣款。（4分） 3）计算“基本工资”、“薪级工资”、“扣款”、“实发工资”的平均值。在A23单元格输入“平均值”，然后计算相应列的平均值。（6分） 8.分析数据（20分） 1）使用条件格式显示数据：用红色粗体字显示实发工资大于或等于2000元的数据。（4分） 2）对数据进行排序：将报表中的数据按基本工资由高到低排序，如果基本工资相同，则按薪级工资由高到低排序。（4分） 3）对数据进行筛选：将sheet1中的数据表（不包括图表）复制到sheet2、sheet3和sheet4中，在sheet2中使用自动筛选找出满足条件“实发工资大于等于1200的讲师”的记录。在sheet3中使用高级筛选找出满足条件“实发工资大于等于1200的讲师”的记录。（6分） 4）对数据进行分类汇总：在sheet4中按部门对基本工资、薪级工资、实发工资进行分类汇总。（6分） 操作提示：①选中实发工资全部数据，在“格式”菜单中选“条件格式”。②排序选数据区很重要，应选A3:O22后再排序。③在sheet2中先删除前两行，在“数据”菜单中选“筛选”，再“选自动筛选”，分别在职称和实发工资右侧的三角形上点击，最后选自定义④在表下面输入条件→选数据区A3:O21→在“数据”菜单中选“筛选”→选“高级筛选”→确认数据区和条件区⑤分类汇总应先按部门名称排序，在进行汇总。