EXCEL公式答案
- 格式：doc
- 大小：34.00 KB
- 文档页数：5
一．3月份销售统计表2.产品名称=VLOOKUP(F3,A$3:B$10,2,FALSE)产品单价=VLOOKUP(F3,A$3:C$10,3,FALSE)3.销售金额={H3:H44*I3:I44}4.总销售额=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(B11<A$4,B$3,IF(B11<A$5,B$4,IF(B11<A$6,B$5,B$6)))4.合计=D11*B11*(1-E11)5.总采购量=SUMIF(A$11:A$43,I12,B$11:B$43)总采购金额=SUMIF(A$11:A$43,I12,F$11:F$43)三．采购情况表2.采购总额{=E3:E18*F3:F18*G3:G18}3.G25 =DA VERAGE(A2:H18,E2,J4:L5)G26 =DCOUNTA(A2:H18,A2,J9:L10)4.B14 =COUNTBLANK(B3:E12)B15 =COUNTIF(B3:E12,"Y")5.B21 =ISTEXT(B21)四．房产销售表2.房价总额=F3*G33.契税总额{=H3:H26*I3:I26}4.销售总额=SUMIF(Sheet1!K$3:K$26,A2,Sheet1!I$3:I$26)5.排名=RANK(B2,B$2:B$6)五．公司员工人事信息表2.新编号=UPPER(A3)3.年龄=YEAR(TODAY())-MID(G3,7,4)4.E31=DGET(A2:J27,C2,L3:M4)5.N11=EXACT(L12,M12)六．公务员考试成绩表2.学位=IF(G3="博士研究生","博士",IF(G3="硕士研究生","硕士",IF(G3="本科","学士","无")))3.笔试比例分{=(I3:I18/3)*60%}面试比例分{=K3:K18*40%}总成绩{=J3:J18+L3:L18}4.修改笔试比例分{=(I3:I18/2)*60%}5.排名=RANK(M3,M$3:M$18)七．教材订购情况表2.金额{=G3:G52*H3:H52}3.L2==COUNTIF(D3:D52,"高等教育出版社")L3=COUNTIF(G3:G52,">110")-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:C50}3.检测结果=IF(D3<4,"合格","不合格")4.K4=COUNTIF(D3:D50,0)K5=COUNTIF(E3:E50,"合格")/COUNTA(E3:E50)5.K9=FIND(J9,I9)九．升级后号码2.年龄=2008-YEAR(C2)3.号码升级=REPLACE(F2,5,0,8)4.大于等于40岁的男性=AND(B2>=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:D39}4.应付金额=IF(HOUR(F9)<1,1,IF(MINUTE(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:G26}5.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(B3>C3,"杭州","上海")3.相差温度值{=B3:B17-C3:C17}4.杭州最高气温=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:I57}3.级别=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(E3<14,"合格","不合格"),IF(E3<16,"合格","不合格"))结果2=IF(D3="男",IF(G3>7.5,"合格","不合格"),IF(G3>5.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:I24}4.考评=IF(J3>=350,"合格","不合格")5.语数都大于等于85=DCOUNT(A2:K24,,M2:N3)体育成绩大于90的女生=DGET(A2:K24,C2,M7:N8)体育男生平均分=DA VERAGE(A2:K24,I2,M12:M13)体育男生最高分=DMAX(A2:K24,I2,M12:M13)十七.医院病人护理统计表2.护理价格=VLOOKUP(E3,K$3:L$5,2,FALSE)3.护理天数{=G3:G30-D3:D30}4.护理费用{=F3:F30*H3:H30}5.N13=DCOUNTA(A2:I30,A2,K8:M9)N22=DSUM(A2:I30,I2,K17:K18)十八.优等生2.总分{=C2:C39+D2:D39+E2:E39}平均分{=F2:F39/3}3.排名=RANK(F2,F$2:F$39)4.优等生=AND(C2>A VERAGE(C$2:C$39),D2>A VERAGE(D$2:D$39),E2>A VERAGE(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,"<80")十九.员工信息表2.升级员工代码=REPLACE(B3,3,0,0)3.年龄=2009-YEAR(E3)工龄=2009-YEAR(G3)4.N3=COUNTIF(D3:D66,"男")N4=COUNTIF(I3:I66,"高级工程师")N5=COUNTIF(H3:H66,">=10")5.高级工程师=AND(H3>20,I3="工程师")二十.员工资料表2.出生日期=CONCA TENA TE(MID(E3,7,4),"年",MID(E3,11,2),"月",MID(E3,13,2),"日")3.职务补贴率=VLOOKUP(H3,A$2:B$6,2,FALSE)4.工作总额{=I3:I38*(1-J3:J38)}5.E2=SLN(B2,B3,B4*360)E3=SLN(B2,B3,B4*12)E4=SLN(B2,B3,B4)。
