สรุปย่อ
การทำตัวเลือกแบบ 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 ไว้ได้เลยครับ 😊