Excel เป็นเครื่องมือที่มีประโยชน์ที่สุดเมื่อต้องจัดการกับชุดข้อมูลขนาดใหญ่ โดยปกติเราจะคุ้นเคยกับการสร้าง รายการแบบหล่นลง แต่เรามักจะต้องสร้างรายการแบบเลื่อนลงแบบไดนามิก ใน Excel เพื่อให้ชีวิตของเราง่ายขึ้น เราสามารถทำได้โดยง่ายโดยใช้ฟังก์ชัน OFFSET . ในบทความนี้ ผมจะแสดงวิธีสร้างรายการแบบเลื่อนลงแบบไดนามิก ใน Excel ด้วย OFFSET ฟังก์ชัน .
นี่คือชุดข้อมูลที่ฉันจะใช้เพื่อแสดงวิธีสร้าง รายการดรอปดาวน์แบบไดนามิก ใน Excel ด้วย ฟังก์ชัน OFFSET . เรามีกีฬา กิจกรรม และ รายชื่อผู้ชนะ . เราจะจัดเรียงผู้ชนะตามเหตุการณ์ที่เกี่ยวข้องโดยการสร้างรายการแบบเลื่อนลงแบบไดนามิก .
3 วิธีในการสร้างรายการดรอปดาวน์แบบไดนามิกโดยใช้ Excel OFFSET
1. สร้างรายการดรอปดาวน์แบบไดนามิกใน Excel ด้วยฟังก์ชัน OFFSET และ COUNTA
ที่นี่ ฉันจะอธิบายวิธีสร้างรายการดรอปดาวน์แบบไดนามิก ใน Excel โดยใช้ ออฟเซ็ต และ COUNTA ฟังก์ชั่น. ฉันต้องการสร้างรายการดรอปดาวน์แบบไดนามิก ในช่วง C4:C11 . ฉันจะเลือก ผู้ชนะ จาก รายชื่อผู้ชนะ .
ขั้นตอน:
➤ เลือกช่วง C4:C11 . จากนั้นไปที่ ข้อมูล แท็บ>> เครื่องมือข้อมูล>> การตรวจสอบข้อมูล>> การตรวจสอบข้อมูล .
➤ การตรวจสอบข้อมูล กล่องโต้ตอบจะปรากฏขึ้น เลือก รายการ จาก ดรอปดาวน์ ในกล่องโต้ตอบนั้น .
➤ ใน ที่มา ให้เขียนสูตรต่อไปนี้
=OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1)
รายละเอียดสูตร
➥ COUNTA($E$4:$E$100) ➜ ส่งกลับจำนวนเซลล์ที่ ไม่ว่าง อยู่ในช่วง E4:E100
ผลลัพธ์ ➜ {4}
➥ OFFSET($E$4,0,0,0,COUNTA($E$4:$E$100),1) ➜ ส่งกลับช่วงตามแถวและคอลัมน์ของข้อมูลอ้างอิงที่กำหนด
➥ OFFSET($E$4,0,0,4,1)
เอาท์พุต ➜ {“Alex”;”Morgan”;”Faulkner”;”Eliot”}
คำอธิบาย: ข้อมูลอ้างอิง คือ E4 . ตั้งแต่ แถว คือ 0 และ คอลัมน์ คือ 0 ในที่สุดก็มี ความสูง จาก 4 เซลล์เราจะได้ค่าจากเซลล์ E4:E7 .
➤ เลือก ตกลง . 
Excel จะสร้าง ดรอปดาวน์ กล่อง ในแต่ละ เซลล์ ของ ช่วง C4:C11 .
สังเกตว่าตัวเลือกในช่องแบบเลื่อนลง เหมือนกับ รายชื่อผู้ชนะ . ตอนนี้ เพื่อตรวจสอบว่านี่เป็น กล่องแบบเลื่อนลงแบบไดนามิก . หรือไม่ หรือไม่ สมมติว่า ผู้ชนะ ของ ถ่ายงาน คือ เจมส์ . ตั้งแต่ เจมส์ ไม่อยู่ในรายชื่อผู้ชนะ มาเพิ่มชื่อของเขาและดูว่าเกิดอะไรขึ้น
ทันทีที่เราเพิ่มชื่อ เจมส์ ใน รายชื่อผู้ชนะ , Excel อัปเดตตัวเลือกโดยอัตโนมัติในตัวเลือกแบบเลื่อนลง . ดังนั้น รายการแบบเลื่อนลง เป็น ไดนามิก ในธรรมชาติ .
➤ ตอนนี้เลือก ผู้ชนะ ที่เหลืออยู่ .
หมายเหตุ :จำไว้ว่า ช่วง เราได้เลือกใน ฟังก์ชัน COUNTA คือ E4:E100 . นั่นเป็นเหตุผลที่ Excel จะอัปเดต ตัวเลือกแบบเลื่อนลง ตราบใดที่เราเพิ่มหรืออัปเดตเซลล์ อยู่ในช่วง E4:E100 .
อ่านเพิ่มเติม: วิธีการสร้างรายการตรวจสอบข้อมูลแบบไดนามิกโดยใช้ VBA ใน Excel
2. วิธีสร้างรายการดรอปดาวน์แบบไดนามิกใน Excel ด้วยฟังก์ชัน OFFSET และ COUNTIF
นอกจากนี้เรายังสามารถสร้างรายการแบบเลื่อนลงแบบไดนามิก ใน Excel โดยใช้ ออฟเซ็ต และ COUNTIF ฟังก์ชัน
ขั้นตอน:
➤ นำ การตรวจสอบความถูกต้องของข้อมูล กล่องโต้ตอบ เช่น method-1 . ใน ที่มา ให้เขียนสูตรต่อไปนี้
=OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,"<>"))
รายละเอียดสูตร
➥ COUNTIF($E$4:$E$100,”<>”) ➜ ส่งกลับจำนวนเซลล์ที่ ไม่ว่าง อยู่ในช่วง E4:E100
ผลลัพธ์ ➜ {4}
➥ OFFSET($E$4,0,0,0,COUNTIF($E$4:$E$100,”<>”)) ➜ ส่งกลับช่วงตามแถวและคอลัมน์ของข้อมูลอ้างอิงที่กำหนด
➥ OFFSET($E$4,0,0,4,1)
เอาท์พุต ➜ {“Alex”;”Morgan”;”Faulkner”;”Eliot”}
คำอธิบาย: ข้อมูลอ้างอิง คือ E4 . ตั้งแต่ แถว คือ 0 และ คอลัมน์ คือ 0 ในที่สุดก็มี ความสูง จาก 4 เซลล์เราจะได้ค่าจากเซลล์ E4:E7
➤ เลือก ตกลง . 
➤ Excel จะสร้าง ดรอปดาวน์ กล่อง ในแต่ละ เซลล์ ของ ช่วง C4:C11 .
เพื่อตรวจสอบว่านี่คือ กล่องไดนามิกแบบเลื่อนลง หรือไม่ สมมติว่า ผู้ชนะ ของ ถ่ายงาน คือ เจมส์ . ตั้งแต่ เจมส์ ไม่อยู่ในรายชื่อผู้ชนะ มาเพิ่มชื่อของเขาและดูว่าเกิดอะไรขึ้น
ทันทีที่เราเพิ่มชื่อ เจมส์ ใน รายชื่อผู้ชนะ , Excel อัปเดตตัวเลือกโดยอัตโนมัติในตัวเลือกแบบเลื่อนลง . ดังนั้น รายการแบบเลื่อนลง เป็น ไดนามิก ในธรรมชาติ .
➤ ตอนนี้เลือก ผู้ชนะ ที่เหลืออยู่ .
หมายเหตุ :จำไว้ว่า ช่วง เราได้เลือกใน ฟังก์ชัน COUNTIF คือ E4:E100 . นั่นเป็นเหตุผลที่ Excel จะอัปเดต ตัวเลือกแบบเลื่อนลง ตราบใดที่เราเพิ่มหรืออัปเดตเซลล์ อยู่ในช่วง E4:E100 .
3. วิธีสร้างรายการดรอปดาวน์ที่ซ้อนกันโดยใช้ฟังก์ชันต่างๆ ร่วมกัน
ในส่วนนี้ เราจะเจาะลึกไปที่การสร้างรายการดรอปดาวน์แบบไดนามิกที่ชาญฉลาดและล้ำหน้ากว่า , ซ้อน หนึ่ง. เราจะใช้ OFFSET , COUNTA และ MATCH ทำหน้าที่ร่วมกัน ให้ฉันอธิบายว่าเรากำลังทำอะไรอยู่
นี่คือ ชุดข้อมูล สำหรับวิธีนี้ที่แสดงข้อมูลของผลิตภัณฑ์เฉพาะ.. โดยพื้นฐานแล้ว เราจะสร้างรายการแบบเลื่อนลงสองรายการ ใน เซลล์ F3 และ F4 . ขึ้นอยู่กับตัวเลือกที่เลือกใน F3 , Excel จะอัปเดตตัวเลือกใน F4 . มาทำทีละขั้นตอน
ขั้นตอนที่ 1:การสร้างรายการแบบเลื่อนลงใน F3
➤ นำการตรวจสอบความถูกต้องของข้อมูล กล่องโต้ตอบ เช่น method-1 . ใน ที่มา กล่อง ทำการอ้างอิงเซลล์ ซึ่งเป็น ส่วนหัวของตาราง (เซลล์ B3:D3 )
Excel จะสร้าง รายการแบบเลื่อนลง ใน F3 .
ขั้นตอนที่ 2:การสร้างรายการดรอปดาวน์แบบไดนามิกใน F4
ตอนนี้ฉันจะสร้าง รายการดรอปดาวน์ ใน F4 . ตัวเลือกใน รายการแบบเลื่อนลง ของ F4 จะขึ้นอยู่กับสิ่งที่เราเลือกใน รายการแบบเลื่อนลง ของ F3 . ในการทำเช่นนั้น
➤ นำ การตรวจสอบความถูกต้องของข้อมูล กล่องโต้ตอบ เช่น method-1 . ใน ที่มา ให้เขียนสูตรต่อไปนี้
=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 .
➤ เลือก ตกลง . 
Excel จะสร้างรายการแบบเลื่อนลงแบบไดนามิก ใน F4 . ตัวเลือกจะเปลี่ยนไปขึ้นอยู่กับสิ่งที่คุณเลือกใน F3 . ตัวอย่างเช่น เมื่อคุณเลือก ชื่อ ใน รายการแบบเลื่อนลง F3 , รายการแบบเลื่อนลง ใน F4 จะแสดงชื่อที่มีอยู่ในคอลัมน์ชื่อ .
ในทำนองเดียวกัน เมื่อคุณเลือก ผลิตภัณฑ์ ใน รายการแบบเลื่อนลง F3 , รายการแบบเลื่อนลง ใน F4 จะแสดงผลิตภัณฑ์ที่มีอยู่ในคอลัมน์ผลิตภัณฑ์ .
ตอนนี้ หากคุณเพิ่มหรืออัปเดต ชื่อ , ผลิตภัณฑ์ หรือ แบรนด์ , Excel จะอัปเดต รายการแบบเลื่อนลง ใน F4 . ตัวอย่างเช่น ฉันได้เพิ่มชื่อใหม่ว่า ร็อค ใน คอลัมน์ชื่อ และ Excel ได้เพิ่มชื่อใน รายการแบบเลื่อนลง . 
อ่านเพิ่มเติม: วิธีสร้างรายการไดนามิก 10 อันดับแรกใน Excel (8 วิธี)
สมุดแบบฝึกหัด
อย่างที่คุณเห็น การสร้างรายการดรอปดาวน์แบบไดนามิก ใน Excel ด้วย ฟังก์ชัน OFFSET เป็นเรื่องยุ่งยากจริงๆ ดังนั้นฉันแนะนำให้คุณฝึกฝนให้มากขึ้น ฉันได้แนบเอกสารแบบฝึกหัดมาให้คุณ
บทสรุป
ในบทความนี้ ฉันได้อธิบาย 3 วิธีการสร้าง รายการแบบหล่นลงแบบไดนามิก ใน Excel ด้วย ฟังก์ชัน OFFSET . ฉันหวังว่าคุณจะพบว่าบทความนี้มีประโยชน์ สุดท้ายนี้ หากคุณมีความคิดเห็นใด ๆ โปรดทิ้งไว้ในช่องแสดงความคิดเห็น
บทความที่เกี่ยวข้อง
- Excel สร้างรายการแบบไดนามิกจากตาราง (3 วิธีง่ายๆ)
- วิธีการสร้างรายการแบบไดนามิกใน Excel ตามเกณฑ์ (เกณฑ์เดียวและหลายเกณฑ์)
