本文介绍工程化的核心思路，将参数独立成表，为后续质的飞跃做准备，介绍 ADDRESS 函数、INDIRECT 函数用法。
系列教程索引和配套练习文件，请点这里。
面向对象的编程讲究封装，以期达到 “高内聚，低耦合 ” 的效果。在 Excel 里也可以这样干，使表格的功能同表与表内的数据相分离，从而拓宽表格的适用范围和灵活性。
打开 Example 3.xlsx，新建工作表，命名为 **Settings **，按如下结构填写表格内容。
填写 Settings 工作表的基本信息
文字内容其实无所谓，意思明确，看着方便就好。按图中的设定，第一列为参数名，第二列为参数值，第三列为第二列的公式文本。第三列不是必须的，只是为了教学方便而设计的。
FORMULATEXT
FORMULATEXT(reference)
- reference：目标单元格地址。
函数返回值为，目标单元格的公式文本。
假设 A1 的公式为
=SUM(1, 2)，则 A1 的值为 3；B1 的公式为=FORMULATEXT(A1)，则 B1 的值为 =SUM(1, 2)，是文本，不作为公式执行计算。
使用 FORMULATEXT 的好处是，让读者可以清晰地看到 B 列中参数的值是怎么得来的。
在 C2 处填写公式
=FORMULATEXT(B2)
并向下填充至 C8 处，可以看到一串 #N/A 出现，这是因为 B 列目前为空，没有任何公式，不急，我们先放着，船到桥头自然直。
根据设计，genre_count 参数用来记录总共定义了多少个分类，也即 Genre 工作表中除了标题行以外的其余非空行数量。在 B2 处填写公式
=COUNTA(Genre!$C:$C) - 1
效果如下
计算 genre_count 的值
这是一个基础参数，用这个数来定位下面函数中的目标单元格。
ADDRESS
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
- row_num：行号；
- column_num：列号；
- abs_num：引用样式
- 1 - 绝对（$A$1，默认）
- 2 - 绝对行 / 相对列（A$1）
- 3 - 相对行 / 绝对列（$A1）
- 4 - 相对（A1）；
- a1：地址样式
- 0 - R1C1 样式（这是 Excel 内置的第二套坐标体系，不常用）
- 1 - A1 样式（默认）
- sheet_text：工作表名称。
函数返回值为，由上述参数确定的单元格的地址字符串。
用 ADDRESS 函数来获取各级分类首位单元格的地址。例如，G1_first 是 Genre 工作表中一级分类的第一个单元格地址，值为 Genre!$A$2，其余同理。对于 first 类，由于位置固定，我们可以手动指定目标单元格的绝对地址。而对于 last 类来说，数量是动态变化的，所以行号的确定需要用到 genre_count 参数。
计算分类首位单元格的地址
由于这里需要人工计算行列位置，务必细心确保数据的准确性。
接下来就是将之前的 VLOOKUP 函数进行修改，使用这里新设计的参数。
INDIRECT
INDIRECT(ref_text, [a1])
- ref_text：地址文本，可以是字符串也可以是一个地址；
- a1：地址样式
- FALSE - R1C1 样式
- TRUE - A1 样式（默认）
函数返回值为，目标单元格的值。
但是这个 “ 目标单元格 ” 有点意思。当 ref_text 的值是文本时（例如 “ A1 ”），函数的值就是 ref_text 所指代的单元格（A1）的值；当 ref_text 的值是一个地址引用时（例如 A1），函数的值就是单元格 A1 的值所指代的单元格的值，例如 A1 的值是 “ B2 ” 的话，函数的值就是 B2 的值。
INDIRECT 函数可以作为单元格看待，所以用冒号连接两个 INDIRECT 函数是合理的，可以当做普通的两个单元格构成的一个区域。所以在上节课我们使用 OFFSET 函数构成区域的方法，可以用 INDIRECT 替代。
在 Tamplate 工作表的 A3 位置填写如下公式
=VLOOKUP($C3, CHOOSE({1,2}, INDIRECT(Settings!$B$5):INDIRECT(Settings!$B$8), INDIRECT(Settings!$B$3):INDIRECT(Settings!$B$6)), 2, FALSE)
注意，INDIRECT 函数内的参数使用了绝对引用，因为对于所有的待填充的单元格来说，分类标准的起止位置是完全相同的。
将公式向下填充至 A9 单元格即可补齐一级分类。处理二级分类时，不能再将 A3 的公式向右填充，需要在 B3 位置重写，如下
=VLOOKUP($C3, CHOOSE({1,2}, INDIRECT(Settings!$B$5):INDIRECT(Settings!$B$8), INDIRECT(Settings!$B$4):INDIRECT(Settings!$B$7)), 2, FALSE)
与 A3 处公式的区别仅在于，第二个 INDIRECT 组，也即 CHOOSE 的 value2 参数，也即最终被 VLOOKUP 作为取用区域的索引地址，发生了改变。即 INDIRECT 从上一个公式的 “ 从 G1_first 到 G1_last ” 变为这个公式的 “ 从
G2_first 到 G2_last ”。
将 B3 的公式向下填充至 B9 即可补齐二级分类，效果如下图
最终效果
如此便完成了 Example 4.xlsx。
下一课中，将介绍整体切换分类体系的方法，把这节课化简为繁的内容真正用起来。
网友评论