本文介紹工程化的核心思路，將參數(shù)獨(dú)立成表哆致，為后續(xù)質(zhì)的飛躍做準(zhǔn)備摊阀，介紹 ADDRESS 函數(shù)、INDIRECT 函數(shù)用法臣咖。
系列教程索引和配套練習(xí)文件漱牵，請(qǐng)點(diǎn)這里酣胀。
面向?qū)ο蟮木幊讨v究封裝，以期達(dá)到 “高內(nèi)聚甚脉，低耦合 ” 的效果儒溉。在 Excel 里也可以這樣干，使表格的功能同表與表內(nèi)的數(shù)據(jù)相分離，從而拓寬表格的適用范圍和靈活性涛碑。
打開 Example 3.xlsx孵淘，新建工作表，命名為 **Settings **揉阎，按如下結(jié)構(gòu)填寫表格內(nèi)容毙籽。
文字內(nèi)容其實(shí)無所謂坑赡，意思明確么抗，看著方便就好。按圖中的設(shè)定螟加，第一列為參數(shù)名，第二列為參數(shù)值甸昏，第三列為第二列的公式文本施蜜。第三列不是必須的雌隅，只是為了教學(xué)方便而設(shè)計(jì)的。
FORMULATEXT
FORMULATEXT(reference)
- reference：目標(biāo)單元格地址罩缴。
函數(shù)返回值為宏蛉，目標(biāo)單元格的公式文本吨枉。
假設(shè) A1 的公式為
=SUM(1, 2)，則 A1 的值為 3柬唯；B1 的公式為=FORMULATEXT(A1)圃庭，則 B1 的值為 =SUM(1, 2)剧腻，是文本，不作為公式執(zhí)行計(jì)算灰伟。
使用 FORMULATEXT 的好處是蕊温，讓讀者可以清晰地看到 B 列中參數(shù)的值是怎么得來的。
在 C2 處填寫公式
=FORMULATEXT(B2)
并向下填充至 C8 處发笔，可以看到一串 #N/A 出現(xiàn)了讨，這是因?yàn)?B 列目前為空，沒有任何公式胞谭，不急男杈，我們先放著伶棒，船到橋頭自然直。
根據(jù)設(shè)計(jì)先蒋，genre_count 參數(shù)用來記錄總共定義了多少個(gè)分類宛渐，也即 Genre 工作表中除了標(biāo)題行以外的其余非空行數(shù)量。在 B2 處填寫公式
=COUNTA(Genre!$C:$C) - 1
效果如下
這是一個(gè)基礎(chǔ)參數(shù)业岁，用這個(gè)數(shù)來定位下面函數(shù)中的目標(biāo)單元格叨襟。
ADDRESS
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
- row_num：行號(hào)；
- column_num：列號(hào)梳玫；
- abs_num：引用樣式
- 1 - 絕對(duì)（$A$1，默認(rèn)）
- 2 - 絕對(duì)行 / 相對(duì)列（A$1）
- 3 - 相對(duì)行 / 絕對(duì)列（$A1）
- 4 - 相對(duì)（A1）姚垃；
- a1：地址樣式
- 0 - R1C1 樣式（這是 Excel 內(nèi)置的第二套坐標(biāo)體系积糯，不常用）
- 1 - A1 樣式（默認(rèn)）
- sheet_text：工作表名稱谦纱。
函數(shù)返回值為，由上述參數(shù)確定的單元格的地址字符串川慌。
用 ADDRESS 函數(shù)來獲取各級(jí)分類首位單元格的地址梦重。例如，G1_first 是 Genre 工作表中一級(jí)分類的第一個(gè)單元格地址降瞳，值為 Genre!$A$2蚓胸，其余同理。對(duì)于 first 類亮靴，由于位置固定茧吊，我們可以手動(dòng)指定目標(biāo)單元格的絕對(duì)地址八毯。而對(duì)于 last 類來說，數(shù)量是動(dòng)態(tài)變化的讶踪，所以行號(hào)的確定需要用到 genre_count 參數(shù)泊交。
由于這里需要人工計(jì)算行列位置廓俭，務(wù)必細(xì)心確保數(shù)據(jù)的準(zhǔn)確性研乒。
接下來就是將之前的 VLOOKUP 函數(shù)進(jìn)行修改，使用這里新設(shè)計(jì)的參數(shù)宽菜。
INDIRECT
INDIRECT(ref_text, [a1])
- ref_text：地址文本竿报，可以是字符串也可以是一個(gè)地址；
- a1：地址樣式
- FALSE - R1C1 樣式
- TRUE - A1 樣式（默認(rèn)）
函數(shù)返回值為隆判，目標(biāo)單元格的值。
但是這個(gè) “ 目標(biāo)單元格 ” 有點(diǎn)意思臭挽。當(dāng) ref_text 的值是文本時(shí)（例如 “ A1 ”）咬腕，函數(shù)的值就是 ref_text 所指代的單元格（A1）的值；當(dāng) ref_text 的值是一個(gè)地址引用時(shí)（例如 A1）纽帖，函數(shù)的值就是單元格 A1 的值所指代的單元格的值懊直，例如 A1 的值是 “ B2 ” 的話火鼻，函數(shù)的值就是 B2 的值。
INDIRECT 函數(shù)可以作為單元格看待融撞，所以用冒號(hào)連接兩個(gè) INDIRECT 函數(shù)是合理的粗蔚，可以當(dāng)做普通的兩個(gè)單元格構(gòu)成的一個(gè)區(qū)域。所以在上節(jié)課我們使用 OFFSET 函數(shù)構(gòu)成區(qū)域的方法致扯，可以用 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 函數(shù)內(nèi)的參數(shù)使用了絕對(duì)引用寺晌，因?yàn)閷?duì)于所有的待填充的單元格來說澡刹，分類標(biāo)準(zhǔn)的起止位置是完全相同的罢浇。
將公式向下填充至 A9 單元格即可補(bǔ)齊一級(jí)分類沐祷。處理二級(jí)分類時(shí)赖临，不能再將 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 處公式的區(qū)別僅在于兼雄，第二個(gè) INDIRECT 組，也即 CHOOSE 的 value2 參數(shù)块攒，也即最終被 VLOOKUP 作為取用區(qū)域的索引地址金砍，發(fā)生了改變恕稠。即 INDIRECT 從上一個(gè)公式的 “ 從 G1_first 到 G1_last ” 變?yōu)檫@個(gè)公式的 “ 從
G2_first 到 G2_last ”。
將 B3 的公式向下填充至 B9 即可補(bǔ)齊二級(jí)分類千扶，效果如下圖
如此便完成了 Example 4.xlsx澎羞。
下一課中敛苇，將介紹整體切換分類體系的方法，把這節(jié)課化簡(jiǎn)為繁的內(nèi)容真正用起來括饶。
