浙江省计算机二级Excel公式一览（1）

第一题 学生成绩

【数组公式】3. 使用数组公式，根据Sheet1中的数据，计算总分和平均分，将其结果保存到表中的“总分”列和“平均分”列。

总分列选中整一列后，在公式编辑栏输入=C2:C39+D2:D39+E2:E39，然后同时摁Ctrl+Shift+Enter。

平均分列同样操作，选中整一列后在公式编辑栏输入=(C2:C39+D2:D39+E2:E39)/3，然后同时摁Ctrl+Shift+Enter。

【排序函数】4. 使用RANK函数，根据Sheet1中的“总分”列对每个同学排名情况进行统计，并将排名结果保存到表中的“排名”列。（如果多个数值排名相同，则返回该组数值的最佳排名）

F2单元格中输入=RANK(F2,$F$2:$F$39)，然后进行自动填充，注意排序区域的绝对引用。

最佳排名使用的函数本应是RANK.EQ，但使用RANK函数也可得到相应分数。

RANK(number,ref,[order])

RANK.EQ(number,ref,[order])

【逻辑函数】5. 使用逻辑函数，判断Sheet1中每个同学的每门功课是否均高于全班单科平均分。

I2单元格中输入=IF(AND(C2>=AVERAGE($C$2:$C$39),D2>AVERAGE($D$2:$D$39),E2>=AVERAGE($E$2:$E$39)),TRUE,FALSE)，然后进行自动填充，注意排序区域的绝对引用。

IF(Logical_test,Value_if_true,Value_if_false)

【统计函数】6. 根据Sheet1中的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将统计结果保存到Sheet2中的相应位置。

在Sheet2中选中B2单元格，输入=COUNTIF(Sheet1!D2:D39,"<20")，B3中输入=COUNTIF(Sheet1!D2:D39,"<40")-COUNTIF(Sheet1!D2:D39,"<20")，B4中输入=COUNTIF(Sheet1!D2:D39,"<60")-COUNTIF(Sheet1!D2:D39,"<40")，B5中输入=COUNTIF(Sheet1!D2:D39,"<80")-COUNTIF(Sheet1!D2:D39,"<60")，B6中输入=COUNTIF(Sheet1!D2:D39,"<100")-COUNTIF(Sheet1!D2:D39,"<80")。

COUNTIF(Range,Criteria)

第二题 采购表

【四舍五入】1. 在Sheet5中，使用函数，将A1单元格中的数四舍五入至整百，存放在B1单元格中。

在B1单元格输入=ROUND(A1,-2)，摁回车。

ROUND(number, num_digits)

【查询函数】3. 使用VLOOKUP函数，对Sheet1中“采购表”的“单价”列进行填充。

在D11单元格中输入=VLOOKUP(A11,$F$2:$G$5,2,0)，然后进行自动填充，注意排序区域的绝对引用。

VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)

【逻辑函数】4. 使用IF函数，对Sheet1“采购表”中的“折扣”列进行填充。

在E11单元格中输入=IF(B11>$A$6,$B$6,IF(B11>$A$5,$B$5,IF(B11>$A$4,$B$4,$B$3)))，然后进行自动填充，注意排序区域的绝对引用。

【数组公式】5. 使用数组公式，对Sheet1中“采购表”的“合计”列进行计算。

选中合计列，在公式编辑栏中输入=B11:B43*D11:D43*(1-E11:E43)，然后同时摁Ctrl+Shift+Enter。

【求和函数】6. 使用SUMIF函数，计算各种商品的采购总量和采购总金额，将结果保存在Sheet1中的“统计表”当中相应位置。

在J12单元格中输入=SUMIF($A$11:$A$43,I12,$B$11:$B$43)，然后向下进行自动填充，注意排序区域的绝对引用。

在K12单元格中输入=SUMIF($A$11:$A$43,I12,$F$11:$F$43)，然后向下进行自动填充，注意排序区域的绝对引用。

SUMIF(range, criteria, [sum_range])

第三题 教材订购情况表

【数组公式】3. 使用数组公式，对Sheet1中“教材订购情况表”的订购金额进行计算。

选中订购金额列，在公式编辑栏输入=G3:G52*H3:H52，然后同时摁Ctrl+Shift+Enter。

【统计函数】4. 使用统计函数，对Sheet1中“教材订购情况表”的结果按以下条件进行统计，并将结果保存在Sheet1中的相应位置。

在L2单元格输入=COUNTIF($A$3:$I$52,"高等教育出版社")，注意绝对引用。

在L3单元格输入=COUNTIF($G$3:$G$52,">110")-COUNTIF($G$3:$G$52,">850")，注意绝对引用。

【求和函数】5. 使用函数，计算每个用户所订购图书所需支付的金额，并将结果保存在Sheet1中“用户支付情况表”的“支付总额”列中。

在L8单元格输入=SUMIF($A$3:$A$52,K8,$I$3:$I$52)，然后向下进行自动填充，注意求和区域的绝对引用。

【闰年公式】6. 使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存“闰年”；如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。

闰年定义：年数能被4整除而不能被100整除，或者能被400整除的年份。

在Sheet2表B2单元格中输入=IF(OR(MOD(A2,400)=0,AND(MOD(A2,4)=0,MOD(A2,100)<>0)),"闰年","平年")，然后向下进行自动填充。

第四题 号码升级

【闰年公式】2. 在Sheet5的B1单元格中输入公式，判断当前年份是否为闰年，结果为TRUE或FALSE。

闰年定义：年数能被4整除而不能被100整除，或者能被400整除的年份。

在Sheet5的B1单元格中输入=IF(OR(MOD(YEAR(NOW()),400)=0,AND(MOD(YEAR(NOW()),4)=0,MOD(YEAR(NOW()),100)<>0)),TRUE,FALSE)。

MOD(Number,Divisor)

YEAR(Serial_Number)

【时间函数】3. 使用时间函数，对Sheet1中用户的年龄进行计算。

在D2单元格中输入=YEAR("2013-5-1")-YEAR(C2)，然后向下进行自动填充。

【号码升级】4. 使用REPLACE函数，对Sheet1中用户的电话号码进行升级。

在G2单元格中输入=REPLACE(F2,5,0,"8")，然后向下进行自动填充。

REPLACE(Old_text,Start_num,Num_chars,New_text)

【逻辑函数】5. 在Sheet1中，使用AND函数，根据“性别”及“年龄”列中的数据，判断所有用户是否为大于等于40岁的男性，并将结果保存在“是否>=40男性”列中。

在H2单元格中输入=IF(AND(D2>=40,B2="男"),TRUE,FALSE)。

【统计函数】6. 根据Sheet1中的数据，对以下条件，使用统计函数进行统计。

在Sheet2表B2单元格中输入=COUNTIF(Sheet1!B2:B37,"男")。

在Sheet2表B3单元格中输入=COUNTIF(Sheet1!D2:D37,">40")。

第五题 采购情况表

【数据查重】2. 在Sheet1中设定第31行不能输入重复的数值。

“允许”选择自定义，勾选忽略空值，公式输入=COUNTIF(31:31,A31)=1。

【数组公式】3. 使用数组公式，计算Sheet1中的每种产品的价值，将结果保存到表中的“价值”列中。

选中“价值”列，在公式编辑栏中输入=E2:E17*F2:F17*G2:G17。

【数据库函数】4. 在Sheet2中，利用数据库函数及已设置的条件区域，计算以下情况的结果，并将结果保存在相应的单元格中。

在Sheet2表G23单元格中输入=DAVERAGE(A2:H18,E2,J3:L4)。

DAVERAGE(Database,Field,Critaria)

在Sheet2表G24单元格中输入=DSUM(A2:H18,G2,J8:L9)。

DSUM(Database,Field,Critaria)

【计数函数】5. 某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。对于调查对象，只能回答Y（吸烟）或者N（不吸烟）。根据调查情况，制作出Sheet3。请使用函数，统计符合以下条件的数值。

在Sheet3表B14单元格中输入=COUNTBLANK(B2:E11)。

COUNTBLANK(Range)。

在Sheet3表B15单元格中输入=COUNTIF(B2:E11,"Y")。

【文本函数】6. 使用函数，对Sheet3中的B21单元格中的内容进行判断，判断其是否为文本，如果是，结果为“TRUE”；如果不是，结果为“FALSE”，并将结果保存在Sheet3中的B22单元格当中。

在Sheet3表B22单元格中输入=ISTEXT(B21)。

ISTEXT(Value)。