man using silver laptop beside another man

Named Range

/ Excel Knowledge, Excel Tutorial / By

Named Range หรือที่ชอบเรียกกันว่า Range Name คือการตั้งชื่อให้กับเซลล์ สามารถตั้งชื่อให้กับเซลล์เดี่ยวหรือกลุ่มเซลล์ กรณีตั้งชื่อให้กับกลุ่มเซลล์สามารถเลือกเซลล์ที่ไม่อยู่ติดกันได้ การตั้งชื่อให้กับเซลล์ช่วยให้เกิดความสะดวก อ้างอิงเพื่อนำไปใช้งานยังตำแหน่งต่าง ๆ ได้โดยง่าย

ขอบเขตของ Named Range
  1. ระดับ Workbook สามารถเรียกใช้จาก Worksheet ใดก็ได้
  2. ระดับ Worksheet สามารถเรียกใช้จาก Worksheet ใดก็ได้ แต่หากเรียกใช้ข้าม Worksheet จะต้องระบุชื่อ Worksheet กำกับไว้ด้านหน้าด้วย
ชนิดของ Named Range
  1. Named Range ชนิดอ่านได้อย่างเดียว ถูกสร้างอัตโนมัติโดยโปรแกรม Named Range ประเภทนี้คือ Table Name มีขอบเขตเป็นระดับ Workbook ตำแหน่ง Refers to ในกล่องโต้ตอบ Name Manager แสดงข้อความเป็นสีเทา
  2. Named Range ชนิดที่สามารถกำหนดค่าและแก้ไขเปลี่ยนแปลงค่าได้
    1. Named Range ที่สำรองไว้และสร้างโดย Program เช่น Print_Area แม้ผู้ใช้จะสร้างเข้าไปใหม่แต่จะถูกแทนที่โดยโปรแกรมเมื่อมีการกำหนด Print_Area ใหม่
    2. Named Range ที่สร้างโดยผู้ใช้
วิธีการกำหนด Named Range
  1. กำหนดผ่าน Name Box จะได้ Named Range ที่มีขอบเขตเป็นระดับ Workbook วิธีการสร้าง Named Range คือ
    1. เลือกเซลล์ที่ต้องการ
    2. คีย์ชื่อที่ช่อง Name Box
    3. Enter
  2. กำหนดผ่านเมนู Formulas
    1. เลือก Name Manager จะเปิดกล่องโต้ตอบ Name Manager
      1. คลิกปุ่ม New โปรแกรมจะเปิดกล่องโต้ตอบ New Name
      2. ที่ช่อง Name: ระบุชื่อที่ต้องการ
      3. ที่ Scope: เลือกขอบเขตของ Named Range
      4. ที่ Comment: ระบุคำอธิบายของ Named Range
      5. ที่ Refers to: เลือกตำแหน่งเซลล์หรือเขียนสูตรเพื่อระบุตำแหน่งเซลล์
      6. คลิกปุ่ม OK
    2. เลือก Define Name จะเปิดกล่องโต้ตอบ New Name
      1. ที่ช่อง Name: ระบุชื่อที่ต้องการ
      2. ที่ Scope: เลือกขอบเขตของ Named Range
      3. ที่ Comment: ระบุคำอธิบายของ Named Range
      4. ที่ Refers to: เลือกตำแหน่งเซลล์หรือเขียนสูตรเพื่อระบุตำแหน่งเซลล์
      5. คลิกปุ่ม OK
    3. คลุมตำแหน่งเซลล์ที่ต้องการสร้าง Named Range แล้วเลือก Create from Selection จะมีกล่องโต้ตอบให้เลือก 4 ตัวเลือกคือ
      1. Top row หมายถึงสร้าง Named Range ตามบรรทัดบนสุดของตำแหน่งที่เลือก ถ้าเลือกหลายคอลัมน์ บรรทัดบนสุดคือ Named Range ของแต่ละคอลัมน์
      2. Left column หมายถึงสร้าง Named Range ตามคอลัมน์แรกสุดของตำแหน่งที่เลือก ถ้าเลือกหลายบรรทัด คอลัมน์แรกสุดคือ Named Range ของแต่ละบรรทัด
      3. Bottom row หมายถึงสร้าง Named Range ตามบรรทัดล่างสุดของตำแหน่งที่เลือก ถ้าเลือกหลายคอลัมน์ บรรทัดล่างสุดคือ Named Range ของแต่ละคอลัมน์
      4. Right column หมายถึงสร้าง Named Range ตามคอลัมน์ขวาสุดของตำแหน่งที่เลือก ถ้าเลือกหลายบรรทัด คอลัมน์ขวาสุดคือ Named Range ของแต่ละบรรทัด
ข้อกำหนดของการตั้งชื่อ Named Range
  1. ต้องไม่ขึ้นต้นด้วยตัวเลข
  2. ต้องไปตรงกับตำแหน่งเซลล์ เช่น AA1, Z22 เป็นต้น
  3. ต้องไม่มีการเว้นวรรค
  4. ต้องไม่ประกอบไปด้วยอักขระพิเศษเช่น (, ), {, }, [, ], !, -, |, @
วิธีการนำ Named Range ไปใช้งาน
  1. การนำ Named Range มาใช้เป็น Argument ของ Function ที่กำลังเขียนแทนการเลือกเอง สามารถทำได้โดย
    1. กรณีจำ Named Range ได้
      1. สามารถคีย์ Named Range ลงไปตรง ๆ หากอักขระนำใดตรงกับ Named Range จะมี List มาให้เลือก
      2. กดแป้น F5 > เลือก Named Range (ถ้ามี) หรือคีย์ Named Range ที่ช่อง Reference > Enter
    2. หากจำ Named Range ไม่ได้สามารถทำอย่างใดอย่างหนึ่งดังนี้
      1. กดแป้น F3 เพื่อเลือก Named Range ที่จะนำมาใช้
      2. เข้าเมนู Formulas > Use in Formula > เลือก Named Range ที่จะนำมาใช้
  2. การนำ Named Range ที่เพิ่งสร้างทีหลังไปใช้ในสูตรที่เขียนเอาไว้ก่อนแล้ว โดยสูตรนั้นเป็นพื้นที่เดียวกันกับ Named Range ที่เพิ่งกำหนดขึ้นมาทีหลัง
    1. เข้าเมนู Formulas
    2. Define Name
    3. Apply Names
    4. เลือกรายการที่ต้องการนำไปใช้
    5. OK
  3. การนำ Named Range ไปใช้ใน Validation
    1. เข้าเมนู Data > Data Validation
    2. ตรง Allow เลือก List > คลิกในช่อง Source สามารถเลือกทำข้อใดข้อหนึ่งดังนี้
      1. คีย์เครื่องหมาย = แล้วตามด้วย Named Range
      2. กดแป้น F3 เพื่อเลือก Named Range ที่จะนำมาใช้
  4. การนำ Named Range ไปใช้เป็น Source ของ Pivot Table
    1. คลิกเซลล์ใด ๆ ในพื้นที่ที่จะนำข้อมูลไปสร้าง PivotTable
    2. เข้าเมนู Insert
    3. คลิก PivotTable
    4. ระบุ Named Range ตรง Table/Range

  5. การนำ Named Range ไปใช้เป็น Parameter ของ Power Query เนื่องจากเกิน Scope ของเนื้อหาส่วนนี้จึงยังไม่กล่าวถึงในรายละเอียด
  6. การนำ Named Range ไปใช้ใน VBA สามารถกำหนด Name Range แทนกำหนดตำแหน่งเซลล์ เช่น Range("Amt").ClearConTents เพื่อ Clear ค่าใน Named Range ที่ชื่อ Amt เป็นต้น
การกำหนด Named Range ให้ Dynamic
  1. ปรับขอบเขตตามปริมาณข้อมูล
    1. การใช้สูตรปรับขอบเขตของ Named Range ตามปริมาณข้อมูลใด ๆ เช่นกำหนดสูตรให้กับ PdName เป็น
      =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)
      หมายถึงให้มีขอบเขตเริ่มที่ B2 ไปจนถึงบรรทัดสุดท้ายที่มีข้อมูล
    2. กำหนด Named Range ให้กับช่วงเซลล์บน Table โดยการเลือกช่วงเซลล์บน Table แล้วกำหนด Named Range ที่ Name Box แล้ว Enter เพื่อสร้าง Named Range ที่ขยายขอบเขตตามปริมาณข้อมูลโดยอัตโนมัติ

  2. ปรับตำแหน่งตามเงื่อนไข เช่นกำหนดสูตรให้กับ FirstBlank เป็น
    =OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B),0)
    หมายถึงให้ FirstBlank คือตำแหน่งว่างแรกถัดจากข้อมูลรายการสุดท้ายในคอลัมน์ B
การเข้าถึง Named Range
  1. เข้าเมนู Formulas > Name Manager
    1. เลือก Named Range
    2. คลิกที่ช่อง Refers to:
  2. เข้าเมนู กดแป้น F5 > เลือกรายการที่แสดงหรือคีย์ Named Range ที่ต้องการในช่อง Reference > Enter
  3. เลือกจาก Name Box
    1. กรณีเป็น Dynamic Named Range และจำ Range Name ได้ ให้คีย์ Named Range แล้ว Enter
    2. กรณีเป็น Fixed Named Range สามารถเลือกจาก Name Box ได้โดยตรง
การแสดงรายการ Named Range ลงใน Worksheet
  1. คลิกเซลล์ใด ๆ ที่ต้องการวาง Named Range
  2. กดแป้น F3
  3. เลือก Paste List
ข้อที่ควรทราบเมื่อใช้ Named Range
  1. โปรแกรม ERP บางค่ายเช่น SAP จะสร้าง Named Range ติดมากับตัวข้อมูลด้วย หากนำชีตจากไฟล์ที่ Export จากระบบ ERP มาใช้ จำเป็นต้องตรวจสอบ Named Range ที่ติดมาด้วยเสมอ
  2. การนำ Named Range มาใช้ข้ามไฟล์หากไม่กำหนดค่าไว้เป็นอย่างอื่นโปรแกรมจะขึ้นกล่องโต้ตอบสอบถามถึงการ Update ข้อมูลเพื่อให้เป็นปัจจุบันทุกครั้งที่เปิดไฟล์
  3. Named Range ที่มีอยู่ในกล่องโต้ตอบ Name Manager ไม่จำเป็นต้องสัมพันธ์กับไฟล์ที่มีการเชื่อมโยงกัน เนื่องจาก Named Range สามารถสร้างได้อิสระ และเป็นไปได้มากที่มีการ Copy ชีตจากไฟล์อื่นมาใช้และติด Named Range ที่ไม่จำเป็นมาด้วย
  4. จำเป็นต้องตรวจตราและลบ Named Range ที่ไม่ได้ใช้งานทิ้งไปเสมอ โดย
    1. เข้าเมนู Formulas
    2. Name Manager
    3. คลิกปุ่ม Filter เพื่อเลือกรายการที่อาจจะเป็นปัญหาเช่น Name with Errors
  5. การลบ Named Range ให้คลิกรายการใด ๆ แล้วคลิกปุ่ม Delete
  6. การเลือกรายการนอกเหนือจากการใช้ปุ่ม Filter
    1. การเลือกรายการติดกันสามารถกดแป้น Shift ช่วยในการเลือกโดยและคลิกรายการแรกและกดแป้น Shift ค้างไว้และคลิกรายการสุดท้าย
    2. กรณีเลือกรายการไม่ติดกัดให้กดแป้น Ctrl ค้างไว้แล้วคลิกรายการที่ต้องการ
    3. กรณีต้องการเลือกทั้งหมดคลิกเลือกรายการใด ๆ แล้วกดแป้น Ctrl+A
  7. การมี Named Range จำนวนมากโดยไม่ขจัด Named Range ที่มีปัญหาทิ้งไปจะทำให้ไฟล์คำนวณนาน ทำงานช้า
  8. Named Range ที่แสดงในกล่องโต้ตอบ Name Manager อาจจะเป็นแค่บางส่วนของรายการทั้งหมดเนื่องจาก Named Range สามารถกำหนดให้ซ่อนเอาไว้ได้ การยกเลิกการซ่อนจะต้องใช้คำสั่งของ VBA
ข้อด้อยของการใช้ Named Range ในทางปฏิบัติ
  1. การกำหนด Named Range ทุกครั้งก่อนการใช้งานเป็นการสิ้นเปลืองเวลา
  2. กรณีไม่ได้เป็นผู้สร้าง Named Range ด้วยตนเองจะเสียเวลาในการติดตามแก้ไขกรณีเงื่อนไขการใช้งานเปลี่ยนไปจากเดิม
  3. สูตรที่ใช้ Named Range ไม่สามารถทราบขอบเขตได้ทันทีจากสิ่งที่มองเห็นในเซลล์หรือจาก Formula Bar ว่าได้ครอบคลุมจำนวนข้อมูลที่มีแล้วหรือไม่ แตกต่างจากการอ้างอิงเซลล์หรือช่วงเซลล์ในสูตรโดยตรงซึ่งจะแสดงขอบเขตข้อมูลให้เห็นเด่นชัดและตรงไปตรงมา
  4. การเขียน Named Range โดยอ้างอิงจาก Named Range อื่นจะยิ่งทำให้เกิดความยุ่งยากในการติดตามแก้ไขปัญหา

กรณีมีปัญหาการใช้งาน Microsoft Excel and VBA สามารถสอบถามได้ที่ Excel Forum

Scroll to Top