[201401函初班]第三讲作业B组上交帖【已开帖】

jio1ye

第一题，下拉，下拉，下拉

1. =IF(ISODD(LEFT(C7,17)),"男","女")

复制代码

1. =IF(ISEVEN(LEFT(C7,17)),"女","男")

复制代码

1. =IF(MOD(MID(C7,14,4),2),"男","女")

复制代码

第二题，下拉，下拉

1. =IF(C20=5,0,IF(C20=7,2,IF(C20=9,4,C20)))

复制代码

1. =IF(OR(C20=5,C20=7,C20=9),C20-5,C20)

复制代码

第三题，公式，数组公式，数组公式

1. =COUNTA(C35:C45)-COUNTIFS(D35:D45,"B",E35:E45,"中专")

复制代码

1. =COUNTA(C35:C45)-SUM((D35:D45="B")*(E35:E45="中专"))

复制代码

1. =SUM(--NOT((D35:D45="B")*(E35:E45="中专")))

复制代码

tt89004808

【2014函数初级班】-B019-tt89004808
1.

1. =IF(MOD(MID(C7,15,3),2),"男","女")
   
2. =IF(MOD(IF(LEN(C7)=15,MID(C7,15,1),MID(C7,17,1)),2),"男","女")

复制代码

2.

1. =IF(OR(C20=5,C20=0),0,IF(OR(C20=2,C20=7),2,IF(OR(C20=4,C20=9),4,C20)))

复制代码

3.

1. =SUM(--NOT(D35:D45&E35:E45="B中专"))
   
2. =COUNTA(D35:D45)-COUNTIFS(D35:D45,"B",E35:E45,"中专")

复制代码

pang1688

b021-pang1688交作业：
一、
1、=IF(ISEVEN(MID(C7,15,3)),"女","男")
2、=IF(MOD(MID(C7,15,3),2),"男","女")
3、=IF(ISEVEN(IF(LEN(C7)=15,RIGHT(RIGHT(C7,2)),LEFT(RIGHT(C7,2)))),"女","男")
二、
=IF(OR(C20=0,C20=5),0,IF(OR(C20=2,C20=7),2,IF(OR(C20=4,C20=9),4,C20)))
三、
1、=SUM(--NOT((D35:D45="B")*(E35:E45="中专")))数组
2、=COUNTIF(D35:D45,"*")-SUMPRODUCT((D35:D45="B")*(E35:E45="中专"))

tracyliunana

2014函数初级班】-b007-tracyliunana--------不好意思，我这里不压缩表格，上传不了，附件添加只能看见压缩文件，所以我又压缩文件了

我真的没醉

交作业喽！
第一题:　
　　　　法１：=IF(ISEVEN(MID(C7,15,3)),"女","男")
           法２：=IF(MOD(MID(C7,15,3),2),"男","女")
           法３:=IF(LEN(C7)=15,IF(MOD(RIGHT(C7),2),"男","女"),IF(MOD(MID(C7,17,1),2),"男","女"))

第二题：=IF(OR(C21=0,C21=5),0,IF(OR(C21=2,C21=7),2,IF(OR(C21=4,C21=9),4,C21)))

第三题：
       法１:=SUM(--(NOT((D35:D45="B")*(E35:E45="中专"))))

　　　　法２：=ROWS(D35:D45)-COUNTIFS(D35:D45,"B",E35:E45,"中专")
　　学委大人，这样写行吗　=SUM(1-{0;1;0;0;0;0;0;1;1;0;0})

心情奔放

第三讲作业答案：
1.     =IF(ISEVEN(MID(C7,15,3)),"女","男")
        =IF(ISEVEN(MID(C7,IF(LEN(C7)=18,17,IF(LEN(C7)=15,15)),1)),"女","男")
        =IF(NOT(ISODD(MID(C7,15,3))),"女","男")
2.    =IF(OR(C20=0,C20=5),0,IF(OR(C20=2,C20=7),2,IF(OR(C20=4,C20=9),4,C20)))
3.    =SUM(--NOT((D33:D43="B")*(E33:E43="中专")))    三键
       =SUM(1-{0;1;0;0;0;0;0;1;1;0;0})

玉缘兔

【2014函数初级班】-B013玉缘兔
1.=IF((-1^MID(C7,IF(LEN(C7)=18,17,15),1))>0,"女","男")
=IF(MOD(MID(C7,IF(LEN(C7)=18,17,15),1),2),"男","女")
=IF(MOD(LEFT(RIGHT(C7,1+(LEN(C7)=18))),2),"男","女")
2.=IF(C20=5,0,IF(C20=7,2,IF(C20=9,4,C20)))
3.=COUNTA(D35:D45)-COUNTIFS(D35:D45,"B",E35:E45,"=中专")
=SUMPRODUCT(--NOT(D35:D45&E35:E45="B中专"))

coreluo

班长统计作业记号................∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽

leila84

第二题if（OR(C20=4,C20=9),4,IF(OR(C20=2,C20=7),2,IF(OR(C20=5,C20=0),0,C20)))

Saint_Zuo

1 、
=IF(ISODD(MID(C7,15+2*(LEN(C7)=18),1)),"男","女")
=IF(ISODD(LEFT(RIGHT(C7,1+(LEN(C7)=18)))),"男","女")
=IF(ISODD(LEFT(C7,LEN(C7)-1*(LEN(C7)=18))),"男","女")
2、
=IF(OR(C20=0,C20=5),0,IF(OR(C20=2,C20=7),2,IF(OR(C20=4,C20=9),4,C20)))
3、
=COUNTA(D35:D45)-COUNTIFS(D35:D45,"B",E35:E45,"中专")
=SUMPRODUCT(--NOT(NOT(NOT(D35:D45="B")+NOT(E35:E45="中专"))))