คุณเคยมีปัญหามีรายการที่จะต้องแสดงใน 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
