สเปรดชีต Excel มักจะรวมการเลื่อนลงของเซลล์เพื่อทำให้ง่ายขึ้นและ / หรือป้อนข้อมูลมาตรฐาน ดรอปดาวน์เหล่านี้สร้างขึ้นโดยใช้คุณสมบัติการตรวจสอบความถูกต้องของข้อมูลเพื่อระบุรายการของรายการที่อนุญาต
หากต้องการตั้งค่ารายการดรอปดาวน์แบบง่าย ๆ ให้เลือกเซลล์ที่จะป้อนข้อมูลจากนั้นคลิก การตรวจสอบข้อมูล(บนแท็บ ข้อมูล) เลือกการตรวจสอบความถูกต้องของข้อมูลเลือก รายการ(ภายใต้อนุญาตให้ :) จากนั้นป้อนรายการ (คั่นด้วยเครื่องหมายจุลภาค) ใน แหล่งที่มา: ฟิลด์ (ดูรูปที่ 1)
ในรายการดรอปดาวน์พื้นฐานประเภทนี้รายการของรายการที่อนุญาตจะถูกระบุ ภายในการตรวจสอบข้อมูลของตัวเอง; ดังนั้นในการเปลี่ยนแปลงรายการผู้ใช้จะต้องเปิดและแก้ไขการตรวจสอบข้อมูล อย่างไรก็ตามอาจเป็นเรื่องยากสำหรับผู้ใช้ที่ไม่มีประสบการณ์หรือในกรณีที่รายการตัวเลือกมีความยาว
ตัวเลือกอื่นคือการวางรายการใน ช่วงที่มีชื่อภายในสเปรดชีต จากนั้นระบุ ชื่อช่วงนั้น (นำหน้าด้วยเครื่องหมายเท่ากับ) ในฟิลด์ แหล่งที่มา: การตรวจสอบความถูกต้องของข้อมูล (ดังแสดงในรูปที่ 2)
วิธีที่สองนี้ทำให้การแก้ไขตัวเลือกในรายการง่ายขึ้น แต่การเพิ่มหรือลบรายการอาจเป็นปัญหาได้ เนื่องจากช่วงที่มีชื่อ (FruitChoices ในตัวอย่างของเรา) หมายถึงช่วงของเซลล์ที่กำหนดตายตัว ($ H $ 3: $ H $ 10 ตามที่แสดง) หากมีการเพิ่มตัวเลือกเพิ่มเติมลงในเซลล์ H11 หรือต่ำกว่าพวกมันจะไม่ปรากฏในรายการแบบเลื่อนลง (เนื่องจากเซลล์เหล่านั้นไม่ได้เป็นส่วนหนึ่งของช่วง FruitChoices)
เช่นกันหากรายการ Pears และ Strawberries ถูกลบพวกเขาจะไม่ปรากฏในรายการแบบเลื่อนลงอีกต่อไป แต่การเลื่อนลงจะรวมสองรายการ ตัวเลือก“ ว่าง” เนื่องจากดรอปดาวน์ยังอ้างอิงช่วง FruitChoices ทั้งหมดรวมถึงเซลล์ว่าง H9 และ H10
ด้วยเหตุผลเหล่านี้เมื่อใช้ช่วงปกติที่มีชื่อเป็นแหล่งรายการสำหรับดรอปดาวน์ช่วงที่กำหนด ต้องแก้ไขตัวเองเพื่อรวมเซลล์มากขึ้นหรือน้อยลงหากรายการถูกเพิ่มหรือลบออกจากรายการ
วิธีแก้ปัญหานี้คือการใช้ ไดนามิกชื่อช่วงเป็นแหล่งข้อมูลสำหรับตัวเลือกแบบเลื่อนลง ชื่อช่วงไดนามิกเป็นชื่อที่ขยาย (หรือสัญญา) โดยอัตโนมัติเพื่อให้ตรงกับขนาดของบล็อกของข้อมูลเมื่อมีการเพิ่มหรือลบรายการ ในการทำเช่นนี้คุณใช้ รูปแบบแทนช่วงที่อยู่ของเซลล์ที่กำหนดตายตัวเพื่อกำหนดช่วงที่มีชื่อ
วิธีตั้งค่าไดนามิก ช่วงใน Excel
ชื่อช่วงปกติ (คงที่) หมายถึงช่วงของเซลล์ที่ระบุ ($ H $ 3: $ H $ 10 ในตัวอย่างของเราดูด้านล่าง):
แต่ช่วงไดนามิกถูกกำหนดโดยใช้สูตร (ดูด้านล่างซึ่งนำมาจากสเปรดชีตแยกต่างหากซึ่งใช้ชื่อช่วงไดนามิก):
ก่อนที่เราจะเริ่มต้นตรวจสอบให้แน่ใจว่าคุณดาวน์โหลด ไฟล์ตัวอย่างของ Excel ของเรา (มาโครการเรียงลำดับถูกปิดใช้งาน)
ให้ตรวจสอบสูตรนี้โดยละเอียด ตัวเลือกสำหรับผลไม้อยู่ในบล็อกของเซลล์ด้านล่างหัวเรื่อง (ผลไม้) ส่วนหัวนั้นยังได้รับการกำหนดชื่อ: FruitsHeading
สูตรทั้งหมดที่ใช้กำหนดช่วงไดนามิกสำหรับ ตัวเลือกผลไม้คือ:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitsHeadingหมายถึงส่วนหัวที่อยู่หนึ่งแถวเหนือรายการแรกในรายการ จำนวน 20 (ใช้สองครั้งในสูตร) คือขนาดสูงสุด (จำนวนแถว) สำหรับรายการ (สามารถปรับได้ตามต้องการ)
โปรดทราบว่าในตัวอย่างนี้มีเพียง 8 รายการ ในรายการ แต่ยังมีเซลล์ว่างด้านล่างซึ่งสามารถเพิ่มรายการเพิ่มเติมได้ จำนวน 20 หมายถึงบล็อกทั้งหมดที่สามารถทำรายการได้ไม่ใช่จำนวนรายการที่แท้จริง
ตอนนี้เรามาแบ่งสูตรเป็นชิ้น ๆ (การเข้ารหัสสีแต่ละชิ้น) เพื่อทำความเข้าใจวิธีการทำงาน :
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
ชิ้นส่วน "ส่วนในสุด" คือ OFFSET (FruitsHeading, 1,0,20,1)สิ่งนี้อ้างอิงบล็อกของ 20 เซลล์ (ใต้เซลล์ FruitsHeading) ซึ่งอาจมีการป้อนตัวเลือก ฟังก์ชั่น OFFSET นี้โดยทั่วไปบอกว่า: เริ่มต้นที่เซลล์ FruitsHeadingลงไป 1 แถวและมากกว่า 0 คอลัมน์จากนั้นเลือกพื้นที่ที่มีความยาว 20 แถวและกว้าง 1 คอลัมน์ ดังนั้นนั่นจึงทำให้เรามีบล็อก 20 แถวที่มีการเลือกตัวเลือกผลไม้
ส่วนต่อไปของสูตรคือฟังก์ชั่น ISBLANK:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
ที่นี่ฟังก์ชัน OFFSET (อธิบายด้านบน) ถูกแทนที่ด้วย“ ด้านบน” (เพื่อให้อ่านง่ายขึ้น) แต่ฟังก์ชั่น ISBLANK นั้นทำงานบนช่วง 20 แถวของเซลล์ที่ฟังก์ชัน OFFSET กำหนดไว้
ISBLANK จะสร้างชุดของค่า 20 TRUE และ FALSE ซึ่งระบุว่าแต่ละเซลล์ใน 20- ช่วงแถวที่อ้างอิงโดยฟังก์ชัน OFFSET ว่างเปล่า (ว่าง) หรือไม่ ในตัวอย่างนี้ค่า 8 แรกในชุดจะเป็น FALSE เนื่องจาก 8 เซลล์แรกไม่ว่างเปล่าและ 12 ค่าสุดท้ายจะเป็น TRUE
ส่วนถัดไปของสูตรคือฟังก์ชัน INDEX:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
อีกครั้ง“ ข้างต้น” หมายถึงฟังก์ชั่น ISBLANK และ OFFSET ที่อธิบายไว้ข้างต้น ฟังก์ชัน INDEX ส่งคืนอาร์เรย์ที่มีค่า 20 TRUE / FALSE ที่สร้างขึ้นโดยฟังก์ชัน ISBLANK
INDEXโดยปกติจะใช้เพื่อเลือกค่าที่แน่นอน (หรือช่วงของค่า) จาก บล็อกของข้อมูลโดยระบุแถวและคอลัมน์ที่แน่นอน (ภายในบล็อกนั้น) แต่การตั้งค่าแถวและคอลัมน์อินพุตให้เป็นศูนย์ (ดังที่ทำไว้ที่นี่) ทำให้ INDEX ส่งคืนอาร์เรย์ที่มีบล็อกข้อมูลทั้งหมด
ส่วนถัดไปของสูตรคือฟังก์ชัน MATCH:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
ฟังก์ชั่น การแข่งขันส่งคืนตำแหน่งของค่า TRUE แรกภายในอาร์เรย์ที่ส่งคืนโดยฟังก์ชัน INDEX เนื่องจาก 8 รายการแรกในรายการไม่ว่างเปล่าค่า 8 อันดับแรกในอาร์เรย์จะเป็น FALSE และค่าที่เก้าจะเป็น TRUE (เนื่องจากแถว th9 ในช่วงนั้นว่างเปล่า)
ดังนั้นฟังก์ชัน MATCH จะส่งคืนค่าของ 9ในกรณีนี้เราต้องการทราบจำนวนรายการที่อยู่ในรายการดังนั้นสูตรจะลบ 1 จากค่า MATCH (ซึ่งให้ตำแหน่งของรายการสุดท้าย) ดังนั้นในที่สุด MATCH (TRUE, ด้านบน, 0) -1 จะส่งกลับค่าของ 8
ส่วนถัดไปของสูตรคือฟังก์ชัน IFERROR:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
ฟังก์ชัน IFERROR ส่งคืนค่าทางเลือกหากค่าแรกที่ระบุส่งผลให้เกิดข้อผิดพลาด ฟังก์ชั่นนี้รวมตั้งแต่ถ้าบล็อกทั้งหมดของเซลล์ (ทั้ง 20 แถว) เต็มไปด้วยรายการฟังก์ชั่น MATCH จะส่งกลับข้อผิดพลาด
นี่เป็นเพราะเรากำลังบอกให้ฟังก์ชั่น MATCH มองหา ค่า TRUE แรก (ในอาร์เรย์ของค่าจากฟังก์ชัน ISBLANK) แต่ถ้าไม่มีเซลล์ใดว่างเปล่าอาร์เรย์ทั้งหมดจะถูกเติมด้วยค่า FALSE หาก MATCH ไม่พบค่าเป้าหมาย (TRUE) ในอาร์เรย์ที่กำลังค้นหามันจะส่งคืนข้อผิดพลาด
ดังนั้นหากรายการทั้งหมดเต็ม (ดังนั้น MATCH จะส่งคืนข้อผิดพลาด) ฟังก์ชัน IFERROR จะ แทนที่จะส่งคืนค่า 20 (โดยรู้ว่าจะต้องมี 20 รายการในรายการ)
ในที่สุด OFFSET (FruitsHeading, 1,0, ด้านบน, 1)จะส่งคืน ช่วงที่เรากำลังมองหา: เริ่มต้นที่เซลล์ FruitsHeading ลงไป 1 แถวและมากกว่า 0 คอลัมน์จากนั้นเลือกพื้นที่ที่มีหลายแถวที่มีความยาวอยู่ในรายการ (และกว้าง 1 คอลัมน์) ดังนั้นสูตรทั้งหมดเข้าด้วยกันจะส่งคืนช่วงที่มีเฉพาะรายการจริง (ลงไปที่เซลล์ว่างแรก)
การใช้สูตรนี้เพื่อกำหนดช่วงที่เป็นแหล่งสำหรับดรอปดาวน์หมายความว่าคุณสามารถแก้ไขได้อย่างอิสระ รายการ (เพิ่มหรือลบรายการตราบใดที่รายการที่เหลือเริ่มต้นที่เซลล์ด้านบนและต่อเนื่องกัน) และรายการแบบหล่นลงจะสะท้อนถึงรายการปัจจุบันเสมอ (ดูรูปที่ 6)
ไฟล์ตัวอย่าง (รายการแบบไดนามิก) ที่ใช้ที่นี่รวมอยู่และสามารถดาวน์โหลดได้จากเว็บไซต์นี้ มาโครไม่ทำงานอย่างไรก็ตามเนื่องจาก WordPress ไม่ชอบหนังสือ Excel ที่มีมาโคร
เป็นทางเลือกหนึ่งในการระบุจำนวนแถวในบล็อกรายการจึงสามารถกำหนดบล็อกรายการได้ ชื่อช่วงของตัวเองซึ่งสามารถใช้ในสูตรที่ปรับเปลี่ยนแล้ว ในไฟล์ตัวอย่างรายการที่สอง (ชื่อ) ใช้วิธีนี้ ที่นี่บล็อกรายการทั้งหมด (ใต้ส่วนหัว“ NAMES” 40 แถวในไฟล์ตัวอย่าง) จะถูกกำหนดชื่อช่วงของ NameBlockสูตรทางเลือกสำหรับการกำหนด NamesList คือ:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
โดยที่ NamesBlockแทนที่ OFFSET (FruitsHeading, 1,0,20,1) และ ROWS (NamesBlock)แทนที่ 20 (จำนวนแถว) ในสูตรก่อนหน้า
ดังนั้นสำหรับรายการแบบเลื่อนลงซึ่งสามารถแก้ไขได้อย่างง่ายดาย (รวมถึงผู้ใช้อื่นที่ไม่มีประสบการณ์) ลองใช้ชื่อช่วงไดนามิก! และโปรดทราบว่าแม้ว่าบทความนี้จะเน้นไปที่รายการดรอปดาวน์ แต่ก็สามารถใช้ชื่อช่วงไดนามิกได้ทุกที่ที่คุณต้องการอ้างอิงช่วงหรือรายการที่อาจมีขนาดต่างกัน สนุก!