ใช้ชื่อช่วงไดนามิกใน Excel สำหรับดรอปดาวน์ที่ยืดหยุ่น

สเปรดชีต Excel(Excel)มักจะมีเมนูดรอปดาวน์ของเซลล์เพื่อทำให้การป้อนข้อมูลง่ายขึ้นและ/หรือเป็นมาตรฐาน ดรอปดาวน์เหล่านี้สร้างขึ้นโดยใช้คุณสมบัติการตรวจสอบข้อมูลเพื่อระบุรายการของรายการที่อนุญาต

ในการตั้งค่ารายการดรอปดาวน์อย่างง่าย ให้เลือกเซลล์ที่จะป้อนข้อมูล จากนั้นคลิกการตรวจสอบข้อมูล(Data Validation) (บน แท็บ ข้อมูล(Data) ) เลือกการตรวจสอบข้อมูล(Data Validation)เลือกรายการ(List) (ภายใต้อนุญาต(Allow) :) แล้วป้อนรายการ (คั่นด้วยเครื่องหมายจุลภาค) ) ใน ฟิลด์ Source : (ดูรูปที่ 1)

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

อีกทางเลือกหนึ่งคือการวางรายการในช่วงที่มีชื่อภายในสเปรดชีต(named range within the spreadsheet)จากนั้นระบุชื่อช่วงนั้น (นำหน้าด้วยเครื่องหมายเท่ากับ) ใน ฟิลด์ Source : ของการตรวจสอบข้อมูล (ดังแสดงในรูปที่ 2(Figure 2) )

วิธีที่สองนี้ช่วยให้แก้ไขตัวเลือกในรายการได้ง่ายขึ้น แต่การเพิ่มหรือลบรายการอาจเป็นปัญหาได้ เนื่องจากช่วงที่มีชื่อ ( ในตัวอย่างของเรา FruitChoices ) หมายถึงช่วงของเซลล์ที่แน่นอน ($H$3:$H$10 ตามที่แสดง) หากมีการเพิ่มตัวเลือกเพิ่มเติมในเซลล์H11หรือต่ำกว่า ตัวเลือกเหล่านั้นจะไม่แสดงในเมนูแบบเลื่อนลง (เนื่องจากเซลล์เหล่านั้นไม่ได้เป็นส่วนหนึ่งของ ช่วง FruitChoices )

ในทำนองเดียวกัน หาก รายการ ลูกแพร์(Pears)และสตรอเบอร์รี่(Strawberries)ถูกลบ รายการเหล่านั้นจะไม่ปรากฏในเมนูดรอปดาวน์อีกต่อไป แต่รายการแบบเลื่อนลงจะมีตัวเลือก "ว่าง" สองรายการแทน เนื่องจากรายการแบบเลื่อนลงยังคงอ้างอิงช่วง FruitChoices ทั้งหมด รวมถึงเซลล์ว่าง H9 และเอ(H10)ช10 .

ด้วยเหตุผลเหล่านี้ เมื่อใช้ช่วงที่มีชื่อปกติเป็นแหล่งที่มาของรายการสำหรับดรอปดาวน์ ต้องแก้ไขช่วงที่ตั้งชื่อเองเพื่อรวมเซลล์มากขึ้นหรือน้อยลงหากมีการเพิ่มหรือลบรายการออกจากรายการ

วิธีแก้ปัญหานี้คือการใช้ ชื่อช่วง ไดนามิก(dynamic)เป็นแหล่งที่มาสำหรับตัวเลือกแบบเลื่อนลง ชื่อช่วงไดนามิกคือชื่อที่จะขยาย (หรือย่อ) โดยอัตโนมัติเพื่อให้ตรงกับขนาดของกลุ่มข้อมูลเมื่อมีการเพิ่มหรือลบรายการ ในการทำเช่นนี้ คุณใช้สูตร(formula)แทนที่จะกำหนดช่วงที่อยู่ของเซลล์ตายตัว เพื่อกำหนดช่วงที่มีชื่อ

วิธีการตั้งค่าช่วงไดนามิก(Dynamic Range)ในExcel

ชื่อช่วงปกติ (คงที่) หมายถึงช่วงของเซลล์ที่ระบุ ($H$3:$H$10 ในตัวอย่างของเรา ดูด้านล่าง):

แต่ช่วงไดนามิกถูกกำหนดโดยใช้สูตร (ดูด้านล่าง นำมาจากสเปรดชีตแยกต่างหากซึ่งใช้ชื่อช่วงไดนามิก):

ก่อนที่เราจะเริ่มต้น ตรวจสอบให้แน่ใจว่าคุณดาวน์โหลดไฟล์ตัวอย่าง Excel ของเราแล้ว  (ปิดใช้งานการจัดเรียงมาโคร)

ลองดูสูตรนี้โดยละเอียด ตัวเลือกสำหรับ Fruits อยู่ในกลุ่มเซลล์ด้านล่างส่วนหัว ( FRUITS ) หัวข้อนั้นยังได้รับมอบหมายชื่อ: FruitsHeading :

สูตรทั้งหมดที่ใช้กำหนดช่วงไดนามิกสำหรับ ตัวเลือก ผลไม้(Fruits)คือ:

=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 แถวที่ป้อนตัวเลือกผลไม้(Fruits)

ส่วนต่อไปของสูตรคือ ฟังก์ชัน ISBLANK :

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)

ที่นี่ฟังก์ชัน OFFSET(OFFSET) (อธิบายไว้ด้านบน) ถูกแทนที่ด้วย "ด้านบน" (เพื่อให้อ่านง่ายขึ้น) แต่ ฟังก์ชัน ISBLANKทำงานในช่วงเซลล์ 20 แถวที่ฟังก์ชัน OFFSET(OFFSET)กำหนด

จากนั้น ISBLANK(ISBLANK)จะสร้างชุดค่าTRUEและFALSE 20 ค่า ซึ่งระบุว่าแต่ละเซลล์ในช่วง 20 แถวที่อ้างอิงโดยฟังก์ชัน OFFSET(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(INDEX)จะใช้เพื่อเลือกค่าหนึ่ง (หรือช่วงของค่า) จากบล็อกข้อมูล โดยการระบุแถวและคอลัมน์ที่ต้องการ (ภายในบล็อกนั้น) แต่การตั้งค่าอินพุตของแถวและคอลัมน์เป็นศูนย์ (ตามที่ทำที่นี่) ทำให้INDEXส่งคืนอาร์เรย์ที่มีบล็อกข้อมูลทั้งหมด

ส่วนต่อไปของสูตรคือ ฟังก์ชัน MATCH :

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)

ฟังก์ชันMATCHจะคืนค่าตำแหน่งของ ค่า TRUE แรก ภายในอาร์เรย์ที่ฟังก์ชันINDEX ส่งคืน (INDEX)เนื่องจาก 8 รายการแรกในรายการไม่เว้นว่าง 8 ค่าแรกในอาร์เรย์จะเป็นFALSEและค่าที่เก้าจะเป็นTRUE (เนื่องจากแถวที่ 9 ในช่วงว่างเปล่า)

ดังนั้น ฟังก์ชัน MATCHจะคืนค่าเป็น9 อย่างไรก็ตาม ในกรณีนี้ เราต้องการทราบจำนวนรายการในรายการ ดังนั้นสูตรจึงลบ 1 ออกจาก ค่า MATCH (ซึ่งให้ตำแหน่งของรายการสุดท้าย) ดังนั้นในที่สุดMATCH ( TRUE ,the above,0)-1 จะส่ง คืนค่า8

ส่วนต่อไปของสูตรคือฟังก์ชัน IFERROR(IFERROR) :

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

ฟังก์ชันIFERRORส่งกลับค่าทางเลือก ถ้าค่าแรกที่ระบุส่งผลให้เกิดข้อผิดพลาด ฟังก์ชันนี้รวมอยู่ด้วย เนื่องจากหากบล็อกเซลล์ทั้งหมด (ทั้ง 20 แถว) เต็มไปด้วยรายการฟังก์ชันMATCH จะส่งคืนข้อผิดพลาด(MATCH)

ทั้งนี้เนื่องจากเรากำลังบอกให้ฟังก์ชันMATCH ค้นหาค่า (MATCH)TRUE แรก (ในอาร์เรย์ของค่าจาก ฟังก์ชัน ISBLANK ) แต่ถ้าไม่มี(NONE)เซลล์ใดว่างอยู่ อาร์เรย์ทั้งหมดจะถูกเติมด้วยค่าFALSE หากMATCHไม่พบค่าเป้าหมาย ( TRUE ) ในอาร์เรย์ที่ค้นหาอยู่ จะส่งคืนข้อผิดพลาด

ดังนั้น หากรายการทั้งหมดเต็ม (และด้วยเหตุนี้MATCHจะส่งกลับข้อผิดพลาด) ฟังก์ชัน IFERROR(IFERROR)จะส่งคืนค่า 20 แทน (โดยรู้ว่าต้องมี 20 รายการในรายการ)

สุดท้ายOFFSET(FruitsHeading,1,0,the above,1)จะคืนค่าช่วงที่เรากำลังมองหาจริงๆ: เริ่มต้นที่ เซลล์ FruitsHeadingลงไป 1 แถวและมากกว่า 0 คอลัมน์ จากนั้นเลือกพื้นที่ที่มีความยาวกี่แถว มีรายการในรายการ (และกว้าง 1 คอลัมน์) ดังนั้นทั้งสูตรจะคืนค่าช่วงที่มีเฉพาะรายการจริงเท่านั้น (ลงไปที่เซลล์ว่างเซลล์แรก)

การใช้สูตรนี้เพื่อกำหนดช่วงที่เป็นแหล่งที่มาของรายการแบบเลื่อนลงหมายความว่าคุณสามารถแก้ไขรายการได้อย่างอิสระ (เพิ่มหรือลบรายการ ตราบใดที่รายการที่เหลือเริ่มต้นที่เซลล์ด้านบนและต่อเนื่องกัน) และรายการแบบเลื่อนลงจะแสดงค่าปัจจุบันเสมอ รายการ (ดูรูปที่ 6(Figure 6) )

ไฟล์ตัวอย่าง (รายการไดนามิก)ที่ใช้ในที่นี้รวมอยู่ด้วยและสามารถดาวน์โหลดได้จากเว็บไซต์นี้ อย่างไรก็ตาม มาโครไม่ทำงาน เนื่องจากWordPressไม่ชอบ หนังสือ Excelที่มีมาโครอยู่ในนั้น

แทนที่จะระบุจำนวนแถวในบล็อกรายการ บล็อกรายการสามารถกำหนดชื่อช่วงของตัวเองได้ ซึ่งสามารถนำไปใช้ในสูตรที่แก้ไขได้ ในไฟล์ตัวอย่าง รายการที่สอง ( Names ) ใช้วิธีนี้ ที่นี่ บล็อกรายการทั้งหมด (ภายใต้หัวข้อ "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 (จำนวนแถว) ในสูตรก่อนหน้า

ดังนั้น สำหรับรายการแบบเลื่อนลงที่สามารถแก้ไขได้ง่าย (รวมถึงผู้ใช้รายอื่นที่อาจไม่มีประสบการณ์) ให้ลองใช้ชื่อช่วงไดนามิก! และโปรดทราบว่าแม้ว่าบทความนี้จะเน้นที่รายการดรอปดาวน์ แต่ชื่อช่วงไดนามิกก็สามารถใช้ได้ทุกที่ที่คุณต้องการเพื่ออ้างอิงช่วงหรือรายการที่อาจมีขนาดแตกต่างกัน สนุก!



About the author

ฉันเป็นนักพัฒนาซอฟต์แวร์ฟรีแวร์และเป็นผู้ให้การสนับสนุน Windows Vista/7 ฉันได้เขียนบทความหลายร้อยบทความเกี่ยวกับหัวข้อต่างๆ ที่เกี่ยวข้องกับระบบปฏิบัติการ รวมถึงคำแนะนำและเคล็ดลับ คู่มือการซ่อม และแนวทางปฏิบัติที่ดีที่สุด ฉันยังเสนอบริการให้คำปรึกษาเกี่ยวกับสำนักงานผ่านทางบริษัท Help Desk Services ของฉัน ฉันมีความเข้าใจอย่างลึกซึ้งเกี่ยวกับวิธีการทำงานของ Office 365 ฟีเจอร์ และวิธีใช้งานอย่างมีประสิทธิภาพสูงสุด



Related posts