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

一杯清荼

【函初201203班】-C08-一杯清荼

1. =LOOKUP(1,0/(H38=C38:C46),D38:D46)

复制代码

dididing

【函初201203班]- E20-dididing


练习一：我用的二种方法都不是最好的，
第一种是在A列中加入辅助列，填入各等 级的下限数值，然后求得公式

1. =VLOOKUP(F3,$A$3:$C$7,3,1)

复制代码

第二种是用IF函数

1. =IF(F3<40,"F",IF(F3<70,"D",IF(F3<80,"C",IF(F3<90,"B","A"))))

复制代码

练习二：

第一种办法是用INDEX+MATCH

1. =INDEX($C$23:$E$27,MATCH(I23,$B$23:$B$27),MATCH(H23,$C$22:$E$22,0))

复制代码

第二种办法是用sumproduct函数

1. =SUMPRODUCT(($C$22:$E$22=H23)*($B$23:$B$27=I23)*$C$23:$E$27)

复制代码

练习三：

第一种办法：SUMIFS。

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

复制代码

第二种办法：SUMPRODUCT。

1. ｛=VLOOKUP(H37&H38,IF({1,0},B38:B46&C38:C46,D38:D46),2,0)｝

复制代码

zzhj

【函初201203班】-F37 zzhj
练习一,用查找函数不会,可能是前面IF函数限定了我的思给
练习二=OFFSET($B$22,MATCH(I23,$B$23:$B$27,0),MATCH(H23,$C$22:$E$22,0),,)下拉
练习三我把部门的序列变成任何数了,然后选择人名为参照
其中查部门为:=OFFSET($C$37,MATCH(H38,$C$38:$C$46,0),-1,,)
查工资数额为:=OFFSET($C$37,MATCH(H38,$C$38:$C$46,0),1,,)

rlz680926

【函初201203班]-e12-rlz680926

2003中试过
1数组公式{=VLOOKUP(H37&H38,IF({1,0},B38:B46&C38:C46,D38:D46),2,0)}
2求和
=SUMPRODUCT((H37=B38:B46)*(H38=C38:C46)*D38:D46)

xiaox0124

【函初201203班】-D05-xiaox0124

1. =LOOKUP(F3,{0,40,70,80,90},$C$3:$C$7)

复制代码

1. =SUMPRODUCT(($C$22:$E$22=$H23)*($B$23:$B$27=$I23)*$C$23:$E$27)

复制代码

1. =VLOOKUP(H37&H38,IF({1,0},B38:B46&C38:C46,D38:D46),2,0)
   
2. 三键

复制代码

ct126_ct

练习一：=LOOKUP(F3,{0,"F";40,"D";70,"C";80,"B";90,"A"})
练习二：=SUMPRODUCT(($B$22:$B$27=I23)*($C$22:$E$22=H23),$C$22:$E$27)
练习三：=VLOOKUP(H38,$C$38:$D$46,2,0)
【函初201203班】-E31-ct126_ct;

gdstcyzzt

【函初201203班]- E34-gdstcyzzt
练习一：=HLOOKUP(F3,{0,40,70,80,90;"F","D","C","B","A"},2,1)
练习二：=SUMPRODUCT(($C$22:$E$22=H23)*($B$23:$B$27=I23)*($C$23:$E$27))
练习二：=SUMPRODUCT((B38:B46=H37)*(C38:C46=H38)*(D38:D46))

yanyupingsheng

【函初201203班】-D01-yanyupisheng:
练习一、 =LOOKUP(F3,--LEFT($B$3:$B$7,(FIND("~",$B$3:$B$7)-1)),$C$3:$C$7)
             =LOOKUP(F3,INT(SUBSTITUTE($B$3:$B$7,"~",".")),$C$3:$C$7)
练习二、=SUMPRODUCT(($C$22:$E$22=H23)*($B$23:$B$27=I23)*($C$23:$E$27))
练习三、=SUMPRODUCT((B38:B46=H37)*(C38:C46=H38)*D38:D46)
             =SUMIFS(D38:D46,B38:B46,H37,C38:C46,H38)

Joyce_chen

【函数初级201203班】-C21-Joyce_chen

练习一：{=VLOOKUP(F3,IF({1,0},--LEFT($B$3:$B$7,FIND("~",$B$3:$B$7)-1),$C$3:$C$7),2)}

练习二：=VLOOKUP($I23,$B$22:$E$27,MATCH($H23,$C$22:$E$22,0)+1,0)

练习三：=VLOOKUP(H37,B37:D46,MATCH(H38,C37:C46,0),0)

xsl343866865

[函初201203班]-D24-xsl343866865

1. =VLOOKUP(F3,{0,"F";40,"D";70,"C";80,"B";90,"A"},2,1)

复制代码

1. =VLOOKUP(I23,$B$23:$E$27,MATCH(H23,$C$22:$E$22,0)+1,0)

复制代码

1. {=VLOOKUP(H37&H38,IF({1,0},B38:B46&C38:C46,D38:D46),2,0)}

复制代码