2013新手班练习题

zac0226

基础篇

1. =IF(SUM((B2:D2>=60)*1)<>3,"否","是")

复制代码

1

1. =IF(B2>89,"A",IF(B2>79,"B",IF(B2>69,"C",IF(B2>59,"D","E"))))

复制代码

2

1. =IF(COUNTIF($B$1:B2,B2)=1,SUMIF($B$2:$B$12,B2,$C$2:$C$12),"")

复制代码

3

1. =MAX((MONTH(A2:A11)<4)*B2:B11)

复制代码

4

1. =IF(SUM(ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1)*1),"有数字","无数字")

复制代码

5

1. =INDEX($A$1:$D$9,MATCH($G$1,$A$1:$A$9,),ROW(B2))
   
2. 工作证号数字编码（为什么只要数字啊，晕）=RIGHT(INDEX($A$1:$D$9,MATCH($G$1,$A$1:$A$9,),ROW(B4)),3)

复制代码

qq1194660920

=IF(SUM(--ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))),"有数字","无数字")

aimee_0324

2013新手班学员。
基础篇”E2”公式  =IF(MIN($B2:$D2)<60,"否","是")
一题 "C2"公式  =IF(B2>89,"A",IF(B2>79,"B",IF(B2>69,"C",IF(B2>59,"D","E"))))
三题 "C2"公式  =MAX(IF(MONTH(A2:A11)<4,B2:B11))

其他题暂时不会

hongqiqqq

进来练练手

1. 基础：=IF(SUM(--(B2:D2<60)),"否","是")（三键）
   
2. 1：=LOOKUP(B2,{0,"E";60,"D";70,"C";80,"B";90,"A"})
   
3. 2：=IF(COUNTIF(B$1:B2,B2)>1,"",SUMIF(B:B,B2,C:C))
   
4. 3：=MAX((MONTH(A2:A11)<4)*B2:B11)（三键）
   
5. 4：=IF(SUM(IFERROR(--MID(A2,ROW($1:$20),1),0)),"有","无")&"数字"（三键）
   
6. 5：=IF(RIGHT(F2)="码",RIGHT(VLOOKUP(G$1,A$1:D$9,ROW(),),3),VLOOKUP(G$1,A$1:D$9,ROW(),))

复制代码

所有公式都是第一个，需要下拉的只管下拉

禾火亦

基础

1. =IF(OR(B2<60,C2<60,D2<60),"否","是")

复制代码

1

1. =IF(B2<60,"E",IF(B2<70,"D",IF(B2<80,"C",IF(B2<90,"B","A"))))

复制代码

2

1. =IF(COUNTIF($B$2:B2,B2)=1,SUMIF(B:B,B2,C:C),"")

复制代码

3

1. =MAX((A2:A11<41365)*B2:B11)

复制代码

4

1. =IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0,"有数字","无数字")

复制代码

5

1. =IF(ROW(A2)=4,RIGHT(VLOOKUP(G$1,$A$1:$D$9,ROW(A2),),3),VLOOKUP(G$1,$A$1:$D$9,ROW(A2),))

复制代码

qq1194660920