Excel实验报告-.pdf

一、课程感想四周时间， 说长不长， 说短不短 眼看着旧同学们都放假回家了，我还留在学校上着万恶的小学期，说实话，心里一百个不情愿，好在我也没有浪费这一个月的时光作为财务管理专业的学生，我们必须要面对一个事实——每天面对各式各样的数据，因此，掌握对数据整理、归类、分析的技巧是非常必要的，而excel就是其中之一小学期选修了excel 在经济管理中的应用，excel 是微软办公套装软件的一个重要的组成部分， 它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、 金融等众多领域老师选了三个专题对excel 之于数据的管理进行了讲解，分别是成绩统计和分析、 工资的核定和整理、销售汇总 这三个专题涉及到excel应用的三个基本方面下面简单谈谈我从每个case 中学到的东西Case 1：制作基本信息的时候，有三个知识点首 先 是 由 身 份 证 号 生 成 个 人 的 出 生 日 期 使 用 了 一 个 复 合 函 数 ， 即 ：=MID(C2,7,4)&"-"&MID(C2,11,2)&"-"&MID(C2,13,2)它的意义是从C2 的第 7 个数字开始取，取 4 个数字，形成“年份” ，从 C2的第 11 个数字开始取，取2 个数字，形成“月份” ，从 C2的第 13 个数字开始取，取2 个数字，形成“日期” 。

此外还有由身份证信息生成性别信息函数表示为 =IF(MOD(MID(C2,17,1),2)=0,"女","男") 它的意义是：从C2的第 17 位开始取值，取1 位，用这个数字除以2，若余数为0，则显示为“女” ，否则显示为“男” 最后是由身份证信息自动计算年龄函数为 =YEAR(TODAY())-MID(C2,7,4)， 它的意义是：今天日期代表的年份，减去身份证号码从第7 位开始取4 位（即出生年份）所得的差值以上三个看似简单的常识，要用计算机的语言表达出来并不是键容易的事，还有一些细节需要注意 而这些方法和函数可以广泛应用到人力资源（HR ）管理中 键入身份证的基本资料就可以通过函数取得其他的基本资料，省去了认为换算和计量的麻烦和误差其后的成绩排名最基本的是rank 函数，但要以文字信息“不及格”“及格”“良好” “优秀”来表述会更加直观，也更有实际意义在书写评级时应用到Lookup 函数，这是excel中最基本的函数类型，在小学期学到的各个case 里面也得以体现成绩分析是case 1 最精髓的部分，因为应用到了数理统计的部分统计部分用了三种方法，分别是Lookup 函数、 Frequency 函数、直方图，而在统计人数时用到了Countif函数，这也是人力资源（HR ）中常常会用到的。

Case 2：Case 2 将在之后做详细叙述，在此不赘言Case 3：Case 3 是一个销售情况的统计，主要从记录、排序、数据筛选、分类汇总、数据透视几个方面介绍了excel 在经济管理中的应用销售汇总表中主要的知识点是随机函数的生成，在case 2 中广泛使用，在此不赘述记录单主要应用于数据的逐条输入和查找，具体操作是： 数据——记录单，弹出如下选项卡此时可以根据需要录入数据或是进行查找；既可以逐条查找，也可以按条件查找排序时需要注意的是排序区域的选择，具体操作详见case 2 的分析报告数据筛选是这个case 重点， 主要分为自动筛选和高级筛选自动筛选的具体操作同case 2, 高级筛选的前提是自定义建立一个筛选条件，该case 中主要是以平均单价为筛选条件的，其条件书写的函数为：=C9>$J$2，得到的结果是“TRUE ”&“FALSE ” 根据条件就可以进行高级筛选，即：数据——筛选——高级筛选，弹出如下选项卡在列表区域选择整个数据区域，条件区域选择之前设置的条件，点击确定后就可以自动生成筛选结果，如图高级筛选的意义是可以自定义筛选条件，对于企业根据不同需求筛选数据有重要意义。

最后是数据透视选中数据区域后，点击数据——数据透视表和数据透视图，生成以下界面，之后再根据系统提示操作即可数据透视图的优点在于可以根据需要对不同指标之间建立关系，进行分析二、Case 2 制作过程1.sheet1 ——税率表税率表根据税法规定编制2.Sheet2 ——工资表工资表由 “职工编号”“单位” “类别” “姓名”“等级工资”“岗位工资”“应发合计”“公积金” “医疗保险” “应扣合计” “实发工资”“个调税1” “个调税2”13 个科目组成，其中广泛应用到随机函数的生成和财务函数下面将一一作出叙述1）职工编号在第一个单元格中打出“100001” ，将鼠标移动到单元格的右下角，当鼠标呈现出黑色小十字时向下拖动拖动100 个单元格后，右下方会出现一个“自动填充选项”控件，选择其中的“以序列方式填充”，此时将会呈现100001 至 100100 共 100 个职工编号2）单位单位是由0～9 共 10 个数字作为代码，代表不同部门的不同岗位，其中涉及到应用随机函数由系统自动生成数字我使用了取整函数int和随机函数rand 其中取整函数int是取得小于输入数值的最大整数，随机函数rand 是取 0～1 中任意数值。

因此， 单位代码的输入函数为 =int(rand()*10),其代表的意义是将0～1 中任意数值扩大10 倍， 然后取得小于或等于所得值的最大整数由于随机函数会随同操作不断变化，因此需要将随机函数值固定具体操作是选中所有随机生成的函数区域，单击鼠标右键，选择“选择性粘贴”，弹出选项卡，选择“数值” ，如图这样就可以将随机函数值固定，并且在单击此单元格时只出现数值，而不会显示函数内容3）类别单位共由“行政” “科研”“教学”“后勤”四部分组成因为文本内容无法由excel系统自动生成，因此我选择逐一随机设定4）姓名为了简便，我以张1～张 100 代表不同人名在第一个单元格输入张1，将鼠标放在右下角，当出现黑色十字时向下拖动单击右边“自动填充选项”控件，选择其中的“以序列方式填充”，此时将会出现张1～张 100 共 100 个人名5）等级工资等级工资也是应用excel随机函数由系统自动生成我设定所有的等级工资都在1000元以上，因此将1000 作为保底具体的函数是=int(rand()*1000)+1000,其意义是将0～1中任意值扩大1000 倍，取小于或等于生成的随机数的最大整数，这个数值介于0～1000 之间，再加上1000，则最后等级工资介于1000～2000 之间。

同理，随机函数值应予以固定，即单击鼠标右键，选择“选择性粘贴”，选择“数值”选项由于“等级工资”是货币形式，因此选中此列， 单击鼠标右键， 选择“设置单元格格式” ，在“数字选项卡”中选择 “货币”，小数位数选择“2” ，如图6）岗位工资岗位工资应用了excel随机函数我设定所有岗位工资在3000 元以上，因此将3000作为保底 具体的函数是 =int(rand()*3000)+3000,其意义是将0～1 中任意值扩大1000 倍，取小于或等于生成的随机数的最大整数，这个数值介于0～1000 之间，再加上3000，则最后等级工资介于3000～4000 之间同理，随机函数值应予以固定，即单击鼠标右键，选择“选择性粘贴” ，选择“数值”选项；同样设置单元格格式7）应发合计“应发合计”是由“等级工资”和“岗位工资”两部分构成使用excel 函数加总“等级工资”和“岗位工资”，具体编写是=E3+F3将鼠标移动到单元格的右下角，当鼠标呈现出黑色小十字时向下拖动由于是非绝对引用，因此每行一次变动，即：E4+F4，E5+F5等8）公积金 &医疗保险这两个项目也是由excel 随机生成的随机函数是=int(rand()*500),之后将鼠标移动到单元格的右下角，当鼠标呈现出黑色小十字时向下拖动。

由于随机函数值应予以固定，即单击鼠标右键，选择“选择性粘贴”，选择“数值”选项；同样设置单元格格式为“货币”，保留两位小数9）应扣合计“应扣合计”由“公积金”和“医疗保险”两部分组成其编写为=H3+J3，之后将鼠标移动到单元格的右下角，当鼠标呈现出黑色小十字时向下拖动由于是非绝对引用，因此每行一次变动，即：H4+J4， H5+J5 等10）实发工资“实发工资”是由“应发合计”减去“应扣合计”，具体编写为G3-J3之后将鼠标移动到单元格的右下角，当鼠标呈现出黑色小十字时向下拖动11）个调税个调税有两种编写方法A．法 I：编写函数=(K3-2000)*VLOOKUP(K3-2000, 税率表 !$C$2:$F$10,3)-VLOOKUP( 工资表 !K3-2000n,税率表 !$C$2:$F$10,4)之后将鼠标移动到单元格的右下角，当鼠标呈现出黑色小十字时向下拖动B．法 II：编写函数=(K3-2000)*VLOOKUP(K3-2000,{0,500,0.05,0;500,2000,0.1,25;2000,5000,0.15,125;5000,20000,0.2,375;20000,40000,0.25,1375;40000,60000,0.3,3375;60000,80000,0.35,6375;80000,100000,0.4,10375;100000,1000000,0.45,15375},3)-VLOOKUP(K3-2000,{0,500,0.05,0;500,2000,0.1,25;2000,5000,0.15,125;5000,20000,0.2,375;20000,40000,0.25,1375;40000,60000,0.3,3375;60000,80000,0.35,6375;80000,100000,0.4,10375;100000,1000000,0.45,15375},4)。

这个函数可以由法I 操作生成 双击某个单元格，将在函数栏生成一个函数，将鼠标指向函数，会出现函数帮助，单击table array，再按 F9，此时自动生成以上函数，如图之后将鼠标移动到单元格的右下角，当鼠标呈现出黑色小十字时向下拖动3.sheet3 ——查询查询共有两种方式，一下逐一论述（1）法 I ——使用 excel自动筛选将“工资表”复制到“查询”，在下方设置“职工编号”“实发工资” “个调税”三个类别将职工编号复制到“职工编号”类别下，“实发工资” “个调税”实用index函数插入其中实发工资是个调税是之后将鼠标移动到单元格的右下角，当鼠标呈现出黑色小十字时向下拖动将鼠标放在“职工编号”上，点击菜单栏的“数据”菜单，选择“筛选”，再选择“自动筛选” 2）法 II ——使用窗体控件查询输入“职工编号” “实发工资”“调节税”三个类别在“职工编号”单元格下插入窗体控件，具体操作是：鼠标指向“窗口”选项，单击右键，选择“窗体”，选择组合框，如图在“职工编号”下的单元格里插入组合框单击鼠标右键，选择设置控件格式，键入数据区域和数据输出单元格、数据容量， 如图 在组合框里选择一个数，则“实发工资” 和“调节税”自动变动。

4.sheet4 ——分类汇总先将工资表中的所有数据复制到“分类汇总” 工作表把鼠标放在一个分类单元格上（如：类别） ，选择工具栏中的“排序”，对其进行排列，如图：选择菜单栏中的“数据”，再选“筛选” ，后“自动筛选” ，如图：选择“数据”，后选“分类汇总” ，弹出对话框选择分类字段、汇总方式及汇总项，如图：点击确定后得到分类汇总结果，如图：5.sheet5 ——工资条制作工资条总体上有两种方法，其中一种会有因细微差别造成的函数参数不同下面逐一叙述1）法 I ——使用函数插入A.插入工作表 “工资条数据源1” ，将工资表中所有内容复制到该工作表在工作表 “工资条 1”的第一行输入“职工编号”等内容，在其下的单元格中插入函数，如图：“职工编号”下会出现100001，之后将鼠标移动到单元格的右下角，当鼠标呈现出黑色小十字时向右拖动，此时， 此行全部填充完再用鼠标选中前三行，后将鼠标移动到单元格的右下角， 当鼠标呈现出黑色小十字时向下拖动，拖动的空白行数是3 的倍数 此时整个工作表都被填充完毕B．插入工作表“工资条数据源2” ，将工资表中除标题以外（第一行）的所有内容复制到该工作表在工作表“工资条2”的第一行输入“职工编号”等内容，在其下的单元格中插入函数，如图：“职工编号”下会出现100001，之后将鼠标移动到单元格的右下角，当鼠标呈现出黑色小十字时向右拖动，此时， 此行全部填充完。

再用鼠标选中前三行，后将鼠标移动到单元格的右下角， 当鼠标呈现出黑色小十字时向下拖动，拖动的空白行数是3 的倍数 此时整个工作表都被填充完毕2）法 II ——建立宏使用工作表“工资条数据源1”的数据，在工作表“工资条3”的第一行输入“职工编号”等内容，在其下的单元格内输入函数=index( 工资条数据源1！A:A，int(row()/3+3))职工编号”下会出现100001，之后将鼠标移动到单元格的右下角，当鼠标呈现出黑色小十字时向右拖动，此时，此行全部填充完选择菜单栏中的“工具”，选择“宏” ，再选“录制新宏” 后用鼠标选中前三行，后将鼠标移动到单元格的右下角，当鼠标呈现出黑色小十字时向下拖动3 行选择“工具” ——“宏”——“停止录制” 删去后三行，选择“工具”——“宏”——“宏”，将弹出对话框，如图：选择刚刚录制的宏，点击执行此时，后三行又将执行为了使这个宏执行100次，要对宏的编辑函数进行修改即是要让“拖动鼠标依次呈现”这个动作再重复98 次将目标行设定到303 行，点击确定，此时会自动生成100 条独立的工资条。