วิธีสร้างดรอปดาวน์ลิสต์ใน Excel (และทำให้อัปเดตอัตโนมัติ)

วิธีสร้างดรอปดาวน์ลิสต์ (drop-down list) ใน Excel คือเลือกเซลล์ ไปที่ ข้อมูล (Data) → การตรวจสอบข้อมูล (Data Validation) ตั้ง อนุญาต (Allow) เป็น รายการ (List) แล้วในช่อง แหล่งที่มา (Source) ให้พิมพ์รายการโดยคั่นด้วยเครื่องหมายจุลภาค หรือเลือกเซลล์ที่มีรายการนั้น คลิก ตกลง (OK) แล้วแต่ละเซลล์จะมีลูกศรสำหรับเปิดรายการของคุณ

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

สูตรในบทความนี้แสดงตามแบบ Excel ภาษาอังกฤษ (Excel ภาษาไทยใช้ชื่อฟังก์ชันภาษาอังกฤษเหมือนกัน)

สร้างดรอปดาวน์ลิสต์ทีละขั้นตอน

ขั้นตอนเหล่านี้ใช้ได้ใน Excel for Microsoft 365, Excel 2021 และ 2019 บน Windows และเกือบเหมือนกันบน Mac และใน Excel บนเว็บ (ความแตกต่างอยู่ด้านล่าง)

1. พิมพ์รายการในคอลัมน์เดียวโดยไม่เว้นเซลล์ว่าง เช่น Not started, In progress, Done และ Blocked ใน F2:F5 การสร้างชีตแยกชื่อ "Lists" ไว้เก็บรายการจะสะดวก
2. เลือกเซลล์ที่ต้องการให้มีดรอปดาวน์ เช่น C2:C50
3. ไปที่ ข้อมูล (Data) → การตรวจสอบข้อมูล (Data Validation) (ในกลุ่ม เครื่องมือข้อมูล (Data Tools)) บน Windows แป้นพิมพ์ลัดคือ Alt, A, V, V (ใช้กับ Excel เวอร์ชันภาษาอังกฤษ)
4. บนแท็บ การตั้งค่า (Settings) ตั้ง อนุญาต (Allow) เป็น รายการ (List)
5. คลิกในช่อง แหล่งที่มา (Source) แล้วเลือกรายการบนชีต (F2:F5 โดยไม่รวมหัวคอลัมน์) Excel จะใส่ =$F$2:$F$5 ให้
6. ตรวจดูว่าทำเครื่องหมายที่ รายการแบบดรอปดาวน์ในเซลล์ (In-cell dropdown) แล้ว และเว้น ละเว้นช่องว่าง (Ignore blank) ไว้หากไม่เป็นไรที่จะมีเซลล์ว่าง
7. คลิก ตกลง (OK)

เลือกเซลล์ใดเซลล์หนึ่งเหล่านั้น แล้วจะมีลูกศรปรากฏข้างเซลล์ คลิกลูกศร หรือกด Alt+Down Arrow (Option+Down Arrow บน Mac) เพื่อเปิดรายการ

พิมพ์รายการเองแทนการเลือกเซลล์

สำหรับรายการสั้นๆ ที่ไม่เปลี่ยนแปลง ให้พิมพ์ลงในช่อง แหล่งที่มา (Source) โดยตรง เช่น Yes,No,Maybe อย่าใส่ช่องว่างหลังเครื่องหมายจุลภาค ไม่เช่นนั้นช่องว่างจะกลายเป็นส่วนหนึ่งของแต่ละรายการ หากการตั้งค่าภูมิภาคของคุณใช้เครื่องหมายจุลภาคเป็นตัวคั่นทศนิยม Excel อาจต้องการเครื่องหมายอัฒภาคแทน (Yes;No;Maybe) ช่องแหล่งที่มารองรับได้สูงสุด 255 อักขระ ดังนั้นรายการที่ยาวกว่านั้นควรเก็บไว้บนชีต

Excel สำหรับ Mac และ Excel บนเว็บ

• Mac: ข้อมูล (Data) → การตรวจสอบข้อมูล (Data Validation) (ในบางเวอร์ชันจะมีป้ายว่า Validate ในกลุ่มเครื่องมือ) กล่องโต้ตอบมีสามแท็บเหมือนกัน
• เว็บ: ข้อมูล (Data) → Data Validation (อยู่ใต้ เครื่องมือข้อมูล (Data Tools) เมื่อหน้าต่างแคบ) จากนั้นทำตามขั้นตอน Allow → List เหมือนกัน
• มือถือ: แอปบน iPhone, iPad และ Android ให้คุณเลือกจากดรอปดาวน์ที่สร้างไว้บนคอมพิวเตอร์ได้

ทำให้รายการเพิ่มขึ้นเองด้วยตาราง

ช่วงธรรมดาอย่าง $F$2:$F$5 จะไม่รวมรายการที่คุณพิมพ์เพิ่มใน F6 แต่ถ้าแปลงรายการเป็นตารางก็จะรวมให้

1. คลิกเซลล์ในรายการของคุณแล้วกด Ctrl+T (Cmd+T บน Mac) ทำเครื่องหมายที่ ตารางของฉันมีส่วนหัว (My table has headers) แล้วคลิก ตกลง (OK)
2. สร้างดรอปดาวน์ตามขั้นตอนข้างต้น โดยเลือกเซลล์ข้อมูลของตาราง (ไม่รวมส่วนหัว) ในช่อง Source
3. พิมพ์รายการใหม่ในแถวที่อยู่ใต้ตารางทันที ตารางจะขยายออก และทุกดรอปดาวน์ที่อ้างอิงตารางนั้นจะแสดงรายการใหม่

คุณพิมพ์การอ้างอิงตาราง เช่น =Table1[Status] ลงในช่อง Source โดยตรงไม่ได้ Excel จะปฏิเสธ ให้เลือกเซลล์แทน หรือใช้ =INDIRECT("Table1[Status]")

ช่วงที่ตั้งชื่อ (named range) ก็ใช้ได้เช่นกัน เลือกรายการ พิมพ์ชื่อ เช่น Statuses ใน กล่องชื่อ (Name Box) (ทางซ้ายของแถบสูตร) กด Enter แล้วใช้ =Statuses เป็นแหล่งที่มา

สร้างรายการไดนามิกจากข้อมูลที่มีอยู่ด้วย UNIQUE

หากตัวเลือกควรมาจากข้อมูลที่คุณมีอยู่แล้ว เช่น ชื่อลูกค้าทั้งหมดในคอลัมน์ A ให้ใช้สูตรสร้างรายการ ใน Excel for Microsoft 365, Excel 2021 ขึ้นไป หรือ Excel บนเว็บ

1. ในเซลล์ว่างเซลล์หนึ่ง เช่น H2 ใส่ =SORT(UNIQUE(FILTER(A2:A500,A2:A500<>""))) รายการที่เรียงลำดับแล้วและไม่ซ้ำกันจะไหลลงมาจาก H2 (ส่วน FILTER ช่วยตัดเซลล์ว่างออก ซึ่งไม่เช่นนั้นจะปรากฏเป็น 0)
2. สร้างดรอปดาวน์โดยตั้ง Source เป็น =$H$2#

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

ใช้รายการจากอีกชีตหนึ่ง

ขณะที่ช่อง Source ใช้งานอยู่ ให้คลิกแท็บของอีกชีตแล้วเลือกเซลล์ Excel จะเขียนสิ่งที่คล้ายกับ =Lists!$A$2:$A$10 จากนั้นคุณซ่อนชีต Lists ได้ (คลิกขวาที่แท็บ → ซ่อน (Hide)) เพื่อไม่ให้ใครแก้ไขโดยไม่ตั้งใจ แหล่งที่มาในเวิร์กบุ๊กอื่นจะใช้ได้เฉพาะเมื่อเวิร์กบุ๊กนั้นเปิดอยู่ ดังนั้นควรเก็บรายการไว้ในไฟล์เดียวกัน

สร้างดรอปดาวน์แบบเชื่อมโยง (เรียงต่อกัน) ด้วย INDIRECT

ดรอปดาวน์แบบเชื่อมโยงจะเปลี่ยนตัวเลือกตามเซลล์อื่น เช่น เลือก Fruit ในคอลัมน์ A แล้วคอลัมน์ B จะแสดงเฉพาะผลไม้

1. บนชีต Lists ของคุณ ใส่แต่ละหมวดหมู่เป็นหัวคอลัมน์และรายการอยู่ด้านล่าง เช่น Fruit ใน D1 โดยมี Apple, Banana, Cherry อยู่ด้านล่าง และ Vegetables ใน E1 โดยมี Carrot, Leek, Pea
2. เลือก D1:E4 แล้วไปที่ สูตร (Formulas) → สร้างจากส่วนที่เลือก (Create from Selection) ทำเครื่องหมายเฉพาะ แถวบนสุด (Top row) แล้วคลิก ตกลง (OK) Excel จะสร้างช่วงที่ตั้งชื่อสำหรับแต่ละคอลัมน์ โดยใช้ชื่อตามหัวคอลัมน์
3. ใน A2 บนชีตหลัก สร้างดรอปดาวน์ปกติที่มีแหล่งที่มาเป็นเซลล์หัวคอลัมน์สองเซลล์ =Lists!$D$1:$E$1 (ชื่อหมวดหมู่)
4. เลือก B2 เปิด ข้อมูล (Data) → Data Validation เลือก List แล้วใส่ =INDIRECT($A2) เป็นแหล่งที่มา
5. หาก Excel เตือนว่าแหล่งที่มาให้ผลเป็นข้อผิดพลาดในขณะนี้ ให้คลิก ใช่ (Yes) เพราะ A2 ยังว่างอยู่เท่านั้น

ชื่อไม่สามารถมีช่องว่างได้ ดังนั้น Create from Selection จะเปลี่ยน "Dairy products" เป็น Dairy_products สำหรับหมวดหมู่ที่มีช่องว่าง ให้ใช้ =INDIRECT(SUBSTITUTE($A2," ","_")) แทน

เพิ่มข้อความนำเข้าและการแจ้งเตือนข้อผิดพลาด

• ข้อความนำเข้า (Input Message): ทำเครื่องหมายที่ แสดงข้อความนำเข้าเมื่อเลือกเซลล์ (Show input message when cell is selected) แล้วใส่ชื่อเรื่องและข้อความสั้นๆ เช่น "เลือกสถานะของงาน" ข้อความจะปรากฏทุกครั้งที่มีคนเลือกเซลล์
• การแจ้งเตือนข้อผิดพลาด (Error Alert): ทำเครื่องหมายที่ แสดงการแจ้งเตือนข้อผิดพลาดหลังจากป้อนข้อมูลไม่ถูกต้อง (Show error alert after invalid data is entered) แล้วเลือก ลักษณะ (Style) หยุด (Stop) จะบล็อกสิ่งที่ไม่อยู่ในรายการ ส่วน คำเตือน (Warning) และ ข้อมูล (Information) จะแสดงข้อความของคุณแต่ปล่อยให้ป้อนข้อมูลได้

โปรดทราบว่าการตรวจสอบจะตรวจเฉพาะสิ่งที่ผู้ใช้พิมพ์ การวางทับเซลล์จะแทนที่ทั้งดรอปดาวน์และเนื้อหาของเซลล์

ค้นหาในดรอปดาวน์ด้วยการพิมพ์

ใน Excel เวอร์ชัน Microsoft 365 ปัจจุบัน การพิมพ์ในเซลล์ดรอปดาวน์จะกรองรายการให้เหลือเฉพาะรายการที่มีตัวอักษรเหล่านั้น ทำให้รายการยาวๆ ใช้งานเร็วขึ้นมาก Excel 2021 และเวอร์ชันก่อนหน้าไม่มีฟีเจอร์นี้

แก้ไข คัดลอก หรือลบดรอปดาวน์ลิสต์

เพิ่มหรือเปลี่ยนรายการ สำหรับตาราง ให้พิมพ์รายการใหม่ไว้ด้านล่าง สำหรับช่วงเซลล์ ให้แก้ไขเซลล์ แล้วเปิด ข้อมูล (Data) → Data Validation อีกครั้งเพื่อปรับ Source หากช่วงยาวขึ้น สำหรับรายการที่พิมพ์เอง ให้แก้ข้อความใน Source ทำเครื่องหมายที่ นำการเปลี่ยนแปลงเหล่านี้ไปใช้กับเซลล์อื่นทั้งหมดที่มีการตั้งค่าเดียวกัน (Apply these changes to all other cells with the same settings) เพื่ออัปเดตทุกดรอปดาวน์ที่ตรงกันในครั้งเดียว

คัดลอกดรอปดาวน์ไปยังเซลล์อื่น ลากจุดจับเติมลงด้านล่าง หรือคัดลอกเซลล์ เลือกปลายทาง แล้วเลือก หน้าแรก (Home) → วาง (Paste) → วางแบบพิเศษ (Paste Special) → การตรวจสอบ (Validation) เพื่อคัดลอกเฉพาะดรอปดาวน์โดยไม่เอาค่าไปด้วย

ลบดรอปดาวน์ เลือกเซลล์ ไปที่ ข้อมูล (Data) → Data Validation คลิก ล้างทั้งหมด (Clear All) แล้วคลิก ตกลง (OK) ค่าที่อยู่ในเซลล์แล้วจะยังคงอยู่ หากต้องการหาเซลล์ทั้งหมดที่มีดรอปดาวน์ ให้ใช้ หน้าแรก (Home) → ค้นหาและเลือก (Find & Select) → การตรวจสอบข้อมูล (Data Validation)

ทำไมดรอปดาวน์ใช้งานไม่ได้

• ไม่มีลูกศรปรากฏ เปิด ข้อมูล (Data) → Data Validation แล้วทำเครื่องหมายที่ In-cell dropdown ลูกศรจะแสดงเมื่อเลือกเซลล์เท่านั้นด้วย หากลูกศรหายไปทุกที่ ให้ตรวจ ไฟล์ (File) → ตัวเลือก (Options) → ขั้นสูง (Advanced) → ตัวเลือกการแสดงผลสำหรับเวิร์กบุ๊กนี้ (Display options for this workbook) แล้วตั้ง สำหรับออบเจ็กต์ ให้แสดง (For objects, show) เป็น ทั้งหมด (All)
• รายการใหม่ไม่ปรากฏ แหล่งที่มาเป็นช่วงคงที่ ให้ขยายช่วง หรือแปลงรายการเป็นตารางหรือช่วงสปิลล์เพื่อให้เพิ่มขึ้นเอง
• Data Validation เป็นสีเทา ชีตถูกป้องกันอยู่ (รีวิว (Review) → ยกเลิกการป้องกันชีต (Unprotect Sheet)) เวิร์กบุ๊กใช้ฟีเจอร์เวิร์กบุ๊กที่แชร์แบบเดิม มีหลายชีตที่จัดกลุ่มไว้ (คลิกแท็บชีตเดียว) หรือคุณยังแก้ไขเซลล์อยู่ (กด Enter หรือ Esc)
• รายการมีช่องว่าง ช่วงแหล่งที่มารวมเซลล์ว่าง ให้ลดช่วงลงหรือใช้ตาราง
• มีคนพิมพ์ค่าที่ไม่อยู่ในรายการ ลักษณะของ การแจ้งเตือนข้อผิดพลาด (Error Alert) เป็น Warning หรือ Information หรือปิดการแจ้งเตือนไว้ ให้เปลี่ยนเป็น Stop

คำถามที่พบบ่อย

จะเพิ่มดรอปดาวน์ลิสต์ให้ทั้งคอลัมน์ได้อย่างไร เลือกช่วงคอลัมน์ที่ต้องการ เช่น C2:C1000 ก่อนเปิด Data Validation หากข้อมูลของคุณอยู่ในตาราง ดรอปดาวน์ที่ใช้กับคอลัมน์จะส่งต่อไปยังแถวใหม่โดยอัตโนมัติ

ดรอปดาวน์ลิสต์ใน Excel เลือกได้หลายรายการไหม ไม่ได้ด้วย Data Validation ในตัว แต่ละเซลล์เก็บได้หนึ่งตัวเลือก การเลือกหลายค่าต้องใช้มาโคร VBA หรือเลย์เอาต์อื่น เช่น หนึ่งคอลัมน์ต่อหนึ่งตัวเลือก

ดรอปดาวน์ลิสต์มีรายการได้กี่รายการ รายการจากเซลล์มีได้เป็นพันรายการ แต่รายการยาวๆ เลื่อนดูลำบาก ส่วนรายการที่พิมพ์เองถูกจำกัดด้วยช่อง Source ที่รองรับ 255 อักขระ

จะสร้างดรอปดาวน์ Yes และ No ได้อย่างไร ตั้ง Allow เป็น List แล้วพิมพ์ Yes,No ใน Source

จะลบดรอปดาวน์แต่เก็บค่าไว้ได้อย่างไร เลือกเซลล์ ไปที่ ข้อมูล (Data) → Data Validation → Clear All แล้วคลิก OK ค่าปัจจุบันจะยังคงอยู่เป็นข้อความธรรมดา

รักษาข้อมูลในรายการให้สะอาด

ดรอปดาวน์จะทำงานได้ดีก็ต่อเมื่อข้อมูลเบื้องหลังสอดคล้องกัน CoreGPT for Excel ให้คุณอธิบายงานทำความสะอาดเป็นภาษาธรรมดา เช่น "ลบลูกค้าที่ซ้ำกันและแก้การสะกดชื่อประเทศที่ไม่ตรงกัน" แล้วทำให้ในเวิร์กบุ๊กของคุณ รายการที่ใช้สร้างดรอปดาวน์จึงเป็นระเบียบอยู่เสมอ เริ่มใช้ได้ฟรี และใช้งานได้ใน Excel บนเว็บ Windows และ Mac

ลองใช้งาน: CoreGPT for Excel

อ่านเพิ่มเติม:

• ลบข้อมูลซ้ำ Excel 5 วิธี (ทั้งแบบลบข้อมูลทิ้งและแบบไม่ลบ)
• เคล็ดลับ Excel ใน Microsoft 365 ที่ช่วยประหยัดเวลาทุกสัปดาห์
• วิธีแชร์ไฟล์ Excel ให้หลายคนแก้ไขพร้อมกัน