EXCEL公式答案.doc

一3月份销售统计表2.产品名称=VLOOKUP(F3,A$3:B$10,2,FALSE) 产品单价=VLOOKUP(F3,A$3:C$10,3,FALSE)3.销售金额=H3:H44*I3:I444.总销售额=SUMIF(K$3:K$44,N3,L$3:L$44)5.总销售额排名=RANK(O3,O$3:O$5)二采购表 2.产品单价=VLOOKUP(A11,F$3:G$5,2,FALSE)3.折扣=IF(B11A$4,B$3,IF(B11A$5,B$4,IF(B11110)-COUNTIF(G3:G52,=850)4.支付总额=SUMIF(A$3:A$52,K8,I$3:I$52)5.闰年=IF(OR(AND(MOD(A2,4)=0,MOD(A2,100)0),MOD(A2,400)=0),闰年,平年)八零件检测结果 2.轮差=B3:B50-C3:C503.检测结果=IF(D3=40,B2=男)5.B2=COUNTIF(Sheet1!B2:B37,男) B3=COUNTIF(Sheet1!D2:D37,40)十停车情况记录表 2.单价=HLOOKUP(B9,A$2:C$3,2,FALSE)3.停放时间=E9:E39-D9:D394.应付金额=IF(HOUR(F9)=15,HOUR(F9)+1,HOUR(F9)*C95.J8=COUNTIF(G9:G39,=40) J9=MAX(G9:G39)十一.通讯费年度计划表 2.岗位标准=VLOOKUP(C4,K$4:L$12,2,FALSE)3.预计报销总时间=INT(F4-E4)/30)4.年度费用=D4:D26*G4:G265.C2=SUM(H4:H26) F2=TEXT(C2,dbnum2)十二.图书订购信息表 2.所属学院=IF(MID(A3,7,1)=1,计算机学院,电子信息学院)3.订书种类数=4-COUNTBLANK(D3:G3)4.订金金额=D3*$L$3+E3*$L$4+F3*$L$5+G3*$L$65.M9=COUNTIF(I3:I50,100)十三.温度情况表 2.温度较高的城市=IF(B3C3,杭州,上海)3.相差温度值=B3:B17-C3:C174.杭州最高气温=MAX(B3:B17) 杭州最低气温=MIN(B3:B17) 上海最高气温=MAX(C3:C17) 上海最低气温=MIN(C3:C17)5.相差温度值绝对值=ABS(B3:B17-C3:C17)十四.学生成绩表(等级) 2.总分=D3:D57+E3:E57+F3:F57+G3:G57+H3:H57+I3:I573.级别=MID(A3,8,1)4.N2=COUNTIF(C3:C57,1) N3=COUNTIF(J3:J57,=60) N4=SUMIF(C3:C57,1,J3:J57)/N25.B7=FV(B3,B5,B4,B2) E7=PV(E3,E4,E2)十五.学生成绩表(男女生结果) 2.新学号=REPLACE(A3:A30,5,0,5)3.结果1=IF(D3=男,IF(E314,合格,不合格),IF(E37.5,合格,不合格),IF(G35.5,合格,不合格)4.K4=MIN(E3:E30) K5=COUNTIF(F3:F30,合格)5.E2=PMT(B4,B3,B2) E3=IPMT(B4/12,9,B3*12,B2)十六.学生成绩表(语数英信体) 2.新学号=REPLACE(A3,1,0,2009)3.总分=E3:E24+F3:F24+G3:G24+H3:H24+I3:I244.考评=IF(J3=350,合格,不合格)5.语数都大于等于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)十七.医院病人护理统计表 2.护理价格=VLOOKUP(E3,K$3:L$5,2,FALSE)3.护理天数=G3:G30-D3:D304.护理费用=F3:F30*H3:H305.N13=DCOUNTA(A2:I30,A2,K8:M9) N22=DSUM(A2:I30,I2,K17:K18)十八.优等生 2.总分=C2:C39+D2:D39+E2:E39 平均分=F2:F39/33.排名=RANK(F2,F$2:F$39)4.优等生=AND(C2AVERAGE(C$2:C$39),D2AVERAGE(D$2:D$39),E2AVERAGE(E$2:E$39)5.数学分数=COUNTIF(Sheet1!D$2:D$39,20)-COUNTIF(Sheet1!D$2:D$39,0)=COUNTIF(Sheet1!D$2:D$39,40)-COUNTIF(Sheet1!D$2:D$39,20)=COUNTIF(Sheet1!D$2:D$39,60)-COUNTIF(Sheet1!D$2:D$39,40)=COUNTIF(Sheet1!D$2:D$39,80)-COUNTIF(Sheet1!D$2:D$39,60)=COUNTIF(Sheet1!D$2:D$39,=100)-COUNTIF(Sheet1!D$2:D$39,=10)5.高级工程师=AND(H320,I3=工程师)二十.员工资料表2.出生日期=CONCATENATE(MID(E3,7,4),年,MID(E3,11,2),月,MID(E3,13,2)