【函数初级201203班】第二讲D组作业上交帖【已开贴】

水吉果果

1、本贴是回贴仅作者可见，直接跟贴回复即可。
2、上交时直接贴公式，只上传附件的不予批改
      直接回复答案者必须注明【函初201203班】-组学号-论坛ID,如【函初201203班]- D01:yanyupingsheng;
3、截止时间2012年12月19日20：00
4、请误重复占楼，非本班同学跟贴回复者扣分

wangjianff

作业1：=CHOOSE((C12>=60)*(D12>=60)*(E12>=60)+1,"否","是")
作业2：=TEXT((C28="男")*60+(C28="女")*55-D28,"0;!0;0;")
作业3：=VLOOKUP(F43,$A$43:$C$49,3,1)
作业4：=SUMPRODUCT((MOD(A58:A69,2)=1)*(B58:B69)*(C58:C69))       【函初201203班】-D31-wangjianff

hwc2ycy

【函初201203班】-D25-hwc2ycy

作业一

1. =IF(AND(C12>=60,D12>=60,E12>=60),"是","否")

复制代码

1. =IF(AND(MIN(C12:E12)>=60,SUM(C12:E12)>=180),"是","否")

复制代码

1. =IF(AND(COUNT(C12:E12)=3,MIN(C12:E12)>=60),"是","否")

复制代码

1. =IF(COUNT(C12:E12)=3,IF(MIN(C12:E12)>=60,"是","否"),"否")

复制代码

作业二

1. =IF(D28>=((C28="男")*5+55),0,(C28="男")*5+55-D28)

复制代码

1. =MAX(0,(C28="男")*5+55-D28)

复制代码

作业三

1. =IF(F43<20,"庚",IF(F43<40,"己",IF(F43<60,"戊",IF(F43<70,"丁",IF(F43<80,"丙",IF(F43<90,"乙","甲"))))))

复制代码

1. =LOOKUP(F43,$A$43:$A$49,$C$43:$C$49)

复制代码

1. =VLOOKUP(F43,$A$43:$C$49,3,1)

复制代码

作业四
D58辅助单元公式：

1. =IF(ISODD(A58),B58*C58,0)

复制代码

辅助用法：

1. =SUM(D58:D69)

复制代码

非辅助用法：

1. =SUMPRODUCT(MOD(A58:A69,2),B58:B69*C58:C69)

复制代码

1. =MMULT(TRANSPOSE(MOD(A58:A69,2)),(B58:B69)*(C58:C69))

复制代码

1. =SUMPRODUCT(--ISEVEN(ROW(A58:A69)),B58:B69*C58:C69)

复制代码

stone1969

【函初201203班]- D03:stone1969(1109590007)
作业一

=IF(AND(C12>=60,D12>=60,E12>=60),"是","否")

=IF(AND(C13>=60,D13>=60,E13>=60),"是","否")

=IF(AND(C14>=60,D14>=60,E14>=60),"是","否")

=IF(AND(C15>=60,D15>=60,E15>=60),"是","否")

=IF(AND(C16>=60,D16>=60,E16>=60),"是","否")

=IF(AND(C17>=60,D17>=60,E17>=60),"是","否")

=IF(AND(C18>=60,D18>=60,E18>=60),"是","否")

=IF(AND(C19>=60,D19>=60,E19>=60),"是","否")

=IF(AND(C20>=60,D20>=60,E20>=60),"是","否")

=IF(AND(C21>=60,D21>=60,E21>=60),"是","否")

作业二

剩余工作年限

=IF(AND(C28="男",D28<=60),60-D28,IF(AND(C28="女",D28<=55),55-D28,0))

=IF(AND(C29="男",D29<=60),60-D29,IF(AND(C29="女",D29<=55),55-D29,0))

=IF(AND(C30="男",D30<=60),60-D30,IF(AND(C30="女",D30<=55),55-D30,0))

=IF(AND(C31="男",D31<=60),60-D31,IF(AND(C31="女",D31<=55),55-D31,0))

=IF(AND(C32="男",D32<=60),60-D32,IF(AND(C32="女",D32<=55),55-D32,0))

=IF(AND(C33="男",D33<=60),60-D33,IF(AND(C33="女",D33<=55),55-D33,0))

=IF(AND(C34="男",D34<=60),60-D34,IF(AND(C34="女",D34<=55),55-D34,0))

=IF(AND(C35="男",D35<=60),60-D35,IF(AND(C35="女",D35<=55),55-D35,0))

=IF(AND(C36="男",D36<=60),60-D36,IF(AND(C36="女",D36<=55),55-D36,0))

=IF(AND(C37="男",D37<=60),60-D37,IF(AND(C37="女",D37<=55),55-D37,0))

作业三

成绩等级

=LOOKUP(F43,A$43:B$49,C$43:C$49)

=LOOKUP(F44,A$43:B$49,C$43:C$49)

=LOOKUP(F45,A$43:B$49,C$43:C$49)

=LOOKUP(F46,A$43:B$49,C$43:C$49)

=LOOKUP(F47,A$43:B$49,C$43:C$49)

=LOOKUP(F48,A$43:B$49,C$43:C$49)

=LOOKUP(F49,A$43:B$49,C$43:C$49)

=LOOKUP(F50,A$43:B$49,C$43:C$49)

=LOOKUP(F51,A$43:B$49,C$43:C$49)

=LOOKUP(F52,A$43:B$49,C$43:C$49)

作业四

销售额

=IF(MOD(A58,2)=1,B58*C58,0)

=IF(MOD(A59,2)=1,B59*C59,0)

=IF(MOD(A60,2)=1,B60*C60,0)

=IF(MOD(A61,2)=1,B61*C61,0)

=IF(MOD(A62,2)=1,B62*C62,0)

=IF(MOD(A63,2)=1,B63*C63,0)

=IF(MOD(A64,2)=1,B64*C64,0)

=IF(MOD(A65,2)=1,B65*C65,0)

=IF(MOD(A66,2)=1,B66*C66,0)

=IF(MOD(A67,2)=1,B67*C67,0)

=IF(MOD(A68,2)=1,B68*C68,0)

=IF(MOD(A69,2)=1,B69*C69,0)

=SUM(D58:D69)

yanyupingsheng

【函初201203班】- D01:yanyupingsheng
作业一、=IF(OR(ISERROR(SMALL(C12:E12,3)),MIN(C12:E12)<=60),"否","是")
作业二、=IF(C28="男",IF(60-D28>=0,60-D28,0),IF((55-D28)>=0,55-D28,0))
作业三、=LOOKUP(F43,$A$43:$A$49,$C$43:$C$49)
作业四、=SUMPRODUCT((MOD(A58:A69,2)<>0)*B58:B69*C58:C69)

wuhairong2011

作业1

1. =IF(AND(C12>=60,D12>=60,E12>=60),"是","否")

复制代码

作业2

1. =IF((IF(C28="男",60,55)-D28)<0,0,IF(C28="男",60,55)-D28)

复制代码

作业3

1. =VLOOKUP(F43,$A$43:$C$49,3,1)

复制代码

1. =LOOKUP(F43,$A$43:$A$49,$C$43:$C$49)

复制代码

作业4

1. =SUMPRODUCT(MOD(A58:A69,2)*B58:B69,C58:C69)

复制代码

1. =SUM(MOD(A58:A69,2)*B58:B69*C58:C69)数组

复制代码

再就业

【函初201203班】-D23-再就业:
作业1：=IF(AND(C12>=60,D12>=60,E12>=60),"是","否")
作业2：=IF(AND(C28="男",D28<60),60-D28,0)+IF(AND(C28="女",D28<55),55-D28,0)
作业3：=LOOKUP(F43,$A$43:$B$49,$C$43:$C$49)
作业4：=SUMPRODUCT((MOD(A58:A69,2)>0)*D58:D69)
            D58=B58*C58

MYHERE131

练习一：=IF(COUNT(C12:E12)<>3,"否",IF(SUM((C12:E12>=60)*1)=3,"是","否"))
练习二：=IF(C28="男",MAX(60-D28,0),MAX(55-D28,0))
练习三：=LOOKUP(F43,{0,20,40,60,70,80,90},{"庚","己","戊","丁","丙","乙","甲"})
练习四：=SUM((MOD(A58:A69,2)=1)*(B58:B69)*(C58:C69))

beasour

【函初201203班]- D10:beasour
作业一：在F12中输入公式 =IF(SUM(C12:E12)>=180,"是","否")  ，下拉菜单至F21
作业二：在E28中输入公式 =IF(AND(D28>=60,C28="男"),0,IF(AND(D28>=55,C28="女"),0,IF(C28="男",60-D28,IF(C28="女",55-D28))))   ,下拉菜单至E37
作业三：在G43中输入公式 =LOOKUP(F43,$A$43:$C$49)，下拉菜单至G52
作业四：在D58中输入公式 =IF(MOD(A58,2)=1,B58*C58,"")，下拉菜单至D69;在D70中输入公式 =SUM(D58:D69)

xiaobai83

【函初201203班】-D34-xiaobai83
第二次作业：
作业一：=IF(ISBLANK(C12:E12),"否",IF(AND(C12>60,D12>60,E12>60),"是","否"))
作业二：=IF(IF(C28="男",60-D28,IF(C28="女",55-D28,0))<0,0,IF(C28="男",60-D28,IF(C28="女",55-D28,0)))
作业三：=VLOOKUP(F43,$A$43:$C$49,3,1)
作业四：=SUM(IF(MOD(A58:A69,2)=1,D58:D69,0))按Ctrl+Shift+Enter三键结束。