ใช้ชื่อช่วงไดนามิกใน 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 (จำนวนแถว) ในสูตรก่อนหน้า
ดังนั้น สำหรับรายการแบบเลื่อนลงที่สามารถแก้ไขได้ง่าย (รวมถึงผู้ใช้รายอื่นที่อาจไม่มีประสบการณ์) ให้ลองใช้ชื่อช่วงไดนามิก! และโปรดทราบว่าแม้ว่าบทความนี้จะเน้นที่รายการดรอปดาวน์ แต่ชื่อช่วงไดนามิกก็สามารถใช้ได้ทุกที่ที่คุณต้องการเพื่ออ้างอิงช่วงหรือรายการที่อาจมีขนาดแตกต่างกัน สนุก!
Related posts
วิธีการลบบรรทัดว่างใน Excel
วิธีใช้ฟีเจอร์ Speak Cells ของ Excel
วิธีแทรกแผ่นงาน Excel ลงใน Word Doc
วิธีใช้การวิเคราะห์แบบ What-If ของ Excel
วิธีแก้ไขแถวใน Excel
คู่มือ VBA ขั้นสูงสำหรับ MS Excel
2 วิธีในการใช้ฟังก์ชัน Transpose ของ Excel
4 วิธีในการใช้เครื่องหมายถูกใน Excel
Google ชีตกับ Microsoft Excel - อะไรคือความแตกต่าง?
วิธีแชร์ไฟล์ Excel เพื่อการทำงานร่วมกันอย่างง่ายดาย
การเรียงลำดับข้อมูลแบบหนึ่งคอลัมน์และหลายคอลัมน์ขั้นพื้นฐานในสเปรดชีต Excel
วิธีลบเส้นตารางใน Excel
จัดกึ่งกลางข้อมูลเวิร์กชีตของคุณใน Excel สำหรับการพิมพ์
วิธีผสานเซลล์ คอลัมน์ และแถวใน Excel
วิธีแก้ไขข้อผิดพลาด #N/A ในสูตร Excel เช่น VLOOKUP
วิธีสร้างรายการแบบหล่นลงที่เชื่อมโยงหลายรายการใน Excel
5 วิธีในการแปลงข้อความเป็นตัวเลขใน Excel
ความแตกต่างระหว่าง Microsoft Excel Online และ Excel สำหรับเดสก์ท็อป
รักษาการอ้างอิงเซลล์เมื่อคัดลอกสูตรใน Excel
วิธีใช้ Flash Fill ใน Excel