《nEXCEL题目.docVIP

一、在考生文件夹下，已有KS.XLS文件，按要求完成以下操作，并将结果存盘（共6题）： 1、 使用VLOOKUP函数，对Sheet1中的商品单价进行自动填充。 要求：根据价格表中的商品单价，利用VLOOKUP函数， 将其单价自动填充到采购表中的单价列中。 =VLOOKUP(A11,F$3:G$5,2,FALSE) 2、 使用VLOOKUP函数，对Sheet1中的商品折扣率进行自动填充。 要求：根据折扣表中的商品折扣率，利用相应的函数，将其折扣率自动填充到采购表中的折扣列中。（用百分号表示，如：6%） =VLOOKUP(B11,A$3:B$6,2,TRUE) =IF(B11=A$6,B$6,IF(B11=A$5,B$5,IF(B11=A$4,B$4,B$3))) 3、 利用数组公式，计算Sheet1中的合计金额。 要求：根据采购数量，单价和折扣率，计算采购的合计金额并取整。 计算公式：单价*采购数量*（1-折扣率） {=B11*D11*(1-E11)} 4、 使用SUMIF函数，统计各种商品的采购总量和采购总金额，将结果保存在Sheet1中的 统计表当中的相应单元格中。 =SUMIF(A11:A43,I12,B11:B43) =SUMIF(A11:A43,I12,F11:F43) 5、 对“采购表”进行高级筛选。 a. 筛选条件为：采购数量150，折扣率0； b. 将筛选结果保存在Sheet1中A50开始的单元格中。 6、 根据Sheet1中的采购表，新建一张数据透视表，要求： a. 显示每个采购时间点所采购的所有项目数量汇总情况； b. 行坐标设置为采购时间； c. 将对应的数据透视表保存在Sheet1中I16开始的区域中。二、在考生文件夹下，已有KS.XLS文件，按要求完成以下操作，并将结果存盘（共6题）： 1、 计算Sheet1中用户的年龄(设当前系统日期为2011年4月20日),将计算结果保存到年龄列中。 =YEAR(TODAY())-YEAR(C2) 2、 使用REPLACE函数，对Sheet1中用户的电话号码进行升级。 升级方法是在原电话号码前加上8，将升位结果保存在升级后号码列的相应单元格中。 =REPLACE(F2,1,0,8) 3、 使用IF函数，判断Sheet1中的大于等于40岁的男性，将结果是或否保存在Sheet1中的 是否=40男性列当中。 =IF(B2=男,IF(D2=40,是,否),否) 4、 对Sheet1中的数据，根据以下条件,利用数据库函数进行统计： a. 统计性别为男、出生年月在1960-12-31以前的人数，将结果填入Sheet1的L2单元格中；=DCOUNT(A1:H37,3,K6:L7) b. 求性别为女的用户的平均年龄,将结果用INT函数取整后填入Sheet1的L3单元格中。=INT(DAVERAGE(A1:H37,4,K9:K10)) 5、 对表格中的数据进行高级筛选。 a. 筛选条件为：性别－女、所在区域－西湖区； b. 将筛选结果保存在Sheet1中A50开始的单元格中。 6、 根据Sheet1的结果，创建一张数据透视表，要求： a. 显示每个区域所拥有的用户数量； b. x坐标设置为所在区域； c. 计数项为所在区域； d. 将对应的数据透视表保存在Sheet1中K50开始的区域中。三、在考生文件夹下，已有KS.XLS文件，按要求完成以下操作，并将结果存盘（共6题）： 1. 使用数组公式，计算每种产品的数量和价格，将结果保存到表中的采购数量和价格列中，其中“价格”列使用人民币货币样式。 {=I2:I17*F2:F17} 2.利用数据库函数及已设置的条件区域，计算以下情况的结果，并将结果保存相应的单元格中。 a. 计算：商标为上海，瓦数小于100的白炽灯的平均单价； =DAVERAGE(A1:J17,6,L2:N3) b. 计算：产品为白炽灯，其瓦数大于等于80且小于等于100的采购数量总和。 =DSUM(A1:J17,9,L7:N8) 3.计算每个采购人员采购的产品总数量和产品总价格，结果保存在相应的单元格中。 =SUMIF(A2:A17,L11,I2:I17) 产品总价格 =SUMIF(A2:A17,L11,J2:J17) 4. 根据产品总价格，对采购人员进行升序排名，结果存放在排名列中。 =RANK(N11,$N$11:$N$15,1) 统计采购盒数大于等于4盒的产品数目，结果存放在相应的单元格中。 =COUNTIF(H2:H17,=4) 5. 对Sheet1中的数据进行高级