浙江省计算机二级Excel公式一览（2）
第六题 房产销售表
【简单公式】3. 使用公式，计算Sheet1中“房产销售表”的房价总额，并保存在“房产总额”列中。
在I3单元格中输入=F3*G3
【数组公式】4. 使用数组公式，计算Sheet1中“房产销售表”的契税总额，并保存在“契税总额”列中。
选中“契税总额”列，在公式编辑栏输入=I3:I26*H3:H26，然后同时摁Ctrl+Shift+Enter。
【求和函数】5. 使用函数，根据Sheet1中“房产销售表”的结果，在Sheet2中统计每个销售人员的销售总额，将结果保存在Sheet2中的“销售总额”列中。
在Sheet2的B2单元格中输入=SUMIF(Sheet1!$K$3:$K$26,A2,Sheet1!$I$3:$I$26)，注意绝对引用。
【排序函数】6. 使用函数，根据Sheet2中“销售总额”列的结果，对每个销售人员的销售情况进行排序，并将结果保存在“排名”列当中。（若有相同排名，则返回最佳排名）
在Sheet2的C2单元格中输入=RANK(B2,$B$2:$B$6,0)，注意绝对引用和降序排序。
第七题 公务员考试成绩表
【逻辑函数】3. 使用IF函数，对Sheet1中的“学位”列进行自动填充。要求：填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）
在H3单元格输入=IF(G3="博士研究生","博士",IF(G3="硕士研究生","硕士",IF(G3="本科","学士","无")))，然后向下进行自动填充，注意括号的对应。
【数组公式】4. 使用数组公式，在Sheet1中计算笔试比例分、面试比例分、总成绩。
选中Sheet1表J列，输入=(I3:I18/3)*60%，然后同时摁Ctrl+Shift+Enter。
选中Sheet1表L列，输入=K3:K18*40%，然后同时摁Ctrl+Shift+Enter。
选中Sheet1表M列，输入=J3:J18+L3:L18，然后同时摁Ctrl+Shift+Enter。
【数组公式】5. 将Sheet1中的“公务员考试成绩表”复制到Sheet2中，根据以下要求修改“公务员考试成绩表”中的数组公式，并将结果保存在Sheet2中相应列中。
选中Sheet2表J列，清除内容后输入=(I3:I18/2)*60%，然后同时摁Ctrl+Shift+Enter。
【排序函数】6. 在Sheet2中，使用函数，根据“总成绩”列对所有考生进行排名。（如果多个数值排名相同，则返回该组数值的最佳排名）
在N3单元格中输入=RANK(M3,$M$3:$M$18,0)，注意绝对引用。
第八题 员工信息表
【数据查重】1. 在Sheet3中设定A列中不能输入重复的数值。
“允许”选择自定义，勾选忽略空值，公式输入=COUNTIF(A:A,A1)=1。
【逻辑函数】3. 使用IF函数，对Sheet1中的“学位”列进行自动填充。要求：填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）
在G3单元格中输入=IF(F3="博士研究生","博士",IF(F3="硕士研究生","硕士",IF(F3="大学本科","学士")))，然后进行自动填充。
【时间函数】4. 使用时间函数和数组公式，对Sheet1中“进厂工作时年龄”列进行计算，计算公式：进厂工作时年龄=进厂工作时日期年份-出生日期年份。
选中I列，在公式编辑栏中输入=YEAR(H3:H94)-YEAR(E3:E94)，然后同时摁Ctrl+Shift+Enter。
【闰年公式】5. 判断出生年份是否闰年，将判断结果（“是”或“否”）填入“是否闰年”列中。
在J3单元格中输入=IF(OR(MOD(YEAR(E3),400)=0,AND(MOD(YEAR(E3),4)=0,MOD(YEAR(E3),100)<>0)),"是","否")，然后进行自动填充。
【数据库函数】6. 利用数据库函数统计六分厂30岁以上（截止2010-03-31，即1980-04-01前出生）具有博士学位的女性研究员人数，将结果填入Sheet1的N12单元格中。
在N12单元格中输入=DCOUNTA(A2:K94,7,N4:R5)，然后摁回车。
DCOUNTA(database, field, criteria)
【排序函数】7. 使用RANK函数对进厂工作日期进行排序，先进厂的排前面（从1开始），结果填入“厂龄排序”列。
在K3单元格中输入=RANK(H3,$H$3:$H$94,1)，注意绝对引用，然后进行自动填充。
第九题 停车情况记录表
【闰年公式】2. 在Sheet4的B1单元格中输入公式，判断当前年份是否为闰年，结果为TRUE或FALSE。
闰年定义：年数能被4整除而不能被100整除，或者能被400整除的年份。
在Sheet4的B1单元格中输入=IF(OR(MOD(YEAR(NOW()),400)=0,AND(MOD(YEAR(NOW()),4)=0,MOD(YEAR(NOW()),100)<>0)),TRUE,FALSE)。
【查询函数】3. 使用HLOOKUP函数，对Sheet1“停车情况记录表”中的“单价”列进行填充。
在C9单元格输入=HLOOKUP(B9,$A$2:$C$3,2,0)，注意绝对引用，然后进行自动填充。
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
【数组公式】4. 在Sheet1中，使用数组公式计算汽车在停车库中的停放时间。
在“停放时间”列输入=E9:E39-D9:D39，然后同时摁Ctrl+Shift+Enter。
【求和公式】5. 使用函数公式，对“停车情况记录表”的停车费用进行计算。
在G9单元格输入=IF(HOUR(F9)<1,1,IF(MINUTE(F9)>15,HOUR(F9)+1,HOUR(F9)))*C9，然后进行自动填充。
HOUR(serial_number)
MINUTE (serial_number)
【统计函数】6. 使用统计函数，对Sheet1中的“停车情况记录表”根据下列条件进行统计。
在J8单元格输入=COUNTIF(G9:G39,">=40")，然后摁回车。
在J9单元格输入=MAX(G9:G39)，然后摁回车。
第十题 温度情况表
【逻辑函数】2. 在Sheet5中，使用函数，根据A2单元格中的身份证号码判断性别，结果为“男”或“女”，存放在B2单元格中。
在B2单元格中输入=IF(MOD(MID(A2,17,1),2)=1,"男","女")，然后摁回车。
MID(text,start_num,num_chars)
【逻辑函数】3. 使用IF函数，对Sheet1“温度情况表”中的“温度较高的城市”列进行填充，填充结果为城市名称。
在D3单元格中输入=IF(B3-C3>0,"杭州","上海")，然后摁回车。
【数组公式】4. 使用数组公式，对Sheet1“温度情况表”中的相差温度值（杭州相对于上海的温差）进行计算，并将结果保存在“相差温度值”列中。
选中“相差温度值”列，在公式编辑栏输入=B3:B17-C3:C17，然后同时摁Ctrl+Shift+Enter。
【统计函数】5. 使用函数，根据Sheet1“温度情况表”中的结果，对符合以下条件的进行统计。
在C19单元格中输入=MAX(B3:B17)。
在C20单元格中输入=MIN(B3:B17)。
在C21单元格中输入=MAX(C3:C17)。
在C22单元格中输入=MIN(C3:C17)。
【数组公式】6. 将Sheet1中的“温度情况表”复制到Sheet2中，在Sheet2中，重新编辑数组公式，将Sheet2中的“相差的温度值”中的数值取其绝对值（均为正数）。
将Sheet2“相差温度值”列数组公式重新在选中对应区域后编辑为=ABS(B3:B17-C3:C17)。
ABS(number)
