Tag: NamedRange

หนึ่งในเทคนิคที่ผู้ใช้งาน Excel ทุกคนควรรู้ คือการตั้งชื่อให้กับ Range

…

… เดี๋ยวเราไปดูกันว่าทำไม ..

…

วิธีการตั้งชื่อ

วิธีที่ง่ายที่สุดก็คือ เลือก Cell หรือ  Range ที่เราต้องการจะตั้งชื่อ* แล้วใส่ชื่อที่ต้องการจะตั้งที่ช่อง Name Box ด้านซ้ายมือบน จากนั้นก็กด Enter

(*สามารถเป็นได้ทั้ง [1] แค่ Cell เดียว [2] Range ของหลายๆ Cells ซึ่งอยู่ติดกันหรือไม่ติดกันก็ได้)

ซึ่งหลังจากตั้งชื่อแล้ว เราก็สามารถจะกระโดดไปที่แต่ละ Named Range ได้ โดยคลิ้กที่ปุ่ม Drop Down ของ Name Box แล้วเลือกชื่อที่ต้องการ

หรือถ้าเรามีตารางของข้อมูลอยู่ แล้วอยากจะตั้งชื่ออัตโนมัติตาม Headers แล้วล่ะก็ ให้เลือกทั้งตารางก่อน แล้วไปที่ FORMULAS>Create From Selection… Excel ก็จะช่วยตั้งชื่อให้โดยอัตโมัติ เช่นตัวอย่างข้างล่างนี้ ผมเลือกตั้งชื่อตาม Top row ก็จะได้ชื่อว่า Firstname, Lastname, และ Age มาใช้ง่ายๆ เลย

---

ทีนี้เรามาลองดูประโยชน์หลักๆ ของ Named Range กันครับ

1. สร้างสูตรได้ง่ายขึ้น (และอ่านเข้าใจได้ง่าย)

เวลาพิมพ์สูตร สามารถใส่ชื่อ Named Range ของเราได้ตรงๆ โดยถ้าเริ่มพิมพ์ไปแล้ว จะมี Auto Suggest ขึ้นให้ด้วย (สามารถ Double Click หรือกด Tab เพื่อเลือกใช้ได้เลย)

หรืออีกแบบหนึ่งคือ ก่อนจะสร้างสูตร เลือก Ribbon ‘FORMULAS’ ไว้ก่อน แล้วระหว่างสร้างสูตร เราสามารถจะเรียกใช้รายชื่อได้จากปุ่ม Use in Formula

ผลที่ตามมาก็คือ สูตรของเราเข้าใจได้ง่าย เพราะเห็นได้ชัดเจนขึ้นว่ากำลังใช้ค่าอะไรมาคำนวณอยู่

2. สร้าง Data Validation โดยใช้ List ข้าม worksheet ได้

ถ้าผมจำไม่ผิด และโดยอ้างอิงจากหน้าเว็บนี้ ใน Excel เวอร์ชั่นเก่าๆ อย่าง 2003 (หรือแม้กระทั่ง 2007) หากเราต้องการใช้ Data Validation เพื่อสร้าง Drop Down List แล้วล่ะก็ เราจะใช้ Refer ข้อมูลจาก List ที่อยู่คนละ worksheet ไม่ได้ โดย Excel จะบอกว่า “You may not use references to other worksheets or workbooks for Data Validation criteria.”

แต่หากเราใช้ Named Range ก็จะสามารถแก้ปัญหาดังกล่าวได้

และถึงแม้เราจะใช้ Excel 2010 / 2013 / 2016 ที่สามารถใช้ List จากคนละ Worksheet มาใช้ใน Data Validation แล้วก็ตาม ผมก็ยังคงแนะนำให้ใช้ Named Range แทนที่จะใส่เป็น Cell Address ตรงๆ อยู่ดี เพราะถ้าเกิดคุณส่งไฟล์นั้นให้เพื่อนร่วมงานที่ใช้ Excel 2007 ไปเปิด Data Validation มันก็อาจจะพังได้ครับ

3. ลดปัญหาการอ้างอิง Range ใน VBA

บ่อยครั้งที่ใน VBA Code เราจะต้องมีการอ้างอิงถึง Range ใน Excel Sheet

ตัวอย่างเช่น

taxRate = ThisWorkbook.Sheets(“Main”).Range(“B2”).Value

taxRate = ThisWorkbook.Sheets(“Main”).Cells(2,2).Value

ซึ่งจากตัวอย่างข้างบนนี้ มันอาจจะทำงานได้ปกติในวันที่เราสร้าง Book นี้ แต่ถ้าหากวันใดวันหนึ่งมีการเพิ่ม Row หรือ Column แล้วทำให้ Cell ที่เราต้องการจะอ้างอิงถึงนั้นๆ เปลี่ยนไป เช่น จากเดิมอยู่ที่ B2 กลายเป็นไปอยู่ที่ C3.. นั่นหมายความว่า VBA เราก็ต้องได้รับการแก้ไขตามไปด้วย

แต่ถ้าใช้ Named Cell แล้ว ปัญหาประเภทนี้ก็จะหมดไปครับ เพราะไม่ว่าจเพิ่ม Column/ Row หรือแม้แต่ Cut มันไปวางที่อื่น ชื่อที่เราตั้งไว้ก็จะยังอ้างอิงไปได้ถูกที่ถูกตำแหน่งอยู่เสมอ

โดยเมื่อเราใช้ Named Range แทน Address ก็จะได้หน้าตาของ VBA Code ประมาณนี้ครับ

taxRate = ThisWorkbook.Sheets(“Main”).Range(“taxRate”).Value

หรือแบบย่อ

taxRate = [taxRate].Value

การแก้ไขหรือลบชื่อ

ทำได้โดยไปที่ FORMULAS > Name Manager หรือกด Ctrl+F3 ในการเรียกหน้าต่าง Name Manager ขึ้นมา เพื่อแก้ไขชื่อ ตำแหน่งที่ชื่อนั้นๆ อ้างอิงถึง หรือลบชื่อทิ้ง

นอกจากนี้ ยังมีปุ่ม New ให้เรายังสามารถตั้งชื่อโดยกำหนด Scope ได้อีกด้วย

กล่าวคือ ถ้า Scope ของชื่อเป็นระดับ Workbook เราก็จะสามารถใช้ชื่อนั้นๆ ได้เลยในทุกๆ Worksheet ที่อยู่ภายในไฟล์เดียวกัน**

แต่ถ้าชื่ออยู่แค่ในระดับ Worksheet ก็จะเป็นที่รู้จักแค่ใน Worksheet นั้นๆ เท่านั้น***

(**,*** ทั้งนี้ เรายังสามารถเรียกใช้ชื่อโดยระบุการอ้างอิงแบบเต็มๆ ได้อยู่ เช่นถ้า Range Name นั้นอยู่ในระดับ Workbook เราก็สามารถเข้าถึงได้ผ่านชื่อ Book และ Range Name เช่น =Book1!taxRate หากอ้างอิงจาก Book อื่น; หรือถ้าชื่ออยู่ในระดับ Worksheet ก็สามารถเข้าถึงได้ผ่านชื่อ Worksheet เช่น =Sheet2!rateSheet2 หากอ้างอิงจาก Sheet อื่น เป็นต้น)

เพิ่มเติม:

การใช้ Named Range มีกฎในการตั้งชื่ออยู่นิดหน่อย โดยผมขออนุญาตอ้างอิงจากหน้า Support ของ Microsoft Office ตามนี้เลยครับ:

• อักขระที่ถูกต้องอักขระตัวแรกจะต้องเป็นตัวอักษร ตัวอักขระขีดล่าง (_) หรือ เครื่องหมายแบคสแลช (\) อักขระที่เหลือในชื่อสามารถเป็นได้ทั้งตัวอักษร ตัวเลข จุด และตัวอักขระขีดล่าง

หมายเหตุ  คุณไม่สามารถใช้อักขระตัวพิมพ์ใหญ่และตัวพิมพ์เล็ก “C” “c” “R” หรือ “r” เป็นชื่อที่กำหนด เนื่องจากอักขระทั้งหมดนี้ใช้เป็นการอ้างอิงแบบย่อในการเลือกแถวหรือคอลัมน์สำหรับเซลล์ที่เลือกในปัจจุบันเมื่อคุณป้อนลงในกล่องข้อความ ชื่อ หรือ ไปที่

• การอ้างอิงเซลล์ที่ไม่สามารถใช้ได้ชื่อไม่สามารถซ้ำกับการอ้างอิงเซลล์ได้ เช่น Z$100 หรือ R1C1
• ไม่ใช้การเว้นวรรค    ไม่อนุญาตให้มีการเว้นวรรคในฐานะเป็นส่วนหนึ่งของชื่อ ใช้ตัวอักขระขีดล่าง (_) และจุด (.) เป็นตัวคั่นคำ เช่น Sales_Tax หรือ Quarter
• ความยาวของชื่อชื่อสามารถมีอักขระได้ถึง 255 ตัว
• ตัวพิมพ์เล็กพิมพ์ใหญ่ชื่อสามารถมีได้ทั้งตัวพิมพ์ใหญ่และเล็ก Excel จะไม่แยกแยะความแตกต่างระหว่างอักขระที่ใช้ตัวพิมพ์ใหญ่หรือเล็กในชื่อ ตัวอย่างเช่น ถ้าคุณสร้างชื่อ Sales แล้วสร้างอีกชื่อเป็น SALES ในสมุดงานเดียวกัน Excel จะพร้อมท์ให้คุณเลือกชื่อที่ไม่ซ้ำ