第一题 学生成绩
第一题 学生成绩【数组公式】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,"110")-COUNTIF($G$3:$G$52,">850"),注意绝对引用。
统计函数2【求和函数】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,"男")。
统计函数1在Sheet2表B3单元格中输入=COUNTIF(Sheet1!D2:D37,">40")。
统计函数2第五题 采购情况表
第五题 采购情况表【数据查重】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)。
countblank函数在Sheet3表B15单元格中输入=COUNTIF(B2:E11,"Y")。
countif函数【文本函数】6. 使用函数,对Sheet3中的B21单元格中的内容进行判断,判断其是否为文本,如果是,结果为“TRUE”;如果不是,结果为“FALSE”,并将结果保存在Sheet3中的B22单元格当中。
在Sheet3表B22单元格中输入=ISTEXT(B21)。
ISTEXT(Value)。
istext函数