1、12:13 2013/6/21 第1题 1. 使用数组公式，对Sheet1计算总分和平均分，将其计算结果保存到表中的“总分”列和“平均分”列当中。利用公式，之后拖动2. 使用RANK函数，对Sheet1中的每个同学排名情况进行统计，并将排名结果保存到表中的“排名”列当中。rank 第一是排名数据：可以总分或者平均分，ref范围，需要选定排名的范围需要绝对值引用加美元$之后升序或者降序省略之=RANK(F2,F$2:F$39)3. 使用逻辑函数判断Sheet1中每个同学的每门功课是否均高于平均分，如果是，保存结果为TRUE，否则，保存结果为FALSE，将结果保存在表中的“三科成绩是否均超过平均”
2、列当中。使用and函数 都为true是返回true:=AND(C2AVERAGE(C$2:C$39),D2AVERAGE(D$2:D$39),E2AVERAGE(E$2:E$39)4. 根据Sheet1中的结果，使用统计函数，统计“数学”考试成绩各个 分数段的同学人数，将统计结果保存到Sheet2中的相应位置。使用countif函数：=COUNTIF(Sheet1!D$2:D$39,“75，“数学” 75，“英语” 75，“总分” 250 b. 将结果保存在Sheet3中。6. 根据Sheet1中的结果，在Sheet4中 一 数据 表，要求：a. 是否三科均超过平均分的学 人数 b. 行区 置
3、为：“三科成绩是否均超过平均” c. 计数 为三科成绩是否均超过平均。：行区 三科 数据区 ：三科第2题1. 使用VLOOKUP函数，对Sheet1中的 进行 动 。u 要求：根据? 表?中的 ，利用VLOOKUP函数，将其 动 到 表中的“ ”列中。u 注意：函数中 数如果需要用到绝对 的， 使用绝对 进行 题，其 式。=VLOOKUP(A11,F$3:G$5,2,FALSE)2. 使用IF逻辑函数，对Sheet1中的“”列进行 。u 要求：根据?表?中的 ，利用相应的函数，将其 到 表中的?列中。=IF(B11=300,B$6,IF(B11=200,B$5,IF(B11=100,B$4,B
4、$3)3. 利用公式，计算Sheet1中的“ 表”的“计”列进行计算。u 要求：根据? 数?，? ?和?，计算 的?计currency1?，将结果保存在?计?列中。u 计算公式： * 数*“1-=D11*B11*(1-E11)4.使用SUMIF函数，统计各 的 总和 总currency1，将结果保存在Sheet1中的“统计表”当中。=SUMIF(A$11:A$43,I12,B$11:B$43)注意绝对引用=SUMIF(A$11:A$43,I12,F$11:F$43)5. 将Sheet1中的“ 表”复制到Sheet2中，并对Sheet2进行高级筛选。要求：a. 筛选条件为：“ 数”150，“”0
5、 b. 将筛选结果保存在Sheet2中。注意：“a需考是否fifl或 动筛选条件;“b复制过中，将题 “ 表”同数据一同复制;“c复制数据表后， ，数据表 ”置。6. 根据Sheet1中的 表， 一个数据 Chart1，要求：a. 每个 间 的 数总情况 b. x 置为“ 间” c. 求和 为 数 d. 将对应的数据 表保存在Sheet3中。注意选数据 表数据 第3题1. 使用数组公式，计算Sheet1中的 currency1，将结果保存到表中的 “currency1”列当中。公式。2.使用统计函数，对Sheet1中结果以 条件进行统计，并将结果保存在Sheet1中的相应位置，要求：a. 统计
6、 名为“高 ”的 的 数 b. 统计 数 于110 于850的 的 数。=COUNTIF(G2:G51,“=500， currency1总数=40”。=IF(B2=“ “,IF(D2=40,TRUE,FALSE),FALSE)4. 对Sheet1中的数据，根据以 条件，利用函数进行统计：a. 统计 为“ ”的用 人数，将结果 Sheet2的B1 元 中 =COUNTIF(Sheet1!B2:B37,Sheet1!B2)b. 统计 为“40” 的用 人数，将结果 Sheet2的B2 元 中。=COUNTIF(Sheet1!D2:D37,“40“)5. 将Sheet1复制到Sheet3，并对She
7、et3进行高级筛选。A. 筛选条件为：“ ” “ 在区 ” 区 b. 将筛选结果保存在Sheet3中。6. 根据Sheet1的结果， 一数据 Chart1，要求：a. 每个区 的用 数 b. x 置为“ 在区 ” c. 计数 为“ 在区 ” d. 将对应的数据 表保存在Sheet4中。在考 文件的 Excel 文件中， Excel.xls 文件。 列要求 ，并将结果在位置保存。注意：考 在 题， 将数据表进行 改。1. 使用数组公式，计算Sheet1中的每产 的 值，将结果保存到表中的“ 值”列中。计算 值的计算 为：“ *每盒数* 盒数”。2. 在Sheet2中，利用数据库函数 置的条件区
8、，计算以 情况的结果，并将结果保存相应的 元中。a. 计算： 为 海，瓦数 于100的白炽灯的平均 =DAVERAGE(A1:H17,E1,J2:L3)b. 计算：产 为白炽灯，其瓦数 于于80 于于100的数。3. 某公司对各个部门员工吸烟情况进行统计， 为人力资源搭配的一个数据依据。对于调查对象，只回Y“吸烟或者N“ 吸烟。根据调查情况，制 Sheet3。 使用函数，统计符以 条件的数值。a. 统计未登记的部门个数 b. 统计在登记的部门中，吸烟的部门个数。4.使用函数，对Sheet3中的B21 元 中的内容进行判断，判断其是否问文本，如果是，结果为“TRUE” 如果 是，结果为“FALS
9、E”，并将结果保存在Sheet3中的B22 元 当中。5. 将Sheet1复制到Sheet4中，对Sheet4进行高级筛选，要求：a. 筛选条件：“产 为白炽灯， 为 海”，并将结果保存 b. 将结果保存在Sheet4中。6. 根据Sheet1的结果，在Sheet5中 一 数据 表，要求：a. 同 的 同产 的 数 b. 行区 置为“产 ” c. 列区 置为“ ” d. 计数 为“ 盒数”。在考 文件的 Excel 文件中， Excel.xls 文件。 列要求 ，并将结果在位置保存。注意：考 在 题， 将数据表进行 改。1. 利用公式，计算Sheet1中的房 总。房 总的计算公式为：“ 积*
10、”2. 使用数组公式，计算Sheet1中的契税总。契税总的计算公式为：“契税*房 总”3. 使用函数，根据Sheet1中的结果，统计每个销售人员的销售总，将结果保存在Sheet2中的相应的元 中。4. 使用RANK函数，根据Sheet2的结果，对每个销售人员的销售情况进行排序，并将结果保存在“排名”列当中。5. 将Sheet1复制到Sheet3中，并对Sheet3进行高级筛选，要求：a. 筛选条件为：“ 型”为两室一厅，“房 总”1000000 b. 将结果保存在Sheet3中。6. 根据Sheet1的结果， 一 数据 Chart1，要求：a. 每个销售人员销售房屋 缴纳契税总 b. 行区 置
11、为“销售人员” c. 求和 置为契税总 d. 将对应的数据 表保存在Sheet4中。在考 文件的 Excel 文件中， Excel.xls 文件。 列要求 ，并将结果在位置保存。注意：考 在 题， 将数据表进行 改。1. 使用IF函数，对Sheet1中的“学位”列进行 动 。要求： 的内容根据“学历”列的内容来确定“假定学 均 获 相应学位：- 博士研究 博士- 硕士研究 硕士- 本科 学士- 其 =IF(G3=“博士研究 “,“博士“,IF(G3=“硕士研究 “,“硕士“,IF(G3=“本科“,“学士“,“)2. 使用数组公式，在Sheet1中计算：a.“笔试比例分”，计算 为：“笔试成绩/
12、3*60b.“ 试比例分”，计算 为： 试成绩*40c.“总成绩”，计算 为：笔试比例分+ 试比例分3. 修改数组公式，将Sheet1复制到Sheet2，在Sheet2中计算：要求：修改“笔试比例分”的计算，计算 为：“笔试成绩/2*60。4. 在 Sheet2中，添加一列，将其命名为“排名”。要求：使用RANK函数，根据“总成绩”对 考 排名。5. 将Sheet2复制到Sheet3，并对Sheet3进行高级筛选。a. 筛选条件为：“报考 位” 中院 “ ” “学历” 硕士研究 b. 将筛选结果保存在Sheet3中6. 根据Sheet2，在Sheet4中 一数据 表。要求：a. 每个报考 位的
13、人的 同学历的总人数b. 行区 置为“报考 位”c. 列区 置为“学历”d. 数据区 置为“学历”e. 计数 为学历第8题 员工代1. 使用REPLACE函数，对Sheet1中的员工代进行升级，要求：a. 升级 ：在PA后 加 0 b. 将升级后的员工代结果 表中的“升级员工代”列中。=REPLACE(B2,1,2,“PA0“)使用replace 严 照 频论pb。2. 使用间函数，对Sheet1员工的“ ”和“工 ”进行计算，并将结果 到表中的“ ”列和“工 ”列中。先计算当前 =YEAR(NOW()-YEAR(E2)再减去 元 的 。工 计算雷同用复制F2到H2 公式 动改变 需要的数据，
14、 己写公式数据错3. 使用统计函数，对Sheet1中的数据，根据以 统计条件进行如 统计。a. 统计 员工的人数，结果 N3 元 中 =COUNTIF(D2:D65,D4)b. 统计高级工师人数，结果 N4 元 中 =COUNTIF(I2:I65,I58)c. 统计工 于于10的人数，结果 N5 元 中。=COUNTIF(H2:H65,“=10“)使用count if函数定统计范围和条件：4. 使用逻辑函数，判断员工是否资 评“高级工师”。评选条件为：工 于20， 为工师的员工。使用AND函数，在 函数中找到，写两个条件：=AND(H220,I2=“工师“)5. 将Sheet1复制到Sheet
15、2中，并对Sheet2进行高级筛选，要求：a. 筛选条件为：“ ” ，“ ”30，“工 ”10，“职” 助工 b. 将结果保存在Sheet2中。己 立筛选条件，在表 底部，否则之后 。数据-筛选-高级筛选，条件区 。6. 根据Sheet1中的数据， 一 数据 Chart1，要求：a. 工厂中各个职的人数 b. x 置为“职” c. 计数 为职 d. 将对应的数据 表保存在Sheet3中。这个只是需要选数据 表行代表x，数据区 是职即可。完成。第9题1. 使用HLOOKUP函数，对Sheet1中的停车 进行 动 。要求：根据Sheet1中的“停车 表” ，利用HLOOKUP函数对“停车情况记录表
16、”中的“ ”列根据 同的车型进行 动 。=HLOOKUP(B16,A$2:C$3,2,FALSE)选中C9 元 ，-fx插 函数-选HLOOKUP-选 数公式注意美元绝对$符号2. 在Sheet1中，利用间函数计算汽车在停车库中的停”间，要求：a. 公式计算 为“库间- 库间”b. 式为：“ ：分钟：秒”“例如：一 十五分十二秒在停”间中的表 为：“1：15：12”选F9 直接相减 注意保存3. 使用函数公式，计算停车费用，要求：根据停”间的长短计算停车费用，将计算结果 到“应 currency1”列中。注意：a. 停车 收费，对于 满一个 的照一个 计费 b. 对于超过 数十五分钟的多累积一
17、个 。“例如1 23分，将以2 计费需要嵌套if条件=IF(HOUR(F9)=0,1,IF(MINUTE(F9)15,HOUR(F9)+1,HOUR(F9)*C9先插 一个if函数之后再在后一个条件插 一个if函数，击左侧位置if函数注意 hour 分钟的拼写minute4. 使用统计函数，对Sheet1中的“停车情况记录表”根据 列条件进行统计，要求：a. 统计停车费用 于于40元的停车记录条数“若Sheet1中未 置指定 元 ， 考 己在Sheet1中选适的位置进行统计b. 统计最高的停车费用 “若Sheet1中未 置指定 元 ， 考 己在Sheet1中选适的位置进行统计需要 己 元 ：=
18、COUNTIF(G8:G39,“=40“)使用countif函数，range选G5列，条件写=40边也是 己把选 元 ：使用max函数，选范围就可以5. 对Sheet2，进行高级筛选，要求：a. 筛选条件为：“车型” 汽车，“应 currency1”30 b. 将结果保存在Sheet2中。己编辑条件区 ，选定筛选区 ，数据-筛选-高级筛选-确定保存。注意条件区 车型应 currency1汽车 =306. 根据Sheet1， 一个数据 Chart1，要求：a. 各车型 收费用的总 b. 行区 置为“车型” c. 求和数 为“应 currency1” d. 将对应的数据 表保存在Sheet3中。选
19、sheet1中数据区 -数据- 表 ，选最底部的选 ，chart1 动 成。注意选数据表数据在考 文件的 Excel 文件中， Excel.xls 文件。 列要求 ，并将结果在位置保存。注意：考 在 题， 将数据表进行 改。1. 使用IF函数，对Sheet1中的“温度较高的 ”列进行 动 。击D2-击fx插 函数-选if( 用第三)- 写条件=IF(B2C2,“ “,“ 海“)， 完成。2. 使用数组公式，对Sheet1中的相 温度值“ 相对于 海的温 进行 。选E2-E16- 号-选B2-B16-C2-C16 - ctrl+shift+enter 计算3. 利用函数，根据Sheet1中的结果
20、，符以 条件的进行统计。a. 这 个 以来的最高 温和最 温 选C18 元 -击fx插 函数-max-范围选C2-C16 确定C19 min b. 海这 个 以来的最高 温和最 温。4. 将Sheet1复制到Sheet2中，在Sheet2中， 编辑数组公式，将Sheet2中的“相 的温度值”中的数值 其绝对值“均为 数。ctrl Actrl c ctrl v 元 定位A1 温 绝对值选E2-E16 编辑公式=ABS(B2:B16-C2:C16) abs绝对值公式 5. 将Sheet2复制到Sheet3中，并对Sheet3进行高级筛选，要求：筛选条件：“ 平均 温”=20，“ 海平均 温”ctr
21、l c ctrl v 元 定位A1 筛选：复制B1 到某区 复制C1到某区 平均 温 海平均 温=20 “2009001“到B3-fx插 函数-replace - 数-old_text:A3Start_num:1 Num_chars New_text:2009 回车 拖动=REPLACE(A3,1,2009) 2. 使用数组公式，对Sheet1中“学 成绩表”的“总分”列进行计算。计算 ：总分 =语文 + 数学 + 英语 + + 题算：=E3:E24+F3:F24+G3:G24+H3:H24+I3:I24选中J3:J24 3. 使用IF函数，根据以 条件，对Sheet1中“学 成绩表”的“考评
22、”列进行计算。条件：如果总分=350， 为“ ” 否则， 为“ ”。IF(J3=350,“ “,“ “)4. 在Sheet1中，利用数据库函数 置的条件区 ，根据以 情况计算，并将结果 到相应的 元 当中。条件：“a计算：“语文”和“数学”成绩都 于或于85的学 人数 选中I28 元 -fx插 函数-数据库函数DCount-Database:A2:K24 Field:F2 Criteria 条件 M2:N3 -确定DCOUNT(A2:K24,F2,M2:N3)“b计算：“ ”成绩 于或于90的“ ” 名 =DGET(A2:K24,C2,M7:N8)“c计算：“ ”成绩中 的平均分 =DAVER
23、AGE(A2:K24,I2,M12:M13)“d计算：“ ”成绩中 的最高分。=DMAX(A2:K24,I2,M12:M13)5. 将Sheet1中的“学 成绩表”复制到Sheet2当中，并对Sheet2进行高级筛选。要求：“a筛选条件为：“ ” - “英语” - 90 “ ” - 95 “b将筛选结果保存在Sheet2中。注意：“a需考是否fifl或 动筛选条件;“b复制过中，将题 “学 成绩表”同数据一同复制;“c复制数据表后， ，数据表 ”置。sheet 中ctrl A+Ctrl C+sheet2 CtrlV 立筛选条件： 英语 9095在筛选区 ：数据(D)-筛选()-高级筛选-选条件区 ，确定6. 根据Sheet1中“学 成绩表”，在Sheet3中 一 数据 表。要求：“a 同 同考评结果的学 人数情况 “b行区 置为“ ” “c列区 置为“考评” “d数据区 置为“考评” “e计数 为“考评”。选中区 -数据- -本 表-选sheet3:A1-选 -拖 计数 动完成需 置
