实验项目一  常见表格的制作  

一、实验目的

本实验主要是全面复习excel中的基础知识，让学生对excel有一个基本的了解，会制作基础表格，并能够对该表格进行相关的基础设置。

二、实验原理或方法、手段

此次实验主要采用讲授法、互动式教学法。

三、主要仪器设备及耗材

多媒体、相关表格案例等

四、学生课前准备事项

对excel的基本界面和常见术语有所了解

五、实验内容及步骤

（一）理论讲解

excel2007的基本界面和基本术语

介绍excel2007工作界面，区分工作簿、工作表和单元格的含义；

excel数据：包含数字、文本、公式，数字包含文本型数字和数值型数字；

介绍连字符&：文本运算符，连接字符串。

（二）示范操作

使用填充柄来填充序列、复制数据，双击快捷键自动填充数据；

调整单元格行高和列宽美化表格，选中表格双击快速调整合适列宽；

在表格里，输入以“0”为首长度的销售人员编号，设置销售人员编号输入限制条件为6位，并设置输入提醒和出错提醒，演示加密工作表和工作簿的方法，并演示如何取消加密；

新建一个楼盘楼栋的空白表格，在空白表格里面输入楼栋号，用连字符&来制作房号，用公式减和除来计算公摊和公摊率，介绍多个方法比较房号的输入，房号的要求是2栋2单元8层4户，跟学生互动分析房号的排列情况，分别是号是1-4循环，楼层是4个1-4个8循环，单元是32个1-32个2循环，栋是64个1-64个2，一共有128个房间。

方法一：用现有知识填充数据来做，首先填充楼层4次1-8，筛选后排序变成我们需要的4个1-4个8，然后选中数据仅数值填充，然后填充号1-4，选中数据仅数值填充，选中单元填充32个1和32个2，选中数据仅数值填充，然后填充栋64个1和64个2，即完成所有房号的所有数据填充，现在我们需要运用&连接符将这些数据组合起来=C2&"-"&D2&"-"&E2&"-"&F2；

方法二：需要运用替换这个知识点就可以完成a-b-c-1，填充到a-b-c-4，之后逐渐替换，显示下，学生之后自行完成，这个方法比较繁杂并不推广；

方法三：运用函数来做，让学生体会到函数的强大作用，对该函数点到为止的介绍，在后面学习工资表会深入进行讲解。=CEILING(ROW(A1)/64,1)&"-"&(MOD((CEILING(ROW(A1)/32,1)-1),2)+1)&"-"&(MOD((CEILING(ROW(A1)/4,1)-1),8)+1)&"-"&(MOD(ROW(A4),4)+1)；

用条件格式查找楼盘价格表是否有重复的楼栋号，如有便用数据—重复值清除重复楼栋号，用条件格式突出价格大于50万和小于30万的房号，用公式乘和除计算房号的单价和总价，要求单价保留2位小数，总价为整数，用函数sum计算该价格表总价格，并用函数average计算价格表的平均价格。

（三）学生操作

六、实验注意事项

1.实验过程中，学生应遵守相关教学制度。

2.实验结束后要进行反思与总结。

七、实验报告（根据课程需要设计）

实验项目二  员工工资表及工资条的制作  

一、实验目的

本实验主要制作员工工资表，通过该实验课的学习，希望学生根据自己的专业与企业的管理方式和工资构成状况相结合，制作不一样的工资表，由于企业里工资是发放给个人的，还需要为个人发放工资条，所以教会学生制作工资条。

二、实验原理或方法、手段

此次实验主要采用讲授法、互动式教学法。

三、主要仪器设备及耗材

多媒体、相关表格案例等

四、学生课前准备事项

对工资表的构成有一定的了解

五、实验内容及步骤

（一）建立基础表结构

根据要求建立员工基本信息表结构

包括员工姓名、编号、学历等各方面的内容，员工基本信息中的基本工资等信息是工资表中计算总工资的一部分，介绍id字段的作用，其是联系 各个工作表的纽带，该字段的设置方式是员工编号加上年和月的信息，该字段可以保证在指定的年份和月份中是唯一的，基本工资和工龄工资用会计专用，小数位数设置为2，无货币类型。

设置员工基本信息表公司

员工id使用文本运算符&完成，基本工资的要求是专科以下学历为2000元，专科学历为2600元，本科学历为3200元，硕士研究生学历为3800元，博士研究生学历为4400元，使用函数match来做，具体操作“=2000+（match（H2，{“专科以下”，“专科”，“本科”，“硕士”，“博士”，0}）-1）*600”，完成基本工资计算。

工龄工资DATEDIF(start_date,end_date,unit)
Start_date 为起始日期；End_date 为结束日期。
Unit 为所需信息的返回类型。Unit 返回
"Y" 时间段中的整年数。
"M" 时间段中的整月数。
"D" 时间段中的天数。

DATE(year,month,day)将指定的年、月、日合并为完整的日期格式，系统默认为1990日期系统，也就是1990年1月1日日期编号为1，=DATEDIF(I2,DATE(A2,B2,1),"y")*100。              

设置考勤表结构及公式

VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)

注意：在使用该函数时，lookup_value的值必须在table_array中处于第一列。

=VLOOKUP(E2,员工基本信息!$E$2:$F$15,2,0)

如果我们不用绝对引用我们，可以将第二个参数设置为：员工基本信息!E:F

设置业绩表结构及公式

生产数量和价格乘积的5%提成，每单位数量的计算标准是300元，非生产和销售部门的业绩工资按照200单位固定值计算。

=IF(OR(F2="销售",F2="生产"),G2*300*0.05,G2*200*0.05)

（二）导入员工基本信息表内容

数据—获取外部数据—自其它来源—来自Microsoft query—查找数据所存储位置—双击选中的表格—筛取所需的列—在Microsoft query中查看数据—条件—添加年份和月份，设置指定值，由于是个变动的量，所以用[]标示出来。—将数据返回excel表格—弹出导入数据对话框—选择放置的位置—选择输入参数值—勾选“在以后的刷新中使用该值或该引用”。

开发工具—插入—窗体控件—设置空间格式—当前值1最小1最大12步长1单元格链接$B$2。

（三）制作工资表

方法一：VLOOKUP函数

方法二：lookup函数

有两种形式：

向量形式:

公式为 = LOOKUP(lookup_value,lookup_vector,result_vector)

第一个参数表示查找的数值，第二个和三个参数只能是一列或者一行，区域大小相同，第二个参数必须升序排列。

数组形式:公式为= LOOKUP(lookup_value,array)

表示在区域或者数组中查找数据。第二个参数的第一列必须升序排列。如果没有查找的数值，那么返回区域里小于该数值的最大值。区域的行要多于等于列，否则返回最后一个数值。

应发数：选中k2-k8区域，公式选项卡下点击自动求和。

应缴税所得额: =K2-个税税率表!$E$2或者直接减3500。

（五）制作个人应缴所得税

认识“个税税率表”速算法。

一共7个级别，这个表的意思是应纳税所得额在相应级别范围内，用应纳税所得额*相应税率—速算扣除数。

对于这类连续区间递增的表格，我们可以考虑if函数嵌套，在这节课我们介绍了一个lookup函数，这类函数就可以对有递增区间的表格进行查询。

介绍个分布查询复杂公式。

在excel中不乏复杂公式，在使用复杂公式计算数据时如果对计算结果产生怀疑，可以分布查询公式。

公式——公式审核——公式求值

具体操作方法：

Lookup的向量和数组方式来做，vlookup函数来做。

（六）制作工资条

不用函数的方法：

方法一：复制标题，填充数字并升序排列

方法二：对角线输入数据，定位常量或者空值插入一行，复制标题行，再定位空值，粘贴即可。

用函数有三种方法

方法一：vlookup函数。

先在姓名前加辅助列添加序号，复制标题到第9行，在c10中输入1.

“=VLOOKUP($A10,$A$2:$N$8,COLUMN(),0)”

方法二：用mod函数和index函数来做

Mod函数，返回两数相除的余数，结果的正负号与除数相同。

Index函数：返回表或区域中的值或对值的引用。

两种用法，有数组形式和引用形式，这节课我们掌握数组形式，对引用形式感兴趣的同学可以自行学习，数组形式有三个参数，INDEX(array，row_num，column_num)第一个参数是区域，第二个是返回的行序号，第三个是返回的列序号。注意：行与列序号是相对于第一个参数区域而言的，并不是整个工作表，类似vlookup函数对于列序号的规定。

无空行：除2余1返回标题行，除2余0返回数据行。

空一行，意思是行号是9，12，15……是标题内容，行号是10，13，16……是具体的数据，行号为11，14，17……为空行，这些行规律就是凡是行号除以3余数为0就是标题行，余数为1就是数据行，余数为2就是空行。

空两行：要求9，13，17……标题内容，行号为10，14，18……为数据，剩余行号都为空行，这些行规律就是凡是行号除以4余数为1就是标题行，余数为2就是数据行，余数为0和3就是空行。

方法三：offset函数

分析数据，单行都是标题行，双行则为数据行，从姓名开始，双行则分别向下偏移1、2、3……，在excel函数里有个函数offest，可以处理偏移取数类的数据。

offset函数的含义

是以一个参照点，通过偏移量得到一个新的引用

返回的引用可以是一个单元格也可以是一个区域（可以指定行列数）。

Offset（参照单元格，行偏移量，列偏移量，返回几行，返回几列）如果省略 height 或 width，则默认其高度或宽度与 reference 相同。

如图所示，返回的区域形式相同。

先隔行添加序列。A10输入1。

六、实验注意事项

1.实验过程中，学生应遵守相关教学制度。

2.实验结束后要进行反思与总结。

七、实验报告（根据课程需要设计）

实验项目三  统计图表的应用  

一、实验目的

认识和掌握图表的数据、文字、类型和格式，掌握制作符合要求的图表，能够将数据转换成可视化的图表进行数据分析。

二、实验原理或方法、手段

此次实验主要采用讲授法、互动式教学法。

三、主要仪器设备及耗材

多媒体、相关表格案例等

四、学生课前准备事项

对各类型图表有一定的区分

五、实验内容及步骤              

利用柱形图分析员工考评成绩，具体步骤：创建柱形图表，调整图表布局，设置图表格式；

创建销售情况统计图，具体步骤：使用迷你图分析销售量变化趋势，创建销量对比图，月销售额比例图；

制作人力资源月报，具体步骤：制作员工总人数变化图，制作各部门员工性别分布图，制作各部门员工年龄结构分布图，制作各部门员工人数分布图；

介绍相关的操作技巧，包括使用推荐的图表，快速分析图表，设置双轴图表。

六、实验注意事项

1.实验过程中，学生应遵守相关教学制度。

2.实验结束后要进行反思与总结。

七、实验报告（根据课程需要设计）

实验项目四  分析数据  

一、实验目的

运用排序和筛选来分析数据，并且运用数据透视表和数据透视图可视化动态分析数据。

二、实验原理或方法、手段

此次实验主要采用讲授法、互动式教学法。

三、主要仪器设备及耗材

多媒体、相关表格案例等

四、学生课前准备事项

对数据的分析有一定的认识

五、实验内容及步骤

（一）排序和筛选

排序销售数据，具体步骤：简单升降序排序，使用排序对话框升降排序，新增条件进行复杂排序，自定义排序。

筛选订单明细,具体步骤：自动筛选，自定义筛选，高级筛选。

（二）分类汇总

分类汇总部门费用，创建和删除分类汇总；显示分类汇总的分页打印功能。

（三）数据透视表

介绍数据透视表的术语：数据源、字段、项/项目。

数据透视表对数据源的要求：数据源第一行必须要有标题，不能有相同的标题，标题不能是占两个单元格，不能有合并单元格，不能有公式，不能有小计、合计、分类汇总，也不要有空行、空列，同一列中数据类型要一致。

生成订单统计透视表，具体步骤：创建数据透视表，设置数据透视表字段，更改数据透视表的报表布局。

修改订单的数据，刷新得到新的数据透视表。

动态数据透视表：介绍将数据区域转换成表格，表格是列与行的集合，因此在数据源的增加数据会默认添加到表格里，形成动态数据透视表。

（四）数据透视图

应用数据透视图表分析产品销售情况，按部门分析产品销售情况：创建数据透视图，设置双轴图表，分析产品销售情况，按月份分析各产品平均销售额。

技巧总结，一键调出明细数据，使用分组功能按月显示汇总数据，刷新数据透视表。

六、实验注意事项

1.实验过程中，学生应遵守相关教学制度。

2.实验结束后要进行反思与总结。

七、实验报告（根据课程需要设计）

实验项目五  数据的模拟运算和预决算分析  

一、实验目的

能够通过合并计算和假设分析制作预测报表。

二、实验原理或方法、手段

此次实验主要采用讲授法、互动式教学法。

三、主要仪器设备及耗材

多媒体、相关表格案例等

四、学生课前准备事项

对数据的分析有一定的认识

五、实验内容及步骤

（一）合并计算不同分部的销售数据

使用工作组创建表格

创建工作簿和工作表，选定全部工作表，组成工作组，输入基本项目，执行对话框启动器命令，设置边框，查看边框效果，设置底纹颜色，选择颜色，查看颜色设置效果，按产品类别求和，选择求和区域，填充求和公式，按季度求和，执行取消组合工作表命令，输入上海分部的销售数据，输入北京分部的销售数据。

合并计算产品销售额

执行合并计算命令，设置函数类别，设置引用区域，添加引用位置，设置引用区域，添加引用位置，查看计算结果。

（二）使用假设分析预测月度销售收入

打开素材文件，执行显示公式命令，查看计算公式，取消显示公式，执行假设分析命令，设置求解项目，确认设置，查看求解结果。

（三）使用假设分析制作产品利润预测表

根据单价预测产品利润

根据单价和销量预测产品利润；

（四）预测产品的保本销量

使用定义名称创建方案，显示和修改方案，生成方案摘要；

技巧总结，使用单变量求解命令实现利润最大化，如何清除模拟运算表，使用双变量求解功能计算贷款的月供。

六、实验注意事项

1.实验过程中，学生应遵守相关教学制度。

2.实验结束后要进行反思与总结。

七、实验报告（根据课程需要设计）