【函数初级201203班】练习5---查找函数练习【已开贴】

libenwen2011

【函初201203班]-B27-libenwen2011-练习4
=SUMPRODUCT((B38:B46=H37)*(C38:C46=H38)*D38:D46)

wangjianff

练习一：=VLOOKUP(F3,{0,"F";40,"D";70,"C";80,"B";90,"A"},2)
练习二：=OFFSET($B$22,MATCH(I23,B$23:B$27,0),MATCH(H23,C$22:E$22,0))
练习三：{=INDEX(D38:D46,MATCH(H37&H38,B38:B46&C38:C46))}
【函初201203班】- D31-wangjianff

chenrongwei

,=LOOKUP(F6,$B$3:$C$7)
,=INDEX($C$23:$E$27,MATCH(I23,$B$23:$B$27),MATCH(H23,$C$22:$E$22))
{=INDEX($D$38:$D$46,MATCH(1,($I$37=B38:B46)*($I$38=C38:C46),0),1)}
b32chenrongwei

jerryzhouah

【函初201203班]- A10-JERRYZHOUAH
练习一：=LOOKUP(F3,{0;40;70;80;90;100},$C$3:$C$7)
练习二：=HLOOKUP(H23,$B$22:$E$27,MATCH(I23,$B$22:B27,),)
练习三：{=INDEX($D$38:$D$46,MATCH(1,($B$38:$B$46=H37)*($C$38:$C$46=H38),0))}

lfwzh0059

老师好，练习3和4没有赶上做，非常抱歉
练习5中的练习一：想了很长时间不知道怎么把成绩列中的0～40变成数值，最后只好利用分列方法把原成绩分成两列成绩1和成绩2分别为0和40，依次为40和70，70和80，80和90，90和100，然后利用函数=VLOOKUP(F3,$I$2:$K$7,3)，i列和k列分别为成绩1和成绩2
练习二=VLOOKUP(I23,$B$23:$E$27,MATCH(H23,$C$22:$E$22,0)+1)，结果出来后只有福特引擎的价值不对，其余的都对，也挺纳闷的，请老师看看
联系三=LOOKUP(1,0/(B38:B46&C38:C46=H37&H38),D38:D46)

lw2294

数组公式
=VLOOKUP(H37&H38,IF({1,0},$B$38:$B$46&$C$38:$C$46,$D$38:$D$46),2,0)

sdexcel

【函初201203班]- A34-sdexcel;

很是惭愧只会前面的两个：
1：=IF(F3<40,"F",IF(F3<70,"D",IF(F3<80,"C",IF(F3<90,"B",IF(F3<=100,"A")))))
2：=VLOOKUP(I23,$B$23:$E$27,MATCH(H23,$C$22:$E$22,0)+1,FALSE)

寂寞深水鱼

【函数初级201203班】-F04-寂寞深水鱼

1. =VLOOKUP(H37&H38,IF({1,0},B37:B46&C37:C46,D37:D46),2,)   

复制代码

1. =SUM((B38:B46=H37)*(C38:C46=H38)*D38:D46)   

复制代码

1. =SUMPRODUCT((B38:B46=H37)*(C38:C46=H38)*D38:D46)

复制代码

1. =SUMIFS(D38:D46,B38:B46,H37,C38:C46,H38)

复制代码

1. =LOOKUP(,0/((B38:B46=H37)*(C38:C46=H38)),D38:D46)

复制代码

再就业

【函初201203班]- D23-再就业：{=VLOOKUP(H37&H38,IF({1,0},B38:B46&C38:C46,D38:D46),2,0)}

Laphe

=LOOKUP(F3,{0,40,70,80,90},{"F","D","C","B","A"})
=INDEX($C$23:$E$27,MATCH(I23,$B$23:$B$27,),MATCH(H23,$C$22:$E$22,))
第三题不会
【函初201203班]- B33-Laphe;