เราส่วนมากใช้ Google กันจนเคยชินกับการใช้ตัวเลือกที่เขาเสนอมาให้ ซึ่งมันสะดวกมาก เพราะบางทีเราก็ไม่ค่อยแน่ใจนักว่าจะพิมพ์อะไรต่อไปดี 😅
ตัวอย่างที่งานฝ่ายบุคคลพบบ่อยได้แก่ คนที่ชื่อ “นา” แผนก Operation นี่เขาชื่อจริงว่าอะไรนะ โดยเฉพาะคนที่ไม่ได้ส่งเมล์หาคนคนนั้นบ่อย ๆ
แล้วถ้าเราใช้ Excel จะพอมีวิธีไหนช่วยกรองข้อมูลเฉพาะที่ตรงกับที่เราพิมพ์เข้าไปได้บ้าง แล้วให้ cell ที่ต้องการนำข้อมูลที่เกี่ยวข้องขึ้นมาแสดงผลเอง มาดูวิธีกันครับ
![](https://excelavista.com/wp-content/uploads/2023/01/example-excel-searchable-drop-down-list.png)
ก่อนอื่นข้อมูลตัวอย่างอยู่ในซีตชื่อ “data” โดยข้อมูลนี้ได้จากการสุ่มมาจาก https://kidhaina.com/thainamegenerator.html และเหมือนเช่นเคย (สำหรับผม 😁) ข้อมูลทำการ Format as Table แล้ว และตั้งชื่อเป็น tblData
![](https://excelavista.com/wp-content/uploads/2023/01/example-employees-data.png)
มีอีก 2 คอลัมภ์ที่นำข้อมูลที่มีอยู่มาเชื่อมกัน โดยคอลัมภ์ i หรือ forlookup จะใช้แสดงผลลัพธ์เมื่อผู้ใช้เลือกชื้อใดชื่อหนึ่ง
ส่วนคอลัมภ์ j หรือ forsearch ใช้เพื่อช่วยให้เราสามารถค้นหาได้ทั้งภาษาไทยและภาษาอังกฤษ ดังภาพ
![](https://excelavista.com/wp-content/uploads/2023/01/example-employees-data-for-search-lookup.png)
ฟังก์ชันที่ใช้กรองข้อมูล
สำหรับการกรองข้อมูลที่มีอยู่เทียบกับข้อมูลที่ถูกพิมพ์เขามา เราจะใช้ฟังก์ชัน FILTER(), SEARCH() และ ISNUMBER() ทำงานร่วมกัน
สำหรับตัวอย่างผมแยกชีต “Data” กับ “Search” ออกจากกัน โดยข้อมูลที่จะให้ผู้ใช้พิมพ์และเลือกตัวเลือกจะอยู่ในเซลล์ B2 ของชีตชื่อ “Search”
ของเริ่มจากฟังก์ชัน SEARCH() ซึ่งใช้สำหรับค้นหาตัวอักษรหรือข้อความ โดยไม่สนความแตกต่างของตัวใหญ่หรือตัวเล็กในภาษาอังกฤษ โดยสูตรผมจะใส่ในเซลล์ L2 ตามนี้ก่อน
=SEARCH(Search!$B$2,tblData[forsearch])
ในที่นี้หมายถึงให้ค้นหาคำว่า “นา” ซึ่งถูกพิมพ์ไว้ในเซลล์ B2 ของชีตชื่อ Search โดยผลลัพธ์จะเป็นดังภาพ ซึ่งก็คือในแถว 6 เจอคำนี้อยู่ลำดับที่ 16 ส่วนแถวที่ 8 เจอที่ตัวแรก ไปเรื่อย ๆ
![](https://excelavista.com/wp-content/uploads/2023/01/search-function-return-value.png)
แต่เราจะใช้เฉพาะแถวที่เป็นผลเป็นตัวเลข เราก็เลยต้องพึ่งฟังก์ชัน ISNUMBER() ซึ่งผลจะได้เป็นไม่ TRUE ก็จะเป็น FALSE ดังภาพ
=ISNUMBER(SEARCH(Search!$B$2,tblData[forsearch]))
![](https://excelavista.com/wp-content/uploads/2023/01/after-isnumber-function-return-value.png)
และเราจะใช้ค่านี้ส่งเข้าไปให้ฟังก์ชัน FILTER() ช่วยกรองเอาเฉพาะข้อมูลที่ตรงกับที่พิมพ์มา ซึ่งก็คือค่าเป็น TRUE และสูตรจะกลายเป็น
=FILTER(tblData[ชื่อ-สกุล],ISNUMBER(SEARCH(Search!$B$2,tblData[forsearch])),"Not found")
โดยอาร์กิวเมนต์ตัวแรกจะเป็นข้อมูลที่เราต้องการ ซึ่งในที่นี้คือคอลัมภ์ “ชื่อ-สกุล” เพราะต้องการแสดงข้อมูลจากคอลัมภ์นี้
และเป็นสูตรที่เราใช้ก่อนหน้านี้ ⚠️ ซึ่งจะค้นหาจากคอลัมภ์ชื่อ “forsearch” เพราะเราต้องการค้นหาทั้งภาษาไทยและภาษาอังกฤษ
สุดท้ายถ้าไม่พบให้ส่งค่า “Not found” กลับมา
![](https://excelavista.com/wp-content/uploads/2023/01/after-filter-function-return-value-1024x385.png)
จะเห็นว่าเราจะใช้เฉพาะชื่อพนักงานที่มีคำว่า “นา” อยู่ด้วยกลับมา
และหากเราต้องการเรียงข้อมูลด้วย เราสามารถใส่ฟังก์ช้น SORT() เพิ่มเข้าไปอีกชั้นหนึ่งได้ และสูตรก็จะกลายเป็น
=SORT(FILTER(tblData[ชื่อ-สกุล],ISNUMBER(SEARCH(Search!$B$2,tblData[forsearch])),"Not found"))
![](https://excelavista.com/wp-content/uploads/2023/01/after-sort-function-return-value.png)
โดยฟังก์ชัน FILTER() จะให้ข้อมูลกลับมาเป็นไดนามิก อาร์เรย์ (dynamic array) ซึ่งผลลัพธ์จะส่งกลับมามากกว่า 1 ค่า และจะเรียงลงไปตามแถวเรื่อย ⚠️ ดังนั้นคอลัมภ์ L ไม่ควรจะมีอะไรนอกจากนี้ ไม่เช่นนั้นข้อมูลจะถูกวางทับไป แต่ถ้าเราจะแก้ไขอะไรก็แก้ไขแค่ L2 เท่านั้น
เมื่อเสร็จจากการเตรียมรายการข้อมูลที่จะให้ผู้พิมพ์เลือกแล้ว เราจะกลับไปที่ชีต Search และเลือกเซลล์ B2 เพื่อทำ Data Validation (Data > Data Validation > Data Validation…)
ที่ Allows: เลือกเป็น List และที่ Source: เลือกเป็นชีต Data เซลล์ L2 และพิมพ์เครื่องหมาย # เปิดท้าย 💡 เพื่อให้ Excel ดึงรายการด้านล่างมาดด้วย ดังภาพ
=Data!$L$2#
![](https://excelavista.com/wp-content/uploads/2023/01/create-data-validation.png)
และ💥ที่ลืมไม่ได้คือ เราต้องยกเลิก Error Alert ของ Data Validation ของเซลล์นี้ด้วย ไม่เช่นนั้นเราจะพิมพ์ค่าอื่น นอกเหนือจากที่มีข้อมูลไม่ได้ และ Excel จะแสดง Error ให้ผู้ใช้ทราบ
ซึ่งถ้าพิมพ์ข้อมูลที่ไม่พบ รายการจะแจ้งว่า “Not found” อยู่แล้ว
คลิกที่แถบ Error Alert และคลิกที่เครื่องหมายถูกที่มีอยู่ให้หายไป ซึ่งจะได้ลักษณะดังภาพ
![](https://excelavista.com/wp-content/uploads/2023/01/cancel-error-alert-data-validation.png)
เมื่อทำเสร็จ สมมติเราลองพิมพ์ “ya” เข้าไป เพื่อหาผู้ที่ในชื่อมีคำนี้ ก็จะได้ผลลัพธ์ดังภาพ
![](https://excelavista.com/wp-content/uploads/2023/01/after-select-name.png)
และหากเราเลือกเป็น “ชยา ภัชรปรีดา (คิด)” ในช่อง D2 ก็จะแสดงชื่อและแผนกของคน ๆ นั้นขึ้นมา ดังภาพด้านล่างนี้
![](https://excelavista.com/wp-content/uploads/2023/01/use-xlookup-to-find-user-selected.png)
เพียงเท่านี้ก็เรียบร้อยแล้วครับ หากติดขัดตรงไหนดูจากไฟล์ตัวอย่าง หรือ comment ไว้ด้านล่างได้นะครับ
Leave a Reply