Excel第1-10套简洁答案

1、【第1套】 VLOOKUP和SUMPRODUCT函数 计算机图书销售公司，市场部助理，主要工作职责；销售数据报表（"Excel.xlsx" 文件）：2. 根据图书编号，请在"订单明细表"工作表的"图书名称"列中，使用VLOOKUP函数完成图书名称的自动填充。"2.解题步骤：在"订单明细表"工作表的E3单元格中输入：=VLOOKUP(D3，编号对照!$A$3:$C$19,2,FALSE)，按"Enter"键完成。3. 根据图书编号，请在"订单明细表"工作表的"

2、;单价"列中，使用VLOOKUP函数完成图书单价的自动填充。3.解题步骤：在"订单明细表"工作表的F3单元格中输入：=VLOOKUP(D3,编号对照!$A$3:$C$19,3,FALSE)，按"Enter"键完成。6. 根据"订单明细表"工作表中的销售数据，统计MS Office高级应用图书在2012年的总销售额，并将其填写在"统计报告"工作表的B4单元格中。 6.解题步骤1：在"订单明细表"工作表中，单击"日期"单元格的下拉按钮，选择"降序"后

3、单击"确定"按钮。解题步骤2：切换至"统计报告"工作表，在B4单元格中输入：=SUMPRODUCT(1*(订单明细表!E3:E262="MS Office高级应用"),订单明细表!H3:H262)，按"Enter"键确认。7. 根据"订单明细表"工作表中的销售数据，统计隆华书店在2011年第3季度的总销售额，并将其填写在"统计报告"工作表的B5单元格中。7.解题步骤：在"统计报告"工作表的B5单元格中输入：=SUMPRODUCT(1*(订单明细表!C350

4、:C461="隆华书店"),订单明细表!H350:H461)，按"Enter"键确认。 8. 根据"订单明细表"工作表中的销售数据，统计隆华书店在2011年的每月平均销售额（保留2位小数），并将其填写在"统计报告"工作表的B6单元格中。8.解题步骤：在"统计报告"工作表的B6单元格中输入=SUMPRODUCT(1*(订单明细表!C263:C636="隆华书店"),订单明细表!H263:H636)/12，按"Enter"键确认，然后设置该单元格格式保留2位小

5、数。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(

6、MID(A2,3,2),"01","02","03","1班","2班","3班")，Enter6. 通过分类汇总功能求出每个班各科的平均成绩，并将每组结果分页显示。6.解题步骤1：首先对班级按升序进行排序，选中C2：C19，单击【数据】选项卡下【排序和筛选】组中的"升序"按钮，弹出"排序提醒"对话框，单击"扩展选定区域"单选按钮。单击"排序"按钮后即可完成设置。步骤2：选中D20单元格，单击【数

7、据】选项卡下【分级显示】组中的"分类汇总"按钮，弹出"分类汇总"对话框，单击"分类字段"组中的下拉按钮，选择"班级"选项，单击"汇总方式"组中的下拉按钮，选择"平均值"选项，在"选定汇总项"组中勾选"语文"、"数学"、"英语"、"生物"、"地理"、"历史"、"政治"复选框。最后勾选"每组数据分页"复

8、选框。步骤3：单击"确定"按钮。【第3套】RANK()函数 某公司拟对其产品季度销售情况进行统计，文件名(Excel.xlsx)保存工作簿。 （2） 在 "产品销售汇总图表"内,计算"一二季度销售总量"和"一二季度销售总额"列内容，数值型，保留小数点后0位；在不改变原有数据顺序的情况下，按一二季度销售总额给出销售额排名。2.解题步骤：在"产品销售汇总图表"的E2单元格中输入:=RANK(D2,$D$2:$D$21,0)，后按Enter（3）选择"产品销售汇总图表"内A1：E2

9、1 单元格区域内容，建立数据透视表，行标签为产品型号，列标签为产品类别代码，求和计算一二季度销售额的总计，将表置于现工作表G1为起点的单元格区域内。（3）解题步骤1：在"产品销售汇总图表"中，单击【插入】选项卡下【表格】组中的【数据透视表】下拉按钮，从弹出的下拉列表中选择"数据透视表"，弹出"创建数据透视表"对话框，设置"表/区域"为"产品销售汇总图表!$A$1:$E$21"，选择放置数据透视表的位置为现有工作表，"位置"为"产品销售汇总图表!$G$1"，

10、单击"确定"按钮。步骤2：在"数据透视字段列表"任务窗格中拖动"产品型号"到行标签，拖动"产品类别代码"到列标签，拖动"一二季度销售总额"到数值。步骤3：单击Excel左上角的"保存"按钮，保存文件Excel.xlsx。【第4套】 第五次、第六次人口普查数据的统计分析：（2） 浏览网页"第五次全国人口普查公报.htm"，将其中的"2000年第五次全国人口普查主要数据"表格导入到工作表"第五次普查数据"中；浏览网页&q

11、uot;第六次全国人口普查公报.htm"，将其中的"2010年第六次全国人口普查主要数据"表格导入到工作表"第六次普查数据"中（要求均从A1单元格开始导入，不得对两个工作表中的数据进行排序）。步骤1：双击打开网页"第五次全国人口普查公报.htm"，在工作表"第五次普查数据"中选中A1，单击【数据】选项卡下【获取外部数据】组中的"自网站"按钮，弹出"新建Web查询"对话框，在"地址"文本框中输入网页"第五次全国人口普查公报.htm&quo

12、t;的地址，单击右侧的"转到"按钮。单击要选择的表旁边的带方框的黑色箭头，使黑色箭头变成对号，然后单击"导入"按钮。之后会弹出"导入数据"对话框，选择"数据的放置位置"为"现有工作表"，在文本框中输入"=$A$1"，单击"确定"按钮。【第5套】IF、WEEKDAY、LEFT 、VLOOKUP和SUMPRODUCT函数 财务部助理小王，向主管汇报，2013年度公司差旅报销情况，文档为EXCEL.XLSX。（1） 日期为"2013年1月20日&quo

13、t;的单元格显示为 "2013年1月20日 星期日"；解题步骤：设置单元格格式，切换至"数字"选项卡，在"分类"列表框中选择"自定义"，在右侧的"示例"组中"类型"列表框中输入 yyyy"年"m"月"d"日" aaaa ,单击确定即可。（2） 如果"日期"列中的日期为星期六或星期日，则在"是否加班"列的单元格中显示"是"，否则显示"否"（

14、必须使用公式）。解题步骤：在"费用报销管理"工作表的H3单元格中输入：=IF(WEEKDAY(A3,2)>5,"是","否")，表示在星期六或星期日下显示"是"，否则显示"否"，按Enter键确认（3） 使用公式统计每个活动地点所在的省份或直辖市，并将其填写在"地区"列所对应的单元格中，例如"北京市"、"浙江省"。解题步骤：在"费用报销管理"工作表的D3单元格中输入：=LEFT(C3,3)， 表示取当前文字左侧

15、的前三个字符，按Enter键确认；（4） 依据"费用类别编号"列内容，使用VLOOKUP函数，生成"费用类别"列内容。对照关系参考"费用类别"工作表。解题步骤：在"费用报销管理"工作表的F3单元格中输入：=VLOOKUP(E3,费用类别!$A$3:$B$12,2,FALSE)，按Enter后完成。（5） 在"差旅成本分析报告"工作表B3单元格中，统计2013年第二季度发生在北京市的差旅费用总金额。解题步骤：选中A列单元格，在【编辑】选项组中，单击"排序和筛选"下拉菜单中的&q

16、uot;升序"按钮，在弹出的"排序提醒"对话框中单击"排序"按钮。在"差旅成本分析报告"工作表的B3单元格中输入：=SUMPRODUCT(1*(费用报销管理!D74:D340="北京市"),费用报销管理!G74:G340)，按Enter。（6） 在"差旅成本分析报告"工作表B4单元格中，统计2013年员工钱顺卓报销的火车票费用总额。解题步骤：在"差旅成本分析报告"工作表的B4单元格中输入：=SUMPRODUCT(1*(费用报销管理!B3:B401="钱顺卓

17、"),1*(费用报销管理!F3:F401="火车票"),费用报销管理!G3:G401，按Enter。（7） 在"差旅成本分析报告"工作表B5单元格中，统计2013年差旅费用中，飞机票费用占所有报销费用的比例，并保留2位小数。解题步骤：在"差旅成本分析报告"工作表的B5单元格中输入：=SUMPRODUCT(1*(费用报销管理!F3:F401="飞机票"),费用报销管理!G3:G401)/SUM(费用报销管理!G3:G401)，按Enter，并设置数字格式，保留两位小数。（8） 在"差旅成本分析报告

18、"工作表B6单元格中，统计2013年发生在周末（星期六和星期日）的通讯补助总金额。解题步骤：在"差旅成本分析报告"工作表的B6单元格中输入：=SUMPRODUCT(费用报销管理!H3:H401="是")*(费用报销管理!F3:F401="通讯补助"),费用报销管理!G3:G401)，按Enter。【第6套】VLOOKUP函数文涵,大地公司销售部助理，负责对公司的销售情况进行统计分析；打开”计算机设备全年销量统计表.xlsx"。4. 将工作表"平均单价"中的区域B3:C7定义名称为"商品

19、均价"。运用公式计算工作表"销售情况"中F列的销售额，要求在公式中通过VLOOKUP函数自动在工作表"平均单价"中查找相关商品的单价，并在公式中引用所定义的名称"商品均价"。解题步骤1：在“平均单价”工作表中选中B3：C7区域，单击鼠标右键，在弹出的下拉列表中选择"定义名称"命令，打开"新建名称"对话框。在"名称"中输入"商品均价"后单击“确定”按钮即可。步骤2：根据销售量以及平均单价计算销售额。在F4单元格中输入：=VLOOKUP（D4，商品均

20、价，2，0）*E4，然后按Enter。5. 为工作表"销售情况"中的销售数据创建一个数据透视表7. 保存"计算机设备全年销量统计表.xlsx"文件。【第7套】IF、ROUND和SUMPRODUCT函数 东方公司会计，记账，用Excel编制了2014年3月员工工资表"Excel.xlsx"。（5） 参考考生文件夹下的"工资薪金所得税率.xlsx"，利用IF函数计算 "应交个人所得税"列。 (提示：应交个人所得税=应纳税所得额*对应税率-对应速算扣除数)解题步骤：在"2014年3月&quo

21、t;工作表L3单元格中输入：=ROUND(IF(K3<=1500,K3*3/100,IF(K3<=4500,K3*10/100-105,IF(K3<=9000,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） 在"分类汇总"工作表中通过分类汇总功能求出各部门"应付工资合计"、"实发工资"的和

22、，每组数据不分页。解题步骤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="

23、;行政"),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单元格

24、输入：=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级法律”工作表中，利用公式、根据学生的学号、将其班级的名称填入“班级”列，规则为：学号的第三位为专业代码、第

25、四位代表班级序号，即01为“法律一班”，02为“法律二班”，03为“法律三班”，04为“法律四班”。解题步骤：选择A3单元格，在该单元格内输入="法律"&TEXT(MID(B3,3,2),"DBNum1")&"班"，按Enter 5. 根据“2012级法律”工作表，创建一个数据透视表 【第9套】VLOOKUP、MONTH、SUMIFS和SUBTOTAL 销售部助理，2012年和2013年的图书产品销售情况进行统计分析，制订新一年的销售计划和工作任务。在文档“EXCEL.XLSX”中完成并保存。 1. 在“销售订单”工作

26、表的“图书编号”列中，使用VLOOKUP函数填充所对应“图书名称”的“图书编号”，“图书名称”和“图书编号”的对照关系请参考“图书编目表”工作表。解题步骤：在“销售订单”工作表的E3单元格中输入=VLOOKUP(D3,图书编目表!$A$2:$B$9,2,FALSE)，按Enter3. 在“2013年图书销售分析”工作表中，统计2013年各类图书在每月的销售量，并将统计结果填充在所对应的单元格中。为该表添加汇总行，在汇总行单元格中分别计算每月图书的总销量。解题步骤1：在“销售订单”工作表中选中“书店名称”单元格，右键单击在快捷菜单中选择“插入”级联菜单中的“在左侧插入表列”命令，插入一列单元格，

27、然后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,销售订

28、单!$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单元格开始导入到工作表“初三学生档案”中，注意不得改变原

29、始数据的排列顺序。将第1列数据从左到右依次分成“学号”和“姓名”两列显示。最后创建一个名为“档案”、包含数据区域A1:G56、包含标题的表，同时删除外部链接。解题步骤1：选中A1单元格，单击【数据】选项卡下【获取外部数据】组中的“自文本”按钮，弹出“导入文本文件”对话框，在该对话框中选择考生文件夹下的“学生档案.txt”选项，然后单击“导入”按钮。步骤2：在弹出的对话框中选择“分隔符号”单选按钮，将“文件原始格式”设置为“54936：简体中文（GB18030）”。单击“下一步”按钮，只勾选“分隔符”列表中的“Tab键”复选项。然后单击“下一步”按钮，选中“身份证号码”列，然后点击“文本”单选按

30、钮，单击“完成”按钮，在弹出的对话框中保持默认，单击“确定”按钮。步骤3：选中B列单元格，单击鼠标右键，在弹出的快捷菜单中选择“插入”选项。然后选中A1单元格，将光标置于“学号”和“名字”之间，按3次空格键，然后选中A列单元格，单击【数据工具】组中的“分列”按钮，在弹出的对话框中选择“固定宽度”单选按钮，单击“下一步”按钮，然后建立分列线。单击“下一步”按钮，保持默认设置，单击“完成”按钮。步骤4：选中A1:G56单元格，单击【开始】选项卡下【样式】组中的“套用表格格式”下拉按钮，在弹出的下拉列表中选择“表样式中等深浅2”。步骤5：在弹出的对话框中勾选“表包含标题”复选框，单击“确定”按钮，然

31、后再在弹出的对话框中选择“是”按钮。在【设计】选项卡下【属性】组中将“表名称”设置为档案。3. 在工作表“初三学生档案”中，利用公式及函数依次输入每个学生的性别“男”或“女”、出生日期“××××年××月××日”和年龄。其中：身份证号的倒数第2位用于判断性别，奇数为男性，偶数为女性；身份证号的第714位代表出生年月日；年龄需要按周岁计算，满1年才计1岁。最后适当调整工作表的行高和列宽、对齐方式等，以方便阅读。解题步骤1：选中D2单元格，在该单元格内输入函数“=IF(MOD(MID(C2,17,1),2)=1,&quo

32、t;男","女")，按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. 参考工作表“初三学生档

33、案”，在工作表“语文”中输入与学号对应的“姓名”；按照平时、期中、期末成绩各占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单元格，在该单元格中输入函

34、数:=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. 将工作表“语文”的格式全部应用到其

35、他科目工作表中，包括行高（各行行高均为22默认单位）和列宽（各列列宽均为14默认单位）。并按上述4. 中的要求依次输入或统计其他科目的“姓名”、“学期成绩”、“班级名次”和“期末总评”。解题步骤:在“英语”工作表中的H2单元格中输入公式：=IF(F2>=90,"优秀",IF(F2>=75,"良好",IF(F2>=60,"及格",IF(F2>60,"及格","不及格")，按Enter。 6. 分别将各科的“学期成绩”引入到工作表“期末总成绩”的相应列中， 在工作表“期末总成绩”中依次引入姓名、计算各科的