二级软件计算公式

1、第1题总分和平均分1.1 数组公式计算总分：选中区域F2:F39,输入“=C2:C39+D2:D39+E2:39"，然后同时按组合键Shift+Ctrl+Enter,公式编辑器显示=C2:C39+D2:D39+E2:39。计算平均分：选中区域G2:G39,输入“=F2:F39/3”，然后同时按组合键Shift+Ctrl+Enter,公式编辑器显示=F2:F39/3。1.2 RANK函数1.3 逻辑函数AND1.4 统计函数COUNTIF数学分数大于等于0,小于20的人数：=COUNTIF(Sheet1!$D$2:$D$39,"<20")数学分数大于等于20,

2、小于40的人数：=COUNTIF(Sheet1!$D$2:$D$39,"<40")-B2数学分数大于等于40,小于60的人数：=COUNTIF(Sheet1!$D$2:$D$39,"<60")-B2-B3数学分数大于等于60,小于80的人数：=COUNTIF(Sheet1!$D$2:$D$39,"<80")-B2-B3-B4数学分数大于等于80,小于100的人数：=COUNTIF(Sheet1!$D$2:$D$39,">=80")1.5 高级筛选1.6 数据透视表352.1时间函数YEAR=

3、Year(2008-11-15")-Year(C2)2.2REPLACE函数第2题姓名2.3逻辑函数IF和AND2.4统计函数COUNTIF2.5 高级筛选2.6 数据透视图第3题折扣表3.1 VLOOKUP查询函数画班参数衣服，；哪彳2FALSE=120搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值口默认情况下，表是以升序排序的Lookup_valu*需要在数据表首列进行搜索的值，Igkup/al比可以是数值、弓|用 或字符串讨菖结果=120有关通函数的黏助QD3.2 逻辑函数IF=IF(B11<100,$B$3,IF(B11<

4、;200,$B$4,IF(B11<300,$B$5, $B$6)3.3 使用公式计算=B11*D11*(1-E11)3.4 SUMIF 函数E1IMTF-0对满足条件的单元格求和Sum皿中用于求和计篁的安际单元格口如果省略，将使用区暧中的单元格汁算结果=0有关该函数的帮助出）确定取清3.5 高级筛选3.6 数据透视图第4题采购情况表4.1 数组公式公式编辑选中区域H3:H18,输入“=E3:E18*F3:F18*G3:G18"，然后同时按组合键Shift+Ctrl+Enter器显示=E3:E18*F3:F18*G3:G18。4.2 数据库函数DAVERAGE和DSUM4.3 统

5、计函数COUNTIF和COUNTBLANK4.4信息函数ISTEXT4.5 高级筛选4.6 数据透视表第5题停车价目表5.1 HLOOKUP函数5.2 计算时间在F9单元格输入公式：=E9-D95.3 IF函数与HOUR和MINUTE函数最后在编辑栏中的表达式后输入*C9,即得到每小时停车费，整个表达式为：=IF(HOUR(F9)=0,1,IF(MINUTE(F9)>=15,HOUR(F9)+1,HOUR(F9)*C95.4统计函数COUNTIF和MAX函数5.5 高级筛选5.6 数据透视图第6题教材订购情况表6.1 数组公式选中区域13:152,输入“=G3:G52*H3:H52&qu

6、ot;，然后同时按组合键Shift+Ctrl+Enter,公式编辑器显示=G3:G52*H3:H52。6.2 统计函数COUNTIF订购数量大于110,且小于850的书的种类数：=COUNTIF(G3:G52,"<850")-COUNTIF(G3:G52,“<=110")6.3 SUMIF函数=SUMIF(A3:A52K9,I3:I52)=SUMIF(A3:A52,K10,I3:I52)=SUMIF(A3:A52,K11,I3:I52)6.4 判断函数IF与逻辑函数AND和OR=IF(OR(AND(MOD(A2,4)=0,MOD(A2,100)<

7、>0),MOD(A2,400)=0),"闰年","平年")6.5 高级筛选6.6 数据透视表第7题房产销售表7.1 使用公式选才iI3单元格，输入公式：=F3*G37.2 使用数组公式选中区域J3:J26,输入“=H3:H26*I3:I26"，然后同时按组合键Shift+Ctrl+Enter,公式编辑器显示=H3:H26*I3:I26。7.3 SUMIF函数=SUMIF(Sheet1!K3:K26,A3,Sheet1!I3:I26)=SUMIF(Sheet1!K3:K26,A4,Sheet1!I3:I26)=SUMIF(Sheet1!K3

8、:K26,A5,Sheet1!I3:I26)=SUMIF(Sheet1!K3:K26,A6,Sheet1!I3:I26)7.4 RANK函数7.5 高级筛选7.6 数据透视图题温度情况表8.1 逻辑函数IF8.2 数组公式选中区域E3:E17,输入“=B3:B17-C3:C17"，然后同时按组合键Shift+Ctrl+Enter,公式编辑器显示=B3:B17-C3:C17。8.3 统计函数MAX和MIN杭州最高气温:杭州最低气温:上海最高气温：=MAX(C3:C17)上海最低气温：=MIN(C3:C17)8.4 数组公式和绝对值函数ABS将Sheetl中的内容复制到Sheet2,选中

9、E3:E17,输入如下公式：“=ABS(B3:B17-C3:C17)Shift+Ctrl+Enter。8.5 高级筛选8.6 数据透视表第9题员工信息表9.1 REPLAC函数9.2 时间函数YEAR与TODAY计算年龄：=YEAR(TODAY()-YEAR(E3)计算工龄：=YEAR(TODAY()-YEAR(G3)9.3 统计函数COUNTIF高级工程师人数：=COUNTIF(I66,"高级工程师")工龄大于等于10年的人数：=COUNTIF(H3:H66,">=10")9.4 逻辑函数IF9.5 高级筛选9.6 数据透视图第10题公务员考试成

10、绩表10.1 逻辑函数IF选中区域H3,输入=IF(G3="博士研究生","博士”,IF(G3="硕士研究生","硕士",IF(G3="本科"，"学士"，"无")10.2 数组公式选中区域J3:J18,输入=13:118/3*0.6,然后同时按组合键Shift+Ctrl+Enter,公式编辑栏显示=13:118/3*0.6选中区域L3:L18,输入=K3:K18*0.4,然后同时按组合键Shift+Ctrl+Enter,公式编辑栏显示=K3:K18*0.4选中区域M

11、3:M18,输入=J3:J18+L3:L18,然后同时按组合键Shift+Ctrl+Enter,公式编辑栏显示=J3:J18+L3:L1810.3 修改数组公式选中区域J3:J18,将=I3:I18/3*0.6改为=I3:I18/2*0.6,然后同时按组合键Shift+Ctrl+Enter,公式编辑栏显示=I3:I18/2*0.610.4 RANK函数10.5 高级筛选10.6 数据透视图第11题公司员工人事信息表11.1 UPPER函数（将文本转换为大写）11.2 文本函数MID和时间函数YEAR与NOW=YEAR(NOW()-MID(G3,7,4)11.3 数据库函数DGET11.4文本函

12、数EXACT,判断两个文本值是否完全相同11.511.6自动筛选数据透视表第12题零件检测结果表12.1 数组公式12.2 IF函数12.3 统计函数COUNTIF=COUNTIF（E3:E50,"合格"）/（COUNTIF（E3:E50,"合格"）+COUNTIF（E3:E50,"不合格"）单元将格式数字 对齐 字体 边框图案 保护分类©:示例12.00讨期间分数学本殊定 SA日时枭科文炼自数 比记 义小数位数：口使用千位分隔符。他负数国）:(1234 10) (1234 10)1234.10-1234.10数值格式用于一

13、般数字的表示口货币和会讨格式则提供货币值计篁的专用格式h通道J取消12.4 文本函数FIND函数参数Finn"10*to tally hqp r二25返回一个字符串在另一个字符串中出现的起始位置区分大小写）Findtext要查找的字符串用双引号催示空串）可匹配Within_text中的第一个字符,不能使用通配符浒算结果=26有夷该函数的帮助QD确定取消12.5 自动筛选12.6 数据透视表第13题企业销售产品清单13.1VLOOKUP 函数画班参数fxvinoKV?Lq okup_value*AD1*广产型号”产用2FALSE="卡特扫描枪r回选定单元格的值口默认情况下，表

14、是以升序排序的搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返Lookup_valu*需要在数据表首列进行搜索的值，Igkup/al比可以是数值、弓|用或字符串计菖结果=有关通函数的帮助H)卡特扫描枪I口确定 j 取消 .厂产品型号”产由3FALSE=36E回选定单元格的值口默认情况下，表是以升序排序的搜索表区蹴首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返Lookup531M需要在数据表首列进行搜索的值，Igkup,ns可以是数值，引用或字符串讨篁结果=368百天邃函数的帮助0D13.2数组公式13.3SUMIF函数13.4 RANK函数13.5

15、高级筛选13.6 数据透视图第14题通讯费年度计划表14.1 VLOOKUP函数14.2 取整函数INT和时间函数DAYS360EAYS360Start date 回：4End dat« F4回二© =381393m533Method FALSE国|二FALSE=3S9按诲年360天返回两个日期间相差的天数（每月30天）Start_dat»5tart_data和enddte是要计算天数差值的起it日期讨篁结果=有关该函数的帮助0D369.00确定取消=INT(DAYS360(E4,F4,FALSE)14.3 数组公式14.4 求和函数SUM和文本函数TEXT在C2

16、单元格中输入公式“=SUM(H4:H26)”，按回车键确认。在F2单元格中输入公式“=TEXT(INT(C2),"dbnum2")&IF(INT(C2)=C2,"元整","元")"，按回车键确认。14.5 自动筛选14.6 数据透视表第15题图书订购信息表15.1 文本函数MID和IF函数15.2 统计函数COUNTBLANK15.3 使用公式=D3*$L$3+E3*$L$4+F3*$L$5+G3*$L$615.4 统计函数COUNTIF15.5 自动筛选15.6 数据透视图第16题学生成绩表116.1REPLACE

17、函数16.2数组公式16.3IF函数16.4数据库函数函数参数XTIMIlf二92返回满足给定条件的数据库中记录的字段例）中数据的最大值。口式abas%构成列表或数据库的单元格区域口数据库是一系列相关的数据列表汁算结果=92有关该函数的帮助出）确定取消16.5高级筛选16.6数据透视表36数字 对齐 字体 边椎 图案 保护 分类©:示例1币计期间分数学本殊定货会日时且科莫特自数 比记 义函数参数常规单元格格式不包含任何特定 的数字格式*确定取消第17题学生成绩表217.1 数组公式17.2 文本函数MID选中C3:C57单元格，右键一设置单元格格式，选择常规。点击确定。单元招招式从文

18、本字符串中指定的起始位置起返回指定长度的字符选备从中提取字符串的文本字符串讨篁结果=1有关该I函数的帮助QD确定取消4017.3统计函数COUNTIF和SUMIF=SUMIF(C3:C57,1,J3:J57)/N217.4财务函数FV和PV17.5高级筛选17.6数据透视表第18题学生成绩表318.1 数组公式与REPLACE选中B3:B30单元格，单击插入函数单击确定，最后按组合键Shift+Ctrl+Enter,公式编辑器显示=REPLACE(A3:A30,5,0,5)18.2 IF函数与逻辑函数AND结果1=IF(AND(D3="男",E3<14),"

19、合格",IF(AND(D3="女",E3<16),"合格","不合格")结果2=IF(AND(D3="男",G3>7.5),"合格",IF(AND(D3="女",G3>5.5),"合格","不合格")18.3 统计函数MAX和COUNTIF函数参数区IrmrwTTF计篁某个区域中满足给定条件的单元格数目R血金要讨算其中非空单元格数目的区域讨菖结果=19有关该函数的帮助出）18.4 财务函数PMT和IPMT4143函数参数TPMT=-4023.760032返回在定期