EXCEL如何製作可以模糊搜尋的下拉式選單

閱讀時間約 4 分鐘

下拉式選單是EXCEL中一個超級方便的功能,可以簡化工作上重複輸入資料的動作,並且讓資料數據更加統一,但是如果今天選項太多了那麼下拉式選單就就必須花很多時間去尋找。

就像下圖一樣,下拉式選單有100個姓名,那麼就要花很多時間去找,根本考驗視力

資料太多,下拉式選單很不實用

資料太多,下拉式選單很不實用


那如果真的需要下拉式選單,可是選項又這麼多該怎麼辦呢?其實可以用函數設定成,輸入第一個字,下拉式選單就自動會顯示符合第一個字所有的資料,這樣資料很多的時候也不用找半天了,你的眼睛也會感謝你哦😏

輸入第一個字,自動列出符合的內容

輸入第一個字,自動列出符合的內容


方法一、手動排序法

  1. 先將下拉式選單的資料來源排序(由A到Z或Z到A都可以)
  2. 選取要設定下拉式選單的範圍
  3. 資料-->資料驗證-->清單
  4. 輸入=OFFSET($A$1,MATCH($D1&"*",A:A,0)-1,,COUNTIF(A:A,$D1&"*"),)

函數說明:

  • [公式1]-->MATCH($D1&"*",A:A,0) : 找到D1儲存格,輸入姓氏是在A欄第幾個儲存格
  • [公式2]-->COUNTIF(A:A,$D1&"*") : 計算A欄有多少儲存格,是D1輸入的姓氏
  • OFFSET($A$1,[公式1]-1,,[公式2],) : 從A1儲存格開始往下位移[公式1]-1個儲存格,並且包含[公式2]的儲存格數量
raw-image

設定好之後要記得到資料驗證視窗中的錯誤提醒,把輸入的資料不正確時警訊打勾取消,這樣才能夠正常使用哦

raw-image


方法二:函數排序法(全版本通用)

假設A欄是原始的資料清單,使用輔助欄將原始的資料清單依照第一個字進行排序,這樣才能順利製作模糊查詢下拉式選單

  1. 輔助欄B1=CODE(A1)+ROW()*0.00001
  2. 輔助欄C1=RANK.EQ(B1,$B$1:$B$100,1)
  3. 輔助欄D1=INDEX(A:A,MATCH(ROW(A1),C:C,0))
  4. 資料驗證=OFFSET($D$1,MATCH($F1&"*",D:D,0)-1,,COUNTIF(D:D,$F1&"*"),)

函數說明

  • B1=CODE(A1)+ROW()0.00001 : 利用CODE函數取出每個儲存格第一個字的代碼,加上列號*0.00001(一個極小值),使所以數據不重複
  • C1=RANK.EQ(B1,$B$1:$B$100,1) : 利用RANK.EQ函數將B欄抓出來的代碼進行數字進行名次的排序
  • D1=INDEX(A:A,MATCH(ROW(A1),C:C,0)) : 利用MATCH配合ROW函數將C欄的排序小到大的位置找出來,在使用INDEX配合MATCH找出來的位置將姓氏的第一個字相同的排序再一起
  • 資料驗證的公式同方法一
raw-image

與方法一一樣,設定好之後要記得到資料驗證視窗中的錯誤提醒,把輸入的資料不正確時警訊打勾取消,這樣才能夠正常使用哦

raw-image



方法三:函數排序法(365版)

如果你的版本比較新,有SORT函數,那麼恭喜你輔助欄只需要短短的一欄就可以了🤩

  1. 輔助欄B1=SORT(A:A,1,1)
  2. 資料驗證=OFFSET($B$1,MATCH($D1&"*",B:B,0)-1,,COUNTIF(B:B,$D1&"*"),)

函數說明

  • B1=SORT(A:A,1,1) : 利用SORT函數直接將A欄的原始資料進行排序,SORT函數在365版本的EXCEL才可以使用
  • 資料驗證的公式同方法一
raw-image

365也是一樣的,設定好之後要記得到資料驗證視窗中的錯誤提醒,把輸入的資料不正確時警訊打勾取消,這樣才能夠正常使用哦

raw-image

看文字圖片還不夠清楚的話,來看一下影片吧,看教學影片之前可以先下載練習檔,學中做、做中學效果更好哦。

檔案下載



覺得分享有幫助到你在工作上提升效率📈
可以追蹤我的方格子或請我喝杯咖啡☕
也可以訂閱EXCEL設計新思維👓
可以看到更多增加效率的好方法唷~
raw-image
職場上有相關問題,加入我的line社群討論唷~
追蹤我的IG
追蹤我的粉絲團
追蹤我的Dcard


18.3K會員
243內容數
此專題旨在幫助職場人士提升工作效率、提升專注力並更有效地管理時間,以達到更高的生產力和工作成果。在這個快節奏且競爭激烈的職場環境中,掌握提升效率的技巧尤為重要,主要會著重於分享OFFICE上最常使用的軟體,EXCEL、PPT、WORD各種增加效率的小技巧。
留言0
查看全部
發表第一個留言支持創作者!
效率基地 的其他內容
要下班前老闆突然說,有1000筆資料需要換行,這時如果用ALT+ENTER一筆一筆換行,要按1000次!!你有考慮過鍵盤的感受嗎??? NOTE: ATL+ENTER是EXCEL換行輸入的快捷鍵 這邊分享三種方法,不論是1000筆資料還是10000筆資料通通都1秒解決,輕鬆應付難搞的老闆。
工作或職場上,免不了有一些機密不適合公開讓所有人都能觀看,特定人士必須輸入密碼才能看到,例如:考核、報價等,這樣的需求要如何做到呢? 其實這樣的效果,完全不需要用到函數,只需要稍微設定一下儲存格格式與條件式格式設定就可以達成了。 1.5分鐘保證班,現在就花1分半的時間看影片把這個效果學起來吧
建立序號跟流水號是資料中少不了的元素,但如何根據各種不同的需求來填入合適的流水號呢? 今天就來分享4種建立流水號的方法,每個的使用場合都不同唷 LEVEL 1 自動填滿填入序號、流水號 如果輸入1直接向下填滿,不會變成序號,這時候配合CTRL鍵,再向下填滿就可以快速跑出序號囉。 PS.選取1.
上一篇文章:EXCEL 將儲存格多個資料快速整理成一欄 你一定不知道的神仙級功能-左右對齊有介紹到左右對齊的其中一個使用方式,這集一樣是左右對齊,但是,是在不同場景的應用。 場景1:如何將B欄中的3個資訊,姓名、年分、等第,整理到同一個儲存格 這種字數相同的,用左右對齊最適合了,大約5秒就解決,
有一個資料,裡面包含了編號與姓名,但是很多內容都塞在同一個儲存格內,如果要把這些資料全部整理成一欄,那該怎麼做呢? 如果用土法煉鋼的方式,慢慢一個一個複製貼上,肯定需要貼超久的!! 其實EXCEL中有一個內建功能"左右對齊",一秒就取代這些複製貼上的動作了!! 也可以到YT看有字幕跟語
建立序號或稱流水號情境百百種,今天的情境是,相同類別建立相同的流水編號或序號,這樣的序號如何快速建立呢? 其實用對函數,不用30秒就解決了!!!先花1分鐘看一下教學影片吧,觀看影片之前可以先下載檔案,學中做、做中學效果最好唷。(文末有函數說明) 檔案下載 函數說明 ✍🏾COUNTIF有
要下班前老闆突然說,有1000筆資料需要換行,這時如果用ALT+ENTER一筆一筆換行,要按1000次!!你有考慮過鍵盤的感受嗎??? NOTE: ATL+ENTER是EXCEL換行輸入的快捷鍵 這邊分享三種方法,不論是1000筆資料還是10000筆資料通通都1秒解決,輕鬆應付難搞的老闆。
工作或職場上,免不了有一些機密不適合公開讓所有人都能觀看,特定人士必須輸入密碼才能看到,例如:考核、報價等,這樣的需求要如何做到呢? 其實這樣的效果,完全不需要用到函數,只需要稍微設定一下儲存格格式與條件式格式設定就可以達成了。 1.5分鐘保證班,現在就花1分半的時間看影片把這個效果學起來吧
建立序號跟流水號是資料中少不了的元素,但如何根據各種不同的需求來填入合適的流水號呢? 今天就來分享4種建立流水號的方法,每個的使用場合都不同唷 LEVEL 1 自動填滿填入序號、流水號 如果輸入1直接向下填滿,不會變成序號,這時候配合CTRL鍵,再向下填滿就可以快速跑出序號囉。 PS.選取1.
上一篇文章:EXCEL 將儲存格多個資料快速整理成一欄 你一定不知道的神仙級功能-左右對齊有介紹到左右對齊的其中一個使用方式,這集一樣是左右對齊,但是,是在不同場景的應用。 場景1:如何將B欄中的3個資訊,姓名、年分、等第,整理到同一個儲存格 這種字數相同的,用左右對齊最適合了,大約5秒就解決,
有一個資料,裡面包含了編號與姓名,但是很多內容都塞在同一個儲存格內,如果要把這些資料全部整理成一欄,那該怎麼做呢? 如果用土法煉鋼的方式,慢慢一個一個複製貼上,肯定需要貼超久的!! 其實EXCEL中有一個內建功能"左右對齊",一秒就取代這些複製貼上的動作了!! 也可以到YT看有字幕跟語
建立序號或稱流水號情境百百種,今天的情境是,相同類別建立相同的流水編號或序號,這樣的序號如何快速建立呢? 其實用對函數,不用30秒就解決了!!!先花1分鐘看一下教學影片吧,觀看影片之前可以先下載檔案,學中做、做中學效果最好唷。(文末有函數說明) 檔案下載 函數說明 ✍🏾COUNTIF有
你可能也想看
Google News 追蹤
Thumbnail
接下來第二部分我們持續討論美國總統大選如何佈局, 以及選前一週到年底的操作策略建議 分析兩位候選人政策利多/ 利空的板塊和股票
Thumbnail
🤔為什麼團長的能力是死亡筆記本? 🤔為什麼像是死亡筆記本呢? 🤨作者巧思-讓妮翁死亡合理的幾個伏筆
Thumbnail
【特殊圖表教學目錄傳送門 : EXCEL特殊圖表大合輯 | 持續更新中】 EXCEL環圈圖(Doughnut Chart)是一種圖表類型,類似於圓餅圖,但具有中空的中心,使其更適合表現多個資料系列。它能夠幫助您清楚地展示各項目在總體中所佔的百分比比例,並且能夠顯示多層級的數據,
Thumbnail
在工作中,我們經常需要處理各種報表,而其中一項任務就是每天手動更新報表。這可能是一個繁瑣且容易被遺忘的工作,但有幸的是,我們可以利用VBA(Visual Basic for Applications)和Windows工作排程器來自動化這個過程,讓我們在不知不覺中完成這項任務。
Thumbnail
在職場上,名牌是一種常見的禮儀用品,用來展示個人身份和資訊。在會議、研討會、招聘會等場合,名牌可以幫助我們快速認識新朋友,建立良好的第一印象。 手繪名牌雖然可以展現個人特色,但製作起來比較費時費力。而使用Excel製作名牌,可以快速生成專業的雙面名牌,並且可以根據需要進行修改和調整。 如何使用E
Thumbnail
許多系統再產出資料的時候,會將相同的內容直接省略,以節省一些空間,那麼問題來了,這樣的資料如果要進行分析,會有些阻礙,部分關鍵字變成了空格就會無法正常分析,這時候就會需要將那些空格全部往下填滿。 但如果要自動填滿,資料又不連續一個一個拉,真的是會拉到天荒地老ㄟ 這邊分享一個方法,超級快速,不
Thumbnail
[設定格式化條件]是EXCEL超好用的一個功能,他可以自動依據條件,顯示不同的視覺效果來標示資料,或提醒使用者相關數據的上下限(較新的EXCEL版本稱為條件是格式設定)。 💥設定格式化條件問題 但當在設定條件時,常常會遇到,我需要一個條件符合整列都套用,使用起來都不符合預期,例如下圖的已結
Thumbnail
如何用XQ輸出可轉債資料到Excel   其實很多軟體,學著使用,你就可以節省整理資料的時間,之前寫過一篇文章教大家怎麼用XQ來整理可轉債的資料。文章如下: 接下來,我們來談談如何把收盤的資料整理到Excel,如果你想用Excel做一些資料輸出的話。 當你設定好可轉債的資料都在”自選
Thumbnail
本篇分享Excel表格轉為圖片、PDF、及html網頁的方式,以及如何突破使用老舊網站編輯器會碰到的疑難雜症。
Thumbnail
不曉得大家有沒有使用篩選工具後,想刪除特定表格,卻把隱藏的表格也給刪除的經驗呢?這篇文章想與你分享如何只刪除可見表格(保留隱藏表格),以及如何在Excel崁入隱藏版按鈕,一秒選取篩選的可見儲存格。
Thumbnail
接續上篇 00878 五百萬質押規劃,怎樣月領三萬元? 質押中3個須注意的事情 圖片引用 大綱: 6.投資標的股價波動性的比較 7.維持率最低計算 8.目前股價表現狀況評估 9.股價還原報酬率 10.還原股價比較00878 VS 0050 11.結語 本篇範例EXCEL 連結 4.股息安全邊際? 股
Thumbnail
Microsoft Excel是一款廣泛應用於資料處理和分析的辦公軟體,而快速切換字母大小寫是Excel中常見的操作。在本文中,我們將介紹如何在Excel中快速切換字母的大小寫,這些方法可以幫助用戶快速高效地完成文字處理工作,提昇工作效率。希望這些方法能夠為大家提供幫助。
Thumbnail
接下來第二部分我們持續討論美國總統大選如何佈局, 以及選前一週到年底的操作策略建議 分析兩位候選人政策利多/ 利空的板塊和股票
Thumbnail
🤔為什麼團長的能力是死亡筆記本? 🤔為什麼像是死亡筆記本呢? 🤨作者巧思-讓妮翁死亡合理的幾個伏筆
Thumbnail
【特殊圖表教學目錄傳送門 : EXCEL特殊圖表大合輯 | 持續更新中】 EXCEL環圈圖(Doughnut Chart)是一種圖表類型,類似於圓餅圖,但具有中空的中心,使其更適合表現多個資料系列。它能夠幫助您清楚地展示各項目在總體中所佔的百分比比例,並且能夠顯示多層級的數據,
Thumbnail
在工作中,我們經常需要處理各種報表,而其中一項任務就是每天手動更新報表。這可能是一個繁瑣且容易被遺忘的工作,但有幸的是,我們可以利用VBA(Visual Basic for Applications)和Windows工作排程器來自動化這個過程,讓我們在不知不覺中完成這項任務。
Thumbnail
在職場上,名牌是一種常見的禮儀用品,用來展示個人身份和資訊。在會議、研討會、招聘會等場合,名牌可以幫助我們快速認識新朋友,建立良好的第一印象。 手繪名牌雖然可以展現個人特色,但製作起來比較費時費力。而使用Excel製作名牌,可以快速生成專業的雙面名牌,並且可以根據需要進行修改和調整。 如何使用E
Thumbnail
許多系統再產出資料的時候,會將相同的內容直接省略,以節省一些空間,那麼問題來了,這樣的資料如果要進行分析,會有些阻礙,部分關鍵字變成了空格就會無法正常分析,這時候就會需要將那些空格全部往下填滿。 但如果要自動填滿,資料又不連續一個一個拉,真的是會拉到天荒地老ㄟ 這邊分享一個方法,超級快速,不
Thumbnail
[設定格式化條件]是EXCEL超好用的一個功能,他可以自動依據條件,顯示不同的視覺效果來標示資料,或提醒使用者相關數據的上下限(較新的EXCEL版本稱為條件是格式設定)。 💥設定格式化條件問題 但當在設定條件時,常常會遇到,我需要一個條件符合整列都套用,使用起來都不符合預期,例如下圖的已結
Thumbnail
如何用XQ輸出可轉債資料到Excel   其實很多軟體,學著使用,你就可以節省整理資料的時間,之前寫過一篇文章教大家怎麼用XQ來整理可轉債的資料。文章如下: 接下來,我們來談談如何把收盤的資料整理到Excel,如果你想用Excel做一些資料輸出的話。 當你設定好可轉債的資料都在”自選
Thumbnail
本篇分享Excel表格轉為圖片、PDF、及html網頁的方式,以及如何突破使用老舊網站編輯器會碰到的疑難雜症。
Thumbnail
不曉得大家有沒有使用篩選工具後,想刪除特定表格,卻把隱藏的表格也給刪除的經驗呢?這篇文章想與你分享如何只刪除可見表格(保留隱藏表格),以及如何在Excel崁入隱藏版按鈕,一秒選取篩選的可見儲存格。
Thumbnail
接續上篇 00878 五百萬質押規劃,怎樣月領三萬元? 質押中3個須注意的事情 圖片引用 大綱: 6.投資標的股價波動性的比較 7.維持率最低計算 8.目前股價表現狀況評估 9.股價還原報酬率 10.還原股價比較00878 VS 0050 11.結語 本篇範例EXCEL 連結 4.股息安全邊際? 股
Thumbnail
Microsoft Excel是一款廣泛應用於資料處理和分析的辦公軟體,而快速切換字母大小寫是Excel中常見的操作。在本文中,我們將介紹如何在Excel中快速切換字母的大小寫,這些方法可以幫助用戶快速高效地完成文字處理工作,提昇工作效率。希望這些方法能夠為大家提供幫助。