เทพเอ็กเซล ช่วย "ปลุกเทพ Excel ในตัวคุณ"
0
  • No products in the cart.
Excel ทั่วไปHighlights : บทความแนะนำ

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

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

คุณเคยมีปัญหามีรายการที่จะต้องแสดงใน 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 ดักว่าถ้าหาไม่เจอ ให้แสดงคำว่า “ไม่เจอข้อมูล”
Drop Down List แบบ Search ได้ใน Excel (ไม่ต้องใช้ VBA)

สรุปสูตร

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 ได้ครับ

แหล่งอ้างอิง

แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 1.2K
  •  
  •  
  •  
  •  
  • 1.2K
  •  
  •  
  •  
  •  
Sira Ekabut
the authorSira Ekabut
• Microsoft Office Specialist : Excel ระดับ Expert • ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 8 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views + • หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel , Excel Power Up!วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ

4 Comments

  • ทำได้ทุกขั้นตอน แต่ พอถึงขั้นตอนสุดท้าย จบคะ ไม่ขึ้นเหมือนของ อ.คะ ทำไงดี คะ

    • ถ้าไม่ขึ้นตอนจบสูตรน่าจะผิดนะครับ ลองดูดีๆ

  • เปลี่ยน 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 ตัวครับ

    รบกวนด้วยครับ ขอบคุณครับ

Leave a Reply