制作下拉菜單、制作多級(jí)聯(lián)動(dòng)下拉菜單，甚至是隨數(shù)據(jù)源自動(dòng)增減的下拉菜單，這些我都講解過(guò)詳細(xì)案例，不算什么新鮮知識(shí)點(diǎn)，今天為什么我又要講動(dòng)態(tài)下拉菜單了呢？

因?yàn)榻裉斓闹攸c(diǎn)是講動(dòng)態(tài)數(shù)組區(qū)域的引用。這是一個(gè)全新的知識(shí)點(diǎn)，請(qǐng)務(wù)必耐心看完。

案例：

下圖 1 是各部門(mén)的人員列表，請(qǐng)制作部門(mén)和姓名聯(lián)動(dòng)的二級(jí)下拉菜單，重點(diǎn)是：如果原始數(shù)據(jù)表中的部門(mén)名稱有更新，“部門(mén)”下拉列表選項(xiàng)會(huì)自動(dòng)隨之更新。

效果如下圖 2 至 4 所示。

解決方案：

1. 在部門(mén)前面添加輔助列 --> 在 A2 單元格中輸入以下公式 --> 下拉復(fù)制公式：

=B2&COUNTIF(B$2:B2,B2)

公式釋義：

• 該公式的作用是在部門(mén)名字后面加上重復(fù)出現(xiàn)的次數(shù)，從而重新生成一列沒(méi)有重復(fù)值的新部門(mén)名

* 請(qǐng)注意：countif 第一個(gè)參數(shù)的起始單元格行值必須絕對(duì)引用，終止單元格的行值要相對(duì)引用。

接下來(lái)提取部門(mén)的唯一值列表。

2. 在 H2 單元格中輸入以下公式：

=UNIQUE(B2:B10)

• unique 是 O365 版本才有的函數(shù)，作用是提取區(qū)域內(nèi)的唯一值；

• O365 不僅是增加了新函數(shù)，還簡(jiǎn)化了公式的用法，只要在返回區(qū)域的第一個(gè)單元格內(nèi)輸入公式即可，既不需要復(fù)制公式，也不需要運(yùn)用數(shù)組公式，就會(huì)自動(dòng)返回一個(gè)動(dòng)態(tài)數(shù)組區(qū)域。

有關(guān) unique 函數(shù)的詳解，可參閱 Excel – 提取不重復(fù)值，終于有專門(mén)的函數(shù)了。

3. 在 I2 單元格中輸入以下公式 --> 向右向下拖動(dòng)復(fù)制公式：

=VLOOKUP($H2&COLUMN(A1),$A:$C,3,0)

公式釋義：

• COLUMN(A1)：計(jì)算 A1 單元格的列號(hào)，單元格向右拖動(dòng)的時(shí)候，列號(hào)遞增，向下拖動(dòng)則不變；

• $H2&..：將 $H2 單元格與上述列號(hào)連接起來(lái)，得到一組與 A 列相匹配的值；

• VLOOKUP(...,$A:$C,3,0)：在區(qū)域 $A:$C 中查找上述值，返回第 3 列的值

* 請(qǐng)注意：$H2 單元格的列標(biāo)需要絕對(duì)引用，行號(hào)要相對(duì)引用；查詢區(qū)域 $A:$C 要絕對(duì)引用。

接下來(lái)開(kāi)始制作下拉菜單。

4. 選中 E2 單元格 --> 選擇菜單欄的“數(shù)據(jù)”-->“數(shù)據(jù)驗(yàn)證”-->“數(shù)據(jù)驗(yàn)證”

5. 在彈出的對(duì)話框中選擇“設(shè)置”選項(xiàng)卡 --> 按以下方式設(shè)置 --> 點(diǎn)擊“確定”：

• 允許：選擇“序列”

• 來(lái)源：輸入“=$H$2#”

* 敲黑板，今天的重點(diǎn)知識(shí)點(diǎn)來(lái)了：

• 前面已經(jīng)說(shuō)了，sort 函數(shù)返回的結(jié)果是一個(gè)動(dòng)態(tài)區(qū)域；

• 引用這個(gè)動(dòng)態(tài)區(qū)域的方式，只要在區(qū)域的第一個(gè)單元格后面加個(gè)“#”號(hào)，就能動(dòng)態(tài)地引用這個(gè)區(qū)域了。
  

然后創(chuàng)建二級(jí)聯(lián)動(dòng)下拉菜單。

6. 按 Ctrl+F3 --> 在彈出的對(duì)話框中點(diǎn)擊“新建”按鈕

7. 在彈出的對(duì)話框中按以下方式設(shè)置：

• 名稱：輸入“銷售一部”

• 引用位置：選擇 I2:K2 區(qū)域
  

8. 再次點(diǎn)擊“新建”按鈕 --> 用同樣的方式創(chuàng)建其他部門(mén)的名稱。

9. 選中 F2 單元格 --> 選擇菜單欄的“數(shù)據(jù)”-->“數(shù)據(jù)驗(yàn)證”-->“數(shù)據(jù)驗(yàn)證”

10. 在彈出的對(duì)話框中按以下方式設(shè)置 --> 點(diǎn)擊“確定”：

• 允許：選擇“序列”

• 來(lái)源：輸入“=INDIRECT($E$2)”

有關(guān) indirect 函數(shù)在二級(jí)聯(lián)動(dòng)下拉菜單中的應(yīng)用，請(qǐng)參閱 Excel indirect 函數(shù)(2) – 制作多級(jí)聯(lián)動(dòng)菜單（文末彩蛋）。

現(xiàn)在選擇“部門(mén)”下拉菜單中的選項(xiàng)，“姓名”下拉菜單中的選項(xiàng)就會(huì)動(dòng)態(tài)變成相應(yīng)部門(mén)的人員列表。

既然今天的重點(diǎn)講的是動(dòng)態(tài)區(qū)域的引用，那么我們繼續(xù)驗(yàn)證一下，如果部門(mén)列的源數(shù)據(jù)更新了，最終是否會(huì)導(dǎo)致下拉菜單自動(dòng)更新。

11. 修改任意一個(gè)部門(mén)的名稱，如下圖所示。

我什么都沒(méi)做，H 列就自動(dòng)增加了這個(gè)新的部門(mén)名，這就是動(dòng)態(tài)數(shù)組結(jié)果的魅力所在。

因?yàn)椤安块T(mén)”下拉菜單引用的是動(dòng)態(tài)區(qū)域，所以下拉選項(xiàng)中也自動(dòng)新增了這個(gè)部門(mén)。

這簡(jiǎn)直太方便了，所有聯(lián)動(dòng)一氣呵成，連刷新動(dòng)作都不需要。

12. 向下拖動(dòng) I 列的公式，就能查詢出新部門(mén)對(duì)應(yīng)的人員。

13. 此時(shí)只要重復(fù)步驟 6、7，創(chuàng)建新的名稱列表，就能在“名稱”下拉菜單中增加新的聯(lián)動(dòng)列表。