本文介绍 OFFSET 函数用法，COUNTA 函数用法，以及一个良好的构架思路。
系列教程索引和配套练习文件，请点这里。
上一课中，介绍了自动补齐分类的基本方法，核心是使用 VLOOKUP 函数和 CHOOSE 函数。
打开 Example 2.xlsx ，效果如下图
上节课的最终效果
其中 A3 处的公式为
=VLOOKUP($C3, CHOOSE({1, 2}, Genre!$C$2:$C$121, Genre!A$2:A$121), 2, FALSE)
在固定的分类体系下，这样做没有问题。但如果随着时间的推进，Genre 工作表中现有的 120 个分类标准不够用，需要增加新的分类时，问题就出现了。
假设我们发现某条用户反馈内容为 “ 导致死机 ”，将它写在三级分类的位置上，填充一级分类和二级分类的公式，会有如下结果
无法识别的三级分类
该条目对应的一级分类和二级分类值为 #N/A，因为 Excel 在现有的分类体系中（Genre 工作表），找不到名为 “ 导致死机 ” 的三级标题。于是我们理所应当地在 Genre 工作表中添加一行，把这一个新的分类纳入到整个体系中去。
在 Genre 工作表中插入一行新的分类
但是这样无济于事，因为在公式
=VLOOKUP($C3, CHOOSE({1, 2}, Genre!$C$2:$C$121, Genre!A$2:A$121), 2, FALSE)
中，我们将搜索三级分类的区域人为地限定在了 Genre!$C$2:$C$121 中，所以新加的第 122 行内容没有被包含。要实现对新分类的有效识别，就必须让 VLOOKUP 函数的搜索区域和取用区域，也即它的整个作用区域，随着 Genre 工作表中的内容动态的增减。
要知道在有多少个分类标准，需要使用 COUNTA 函数。
COUNTA
COUNTA(value1, [value2], ...)
- value1/2/...：第几个值。
函数返回值为，参数中非空值的个数。
在 Genre 工作表中，非空的行有 122 个，除去标题行，有 121 个有效的分类。这个数字即可用如下公式获得
COUNTA(Genre!$C:$C) - 1
并且该数字会随着 Genre 工作表中三级分类（C 列）的增减而变化。
接着，使用这个动态变动的行数，来构建 VLOOKUP 的作用区域，使用 OFFSET 函数。
OFFSET
OFFSET(reference, rows, cols, [height], [width])
- reference：某一单元格的地址，作为锚点；
- rows：锚点向下偏移几行；
- cols：锚点向右偏移几列；
- height：从锚点开始，向下选择几行；
- width：从锚点开始，向右选择几行。
函数返回值为，由上述五个参数所确定的数据区域。
将 COUNTA 的结果放到 OFFSET 中 height 的位置上去，即可构建一个会动态增减大小的数据区域，把这个结果放到 CHOOSE 中，就能实现动态增减的作用区域。
具体地，在 Tamplate 工作表的 A3 处输入公式
=VLOOKUP($C3, CHOOSE({1,2}, OFFSET(Genre!$C$2, 0, 0, COUNTA(Genre!$C:$C) - 1), OFFSET(Genre!A$2, 0, 0, COUNTA(Genre!$C:$C) - 1)), 2, FALSE)
与前面公式的不同在于，将 Genre!$C$2:$C$121 替换为 OFFSET(Genre!$C$2, 0, 0, COUNTA(Genre!$C:$C) - 1)，Genre!A$2:A$121 替换为 OFFSET(Genre!A$2, 0, 0, COUNTA(Genre!$C:$C) - 1))。
OFFSET(Genre!$C$2, 0, 0, COUNTA(Genre!$C:$C) - 1) 的意思是，从 Genre!$C$2 开始，向下移动 0 行，向右移动 0 列，作为起点，向下选择 COUNTA(Genre!$C:$C) - 1 （121）行（包括其自身），向右选择 1 列（缺省值）。此刻，这个公式的返回值是区域 Genre!$C$2:Genre!$C$122，刚好是定义三级分类的范围。
注意
- 在第二个 OFFSET 函数中，列绝对引用的变为相对引用，因为将公式填充至 B 列时，相应地要取用 Genre 中的二级分类。
- 在第二个 COUNTA 函数中，仍然以三级分类为标准，是出于一致性的考虑。这样写的意思是，只要有三级分类在，不管一二级有没有，都尝试寻找。避免了在 Genre 工作表中单独新增了三级分类，空着其对应的一二级分类，导致的，在 Tamplate 中无法反映出来的问题。
- 这样的写法要求 Genre 工作表中，定义分类的区域不能有空行。如果将新的分类写在第 123 行，非空行数量还是 122，导致动态增减的区域只作用到 122 行，不能包含第 123 行的数据。
这样一来，在 Genre 中新增的分类标准在 Tamplate 中就可以被有效地识别了。
最终效果
如此便完成了 Example 3.xlsx。
下一课中，将介绍参数分离的思想，为构建灵活性更强的表格做准备。
网友评论