Excel“搜索”式下拉菜單，無需排序，小白慎進(jìn)！
Excel“搜索”式下拉菜單，無需排序，小白慎進(jìn)！
Excel1994
實(shí)用的數(shù)據(jù)可視化案例教程，源于Excel，不止于Excel
以下文章來源于Excel表哥 ，作者FunExcel
Excel rapid the world!
上期我們分享了 Excel聯(lián)想式下拉菜單制作教程 ，可以簡化大家在下拉菜單過長時(shí)的數(shù)據(jù)錄入工作。
文章末尾給大家提出了一個(gè)問題： 為什么下拉列表數(shù)據(jù)源需要先排序？
今天我們繼續(xù)分享一個(gè)不用對(duì)數(shù)據(jù)源排序也能實(shí)現(xiàn)的下拉列表制作方法。
先看最終的效果動(dòng)圖：
今日分享難度指數(shù)：★★★★☆
=IFERROR(INDEX(數(shù)據(jù)源!$B$3:$B$999,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),數(shù)據(jù)源!$B$3:$B$999),數(shù)據(jù)源!$B$3:$B$999,""),數(shù)據(jù)源!$B$3:$B$999,0),2^16),ROW(B1))),"")
▎注意：這是一個(gè)數(shù)組公式，輸入完成后需要按Ctrl+Shift+Enter三鍵完成數(shù)組公式的設(shè)置。
這個(gè)公式似乎有點(diǎn)長??！不過公式雖長，我們卻可以通過定義名稱的形式進(jìn)行縮減。
然后我們的公式就變短了：
=IFERROR(INDEX(List,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),List),List,""),List,0),2^16),ROW(B1))),"")
=IFERROR(INDEX(List,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),List),List,""),List,0),2^16),ROW(B1))),"")
-
FIND函數(shù)返回輸入的字符 $B$3 在數(shù)據(jù)源!$B$3:$B$999中每一行字符的起始位置。
IF函數(shù)如果匹配到數(shù)據(jù)則返回?cái)?shù)據(jù)源中該行數(shù)據(jù)，否則返回空""(,""也可省略)。
MATCH函數(shù)根據(jù)IF函數(shù)的結(jié)果返回在數(shù)據(jù)源中相應(yīng)的行號(hào)，如果未匹配到$B$3中的關(guān)鍵字則返回#VALUE!錯(cuò)誤值。
IFERROR函數(shù)則將#VALUE!錯(cuò)誤值修改為一個(gè)極大值2^16=65536。
-
SMALL函數(shù)根據(jù)J列查找出來的行號(hào)進(jìn)行排序。由于SMALL函數(shù)不支持錯(cuò)誤值，這也是上一步必須用IFERROR函數(shù)修正MATCH返回結(jié)果的原因。
INDEX函數(shù)根據(jù)SMALL函數(shù)返回的符合項(xiàng)從小到大的行號(hào)查找出所有的包含關(guān)鍵詞的選項(xiàng)。
最后再次通過IFERROR函數(shù)屏蔽#REF!引用錯(cuò)誤。
至此，終于完成了核心公式的設(shè)置。
最后我們把查找關(guān)鍵詞替換為CELL("contents")就可以實(shí)現(xiàn)任意位置(除了輔助列)輸入字符，在F列匹配出數(shù)據(jù)源中所有包含該關(guān)鍵詞的符合項(xiàng)了。
仔細(xì)看，WPS確實(shí)自帶聯(lián)想式輸入技能！
不得不說這是Excel用戶非常羨慕的一個(gè)功能。那么恰好看到表哥這篇文章的Excel用戶的讀者朋友，希望能彌補(bǔ)這個(gè)小遺憾。
感謝Excel表哥的干貨，如果你想了解更多 Excel文件管理、項(xiàng)目管理模板 ，請關(guān)注下方表哥的公眾號(hào) @Excel表哥 ，在該公眾號(hào)聊天框回復(fù)【 聯(lián)想式下拉菜單2 】，獲取本文件。
鏈接:https://pan.baidu.com/s/1iEEIdzZeB8w_lVdGGMXARA
提取碼:389s