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

hhl1984

【函初201203班】-B25-hhl1984
作业一\F12=IF(AND(C12>60,D12>60,E12>60),"是","否"),下拉
作业二\E28=IF(AND(C28="男",D28<60),60-D28,IF(AND(C28="女",D28<55),55-D28,0)),下拉
作业三\G43=IF(F43<20,"庚",IF(F43<40,"已",IF(F43<60,"戊",IF(F43<70,"丁",IF(F43<80,"丙",IF(F43<90,"乙","甲")))))),下拉
作业四\D58=IF(MOD(A58,2),B58*C58,""),下拉到D69,然后求和D70=SUM(D58:D69)

552314637

【函初201203班】--B34--552314637
第一题：F12=IF(C12<60,"否",IF(D12<60,"否",IF(E12<60,"否","是")))
第二题：（没有解出来）只解到这一步=IF(C28="男",60-D28,IF(C28="女",55-D28,))还有条件怎么写都不对
第三题：G43=IF(F43<20,"庚",IF(F43<40,"己",IF(F43<60,"戊",IF(F43<70,"丁",IF(F43<80,"丙",IF(F43<90,"乙","甲"))))))
第四题：D58=IF(ISODD(A58),(B58*C58),0)最后=SUM(D58:D69)分了两步计算

石磊.

【函初201203班]- b05-石磊.
1. =IF(AND(C12>=60,D12>=60,E12>=60),"是","否")
2. =IF(IF(C28="男",60,55)-D28>0,IF(C28="男",60,55)-D28,0)
3. =LOOKUP(F43,$A$43:$C$49)
4. =SUMPRODUCT((B58:B69*C58:C69)*ISODD(ROW(1:12)))

深埋雪中

函初3.B17.深埋雪中
=IF(AND(C12>=60,D12>=60,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)
=SUM(IF(MOD(A58:A69,2)=1,B58:B69*C58:C69,0))组合键

福将杨

作业1.IF(AND(C12>=60,D12>=60,E12>=60),"是","否")
作业2.IF(AND(C28="女",D28<55),55-D28,IF(AND(C28="男",D28<60),60-D28,0))
作业3.方法1.VLOOKUP(F43,A$43:C$49,3,1)
         方法2.LOOKUP(F43,$A$43:$A$49,C$43:C$49)
作业4.在D58单元格输入公式ISODD(A60)*B60*C60  再下拉至D69单元格，再在第D70单元格输入公式SUM(D58:D69)

深蓝色的海洋

【函初201203班]- b12-深蓝色的海洋

作业一
         A、=IF(AND(C12>60,D12>60,E12>60),"是","否")
         B、=IF(COUNTIF(C12:E12,"<60")+COUNTIF(C12:E12,""),"否","是")

作业二
         =IF(OR(AND(C28="男",D28>=60),AND(C28="女",D28>=55)),"0",IF(C28="男",60-D28,55-D28))

作业三
         A、=IF(F43>=90,"甲",IF(F43>=80,"乙",IF(F43>=70,"丙",IF(F43>=60,"丁",IF(F43>=40,"戊",IF(F43>=20,"己","庚"))))))
        B、=IF(F43<20,"庚",IF(F43<40,"己",IF(F43<60,"戊",IF(F43<70,"丁",IF(F43<80,"丙",IF(F43<90,"乙","甲"))))))

作业四
         A、=SUMPRODUCT((MOD(A58:A69,2)=1)*B58:B69*C58:C69)
         B、=SUMPRODUCT(MOD(A58:A69,2)*B58:B69*C58:C69)

金牛绅士

【函初201203班】-B36-金牛绅士

选做
1、=IF(C6>AVERAGE($C$6:$E$15),"是",IF(D6>AVERAGE($C$6:$E$15),"是",IF(E6>AVERAGE($C$6:$E$15),"是","否")))
2、=IF(COUNTIF($B$21:$B$24,$B27)=0,"不存在",VLOOKUP($B27,$B$21:$E$24,COLUMN(B:B),0))
3、=IF(COUNTIF(C$36:C36,C36)=1,B36,"")
4、=IF(OR(AND((C50>=60),(D50>=60),(E50>=60)),AVERAGE(C50:E50)>=80),"良","")
5、=SUMPRODUCT((B77:B89={"人事部","生产部","账务部"})*(D77:D89="男")*E77:E89)
6.1，2=SUMIF($B$97:$B$115,$H97&"*",D$97:D$115)
6.3、=MAX(D97:D115)
必做
=IF(D6="男","A","B")&C6&TEXT(ROW(A1),"00")
  

aiwenaixuexi

201203-B37-aiwenaixuexi [函数初级201203班]第二讲作业 第一题: =IF(AND(C12>=60,D12>=60,E12>=60),"是","否") 下拉

第二题:=IF(C28="男",IF(D28<=60,60-D28,"0"),IF(D28<=55,55-D28,"0"))下拉

第三题=IF(F43>=90,$C$49,IF(F43>=80,$C$48,IF(F43>=70,$C$47,IF(F43>=60,$C$46,IF(F43>=40,$C$45,IF(F43>=20,$C$44,$C$43))))))

第四题先在辅助列用=IF(ISODD(A58),B58*C58,"")求出奇数行的金额

然后用SUM选中辅助列数据区域求和,得出答案

老师对不起（刚才交错地方了重新交作业）

M_BC

作业一：
F12=IF(C12>=60,IF(D12>=60,IF(E12>=60,"是","否"),"否"),"否")

作业二：
E28=IF(C28="男",IF(D28<60,60-D28,"0"),IF(D28<55,55-D28,"0"))

作业三：
G43=LOOKUP(F43,A$43:B$49,C$43:C$49)

作业四：
D58=IF(MOD(A58,2)=1,B58*C58,)
D70=SUM(D58:D69)

Laphe

【作业一】

F12=IF(OR(C12<60,D12<60,E12<60)=FALSE,"是","否")

---------------------------------------------------------------------------------
【作业二】

E28=IF(IF(MATCH(C28,{"男","女"})=1,60-D28,55-D28)>0,IF(MATCH(C28,{"男","女"})=1,60-D28,55-D28),0)
--------------------------------------------------------------------------------------------------------------------------------------------
【作业三】

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

------------------------------------------------------------------------------------------------
【作业四】
=IF(ISODD(A58)=TRUE,B58*C58,0)
D70=SUM(D58:D69)

-----------------------------------------------------------------------------------------------------

【函初201203班]- B33-Laphe