本文介紹 OFFSET 函數(shù)用法，COUNTA 函數(shù)用法，以及一個良好的構(gòu)架思路。
系列教程索引和配套練習(xí)文件，請點這里。
上一課中，介紹了自動補齊分類的基本方法，核心是使用 VLOOKUP 函數(shù)和 CHOOSE 函數(shù)。
打開 Example 2.xlsx ，效果如下圖
其中 A3 處的公式為
=VLOOKUP($C3, CHOOSE({1, 2}, Genre!$C$2:$C$121, Genre!A$2:A$121), 2, FALSE)
在固定的分類體系下，這樣做沒有問題。但如果隨著時間的推進，Genre 工作表中現(xiàn)有的 120 個分類標準不夠用，需要增加新的分類時，問題就出現(xiàn)了。
假設(shè)我們發(fā)現(xiàn)某條用戶反饋內(nèi)容為 “ 導(dǎo)致死機 ”，將它寫在三級分類的位置上，填充一級分類和二級分類的公式，會有如下結(jié)果
該條目對應(yīng)的一級分類和二級分類值為 #N/A，因為 Excel 在現(xiàn)有的分類體系中（Genre 工作表），找不到名為 “ 導(dǎo)致死機 ” 的三級標題。于是我們理所應(yīng)當(dāng)?shù)卦?Genre 工作表中添加一行，把這一個新的分類納入到整個體系中去。
但是這樣無濟于事，因為在公式
=VLOOKUP($C3, CHOOSE({1, 2}, Genre!$C$2:$C$121, Genre!A$2:A$121), 2, FALSE)
中，我們將搜索三級分類的區(qū)域人為地限定在了 Genre!$C$2:$C$121 中，所以新加的第 122 行內(nèi)容沒有被包含。要實現(xiàn)對新分類的有效識別，就必須讓 VLOOKUP 函數(shù)的搜索區(qū)域和取用區(qū)域，也即它的整個作用區(qū)域，隨著 Genre 工作表中的內(nèi)容動態(tài)的增減。
要知道在有多少個分類標準，需要使用 COUNTA 函數(shù)。
COUNTA
COUNTA(value1, [value2], ...)
- value1/2/...：第幾個值。
函數(shù)返回值為，參數(shù)中非空值的個數(shù)。
在 Genre 工作表中，非空的行有 122 個，除去標題行，有 121 個有效的分類。這個數(shù)字即可用如下公式獲得
COUNTA(Genre!$C:$C) - 1
并且該數(shù)字會隨著 Genre 工作表中三級分類（C 列）的增減而變化。
接著，使用這個動態(tài)變動的行數(shù)，來構(gòu)建 VLOOKUP 的作用區(qū)域，使用 OFFSET 函數(shù)。
OFFSET
OFFSET(reference, rows, cols, [height], [width])
- reference：某一單元格的地址，作為錨點；
- rows：錨點向下偏移幾行；
- cols：錨點向右偏移幾列；
- height：從錨點開始，向下選擇幾行；
- width：從錨點開始，向右選擇幾行。
函數(shù)返回值為，由上述五個參數(shù)所確定的數(shù)據(jù)區(qū)域。
將 COUNTA 的結(jié)果放到 OFFSET 中 height 的位置上去，即可構(gòu)建一個會動態(tài)增減大小的數(shù)據(jù)區(qū)域，把這個結(jié)果放到 CHOOSE 中，就能實現(xiàn)動態(tài)增減的作用區(qū)域。
具體地，在 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 列（缺省值）。此刻，這個公式的返回值是區(qū)域 Genre!$C$2:Genre!$C$122，剛好是定義三級分類的范圍。
注意
- 在第二個 OFFSET 函數(shù)中，列絕對引用的變?yōu)橄鄬σ?，因為將公式填充?B 列時，相應(yīng)地要取用 Genre 中的二級分類。
- 在第二個 COUNTA 函數(shù)中，仍然以三級分類為標準，是出于一致性的考慮。這樣寫的意思是，只要有三級分類在，不管一二級有沒有，都嘗試尋找。避免了在 Genre 工作表中單獨新增了三級分類，空著其對應(yīng)的一二級分類，導(dǎo)致的，在 Tamplate 中無法反映出來的問題。
- 這樣的寫法要求 Genre 工作表中，定義分類的區(qū)域不能有空行。如果將新的分類寫在第 123 行，非空行數(shù)量還是 122，導(dǎo)致動態(tài)增減的區(qū)域只作用到 122 行，不能包含第 123 行的數(shù)據(jù)。
這樣一來，在 Genre 中新增的分類標準在 Tamplate 中就可以被有效地識別了。
如此便完成了 Example 3.xlsx。
下一課中，將介紹參數(shù)分離的思想，為構(gòu)建靈活性更強的表格做準備。
