點(diǎn)擊藍(lán)字關(guān)注【秋葉AIExcel】

發(fā)送【7】

免費(fèi)領(lǐng) 1000+篇 Excel 精選教程！

本文作者：小爽

本文編輯：小蘭

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

然后再對輔助表設(shè)置對應(yīng)的「自定義名稱」，最后利用 Indirect 函數(shù)，通過【數(shù)據(jù)驗(yàn)證】達(dá)到我們想要的效果。

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

今天我們就來聊聊~

如下圖，左邊為參數(shù)表區(qū)域，現(xiàn)在我們要做成右表的形式：

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

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

那可千萬別錯過《和秋葉一起學(xué) Excel 速成實(shí)戰(zhàn)課》這門網(wǎng)課！

原價 999 元

231 節(jié)實(shí)戰(zhàn)課程

還送 900+套精選 Excel 模板

307 個函數(shù)清單

現(xiàn)在超值優(yōu)惠價

到手僅需99元！

去重

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

▋方法一：利用刪除重復(fù)值

具體步驟：

? 將大類復(fù)制到 E 列中。

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

然后在 G2 單元格中輸入公式，將去重后的數(shù)據(jù)進(jìn)行轉(zhuǎn)置：

=TRANSPOSE(E2:E4)

當(dāng)然，我們也可以使用選擇性粘貼轉(zhuǎn)置的功能。

動圖效果如下：

▋方法二：利用 Office365 的 Unique 函數(shù)

Unique 是一個去重函數(shù)。

在 G2 單元格中輸入公式：

=TRANSPOSE(UNIQUE(A2:A12))

一對多匹配

去重之后，得到轉(zhuǎn)置后的的標(biāo)題后，接下來，我們就要根據(jù)大類標(biāo)題，進(jìn)行一對多查詢啦~

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

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

具體操作：

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

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

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

動圖效果如下：

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

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

Row 函數(shù)能夠返回對應(yīng)的行數(shù)。

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

所以我們直接用 Vlookup 函數(shù)進(jìn)行匹配就可以達(dá)到所想要的結(jié)果，Iferror 函數(shù)將匹配不到的錯誤值替換為空值。

▋方法二：Office365 的 Filter 函數(shù)

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

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

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

Filter 函數(shù)基本語法：

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

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

公式中：

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

① 要篩選的數(shù)組或區(qū)域：$B$2:$B$12 小類列。

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

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

總結(jié)一下

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

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

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

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

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

反過來，我們把輔助表反轉(zhuǎn)過來，其實(shí)也是可以的，如下圖所示。

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

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

如果你想提升 Excel 技能，那么強(qiáng)烈推薦你學(xué)習(xí)《和秋葉一起學(xué) Excel 速成實(shí)戰(zhàn)課》。

課程包含 231節(jié)實(shí)戰(zhàn)課程，系統(tǒng)全面帶你掌握Excel 系統(tǒng)操作、商務(wù)圖表、函數(shù)公式、數(shù)據(jù)透視表、高效技巧，一站式學(xué)透 Excel 表格！

《和秋葉一起學(xué) Excel 速成實(shí)戰(zhàn)課》

課程原價 999 元

限時優(yōu)惠價，僅需 99 元

名師授課+系統(tǒng)教學(xué)+配套練習(xí)

長期有效，可反復(fù)回看

別猶豫了！趕緊掃碼搶課

每天學(xué)點(diǎn)小技巧，工作效率up~up~

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