สร้างตัวกรองแบบเลื่อนลง (Drop-Down Filter) ใน Excel เพื่อดึงข้อมูลแบบไดนามิก

ฟีเจอร์ ตัวกรอง (Filter) ใน Excel ช่วยให้เราสามารถดึงข้อมูลตามที่เราเลือกได้ แต่ปัญหาคือ หมายเลขแถวและการอ้างอิงเซลล์ยังคงคงที่ การสร้างตัวกรองแบบเลื่อนลงอย่างง่ายจะแก้ไขปัญหานี้ได้ ในบทความนี้ เราจะแสดงวิธีที่มีประสิทธิภาพสำหรับ การสร้างตัวกรองแบบเลื่อนลงเพื่อดึงข้อมูลตามการเลือกใน Excel

เพื่อการสาธิต เราจะใช้ชุดข้อมูลตัวอย่าง ตัวอย่างเช่น ชุดข้อมูลต่อไปนี้แสดง พนักงานขาย, สินค้า และ ยอดขายสุทธิ ของบริษัท

ดาวน์โหลดสมุดงานเพื่อฝึกปฏิบัติเอง

4 วิธีสร้างตัวกรองแบบเลื่อนลงเพื่อดึงข้อมูลตามการเลือกใน Excel

1. สร้างตัวกรองแบบเลื่อนลงด้วยคอลัมน์ช่วยเหลือ (Helper Columns)

ในวิธีแรก เราจะใช้ 3 คอลัมน์ช่วยเหลือ เพื่อสร้าง ตัวกรองแบบเลื่อนลง โปรดทำตามขั้นตอนด้านล่าง

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ D5 และพิมพ์สูตร:

=ROWS($B$5:B5)

• กด Enter และใช้ AutoFill เพื่อเติมลำดับ

• หลังจากนั้น เลือกเซลล์ G5 หรือตำแหน่งที่ต้องการสร้าง ตัวกรองแบบเลื่อนลง
• ไปที่ ข้อมูล (Data) ➤ เครื่องมือข้อมูล (Data Tools) ➤ ตรวจสอบความถูกต้องของข้อมูล (Data Validation)

• กล่องโต้ตอบ ตรวจสอบความถูกต้องของข้อมูล จะปรากฏขึ้น
• ในแท็บ การตั้งค่า (Settings) เลือก รายการ (List) ใน อนุญาต (Allow) และพิมพ์ TV, AC ในช่อง แหล่งที่มา (Source)

• กด ตกลง (OK)
• ตอนนี้ตัวกรองที่ต้องการได้ถูกสร้างขึ้นแล้ว

• ตอนนี้ เลือกเซลล์ E5 และพิมพ์สูตร:

=IF(B5=$G$5,D5,"")

• กด Enter

• ใช้ AutoFill เติมส่วนที่เหลือ
• จากนั้น เลือก F5 พิมพ์สูตร:

=IFERROR(SMALL($E$5:$E$10,D5),"")

• กด Enter

• ใช้ AutoFill อีกครั้งเพื่อเติมส่วนที่เหลือ
• ต่อไป เลือกเซลล์ I5 พิมพ์สูตร:

=IFERROR(INDEX($A$5:$C$10,$F5,COLUMNS($I$5:I5)),"")

• กด Enter

ที่นี่ ฟังก์ชัน COLUMNS จะคืนจำนวนคอลัมน์ในช่วง $I$5:I5 ฟังก์ชัน INDEX จะคืนการอ้างอิงเซลล์หรือค่าเซลล์ที่จุดตัดของหมายเลขแถวใน F5 และหมายเลขคอลัมน์ใน I5 ฟังก์ชัน IFERROR จะคืนเซลล์ว่างหากพบข้อผิดพลาดในนิพจน์

• สุดท้าย ใช้ AutoFill เพื่อเติมลำดับ ดังนั้นคุณจะได้ข้อมูลที่ต้องการตามการเลือกของคุณ

• เช่นเดียวกัน เลือก AC จาก ตัวกรองแบบเลื่อนลง ชุดข้อมูลจะอัปเดตโดยอัตโนมัติ

อ่านเพิ่มเติม: วิธีสร้างรายการแบบเลื่อนลงพร้อมตัวกรองใน Excel (7 วิธี)

2. ฟังก์ชัน FILTER ใน Excel สำหรับสร้างตัวกรองแบบเลื่อนลงดึงข้อมูลตามการเลือก

เราจะใช้ฟังก์ชัน FILTER ในวิธีนี้เพื่อ สร้างตัวกรองแบบเลื่อนลงเพื่อดึงข้อมูลตามการเลือกใน Excel เรียนรู้ขั้นตอนด้านล่าง

ขั้นตอน:

• ขั้นแรก เลือกช่วง A4:C10
• จากนั้นในแท็บ แทรก (Insert) เลือก ตาราง (Table)

• กล่องโต้ตอบจะปรากฏ คลิก ตกลง (OK)
• จะสร้างตารางชื่อ Table1 โดยอัตโนมัติ
• เปิดแผ่นงานใหม่ เลือกเซลล์ B2 พิมพ์สูตร:

=UNIQUE(Table1[Product])

• กด Enter จะแสดงชื่อสินค้าที่ไม่ซ้ำกัน

• เลือกเซลล์ E5 หรือเซลล์อื่นในแผ่นหลัก
• ไปที่ ข้อมูล (Data) ➤ เครื่องมือข้อมูล (Data Tools) ➤ ตรวจสอบความถูกต้องของข้อมูล (Data Validation)

• กล่องโต้ตอบจะปรากฏ
• ในแท็บ การตั้งค่า (Settings) เลือก รายการ (List) ใน อนุญาต (Allow) และในช่อง แหล่งที่มา (Source) พิมพ์สูตร:

=list!$B$2#

ที่นี่ list คือชื่อแผ่นงานที่สร้างใหม่ จะค้นหาค่าเซลล์ B2 ในแผ่น list

• หลังจากนั้น เลือกเซลล์ G5 พิมพ์สูตร:

=FILTER(Table1,Table1[Product]=E5)

• กด Enter ข้อมูลจะกระจาย (spill) ออกมา

ที่นี่ ฟังก์ชัน FILTER จะกรอง Table1 และคืนชุดข้อมูลที่ตรงกับเซลล์ E5

• คุณสามารถเปลี่ยน ตัวกรองแบบเลื่อนลง เป็น AC และได้ข้อมูลตามการเลือก

อ่านเพิ่มเติม: สร้างตัวกรอง Excel ด้วยรายการแบบเลื่อนลงตามค่าเซลล์

บทความคล้ายกัน:

• วิธิกัดลอกตัวกรองรายการแบบเลื่อนลงใน Excel (5 วิธี)
• วิธีสร้างรายการแบบเลื่อนลงที่ขึ้นกันได้พร้อมช่องว่างใน Excel
• สร้างรายการแบบเลื่อนลงที่ค้นหาได้ใน Excel (2 วิธี)
• วิธีสร้างรายการแบบเลื่อนลง Excel พร้อมสี (2 วิธี)
• รายการแบบเลื่อนลง Excel ไม่ทำงาน (8 ปัญหาและวิธีแก้)

3. ดึงข้อมูลตามการเลือกด้วยตัวกรองแบบเลื่อนลงและฟังก์ชัน INDIRECT

เราสามารถ ดึงข้อมูลจากหลายแผ่นงาน โดยใช้ฟังก์ชัน INDIRECT ของ Excel ตัวอย่างเช่น ในชุดข้อมูลต่อไปนี้ มี 2 แผ่นงาน: Sheet1 และ Sheet2 ซึ่งมีข้อมูล เราจะดึง ยอดขายรวม ตามการเลือกแผ่นงานในวิธีนี้ ดังนั้นโปรดทำตามขั้นตอนสำหรับการดึงค่า ยอดขายรวม เท่านั้นตามการเลือกของเรา

ขั้นตอน:

• ในตอนแรก เลือกเซลล์ C4 ในแผ่นที่ต้องการวางข้อมูลที่ดึงมา

• ไปที่ ข้อมูล (Data) ➤ เครื่องมือข้อมูล (Data Tools) ➤ ตรวจสอบความถูกต้องของข้อมูล (Data Validation)
• ในกล่องโต้ตอบ เลือก รายการ (List) ใน อนุญาต (Allow) ในช่อง แหล่งที่มา (Source) พิมพ์สูตร:

=$C$8:$C$9

• กด ตกลง (OK)
• ตอนนี้ เลือกเซลล์ C6 พิมพ์สูตร:

=INDIRECT("'"&C4&"'!C11")

• กด Enter จะดึง ยอดขายรวม จากแผ่นงานที่ระบุในเซลล์ C4

• สุดท้าย เปลี่ยนแผ่นงานโดยใช้ ตัวกรองแบบเลื่อนลง คุณจะเห็นการเปลี่ยนแปลงที่ต้องการในเซลล์ C6

อ่านเพิ่มเติม: วิธีดึงข้อมูลตามการเลือกจากรายการแบบเลื่อนลงใน Excel

4. VBA สำหรับสร้างตัวกรองแบบเลื่อนลงดึงข้อมูลตามการเลือกใน Excel

เราจะใช้รหัส VBA ในวิธีสุดท้ายนี้สำหรับ การสร้างตัวกรองแบบเลื่อนลงเพื่อดึงข้อมูลตามการเลือกใน Excel เรียนรู้กระบวนการเพื่อทราบวิธีปฏิบัติ

ขั้นตอน:

• ขั้นแรก เรามีข้อมูลในแผ่น vba1

• และเรามี ตัวกรองแบบเลื่อนลง ในแผ่น vba2 ตอนนี้เราต้องการกรองข้อมูลในแผ่น vba1 ตามการเลือก ตัวกรองแบบเลื่อนลง ในแผ่น vba2

• คลิกขวาที่แผ่น vba2 เลือก View Code

• หน้าต่าง Module จะปรากฏ
• คัดลอกรหัสด้านล่างและวางไว้ที่นั่น

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Range("A2"), Target) Is Nothing Then
    Application.EnableEvents = False
    If Range("A2").Value = "" Then
        Worksheets("vba1").ShowAllData
    Else
        Worksheets("vba1").Range("A2").AutoFilter 1, Range("A2").Value
    End If
    Application.EnableEvents = True
End If
End Sub

• กด F5 กล่องโต้ตอบ Macros จะปรากฏ พิมพ์ VBA ใน Macro Name
• คลิก Create

• กด F5 อีกครั้ง และเลือก Run

• ปิดหน้าต่าง จาก ตัวกรองแบบเลื่อนลง เลือก TV

• ข้อมูลที่กรองแล้วจะแสดงในแผ่น vba1

• เช่นกัน เลือก AC จาก ตัวกรองแบบเลื่อนลง ใน vba2 จะคืนข้อมูลที่ดึงมา

อ่านเพิ่มเติม: VBA เลือกค่าจากรายการแบบเลื่อนลงใน Excel (2 วิธี)

สรุป

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

บทความที่เกี่ยวข้อง

• วิธีสร้างฟอร์มพร้อมรายการแบบเลื่อนลงใน Excel
• ซ่อน/แสดงคอลัมน์ตามการเลือกจากรายการแบบเลื่อนลงใน Excel
• วิธีสร้างรายการแบบเลื่อนลงที่ขึ้นกันได้พร้อมหลายคำใน Excel
• ลบรายการที่ใช้แล้วออกจากรายการแบบเลื่อนลงใน Excel (2 วิธี)
• วิธيلบค่าซ้ำจากรายการแบบเลื่อนลงใน Excel (4 วิธี)