试题及解析Excel篇.doc

3服装采购表1、 使用VLOOKUP函数，对Sheet1中的商品单价进行自动填充。要求：根据“价格表”中的商品单价，利用VLOOKUP函数，将其单价自动填充到采购表中的“单价”列中。提示：D11=VLOOKUP(A11,$F$2:$G$4,2,0)，然后填充柄填充。2、 使用逻辑函数，对Sheet1中的商品折扣率进行自动填充。要求：根据“折扣表”中的商品折扣率，利用相应的函数，将其折扣率自动填充到采购表中的“折扣”列中。提示：E11=IF(B11100,0%,IF(B11200,6%,IF(B11300,8%,10%)，然后填充柄填充。3、 利用公式，计算Sheet1中的“合计金额”。要求：根据“采购数量”、“单价”和“折扣”，计算采购的“合计金额”。计算公式：单价*采购数量*（1-折扣率）提示：F11=B11*D11*(1-E11)，然后填充柄填充。4、 使用SUMIF函数，统计各种商品的采购总量和采购总金额，将结果保存在Sheet1中的“统计表”当中。提示：J12=SUMIF($A$11:$A$43,I12,B11:B43)，然后填充柄填充。 K12=SUMIF($A$11:$A$43,I12,$F$11:$F$43)，然后填充柄填充。5停车收费表：1、 使用HLOOKUP函数，对Sheet1中的停车单价进行自动填充。 要求：根据Sheet1中的“停车价目表”价格，利用HLOOKUP函数对“停车情况记录表”中的“单价”列根据不同的车型进行自动填充。提示：C9=HLOOKUP(B9,$A$2:$C$3,2,FALSE) ，然后填充柄填充。2、 在Sheet1中，利用时间函数计算汽车在停车库中的停放时间，要求：a. 公式计算方法为“出库时间入库时间”b. 格式为：“小时：分钟：秒”（例如：一小时十五分十二秒在停放时间中的表示为：“1：15：12”）提示：F9=E9-D9，然后填充柄填充。3、 使用函数公式，计算停车费用，要求： 根据停放时间的长短计算停车费用，将计算结果填入到“应付金额”列中。注意：a. 停车按小时收费，对于不满一个小时的按照一个小时收费；b. 对于超过整点小时数十五分钟的多积累一个小时。（例如1小时23分，将以2小时计费）提示：G9=(IF(HOUR(F9)1,1,IF(MINUTE(F9)40) J3=MAX(G9:G39)5对sheet2，进行高级筛选，要求：a.筛选条件为：“车型”-小汽车，“应付金额”=30；b.将结果保存在sheet2中。提示：6根据sheet1，创建一个数据透视图Chart1，要求：a.显示各种车型所收费用的汇总；b.行区域设置为“车型”；c.计数项为“应付金额”；d.将对应的数据透视表保存在sheet3中。提示：选择“数据透视表和数据透视图”，在步骤3中，如图设置，然后点击“”，将“车型”拖入行，“应付金额”拖入数据中即可。14公司员工人事信息表1使用大小写转换函数，根据Sheet1中“公司员工人事信息表”的“编号”列，对“新编号”列进行填充。要求：把编号中的小写字母改成大写字母，并根据结果保存在“新编号”列中。例如，“a001”更改后为“A001”。提示：使用PROPER()函数，将第一个字母转换成大写，其他字符转换为小写。新编号B3 =PROPER(A3)2使用文本函数和时间函数，根据Sheet1中“公司员工人事住处表”的“身份证号码”列，计算用户的年龄，并保存在“年龄”列中。注意：（a）身份证的第7位第10位表示出生年份； （b）计算方法：年龄=当前年份-出生年份。其中“当前年份”使用时间函数计算。提示：使用year()、today()和mid()函数。分析：当前年份 =YEAR(TODAY()使用mid()函数从身份证号码中提取出生年份 =MID(G3,7,4)将两者相减即可。年龄F3 =YEAR(TODAY()-MID(G3,7,4) 3在Sheet1中，利用数据库函数及已设置的条件区域，根据以下情况计算，并将结果填入相应的单元格中。计算：获取具有硕士学历，职务为经理助理的员工姓名，并将结果保存在Sheet1中的E31单元格中。提示：使用DGET()函数，从数据库中取出符合指定条件且唯一存在的记录。3个参数，与DAVERAGE()、DSUM()相同。E31 =DGET(A2:J27,C2,L3:M4)4使用函数，判断Sheet1中L12和M12单元格中的文本字符串是否完全相同。注意：（a）如果完全相同，结果保存为TRUE，否则保存为FALSE。 （b）将结果保存在Sheet1中的N11单元格中。提示：N12 =EXACT(L12,M12)5将sheet1中的“公司员工人事信息表”复制到sheet2中，并对sheet2进行高级筛选。要求：（a）筛选条件为：“籍贯”-广东、“学历”-硕士、“职位”-职员；（b）将筛选条件保存在sheet2中。注意：（a） 复制过程中，将标题项“公司员工人事信息表”连同数据一同复制；（b） 复制数据表后，粘贴时，数据表必须顶格放置。提示：选中“公司员工人事停息表”，“复制”定位在SHEET2，“粘贴”建立条件，高级筛选。6根据sheet1中的“公司员工人事信息表”，在sheet3中新建一张数据