Excel复习题.doc

实验一Sheet1表中是“2011级学生成绩表”，实验要求如下：
（1）练习添加记录，并用公式和函数求出每位同学的总成绩和平均分。
（2）将工作表重新命名为“学生成绩表”。
（3）将“2011级学生考试成绩表”这一标题设置格式如下：标题为黑体14号字，居中显示，颜色为红色。表格中的文字型数据居中显示。
（4）将“学生成绩表”调整到适当的列宽和行高。
（5）将“学生成绩表”复制到同一工作簿的另一工作表中，命名为“学生成绩表2”。
（6）显示“学生成绩表”平均分大于等于75且小于80的记录，然后恢复原状。
（7）在“2011级学生成绩表”数据区中使用自动套用格式。
（8）在“学生成绩表2”的最后添加一行，利用公式计算出各科成绩和全班同学总成绩的平均分。
（9）将“学生成绩表2”的标题设为楷体，22号字，水平居中显示，并且将标题单元格所在的行高设为24。
（10）在“学生成绩表2”中只显示姓王的成绩记录。
（11）为“成绩表”工作簿设置文件打开密码123456。
（12）在“学生成绩表2”中，根据每位同学的总成绩建立柱形图，并在该图中添加数据标志。
（13）为“学生成绩表”设置页边距．页眉和页脚，并练习设置打印区域．设置打印标题及其他打印选项，最后进行打印预览。
实验一Sheet2实验要求如下：
（1）输入上述职工工资表（除性别列外），将工作表命名为“工资表并计算应发工资=基本工资+职务工资+奖金。
（2）利用数据的有效性，在“性别”列中只允许用户选择男或女输入每个人的性别。
（3）公积金扣款计算公式为当应发工资小于1300元的扣款为应发工资的2%，否则为5%（用IF函数），格式保留两位小数。
（4）计算实发工资＝应发工资－公积金扣款－其他扣款。
（5）在现有表格的下一行计算基本工资、职务工资、奖金、应发工资、公积金扣款、其他扣款和实发工资的总额。
（6）将该表标题设定为“职工工资表”，合并居中，“姓名”、“性别”列水平垂直均分散对齐。
（7）给表格加边框，外粗内细，外红内黑。
（8）将表格内每行的高度设置为18（24像素），“编号”列宽度设为4.5（41像素）。
（9）根据（5）计算的各工资项目总额，分别计算基本工资总额、职务工资总额和奖金总额占应发工资总额的比例，建立可分离的饼状图，标明具体数值。
（10）按科室分类汇总实发工资、应发工资。
（11）将“职工工资表”设置密码保护，整个表格的“实发工资”列以及表外的各行各列不允许鼠标到达，表中其它各列用户可以输入数据。
（12）将“职工工资表”中的首行冻结起来，使得向下按垂直滚动条时，首行保持不动。
（13）再添加若干行的数据，进行页面设置，使得浏览（或打印）时每页都显示首行标题，页脚显示页码。
实验一Sheet3在Excel默认状态下建立工作表如左表，按照如下步骤操作，请写出相应的结果。
（1）Excel表中，选中A1单元格，按下Ctrl键的同时拖动A1单元格右下角的填充柄到A6，看看A2、A3被填充的数据分别为多少？
（2）选择B1:B2单元格区域，拖动B2单元格右下角的填充柄到B6 ，则B3、B4被填充的数据分别为多少？
（3）选中C3单元格， 拖动C3单元格右下角的填充柄到C4， 则C4被填充的数据为多少？
（4）在D1单元格中输入公式“=$A1+C$2”，然后按回车键，并将公式复制到D2、D3单元格中，则D1、D2、B3单元格中的数据分别为多少？
（5）选中C5单元格，单击工具栏上的∑，然后按回车，则该单元格的值为多少？

（6）现将A1:B2单元格区域命名为“求和”，A7单元格有公式“=SUM（求和）”，此时A7单元格的值为多少？若重新将“求和”指定为B1:C2单元格区域的名字，则A7单元格的值为？
思考：1、绝对引用、相对引用和混合引用在何种情况下使用？
实验二1．在空单元格中分别输入以下函数，回车后看其结果
（1）＝round(3.45673,2)（2）=round(3.4657,0)（3）=round(5673.45673,-2)（4）=int(85.45)（5）=int(-41.48)（6）=int(54.98)（7）=today()（8） =now() （9）=left(“abcdefg”,4) （10）=left(“上机指导”,2)（11）=right(“abcdefg”,4)（12）=right(“上机指导”,2)
2. 在表2-1中，计算：
（1）计算每人平均分和单科平均分（2）计算参加考试的学生总人数。（3）统计出专业成绩在80分以上的人数。（4）计算一班高数成绩的和。（5）计算二班所有同学的平均分。
思考：（1）将学号为06082101学生的“平均分”用公式“＝AVERAGE（C2：E2）”换成“＝AVERAGE(C$2:E$2)”，再复制到以下行的其它单元格时，其他学生的平均值还对吗？换成“＝AVERAGE($C2:$E2)”时，复制到该列以下行的其他单元格，结果还对吗？换成“AVERAGE($C$2:$E$2)”还对吗？为什么？（2）二班所有同学的平均分你是如何计算的？可以直接用函数吗？
3、如表2-2所示，在商品销售明细表中，根据要求求和。
（1）根据B列销售金额求和，要求对销售金额大于2000的数值进行求和。（2）根据商品名称求和，要求对商品名称为A1的商品相对应的销售金额进行求和。（3）求商品名称包含“A”的销售金额之和（4）商品名称第四五个字符为“A2”且字符总长度为6个字符的的销售金额之和
4、如表所示的员工工资表，根据要求计数（1）统计实发工资大于2500的人数（2）统计财务部的人数
5、某公司的工资表如表2-4所示，要求：求出职务是“财务”的员工的平均工资；思考：（1）你考虑用什么方法？用DAVERAGE函数可以吗？
实验四1．现假设要购买一个商铺，投资成本为400000元，并且购买后第一年没有收益，以后5年的收益分别为85000元、97000元、120000元、125000元和140000元。现假设当年的利率为5%，请判断该项投资值不值得。
2．假设某用户开一家公司投资额为 1500000元，预计在今后5年内的收益分别为：400000元、4500000元、5800000元、660000元与710000元，现计算投资3年后和5年后的内部收益率是多少？
3．假设某用户花250000元买了一辆车，其使用年限为8年，残值为10000元，现用直线折旧法进行折旧，请求出每月的折旧额是多少？（直线折旧法用SLN函数）
4．假设某工厂在今年的8月，以2000000元买了一部生产用的机器，使用年限为6年，估计残值是300000元，用单倍余额递减法计算该机器的折旧值。（单倍余额递减法分期算期限）
5．某公司花2000000元购入一台机器，运输费及安装费共500000元，这台机器的使用年限为6年，估计残值为250000元，用双倍余额递减法计算该机器每年计提的折旧金额。（最后两期用直线法折旧）
6．某公司购入一台机器，资产原值为1500万元，预计残值为100万元，这台机器的使用年限为10年，请用VDB函数分别求出这台机器第一天的折旧值．第一年的折旧值以及第6个月与18个月之间的折旧值是多少？
7．某公司花2000000元购入一台机器，运输费及安装费共500000元，这台机器的使用年限为6年，估计残值为250000元，用年数总和法计算该机器每年计提的折旧金额。
8.函数F(X)=5X+8，利用模拟运算表的行引用和列引用两种方式分别计算X取值为-5、-4、-3、-2、-1、0、1、2、3、4、5时的F(X)的值。（两种方法的引用，练习！）
9.利用模拟运算表计算在利率为5%、5.5%、6%…15%，期限为10年，投资额分别为10万、10.5万…20万元的资金未来值。
10. 某公司现有甲、乙、丙三个投资项目可供选择，这些项目的初始（第0年）投资额与第1年继续投入的资金额以及它们在第 1 、2两年的现金收入分别如下表所示：
三个项目在第2年以后将不再获得收入(即它们的有效期都等于两年）。试在公司资本成本率等于15%的条件下确定三个投资项目中的最优者，并计算此时各项目的内含报酬率。此外，如果公司贴现率有可能在5％-40％（间距5%）范围内变化的话，试研究在此变化过程中最优投资项目的可能变化，请用图形表示NPV随着贴现率的变化曲线。（再做）
11.某企业拟进行扩建，面临着3种选择：（1）一次较大扩建，使未来10年产量增加一倍；（2）先进行较小扩建，产量增加40%，5年后第2次扩建，使产量达到现在的两倍；（3）进行小扩建后不再扩建；这3个方案有关数据如表4-2所示。
3种扩建方案的有效期为10年，10年后项目的扩建投资均有残值，公司使用的贴现率为9%，残值率为10%。试确定哪一种方案是最优方案。同时，绘制图形来说明但贴现率在2%-10%，残值率在5%-15%（间距均为1%）之间变化时最优方案选择的变化。(问婷婷)

实验五1．某电信公司需用大量电缆，年需求量大约为1000吨，而这种电缆的年单位储存费用为750元，一次订购的订货费用受市场的影响可能为30、40、50或60元不等，零担运输每吨25元，整车运输每吨24元，整车运输起始吨位是15吨。要求建立订货量决策模型，以帮助电信公司决策：究竟是采用整车运输的起始订货量订货，还是选择按经济订货量订货。（未作）
2．“猛牛”奶制品厂用牛奶为原料生产A和B两种奶制品。一桶牛奶可以在1号生产线上用12小时加工成3公斤A产品，或者在2号生产线上用8小时加工成4公斤B产品。由于市场对A、B两种奶制品的需求非常旺盛，因此每天所生产的A、B两种奶制品都能全部出售。根据目前的市场情况，销售每公斤A产品可获利24元，销售每公斤B产品可获利16元。“猛牛”奶制品厂每天能得到50桶牛奶的供应，每天工人的总劳动时间为480小时，并且1号生产线每天至多能加工100公斤A产品，2号生产线的加工能力没有限制。请为该厂制定一个生产计划，如何安排A、B两种奶制品的生产，能使每天获利最大。
3．当公司生产多种产品时，管理者需要回答每种产品应该生产多少合适？在多数情况下，由于公司存在资源方面的约束，其生产能力总是受到一定的限制，如机器工时、人工和原材料的限制等。假设，某公司生产两种巧克力：朱古利和朱尔斯，该公司的会计师给出相关的数据如下表：
公司老板要求会计师根据上述数据，建立规划求解模型，解决公司决策，即分别生产多少朱古利和朱尔斯，总边际贡献最大。为该厂制定一个生产计划，如何安排A、B两种奶制品的生产，能使每天获利最大。

4、Nutri-Jenny是一个体重控制中心。它为客户提供各种各样的冷冻主餐。这些主餐受到了严格的营养成分监控，保证客户膳食平衡。一种新的主餐称为“牛腰间肉大餐”。他包括牛腰间肉和肉汁，加上一些豌豆、胡罗卜和面包卷。Nutri-Jenny需要确定主餐中各种配料的数量以满足营养需求，同时使成本尽可每一种配料的营养成分和成本如下表所示：
主餐的营养要求如下：（1）必须含有280到320之间的卡路里；（2）脂肪所含卡路里不能超过总卡路里含量的30%；（3）必须至少含有600国际单位的维生素A、10毫克的维生素C、30克的蛋白质。而且由于实际原因，至少必须包含2盎司的牛腰间肉，每盎司牛腰间肉至少配有半盎司的肉汁。要求：建立线性规划模型并求解。
5人体每天需要一定量的维生素，现在可以提供维生素的有西红柿、木瓜、胡罗卜和香蕉四种水果。其中，西红柿0.5元/个、木瓜0.8元/个、胡萝卜0.4元/个、香蕉0.6元/个。假设某运动员每天需要的最少维生素量如下表所示。（单位：毫克）
要求：在满足运动员各种维生素摄取量的条件下，建立Excel模型，求花费最小的饮食安排。
实验六1．假设你要购买一项保险，该保险可以在今后20年内每月末汇报500元。此项保险的购买成本为60000元，假定投资回报率为8%，请问是否值得购买？
2．假设需要为一年后的某个项目预筹资金，现在讲1000元以年利率6%，按月计息存入银行账户，并在以后12个月内每月末在存入100元，则一年后银行存款账户余额是多少？
3．某客户欲购买一套价值50万元的房子，首付20万元，余下30万元向银行申请商业贷款，期限10年，年利率5%，试采用等额本息还款和等本金还款两种方式计算每月的还款额。（提示：等本金还款利息可用ISPMT函数）
4．现取得30万元贷款，列表计算利率为5%、6%…15%，期限分别为1、2、3、4、5…10年的到期还款额。
5.某企业需要一台价值100万人民币的设备，使用6年。如果要租赁该设备，年租金为24.32万元人民币，每年年末支付一次，残值归租赁公司所有；如果要购买该设备，需要向银行贷款100万元，银行利率为8.59%，偿还方式为每年年末等额偿还。该企业采用年限平均法计提折旧，6年后残值为15万元，所得税税率为25%，现金贴现率为10%。计算年租金为24.32万元时的内含利息率，并设计制作模型，判断是贷款购买还是租赁更合适？内含利息率在1%-15%之间变动时，年租金也会发生变化，判断是贷款购买还是租赁更合适？（未作）
实验七1．利用给定的“基本数据”，对基本数据的金额按“产品名称”进行分类汇总；并按客户和产品名称进行排序；（先排序再汇总）
2．根据“基本数据”，使用Excel数据透视表功能制作行为客户、列为产品名称的销售额数据透视表。
3. “阿曼德比萨”是一个制作和外卖意大利比萨的餐饮连锁店，其主要客户群是在校大学生。为了研究各店铺销售额与店铺附近地区大学生人数之间的关系，随机抽取了10个分店的样本，得到数据如表所示。试根据这些数据建立回归模型。并根据模型进一步预测一个区内大学生人数为1.8万的店铺的季度销售额。（同时出两个结果需按Ctrl+Shift+Enter）
4斯拜尔曼公司（Spellman）过去10个月的销售量如下表所示：由于销售量的稳定增长，管理层决定使用线性回归方程来预测将来几个月的销售量，以月份为自变量，销售量为因变量。要求：（1）列出线性回归方程；（2）利用该方程预测11、12月份的销售量（slope求斜率，intercept求截距，linest求双变量）