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

涧水长流

【函初201203班】 A17-涧水长流上交作业 ，请老师批阅。
作业一
=IF(AND(C12>=60,D12>=60,E12>=60),"是","否")
=IF(OR(C12<60,D12<60,E12<60),"否","是")
=IF(C12>=60,IF(D12>=60,IF(E12>=60,"是","否"),"否"),"否")
=CHOOSE((AND(C12>=60,D12>=60,E12>=60)*1+1),"否","是")
作业二
=CHOOSE((IF(C28="男",60-D28,55-D28)>0)+1,0,IF(C28="男",60-D28,55-D28))
=MAX(IF(C28="男",60-D28,55-D28),0)
=IF(IF(C28="男",60-D28,55-D28)>0,IF(C28="男",60-D28,55-D28),0)
作业三
=VLOOKUP(F43,$A$43:$C$49,3)
=LOOKUP(F43,$A$43:$A$49,$C$43:$C$49)
=HLOOKUP(F43,{0,20,40,60,70,80,90;"庚","己","戊","丁","丙","乙","甲"},2)
=OFFSET($A$42,MATCH(F43,$A$43:$A$49,1),2,1,1)
=INDEX($A$43:$C$49,MATCH(F43,$A$43:$A$49,1),3)
作业四
辅助列(D58:D69)：=IF(ISODD(A58),B58*C58,"")，=SUM(D58:D69)
=SUM(ISODD({1;2;3;4;5;6;7;8;9;10;11;12})*{99;62;75;145;106;89;59;133;139;123;136;107}*{115;129;121;106;112;102;122;101;117;122;106;101})
=SUMPRODUCT(ISODD({1;2;3;4;5;6;7;8;9;10;11;12})*1,B58:B69,C58:C69)

水上漂123

【函初201203班]- A09-水上漂123
第一题：=IF(AND(C12>60,D12>60,E12>60),"是","否")
第二题：=IF(IF(C28="男",60-D28,55-D28)<0,0,IF(C28="男",60-D28,55-D28))
第三题：=IF(IF(C28="男",60-D28,55-D28)<0,0,IF(C28="男",60-D28,55-D28))
            =LOOKUP(F43,{0,"庚";20,"己";40,"戊";60,"丁";70,"丙";80,"乙";90,"甲"})
            =VLOOKUP(F43,{0,"庚";20,"己";40,"戊";60,"丁";70,"丙";80,"乙";90,"甲"},2,TRUE)
            =LOOKUP(F43,{0,20,40,60,70,80,90},{"庚","己","戊","丁","丙","乙","甲"})
            =IF(F43<20,"庚",IF(F43<40,"己",IF(F43<60,"戊",IF(F43<70,"丁",IF(F43<80,"丙",IF(F43<90,"乙","甲"))))))
第四题：=SUMPRODUCT(MOD($A$58:$A$69,2)*$B$58:$B$69*$C$58:$C$69)
=SUM(IF(MOD($A$58:$A$69,2)=1,$B$58:$B$69*$C$58:$C$69))数组公式
=SUM(D58:D69)利用辅助列，辅助列中公式=IF(MOD(A58,2)=1,B58*C58,0)

xuyouxm

【函初201203班】-A01-xuyouxm

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

复制代码

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

复制代码

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

复制代码

1. 04. 辅助列方式:
   
2. 01. =IF(ISODD(A58)=TRUE,B58*C58,"")
   
3. =SUM(D58:D69)
   
4. 02. =IF(MOD(A58,2)=1,B58*C58,"")
   
5. =SUM(D58:D69)
   
6. 不用辅助列方式:
   
7. =SUM(IF(MOD(A58:A69,2),(B58:B69)*(C58:C69),0))

复制代码

cixi1632003

作业1=IF(OR(C12<60,D12<60,E12<60),"否","是")
作业2=IF(AND((IF(C28="男",60,IF(C28="女",55))-D28)>0),E28,IF((IF(C28="男",60,IF(C28="女",55))-D28)<0,0))
【函初201203班]- A30-cixi1632003

sprite_chen

【函初201203班]- A02-sprite_chen

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

2、=IF(IF(C28="男",60-D28,55-D28)<=0,0,IF(C28="男",60-D28,55-D28))

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

4、=SUMPRODUCT(IF(MOD(A58:A69,2)=1,B58:B69,0),C58:C69)  数组公式，三键输入

hongqiqqq

1. =IF(AND(C12>=60,D12>=60,E12>=60),"是","否")
   
2. =IF(IF(C28="男",60-D28,55-D28)<=0,"0",IF(C28="男",60-D28,55-D28))
   
3. =LOOKUP(F43,A$43:B$49,C$43:C$49)
   
4. =SUM((MOD(A58:A69,2)=1)*B58:B69*C58:C69)(本题三键回车)

复制代码

已处理，请审批。
【函初201203班]- A11-hongqiqqq

闪闪星儿

【函初201203班]- A29-闪闪星儿

1、F12单元格中输入公式 =IF(AND(C12>=60,D12>=60,E12>=60),"是","否")，下拉完成。
2、E28单元格中输入公式 =IF(IF(C28="男",60-D28,55-D28)>0,IF(C28="男",60-D28,55-D28),0)，下拉完成。
3、G43单元格中输入公式 =LOOKUP(F43,$A$43:$A$49,$C$43:$C$49)，下拉完成。
4、D70单元格中输入数组公式 {=SUM(B58:B69*C58:C69*MOD(A58:A69,2))}

jerryzhouah

【函初201203班]- A10-jerryzhouah
作业一：=IF(AND(C12>=60,D12>=60,E12>=60),"是","否")再向下拉即可
作业二：=IF(C28="男",IF(D28<60,60-D28,0),IF(D28<55,55-D28,0))再向下拉即可
作业三：=LOOKUP(F43,{0,20,40,60,70,80,90,100},C$43:C$49)再向下拉即可
作业四：=IF(ISODD(A58),B58*C58,"")再向下拉即可
        =SUM(E58:E69)

zjf1969

【函初201203班】-A36-zjf1969

作业一

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

复制代码

作业二

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

复制代码

作业三

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

复制代码

作业四
输入

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

复制代码

后按shift+ctrl+enter

kouhogliang

作业一:f12=IF(OR(C12<60,D12<60,E12<60,E12="",D12="",C12=""),"否","是") 下拉
作业二:e28=IF(C28="男",IF((60-D28)<0,0,60-D28),IF((55-D28)<0,0,55-D28)) 下拉
作业三:g43=LOOKUP(F43,A$43:A$49,C$43:C$49) 下拉
作业四:辅助列:d58==IF(ISODD(A58)=TRUE,B58*C58,0) 下拉到d69
              d70=SUM(D58:D69)
【函初201203班】- A07-kouhogliang