excel补充题2.docx
《excel补充题2.docx》由会员分享，可在线阅读，更多相关《excel补充题2.docx（29页珍藏版）》请在冰豆网上搜索。
excel补充题2
第二部分EXCEL试题
一、excel格式设置
1.购货优惠统计表
日期
顾客
购货金额
反劵金额
10月1日
（1）A1：
D1区域格式设置；合并及居中，垂直居中，填充颜色为黄色，字体颜色为红色，字号为19磅。
（要求：
对于工作表格式化的操作，可以使用“格式工具按钮”或使用“右键快捷菜单”）
（2）A3：
A12区域格式设置：
合并及居中，垂直居中。
（3）A2：
D12区域格式设置：
填充颜色为浅绿色。
（4）在B3：
B12区域输入甲、乙、丙······癸等文字，水平居中。
（5）在C3：
C12区域：
输入初始值为1000，步长为1500的序列（结果采用默认格式）。
（6）在D3：
D12，利用IF（）函数，计算出反劵金额。
（当购货金额大于等于10000时，按购货金额的25%获得反劵金额；当购货金额大于7000时，按购货金额的15%获得反劵金额，当购货金额小雨7000时，按购货金额的5%获得反劵金额。
）
（7）D3：
D12区域数据设置为会计专用，货币符号为“中文（中国）”
2.销售人员绩效考核表
姓名
性别
商品单价
销售数量
销售额
是否完成销售任务
男
456.13
5
女
6
男
9
女
11
男
23
女
56
男
7
女
12
男
27
女
59
（1）在A3：
A12区域输入甲、乙、丙······癸等文字，水平居中。
（2）B3：
B12区域水平居中，粗体。
（3）在E3：
E12区域，计算出销售额，计算结果保留两位小数。
（4）在F3：
F12用IF函数，定义公式：
销售额大于等于5000，显示为“完成”，否则显示为“未完成”。
（5）在F3：
F12区域，将“未完成”设置为红字，加粗格式。
（提示：
使用格式工具按钮）。
（6）A2：
F12格式为：
外边框为红色双线，内部为绿色细线。
（7）A1：
F1区域格式为：
合并及居中。
（8）A2：
F12区域格式为：
水平居中。
3.滨山市公务员考试成绩单（部分考生）
完成以下统计：
最高分：
最低分；
平均分：
表一：
年份
区县编号
报名序号
考号
姓名
行测成绩
申论成绩
面试成绩
总分
2012
01
00001
张三
93
66
88
2012
02
00002
李四
41
68
50
2012
03
00003
王五
78
72
68
2012
04
00004
李珊
26
88
56
2012
05
00005
江开
88
68
66
2012
06
00006
王开红
78
50
60
2012
07
00007
本田
71
78
67
2012
08
00008
约汉
80
85
77
2012
09
00009
天花
60
65
99
2012
10
00010
黎明
89
80
79
第3题
（1）A1：
I1合并及居中、垂直居中；填充颜色为“大蓝”，行高为40.5，字号为26磅。
（2）A2：
I2区域行高33.75，填充颜色为“浅黄”，字体为“蓝色”。
（3）在D3：
D12区域，利用文本运用符“&”连接“年份”、“区县编号”、“报名序号”，生成考号。
（4）A3：
I12区域的填充颜色为灰色—25%
（5）利用函数，在I3：
I12区域计算总分
（6）利用函数在L3：
L5计算总分的最高分，最低分和平均值（结果保留金两位小数）
4.某公司2012年1月份材料费用分配表
产品名称
实际产量（件）
单位消耗定额（千克）
定额消耗量（千克）
分配率
材料成本（元）
甲产品
575
18
乙产品
625
32
丙产品
588
18
丁产品
676
32
戊产品
557
18
己产品
652
32
合计
299517.59
（1）在D3：
D8单元格区域中计算甲、乙、丙、丁、戊、己各种产品的定额消耗量（保留两位小数，采用默认格式）。
（2）在D9单元格计算定额消耗量合计（保留两位小数，采用默认格式）。
（3）在E9单元格计算分配率（保留两位小数，采用默认格式）。
（4）在F3：
F8单元格区域计算甲、乙、丙、丁、戊、己各种产品的材料成本（保留两位小数，采用默认格式）。
5.
水费清洁费计算表
水费单价：
5.34
单元编号
上月读表数
本月读表数
本月用水量
本月水费金额
清洁费
本月费用合计
101
12
25
18
102
17
40
103
19
34
201
23
67
202
25
90
203
40
77
301
45
58
302
26
67.6
303
50
96
401
44
88
402
16
45
403
34
56
501
60
88
502
23.5
51
503
66
99
第3题
（1）A1：
G1区域格式为：
行高30，填充颜色为黄色，字体颜色为红色，字体为隶书，字号23磅，合并及居中。
（2）A2：
G2区域格式为：
行高25，填充颜色为：
青绿色，水平居中。
（3）A3：
G17区域格式为：
行高17，填充颜色为灰色25%，字体为黑色，13磅。
（4）在D3：
D17区域计算本月用水量，计算结果保留两位小数。
（5）在E3：
E17区域计算出本月水费金额，计算结果保留两位小数。
（6）在G3：
G17区域计算出本月费用合计，计算结果保留两位小数。
（7）A2：
G17外边框为红色双线，内部为蓝色双线。
6.各部门上半年销售统计表
月份
部门
1233
1655
6543
2345
5480
3490
3444
4567
1122
1111
2222
3333
1234
6543
2345
3333
1235
6458
5234
2346
6754
3455
1300
3200
（1）A1：
G1区域格式：
行高35，跨列居中，填充颜色为黄色，字体颜色为粉色，字号为25磅
（2）A2格式为：
填充颜色为灰色25%。
（3）在B2：
G2区域输入一月······六月，并设置格式为：
水平居中、垂直居中、填充颜色为“青绿色”
（4）在A3：
G6区域输入销售1部、销售2部······销售4部，并水平居中
（5）A2：
G6区域格式为：
外边框为蓝色双线，内部为红色细线。
（6）根据A2：
G6区域数据制作三维堆积柱形图，系列产生在行，图表标题为“各部门销售统计图”，将图表嵌入在本工作表右侧
（7）将图表标题设置为红字，字号为20磅，双侠划线
7.海天公司员工销售情况统计表
员工ID
姓名
冰箱
彩电
洗衣机
微波炉
1
张宏
93
66
90
88
2
李天羽
41
68
54
50
3
王金明
78
72
74
68
4
李珊
26
88
45
56
5
黄则名
29
48
51
56
6
田竖笛
78
50
56
60
7
陈津津
71
78
60
67
8
冀洪辰
80
85
62
77
9
翟丽丽
85
45
41
58
10
刘成成
8
64
73
40
（1）标题A1：
F1区域，行高30，合并居中，垂直居中，填充颜色为黄色，字体颜色为红色，字号17磅。
（2）将员工ID，1,2,3······10用001,002,003······010表示，并水平居中。
（3）A2：
F12区域：
填充颜色为青绿色，外边框、内边框线均为双线，白色。
（4）利用B2：
F12区域数据，制作折线图，图表标题为：
海天公司员工销售统计图，图表放于本表右侧
二、Excel常用财务函数
1.年金终值函数FV（rate,nper,pmt,pv,type）
例题：
5年中每年存入银行100元，存款利率为8%，求第5年末年金终值。
每年年底存入100元FV=
每年年初存入100元FV=
2.年金现值函数PV（rate,nper,pmt,fv,type）例题：
现在存入一笔钱，准备在以后5年每年得到100元，如果存款利率为8%，现应该存入多少钱？
每年年底得到100元PV=
每年年初得到100元PV=
3．年金函数PMT（rate,nper,pv,fv,type）
本金函数PPMT（rate,per,nper,pv,fv,type）利息函数IPMT（rate,per,nper,pv,fv,type）
项目
第1年
第2年
第3年
第4年
第5年
还款合计
各年还款额
各年还款本金
各年偿还利息
本息合计
例题：
某企业贷款36000元，贷款期限为5年，年利率为8%,采用等额本息还款方式，每年年末支付。
请问：
各年还款额是多少？
各年还款本金是、利息各是多少？
4．期数函数RATE（rate,pmt,pv,fv,type,）
例题：
某企业贷款36000元，年利率为8%,采用等额本息还款方式，每年还款总额9016，需要多少年还清贷款？
期数函数NPER=
5.利率函数RATE（nper,pmt,pv,fv,type,guess）例题：
某企业租用一设备，租金36000元，每年年末支付租金，若租期为5年，每年支付9016元，则利率为多少。
利率函数RATE=
6.
年利率：
6.50%
贷款金额：
480000
贷款期限：
5
每月月底须缴款金额：
五年到期总缴款金额：
（1）B1：
B5填充颜色为：
浅黄色。
（2）李先生向银行贷款购商品房，年利率6.5%，贷款金额480000元，贷款期限5年且等额偿还，请计算李先生每月还款多少元？
5年到期总还款金额为多少？
（年金按年计算，每月末还款。
请将公式定义在B4和B5单元格中，结果要求为正数，会计专用，货币符号为“中文（中国）”，保留两位小数）
7.
已知条件：
每年存款
年利率
期限
2000
6%
8
答案：
（1）某人8年中每年末存入银行2000元，存款利率为6%，求第8年末年金终值，请在A6单元格作答（结果要求为正数，保留两位小数）。
（2）A6单元格格式设置：
填充颜色为；玫瑰红，数字格式为：
会计专用，货币符号为：
中文（中国），保留两位小数。
二、投资决策函数
1．净现值函数
净现值函数NPV（RATE,VALUE1，VALUE2，…）
投资决策
期数
A方案净现金流量
B方案净现金流量
0
-150000
-200000
1
40000
20000
2
40000
25000
3
40000
30000
4
40000
35000
5
40000
40000
6
40000
45000
7
40000
50000
8
40000
55000
净现值NPV
98392
23,404
资金成本率6%
2．内含报酬率函数
内含报酬率函数IRR（VALUES,GUESS）
期数
甲方案净现金流量
乙方案净现金流量
0
-2000000
-2500000
1
450000
690000
2
600000
750000
3
700000
840000
4
650000
720000
5
550000
650000
6
500000
640000
内含报酬率IRR
投资方案的比较
3.
投资评价
期间
A项目
B项目
C项目
0
—10000
—20000
—30000
1
10000
15000
20000
2
8000
10000
15000
3
7000
10000
10000
净现值
内含报酬率
（1）计算各项目的净现值（假定利率为10%）和内含报酬率，请将公式定义在上表中（B7：
B8），（结果保留两位小数，采用默认格式）。
4.
已知：
数据
某项目初期成本
—70000
第一年的收入
15000
第二年的收入
18000
第三年的收入
20000
第四年的收入
22000
第五年的收入
25000
答案：
投资四年后的内容报酬率（%）
投资五年后的内容报酬率（%）
（1）已知某项目初期成本和个年收入，按照要求计算内含报酬率，请在B9和B10单元格作答（结果保留两位小数，采用默认格式）。
（2）B9：
B10区域的填充颜色为“浅黄色”，字号为17磅，加粗。
三、折旧函数
1.直线折旧函数SLN（cost,valvage,life）
例某机器设备，可以使用期限5年，原值1500000元，预计净残值150000元，用直线法SLN计算每期折旧额.
2.年数总和法函数SYD（cost,valvage,life，per）
例某自动化机床，原始成本1000000元，预计净残值100000万元，使用期限5年，用年数总和法SYD计算1-5年的折旧额
3.双倍余额递减函数DDB（cost,valvage,life，PERIOD,FACTOR）
例假设某一公司购买自动化生产设备的成本为20000元，预计使用年限为5年，预计净残值为600元，采用双倍余额递减函数DDB计算各期的折旧额.（前三年采用双倍余额递减法计算,后两年采用直线法计算）前三年
4.倍率余额递减函数
VDB（cost,valvage,life，stard-period,end-period,factor,no_switch）
例见上例
第一年折旧额=
第二年折旧额=
第三年折旧额=
第四年折旧额=
第五年折旧额=
第一期到第三期的累积折旧额=
第一期到第五期的累积折旧额=
5.
已知：
说明
数据
资产原值
20,000
预计净残值
600
使用寿命
5
计算结果：
计算第1年的折旧值
计算第2年的折算值
计算第3年的折算值
计算第4年的折算值
计算第5年的折算值
（1）某项资产相关数据见下表，根据DDB和SLN函数，计算每年的固定资产折旧值，请在B7：
B11区域作答（保留两位小数，采用默认格式）。
6.
第1年的折旧值
第2年的折旧值
第3年的折旧值
第4年的折旧值
第5年的折旧值
第10年的折旧值
（1）假设购买一台服务器，价值为30000元，使用期限为10年，残值为3500元，请利用年数中和法计算第1、2、3、4、5年和第10年的折旧值，请在把B3：
B8区域作答，否则答题无效（保留两位小数，采用默认格式）。
四、查找函数
1.VLOOPUP（lookup_value,table_array,col_nem,range_loopup））
例
美的公司小家电库存表
产品名称
期初库存量
本月入库数
本月退货数
盘亏/盘盈
期末库存量
12寸鸿运扇
235235
21562156
35
3
2359
13寸鸿运扇
542542
5674
56
-2
6159
柜式空调
876
8923
85
-6
9708
1P窗饰空调
986
2315
45
2
3258
2P窗饰空调
84
5678
12
-1
5749
单门冰箱
943
9874
2
0
10815
双门冰箱
426
1235
0
-1
1660
产品名称2P窗饰空调期末库存量5749
本月退货数12
存货盘亏/盘盈-1
2.
农产品名称
单价（元/公斤）
小麦
2.4
水稻
2.8
玉米
2.5
花生
12.2
大豆
4.4
棉花
8.2
高粱
2.2
豌豆
3.2
绿豆
9.0
农产品名称
数量（公斤）
单价（元/公斤）
销售金额（元）
玉米
10000
大豆
2000
高粱
2000
豌豆
500
绿豆
500
总金额
（1）请利用VLOOKUP函数查找相应农产品的单价，填在F2：
F6单元格中（结果采用默认格式）
（2）在G2：
G6区域，计算每种农产品的销售金额，结果保留两位小数，采用默认格式。
（3）利用函数在G7单元格计算总金额，保留两位小数，采用默认格式。
3.
今日蔬菜单价（元）
黄瓜
3.5
韭菜
3.4
大葱
3.6
荷兰豆
7.5
茄子
2.3
冬瓜
2.5
豆角
8.5
西红柿
3.5
菜花
2.8
丝瓜
4.2
土豆
1.8
蔬菜名称
蔬菜单价
销售量（公斤）
销售金额（元）
荷兰豆
12
茄子
15
黄瓜
70
韭菜
47
大葱
9
西红柿
8
冬瓜
20
土豆
10
豆角
9
菜花
12
丝瓜
6
（1）利用VLOOKUP函数，在E4：
E14计算出蔬菜单价，结果保留两位小数，采用默认格式。
（注意：
必须利用函数操作，否则不得分。
）
（2）在G4：
G14区域，计算销售金额，结果保留两位小数，采用默认格式。
（3）根据D3：
D14，G3;G14，制作三维图，数据标志位百分比，图表标题为“红星超市1月1日销售统计图”，将最大的饼块分离出来，将图表放于本表右侧
4.
销售记录
售货员
性别
销售日期
产品
销售数量
单价
销售金额
李锦玉
女
2012-5-2
彩电
20
3000
王通刚
男
2012-5-3
空调
9
3560
李明杰
男
2012-5-4
电冰箱
12
4000
黄珊珊
女
2012-5-5
微波炉
18
2567
张静同
男
2012-5-6
彩电
17
3000
刘金津
女
2012-5-7
空调
3
3560
郭玲
女
2012-5-8
电冰箱
6
4000
范佳佳
男
2012-5-9
微波炉
9
2567
何玉昆
男
2012-5-10
彩电
12
3000
杨进才
男
2012-5-11
空调
15
2567
销售数量和销售金额统计表
产品
销售数量
销售金额
彩电
空调
电冰箱
微波炉
售货员
销售金额
黄珊珊
郭玲
杨进才
（1）在G3：
G12区域计算出金额，结果采用默认格式。
（2）利用SUMIF函数在J3：
J6，K3：
K6区域分别计算出各种商品的销售数量合计、销售金额合计，结果采用默认格式。
（3）利用VLOOKUP函数，分别在K10/K11/K12单元格显示出黄珊珊、郭玲、杨进才的销售金额，结果保留两位小数，采用默认格式。
5.DGET函数
DGET（database,field,criteria）
数据库函数的应用
客户名称
应收账款金额
北京轮胎厂
20000
天津锅炉厂
58900
天津一汽
67800
匕海大众
23456
东方汽车厂
34567
北京吉普厂
98764
松花江汽车厂
23478
吉利汽车厂
98675
客户名称
东方汽车厂
查找客户所欠款34567
6.
购买灯泡汇总表
产品
瓦数
寿命（小时）
商标
单价
每盒数量
采购盒数
采购金额
白炽灯
200
3000
上海
￥4.50
6
3
氖管
100
2000
上海
￥2.00
15
2
日光灯
60
未知
北京
￥2.50
10
3
其他
10
8000
北京
￥0.80
25
6
白炽灯
80
1000
上海
￥1.80
40
3
日光灯
100
未知
上海
￥2.25
10
4
日光灯
200
3000
上海
￥2.50
15
1
其他
25
未知
北京
￥1.00
10
3
白炽灯
200
3000
北京
￥5.00
8
2
氖管
100
2000
北京
￥1.80
20
8
白炽灯
100
未知
北京
￥2.30
10
5
白炽灯
10
800
上海
￥0.80
25
2
白炽灯
60
1000
北京
￥1.20
25
1
白炽灯
80
1000
北京
￥1.50
30
2
白炽灯
100
2000
上海
￥2.50
10
6
白炽灯
40
1000
上海
￥1.00
20
7
采购金额合计
表2符合条件的表格内容
升级会员 