LOOKUP是一个在工作中经常会用到的函数,他是一个查找函数,既可以从上往下竖着查找,也可以从左往右横向查找。 语法LOOKUP(查找对象,查找区域,结果区域) 第一个参数查找对象,就是要查找的值,可以是数字、文本、逻辑值或引用值的名称或引用; 第二个参数查找区域,就是搜索查找对象的区域,他只能是单行或者单列,查找区域中的值可以是文本、数字或逻辑值。; 第三个参数结果区域,他和查找区域必须大小相同且一一对应。 示例单条件查找由此可见,LOOKUP函数不仅可以纵向从左往右查找,可以从右往左查找。还可以横向从上往下查找,或者从下往上查找。 多条件查找多条件查询的写法为: =LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),结果区域) 或者: =LOOKUP(1,0/(条件区域1=条件1)/(条件区域2=条件2)结果区域) 如图,查询员工A的香蕉的销售额: =LOOKUP(1,0/((A2:A5=E2)*(B2:B5=F2)),C2:C5) 或者 =LOOKUP(1,0/(( A2:A5=E2)/( B2:B5=F2)),C2:C5) 公式中: 第一个参数1,在这里是TRUE的意思,在查找区域中查找返回的TRUE值; A2:A5=E2和B2:B5=F2返回结果为TRUE或者FALSE的数组,也就是0或者1; 当除数为0时没有意义,会返回错误值,所以0除以数组中的TURE(1)或者FALSE(0),是也会分别返回TRUE或者错误值; 当查找区域返回TRUE值是,返回结果区域对应的值,错误值则会被LOOKUP自动忽略。 近似查询如图,根据销售额在对照表中查找对应的提成率。 E2单元格公式为:= LOOKUP(C2,A9:B14) 在使用近似查找是,对照表的首列必须是升序处理。 LOOKUP提取数据单元格A1内容为“58号学生”,在A1中提取学号,则公式为: = -LOOKUP(0,-LEFT(A1,ROW(1:9))) ROW(1:9)返回包含1到9的数组,就是{1,2,3,4,5,6,7,8,9}。 LEFT(A1,ROW(1:9))在A1单元格中截取9个数据,分别为5、58及错误值,错误值会自动被LOOKUP忽略。 -LEFT(A1,ROW(1:9)则分别为-5和-58; LOOKUP在{-5; -58}中找0。0比这组数据都大,当查找值大于查找范围中所有数据时,LOOKUP的实质就是在找最后一个数据。所以返回-58,; 再再LOOKUP前增加负号计算,返回58。 如果是在右边截取数值,则把LEFT换为RIGHT函数,即可。 单元格A1内容为“学号是58”,在A1中提取学号,则公式为: = -LOOKUP(0,-RIGHT(A1,ROW(1:9))),原理与从左边截取相同。 单元格A1内容为“学号是58的学生”,在A1中提取学号,则公式为: =LOOKUP(9^9,MID(A1,MATCH(1,MID(A1,ROW(1:9),1)^0,0),ROW(1:9))*1) CTRL+SHIFT+ENTER三件退出。 公式中: 9^9是9的9次方就是一个非常大的数,让lookup返回单元格中最下面的一个值,没有完全匹配的数字时,会自动区配最后一个数值。 MID(A1,ROW(1:9)在A1单元格中分别截取1到9个字符,返回数值{'学';'号';'是';'5';'8';'的';'学';'生';''} MID(A1,ROW(1:9)^0也就是{'学';'号';'是';'5';'8';'的';'学';'生';''}的0次幂,汉字的0次幂会返回错误值,数值的0次幂返回1,也就是数组: {#VALUE!;#VALUE!;#VALUE!;1;1;#VALUE!;#VALUE!;#VALUE!;#VALUE!} MATCH(1, {#VALUE!;#VALUE!;#VALUE!;1;1;#VALUE!;#VALUE!;#VALUE!;#VALUE!},0),在数组中查找1.返回4; MID(A,1,MATCH(1,MID(A2,ROW(1:9),1)^0,0),ROW(1:9))也就是: MID(A1,4,ROW(1:9))*1,在A单元格中第四位开始截取字符,截取9次,返回数组:{5;58;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!},乘1是将返回的字符转换为数值;LOOKUP函数当查到到最后一个数字58时,则返回结果区域对应的单元格内容。 简称查全称如图,查找苹果的单价。 E3单元格公式为:=LOOKUP(9^9,FIND(D3,A3:A6),B3:B6) FIND(D3,A3:A6)在A3到D6单元格中查找D3单元格内容,返回一个数值4; LOOKUP函数会总最小数开始查找,当查到到4时,有其对应的值,则返回结果区域对应的单元格内容。 也可以写为: =-LOOKUP(0,-FIND(A3:A5,D3),B3:B5) 0是最趋近于最大负数的数字,第二个参数前增加负号,返回负值,LOOKUP函数当查到到-4时,则返回结果区域对应的单元格内容,再前面增加负号计算为正值。 。 全称查简称如图,查找红富士苹果的单价。 E3单元格公式为:=LOOKUP(9^9,FIND(A3:A5,D3),B3:B5) 全称查简称与简称查全称原理相同。 |
|