一、VLOOKUP函数与HLOOKUP函数——关联查找神器
提问：如何根据其他应收款余额表（见图4-27）和其他应付款余额表（见图4-28），编制职员其他应收（应付）款对照表（以下简称“对照表”，如图4-29所示）？
1.函数技能
VLOOKUP函数与HLOOKUP函数的拿手绝活是根据源工作表指定的索引字段，自动匹配关联工作表（无论是否同一工作簿，下同）中与该索引字段相关的关联信息。如果指定的索引字段的信息在关联工作表中不存在，则返回“#N/A”。关于如何避免出现“#N/A”的方法，请参见本章第七节ISERROR函数的介绍。
两个函数的区别主要在查找的方向上，我们以相对常用的VLOOKUP函数来进行讲解。
2.语法格式
VLOOKUP(索引关键字,查找范围,关联信息位置,精确查找选择)
其中：
（1）索引关键字是指源工作表与关联工作表共同具有的具备关联价值的关键信息。类似于两个准备交流的人首先需要找到共同语言，再围绕该话题展开交流讨论。
在图4-29中，由于是要以职员为基准，关联其他应收（应付）款余额。所以职员代码（或职员姓名），就是对照表（源工作表）关联其他应收（应付）款余额表（关联工作表）的索引关键字。
（2）查找范围是在关联工作表中圈定的一个区域，但是该区域的第一列必须为索引关键字所在的列。这也是第一章和第七章中强调的，表格布局除了考虑数据的逻辑布局，还应该考虑查找引用的需求，将主关键字放在前面。
如果需引用的数据在关键字的左侧，且表格布局无法改变时，可以联合IF函数构造一个符合条件的区域，实现逆向查找引用。如图4-27其他应收款余额表中，现知道员工姓名（B4单元格），要查找引用职员编码，可使用下面的公式：
=VLOOKUP(B4,IF({1,0},$B$4:$B$10,$A$4:$A$10),2,0)
（3）该区域至少应包含需要关联的信息。例如，我们在对照表中以职员代码为索引关键字，通过其他应收款余额表关联职员其他应收款余额时，查找范围的起点必须是其他应收款余额表的A列（索引关键字“职员代码”字段所在的列），终点至少应在C列（需要关联的“其他应收款余额”所在的列）。
（4）关联信息位置是指需要关联的信息字段在搜索范围中（注意与列代码无关）所在的列次，例如在图4-27中，以员工代码为索引关联其他应收款余额时，其位置为3。
（5）精确查找选择是根据用户是否需要精确查找进行的设置，需要精确查找时输入“0”，接受模糊查找时输入“1”。该参数空缺时，将默认为接受模糊查找。
3.提问解答
根据要求，我们应该在对照表中将与职员代码匹配的职员姓名、其他应收款余额以及其他应付款余额关联到对应的单元格中。所以，职员代码将成为索引字段。
在关联职员姓名时，假设我们选择其他应付款余额表进行关联，则搜索范围至少应该是其他应付款余额表的A4:B10单元格，关联信息位置为2，假设我们要求进行精确查找。
B4单元格的公式为：=VLOOKUP(A4,其他应付款!$A$4:$B$10,2,0)
执行列填充后，即可完成职员姓名的关联（见图4-30）。
同理，可知其他应收（应付）款余额的关联公式。
C4单元格的公式为：=VLOOKUP(A4,其他应收款!$A$4:$C$10,3,0)
D4单元格的公式为：=VLOOKUP(A4,其他应付款!$A$4:$C$10,3,0)
执行列填充后，即可完成关联任务（见图4-31）。
VLOOKUP函数在本书第五章的多个案例中均有具体应用。从这些案例中我们可以看出，VLOOKUP函数的索引关键字为纵向排列，关联信息为横向排列。如果遇到索引关键字为横向排列，关联信息字段为纵向排列的情况时，就需要用到HLOOKUP函数了。两个兄弟函数参数设置原理一致。在第五章第二节案例中，我们将看到关于 HLOOKUP函数的具体应用。
4.注意事项
使用VLOOKUP函数以及HLOOKUP函数时，应注意以下几个问题。
（1）被关联表中索引关键字的信息不能重复出现。否则，关联信息只能先入为主地引入被关联表中相关索引字段信息第一次出现时对应的关联信息。
例如在图4-32中，其他应付款余额表中有两名职员都叫李远威，此时如果以职员姓名作为索引关键字，则对照表中关联的李远威其他应付款余额，就只显示第一个李远威对应的金额220元。
（2）查找区域一般需要使用绝对引用。由于使用VLOOKUP函数以及HLOOKUP函数时往往都伴随着行列的填充，而且实务中源工作表与关联工作表的索引关键字排序是无规律的，所以采用绝对引用锁定搜索区域是保证关联准确的必要手段。
（3）注意索引字段的绝对一致性。这其实是我们在第一章就强调过的基本素养问题。
很多时候我们会遇到明明索引信息在两个表中都存在，但就是无法关联信息的情况。这种情况一般就是因为两个表中的索引字段没有“绝对”一致。常见的原因有：
1）索引字段格式不一致，例如一个为数值，一个为文本。此时就需要进行单元格格式整理，整理方式可参见第二章相关内容。
2）存在空格、换行符以及其他不可见的非法字符串。此时我们的眼睛已经被欺骗了，两个表中的索引字段因为这些隐身杀手的出现，实际上已经不具备一致性，所以函数无法实现一对一的精确关联。这个时候，我们一般用替换方式进行整理，第二章也有相关处理技巧的介绍。
二、INDEX函数——坐标追踪仪
1.函数技能
INDEX函数的技能是，根据用户指定的表格区域及二维坐标，返回相关单元格的信息。该函数与MATCH函数搭档，将会在设置查询报表时给你意想不到的惊喜。
2.语法格式
INDEX(表格区域,行坐标,列坐标)
通俗的理解就是，用户指定某个电影院放映厅（表格区域），以及这个放映厅的某排（行坐标）某号（列坐标），函数就将锁定并返回这个唯一位置的信息。
其中，行坐标和列坐标是相对于用户指定表格区域的范围而言的，与单元格的行号和列号无关。它可以是文本、数值、单元格地址或公式等。
在图4-33中，我们要查询逸凡B公司三季度的收入时，就可以理解为锁定A3:E7单元格这个表格范围内的第3行第4列所对应的数据。
B10单元格的公式为：=INDEX(A3:E7,3,4)
当然，锁定逸凡B公司三季度收入的参数组合还有很多，比如：
B10单元格的公式为：=INDEX(B4:F7,2,3)
三、MATCH函数——坐标反馈仪
1.函数技能
MATCH函数相对于INDEX函数是反其道而行之，它主要反馈的是某个数据在用户指定的区域内对应的顺序号（注意不是二维坐标）。
2.语法格式
MATCH(待查询信息,数据所在的区域,查询模式)
其中：
（1）待查询信息可以为文本、数值、单元格地址或公式等。
（2）由于MATCH函数只返回一个顺序号（而不是二维坐标），所以数据所在的区域只能为连续的单行或者连续的单列。
（3）查询模式为三种，参数分别为-1、0、1（若空缺，则默认为1）。其代表的方式见图4-34。
示例：我们要在图4-35中的第6行查询“1,010”在A6:F6单元格这个范围内的序号。
D11单元格的公式为：=MATCH(1010,A6:F6,0)，其结果为4。
3.拓展应用
介绍完INDEX函数和MATCH函数，现在我们来看看它们组合应用的威力。
例如，我们需要在“子公司收入季度统计表”中增加一个查询功能（新增第3行），使得统计表可以根据用户需要输入查询的公司（B3单元格）和季度（D3单元格）自动显示对应的收入金额（F3单元格）时，就需要INDEX函数和MATCH函数联合出击了。
很明显，这是一个需要在B5:E9单元格区域内锁定行坐标和列坐标并反馈上述二维坐标对应的数据的问题。
行坐标，实际上就是查询的公司在A5:A8单元格中对应的顺序号。
列坐标，实际上就是查询的季度在B4:E4单元格中对应的顺序号。
据此，我们便可以得出查询公式（见图4-36）。
E3单元格的公式为：=INDEX(B5:E8,MATCH(B3,A5:A8,0),MATCH(D3,B4:E4,0))
第五章第三节的案例中，我们还将看到这两个函数的具体应用。
四、ROW函数与COLUMN函数——行列坐标查询仪
1.函数技能
ROW函数与COLUMN函数分别返回查询单元格的行与列的序号。和INDEX函数中锁定坐标不同的是，该序号为Excel的原始序号，与相关查询范围无关。列号查询时，返回的信息并非行坐标对应的字母，而是字母的顺序。
2.语法公式
ROW(待查询的单元格或区域)
COLUMN(待查询的单元格或区域)
其中：
（1）当参数为单元格区域时，仅以第一个单元格的信息为准。
（2）若参数空缺，则默认为查询该公式所在的单元格。
示例：=ROW(A3)、=ROW(A3:A10)、=ROW(A3:C10)。结果均为3。
=COLUMN(B5)、=COLUMN(B5:D9)。结果均为2。
五、OFFSET函数——偏移追踪器
请问，在“车间工时统计表”中（见图4-37），如何查询任意月份的合计工时和累计工时？
1.函数技能
OFFSET函数可以追踪以某单元格为起点，向上（下）左（右）移动任意单位后所对应的单元格或单元格区域的信息。简单来说，它可以告诉你，教室里坐在小明后面第三排再往左数第二个的同学以及他的若干邻座都是谁。
2.语法格式
OFFSET(起点单元格,行偏移单位,列偏移单位,追踪区域的行跨度,追踪区域的列跨度)
其中：
（1）行偏移单位为正时，表示向下移动，为负时，表示向上移动。（2）列偏移单位为正时，表示向右移动，为负时，表示向左移动。
示例：=OFFSET(B5,-2,3)，结果为：E3单元格对应的信息。
=OFFSET(B5,2,-1)，结果为：A7单元格对应的信息。
（3）追踪区域的行跨度和列跨度将锁定一个以被追踪单元格为起点，并指定行列跨度构成的单元格区域。上述两个参数省略时，均默认为1，即仅锁定被追踪单元格本身。追踪区域的行跨度和列跨度一般在需要对被追踪单元格区域进行计算时使用。
3.提问解答
在“车间工时统计表”中，如果我们以一车间1月工时对应的B4单元格为起点，那么计算某月的合计工时，以4月为例，实际上就是计算B4单元格往下偏移的行数为零（即纵向不变），并向右偏移3列(月份数减1)，且行跨度为3、列跨度为1的单元格区域（即E4:E6单元格区域）的合计（见图4-38）。
D7单元格的公式为：=SUM(OFFSET(B4,0,B7-1,3,1))
我们再来看截至某月的累计工时计算，仍以4月为例，此时应对以起点为基准（即偏移行数和列数均为零），行跨度为3、列跨度为统计月份数的单元格区域（即B4:E6单元格区域）进行求和。如图4-39所示。
D8单元格的公式为：=SUM(OFFSET(B4,0,0,3,B8))
