เทพเอ็กเซล ช่วย "ปลุกเทพ 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 ให้บริษัทชั้นนำ

2 Comments

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

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

Leave a Reply