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