制作二級下拉列表必備，這個奇葩方法，你可能真的沒見過！

點擊藍字關注【秋葉AIExcel】

發送【7】

免費領 1000+篇 Excel 精選教程！

本文作者：小爽

本文編輯：小蘭

在制作二級下拉列表的時候，我們通常需要先制作下圖這樣的輔助表。

然后再對輔助表設置對應的「自定義名稱」，最后利用 Indirect 函數，通過【數據驗證】達到我們想要的效果。

那么問題來了，上面這種輔助表是怎么樣做出來的呢？

今天我們就來聊聊~

如下圖，左邊為參數表區域，現在我們要做成右表的形式：

要完成這個效果，需要用到去重，還有一對多匹配的知識。

如果你還想學習更多 Excel 實用辦公技巧，讓工作變得高效又省力！

那可千萬別錯過《和秋葉一起學 Excel 速成實戰課》這門網課！

原價 999 元

231 節實戰課程

還送 900+套精選 Excel 模板

307 個函數清單

現在超值優惠價

到手僅需99元！

去重

我們需要對大類做一個去重操作，同時通過轉置函數把豎向轉為橫向。

▋方法一：利用刪除重復值

具體步驟：

? 將大類復制到 E 列中。

? 選中 E1:E12，在【數據】選項卡下，單擊【刪除重復項】-【確定】，即可將數據去重。

然后在 G2 單元格中輸入公式，將去重后的數據進行轉置：

=TRANSPOSE(E2:E4)

當然，我們也可以使用選擇性粘貼轉置的功能。

動圖效果如下：

▋方法二：利用 Office365 的 Unique 函數

Unique 是一個去重函數。

在 G2 單元格中輸入公式：

=TRANSPOSE(UNIQUE(A2:A12))

一對多匹配

去重之后，得到轉置后的的標題后，接下來，我們就要根據大類標題，進行一對多查詢啦~

▋方法一：用 Countif 做輔助列，再用 Vlookup 查詢

由于 Vlookup 函數只能返回第一次出現的值，所以對于一對多匹配，我們的做法就是利用 Countif 函數拉燈模式做輔助列，然后利用 Vlookup 函數索引每一次出現的位置。

具體操作：

? 選中 A 列，按住快捷鍵【Ctrl+Shift++】，向左新增一列。

? 在 A2 單元格中輸入如下公式并向下填充：

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

動圖效果如下：

? 在 G3 單元格中輸入如下公式，并向下向右填充公式：

=IFERROR(VLOOKUP(ROW(A1)&G$2,$A$1:$C$12,3,0),"")

Row 函數能夠返回對應的行數。

Row(A1)&G$2 就是 1Word； 向下拉就是，Row(A2)&G$2 ，就是 2Word； 向右拉就是，Row(B1)&F$2，就是 1PPT。

所以我們直接用 Vlookup 函數進行匹配就可以達到所想要的結果，Iferror 函數將匹配不到的錯誤值替換為空值。

▋方法二：Office365 的 Filter 函數

Filter 函數是一個篩選函數，它是做一對多查詢的利器，前面我們通過輔助列的做法完成，現在使用 Filter 函數，只需一個公式！

在 F3 單元格中，輸入如下公式，向右填充：

=FILTER($B$2:$B$12,$A$2:$A$12=F$2)

Filter 函數基本語法：

=FILTER(要篩選的數組或區域,篩選條件,[是否忽略空值])

Filter 函數是一個篩選函數，它可以將數組中條件為 True 的結果篩選出來。

公式中：

=FILTER($B$2:$B$12,$A$2:$A$12=F$2)

① 要篩選的數組或區域：$B$2:$B$12 小類列。

② 篩選條件：$A$2:$A$12=F$2 大類是否等于「Word」。

就是將小類列中包含 Word 的，全部篩選出來，也就達到我們想要的一對多查詢效果啦~

總結一下

在制作二級下拉列表的時候，我們通常需要制作一個輔助表，以便做好準備工作。

本文就介紹了下圖的制作方法。

去重的話，我們用的是刪除重復值功能，如果是 Office365 的話，直接 Unique 函數就可以搞定！

一對多匹配的話，我們就用到了 Countif 函數做一個輔助列，最后再用 Vlookup 進行查詢匹配，這是一個很常見的思路。

如果是 Office365 的話，一個 Filter 函數就可以搞定一對多的效果。

反過來，我們把輔助表反轉過來，其實也是可以的，如下圖所示。

制作方法也是跟上文介紹的思路差不多，小伙伴有空可以去試試~

看完本文，是不是直呼「漲見識了！！！」

如果你想提升 Excel 技能，那么強烈推薦你學習《和秋葉一起學 Excel 速成實戰課》。

課程包含 231節實戰課程，系統全面帶你掌握Excel 系統操作、商務圖表、函數公式、數據透視表、高效技巧，一站式學透 Excel 表格！

《和秋葉一起學 Excel 速成實戰課》

課程原價 999 元

限時優惠價，僅需 99 元

名師授課+系統教學+配套練習

長期有效，可反復回看

別猶豫了！趕緊掃碼搶課

每天學點小技巧，工作效率up~up~

如果本篇文章對你有幫助，那就點個贊支持一下吧！