การทำตัวเลือก drop down แบบค้นหาได้

เราส่วนมากใช้ Google กันจนเคยชินกับการใช้ตัวเลือกที่เขาเสนอมาให้ ซึ่งมันสะดวกมาก เพราะบางทีเราก็ไม่ค่อยแน่ใจนักว่าจะพิมพ์อะไรต่อไปดี 😅

ตัวอย่างที่งานฝ่ายบุคคลพบบ่อยได้แก่ คนที่ชื่อ “นา” แผนก Operation นี่เขาชื่อจริงว่าอะไรนะ โดยเฉพาะคนที่ไม่ได้ส่งเมล์หาคนคนนั้นบ่อย ๆ

แล้วถ้าเราใช้ Excel จะพอมีวิธีไหนช่วยกรองข้อมูลเฉพาะที่ตรงกับที่เราพิมพ์เข้าไปได้บ้าง แล้วให้ cell ที่ต้องการนำข้อมูลที่เกี่ยวข้องขึ้นมาแสดงผลเอง มาดูวิธีกันครับ

ก่อนอื่นข้อมูลตัวอย่างอยู่ในซีตชื่อ “data” โดยข้อมูลนี้ได้จากการสุ่มมาจาก https://kidhaina.com/thainamegenerator.html และเหมือนเช่นเคย (สำหรับผม 😁) ข้อมูลทำการ Format as Table แล้ว และตั้งชื่อเป็น tblData

มีอีก 2 คอลัมภ์ที่นำข้อมูลที่มีอยู่มาเชื่อมกัน โดยคอลัมภ์ i หรือ forlookup จะใช้แสดงผลลัพธ์เมื่อผู้ใช้เลือกชื้อใดชื่อหนึ่ง

ส่วนคอลัมภ์ j หรือ forsearch ใช้เพื่อช่วยให้เราสามารถค้นหาได้ทั้งภาษาไทยและภาษาอังกฤษ ดังภาพ

ฟังก์ชันที่ใช้กรองข้อมูล

สำหรับการกรองข้อมูลที่มีอยู่เทียบกับข้อมูลที่ถูกพิมพ์เขามา เราจะใช้ฟังก์ชัน FILTER(), SEARCH() และ ISNUMBER() ทำงานร่วมกัน

สำหรับตัวอย่างผมแยกชีต “Data” กับ “Search” ออกจากกัน โดยข้อมูลที่จะให้ผู้ใช้พิมพ์และเลือกตัวเลือกจะอยู่ในเซลล์ B2 ของชีตชื่อ “Search”

ของเริ่มจากฟังก์ชัน SEARCH() ซึ่งใช้สำหรับค้นหาตัวอักษรหรือข้อความ โดยไม่สนความแตกต่างของตัวใหญ่หรือตัวเล็กในภาษาอังกฤษ โดยสูตรผมจะใส่ในเซลล์ L2 ตามนี้ก่อน

=SEARCH(Search!$B$2,tblData[forsearch])

ในที่นี้หมายถึงให้ค้นหาคำว่า “นา” ซึ่งถูกพิมพ์ไว้ในเซลล์ B2 ของชีตชื่อ Search โดยผลลัพธ์จะเป็นดังภาพ ซึ่งก็คือในแถว 6 เจอคำนี้อยู่ลำดับที่ 16 ส่วนแถวที่ 8 เจอที่ตัวแรก ไปเรื่อย ๆ

แต่เราจะใช้เฉพาะแถวที่เป็นผลเป็นตัวเลข เราก็เลยต้องพึ่งฟังก์ชัน ISNUMBER() ซึ่งผลจะได้เป็นไม่ TRUE ก็จะเป็น FALSE ดังภาพ

=ISNUMBER(SEARCH(Search!$B$2,tblData[forsearch]))

และเราจะใช้ค่านี้ส่งเข้าไปให้ฟังก์ชัน FILTER() ช่วยกรองเอาเฉพาะข้อมูลที่ตรงกับที่พิมพ์มา ซึ่งก็คือค่าเป็น TRUE และสูตรจะกลายเป็น

=FILTER(tblData[ชื่อ-สกุล],ISNUMBER(SEARCH(Search!$B$2,tblData[forsearch])),"Not found")

โดยอาร์กิวเมนต์ตัวแรกจะเป็นข้อมูลที่เราต้องการ ซึ่งในที่นี้คือคอลัมภ์ “ชื่อ-สกุล” เพราะต้องการแสดงข้อมูลจากคอลัมภ์นี้

และเป็นสูตรที่เราใช้ก่อนหน้านี้ ⚠️ ซึ่งจะค้นหาจากคอลัมภ์ชื่อ “forsearch” เพราะเราต้องการค้นหาทั้งภาษาไทยและภาษาอังกฤษ

สุดท้ายถ้าไม่พบให้ส่งค่า “Not found” กลับมา

จะเห็นว่าเราจะใช้เฉพาะชื่อพนักงานที่มีคำว่า “นา” อยู่ด้วยกลับมา

และหากเราต้องการเรียงข้อมูลด้วย เราสามารถใส่ฟังก์ช้น SORT() เพิ่มเข้าไปอีกชั้นหนึ่งได้ และสูตรก็จะกลายเป็น

=SORT(FILTER(tblData[ชื่อ-สกุล],ISNUMBER(SEARCH(Search!$B$2,tblData[forsearch])),"Not found"))

โดยฟังก์ชัน FILTER() จะให้ข้อมูลกลับมาเป็นไดนามิก อาร์เรย์ (dynamic array) ซึ่งผลลัพธ์จะส่งกลับมามากกว่า 1 ค่า และจะเรียงลงไปตามแถวเรื่อย ⚠️ ดังนั้นคอลัมภ์ L ไม่ควรจะมีอะไรนอกจากนี้ ไม่เช่นนั้นข้อมูลจะถูกวางทับไป แต่ถ้าเราจะแก้ไขอะไรก็แก้ไขแค่ L2 เท่านั้น

เมื่อเสร็จจากการเตรียมรายการข้อมูลที่จะให้ผู้พิมพ์เลือกแล้ว เราจะกลับไปที่ชีต Search และเลือกเซลล์ B2 เพื่อทำ Data Validation (Data > Data Validation > Data Validation…)

ที่ Allows: เลือกเป็น List และที่ Source: เลือกเป็นชีต Data เซลล์ L2 และพิมพ์เครื่องหมาย # เปิดท้าย 💡 เพื่อให้ Excel ดึงรายการด้านล่างมาดด้วย ดังภาพ

=Data!$L$2#

และ💥ที่ลืมไม่ได้คือ เราต้องยกเลิก Error Alert ของ Data Validation ของเซลล์นี้ด้วย ไม่เช่นนั้นเราจะพิมพ์ค่าอื่น นอกเหนือจากที่มีข้อมูลไม่ได้ และ Excel จะแสดง Error ให้ผู้ใช้ทราบ

ซึ่งถ้าพิมพ์ข้อมูลที่ไม่พบ รายการจะแจ้งว่า “Not found” อยู่แล้ว

คลิกที่แถบ Error Alert และคลิกที่เครื่องหมายถูกที่มีอยู่ให้หายไป ซึ่งจะได้ลักษณะดังภาพ

เมื่อทำเสร็จ สมมติเราลองพิมพ์ “ya” เข้าไป เพื่อหาผู้ที่ในชื่อมีคำนี้ ก็จะได้ผลลัพธ์ดังภาพ

และหากเราเลือกเป็น “ชยา ภัชรปรีดา (คิด)” ในช่อง D2 ก็จะแสดงชื่อและแผนกของคน ๆ นั้นขึ้นมา ดังภาพด้านล่างนี้

เพียงเท่านี้ก็เรียบร้อยแล้วครับ หากติดขัดตรงไหนดูจากไฟล์ตัวอย่าง หรือ comment ไว้ด้านล่างได้นะครับ