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