สร้างรายการแบบเลื่อนลงแบบไดนามิกใน Excel ด้วยฟังก์ชัน OFFSET – 3 วิธีที่พิสูจน์แล้ว

Excel เป็นเครื่องมือที่มีประโยชน์ที่สุดเมื่อต้องจัดการกับชุดข้อมูลขนาดใหญ่ ปกติเราคุ้นเคยกับการสร้าง รายการแบบเลื่อนลง (drop-down list) แต่เรามักจะต้อง สร้างรายการแบบเลื่อนลงแบบไดนามิก (dynamic drop-down list) ใน Excel เพื่อให้การทำงานสะดวกขึ้น เราสามารถทำได้ง่ายๆ โดยใช้ ฟังก์ชัน OFFSET ในบทความนี้ จะแนะนำวิธีสร้าง รายการแบบเลื่อนลงแบบไดนามิก ใน Excel ด้วย ฟังก์ชัน OFFSET

นี่คือชุดข้อมูลตัวอย่างที่จะใช้สาธิตวิธีสร้าง รายการแบบเลื่อนลงแบบไดนามิก ใน Excel ด้วย ฟังก์ชัน OFFSET เรามี กิจกรรมกีฬา (Events) และ รายชื่อผู้ชนะ (List of Winners) เราจะจัดกลุ่มผู้ชนะให้ตรงกับกิจกรรมแต่ละรายการโดยการสร้าง รายการแบบเลื่อนลงแบบไดนามิก

3 วิธีสร้างรายการแบบเลื่อนลงแบบไดนามิกด้วยฟังก์ชัน OFFSET ของ Excel

1. สร้างรายการแบบเลื่อนลงแบบไดนามิกใน Excel ด้วยฟังก์ชัน OFFSET และ COUNTA

ในส่วนนี้ จะสาธิตวิธีสร้าง รายการแบบเลื่อนลงแบบไดนามิก ใน Excel โดยใช้ ฟังก์ชัน OFFSET และ COUNTA ต้องการสร้างรายการแบบเลื่อนลงแบบไดนามิกในช่วง C4:C11 โดยเลือก ผู้ชนะ (Winner) จาก รายชื่อผู้ชนะ (List of Winners)

ขั้นตอน:

1. เลือกช่วง C4:C11 จากนั้นไปที่แท็บ Data (ข้อมูล) > Data Tools (เครื่องมือข้อมูล) > Data Validation (ตรวจสอบข้อมูล) > Data Validation

2. กล่องโต้ตอบ Data Validation จะปรากฏขึ้น เลือก List (รายการ) จากรายการแบบเลื่อนลงในกล่องโต้ตอบ

3. ในกล่อง Source (แหล่งที่มา) ให้ใส่สูตรดังต่อไปนี้:

=OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1)

การวิเคราะห์สูตร

• COUNTA($E$4:$E$100) ➜ นับจำนวนเซลล์ที่ ไม่ว่างเปล่า ในช่วง E4:E100 → ผลลัพธ์: {4}
• OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1) ➜ ส่งคืนช่วงข้อมูลตามแถวและคอลัมน์ของจุดอ้างอิงที่กำหนด
• OFFSET($E$4,0,0,4,1) → ผลลัพธ์: {"Alex";"Morgan";"Faulkner";"Eliot"}

คำอธิบาย: จุดอ้างอิงคือ E4 เนื่องจาก แถว (row) เป็น 0 และ คอลัมน์ (column) เป็น 0 พร้อมกับ ความสูง (height) 4 เซลล์ จึงจะได้ค่าจากเซลล์ E4:E7

4. คลิก OK

Excel จะสร้าง กล่องแบบเลื่อนลง (drop-down box) ในทุก เซลล์ ของ ช่วง C4:C11

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

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

หมายเหตุ: จำไว้ว่า ช่วง ที่เลือกใน ฟังก์ชัน COUNTA คือ E4:E100 นั่นคือเหตุผลที่ Excel จะอัปเดต ตัวเลือกแบบเลื่อนลง ตราบใดที่เราเพิ่มหรือแก้ไข เซลล์ ในช่วง E4:E100

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

2. วิธีสร้างรายการแบบเลื่อนลงแบบไดนามิกใน Excel ด้วยฟังก์ชัน OFFSET และ COUNTIF

เรายังสามารถสร้าง รายการแบบเลื่อนลงแบบไดนามิก ใน Excel โดยใช้ ฟังก์ชัน OFFSET และ COUNTIF ได้เช่นกัน

ขั้นตอน:

1. เปิดกล่องโต้ตอบ Data Validation เหมือน วิธีที่ 1 ในกล่อง Source ใส่สูตรดังนี้:

=OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,"<>"))

การวิเคราะห์สูตร

• COUNTIF($E$4:$E$100,"<>") ➜ นับจำนวนเซลล์ที่ ไม่ว่างเปล่า ในช่วง E4:E100 → ผลลัพธ์: {4}
• OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,"<>")) ➜ ส่งคืนช่วงข้อมูลตามจุดอ้างอิง
• OFFSET($E$4,0,0,4,1) → ผลลัพธ์: {"Alex";"Morgan";"Faulkner";"Eliot"}

คำอธิบาย: จุดอ้างอิงคือ E4 เนื่องจาก แถว เป็น 0 และ คอลัมน์ เป็น 0 พร้อมกับ ความสูง 4 เซลล์ จึงจะได้ค่าจากเซลล์ E4:E7

2. คลิก OK

3. Excel จะสร้าง กล่องแบบเลื่อนลง ในทุก เซลล์ ของ ช่วง C4:C11

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

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

หมายเหตุ: จำไว้ว่า ช่วง ที่เลือกใน ฟังก์ชัน COUNTIF คือ E4:E100 นั่นคือเหตุผลที่ Excel จะอัปเดต ตัวเลือกแบบเลื่อนลง ตราบใดที่เราเพิ่มหรือแก้ไข เซลล์ ในช่วง E4:E100

3. วิธีสร้างรายการแบบเลื่อนลงแบบซ้อน (Nested Drop-Down List) ด้วยการผสมผสานฟังก์ชัน

ในส่วนนี้ จะแนะนำการสร้าง รายการแบบเลื่อนลงแบบไดนามิก ที่ฉลาดและขั้นสูงกว่า คือ รายการแบบซ้อน (nested) จะใช้ฟังก์ชัน OFFSET, COUNTA, และ MATCH ร่วมกัน มาอธิบายเป้าหมายของเราก่อน
นี่คือ ชุดข้อมูล สำหรับวิธีนี้ ซึ่งแสดงข้อมูลของผลิตภัณฑ์เฉพาะเจาะจง เราจะสร้าง รายการแบบเลื่อนลง 2 รายการ ใน เซลล์ F3 และ F4 ขึ้นอยู่กับตัวเลือกที่เลือกใน F3 Excel จะอัปเดตตัวเลือกใน F4 มาทำทีละขั้นตอน

ขั้นตอนที่ 1: สร้างรายการแบบเลื่อนลงใน F3

1. เปิดกล่องโต้ตอบ Data Validation เหมือน วิธีที่ 1 ในกล่อง Source ให้ทำ การอ้างอิงเซลล์ (cell reference) ซึ่งคือ หัวตาราง (table headers) (เซลล์ B3:D3)

2. Excel จะสร้าง รายการแบบเลื่อนลง ใน F3

ขั้นตอนที่ 2: สร้างรายการแบบเลื่อนลงแบบไดนามิกใน F4

ตอนนี้จะสร้าง รายการแบบเลื่อนลง อีกอันใน F4 ตัวเลือกใน รายการแบบเลื่อนลง ของ F4 จะขึ้นอยู่กับสิ่งที่เลือกใน รายการแบบเลื่อนลง ของ F3 วิธีทำ:

1. เปิดกล่องโต้ตอบ Data Validation เหมือน วิธีที่ 1 ในกล่อง Source ใส่สูตรดังนี้:

=OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)),1)

การวิเคราะห์สูตร

• MATCH($F$3,$B$3:$D$3,0) ➜ ส่งคืนตำแหน่งสัมพัทธ์ของ ค่าเซลล์ F3 จาก ช่วง B3:D3 → ผลลัพธ์: {1}
• OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1) ➜ ส่งคืน ช่วง ตาม แถว และ คอลัมน์ ของ จุดอ้างอิงที่กำหนด ความสูง คือ 10 ดังนั้น ผลลัพธ์ จะเป็นอาร์เรย์ของ 10 ค่าเซลล์ เริ่มจากจุดอ้างอิง → ผลลัพธ์: {"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0}
• COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)) ➜ ส่งคืน จำนวนเซลล์ ที่ ไม่ว่างเปล่าใน ช่วงที่เลือก → COUNTA{"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0} → ผลลัพธ์: {4}
• OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)),1) ➔ ส่งคืน ช่วง ตาม แถว และ คอลัมน์ ของจุดอ้างอิงที่กำหนด → OFFSET($B$3,1,1-1,COUNTA{"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0}),1) → OFFSET($B$3,1,0,4,1) → ผลลัพธ์: {"Sam";"Curran";"Yank";"Rochester"}

คำอธิบาย: จุดอ้างอิงคือ B3 เนื่องจาก แถว เป็น 1 และ คอลัมน์ เป็น 0 พร้อมกับ ความสูง 4 เซลล์ จึงจะได้ค่าจากเซลล์ B4:B7

2. คลิก OK

Excel จะสร้าง รายการแบบเลื่อนลงแบบไดนามิก ใน F4 ตัวเลือกจะเปลี่ยนแปลงขึ้นอยู่กับสิ่งที่คุณเลือกใน F3 ตัวอย่างเช่น เมื่อเลือก Name ใน รายการแบบเลื่อนลง F3 รายการแบบเลื่อนลง ใน F4 จะแสดงชื่อที่มีอยู่ใน คอลัมน์ Name

เช่นเดียวกัน เมื่อเลือก Product ใน รายการแบบเลื่อนลง F3 รายการแบบเลื่อนลง ใน F4 จะแสดงผลิตภัณฑ์ที่มีอยู่ใน คอลัมน์ Product

ตอนนี้ถ้าคุณเพิ่มหรือแก้ไข Name, Product, หรือ Brand Excel จะอัปเดต รายการแบบเลื่อนลง ใน F4 ตัวอย่างเช่น ผมเพิ่มชื่อใหม่ Rock ใน คอลัมน์ Name และ Excel ได้เพิ่มชื่อใน รายการแบบเลื่อนลง

อ่านเพิ่มเติม: วิธีสร้างรายการ Top 10 แบบไดนามิกใน Excel (8 วิธี)

สมุดทำงานฝึกปฏิบัติ (Practice Workbook)

Какที่เห็น การสร้าง รายการแบบเลื่อนลงแบบไดนามิก ใน Excel ด้วย ฟังก์ชัน OFFSET ค่อนข้างซับซ้อน จึงขอแนะนำให้ฝึกฝนมากขึ้น ผมได้แนบไฟล์ฝึกปฏิบัติไว้ให้