商务办公软件.doc

商务办公软件应用《Excel》实训报告

学院：经济学院
班级：12级国贸一班
实训指导教师：张必清
组长姓名：赵玲姗
小组成员：伍玉婷，赵玲姗，王秋，赵银娜，李昕言

2013年6月28日

实验一 建立员工工资表

一、实验目的
1． 掌握函数if、sum、index和vlookup的使用和功能
2． 学会在公式中使用绝对地址和相对地址
3． 学会条件格式的设置
二、实验类型
综合型
三、实验仪器
仪器名称：计算机
运用软件：Office2003-EXCEL2003
运用环境：Windows XP Professional
四、实验原理
利用Excel的函数及其功能进行复杂的数据提取和分析。
五、实验步骤和结果
1,先做出基本的员工工资表，工资表中必须包括如下几项：职员编号, 部门名称, 职员姓名, 基本工资, 浮动奖金, 核定工资总额, 交通/通讯等补助,迟到/旷工等扣减, 养老/医疗/失业保险, 合计应发, 应纳税额, 个人所得税, 实发工资
2．用SUM函数算出核定工资总额，核定工资总额=基本工资+浮动工资，如：=ＳＵＭ（Ｄ３：Ｅ３）
3. 用SUM函数计算合计应发（合计应发=核定工资总额+交通/通讯等补助+迟到/旷工等扣减+养老/医疗/失业保险）
4．计算应纳税额，应纳税额=合计应发-3500（基底工资）
5．计算个人所得税，用ＩＦ条件，根据税率表（个人所得税=应缴税额*该范围税率-扣除数）
６．利用公式计算实发工资
实验结果

职员编号
部门名称
职员姓名
基本工资
浮动奖金
核定工资总额
交通/通讯等补助
迟到/旷工等扣减
养老/医疗/失业保险
合计应发
应纳税额
个人所得税
实发工资
C001
行销企划部
黄建成
3800
4000
7800
200
0
80
8080
4580
562
7438

职员编号
部门名称
职员姓名
基本工资
浮动奖金
核定工资总额
交通/通讯等补助
迟到/旷工等扣减
养老/医疗/失业保险
合计应发
应纳税额
个人所得税
实发工资
C002
行销企划部
司马相
3500
3800
7300
200
-300
80
7280
3780
442
6758

职员编号
部门名称
职员姓名
基本工资
浮动奖金
核定工资总额
交通/通讯等补助
迟到/旷工等扣减
养老/医疗/失业保险
合计应发
应纳税额
个人所得税
实发工资
C003
人力资源部
黄平
3250
2000
5250
400
-180
90
5560
2060
184
5286

职员编号
部门名称
职员姓名
基本工资
浮动奖金
核定工资总额
交通/通讯等补助
迟到/旷工等扣减
养老/医疗/失业保险
合计应发
应纳税额
个人所得税
实发工资
C004
系统集成部
贾申平
2800
2500
5300
180
-200
100
5380
1880
163
5117

职员编号
部门名称
职员姓名
基本工资
浮动奖金
核定工资总额
交通/通讯等补助
迟到/旷工等扣减
养老/医疗/失业保险
合计应发
应纳税额
个人所得税
实发工资
C005
系统集成部
涂永宇
2600
1930
4530
180
0
100
4810
1310
106
4604

职员编号
部门名称
职员姓名
基本工资
浮动奖金
核定工资总额
交通/通讯等补助
迟到/旷工等扣减
养老/医疗/失业保险
合计应发
应纳税额
个人所得税
实发工资
C006
系统集成部
于志强
2300
2500
4800
180
-100
100
4980
1480
123
4757

职员编号
部门名称
职员姓名
基本工资
浮动奖金
核定工资总额
交通/通讯等补助
迟到/旷工等扣减
养老/医疗/失业保险
合计应发
应纳税额
个人所得税
实发工资
C007
系统集成部
胡图图
2800
2200
5000
180
-60
100
5220
1720
147
4973

实验二 人事档案管理与统计

一、实验目的
1.掌握常用文本函数：TEXT,MID,LEFT,LEN等的使用和功能
2.掌握常用日期函数：DATE、DATEDIF、TODAY等的使用和功能
3.使用数组公式进行求和
4.学会在公式中使用绝对地址和相对地址
二、实验类型
综合型
三、实验仪器
仪器名称：计算机　
运用软件：Office2003-EXCEL2003
运用环境：Windows XP Professional
四、实验原理
利用Excel的函数及其功能进行复杂的数据提取和分析。
五、实验步骤和结果
１　做出基本的表格　人事档案管理与统计系统，：职员编号, 职员姓名, 性别, 出生日期, 身份其中人事档案表中必须包括如下几项证号码, 参加工作时间,工龄, 民族, 籍贯, 学历, 毕业学校及专业, 职称, 现任职务, 工作简历。
２、　根据身份证号自动提取性别和出生年月日（＝IF（ＬＥＮ（B2）=15,............）
３、根据参加工作的时间自动计算工龄（把当前时间、入行时间的格式设置成日期格式！假如A1是入行日期、B1是当前日期，在C1输入公式：=datedif(A1,B1,"y")
４、根据不同性别年龄段和性别进行统计（用ＤＡＴＥＩＦ函数进行制作）
５、根据人事资料自动生成员工简历表（如：分别选中需要填写内容的单元格（除B2单元格外，如D2、F2等），输入公式：=IF(ISERROR(VLOOKUP(B2,简历!A3:K12,*,FALSE)),"",VLOOKUP(B2,简历!A3:K12,*,FALSE))
实验结果

人事档案管理与统计（2006-7-3）

职员编号
职员姓名
性别
出生日期
身份证号码
参加工作时间
工龄
民族
籍贯
学历
毕业学校及专业
职称
现任职务
工作简历
当前日期
C001
黄建成
男
1958-12-6
210201195812063251
1980-5-22
26年1月
汉
河南永城
本科
合工大机械制造专业
高工
经理
1:1965年至1968年上大学；2:1968至1988年在市公交公司工作；3：1989年至今在环宇公司工作
2006-7-3
C002
司马相
男
1965-2-8
210204196502085612
1989-4-8
17年2月
汉
安徽当涂
本科
安徽大机械热处理专业
高工
技术工人
1:1984年至1988年上大学；2:1988年至1993年在安于机械制造产工作；3:1994年至今在环宇公司工作
2006-7-3
C003
黄平
男
1968-12-9
210201196812098156
1990-12-9
15年6月
汉
安徽枞阳
大专
昆明冶金物流专业
中工
出货员
1:1988年至1991年上大专；2:1991年至今在环宇公司工作
2006-7-3
C004
贾申平
女
1977-9-19
203201197709192543
2001-8-29
4年10月
汉
安徽当涂
高中
安徽职业技术高中英语专业
高工
公关人员
1:1995年念完高中：2:1996年在中兴外语学校教书；3:1997年至今在环宇公司工作
2006-7-3
C005
于永宇
女
1975-12-5
210205197512052941
1997-11-20
8年7月
汉
皖安庆
本科
云南财大财务管理专业
高工
经理
1:1995年至1999年上大学；2:1999年至今在环宇公司工作
2006-7-3
C006
于志强
男
1975-6-22
210208197506228432
1996-5-11
10年1月
汉
安徽港城
本科
河南科技大工商管理专业
高工
秘书
1:1995年至1999年上大学；2:1999年至今在环宇公司工作
2006-7-3
C007
胡图图
男
1971-8-31
210223197108315912
1994-3-1
12年4月
汉
安徽和县
大专
爱因森计算机专业
高工
技术工人
1:1991年至1994年上大专；2:1995年至1997年留校担任计算机指导员；3:2000年至今在环宇公司工作
2006-7-3

年龄分布统计
年龄段
女性
比例
男性
比例
30岁以下
3
15%
3
15%
30-40
3
10%
3
10%
40-50
2
10%
4
20%
50岁以上
2
10%
6
30%

环宇汽车公司员工简历表

姓名
黄继承
性别
男
民族
汉
籍贯
河南
出生时间
参加工作时间
1958年12月6日 星期六
职称
高工
现任职务
经理
学历
大学
毕业学校及专业
合工大机械制造

工作简历
1965年至1968年上大学；2:1968至1988年在市公交公司工作；3：1989年至今在环宇公司工作

实验三 产品销售数据分析

一、实验目的
1.掌握并使用数据序列有效性快速输入数据
2.掌握并使用查找函数在表中找到需要的值
3.掌握并使用数组公式
4.掌握偏移函数OFFSET的使用
5.掌握查找函数indirect的使用
6.掌握并使用工作表编组

二、实验类型
综合型
三、实验仪器
仪器名称：计算机
运用软件：Office2003-EXCEL2003
运用环境：Windows XP Professional
四、实验原理
利用Excel的函数及其功能进行复杂的数据提取和分析。
五、实验步骤和结果
１初步形成表格（设计产品销售数据分析系统，其中销售业绩表中包括：日期, 销售部门, 销售员, 产品类别, 产品编号, 区域, 单价, 数量, 金额；提成表中包括：销售人员, 销售数量, 销售金额, 提成比率, 提成额）
２、制作下拉表（通过数据有效性等一系列设置完成）在销售业绩表中完成：通过下拉表选择部门，并根据部门出现不同的姓名以供选择；通过下拉表选择产品类别，并根据产品出现不同的姓名以供选择。
３、在销售业绩表中完成：通过查询函数在“产品与价格”中查找单价，并填充到单价列中。
４、在销售业绩表中完成（用基本函数完成）：计算产品的销售额
５、在销售提成表中完成：使用数组公式完成每人的销售数量和销售
实验结果

日期
销售部门
销售员
产品类别
产品编号
区域
单价
数量
金额
2003-10-2
销售一部
张月群
电视机
330BK
中部
1220
23
28060
2003-10-2
销售二部
司徒淳
影碟机
810BK
北部
1120
60
67200
2003-10-2
销售二部
司徒淳
电视机
C2919PV
北部
5260
52
273520
2003-10-2
销售三部
薛凯
影碟机
C2991E4
北部
4020
41
164820
2003-10-2
销售二部
董卿
影碟机
820BK
中部
980
36
35280
2003-10-3
销售二部
董卿
影碟机
C2919PK
西部
5300
52
275600
2003-10-3
销售一部
张月群
影碟机
C2991E
中部
4020
13
52260
2003-10-4
销售二部
何琳琳
影碟机
810BK
中部
1200
89
106800
2003-10-4
销售二部
高三
影碟机
810BK
南部
1020
45
45900
2003-10-4
销售一部
张岩
电视机
830BK
东部
920
12
11040
2003-10-4
销售一部
张岩
电视机
830BK
南部
920
46
42320
2003-10-4
销售二部
高三
电视机
C2919PK
南部
5300
13
68900
2003-10-5
销售三部
易天
电视机
C3518P
中部
980
12
11760
2003-10-5
销售一部
白百合
影碟机
330BK
北部
6300
42
264600
2003-10-5
销售二部
高三
电视机
830BK
南部
1300
27
35100
2003-10-5
销售三部
易天
影碟机
820BK
西部
1250
30
37500
2003-10-6
销售一部
白百合
电视机
830BK
中部
4500
28
126000
2003-10-6
销售二部
何琳琳
电视机
C2919PK
北部
1320
25
33000
2003-10-6
销售二部
何琳琳
影碟机
830BK
西部
1920
16
30720

销售人员
销售数量
销售金额
提成比率
提成额
张月群
36
80320
0.25%
200.8
高三
85
149900
0.25%
374.75
司徒淳
112
340720
0.31%
1056.232
张岩
58
53360
0.25%
133.4
白百合
70
390600
0.31%
1210.86
董卿
88
310880
0.31%
963.728
何琳琳
130
170520
0.25%
426.3
薛凯
41
164820
0.25%
412.05
易天
42
49260
0.25%
123.15

实验四 制作万年历

一、实验目的
1.掌握偏移函数OFFSET的使用
2.掌握并使用日期函数：data month datedif weekday now等
3.掌握控件按钮的使用技巧和显示图片。

二、实验类型
综合型
三、实验仪器
仪器名称：计算机
运用软件：Office2003-EXCEL2003
运用环境：Windows XP Professional
四、实验原理
利用Excel的函数及其功能进行复杂的数据提取和分析。
五、实验步骤及结果

1准备工作，制作如下电子表格：第一步：在C2中输入=TODAY（）回车。TODAY（）函数用于提取当前系统日期。
2第二步：在F2中输入=IF(WEEKDAY(C2,2)=7,"日",WEEKDAY(C2,2))  回车。
3第三步：在H2中输入 =NOW() 回车。NOW（）用于提取当前系统时间。
4第四步：在D14中设置下拉菜单，数值为1900—2029。
5第五步：在F14中设置下拉菜单，数值为1—12。

将查询日期调为当前日期，如下图：

6第六步：在A3中输入：
=IF(F14=2,IF(OR(D14/400=INT(D14/400),AND(D14/4=INT(D14/4),D14/100<>INT(D14/100))),29,28),IF(OR(F14=4,F14=6,F14=9,F14=11),30,31))回车。
7第七步：B3中输入=IF(WEEKDAY(DATE($D$14,$F$14,1),2)=B4,1,0)回车。选中B3向右复制公式至H3。
8第八步：B6中输入=IF(B3=1,1,0)回车。
9第九步：在B7中输入=H6+1回车，选中B7，向下复制公式至B9。
10 第十步：在B10中输入=IF(H9>=A3,0,H9+1)回车。
11第十一步：在B11中输入=IF(H10>=A3,0,IF(H10>0,H10+1,0))回车。
12第十二步：在C6中输入=IF(B6>0,B6+1,IF(C3=1,1,0))回车。
13第十三步：在C7中输入=B7+1回车。
14第十四步：选中C7向下复制公式至  C9。
15第十五步：在C10中输入=IF(B10>=$A$3,0,IF(B10>0,B10+1,IF(C6=1,1,0)))回车。
16第十六步：选中C6，向右复制公式至H6。选中C7，向右复制公式至H7。
                  选中C8，向右复制公式至H8。
                 选中C9，向右复制公式至H9。
                选中C10，向右复制公式至H10。
                选中B11，向右复制公式至H11。

17第十七步：选中第3行和第4行，字体颜色设置为白色。隐藏I列和J列。
18第十八步：美化万年历表格：更改日期和时间格式、设置单元格背景等式样
实验结果

万年历

当前日期
2013年6月
星期
肆
时间
2013-6-27 15:06
30
0
0
0
0
0
0
0
31
0
0
0
0
0
0
0

星期日
星期一
星期二
星期三
星期四
星期五
星期六

0
0
0
0
0
0
0

1
2
3
4
5
6
7

8
9
10
11
12
13
14

15
16
17
18
19
20
21

22
23
24
25
26
27
28

29
0
0
0
0
0
0

查询日期
2013
年
6
月

实验五 学生成绩统计系统设计

一、实验目的
1.掌握rank函数的使用
2.掌握Average、countif函数的使用
3.掌握Max 、large、min、small统计最大和最小的几个数
4.掌握使用数组公式进行条件计数和条件求和
5.掌握组合框控件的使用

二、实验类型
综合型
三、实验仪器
仪器名称：计算机
运用软件：Office2003-EXCEL2003
运用环境：Windows XP Professional
四、实验原理
利用Excel的函数及其功能进行复杂的数据提取和分析。
五、实验步骤和结果
步骤一：在excel表中，输入编号, 姓名, 语文成绩, 数学成绩, 英语成绩, 物理成绩, 化学成绩, 生物成绩, 总分, 名次, 班级，并填写相关信息。
步骤二：计算总分在I3单元格输入“=C3+D3+E3+F3+G3+H3”得出结果即可。计算名次在J3输入“=RANK(J3，$J$3:$J$47)得出结果即可。分班K3输入“=IF(I3<=15，“高三一班”，IF(I3<=30，“高三二班”，IF(I3<=45，“高三三班”，””)))得出结果即可。
步骤三：在C48输入“=AVERAGE(C3：C47)得出结果即可，其他科目同理利用公式得出答案，优秀率在C49输入“=COUNTIF(C3：C47，“》=85”/COUNT(C3:C47)得出结果即可 及格率同理

实验结果

编号
姓名
语文成绩
数学成绩
英语成绩
物理成绩
生物成绩
化学成绩
总分
名次
班级
1
张一
135
112
130
89
77
79
622
1
高三一班
2
张二
120
109
135
88
73
88
613
2
高三一班
3
张三
112
100
136
84
74
91
597
3
高三一班
29
李九
110
125
121
72
91
74
593
4
高三一班
28
李八
107
134
120
75
73
71
580
5
高三一班
19
张世久
124
111
116
65
67
90
573
6
高三一班
4
张思
102
98
137
82
75
76
570
7
高三一班
15
张世武
123
120
88
76
80
82
569
8
高三一班
20
张二十
125
102
118
69
80
75
569
9
高三一班
24
李四
106
108
117
91
79
65
566
10
高三一班
26
李留
102
109
135
80
70
67
563
11
高三一班
34
李世思
120
86
109
75
88
83
561
12
高三一班
25
李武
103
107
123
85
73
68
559
13
高三一班
35
李世武
123
105
99
71
77
84
559
14
高三一班
43
赵三
125
99
99
89
73
73
558
15
高三一班
18
张十八
119
110
106
70
68
82
555
16
高三二班
17
张士奇
107
119
105
71
69
83
554
17
高三二班
5
张武
110
99
118
75
76
75
553
18
高三二班
16
张十六
115
129
87
74
64
84
553
19
高三二班
14
张十四
116
128
89
53
81
81
548
20
高三二班
12
张十二
115
112
98
60
92
70
547
21
高三二班
27
李奇
101
113
124
64
71
70
543
22
高三二班
9
张九
97
94
112
73
93
73
542
23
高三二班
37
李诗琦
128
99
97
70
61
86
541
24
高三二班
42
赵二
108
103
111
81
71
67
541
25
高三二班
30
李氏
112
99
112
54
90
72
539
26
高三二班
21
李逸
98
114
103
68
82
73
538
27
高三二班
11
张诗宜
105
102
99
66
90
72
534
28
高三二班
22
李二
96
115
104
62
81
76
534
29
高三二班
36
李世柳
125
89
98
72
65
85
534
30
高三二班
6
张柳
100
97
119
71
78
68
533
31
高三三班
44
赵四
123
99
84
90
66
71
533
32
高三三班
33
李十三
119
88
105
60
79
81
532
33
高三三班
39
李世久
99
110
86
84
62
90
531
34
高三三班
10
张氏
89
99
113
64
94
71
530
35
高三三班
8
张吧
99
95
104
72
81
78
529
36
高三三班
32
李世尔
118
87
104
59
79
80
527
37
高三三班
23
李三
87
118
119
63
72
66
525
38
高三三班
45
赵武
113
99
85
91
65
72
525
39
高三三班
13
张十三
104
113
97
54
76
80
524
40
高三三班
40
李二师
98
112
85
82
56
91
524
41
高三三班
41
赵一
97
101
103
83
70
68
522
42
高三三班
7
张启
98
96
109
65
79
69
516
43
高三三班
31
李诗意
114
89
101
55
78
75
512
44
高三三班
38
李氏吧
99
97
89
74
64
87
510
45
高三三班

平均分
109.91
105.56
107.76
72.69
####
####
#####

优秀率
77.27%
61.36%
68.18%
13.64%
####
####

及格率
95.45%
88.64%
81.82%
88.64%
####
####

前三名
135
134
137
91
94
91

128
129
136
90
93
90

125
128
135
89
92
88

后三名
96
88
86
55
62
67

89
87
85
54
61
66

87
86
84
53
56
65

人数
语文平均
数学平均
英语平均
物理平均
化学平均
生物平均

高三一班
15
114.43
108.07
118.07
####
####
#####

高三二班
15
110.13
108.33
104.20
####
####
#####

高三三班
15
103.80
100.00
100.20
####
####
#####

实验六 求解方程式

一、实验目的
1.掌握图表的使用
2.掌握单变量求解的使用
3.掌握规划求解的使用

二、实验类型
综合型
三、实验仪器
仪器名称：计算机
运用软件：Office2003-EXCEL2003,vba
运用环境：Windows XP Professional
四、实验原理
利用Excel的函数及其它功能进行复杂的数据提取和分析；利用图表和数据之间的动态关系分析数据。
五、实验步骤和结果
1 在A1 、A2单元格中分别输入字符串X 及3x-cos(x)-1=0
并适当改变ＡＢ两列的列宽
２在Ｂ２单元格输入3×EXP（Ａ２）-cos(Ａ２)-1=0
3执行工具单变量去求解菜单
4 在弹出的对话框中输入目标单元格为B2 设目标值为0 设可变单元格为A2（计算前为空单元）
5 单击确定 在A2单元显示出答案
利用exce l的函数及其它功能 进行方程式求解，具体要求如下：
1．用图解法和迭代法完成方程式3x-cos(x)-1=0的求0解。
2．采用矩阵法和规划求解下列方程组。
2x-2y+4z=-12
2x+3y+2z=8
-x+y-z=3.5
3．采用规划求解下述求解约束方程。
目标函数：3x+y+3z
3x+y+z<=2
x+2y+3z<=5
2x+2y+z<=6
x,y,z >= 0

实验结果

x的步长
x值
3x-cos(x)-1
x值
3x-cos(x)-1

0.1
0.1
-1.695004165
0.607099663
-7.08721E-06

0.2
-1.380066578

0.3
-1.055336489

0.4
-0.721060994

0.5
-0.377582562

0.6
-0.025335615

0.7
0.335157813

0.8
0.703293291

0.9
1.078390032

1
1.459697694

1.1
1.846403879

1.2
2.237642246

1.3
2.632501171

1.4
3.030032857

1.5
3.429262798

1.6
3.829199522

1.7
4.228844494

求解方程：3x-cos(x)-1=0

A
B
矩阵求解
规划求解
2
-2
4
-12
X1=
2
X1=
2
-12
2
3
2
8
X2=
3
X2=
3
8
-1
1
-1
3.5
X3=
-2.5
X3=
-2.5
3.5

2x-2y+4z=-12

2x+3y+2z=8

-x+y-z=3.5

X1
X2
X3
目标函数
约束1
约束2
约束3
0.125
0
1.625
5.25
2
5
1.875

目标函数：3x+y+3z

3x+y+z<=2

x+2y+3z<=5

2x+2y+z<=6

x,y,z >= 0