สเปรดชีต Excel มักจะรวมการเลื่อนลงของเซลล์เพื่อให้ง่ายขึ้นและ / หรือป้อนข้อมูลมาตรฐาน ดรอปดาวน์เหล่านี้สร้างขึ้นโดยใช้คุณสมบัติการตรวจสอบความถูกต้องของข้อมูลเพื่อระบุรายการของรายการที่อนุญาต
หากต้องการตั้งค่ารายการแบบเลื่อนลงแบบง่ายให้เลือกเซลล์ที่จะป้อน ข้อมูล จากนั้นคลิก การตรวจสอบความถูกต้องของข้อมูล (บนแท็บ ข้อมูล ) เลือกการตรวจสอบความถูกต้องของข้อมูลเลือก รายการ (ภายใต้อนุญาต :) และป้อนรายการในรายการ ) ในช่อง Source : (ดูรูปที่ 1)
ในดร็อปดาวน์พื้นฐานชนิดนี้รายการของรายการที่อนุญาตจะถูกระบุภายในการตรวจสอบความถูกต้องของข้อมูลเอง ดังนั้นในการเปลี่ยนแปลงรายการผู้ใช้จะต้องเปิดและแก้ไขการตรวจสอบข้อมูล อย่างไรก็ตามนี่อาจเป็นเรื่องยากสำหรับผู้ใช้ที่ไม่มีประสบการณ์หรือในกรณีที่รายการตัวเลือกมีความยาว
ตัวเลือกอื่นคือการวางรายการในช่วงที่ตั้งชื่อไว้ภายในสเปรดชีตแล้วระบุชื่อช่วง (นำหน้าด้วยเครื่องหมายเท่ากับ) ในฟิลด์ แหล่งที่มา : การตรวจสอบความถูกต้องของข้อมูล (ดังแสดงในรูปที่ 2)
วิธีที่สองนี้ทำให้การแก้ไขตัวเลือกในรายการง่ายขึ้น แต่การเพิ่มหรือลบรายการอาจเป็นปัญหาได้ เนื่องจากช่วงที่ตั้งชื่อ (FruitChoices ในตัวอย่างของเรา) หมายถึงช่วงของเซลล์ที่กำหนดตายตัว ($ H $ 3: $ H $ 10 ตามที่แสดง) หากมีการเพิ่มตัวเลือกเพิ่มเติมลงในเซลล์ H11 หรือต่ำกว่าพวกมันจะไม่ปรากฏในรายการแบบเลื่อนลง (เนื่องจากเซลล์เหล่านั้นไม่ได้เป็นส่วนหนึ่งของช่วง FruitChoices)
เช่นถ้ารายการแพร์และสตรอเบอร์รี่ถูกลบพวกเขาจะไม่ปรากฏในรายการแบบเลื่อนลงอีกต่อไป แต่แทนที่จะเป็นรายการแบบเลื่อนลงจะมีตัวเลือก "ว่างเปล่า" สองรายการเนื่องจากดรอปดาวน์ยังอ้างอิงช่วง FruitChoices ทั้งหมดรวมถึงเซลล์ว่าง H9 และ H10
ด้วยเหตุผลเหล่านี้เมื่อใช้ช่วงที่มีชื่อปกติเป็นแหล่งรายการสำหรับรายการแบบหล่นลงช่วงที่ตั้งชื่อนั้นจะต้องแก้ไขเพื่อรวมเซลล์มากหรือน้อยลงหากมีการเพิ่มหรือลบรายการจากรายการ
วิธีแก้ไขปัญหานี้คือใช้ชื่อช่วง ไดนามิก เป็นแหล่งข้อมูลสำหรับตัวเลือกแบบเลื่อนลง ชื่อช่วงไดนามิกเป็นชื่อที่ขยาย (หรือสัญญา) โดยอัตโนมัติเพื่อให้ตรงกับขนาดของบล็อกของข้อมูลเมื่อมีการเพิ่มหรือลบรายการ เมื่อต้องการทำเช่นนี้คุณใช้ สูตร แทนช่วงที่อยู่เซลล์ถาวรเพื่อกำหนดช่วงที่มีชื่อ
วิธีการตั้งค่าช่วงไดนามิกใน Excel
ชื่อช่วงปกติ (คงที่) หมายถึงช่วงของเซลล์ที่ระบุ ($ H $ 3: $ H $ 10 ในตัวอย่างของเราดูด้านล่าง):
แต่ช่วงไดนามิกถูกกำหนดโดยใช้สูตร (ดูด้านล่างซึ่งนำมาจากสเปรดชีตแยกซึ่งใช้ชื่อช่วงไดนามิก):
ก่อนที่เราจะเริ่มต้นให้แน่ใจว่าคุณดาวน์โหลดไฟล์ตัวอย่างของ Excel (มาโครการเรียงลำดับถูกปิดใช้งาน)
ลองตรวจสอบสูตรนี้อย่างละเอียด ตัวเลือกสำหรับผลไม้อยู่ในบล็อกของเซลล์ด้านล่างหัวเรื่อง ( FRUITS ) ส่วนหัวนั้นได้รับการกำหนดชื่อ: 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 (ด้านบน), 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 (ด้านบน, 0, 0), 0) -1, 20), 1)
อีกครั้ง“ ข้างต้น” หมายถึงฟังก์ชั่น ISBLANK และ OFFSET ที่อธิบายไว้ข้างต้น ฟังก์ชัน INDEX ส่งคืนอาร์เรย์ที่มีค่า 20 TRUE / FALSE ที่สร้างโดยฟังก์ชัน ISBLANK
โดยปกติจะใช้ INDEX เพื่อเลือกค่า (หรือช่วงของค่า) บางอย่างจากบล็อกข้อมูลโดยการระบุแถวและคอลัมน์ที่แน่นอน (ภายในบล็อกนั้น) แต่การตั้งค่าแถวและคอลัมน์อินพุตให้เป็นศูนย์ (ดังที่ทำไว้ที่นี่) ทำให้ INDEX ส่งคืนอาร์เรย์ที่มีบล็อกข้อมูลทั้งหมด
สูตรถัดไปคือฟังก์ชัน MATCH:
= OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (TRUE, ด้านบน, 0) -1, 20), 1)
ฟังก์ชัน MATCH ส่งคืนตำแหน่งของค่า TRUE แรกภายในอาร์เรย์ที่ส่งคืนโดยฟังก์ชัน INDEX เนื่องจาก 8 รายการแรกในรายการไม่ว่างเปล่าค่า 8 อันดับแรกในอาร์เรย์จะเป็น FALSE และค่าที่เก้าจะเป็น TRUE (เนื่องจากแถวที่ 9 ในช่วงนั้นว่างเปล่า)
ดังนั้นฟังก์ชัน MATCH จะส่งคืนค่า 9 ในกรณีนี้เราต้องการทราบจำนวนรายการที่อยู่ในรายการดังนั้นสูตรจะลบ 1 จากค่า MATCH (ซึ่งให้ตำแหน่งของรายการสุดท้าย) ดังนั้นท้ายที่สุด MATCH (TRUE, ด้านบน, 0) -1 จะส่งกลับค่า 8
สูตรต่อไปคือฟังก์ชัน IFERROR:
= OFFSET (FruitsHeading, 1, 0, IFERROR (ด้านบน, 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 (จำนวนแถว) ในสูตรก่อนหน้า
ดังนั้นสำหรับรายการแบบเลื่อนลงซึ่งสามารถแก้ไขได้ง่าย (รวมถึงผู้ใช้รายอื่นที่อาจไม่มีประสบการณ์) ลองใช้ชื่อช่วงไดนามิก! และโปรดทราบว่าแม้ว่าบทความนี้จะเน้นไปที่รายการดรอปดาวน์ แต่ชื่อช่วงไดนามิกสามารถใช้ได้ทุกที่ที่คุณต้องการอ้างอิงช่วงหรือรายการที่อาจมีขนาดต่างกัน สนุก!
