Excel 公式函数之 Lookup

在Excel中用公式（Formula）进行计算时，在数据表中查询特定单元格的值是进行复杂计算的基础。

CHOOSE

=CHOOSE(2, "A", "B", "C")  ' 返回 "B"

这有点像Tcl里面的 lindex

VLOOKUP

把Excel看作一张数据库表格。

一张典型的数据库表格由很多行（Row）组成，每行有一个主键（ID）。可以根据ID找到相应的行，进而获取该行中某一列的值。

用SQL表示就是

SELECT column_want FROM data_table WHERE id="id_want"

同样的意图，在Excel里就是VLOOKUP

=VLOOKUP("id_want", A2:F10, 3, FALSE)

其中，

  • 第一个参数是需要查找的“主键”
  • 第二个参数是需要超找的“表格”
    • 表格第一列必须是“主键”列
  • 第三个参数是需要获取的列的序号
    • 表格第一列的序号是1
  • 第四个参数是查找匹配的方式
    • FALSE是“完全”匹配
      • 查找文本的话，可以使用通配符?和*
    • TRUE是默认值，要求主键列升序排列
      • 结果是<=（小于或等于）查找值的那一行

HLOOKUP

VLOOKUP是把数据表格看作是一行一行的。如果数据表格是按照一列一列的形式组织的，对应的查找函数就是HLOOKUP。即

  • 在第一行中，查找指定值，找到对应的列
  • 在该列中返回指定的行的值

LOOKUP

LOOKUP可以看作是VLOOKUP和HLOOKUP的更一般化的形式。即在某一列（或某一行，下略）中查找某个值，然后在另外一列（或行）找返回对应位置的值。

因此LOOKUP的形式如下

=LOOKUP(tofind, input_column, output_column)

也可以省略第三个参数，此时第二个参数指定多个列，output_column默认为最后一列。

=LOOKUP(tofind, input_table)

这这种情况下隐含的一个问题是按行查找（VLOOKUP）还是按列查找（HLOOKUP）。LOOKUP的做法是看指定的数据表格是“高”的还是“宽”的。宽的，就认为是一列一列的，不然就认为是一行一行的。

  • 要查找的列或行要求是升序排列的
    • 类似于VLOOKUP或HLOOKUP里匹配模式是TRUE的情况
  • 不支持完全匹配的模式。
  • 按行查找还是按列查找不是很直观。
  • 功能可以用VLOOKUP和HLOOKUP代替。因而实践中不建议使用。

MATCH + INDEX

上面的VLOOKUP、HLOOKUP、LOOKUP本质上都可以分为两步操作：查找和索引。事实上我们也确实可以自己用两步操作来模拟它们。

查找用MATCH，索引用INDEX

=VLOOKUP("id_want", A2:F10, 3, FALSE)
=INDEX(F2:F10, MATCH(A2:A10, "id_want", 0))

上面两者是等效的。MATCH返回结果的位置索引。其第三个参数是匹配模式：

  • 1是默认值。类似于TRUE，要求升序排列，查找小于或等于。
  • 0类似于FALSE，完全匹配模式。
  • -1。要求降序排列，查找大于或等于目标值的。

INDEX+MATCH显然是最灵活的。比如VLOOKUP只能查找数据表里第一列的值，用INDEX+MATCH就可以突破这个限制。

INDEX+MATCH 比 VLOOKUP 更快？

对于需要查找同一行里多个列的值的情况，INDEX+MATCH组合由于可能会缓存MATCH这一步的结果，因而有说法表示可能要比VLOOKUP速度更快。

对此，实际效果取决于VLOOKUP内部的实现。此处姑且一提，有些时候可以一试。

用OFFSET指定数据表格

上面的VLOOKUP和HLOOKUP函数需要指定一个数据表格。我们自然可以用诸如A2:F10这样的形式来指定左上角和右下角。但也可以通过左上角（或右下角）的位置和行和列的数目来指定。

这有点像平面上的长方形可以用两个对角顶点的座标来表示，也可以用一个定点座标和长宽值来表示。

OFFSET函数就是用于这种用法，而且它进一步提供了对锚点进行偏移的能力。

=OFFSET(A3, 1, 2， 5，6)
# 从位置A3开始
# 向下移动一行，向右移动一列作为起点，
# 然后向下跨越5行，向右跨越6列，返回数据表格

OFFSET函数返回的是一个引用区块（Range）。如果省略后面两个参数，就只是指定了一个单元格，因此返回的该单元格的值。

OFFSET适合动态构建参与查找或计算的数据区块。