一个比 vlookup 好用10倍的查找函数,你知道吗

bodog博狗平台
查找,反向查找等。有一个功能可以轻松实现,它是今天的主角:LOOKUP功能片段记录

[示例1]查找A产品的最后一个仓储单价

=查找(1,0 /(c5: 10=B13),D5: D10)

48d050c180b645279b017abfa82a7257

找到

[例2]按时间和产品名称查找

=查找(1,0 /((b25: b30=C33)*(C25: C30=C34)),D25: D30)

0a34ba33823147328d0bc17b80752b7a

第三,指定区域中的最后一次非空值查找

[例3]在第16行找到每个人的最后还款日期

=查找(1,0 /(B2: B13''),$ A2: $ A13)

fa314144e08848b1a60e55505d2e35be

第四,反向查找

[示例4]根据真实姓名

查找A列名称

=查找(1,0(b2: b5=A9),A2: A5)

a6301df3ffda4b6bafe1407a71e665e4

五,模糊搜索1

[例5]如下图所示,需要根据提供的城市从上表中找到城市名称的第二列的值。

=LOOKUP(9 ^ 9,查找(A7,A2: A4),B2: B4)

9b6abc68ca0c40428b1e3b296870a5c6

6.模糊搜索2

[例6]如下图所示,需要根据地址查找上表中的城市佣金。

=查询(9 ^ 9.find(A $ 3: A $ 6,A10),B $ 3: B $ 6)

b202ff69a7ea4fa59e686cb2f99353d8

许多新人可能不理解上面的公式,为什么公式应该这样写。实际上,您只需记住固定用法(如果您必须了解,请参阅本文的最后部分)。即:

n)),区域或阵列)

当然,查找功能的使用远远超过上述功能。它是excel功能大师的最爱。如果有人说“我仍能运作”,你会问他:你是否使用查询?

如果你真的想知道查找的用法,小编会告诉你原理。如果你能理解它,你会看到你的理解。

使用查找搜索原理:二分法

要理解查找的查找原理并更深入地使用它,您必须了解您今天学习的二分法原则。

举个例子:

[示例]在下图中,左表和右表在第五行中只有不同的成员名称,但在查找第11行中相应的消耗量时结果不同。甚至左表中的金额也是会员A花费的金额。

式:

B11=LOOKUP(A11,A2: B8)

E11=LOOKUP(D11,D2: E8)

1f3714d79bc44d74899a1fe038578b60

事实上,查找功能非常清晰,而不是愚蠢,但我们对它知之甚少!

The lookup function search follows the dichotomy search principle, so to understand the search results in the above example, you must understand what a dichotomy lookup is. (The dichotomy is the most difficult function knowledge point in excel. It is recommended that students wash their faces and look down)

4fcd64455b5f48df93914936988c6a83

First, what is the dichotomy.

One-to-one lookup from front to back is traversal. The dichotomy is not the case. It is searched from the binary position. If it is not found, it can be searched from the next binary position until it finds the same size or smaller than its size.

Second, the basic principles.

To understand the dichotomy, you must understand the following two principles.

1. Determination of the dichotomy

To put it bluntly, the binary position is the middle position. If there are 7 numbers (the total number of lines of the 2nd parameter of the lookup function), then the 4th number is the middle position.

=LOOKUP(A11,A2: B8)

9e42feb19db048cab77373772c16badd

If there are 10 numbers, the 5th position is the binary. There is a formula here that can be calculated.

=INT((total number of rows + 1)/2)

2, find direction to determine

When you can't find it in the binary, how do you find it next?

The value of the piece. As shown in the following figure, first check from the 5th line, because C>B, so continue to check upwards, the divisor value of the D2: D4 area in the upper area is D3, and the value of D3 is B, then the corresponding E column value is 800. Is the result of the search.

634c7624ca494442a3851c4ad921052d

(find the value equal to the target value or smaller than the target value)

6c6d3853623d4d609eff093c7eb1ff23

Pieces.

xx如下图所示,A在A5(第一个二进制)中找到。原始搜索结果应为B5的860,但由于A6的值也是A,搜索结果将成为第6行的值。虽然A8也是A,但它不是A5的连续区域。所以结果不是B8。

5fb4e5477ef64ce7934404abe4c8b610

三,查找示例分析

1,间隔搜索。

[例1]如下图所示,它是典型的区间搜索公式。对应于搜索350的特许权使用费率为7%。

51f9c6ebb1324adb9285e6f69097debd

找到它:

从第一个二分法(A4)中寻找它,200< 350,往下看。

在第二个二分法(A6)中,值为400> 350,所以要检查,只有A5的值为300,并且该值小于350,因此B5的值是最终的搜索结果。

2.找到最后一个。

[示例2]查找客户A的最后购买金额,如下图所示。

9e350b41423d4142b7d281e200ca7cec

分析:

变为错误的值:

=0 /(A2: A8=A11)

结果是

{0; 0;#DIV/0;#DIV/0;0;#DIV/0;#DIV/0}

由于查找过滤掉了错误的值,因此在查找眼中,上面的数组如下所示:

{0; 0; 0}

然后用1来找到最后0的位置

=查找(1,0 /(A2: A8=A11),B2: B8)

这里1是任何大于0的值,因为它大于0,所以当通过二分法搜索时,它总是向下看直到最后的0值。

Lookup的二进制搜索是跳转查找。它总是认为要搜索的1组值从小到大排列。如果你遇到一个小的,请小看一个更大的。如果你遇到一个大的,看起来更小。

查看Excel表,除了一般的精确查找外,另一个可以缩减为通用查找功能。