สอนวิธีทำ Drop Down List แบบ Search ได้ใน Excel (ไม่ต้องใช้ VBA)

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