การทำตัวเลือกแบบ drop down แบบหลายชั้นใน Excel

สรุปย่อ

การทำตัวเลือกแบบ drop down หลายชั้น หรือตัวเลือกในเซลล์หนึ่งขึ้นอยู่กับข้อมูลที่เราเลือกในเซลล์อื่น เราจะใช้ data validation, named range, และ INDIRECT() ทำงานร่วมกัน

ตัวอย่าง

สมมติว่าเราต้องการให้ตัวเลือกในคอลัมภ์ A เป็นประเภทของสินค้า ซึ่งมี 3 อย่าง ก็คือ ผลไม้ เนื้อสัตว์ และพืชผัก

และข้อมูลในคอลัมภ์ B จะขึ้นอยู่กับสิ่งที่เราเลือกในคอลัมภ์ A เช่น ถ้าคอลัมภ์ A เลือกเป็น “พืชผัก” ในคอลัมภ์ B ควรจะมีตัวเลือกเป็น ผักหัว ผักใบ ผักสลัด เป็นต้น ดังภาพ

ขั้นตอนที่ 1 กำหนดตัวเลือกและกำหนดชื่อ (named range)

จากภาพตัวอย่างด้านบน จะเห็นว่าผมใส่ข้อมูลที่จะใช้เป็นตัวเลือกไว้ในคอลัมภ์ E ถึง G โดยประเภทที่จะเลือกในคอลัมภ์ A จะอยู่ในแถวที่ 1 ส่วนตัวเลือกที่จะให้แสดงในคอลัมภ์ B จะอยู่ในแถวที่ 2 เป็นต้นไป

ที่นี้การกำหนดชื่อเรียกใช้กับ cell หรือ range ให้เราไปที่แถบ (tab) Formulas > Name Manager แล้วเลือก New… แล้วจะมีหน้าต่างขึ้นมาให้เราใส่ข้อมูล ดังภาพ

แล้วให้เรากำหนดชื่อที่เราต้องการในช่อง Name: พร้อมกับเลือก cell หรือ range ของข้อมูลในช่อง Refer to:

โดยในตัวอย่างที่เราใช้ แต่ละช่องจะใส่ข้อมูลตามตารางนี้

Name:	Scope:	Comment:	Refers to:
ประเภท	Workbook		=Sheet1!$E$1:$G$1
ผลไม้	Workbook		=Sheet1$E$2:$E$12
เนื้อสัตว์	Workbook		=Sheet1$F$2:$F$10
พืชผัก	Workbook		=Sheet1$G$2:$G$7

📝 กฎการตั้งชื่อ named range หรือ Name Manager ของ Excel

ชื่อจะต้องขึ้นต้นด้วยตัวอักษร หรือ underscore ( _ ) หรือ backslash ( \ )
ในชื่อจะต้องไม่มีช่องว่าง (space) รวมถึงสัญลักษณ์พิเศษอื่น ๆ
อย่าตั้งชื่อเหมือนชื่อ cell หรือ range อย่างเช่น A1 Z100 หรือ XY22.
ใช้อักษรตัวเดียวก็ได้ แต่จะใช้ตัว r กับตัว c ไม่ได้ เพราะ Excel สงวนไว้ใช้เอง
ตัวอักษรเล็ก หรือใหญ่ ถือว่าเหมือนกัน (not case-sensitive) ดังนั้น VAT, Vat, vat, vAt จะถือว่าเป็นชื่อเดียวกัน

ขั้นตอนที่ 2 กำหนด data validation ให้ range ที่เราต้องการใช้ drop down

เลือก range หรือ cell (ซึ่งในภาพเลือกหลายเซลล์ หรือ range 😂 บอกเ.ฉย ๆ เผื่อเผลอลืม) ที่ต้องการจะตรวจสอบข้อมูลว่า ใส่ได้เฉพาะที่เข้าเงื่อนไขเท่านั้น หรือ data validation นั่นเอง
ไปที่แถบ (tab) Data
คลิกที่ไอคอนของ Data Validation

ที่แถบ Settings ในช่อง Allow: ให้เลือกเป็น List
ในช่อง Source: ให้พิมพ์ =ประเภท หรือชื่อที่คุณตั้งไว้ในขั้นตอนที่ 1 (หรือใส่ =Sheet1!$E$1:$G$1 ก็ได้ หากคุณไม่ได้ทำตามขั้นตอนที่ 1 มาก่อน แต่การทำแบบนี้หากคุณปรับ range ที่เก็บข้อมูล “ประเภท” คุณต้องมาไล่แก้ค่าที่ใส่นี้ในทุก cell คงไม่สะดวกนัก ถึงจะมีตัวเลือก ◽ Apply these changes to all other cells with the same settings แต่แนะนำให้ใช้ named range จะดีกว่า)

พอเสร็จขั้นตอนนี้ หากคุณคลิกที่ A2 ถึง A10 จะเห็นว่าจะมีตัวเลือก หรือรายการให้เลือกแบบ drop down ขึ้นมาแล้ว อย่างในภาพ

ขั้นตอนที่ 3 การทำตัวเลือกแบบ drop down ชั้นที่ 2 โดยใช้ฟังก์ชัน INDIRECT

ซึ่งตัวเลือกในชั้นที่ 2 นี้จะขึ้นอยู่กับตัวเลือกที่เราเลือกไปก่อนหน้านี้ หรือในคอลัมภ์ A ของตัวอย่างนี้

โดยเราก็จะใช้ data validation เหมือนในขั้นตอนที่ 2 แต่ในช่อง Source: เราจะใช้ฟังก์ชัน INDIRECT ไปดูชื่อ “ประเภท” ที่ถูกเลือกไว้ในคอลัมภ์ A ของแถวนั้น ๆ สำหรับตัวอย่างที่เราใช้จะมีขั้นตอนการทำดังนี้

เลือก range ที่ต้องการ (ในตัวอย่างคือ B2:B10) แล้วไปที่แถบ Data > Data Validation

ดูให้แน่ใจว่าในช่อง Allow: คุณเลือกเป็น List
ในช่อง Source: ให้พิมพ์ =INDIRECT($A2) ❗ให้สังเกตนะ เรา fix คอลัมภ์อย่างเดียวนะ ไม่ได้ fix แถว

เมื่อเสร็จขั้นตอนนี้แล้ว ลองเลือกตัวเลือกในคอลัมภ์ B ดู โดยให้สังเกตว่า จะมีเฉพาะตัวเลือกที่อยู่ในหมวดของคอลัมภ์ A เท่านั้น

อย่างเช่นถ้าคอลัมภ์ A เป็น “พีชผัก” ตัวเลือกในคอลัมภ์ B ก็จะมีเฉพาะ ผักหัว ผักใบ ผักสลัด …

จากตัวอย่างการทำตัวเลือกแบบ drop down 2 ชั้นนี้ คุณสามารถนำไปประยุกต์ใช้แบบหลาย ๆ ชั้นก็ได้ โดยการผ่านกำหนดข้อมูลและตั้งชื่อ แล้วมาใช้ Data Validation ร่วมกับฟังก์ชัน INDIRECT แบบที่เราทำใน 3 ขั้นตอนที่ผ่านมา

⚠️ข้อควรระวัง

ชื่อหมวดหมู่ที่จะใช้ ต้องเป็นไปตามกฎการตั้งชื่อ named range ของ Excel เท่านั้น ❗สำคัญมาก // หมายความว่า คุณจะตั้งชื่อตามใจตนเองได้ แต่ไม่มากนัก
ใน Range ที่จะกำหนด อย่าเลือกช่องว่างเผื่อไว้ สำหรับเพิ่มข้อมูลทีหลัง เพราะช่องว่างจะแสดงมาให้เลือกด้วย ตามจำนวนช่องว่างที่คุณเลือกไว้เลยด้วย (ตรงนี้ทาง Microsoft ทราบปัญหาอยู่แล้ว และกำลังให้กลุ่มผู้ใช้ Office Insider Program ทดลองใช้อยู่)
หากคุณพิมพ์ตัวเลือกซ้ำ Excel ก็จะแสดงข้อมูลนั้นซ้ำด้วย
ตัวเลือกจะเรียงตามลำดับที่คุณป้อนข้อมูลเข้าไป Excel จะไม่เรียงให้ หากต้องการข้อมูลเรียงตามลำดับอักษร ให้จัดเรียงใน range ที่กำหนดไว้

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

สำหรับไฟล์ตัวอย่างดาวน์โหลดได้นี่ ➡️ ตัวอย่างการทำตัวเลือกแบบ drop down หลายชั้นใน Excel

🎉 ในไฟล์มีตัวอย่างการทำ drop down ขั้นที่ 3 ด้วยนะ ✨

อ่านแล้ว ถ้าติดปัญหาตรงไหน ก็ใส่ comment ไว้ได้เลยครับ 😊