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

tianyiyi

D组童鞋交作业了
1.作业请注明学号+ID 如：【2014函数初级班】-D006-云子。
2.作业请直接贴答案，压缩包下载太累{:021:}
3.作业上交截止时间：2014年3月27日 晚上6:30点
第三讲作业贴：http://www.excelpx.com/thread-321463-1-1.html

蓝粆

交作业 有BB拿~~{:091:}走过路过的多发点~~过了这个村就没这个店了~~
一、

1. =TEXT(-1^MID(C7,15,3),"女;男")

复制代码

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

复制代码

1. =LOOKUP(MOD(MID(C7,15,3),2),{0,1},{"女","男"})

复制代码

二、

1. =IF(OR(C20={5,7}),-5,)+C20

复制代码

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

复制代码

三、
三键

1. =SUM(IF((E35:E45<>"中专")+(D35:D45<>"B"),1))

复制代码

1. =SUM(IF(NOT(E35:E45="中专")+(D35:D45<>"B"),1))

复制代码

云子

蓝妹动作太快了
1

1. =TEXT(-1^MID(C7,15,3),"女;男")

复制代码

1. =IF(ISODD(MID(C7,15,3)),"男","女")

复制代码

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

复制代码

2

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

复制代码

3

1. <span style="line-height: 1.5;">=SUM(1-(D35:D45=D36)*(E35:E45=E36))</span>

复制代码

1. =SUM(--NOT((D35:D45=D36)*(E35:E45=E36)))

复制代码

皮皮2

【2014函数初级班】-D004-皮皮2
第一题：

1. =IF(ISODD(MID(C7,15,3)),"男","女")
   
2. =IF(MOD(RIGHT(LEFT(C7,17)),2),"男","女")
   
3. =IF(MOD(IF(LEN(C7)=15,MID(C7,15,1),MID(C7,17,1)),2)=1,"男","女")
   
4. =IF(LEN(C7)=15,IF(MOD(RIGHT(C7,1),2)>0,"男","女"),IF(MOD(MID(C7,17,1),2)>0,"男","女") )

复制代码

第二题：

1. =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. =SUMPRODUCT(--NOT((D35:D45="B")*(E35:E45="中专")))
   
3. =SUMPRODUCT(1-((D35:D45="B")*(E35:E45="中专")))
   
4. =SUMPRODUCT(--NOT((D35:D45=MIDB(B33,5,1))*(E35:E45=MID(B33,7,2))))

复制代码

复制代码

沐颜

1.

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

复制代码

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

复制代码

1. =IF(MOD(RIGHT(LEFT(C7,17)),2),"男","女")

复制代码

2.

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

复制代码

3.

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

复制代码

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

复制代码

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

复制代码

珺珺妈

【2014函数初级班】-D0018-珺珺妈

1. =IF(ISODD(MID(C7,15,3)),"男","女")

复制代码

1. =IF(ISEVEN(MID(C7,15,3)),"女","男")

复制代码

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

复制代码

1. =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="中专")))

复制代码

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

复制代码

就是我

【2014函数初级班】-D002-就是我

1. 第一题 方法1 =IF(MOD(MID(C7,15,3),2),"男","女")
   
2. 第一题 方法2 =IF(ISEVEN(MID(C7,15,3)),"女","男")
   
3. 第一题 方法3 =IF(ISODD(MID(C7,15,3)),"男","女")
   
4. 第一题 方法4 =IF(MOD(RIGHT(LEFT(C7,17),1),2),"男","女")
   
5. 第一题 方法5 =IF(MOD(MID(C7,15+(LEN(C7)=18)*2,1),2),"男","女")
   
6. 第二题 =IF(OR(C20=0,C20=5),0,IF(OR(C20=2,C20=7),2,IF(OR(C20=4,C20=9),4,C20)))
   
7. 第三题 方法1 =SUM(--NOT((D35:D45="B")*(E35:E45="中专")))
   
8. 第三题 方法2 =SUM(1-((D35:D45="B")*(E35:E45="中专")))
   
9. 第三题 方法3 =COUNTA(D35:D45)-SUM((D35:D45="B")*(E35:E45="中专"))
   
10. 第三题 方法4 =SUM(--(D35:D45&E35:E45<>"B"&"中专"))
    

复制代码

xhwts

【2014函数初级班】-D008-xhwts
一题：
=IF(ISEVEN(MID(C7,15,3)),"女","男"),=IF(MOD(RIGHT(LEFT(C7,17),1),2)=1,"男","女"),=TEXT(MOD(RIGHT(LEFT(C7,17),1),2),"男;;女")
二题：
=IF(OR(0=C20,5=C20),"0",IF(OR(2=C20,7=C20),"2",IF(OR(4=C20,9=C20),"4",C20)))
三题：
=SUMPRODUCT(NOT((D35:D45="B")*(E35:E45="中专"))*1)
数组=SUM(NOT((D35:D45="B")*(E35:E45="中专"))*1)数组

zac0226

【2014函数初级班】-D015-zac0226
1

1. =IF(ISEVEN(MID(C7,MIN(17,LEN(C7)),1)),"女","男")

复制代码

1. =IF(ISEVEN(MID(C7,15,3)),"女","男")

复制代码

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

复制代码

2

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

复制代码

3

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

复制代码

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

复制代码

13641096715

一、1.三种方法判断性别
=TEXT(-1^MID(CLEAN(C7),15,3),"女;男")
=IF(MOD(-LEFT(RIGHT(C7,2)),2),"l男","女")
=IF(ISODD(MID(CLEAN(C9),15,3)),"男","女")
=IF(ISEVEN(MID(CLEAN(C7),15,3)),"女","男")
=IF(MOD(MID(C7,IF(LEN(CLEAN(C7))=15,15,17),1),2),"男","女")
=IF(MOD(RIGHT(LEFT(CLEAN(C7),17),3),2),"男","女")
=IF(MOD(MID(CLEAN(C7),15,3),2),"男","女")
二、1.使用if函数
=IF(ISNUMBER(FIND(0,C20)),0,IF(ISNUMBER(FIND(5,C20)),0,IF(ISNUMBER(FIND(2,C20)),2,IF(ISNUMBER(FIND(7,C20)),2,IF(ISNUMBER(FIND(4,C20)),4,IF(ISNUMBER(FIND(9,C20)),4,C20))))))
三、"1.除B组别中专以外的人数(两种方法其中一种必须含有not）                       
{=SUM(--NOT((D35:D45="B")*(E35:E45="中专")))}
{=SUM(1-((D35:D45="B")*(E35:E45="中专")))}