大家好，今天我們來講解一個可以大大提高辦公效率的excel技巧，那就是二級聯(lián)動下拉菜單的制作。

如下圖所示：

注意，這可不是簡單的下拉菜單，二級菜單不僅可以實現(xiàn)與一級主菜單的聯(lián)動，并且當數(shù)據(jù)源，新增列、刪減列，新增二級數(shù)據(jù)，減少二級數(shù)據(jù)時，都能實現(xiàn)對二級聯(lián)動下拉菜單的實時更新。

微軟Office LTSC 2021專業(yè)增強版 簡體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大?。?/span>2.2GB
• 語言：簡體中文
• 時間：2024-09-12

查看詳情

WPS Office 2025 V12.1.0.21915 官方最新正式版

• 類型：辦公軟件
• 大小：260MB
• 語言：簡體中文
• 時間：2025-07-03

查看詳情

解決這個問題有兩個核心技巧：一個是超級表，另一個是INDIRECT函數(shù)對超級表的結構化引用語法。

很明顯，第一步我們需要進行對數(shù)據(jù)源A1:C4區(qū)域的超級表轉換。

選中數(shù)據(jù)源A1:C4區(qū)域，點擊“插入-表格”（或快捷鍵Ctrl+T）,調(diào)出“創(chuàng)建表”的對話框，記住一定要勾選“表包含標題”，點擊“確定”后，原普通表格就轉換成超級表了。

當數(shù)據(jù)區(qū)域被轉換為超級表（套用表格格式）后，無論使用Excel或WPS表格，都會為其分配一個表名稱（如"表1"），并支持通過列標題直接引用數(shù)據(jù)區(qū)域。

INDIRECT函數(shù)的作用機制是文本轉引用的核心功能。INDIRECT函數(shù)的本質(zhì)是將具有引用樣式的文本字符串，變成真正的引用。

參數(shù)也非常簡單：

=INDIRECT(具有引用樣式的字符串,[引用樣式])

如果第二參數(shù)為TRUE或省略(包括參數(shù)值和逗號)，會將第一參數(shù)中的字符串解釋為A1樣式的引用，如果第二參數(shù)為FALSE或是0，則將第一參數(shù)中的字符串解釋為R1C1樣式的引用。

例如：

若單元格A1存儲文本"蔬菜"，則=INDIRECT("A1")等價于直接引用A1單元格內(nèi)容“蔬菜”。

在WPS表格中引用超級表的內(nèi)容，其核心原理是通過結構化引用語法與INDIRECT函數(shù)的間接引用特性實現(xiàn)動態(tài)數(shù)據(jù)關聯(lián)。

例如：

=INDIRECT("表1[#標題]")

表1[#標題]

表示引用"表1"中"標題"行的所有數(shù)據(jù)。

表1：

表示超級表名稱（需與表格實際名稱一致）

[#標題]：

是結構化引用的特殊標識符，特指超級表的標題行（即列名所在行）。

INDIRECT參數(shù)雙引號包裹：

因INDIRECT函數(shù)的參數(shù)需以文本形式輸入引用路徑。

這樣做的優(yōu)勢是實現(xiàn)動態(tài)范圍擴展性：

超級表的優(yōu)勢在于動態(tài)擴展：新增數(shù)據(jù)會自動納入引用范圍。通過[#標題]語法，無論后續(xù)是否新增列，都能精準定位標題行，無需手動調(diào)整公式范圍。

我們找一個任意空白單元格輸入公式做測試：

=INDIRECT("表1[#標題]")

則會將字符串解析為對"表1"標題行的引用，得到數(shù)組溢出結果：

{"蔬菜","水果","肉類"}

所以我們將“=INDIRECT("表1[#標題]")”這個公式帶入數(shù)據(jù)有效性（或數(shù)據(jù)驗證），設置一級菜單：

選中需要設置一級下拉菜單的區(qū)域F2:F5，點擊“數(shù)據(jù)-有效性”，打開“數(shù)據(jù)有效性”功能面板，將“允許”設置為“序列”，在“來源”處，將“=INDIRECT("表1[#標題]")”公式輸入進去，這樣就可以動態(tài)引用數(shù)據(jù)源中的首行列標題{"蔬菜","水果","肉類"}了。

接下來嘗試設置二級聯(lián)動下拉菜單。

那么我們?nèi)绾伪硎綟列具體類別在數(shù)據(jù)源中對應類別的下方的具體名稱呢？我們可以這樣表示：

=INDIRECT("表1["&$F2&"]")

這樣我們就可以將F2單元格表示的“蔬菜”，在數(shù)據(jù)源A1:D4區(qū)域中，找到對應的列位置，也就是第一列列標題“蔬菜”，并將其垂直方向下面的所有蔬菜明細名稱分別提取出來。

所以我們將“=INDIRECT("表1["&$F2&"]")”這個公式帶入數(shù)據(jù)有效性（或數(shù)據(jù)驗證），設置二級菜單：

選中需要設置二級下拉菜單的區(qū)域G2:G5，點擊“數(shù)據(jù)-有效性”，打開“數(shù)據(jù)有效性”功能面板，將“允許”設置為“序列”，在“來源”處，將“=INDIRECT("表1["&$F2&"]")”公式輸入進去，這樣就可以引用數(shù)據(jù)源中對應的首行標題名稱下的全部明細內(nèi)容了。

至此，二級聯(lián)動下拉菜單已全部設置完成。無論怎樣變化數(shù)據(jù)源部分，二級聯(lián)動下拉菜單都可以實現(xiàn)實時的更新。

推薦閱讀：wps怎么做三級下拉菜單? 利用indirect函數(shù)制作三級下拉菜單的教程

最新評論