ชุดข้อมูลแสดงข้อมูลช้อปปิ้งออนไลน์: 'Order ID', 'Customer Name', 'Device', 'Order Date', 'Delivery date', และ 'Payment Method'
Drop-down List ใน Excel คืออะไร?
Drop-down list (รายการแบบเลื่อนลง) เป็นคุณสมบัติที่อนุญาตให้ผู้ใช้เลือกองค์ประกอบหนึ่งจากรายการตัวเลือก
วิธีสร้าง Drop-down List ใน Excel
1. สร้าง Drop-down List จากช่วงเซลล์ (Cell Range)
เพื่อสร้าง Drop-down list จากค่าในคอลัมน์ B:
- เลือกเซลล์ (C18)
- ไปที่แท็บ Data และเลือก Data Validation
- ใน Data Validation ไปที่ Setting
- ใน Allow เลือก List
- กำหนดช่วง ($B$6:$B$15) ใน Source
- คลิก OK
นี่คือผลลัพธ์
- ใส่สูตรต่อไปนี้ใน C19 เพื่อดึงค่าจากคอลัมน์ C ตามการเลือก Order ID
=VLOOKUP($C$18,$B$6:$G$15,ROW(B19)-ROW($B$18)+1,FALSE)
- ใช้ Fill Handle เพื่อดึงค่าอื่นๆ
2. สร้าง Drop-down List จากตาราง (Table)
- เลือกช่วงเซลล์และไปที่แท็บ Insert เพื่อสร้างตาราง
- คลิก Table
- ติ๊ก My table has headers
- คลิก OK.
- เพื่อสร้าง Drop-down list ใน C18 ด้วยข้อมูลใน Customer Name ให้เลือก List ใน Allow
- ใส่สูตรต่อไปนี้พร้อมฟังก์ชัน INDIRECT ใน Source และคลิก OK
=INDIRECT("Table1[Customer Name]")
- ใส่สูตรต่อไปนี้ใน C19 เพื่อดึงชื่ออุปกรณ์ตามการเลือกชื่อลูกค้า
=INDEX(Table1,MATCH(C18,Table1[Customer Name],0),3)
นี่คือผลลัพธ์
3. สร้าง Drop-down List ด้วย Named Range
- เลือกช่วงเซลล์และไปที่แท็บ Formulas
- คลิก Define Name
- ตั้งชื่อ (ID) ใน Name และกำหนดช่วงใน Refers to
- สร้าง Drop-down list ของ Order IDs ใน C19 โดยใส่สูตรต่อไปนี้ใน Source:
- ใช้สูตรต่อไปนี้ใน C19 เพื่อได้ชื่อลูกค้าตามการเลือก Order ID
=INDEX(B6:G15,MATCH($C$18,ID,0),2)
- ใช้สูตรต่อไปนี้เพื่อได้ชื่ออุปกรณ์ใน C20
=INDEX(B6:G15,MATCH($C$18,ID,0),3)
นี่คือผลลัพธ์
4. สร้าง Drop-down List พร้อมสี (Color)
กำหนดสีแบบกำหนดเองด้วย Conditional Formatting สำหรับแต่ละค่าของ Drop-down list
- เลือก List ใน Allow.
- ใส่ชื่อสีคั่นด้วยเครื่องหมายจุลภาค (,): (Black,White,Silver,Gold) ใน Source.
- เพื่อกำหนดสีสำหรับแต่ละองค์ประกอบของ Drop-down ให้เลือก Drop-down แล้วไปที่ Conditional Formatting ในแท็บ Home.
- ไปที่ Highlight Cell Rules และคลิก Equal To…
- ใน Equal To ใส่ชื่อสี (Black)
- เลือก Custom Format เพื่อตั้งค่าสีสำหรับชื่อสีนั้น
- เลือกสีใน Fill.
- คุณสามารถเปลี่ยนสีฟอนต์ใน Font ได้ ที่นี่ใช้สีขาว
- คลิก OK.
- ตั้งค่าสีสำหรับตัวเลือกอื่นๆ
Drop-down list จะแสดงสีตามค่าเซลล์
สีจะเปลี่ยนอัตโนมัติตามการเลือก
- ใช้ Fill Handle เพื่อ AutoFill Drop-down
5. สร้าง Drop-down List ด้วยค่าที่ไม่ซ้ำกัน (Unique Values)
สร้าง Drop-down lists ด้วยค่าที่ไม่ซ้ำในคอลัมน์ D และ E
- ใช้สูตรต่อไปนี้ใน B22 เพื่อดูชื่อแบรนด์ที่ไม่ซ้ำ
- ใส่สูตรต่อไปนี้เพื่อสร้าง Drop-down list ใน C5 ด้วยค่าที่ไม่ซ้ำ
- ใส่สูตรต่อไปนี้ใน C22 เพื่อดูค่าที่ไม่ซ้ำในคอลัมน์ E ที่ตรงกับแบรนด์ใน C5
=FILTER(E9:E18,C5=D9:D18)
- ไปที่ Data Validation.
- ใช้สูตรต่อไปนี้ใน Source.
นี่คือผลลัพธ์
6. สร้าง Drop-down List จากข้อมูลในชีทอื่น (Another Sheet)
แบรนด์มือถือและรุ่นโทรศัพท์อยู่ในชีท PhoneList
ใช้ชื่อแบรนด์และรุ่นโทรศัพท์สร้าง Drop-down list 2 ตัวในชีทอื่น (AnotherSheet)
- เพื่อสร้าง Drop-down list ชื่อแบรนด์ใน D6 ด้วยข้อมูลในชีท PhoneList ใส่สูตรต่อไปนี้ใน Source.
- ใช้ Fill Handle สร้าง Drop-down list เดียวกันในคอลัมน์ D
- ใช้สูตรต่อไปนี้พร้อมฟังก์ชัน INDIRECT ใน Source เพื่อให้รุ่นโทรศัพท์จากชีท PhoneList อยู่ใน Drop-down ตามชื่อแบรนด์ในคอลัมน์ D
คลิกเพื่อขยายภาพ
นี่คือผลลัพธ์
7. สร้าง Drop-down List จากข้อมูลในสมุดงานอื่น (Another Workbook)
ในสมุดงาน AvailablePaymentMethods มีรายการวิธีชำระเงินสำหรับช้อปปิ้งออนไลน์
- ไปที่ Formulas และเลือก Define Name
- ใน New Name ตั้งชื่อ (PayType) ใน Name.
- กำหนดช่วง (Sheet1!$B$3:$B$7) ใน Refers to.
- คลิก OK.
- ไปที่ สมุดงาน Drop-down in Excel และตั้ง Named Range (PayType)
- ใส่ [AvailablePaymentMethods.xlsx]Sheet1!PayType เพื่อกำหนด Named Range ในสมุดงาน AvailablePaymentMethods
- ใส่สูตรต่อไปนี้ใน Source เพื่อสร้าง Drop-down list ด้วย Named Range ใน G6
- คุณสามารถเลือกวิธีชำระเงินจาก Drop-down list ได้
- ใช้ Fill Handle เพื่อ AutoFill Drop-down list
หมายเหตุ
i. ทั้งสองสมุดงานต้องเปิดอยู่ขณะสร้าง Drop-down
ii. อย่าสร้าง Drop-down ที่ขึ้นอยู่กับกัน (Dependent) หลายตัวจากสมุดงานอื่น
8. สร้าง Dependent Drop-down List (Drop-down ที่ขึ้นอยู่กับกัน)
- เพื่อสร้าง Drop-down list อิสระของแบรนด์ใน D6 ใส่สูตรต่อไปนี้ใน Source (Data Validation).
- เพื่อสร้าง Drop-down list ที่ขึ้นอยู่ (Dependent) ของรุ่นโทรศัพท์ใน E6 ใส่สูตรต่อไปนี้พร้อมฟังก์ชัน INDIRECT ใน Source.
คลิกเพื่อขยายภาพ
Drop-down list ที่ขึ้นอยู่กับการเลือกแบรนด์จาก Drop-down อิสระ ถูกสร้างขึ้นแล้ว
- ใช้ Fill Handle เพื่อ AutoFill Drop-down lists
9. สร้าง Searchable Drop-down List (Drop-down ค้นหาได้)
- แทรกคอลัมน์ใหม่ (G และ H) เพื่อได้ชื่อแบรนด์และรุ่นที่ไม่ซ้ำ ใช้สูตรต่อไปนี้ใน G6
=UNIQUE(OFFSET(D6,0,0,COUNTA(D:D)-1,1))
- ใส่สูตรต่อไปนี้ใน Source เพื่อสร้าง Searchable Drop-down list ใน C19 ด้วยชื่อแบรนด์
คุณจะสามารถค้นหาชื่อแบรนด์ใน C19 ได้
- ใส่สูตรต่อไปนี้ใน H6 เพื่อดูรุ่นโทรศัพท์ตามการเลือกแบรนด์จาก Searchable Drop-down
=UNIQUE(FILTER(E6:E17,C19=D6:D17))
- ใส่สูตรต่อไปนี้ใน Source เพื่อสร้าง Searchable Drop-down ใน C20 ด้วยชื่อรุ่นโทรศัพท์
คุณได้ Drop-down list อิสระและขึ้นอยู่กันที่ค้นหาได้ทั้งคู่
10. สร้าง Dynamic Drop-down List เพิ่มและอัปเดตรายการใหม่อัตโนมัติ
เพื่อดึงค่าตาม ID
คลิกเพื่อขยายภาพ
- แทรกคอลัมน์แยกและใส่สูตรต่อไปนี้ใน L6 เพื่อดู ID ที่ไม่ซ้ำจากค่าคอลัมน์ B
=UNIQUE(OFFSET(B6,0,0,COUNTA(B:B)-3,1))
คลิกเพื่อขยายภาพ
- สร้าง Drop-down list ด้วย Unique IDs ใน J5 โดยใส่สูตรต่อไปนี้ใน Source
คลิกเพื่อขยายภาพ
- เลือก ID จาก Drop-down list และใช้สูตรต่อไปนี้ใน J6 เพื่อดูชื่อลูกค้าสำหรับ ID ที่เลือก
=VLOOKUP($J$5,B:G,ROW(I6)-ROW($I$5)+1,FALSE)
- ใช้ Fill Handle เพื่อ AutoFill ค่าที่เหลือ
คลิกเพื่อขยายภาพ
- วันที่ที่ดึงมาจะแสดงเป็นตัวเลข เปลี่ยนรูปแบบตัวเลขเป็น Short Date หรือ Long Date ใน Number Format
- เพิ่ม ID ใหม่และค่าที่เกี่ยวข้อง พวกเขาจะอัปเดตอัตโนมัติใน Drop-down list และคอลัมน์ L
คลิกเพื่อขยายภาพ
นี่คือผลลัพธ์
11. สร้าง Drop-down List ที่ Searchable, Dynamic และ Multi-dependent
- ใส่สูตรต่อไปนี้ใน L6.
=UNIQUE(OFFSET(D6,0,0,COUNTA(D:D)-1,1))
- สร้าง Drop-down list อิสระที่ค้นหาได้ของชื่อแบรนด์ใน J5 โดยใส่สูตรต่อไปนี้ใน Source.
- กรองรุ่นโทรศัพท์ตามแบรนด์ที่เลือกจาก Drop-down list ใน J5 ใช้สูตรต่อไปนี้
=UNIQUE(FILTER(E:E,J5=D:D))
- สร้าง Drop-down list ที่ขึ้นอยู่กับชื่อแบรนด์และค้นหาได้ของชื่อรุ่นโทรศัพท์ใน J6 ใส่สูตรต่อไปนี้ใน Source.
- กรองชิปเซต (Chipsets) ตามรุ่นที่เลือกจาก Drop-down list ใน J6 ใช้สูตรต่อไปนี้
=UNIQUE(FILTER(F:F,J6=E:E))
- ใส่สูตรต่อไปนี้ใน Source เพื่อสร้าง Drop-down list ที่ขึ้นอยู่กับชื่อรุ่นและค้นหาได้ของชิปเซตใน J7
- ใช้สูตรต่อไปนี้ใน J8 เพื่อหาราคาสินค้าที่ตรงกับค่าที่เลือก
=INDEX(G:G,MATCH(1,(J5=D:D)*(J6=E:E)*(J7=F:F),0))
เนื่องจาก Drop-down lists ทั้งหมดเป็น Dynamic คุณสามารถเพิ่มรายการใหม่และ Drop-down lists จะอัปเดต
12. สร้าง Drop-down List พร้อมข้อความกำหนดเอง (Custom Message)
- สร้าง Drop-down list ที่ขึ้นอยู่กันของรุ่นโทรศัพท์ ตามที่อธิบายใน วิธี 7
- ใน Data Validation ไปที่ Input Message.
- ติ๊ก Show input message when cell is selected
- ตั้งชื่อใน Title และข้อความกำหนดเองใน Input message
- คลิก OK
ข้อความกำหนดเองจะแสดงเมื่อเลือกเซลล์
13. สร้าง Drop-down List แก้ไขได้พร้อมข้อความแจ้งเตือนผิดพลาด (Error Alert)
- ใส่สูตรต่อไปนี้ใน G6 เพื่อสร้างคอลัมน์ไดนามิกด้วยชื่อแบรนด์ที่ไม่ซ้ำ
=UNIQUE(OFFSET(D6,0,0,COUNTA(D:D)-1,1))
- สร้าง Drop-down list ไดนามิกที่ค้นหาได้ของชื่อแบรนด์ใน C19 โดยใส่สูตรต่อไปนี้ใน Source
- เพื่อสร้าง Drop-down list ที่แก้ไขได้ (Editable) ไปที่แท็บ Error Alert
- ติ๊กกล่อง Show error alert after invalid data is entered
- ตั้งค่า Style, Title และ Error message สำหรับค่าค้นหาผิด และคลิก OK
- สร้าง Drop-down list ด้วยค่าใน Model
หากคุณใส่ค่าผิดใน Drop-down lists ที่แก้ไขได้เหล่านี้ ข้อความแจ้งเตือนข้อผิดพลาดจะแสดงพร้อมตัวเลือก Retry หรือ Cancel
วิธีเพิ่มหรือลบรายการจาก Drop-down List ใน Excel
คุณสามารถเพิ่มหรือลบรายการได้โดยการเพิ่มหรือลบเซลล์ขณะกำหนดช่วงใน Source
วิธีปกป้อง Drop-down List ใน Excel
มี Drop-down list ชื่อลูกค้าสร้างไว้ใน C18
- เลือกตารางทั้งหมดและไปที่แท็บ Home
- คลิก Alignment เพื่อขยาย
- ใน Format Cells ติ๊ก Locked ในแท็บ Protection
- ตารางถูกปกป้องแล้ว ไปที่แท็บ Review และคลิก Protect Workbook
- ใส่รหัสผ่านและติ๊ก Select locked cells
- คลิก OK
- ใส่รหัสผ่านอีกครั้ง
ทั้งตารางและ Drop-down list ถูกปกป้องแล้ว
วิธีลบ Drop-down List ใน Excel
- เลือก Drop-down list และไปที่แท็บ Data
- คลิก Data Validation
- ใน Data Validation คลิก Clear All.
- คลิก OK
Drop-down list จะถูกลบ
วิธีกรอง Drop-down List และดึงข้อมูลตามการเลือกใน Excel
- สร้างตาราง
- สร้าง Drop-down list ในคอลัมน์ D
- ใส่สูตรต่อไปนี้เพื่อดึงข้อมูลทั้งหมดตามการเลือก
=FILTER(Table3,Table3[Device]=$C$18,"No Information Available")
- เปลี่ยนค่า Drop-down list และการดึงข้อมูลจะอัปเดตอัตโนมัติ
วิธีแก้ไขปัญหา Drop-down List ใน Excel
1. Drop-down List ไม่อยู่ในมุมมอง (Not Visible)
วิธีแก้:
- ติ๊ก In-cell dropdown เพื่อให้ Drop-down list มองเห็นได้
2. แสดงค่าว่าง (Blank) ใน Drop-down List
วิธีแก้:
- กำหนดช่วง Drop-down list ให้ข้ามค่าว่าง
3. ค่าที่ถูกต้องไม่ได้รับอนุญาตใน Drop-down List
วิธีแก้:
- ใช้ตัวพิมพ์เล็ก/ใหญ่ (Case) ให้ถูกต้องขณะใส่ค่าที่ถูกต้อง
4. ค่าที่ไม่ถูกต้องได้รับอนุญาตใน Drop-down List
เนื่องจากช่วง Source ใหญ่กว่ารายการค่าจริง ค่าที่ใส่ในเซลล์เหล่านั้นอาจถูกลิสต์ แม้จะไม่ถูกต้อง
วิธีแก้:
- กำหนดช่วง Drop-down list ให้หยุดการป้อนค่าที่ไม่ถูกต้อง
Download Practice Workbook
ดาวน์โหลดสมุดงานฝึกหัด
Excel Drop-down List: Knowledge Hub
- Create drop-down List in Excel
- Edit drop-down List in Excel
- drop-down List with Filter in Excel
- Excel Dependent drop-down List
- How to Remove drop-down List in Excel
- How to Link a Cell Value with a drop-down List in Excel
- Excel drop-down List Not Working
- IF Statement to Create Drop-Down List in Excel
- Extract Data Based on a drop-down List Selection in Excel
- Populate List Based on Cell Value in Excel
- drop-down List in Multiple Columns in Excel
- Blank Option to drop-down List in Excel
- Select from drop-down and Pull Data from Different Sheet
- [Fixed!] drop-down List Ignore Blank Not Working in Excel
- Autocomplete Data Validation drop-down List in Excel
<< Go Back to Data Validation in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
