Excel第1-10套简洁答案

[第1套]VLOOKUP和SUMPRODUCT函数
计算机图书销售公司,市场部助理,主要工作职责；销售数据报表〔"Excel.xlsx" 文件〕：
2. 根据图书编号,请在"订单明细表"工作表的"图书名称"列中,使用VLOOKUP函数完成图书名称的自动填充."
2.解题步骤：在"订单明细表"工作表的E3单元格中输入：
=VLOOKUP<D3,编号对照!$A$3:$C$19,2,FALSE>,按"Enter"键完成.
3. 根据图书编号,请在"订单明细表"工作表的"单价"列中,使用VLOOKUP函数完成图书单价的自动填充.
3.解题步骤：在"订单明细表"工作表的F3单元格中输入：
=VLOOKUP<D3,编号对照!$A$3:$C$19,3,FALSE>,按"Enter"键完成.
6. 根据"订单明细表"工作表中的销售数据,统计《MS Office高级应用》图书在2012年的总销售额,并将其填写在"统计报告"工作表的B4单元格中.
6.解题步骤1：在"订单明细表"工作表中,单击"日期"单元格的下拉按钮,选择"降序"后单击"确定"按钮.
解题步骤2：切换至"统计报告"工作表,在B4单元格中输入：
=SUMPRODUCT<1*<订单明细表!E3:E262="《MS Office高级应用》">,订单明细表!H3:H262>,按"Enter"键确认.
7. 根据"订单明细表"工作表中的销售数据,统计隆华书店在2011年第3季度的总销售额,并将其填写在"统计报告"工作表的B5单元格中.
7.解题步骤：在"统计报告"工作表的B5单元格中输入：
=SUMPRODUCT<1*<订单明细表!C350:C461="隆华书店">,订单明细表!H350:H461>,按"Enter"键确认.
8. 根据"订单明细表"工作表中的销售数据,统计隆华书店在2011年的每月平均销售额〔保留2位小数〕,并将其填写在"统计报告"工作表的B6单元格中.
8.解题步骤：在"统计报告"工作表的B6单元格中输入
=SUMPRODUCT<1*<订单明细表!C263:C636="隆华书店">,订单明细表!H263:H636>/12,按"Enter"键确认,然后设置该单元格格式保留2位小数.
9. 保存"Excel.xlsx"文件.
[第2套]LOOKUP和MID函数
地处偏远地区学校,通过 Excel 来管理,初一年级三个班,第一学期期末考试,学生成绩的成绩；文件名为"学生成绩单.xlsx"的Excel工作簿文档.
4. 学号第 3、4 位代表学生所在的班级,例如："120105"代表12级1班5号.请通过函数提取每个学生所在的班级并按下列对应关系填写在"班级"列中：
"学号"的3、4位对应班级
01 1班
02 2班
03 3班
4.解题步骤：在C2单元格中输入
=LOOKUP<MID<A2,3,2>,{"01","02","03"},{"1班","2班","3班"}>,Enter
6. 通过分类汇总功能求出每个班各科的平均成绩,并将每组结果分页显示.
6.解题步骤1：首先对班级按升序进行排序,选中C2：C19,单击[数据]选项卡
下[排序和筛选]组中的"升序"按钮,弹出"排序提醒"对话框,单击"扩展选定区域"单选按钮.单击"排序"按钮后即可完成设置.
步骤2：选中D20单元格,单击[数据]选项卡下[分级显示]组中的"分类汇总"按钮,弹出"分类汇总"对话框,单击"分类字段"组中的下拉按钮,选择"班级"选项,单击"汇总方式"组中的下拉按钮,选择"平均值"选项,在"选定汇总项"组中勾选"语文"、"数学"、"英语"、"生物"、"地理"、"历史"、"政治"复选框.最后勾选"每组数据分页"复选框.
步骤3：单击"确定"按钮.
[第3套]RANK<>函数
某公司拟对其产品季度销售情况进行统计,文件名<Excel.xlsx>保存工作簿.
〔2〕在 "产品销售汇总图表"内,计算"一二季度销售总量"和"一二季度销售总额"列内容,数值型,保留小数点后0位；在不改变原有数据顺序的情况下,按一二季度销售总额给出销售额排名.
2.解题步骤：在"产品销售汇总图表"的E2单元格中输入:
=RANK<D2,$D$2:$D$21,0>,后按Enter
〔3〕选择"产品销售汇总图表"内A1：E21 单元格区域内容,建立数据透视表,行标签为产品型号,列标签为产品类别代码,求和计算一二季度销售额的总计,将表置于现工作表G1为起点的单元格区域内.
〔3〕解题步骤1：在"产品销售汇总图表"中,单击[插入]选项卡下[表格]组中的[数据透视表]下拉按钮,从弹出的下拉列表中选择"数据透视表",弹出"创建数据透视表"对话框,设置"表/区域"为"产品销售汇总图表!$A$1:$E$21",选择放置数据透视表的位置为现有工作表,"位置"为"产品销售汇总图表!$G$1",单击"确定"按钮.
步骤2：在"数据透视字段列表"任务窗格中拖动"产品型号"到行标签,拖动"产品类别代码"到列标签,拖动"一二季度销售总额"到数值.
步骤3：单击Excel左上角的"保存"按钮,保存文件Excel.xlsx.
[第4套]
第五次、第六次人口普查数据的统计分析：
〔2〕浏览网页"第五次全国人口普查公报.htm",将其中的"2000年第五次全国人口普查主要数据"表格导入到工作表"第五次普查数据"中；浏览网页"第六次全国人口普查公报.htm",将其中的"2010年第六次全国人口普查主要数据"表格导入到工作表"第六次普查数据"中〔要求均从A1单元格开始导入,不得对两个工作表中的数据进行排序〕.
步骤1：双击打开网页"第五次全国人口普查公报.htm",在工作表"第五次普查数据"中选中A1,单击[数据]选项卡下[获取外部数据]组中的"自"按钮,弹出"新建Web查询"对话框,在"地址"文本框中输入网页"第五次全国人口普查公
报.htm"的地址,单击右侧的"转到"按钮.单击要选择的表旁边的带方框的黑色箭头,使黑色箭头变成对号,然后单击"导入"按钮.之后会弹出"导入数据"对话框,选择"数据的放置位置"为"现有工作表",在文本框中输入"=$A$1",单击"确定"按钮.
[第5套]IF、WEEKDAY、LEFT、VLOOKUP和SUMPRODUCT函数
财务部助理小王,向主管汇报,2013年度公司差旅报销情况,文档为EXCEL.XLSX.
〔1〕日期为"2013年1月20日"的单元格显示为 "2013年1月20日星期日"；
解题步骤：设置单元格格式,切换至"数字"选项卡,在"分类"列表框中选择"自定义",在右侧的"示例"组中"类型"列表框中输入yyyy"年"m"月"d"日" aaaa,单击确定即可.
〔2〕如果"日期"列中的日期为星期六或星期日,则在"是否加班"列的单元格中显示"是",否则显示"否"〔必须使用公式〕.
解题步骤：在"费用报销管理"工作表的H3单元格中输入：
=IF<WEEKDAY<A3,2>>5,"是","否">,表示在星期六或星期日下显示"是",否则显示"否",按Enter键确认
〔3〕使用公式统计每个活动地点所在的省份或直辖市,并将其填写在"地区"列所对应的单元格中,例如"市"、"##省".
解题步骤：在"费用报销管理"工作表的D3单元格中输入：=LEFT<C3,3>,表示取当前文字左侧的前三个字符,按Enter键确认；
〔4〕依据"费用类别编号"列内容,使用VLOOKUP函数,生成"费用类别"列内容.对照关系参考"费用类别"工作表.
解题步骤：在"费用报销管理"工作表的F3单元格中输入：
=VLOOKUP<E3,费用类别!$A$3:$B$12,2,FALSE>,按Enter后完成.
〔5〕在"差旅成本分析报告"工作表B3单元格中,统计2013年第二季度发生在市的差旅费用总金额.
解题步骤：选中A列单元格,在[编辑]选项组中,单击"排序和筛选"下拉菜单中的"升序"按钮,在弹出的"排序提醒"对话框中单击"排序"按钮.在"差旅成本分析报告"工作表的B3单元格中输入：
=SUMPRODUCT<1*<费用报销管理!D74:D340="市">,费用报销管
理!G74:G340>,按Enter.
〔6〕在"差旅成本分析报告"工作表B4单元格中,统计2013年员工钱顺卓报销的火车票费用总额.
解题步骤：在"差旅成本分析报告"工作表的B4单元格中输入：
=SUMPRODUCT<1*<费用报销管理!B3:B401="钱顺卓">,1*<费用报销管
理!F3:F401="火车票">,费用报销管理!G3:G401,按Enter.
〔7〕在"差旅成本分析报告"工作表B5单元格中,统计2013年差旅费用中,飞机票费用占所有报销费用的比例,并保留2位小数.
解题步骤：在"差旅成本分析报告"工作表的B5单元格中输入：
=SUMPRODUCT<1*<费用报销管理!F3:F401="飞机票">,费用报销管
理!G3:G401>/SUM<费用报销管理!G3:G401>,按Enter,并设置数字格式,保留两
位小数.
〔8〕在"差旅成本分析报告"工作表B6单元格中,统计2013年发生在周末〔星期六和星期日〕的通讯补助总金额.
解题步骤：在"差旅成本分析报告"工作表的B6单元格中输入：
=SUMPRODUCT<<费用报销管理!H3:H401="是">*<费用报销管理!F3:F401="
通讯补助">,费用报销管理!G3:G401>,按Enter.
[第6套]VLOOKUP函数
文涵,大地公司销售部助理,负责对公司的销售情况进行统计分析；打开"计
算机设备全年销量统计表.xlsx".
4. 将工作表"平均单价"中的区域B3:C7定义名称为"商品均价".运用公式计算工作表"销售情况"中F列的销售额,要求在公式中通过VLOOKUP函数自动在工作表"平均单价"中查找相关商品的单价,并在公式中引用所定义的名称"商品均价".
解题步骤1：在"平均单价"工作表中选中B3：C7区域,单击鼠标右键,在弹出的下拉列表中选择"定义名称"命令,打开"新建名称"对话框.在"名称"中输入"商品均价"后单击"确定"按钮即可.
步骤2：根据销售量以与平均单价计算销售额.在F4单元格中输入：
=VLOOKUP〔D4,商品均价,2,0〕*E4,然后按Enter.
5. 为工作表"销售情况"中的销售数据创建一个数据透视表
7. 保存"计算机设备全年销量统计表.xlsx"文件.
[第7套]IF、ROUND和SUMPRODUCT函数
东方公司会计,记账,用Excel编制了2014年3月员工工资表"Excel.xlsx".
〔5〕参考考生文件夹下的"工资薪金所得税率.xlsx",利用IF函数计算 "应交个人所得税"列. <提示：应交个人所得税=应纳税所得额*对应税率-对应速算扣除数>
解题步骤：在"2014年3月"工作表L3单元格中输入：
=ROUND<IF<K3<=1500,K3*3/100,IF<K3<=4500,K3*10/100-105,IF<K3<=900 0,K3*20/100-555,IF<K3<=35000,K3*25%-1005,IF<K3<=5500,K3*30%-2755,IF< K3<=80000,K3*35%-5505,IF<K3>80000,K3*45%-13505>>>>>>>,2>,按Enter 〔8〕在"分类汇总"工作表中通过分类汇总功能求出各部门"应付工资合计"、"实发工资"的和,每组数据不分页.
解题步骤1：在"分类汇总"工作表中数据下方建立小表格.
步骤2：在"分类汇总"工作表K22单元格输入:
=SUMPRODUCT<1*<D3:D17="管理">,I3:I17>,按Enter.
步骤3：在"分类汇总"工作表L22单元格输入：
=SUMPRODUCT<1*<D3:D17="管理">,M3:M17>,按Enter.
步骤4：参照步骤2和步骤3,依次在"分类汇总"工作表的单元格中依次输入：K23单元格输入：=SUMPRODUCT<1*<D3:D17="行政">,I3:I17>,
L23单元格输入：=SUMPRODUCT<1*<D3:D17="行政">,M3:M17>,
K24单元格输入：=SUMPRODUCT<1*<D3:D17="人事">,I3:I17>,
L24单元格输入：=SUMPRODUCT<1*<D3:D17="人事">,M3:M17>,
K25单元格输入：=SUMPRODUCT<1*<D3:D17="研发">,I3:I17>,
L25单元格输入：=SUMPRODUCT<1*<D3:D17="研发">,M3:M17>,
K26单元格输入：=SUMPRODUCT<1*<D3:D17="销售">,I3:I17>,
L26单元格输入：=SUMPRODUCT<1*<D3:D17="销售">,M3:M17>,按Enter. [第8套]RANK函数
法律系,2012级四个法律专业,期末成绩单,文件夹"素材.xlsx"文档.
3. 在"2012级法律"工作表中,"年级排名".
步骤3：选择O3单元格,在该单元格内输入=RANK<M3,M$3:M$102,0>,按Enter 4. 在"2012级法律"工作表中,利用公式、根据学生的学号、将其班级的名称填
入"班级"列,规则为：学号的第三位为专业代码、第四位代表班级序号,即01为"法律一班",02为"法律二班",03为"法律三班",04为"法律四班".
解题步骤：选择A3单元格,在该单元格内输入
="法律"&TEXT<MID<B3,3,2>,"[DBNum1]">&"班",按Enter
5. 根据"2012级法律"工作表,创建一个数据透视表
[第9套]VLOOKUP、MONTH、SUMIFS和SUBTOTAL
销售部助理,2012年和2013年的图书产品销售情况进行统计分析,制订新一年的销售计划和工作任务.在文档"EXCEL.XLSX"中完成并保存.
1. 在"销售订单"工作表的"图书编号"列中,使用VLOOKUP函数填充所对应"图书名称"的"图书编号","图书名称"和"图书编号"的对照关系请参考"图书编目表"工作表.
解题步骤：在"销售订单"工作表的E3单元格中输入
=VLOOKUP<D3,图书编目表!$A$2:$B$9,2,FALSE>,按Enter
3. 在"2013年图书销售分析"工作表中,统计2013年各类图书在每月的销售量,并将统计结果填充在所对应的单元格中.为该表添加汇总行,在汇总行单元格中分别计算每月图书的总销量.
解题步骤1：在"销售订单"工作表中选中"书店名称"单元格,右键单击在快捷菜单中选择"插入"级联菜单中的"在左侧插入表列"命令,插入一列单元格,然后C3单元格中输入
=MONTH<$B$3:$B$678>,按Enter.
步骤2：根据题意要求切换至"2013年图书销售分析"工作表中,选择B4单元格,并输入：
=SUMIFS<销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,A4,销售订单!$C$3:$C$678,1>,按Enter.
步骤3：选择"2013年图书销售分析"工作表中的C4单元格,并输入：
=SUMIFS<销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,A4,销售订单!$C$3:$C$678,2>,按Enter键确定.
选中D4单元格并输入：
=SUMIFS<销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,A4,销售订单!$C$3:$C$678,3>,按Enter键确定.使用同样方法在其它单元格中得出结果.
步骤4：在A12单元格中输入"每月图书总销量"字样,然后选中B12单元格输入：=SUBTOTAL<109,B4:B11>,按Enter键确定.
[第10套]IF、MOD、MID、RANK、 --TEXT、DATEDIF和VLOOKUP函数
期末考试,初三〔14〕班的班主任助理王老师,对学生各科成绩统计分析,制作成绩通知单下发给家长.并按原文件名进行保存：
2. 将以制表符分隔的文本文件"学生档案.txt"自A1单元格开始导入到工作表"初三学生档案"中,注意不得改变原始数据的排列顺序.将第1列数据从左到右依次分成"学号"和"##"两列显示.最后创建一个名为"档案"、包含数据区域A1:G56、包含标题的表,同时删除外部.
解题步骤1：选中A1单元格,单击[数据]选项卡下[获取外部数据]组中的"自文本"按钮,弹出"导入文本文件"对话框,在该对话框中选择考生文件夹下的"学生档案.txt"选项,然后单击"导入"按钮.
步骤2：在弹出的对话框中选择"分隔符号"单选按钮,将"文件原始格式"设置为"54936：简体中文〔GB18030〕".单击"下一步"按钮,只勾选"分隔符"列表中的"Tab键"复选项.然后单击"下一步"按钮,选中"##"列,然后点击"文本"单选按钮,单击"完成"按钮,在弹出的对话框中保持默认,单击"确定"按钮.
步骤3：选中B列单元格,单击鼠标右键,在弹出的快捷菜单中选择"插入"选项.然后选中A1单元格,将光标置于"学号"和"名字"之间,按3次空格键,然后选中A列单元格,单击[数据工具]组中的"分列"按钮,在弹出的对话框中选择"固定宽度"单选按钮,单击"下一步"按钮,然后建立分列线.单击"下一步"按钮,保持默认设置,单击"完成"按钮.
步骤4：选中A1:G56单元格,单击[开始]选项卡下[样式]组中的"套用表格格式"下拉按钮,在弹出的下拉列表中选择"表样式中等深浅2".
步骤5：在弹出的对话框中勾选"表包含标题"复选框,单击"确定"按钮,然后再在弹出的对话框中选择"是"按钮.在[设计]选项卡下[属性]组中将"表名称"设置为档案.
3. 在工作表"初三学生档案"中,利用公式与函数依次输入每个学生的性别"男"或"女"、出生日期"××××年××月××日"和年龄.其中：##号的倒数第2位用于判断性别,奇数为男性,偶数为女性；##号的第7~14位代表出生年月日；年龄需要按周岁计算,满1年才计1岁.最后适当调整工作表的行高和列宽、对齐方式等,以方便阅读.
解题步骤1：选中D2单元格,在该单元格内输入函数"
=IF<MOD<MID<C2,17,1>,2>=1,"男","女">,按Enter.
步骤2：选中E2单元格,在该单元格内输入函数
=--TEXT<MID<C2,7,8>,"0-00-00">,按Enter.然后选择E2：E56单元格,单击鼠标右键,在弹出的快捷菜单中选择"设置单元格格式"选项.切换至"数字"选项卡,将"分类"设置为"日期",然后单击"确定"按钮.
步骤3：选中F2单元格,在该单元格内输入函数"
=DATEDIF<--TEXT<MID<C2,7,8>,"0-00-00">,TODAY<>,"y">,按Enter.
4. 参考工作表"初三学生档案",在工作表"语文"中输入与学号对应的"##"；按照平时、期中、期末成绩各占30%、30%、40%的比例计算每个学生的"学期成绩"并填入相应单元格中；按成绩由高到低的顺序统计每个学生的"学期成绩"排名并按"第n名"的形式填入"班级名次"列中；按照下列条件填写"期末总评"：
语文、数学的学期成绩其他科目的学期成绩期末总评
≥102 ≥90 优秀
≥84 ≥75 良好
≥72 ≥60 与格
〈72 〈60 不合格
解题步骤1：进入到"语文"工作表中,选择B2单元格,在该单元格内输入函数=VLOOKUP<A2,初三学生档案!$A$2:$B$56,2,0>,按Enter.
步骤2：选择F2单元格,在该单元格中输入函数:
=SUM<C2*30%>+<D2*30%>+<E2*40%>,按Enter.
步骤3：选择G2单元格,在该单元格内输入函数:
="第"&RANK<F2,$F$2:$F$45>&"名".
步骤4：选择H2单元格,在该单元格中输入公式:
=IF<F2>=102,"优秀",IF<F2>=84,"良好",IF<F2>=72,"与格",IF<F2>72,"与
格","不与格">>>>,按Enter.
5. 将工作表"语文"的格式全部应用到其他科目工作表中,包括行高〔各行行高均为22默认单位〕和列宽〔各列列宽均为14默认单位〕.并按上述4. 中的要求依次输入或统计其他科目的"##"、"学期成绩"、"班级名次"和"期末总评".
解题步骤:在"英语"工作表中的H2单元格中输入公式：
=IF<F2>=90,"优秀",IF<F2>=75,"良好",IF<F2>=60,"与格",IF<F2>60,"与格","不与格">>>>,按Enter.
6. 分别将各科的"学期成绩"引入到工作表"期末总成绩"的相应列中,
在工作表"期末总成绩"中依次引入##、计算各科的平均分、每个学生的总分,并按成绩由高到底的顺序统计每个学生的总分排名、并以1、2、3……形式标识名次,最后将所有成绩的数字格式设为数值、保留两位小数.
解题步骤1：进入到"期末总成绩"工作表中,选择B3单元格,在该单元格内输入公式
=VLOOKUP<A3,初三学生档案!$A$2:$B$56,2,0>,按Enter键完成操作,然后利用自动填充功能将其填充至B46单元格.
步骤2：选择C3单元格,在该单元格内输入公式
=VLOOKUP<A3,语文!$A$2:$F$45,6,0>,按Enter.
步骤3：选择D3单元格,在该单元格内输入公式
=VLOOKUP<A3,数学!$A$2:$F$45,6,0>,按Enter.
步骤4：使用相同的的方法为其他科目填充平均分.选择J3单元格,在该单元格内输入公式
=SUM<C3:I3>,按Enter.
步骤5：选择A3：K46单元格,单击[开始]选项卡[编辑]组中"排序和筛选"下拉按钮,在弹出的下拉列表中选择"自定义排序"选项,弹出"排序"对话框,在该对话框中将"主要关键字"设置为"总分",将"排序依据"设置为"数值",将"次序"设置为"降序",单击"确定"按钮.
步骤6：在K3单元格内输入数字1,然后按住Ctrl键,利用自动填充功能将其填充至K46单元格.
步骤7：选择C47单元格,在该单元格内输入公式：
=AVERAGE<C3:C46>,按Enter.
7. 在工作表"期末总成绩"中分别用红色〔标准色〕和加粗格式标出各科第一名成绩.同时将前10名的总分成绩用浅蓝色填充.
解题步骤1：选择C3：C46单元格,单击[开始]选项卡下[样式]组中的"条件格式"按钮,在弹出的下拉列表中选择"新建规则"选项,在弹出的对话框中将"选择规则类型"设置为"仅对排名靠前或靠后的数值设置格式",然后将"编辑规则说明"设置为"前"、"1".
步骤2：单击"格式"按钮,在弹出的对话框中将"字形"设置为加粗,将"颜色"设置为标准色中的"红色",单击两次"确定"按钮.按同样的操作方式为其他六科分别用红色和加粗标出各科第一名成绩.
步骤3：选择J3：J12单元格,单击鼠标右键,在弹出的快捷菜单中选择"设置单元格格式"选项,在弹出的对话框中切换至"填充"选项卡,然后单击"浅蓝"颜色块,单击"确定"按钮.
8. 调整工作表"期末总成绩"的页面布局以便打印：纸张方向为横向,缩减打印输出使得所有列只占一个页面宽〔但不得缩小列宽〕,水平居中打印在纸上.
解题步骤1：在[页面边距]选项卡下[页面设置]组中单击对话框启动器按钮,在弹出的对话框中切换至"页边距"选项卡勾选"居中方式"选项组中的"水平"复选框.
步骤2：切换至"页面"选项卡,将"方向"设置为横向.选择"缩放"选项组下的"调整为"单选按钮,将其设置为1页宽1页高,单击"确定"按钮.。