1、1. RANK 函数（ 统计函数，Rank(number, ref, order）,number 为需要找到排位的数字；ref 为数字列表数组或对数字列表的引用；order 为一数字，知名排位的方式，0 或省略降序排位，非 0 升序排位= RANK(F2,$F$2:$F$39,0) 注意 ref 为绝对引用2. 使用 逻辑函数 判断 Sheet1 中每个同学的每门功课是否均高于平均分，如果是，保存结果为 TRUE，否则保存结果为 FALSE，将结果保存在表中的“三科成绩是否均超过平均”列当中。=IF(AND(C10AVERAGE($C$2:$C$39),D10AVERAGE($D$2:$D$3
2、9),E10AVERAGE($E$2:$E$39),“TRUE“,“FALSE”)3. 3、根据 Sheet1 中的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将统计结果保存到 Sheet2 中的相应位置。a) 6080 分数段： =COUNTIF(D2:D39,”=75，“英语”=75，“总分”=250;将结果保存在 Sheet3 中。在空白区域创建筛选条件，注意筛选条件如“语文“”订数“与原统计表格式保持一致。数据筛选高级筛选，”列表区域“与”条件区域“的选择1、 使用 统计函数 ，对 Sheet1 中结果按以下条件进行统计，并将结果保存在Sheet1 中的相应位置，要求
3、：a) 统计出版社名称为“高等教育出版社”的书的种类数；b) 统计订购数量大于 110 且小于 850 的书的种类数。a 小题使用 COUNTIF 函数，Range “D3:D52”, Criteria 参数为”高等教育出版社”b 小题=COUNTIF(G3:G52,”0）, MOD(A2, 400)=0），”闰年”，”平年”）1、 在 Sheet2 中，利用数据库函数及已设置的条件区域，计算以下情况的结果，并将结果保存相应的单元格中。计算：商标为上海，瓦数小于 100 的白炽灯的平均单价计算：产品为白炽灯，其瓦数大于等于 80 且小于等于 100 的数量。=DAVERAGE(A1:H17,
4、E1微软用户 4, J2:L3)=DCOUNT(A1:H17, , 微软用户 5J7:L8)2、 某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。对于调查对象，只能回答 Y（吸烟）或者 N（不吸烟）。根据调查情况，制作出 Sheet3。请使用函数，统计符合以下条件的数值。a. 统计未登记的部门个数；b. 统计在登记的部门中，吸烟的部门个数。=COUNTBLANK微软用户 6(B2:E11)=COUNTIF(B2:E11, “Y”)3、 使用函数，对 Sheet3 中的 B21 单元格中的内容进行判断，判断其是否问文本，如果是，结果为“TRUE”；如果不是，结果为“FALS
5、E”，并将结果保存在 Sheet3 中的 B22 单元格中。=ISTEXT微软用户 7(B21)=YEAR(TODAY()微软用户 8)-YEAR(C2)2、使用 REPLACE 函数，对 Sheet1 中用户的电话号码进行升级。要求：对“原电话号码”列中的电话号码进行升级。升级方法是在区号（0571）后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。使用文本函数 REPLACE：Old_text: G2; Start_num:5; Num_char:0 微软用户 9；New_text: “8”2. 使用逻辑函数，判断 Sheet1 中的“大于等于 40 岁的男性”，将结果
6、保存在 Sheet1 中的“是否=40 男性”。=IF(AND(B3=“男“,D3=40),“是“,“否“)3. 对 Sheet1 中的数据，根据以下条件，利用函数进行统计：a) 统计性别为“男”的用户人数，将结果填入 Sheet2 的 B1 单元格中；b) 统计年龄为“40 岁”的用户人数，将结果填入 Sheet2 的 B2 单元格中。COUNTIF(Sheet1! B2: B37, “男”) COUNTIF(Sheet1!D2:D37,”40”)1、 使用函数，根据 Sheet1 总的结果，统计每个销售人员的销售总额，将结果保存在 Sheet2 中相应的单元格中。解法有二：a. 在 She
7、et2 需填充的单元格中分别输入函数，如在人员甲的销售总额函数为：=SUMIF(Sheet1!K3: K26,”=人员甲”, Sheet1!I3:I26)，以此类推。b. 在 Sheet2 B2 单元格中输入公式：=SUMIF(Sheet1!$K$3:$K$26,Sheet2!A2,Sheet1!$I$3:$I$26)，拖拉B2 的填充柄。RANK(B2,$B$2:$B$微软用户 106,0) 注意 ref 为绝对引用1、 使用 VLOOKUP 函数，=VLOOKUP(A11,$F$2:$G$4,2,FALSE)微软用户 112、 使用 逻辑 函数，对 Sheet1 中的商品折扣率进行自动填充
8、。要求：根据“折扣表”中的商品折扣率，利用相应的函数，将其折扣率自动填充到采购表中的“折扣”列中。=IF(B23=15,HOUR(F9)+1,HOUR(F9)*C93、 使用统计函数，对 Sheet1 中的“停车情况记录表”根据下列条件进行统计，要求：a. 统计停车费用大于等于 40 元的停车记录条数 COUNTIFb. 统计最高的停车费用 =MAX(G9:G39)=ABS微软用户 14(B2:B16-C2:C16)=REPLACE(B2:B65,3,0,0)2. 使用时间函数，年龄计算：=YEAR(TODAY() 微软用户 15)-YEAR(E2)工龄计算：如果工龄要写成“*年*个月”这种形
9、式，则=CONCATENATE(DATEDIF(G8,TODAY(),“y“),“年“,DATEDIF(G8,TODAY(),“ym“),“个月“)或=CONCATENATE(YEAR(TODAY()-YEAR(G2),“年“,MONTH(TODAY()-MONTH(G2),“个月“)考虑到下题“统计工龄大于等于 10 的人数，结果填入 N5 单元格中”，故工龄计算应为=(YEAR(TODAY()-YEAR(G2)，直接求出年份，且无格式。sheet2 中的“相差的温度值” 中的数据取其绝对值（均为正数）=ABS(B3:B17-C3:C17)11 期末学生成绩表使用 REPLACE 函数，将
10、sheet1 中“学生成绩表” 的学生学号进行更改，并将更改的学号填入到“新学号”列中，学号的更改的方法为：在原学号的前面加上 2009，例如：“001”为“2009001”=REPLACE(A3,1,0,2009)计算：“语文” 和“数学” 成绩都大于或者等于 85 的学生人数=DCOUNT(A2:K24,M2:N3)计算：“体育” 成绩大于或等于 90 的“女生” 姓名=DGET(A2:K24,C2,M7:N8)计算：“体育” 成绩中男生的平均分=DAVERAGE(A2:K24,I2,M12:M13)计算：“体育” 成绩中男生的最高分=DMAX(A2:K24,I2,M12:M13)使用统计
11、函数 ，根据“3 月份销售统计表”中的数据，计算“分部销售业绩统计表”中的总销售额，并将结果填入到该表的“总销售额” 列中。=SUMIF($K$3:$L$44,N3,$L$3:$L$44)计算机学生成绩表使用文本函数中的一个函数，在 sheet1 中，利用“学号”列的数据，根据以下要求获得考生所考级别，并将结果填入到“级别” 列中。要求：（a）学号中的第八位指示的考生所考级别，例如：“085200821023080”中的“2”标识了该考生所考级别为二级。（b ）在 “级别 ”列中，填入的数据时函数的返回值。=MID(A3,8,1) 注意，在提取出级别后要 “设置单元格格式 ”设为“常规”。还要
12、按一下回车！统计“ 全体 1 级考生的考试平均分” ，并将计算结果填入到 N4 单元格中=SUMIF(C3:C57,1,J3:J57)/COUNTIF(C3:C57,1)根据“ 投资情况表 1”中的数据，计算 10 年以后得到的金额，并将结果填入到 B7 单元格中=FV(B3,B5,B4,B2)根据“ 投资情况表 2”中的数据，计算预计投资金额，并将结果填入到 E7 单元格中。=PV(E3,E4,E2)通讯费年度计划表使用 VLOOKUP 函数，根据 sheet1 中的“ 岗位最高限额明细 表”，填充“ 通讯费年度计划表”中的“ 岗位标准”列。=VLOOKUP(C4,$K$5:$L$12,2,
13、FALSE)使用 INT 函数，计算 sheet1 中“通讯费年度计划表”的“预计报销总时间” 列。（a）每月以 30 天计算；（b ）将结果填充在“预计报销总时间” 列中。=INT(F4-E4)/30)5、使用函数，根据 sheet1 中“ 通讯费年度计划表”的“ 年度费用” 列，计算预算总金额。要求：（a）将结果保存在 sheet1 中的 C2 单元格中=SUM(H4:H26)（b ）并根据 C2 单元格中的结果，转换为金额大写形式，保存在 sheet1 中 F2 单元格中。=TEXT(C2,“DBNum2“)医院病人护理统计表使用 VLOOKUP 函数，根据 sheet1 中的“ 护理价
14、格表”，对“医院病人护理统计表”中的“护理价格”列进行自动填充=VLOOKUP(E3,$K$3:$L$5,2,FALSE)使用数组公式，根据 sheet1 中“医院病人护理统计表” 中的“入住时间” 列和“出院时间”列中的数据计算护理天数，并把结果保存在“护理天数” 列中=G3:G30-D3:D30计算 sheet1“医院病人护理统计表” 中，性别为女性。护理级别为中级护理，护理天数大于 30 天的人数，并保存 N13 单元格中=DCOUNT($A$2:$I$30,$K$8:$M$9)计算护理级别为高级护理的护理费用总和，并保存 N22 单元格中=DSUM($A$2:$I$30,I2,$K$1
15、7:$K$18)图书订购信息表使用 IF 和 mid 函数，根据 sheet1 中的“图书订购信息表”中的“学号”列对“ 所属学院”列进行填充=IF(MID(A3,7,1)=“1“,“计算机学院“,“电子信息学院“)使用 COUNTBLANK 函数，对 sheet1 中的“图书订购信息表”中的“ 订数种类数”列进行填充=4-COUNTBLANK(D3:G3)使用公式，对 sheet1 中的“图书订购信息表” 中的“应缴总额（元） ”列进行填充。计算方法为：应缴总额=C 语言*单价+高等数学* 单价+大学语文*单价+大学英语*单价=D3*$L$3+E3*$L$4+F3*$L$5+H3*$L$6使
16、用统计函数，根据 sheet1 中的“图书订购信息表” 中的数据，统计应缴总额大于 100 元的学生人数=COUNTIF(I3:I50,“100“)体育学生成绩表在 sheet1“学生成绩表”中，使用 replace 函数和数组公式，并将原学好转变成新学号，同时将所得的新学号填入到“新学号” 列中，转变的方法为：将原学号的第四位后面加上“5”=REPLACE(A3,5,0,5)使用 if 函数和逻辑函数，对 sheet1“学生成绩表” 中的“结果 1”和“结果 2”列使用填充。要求：填充的内容根据以下条件确定：（将男生、女生分开写进 if 函数当中）结果 1：如果是男生，成绩 =14,填充为“
17、不合格”。如果是女生，成绩=16,填充为“不合格”。=IF(AND(D3=“男“,E37.5,填充为合格，成绩=5.5，填充为合格，成绩7.5),“合格“,IF(AND(D3=“女“,G3=5.5),“合格“,“不合格“)获取“100 米跑的最快的学生成绩”，并将结果填入到 sheet1 的 K4 单元格中=MIN(E3:E30)统计“ 所有学生结果 1 为合格的总人数” ，并将结果填入到 sheet1 的 K5 单元格中=COUNTIF(F3:F30,“合格“)计算“ 按年偿还贷款金额（年末）”=PMT(B4,B3,B2)计算“ 第 9 个月贷款利息金额”=IPMT(B4/12,9,B3*1
18、2,B2)1. RANK 函数（ 统计函数，Rank(number, ref, order）,number 为需要找到排位的数字；ref 为数字列表数组或对数字列表的引用；order 为一数字，知名排位的方式，0 或省略降序排位，非 0 升序排位= RANK(F2,$F$2:$F$39,0) 注意 ref 为绝对引用2. 使用 逻辑函数 判断 Sheet1 中每个同学的每门功课是否均高于平均分，如果是，保存结果为 TRUE，否则保存结果为 FALSE，将结果保存在表中的“三科成绩是否均超过平均”列当中。=IF(AND(C10AVERAGE($C$2:$C$39),D10AVERAGE($D$2
19、:$D$39),E10AVERAGE($E$2:$E$39),“TRUE“,“FALSE”)3. 3、根据 Sheet1 中的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将统计结果保存到 Sheet2 中的相应位置。a) 6080 分数段： =COUNTIF(D2:D39,”=75，“英语”=75，“总分”=250;将结果保存在 Sheet3 中。在空白区域创建筛选条件，注意筛选条件如“语文“”订数“与原统计表格式保持一致。数据筛选高级筛选，”列表区域“与”条件区域“的选择1、 使用 统计函数 ，对 Sheet1 中结果按以下条件进行统计，并将结果保存在Sheet1 中的相应
20、位置，要求：a) 统计出版社名称为“高等教育出版社”的书的种类数；b) 统计订购数量大于 110 且小于 850 的书的种类数。a 小题使用 COUNTIF 函数，Range “D3:D52”, Criteria 参数为”高等教育出版社”b 小题=COUNTIF(G3:G52,”0）, MOD(A2, 400)=0），”闰年”，”平年”）1、 在 Sheet2 中，利用数据库函数及已设置的条件区域，计算以下情况的结果，并将结果保存相应的单元格中。计算：商标为上海，瓦数小于 100 的白炽灯的平均单价计算：产品为白炽灯，其瓦数大于等于 80 且小于等于 100 的数量。=DAVERAGE(A1:
21、H17, E1微软用户 4, J2:L3)=DCOUNT(A1:H17, , 微软用户 5J7:L8)2、 某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。对于调查对象，只能回答 Y（吸烟）或者 N（不吸烟）。根据调查情况，制作出 Sheet3。请使用函数，统计符合以下条件的数值。a. 统计未登记的部门个数；b. 统计在登记的部门中，吸烟的部门个数。=COUNTBLANK微软用户 6(B2:E11)=COUNTIF(B2:E11, “Y”)3、 使用函数，对 Sheet3 中的 B21 单元格中的内容进行判断，判断其是否问文本，如果是，结果为“TRUE”；如果不是，结果为
22、“FALSE”，并将结果保存在 Sheet3 中的 B22 单元格中。=ISTEXT微软用户 7(B21)=YEAR(TODAY()微软用户 8)-YEAR(C2)2、使用 REPLACE 函数，对 Sheet1 中用户的电话号码进行升级。要求：对“原电话号码”列中的电话号码进行升级。升级方法是在区号（0571）后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。使用文本函数 REPLACE：Old_text: G2; Start_num:5; Num_char:0 微软用户 9；New_text: “8”2. 使用逻辑函数，判断 Sheet1 中的“大于等于 40 岁的男性
23、”，将结果保存在 Sheet1 中的“是否=40 男性”。=IF(AND(B3=“男“,D3=40),“是“,“否“)3. 对 Sheet1 中的数据，根据以下条件，利用函数进行统计：a) 统计性别为“男”的用户人数，将结果填入 Sheet2 的 B1 单元格中；b) 统计年龄为“40 岁”的用户人数，将结果填入 Sheet2 的 B2 单元格中。COUNTIF(Sheet1! B2: B37, “男”) COUNTIF(Sheet1!D2:D37,”40”)1、 使用函数，根据 Sheet1 总的结果，统计每个销售人员的销售总额，将结果保存在 Sheet2 中相应的单元格中。解法有二：a.
24、在 Sheet2 需填充的单元格中分别输入函数，如在人员甲的销售总额函数为：=SUMIF(Sheet1!K3: K26,”=人员甲”, Sheet1!I3:I26)，以此类推。b. 在 Sheet2 B2 单元格中输入公式：=SUMIF(Sheet1!$K$3:$K$26,Sheet2!A2,Sheet1!$I$3:$I$26)，拖拉B2 的填充柄。RANK(B2,$B$2:$B$微软用户 106,0) 注意 ref 为绝对引用1、 使用 VLOOKUP 函数，=VLOOKUP(A11,$F$2:$G$4,2,FALSE)微软用户 112、 使用 逻辑 函数，对 Sheet1 中的商品折扣率进
25、行自动填充。要求：根据“折扣表”中的商品折扣率，利用相应的函数，将其折扣率自动填充到采购表中的“折扣”列中。=IF(B23=15,HOUR(F9)+1,HOUR(F9)*C93、 使用统计函数，对 Sheet1 中的“停车情况记录表”根据下列条件进行统计，要求：a. 统计停车费用大于等于 40 元的停车记录条数 COUNTIFb. 统计最高的停车费用 =MAX(G9:G39)=ABS微软用户 14(B2:B16-C2:C16)=REPLACE(B2:B65,3,0,0)2. 使用时间函数，年龄计算：=YEAR(TODAY() 微软用户 15)-YEAR(E2)工龄计算：如果工龄要写成“*年*个
26、月”这种形式，则=CONCATENATE(DATEDIF(G8,TODAY(),“y“),“年“,DATEDIF(G8,TODAY(),“ym“),“个月“)或=CONCATENATE(YEAR(TODAY()-YEAR(G2),“年“,MONTH(TODAY()-MONTH(G2),“个月“)考虑到下题“统计工龄大于等于 10 的人数，结果填入 N5 单元格中”，故工龄计算应为=(YEAR(TODAY()-YEAR(G2)，直接求出年份，且无格式。sheet2 中的“相差的温度值” 中的数据取其绝对值（均为正数）=ABS(B3:B17-C3:C17)11 期末学生成绩表使用 REPLACE
27、函数，将 sheet1 中“学生成绩表” 的学生学号进行更改，并将更改的学号填入到“新学号”列中，学号的更改的方法为：在原学号的前面加上 2009，例如：“001”为“2009001”=REPLACE(A3,1,0,2009)计算：“语文” 和“数学” 成绩都大于或者等于 85 的学生人数=DCOUNT(A2:K24,M2:N3)计算：“体育” 成绩大于或等于 90 的“女生” 姓名=DGET(A2:K24,C2,M7:N8)计算：“体育” 成绩中男生的平均分=DAVERAGE(A2:K24,I2,M12:M13)计算：“体育” 成绩中男生的最高分=DMAX(A2:K24,I2,M12:M13
28、)使用统计函数 ，根据“3 月份销售统计表”中的数据，计算“分部销售业绩统计表”中的总销售额，并将结果填入到该表的“总销售额” 列中。=SUMIF($K$3:$L$44,N3,$L$3:$L$44)计算机学生成绩表使用文本函数中的一个函数，在 sheet1 中，利用“学号”列的数据，根据以下要求获得考生所考级别，并将结果填入到“级别” 列中。要求：（a）学号中的第八位指示的考生所考级别，例如：“085200821023080”中的“2”标识了该考生所考级别为二级。（b ）在 “级别 ”列中，填入的数据时函数的返回值。=MID(A3,8,1) 注意，在提取出级别后要 “设置单元格格式 ”设为“常
29、规”。还要按一下回车！统计“ 全体 1 级考生的考试平均分” ，并将计算结果填入到 N4 单元格中=SUMIF(C3:C57,1,J3:J57)/COUNTIF(C3:C57,1)根据“ 投资情况表 1”中的数据，计算 10 年以后得到的金额，并将结果填入到 B7 单元格中=FV(B3,B5,B4,B2)根据“ 投资情况表 2”中的数据，计算 预计投资金额，并将结果填入到 E7 单元格中。=PV(E3,E4,E2)通讯费年度计划表使用 INT 函数，计算 sheet1 中“通讯费年度计划表”的“预计报销总时间”列。（a）每月以 30 天计算；（b ）将结果填充在“预计报销总时间” 列中。=IN
30、T(F4-E4)/30)5、使用函数，根据 sheet1 中“ 通讯费年度计划表”的“ 年度费用” 列，计算预算总金额。要求：=SUM(H4:H26)转换为金额大写形式=TEXT(C2,“DBNum2“)计算 sheet1“医院病人护理统计表” 中，性别为女性。护理级别为中级护理，护理天数大于 30 天的人数，并保存 N13 单元格中=DCOUNT($A$2:$I$30,$K$8:$M$9)计算护理级别为高级护理的护理费用总和，并保存 N22 单元格中=DSUM($A$2:$I$30,I2,$K$17:$K$18)使用 IF 和 mid 函数，根据 sheet1 中的“图书订购信息表”中的“学
31、号”列对“ 所属学院”列进行填充=IF(MID(A3,7,1)=“1“,“计算机学院“,“电子信息学院“)使用 COUNTBLANK 函数，对 sheet1 中的“图书订购信息表”中的“ 订数种类数”列进行填充=4-COUNTBLANK(D3:G3)使用统计函数，根据 sheet1 中的“图书订购信息表” 中的数据，统计应缴总额大于 100 元的学生人数=COUNTIF(I3:I50,“100“)=REPLACE(A3,5,0,5)结果 1：如果是男生，成绩 =14,填充为“不合格”。如果是女生，成绩=16,填充为“不合格”。=IF(AND(D3=“男“,E37.5,填充为合格，成绩=5.5，填充为合格，成绩7.5),“合格“,IF(AND(D3=“女“,G3=5.5),“合格“,“不合格“)获取“100 米跑的最快的学生成绩”，并将结果填入到 sheet1 的 K4 单元格中=MIN(E3:E30)统计“ 所有学生结果 1 为合格的总人数” ，并将结果填入到 sheet1 的 K5 单元格中=COUNTIF(F3:F30,“合格“)计算“ 按年偿还贷款金额（年末）”=PMT(B4,B3,B2)计算“ 第 9 个月贷款利息金额”=IPMT(B4/12,9,B3*12,B2)
