คุณเคยมีปัญหามีรายการที่จะต้องแสดงใน Drop Down List จำนวนมากจนแสดงไม่ไหวหรือไม่?
วิธีแก้อันนึงที่ผมนำเสนอไปแล้วคือ การทำ Drop Down หลายชั้น แต่หากเราสามารถที่จะทำให้คนใช้งานพิมพ์ข้อความบางส่วนแล้วแสดงรายการที่มีคำนั้นๆ ได้เลยจะดีขนาดไหน?
หลายคนคิดว่าเรื่องแบบนี้ต้องใช้ VBA ทำเท่านั้น แต่ในความเป็นจริง เราก็สามารถเขียนสูตรเพื่อสร้าง “Drop down List แบบค้นหาได้” โดยจะใช้ Excel Version ไหนก็สามารถทำได้ครับ สุดยอดไปเลยมั้ยล่ะ!!
คลิปวีดีโอ
ในคลิปนี้ผมมีการอธิบายโดยละเอียดถึงวิธีทำตั้งแต่ต้นจนจบ ลองดูได้เลยครับ
ไฟล์ประกอบการทำตาม
ไฟล์เริ่มต้น :
https://drive.google.com/open?id=1qiWOJjoB9dGyPDt9HjByaCLUwky8_jqX
ไฟล์จบ :
https://drive.google.com/open?id=17QXXR5xVtHqxPgU1ay5biyg4Vcd1ypod
หลักการทำงานของ Drop Down List แบบค้นหาได้
- ใช้ ISNUMBER + SEARCH เพื่อเช็คว่ารายการไหน มีคำค้นหาอยู่บ้าง
- พยายามเอารายการที่เจอ มากองรวมกันข้างบนด้วย INDEX
- ในนี้ผมมีใช้ฟังก์ชัน ROW กับ RANK เพื่อช่วยจัดอันดับตัวที่เจออันดับ 1,2,3 ด้วยครับ
- จากนั้นใช้ OFFSET เพื่อเลือก Range ของรายการที่เจอทั้งหมด
- เอาสูตร OFFSET ที่ได้ไปตั้งชื่อ
- เอาชื่อที่ตั้งไปใส่ใน Data Validation List
- เลือก Option ว่าไม่ต้องแสดง Error Alert
- ใช้ IF ดักว่าถ้าหาไม่เจอ ให้แสดงคำว่า “ไม่เจอข้อมูล”

สรุปสูตร
IsFound =ISNUMBER(SEARCH($A$2,[Province])) GetRowNum =IF([@IsFound],ROW([@Province]),"") Rank =RANK.EQ([@GetRowNum],[GetRowNum],1) Rearrange =INDEX([Province],MATCH(ROW([@Province])-1,[Rank],0)) count=--NOT(ISERROR([@Rearrange])) =IF(SUM(Table1[count])=0,$K$4,OFFSET(Table1[[#Headers],[Rearrange]],1,0,SUM(Table1[count]),1)) ใน A2 คือช่องที่จะทำ Dropdown ใน K4 มีคำว่า ไม่เจอข้อมูล
อยากใช้ Drop Down หลายที่ทำไง?
ถ้าอยากให้ Dropdown List ที่ทำมาสามารถ Copy ไปใช้ได้หลายๆ ที่ ต้องเปลี่ยนสูตรตรง IsFound เล็กน้อยนะครับ
แก้จาก
IsFound =ISNUMBER(SEARCH($A$2,[Province]))
เป็น
IsFound =ISNUMBER(SEARCH(CELL("contents"),[Province]))
หลักการคือ ฟังก์ชัน CELL หากไม่ระบุ Cell Reference จะอ้างอิงถึงช่องล่าสุดที่มีการแก้ไขได้ ดังนั้น CELL(“contents”) จะดึงข้อมูลจากช่องที่มีการแก้ไขล่าสุดไปใช้ใน SEARCH ได้ครับ
แหล่งอ้างอิง
- ถ้าเกิดใช้ VBA ก็จะทำคล้ายๆ เว็บนี้ได้ครับ https://trumpexcel.com/excel-drop-down-list-with-search-suggestions/
- ถ้ามี Excel 365 Insider ที่มี Dynamic Array ก็จะเขียนสูตรง่ายขึ้นแบบคลิปนี้ได้ด้วยเช่นกัน https://youtu.be/Z-h2UER3b_0
ทำได้ทุกขั้นตอน แต่ พอถึงขั้นตอนสุดท้าย จบคะ ไม่ขึ้นเหมือนของ อ.คะ ทำไงดี คะ
ถ้าไม่ขึ้นตอนจบสูตรน่าจะผิดนะครับ ลองดูดีๆ
เปลี่ยน Scope ให้สามารถไปใช้ได้ทั้ง workbook ได้ไหมครับ
(จริงๆแล้วต้องการจะแยกไปไว้ sheet ต่างหากแล้วซ่อน Table Range Validation นี้ไว้)
ลองทำแล้ว แต่ตรง scope เป็นตัวใส ไม่ยอมให้เปลี่ยนครับ
สอบถามหน่อยครับ ผมลองตามตัวอย่างแต่เจอปัญหาดังนี้
1. เวลาพิมพ์ แล้วกด drop down จะกดไม่ได้ ต้อง enter ก่อน แล้วค่อยกด drop down ถึงจะขึ้น list เป็นเพราะอะไรครับ
2. ถ้ายังไม่ search อะไรเลย พอกด drop down จะไม่ขึ้น list ให้เลือก (คอลัมภ์ search เป็น false ทั้งหมด)
ข้อมูลเพิ่มเติม : ผมใช้ drop down ตัวเดียวกันแต่หลายที่ เลยใช้ cell(“Content”) ส่วนข้อมูลที่ต้องการแสดงมีประมาณ 100 ตัวครับ
รบกวนด้วยครับ ขอบคุณครับ