|
例题1：根据下表中的姓名，查找出定位 本例使用vlookup函数会更方便，大家都知道，在B11单元格输入： =vlookup(a11,b1:d7,3,0) 但是如何用lookup函数计算呢？从而了解lookup函数的更高级方法： 向量型查找=lookup(lookup_value,lookup_vector,result_vector) =lookup（查找的值，查找的范围，返回值的范围） 查找的值一般为数字，在此例中使用公式： =LOOKUP(1,0/($B$2:$B$7=A11),$D$2:$D$7) 函数解读 公式一：B2:B7=A11 结果：{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE} 解读：lookup善长数字查找，将查找范围1，0转化 公式二：0/公式一 结果：{0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!} 解读：利用0/0等于错误值 公式三：lookup(1,公式二,D2:D7) lookup函的两条工作思路： （1）在参数2查找范围内查找参数1，如果找到相同的值，那么返回范围横向对应的值，显然本例找不到数据. （2）在参数2查找范围内查找最接近并且小于参数1的值，输出返回范围横向对应的值，本例中0最接近并且小于1，所以输出结果为辅助 下表为诸葛亮的计算视图： 例题1：根据下表中的姓名，查找出编号 假如使得vlookup函数，则需要将原数据位置更换或使用数组 如果用lookup函数计算则非常方便 在此例中使用公式： =LOOKUP(1,0/($B$2:$B$7=A11),$A$2:$A$7) 函数解读 公式一：B2:B7=A11 结果：{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE} 解读：lookup善长数字查找，将查找范围1，0转化 公式二：0/公式一 结果：{0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!} 解读：利用0/0等于错误值 公式三：lookup(1,公式二,A2:A7) 所以该例题 lookup中的参数一改成0及以上的数字都可以！ 例题3：根据下表中的姓名及定位，查找出编号 假如使用vlookup函数，则需要使用数组公式： =VLOOKUP(A12&B12,IF({1,0},B1:B8&D1:D8,A1:A8),2,0) 如果用lookup函数计算则更加方便 在此例中使用公式： =LOOKUP(1,0/((B1:B8=A12)*(D1:D8=B12)),A1:A8) 函数解读 公式一：((B1:B8=A12)*(D1:D8=B12)) 结果：{0;1;0;0;0;0;0;0} 这个时候参数2并不是唯一的数字且不是升序排列，故需用0除 公式二：0/公式一 结果：{0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!} 这时参数2中唯一数字为0，并且符合小于参数1，故返回结果为A1 例题4：根据下表中学生成绩及成绩标准，将等级评分列算出 本例是可以是多种方法解决的，比如说IF函数的嵌套使用，或使用vlookup函数的模糊查询 先将分数等级进行可分析的表格重新定义，在G列输入满足该等级的最低分数
假如使用vlookup函数，因为匹配数据源是逆向的，所以使用 =VLOOKUP(B2,IF({1,0},$G$2:$G$7,$F$2:$F$7),2,1) 进行计算 如果用lookup函数计算就方便多了 在此例中使用公式： =LOOKUP(B2,$G$2:$G$7,$F$2:$F$7) 函数解读 lookup函数进行模拟查找时 第2个参数需要进行升序排列 会在第2个参数列中查找小于等于并最接近参数1的数据 然后返回对应参数3所在列的值
这个参数2分数列小于等于84的值有 0，60，70，80 但是最近接于84的是80 所以对应参数3列的等级的良好 例题5：根据下表中学生成绩及成绩标准，将等级评分列算出
本例也是可以使用经典的vlookup函数进行求解的。便于理解，建立一个辅助列D 在D2输入公式：=B2&COUNTIF($B$2:B2,B2) 这个公式通过混合引用方式对物品列进行累计计数！和累计求和的原理一样。
然后在F2单元格输入公式： =VLOOKUP(E2&COUNTIF($B$2:$B$10,E2),IF({1,0},$D$1:$D$10,$C$1:$C$10),2,0) 求出了最后一次进货的数量，对VLOOKUP函数不太懂的可以学习下小福的一小时从入门至精通微课录屏。
但是这个例子用lookup函数计算就方便多了 都不需要使用辅助列，在B14中使用公式： =LOOKUP(1,0/($B$2:$B$10=A14),$C$2:$C$10) 直接一步到位，得到最终的结果：
函数解读 lookup函数进行模拟查找时 第2个参数需要进行升序排列 会在第2个参数列中查找小于等于并最接近参数1的数据 假如不是升序排列，Excel会默认为升序排列，如果有多个值都小于查找值，则会返回最后一个数值。
基于上述理论，对参数二进行计算之后，再使用lookup函数的解释如下：
最后一个小于1的数值0对应值为83，所以可乐返回结果为83 例题6：左边是某公式的流水数据，如何进行查找最后一条非空记录，如果数据进行新增，则查找的数据进行自动的变化求值？
1、查找最后一个非空文本 求最后一个非空物品，在物品列全都是文本型数据 在F2单元格输入: =LOOKUP('々',B:B) 或 =LOOKUP('座',B:B) 々或座，在Excel中是编码比较大的字符，在现在查找匹配时，直接最后一个数据才能查找到 2、查找最后一个非空数字 求最后一个数量，在数量列全都数字型数据， 故需使用公式： =LOOKUP(9E 307,C:C) 同样9E307被认为是接近Excel规范与限制允许键入最大数值的数，用它做查询值，可以返回一列或一行中的最后一个数值。 3、查找最后一个混合数据 在备注栏有数字又有文本，如何查找最后一条非空混合数据？ 故需使用公式： =LOOKUP(1,0/(D:D<>''),D:D) 这个公式类似于LOOKUP函数经典案例5 如果本例需要使用vlookup函数计算 则所有文本列查找最后一个文本： =VLOOKUP('々',B:B,1) 所有数字列查找最后一个数字： =VLOOKUP(9E 307,C:C,1) 函数解读 lookup函数进行模拟查找时 第2个参数需要进行升序排列 会在第2个参数列中查找小于等于并最接近参数1的数据 假如不是升序排列，Excel会默认为升序排列，如果有多个值都小于查找值，则会返回最后一个数值。
基于上述理论，对参数二进行计算之后，再使用lookup函数的解释如下：
最后一个参数省略，即返回对应参数二的结果，即最后一个非空值。
如何使用公式快速进行提取？ 在B2单元格输入公式： =LOOKUP(9^9,RIGHT(A2,ROW($1:$6))*1) 提取6个字符以内的数字，如果数字较大，更改6即可 公式一：RIGHT(A2,ROW($1:$6)) 表示对A2单元格分别向右取1，2，3，4，5，6位数进行组成一个数组： {'1';'21';'.21';'5.21';'你5.21';'欢你5.21'} 公式二：公式一*1 表示对生成的数组进行乘以1，屏蔽非字符数字 {1;21;0.21;5.21;#VALUE!;#VALUE!} 然后就是LOOKUP函数的向量应用，9的9次方是个很大的数，进行查找时会找到最后一个结果
如果数字在前方，例：666自学成才 则只需将RIGHT函数更改为LEFT即可 =LOOKUP(9^9,LEFT(A2,ROW($1:$6))*1) 如果数字在中间，如我喜欢666自学成才 则需要使用下面公式： =LOOKUP(9^9,MID(A2,MATCH(1,MID(A2,ROW(1:6),1)^0,0),ROW(1:6))*1) 从A1:B5的原始数据库中，查找出杭州、长沙区域的数量
如果用IF函数，则需要使用IF 通配符进行解决 =VLOOKUP('*'&A9&'*',$A$1:$B$5,2,0) 需要在A9前后使用&连接通配符号*，用双引号进行连接 如果使用Lookup函数，处理起来也比较简单 在B9输入公式： =LOOKUP(9^9,FIND(A9,$A$1:$A$5),$B$1:$B$5)
函数解读 以杭州查找为例，B9的计算过程如下： 首先对FIND(A9,$A$1:$A$5)进行计算 得到下面的效果
然后使用Lookup函数进行求解
所以得到的结果为：88 因此这个杭州所对应的原始数据中必需只有一个，否则也会出现意外的结果。 所以，你学会了吗？ 有什么问题欢迎留言！ |
|
|
