|
原文標(biāo)題：《拒絕加班！這份 Excel 下拉列表超全合集你值得擁有！》 今天講一講「Excel 下拉列表的那些事兒」~
一級下拉列表一個簡單的數(shù)據(jù)驗(yàn)證，搞定一級下拉列表。
制作思路是將分類項(xiàng)目單獨(dú)放在一個參數(shù)表中，然后通過【數(shù)據(jù)驗(yàn)證】引用這些參數(shù)作為數(shù)據(jù)源。 具體的設(shè)置方法如下： ? 打開【數(shù)據(jù)驗(yàn)證】窗口。
? 驗(yàn)證條件選擇【序列】。 ? 選擇添加數(shù)據(jù)來源。
像產(chǎn)品類型、部門、省市等比較固定的分類信息，都可以利用下拉列表限制輸入的內(nèi)容，避免一種分類、多種寫法的情況出現(xiàn)。
二級下拉列表定義名稱 + 數(shù)據(jù)驗(yàn)證 + INDIRECT 函數(shù)，輕松創(chuàng)建二級下拉列表。 所謂二級下拉列表，就是第 2 級的列表選項(xiàng)，可以根據(jù)第 1 級的數(shù)據(jù)動態(tài)更新。
具體的設(shè)置方法如下： ? 準(zhǔn)備二級下拉列表數(shù)據(jù)
? 定義名稱選擇所有的列表數(shù)據(jù)，單擊【公式】選項(xiàng)卡，找到「定義的名稱」-【根據(jù)所選內(nèi)容創(chuàng)建】：
在彈出的窗口中，勾選「首行」，單擊【確定】。
這樣就給二級內(nèi)容起了個總體的名字，這個名字是「首行」單元格的內(nèi)容。比如：
? 制作下拉列表先設(shè)置好一級下拉列表，具體操作前文講過，這里就不重復(fù)了。 二級和一級下拉列表的步驟大同小異，只是在選擇來源時，需要用到 Indirect 函數(shù)：
PS：提示「源當(dāng)前包含錯誤」，是因?yàn)椤付壛斜怼挂玫摹敢患壛斜怼箚卧裰袥]有數(shù)據(jù)，導(dǎo)致源錯誤，不用理會，點(diǎn)「是」。 敲黑板： =indirect(A2) Indirect 函數(shù)是間接引用函數(shù)，可以返回由文本字符串所指定的引用。 比如這里引用的是 A2 單元格，但返回的結(jié)果是參數(shù)表 C2:C5 單元格里的值。即：引用 C 列省份中所包含的市級。
三級下拉列表三級下拉列表其實(shí)并沒有大多數(shù)小伙伴想象中那么難，如果你能學(xué)會一級、二級列表，我相信，這個小技巧也難不住你。
相比前兩者，三級下拉列表最大的不同，就是數(shù)據(jù)源。 三級內(nèi)容的表頭，是由一二級連接在一起的?？磮D更直觀 ↓
具體的設(shè)置方法如下： ? 定義名稱選擇列表數(shù)據(jù)，使用【定位】功能快速選擇所有的非空單元格。 再使用【公式】選項(xiàng)卡中的【根據(jù)所選內(nèi)容創(chuàng)建】的功能，批量的創(chuàng)建省份對應(yīng)城市選區(qū)的自定義名稱。
哦豁，好像翻車了…… Excel 提示「此選擇無效」。
仔細(xì)觀察一下表格，我們發(fā)現(xiàn)，有一處明顯沒有和其他區(qū)域連在一起，這個就是報(bào)錯的原因，Excel 重新識別并選中了一個新的區(qū)域。
這是由于 Excel 本身的機(jī)制引起的，如果出現(xiàn)了連續(xù)兩列行數(shù)相等且后面的列比它們的行數(shù)少，就會報(bào)錯。 不信的話，我們稍微調(diào)整一下列順序，把這兩列分開 ↓
然后再試一遍【定義名稱】。
最后檢查一下：
設(shè)置成功！ ? 創(chuàng)建三級下拉列表。這時還是需要借助【Indirect】函數(shù)，但是又稍微有點(diǎn)不同，公式： =Indirect（A2&B2） 即：同時引用一級列表和二級列表。
是不是很簡單！ 一二三級下拉列表，只涉及到了幾個非常基礎(chǔ)的知識點(diǎn)： ? 數(shù)據(jù)驗(yàn)證 ? 定義名稱 ? Indirect 函數(shù) 看完這篇文章，再動手練習(xí)一下，想必就掌握得七七八八了。 但我接下來要講的【搜索式下拉列表】，就稍微有那么 億 一點(diǎn)點(diǎn)難了。
搜索式下拉列表當(dāng)你在微信搜索框里輸入關(guān)鍵詞【秋葉 Excel】并確定，你可以搜索出這個平臺里所有與【秋葉 Excel】相關(guān)的內(nèi)容。 而搜索式下拉列表也是如此，雖然沒有微信搜索那么強(qiáng)大，但是它可以實(shí)現(xiàn)在 Excel 通過搜索關(guān)鍵詞，找到設(shè)置好的、固定的數(shù)據(jù)，快速選擇且錄入表格。
具體設(shè)置方法： ? 根據(jù)關(guān)鍵詞創(chuàng)建輔助列① 在 A 列填寫完整的省份列表； ② 創(chuàng)建根據(jù)關(guān)鍵詞篩選的輔助列： 將下列公式填入 B2 單元格，使用【CTRL+SHIFT+ENTER】組合鍵結(jié)束公式，向下填充。 公式： =IFERROR(INDEX($A$2:$A$35,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),$A$2:$A$35)0,$A$2:$A$35,),$A$2:$A$35,0),),ROW(A1))),)
公式雖然很長很難，但直接套用即可。 套用方法很簡單： 因?yàn)楣街?4 處標(biāo)藍(lán)的部分是完全一樣的：$A$2:$A$35，就是完整的省份列表所在單元格。 所以，直接把標(biāo)藍(lán)的部分換成你要做的列表區(qū)域就可以了！ ? 定義輔助列名稱① 點(diǎn)擊【公式】選項(xiàng)卡-【名稱管理器】-新建名稱。
② 新建名稱，名稱區(qū)輸入「省份列表」，引用位置輸入公式： =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$35)-COUNTIF(Sheet1!$B$2:$B$35,),1)
別看使用的公式很長，但好處是對 Excel 的版本沒有太高要求，Office2007 以上的版本和 WPS 都可以使用。 PS：如果是 WPS2019 及以上的版本，則自帶「搜索式下拉列表」~ ? 設(shè)置下拉列表選中需要設(shè)置下拉列表的單元格，打開數(shù)據(jù)驗(yàn)證窗口，在驗(yàn)證條件對話框的允許中選擇「序列」，來源填寫「= 省份列表」；
點(diǎn)擊「出錯警告」選項(xiàng)卡，取消勾選「輸入無效數(shù)據(jù)時顯示出錯警告 (S)」。
完成！
如果你只想知道怎么設(shè)置搜索式下拉列表，學(xué)會上面這些就夠了； 最后，能夠看到這里的同學(xué)，真的太不容易了！希望大家都能成功掌握上面這些知識點(diǎn)，提高效率，減輕工作壓力，擁抱生活！ 本文來自微信公眾號：秋葉 Excel （ID：excel100），作者：竺蘭，編輯：竺蘭 廣告聲明：文內(nèi)含有的對外跳轉(zhuǎn)鏈接（包括不限于超鏈接、二維碼、口令等形式），用于傳遞更多信息，節(jié)省甄選時間，結(jié)果僅供參考，IT之家所有文章均包含本聲明。 |
|
|
