神秘的使用LOOKUP [英] Mysterious use of LOOKUP

问题描述

我一直在使用这个Excel公式多年。它工作正常，但我想了解为什么它的作品！该公式用于查找列表中的最后一个关联值。例如：

C14中的公式： = LOOKUP（2,1 /（B1：B12 =肉），C1：C12）

C14 中的公式在列B中查找最新的肉标记的单元格，并返回C中的关联值柱。它在 B9 中找到肉，并相应地返回 C9 。

公式中最令人费解的部分是1 /(....)\" 。这是什么划分？这个语法来自哪里？它可以在其他地方使用吗？为什么查找值 2 ？

解决方案

这是发生了什么：

  = LOOKUP（2,1 /（B1：B12 =肉），C1：C12）
  

成为这个

  = LOOKUP（2,1 / {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}，C1：C12）
  pre> 
 
 
成为这个
  = LOOKUP（2，{1 ;＃DIV / 0;＃DIV / 0;＃DIV / 0;＃DIV / 0;＃DIV / 0;＃DIV / 0;＃DIV / 0;！1;＃DIV / 0！ ;＃DIV / 0！;＃DIV / 0！}，C1：C12）
  
 c $ c> B1：B12 =meat part被计算为TRUE和FALSE值的数组。当您以TRUE或FALSE进行数学运算时，它将变为1或0.除以1或0除以1或0，对于所有的罚款都会返回1，而对于所有的错误，div / 0将返回1。
 
 
 
现在你知道你将有一个完整的两个东西（一个或一个div / 0）的数组，你可以LOOKUP任何大于1的数字，它将返回列表中的最后一个。您可以 = LOOKUP（800，...），它仍然会返回最小值小于或等于查找值，或在您的情况下
 
 
 
所以这两个有点随意 - 它只是一个大于1的数字。它的关键是创建只包含1s和错误的lookup数组参数 - LOOKUP忽略错误。
 
 
 
 二进制搜索 
 
 
 
我没有官方有关这方面的文件，所以这里是非官方版本。 LOOKUP，HLOOKUP和VLOOKUP有一个参数，您可以在该参数中说明数据是否被排序。如果该参数为False，则该函数将查看每个条目直到结束。如果该参数为True或省略，则该函数使用二进制搜索。这样做 - 参数只存在 - 因为二进制搜索的方式比ol一次性搜索更快。
 
 
 
二进制搜索通过查找工作中间值，并针对追求的价值进行评估。如果中间值较大，则右侧的所有内容都将被舍弃 - 所寻求的值必须在左侧。它需要左半边，找到它的中间值。如果它更大，丢弃权利并保留左边。继续迭代，直到找到该值。
 
 
 
所以为什么 LOOKUP（2，{1,1，＃DIV / 0！，1,1 ，＃DIV / 0！，1,1}，...）找到最后一个 1 而不是一些任意的 1 ？我不知道MS如何实现二进制搜索，所以我必须做出一些假设。我的假设是将错误值从数组中抛出，当有偶数个条目（没有中间值）时，将使用中间左侧的值。这些假设可能是错误的，但对结果影响不大。
 
 
 
首先，抛出错误。现在您有这些条目及其原始位置
  1 1 1 1 1 1 
 1 2 4 5 7 8 
  
中间数字是1（4）。它小于2（寻求价值），所以我们抛出一切向左边重新处理权利。现在我们有
  1 1 1 
 5 7 8 
  pre> 
 
 
中间值为1（7）。它不到2，所以我们把所有东西都丢到左边，重新处理权利。现在我们有
  1 
 8 
  
我们下了一个条目，这就是答案。如果所寻求的值高于所有其他值，二进制搜索将始终返回最后一个值。
 
I've been using this Excel formula for years. It works fine, but I want to understand why it works! The formula is used to find the last associated value on a list. For instance:



Formula in C14: =LOOKUP(2,1/(B1:B12="meat"),C1:C12)

Here, the formula in C14 looks in column B for the latest "meat" labeled cell and returns the associated value in the C column. It finds "meat" at B9 and correspondingly returns the value at C9.

The most puzzling part of the formula is "1/(....)". What is this division? Where does this syntax come from? Can it be used elsewhere? Why is the lookup value 2?
解决方案
Here's what's happening: This
=LOOKUP(2,1/(B1:B12="meat"),C1:C12)
becomes this
=LOOKUP(2,1/{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE},C1:C12)
which becomes this
=LOOKUP(2,{1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!},C1:C12)
The B1:B12="meat" part is evaluated as an array of TRUE and FALSE values. When you do math on TRUE or FALSE it turns into 1 or 0. Dividing 1 by 1 or 0 returns 1 for all the trues and div/0 for all the falses.

Now that you know you're going to have an array full of exactly two things (either a 1 or a div/0), you can LOOKUP any number that's greater than 1 and it will return the last 1 in the list.  You could =LOOKUP(800,...) and it will still return the "largest value that's less than or equal to the look up value", or in your case 1.

So the two is somewhat arbitrary - it's just a number that's greater than 1. The crux of it is to create the lookup array argument that includes only 1s and errors - LOOKUP ignores the errors.

Binary Search

I don't have official documentation on this, so here's the unofficial version. LOOKUP, HLOOKUP, and VLOOKUP have an argument where you tell the function if the data is sorted. If that argument is False, the function looks at every entry all the way until the end. If that argument is True or omitted, the function uses a binary search. It does that - the argument only exists - because a binary search is way faster than the ol' one-at-a-time search.

A binary search works by finding the middle value and evaluating it against the sought value. If the middle value is larger, everything to the right is discarded - the sought value must be to the left. It takes the left half and finds the middle value of that. If it's larger, it discards the right and keeps the left. Keep iterating until you find the value.

So why does LOOKUP(2,{1,1,#DIV/0!,1,1,#DIV/0!,1,1},...) find the last 1 instead of some arbitrary 1? I don't know exactly how MS implemented their binary search, so I have to make some assumptions. My assumptions are that error values are thrown out of the array and that when there's an even number of entries (no middle value), the value on the left of middle is used. Those assumptions may be wrong, but they have zero affect on the outcome.

First, throw out the errors. Now you have these entries and their original positions
1  1  1  1  1  1
1  2  4  5  7  8
The "middle" number is 1(4). It is less than 2 (sought value), so we throw out everything to the left and reprocess the right.  Now we have
1  1  1
5  7  8
The middle value is 1(7). It is less than 2, so we throw out everything to the left and reprocess the right. Now we have
1
8
We're down to one entry, so that's the answer. If the sought value is higher than all the other values, a binary search will always return the last value.

                        
这篇关于神秘的使用LOOKUP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！