home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> ซอฟต์แวร์ >> Office

ใช้ Dynamic Range Names ใน Excel สำหรับ Dropdown ยืดหยุ่น

Excel มักใช้ Dropdown (รายการเลือก) เพื่อมาตรฐาน/ลดความผิดพลาดในการกรอกข้อมูล สร้างด้วย Data Validation > List

วิธีพื้นฐาน: เลือกเซลล์ > Data Validation (แท็บ Data) > List > ใส่รายการคั่นด้วยคอมม่าใน Source

ข้อจำกัด: ต้องแก้ Data Validation ทุกครั้งที่เปลี่ยนรายการ ยากสำหรับผู้ใช้ใหม่ หรือรายการยาว

วิธี Named Range: วางรายการใน Named Range แล้วอ้างอิงชื่อใน Source (ขึ้นต้นด้วย =) แก้ไขรายการง่ายขึ้น

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

โซลูชัน: ใช้ Dynamic Range Name ที่ขยาย/หดตัวตามข้อมูลจริง โดยใช้ สูตร (Formula) แทนช่วงเซลล์คงที่

วิธีสร้าง Dynamic Range ใน Excel

Named Range ธรรมดา (Static) อ้างอิงช่วงคงที่ ($H$3:$H$10)

Dynamic Range ใช้สูตร เช่น:

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

FruitsHeading = ชื่อช่องหัวตาราง (อยู่เหนือรายการ 1 แถว) ตัวเลข 20 = ขนาดสูงสุดของรายการ (แถว) ปรับได้

ในตัวอย่างมีข้อมูล 8 แถว แต่บล็อกเตรียมไว้ 20 แถว สูตรจะนับเฉพาะแถวที่มีข้อมูลจริง

แยกวิเคราะห์สูตร (ทีละส่วน):

  1. OFFSET(FruitsHeading,1,0,20,1) – อ้างอิงบล็อก 20 แถวใต้หัวตาราง (เริ่ม 1 แถวล่าง 0 คอลัมน์ สูง 20 แถว กว้าง 1 คอลัมน์)
  2. ISBLANK(...) – ตรวจสอบแต่ละเซลล์ในบล็อก 20 แถว สร้างชุด TRUE/FALSE 20 ค่า (FALSE = มีข้อมูล, TRUE = ว่าง) 8 ค่าแรก FALSE, 12 ค่าท้าย TRUE
  3. INDEX(...,0,0) – คืนค่า Array ทั้งชุด 20 TRUE/FALSE (row=0, col=0 = ทั้งบล็อก)
  4. MATCH(TRUE,...,0) – หาตำแหน่ง TRUE แรก ใน Array (แถวที่ 9 = ว่างแรก) คืนค่า 9
  5. MATCH(...)-1 – ลบ 1 ได้ 8 = จำนวนรายการจริง (แถวสุดท้ายที่มีข้อมูล)
  6. IFERROR(...,20) – ถ้าบล็อกเต็ม 20 แถว (ไม่มี TRUE) MATCH จะ Error IFERROR คืนค่า 20 แทน
  7. OFFSET(FruitsHeading,1,0,ผลลัพธ์ข้างต้น,1) – ส่งคืนช่วงที่ต้องการ: เริ่มใต้หัวตาราง สูง = จำนวนรายการจริง กว้าง 1 คอลัมน์

ใช้สูตรนี้เป็น Source ของ Dropdown: เพิ่ม/ลบรายการได้อิสระ (ต้องต่อเนื่องจากบนสุด) Dropdown จะอัปเดทอัตโนมัติ

วิธีทางเลือก: ตั้งชื่อบล็อกรายการเป็น Named Range (เช่น NameBlock) ใช้สูตรปรับเปลี่ยน:

=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NameBlock),0,0),0)-1,ROWS(NameBlock)),1)

NameBlock แทน OFFSET(...) และ ROWS(NameBlock) แทนตัวเลข 20

สรุป: Dropdown ที่แก้ไขง่าย ให้ผู้ใช้อื่นทำได้ ใช้ Dynamic Range Names! ไม่ได้ใช้แค่ Dropdown ใช้ได้ทุกที่ที่ต้องอ้างอิงช่วงข้อมูลที่เปลี่ยนขนาดได้