Named Range: ตั้งชื่อให้ใช้งานง่าย

Named Range หรือ การตั้งชื่อช่วงข้อมูล เป็นการอ้างอิงขั้นพื้นฐานอย่างหนึ่ง ซึ่ง หลายคนมองข้ามไป ดังนั้นจึงอยากนำเรื่องนี้มาเขียนถึงอีกครั้ง โดยเฉพาะเรื่องการนำไปใช้งานจริง เผื่อว่าจะมีประโยชน์กับท่านทั้งหลายบ้าง

Named Range คืออะไร?

คำถามแรก รู้จักการตั้งชื่อช่วงหรือเปล่า? ถ้ายังไม่รู้จักมาก่อน แนะนำให้กลับไปอ่านเรื่องการอ้างอิงตำแหน่งเซลล์

กล่าวโดยทั่วไป “ชื่อ” ก็คือตัวแทนอะไร อย่างใดอย่างหนึ่ง เช่น DataRevol.com คือชื่อเว็บไซต์หนึ่ง ธนาธร เป็นชื่อคน หมา เป็นสัตว์ชนิดหนึ่ง แดง เป็นสีหนึ่ง

เราใช้ชื่อ แสดงการอ้างอิงกันตลอดเวลา เมื่อเราบอกว่า เราอยู่ “กรุงเทพฯ” มันก็สร้างภาพในใจคนฟังที่รู้จักว่า กรุงเทพฯ อยู่ตรงไหนของประเทศไทยได้ว่าตำแหน่งแห่งหนโดยรวม ๆ แล้วอยู่ตรงไหน แต่สำหรับคนไม่รู้จัก กรุงเทพฯ มันก็เป็นชื่อที่ปราศจากความหมาย แต่รู้ว่ามีที่แห่งหนึ่งเรื่องว่า กรุงเทพฯ แต่มันอยู่ตรงไหนประเทศอะไรล้วนแต่ไม่รู้ทั้งสิ้น จนกระทั่งเราบอกว่า เราอยู่ที่ Bangkok หรือ บางกอก อาจจะมีคนรู้จักก็ได้ หรืออาจจะต้องบอกเป็นตำแหน่ง Coordinates: 13°45′09″N 100°29′39″E เพื่อระบุให้ชัดเจน แบบนี้เป็นต้น

นั่นคือ สิ่งสิ่งหนึ่ง อาจจะมีชื่อเรียกได้หลายชื่อ ที่หมายถึงสิ่งเดียวกันนั่นแหละ

ทำไมควรใช้ Named Range

เคยอ่านสูตรการใช้ฟังก์ชันยาว ๆ แล้วงง ๆ มึน ๆ บ้างมั้ย? จะดีกว่ามั้ย ถ้าเราสร้างชื่อแทนช่วงยาว ๆ เพื่อให้เข้าใจง่าย ๆ เช่น สมมติตัวอย่างจากตาราง เราต้องการหาผลรวมยอดจำหน่ายทั้งหมดของแต่ละ Product_ID ซึ่งเราจะใช้ฟังก์ชัน SumIf เป็นตัวช่วย

เราเขียนสูตรได้อย่างนี้

=SUMIF($B$2:$B$151, Y2, $O$2:$O$151)

นั่นคือ หาผลรวมจาก $O$2:$O$151 (คอลัมน์ O คือ ยอดจำหน่าย) ตาม Product_ID (คือ คอลัมน์ B ที่เราจะคัดกรองตามที่กรอกลงในช่อง Y2

สูตรนี้ไม่ยาก

=SUMIF($B$2:$B$151, Y2, $O$2:$O$150)

แต่ว่า ถ้าเราตั้งชื่อเอาไว้ เราก็อาจจะเขียนว่า

=SUMIF(Product_ID,Y2,sale)

โดย Product_ID แทนชื่อช่วงในคอลัมน์ B ซึ่งเป็นรหัสสินค้า และ sale คือชื่อช่วงของการจำหน่าย ตรงนี้คงต้องอยู่ที่เลือกแล้วล่ะว่า ชอบแบบไหน? โดยส่วนตัว จะตั้งชื่อเอาไว้ (และต้องเป็นชื่อที่สื่อความหมายด้วย ไม่ใช่ตั้งชื่อว่า AAA BBB xyz อะไรแบบนั้นไม่มีประโยชน์

ข้อสังเกต จะเห็นว่าในการเขียนสูตร ยังมีที่อ้างอิงเป็น Cell คือ Y2 อยู่ ความจริงตรงนี้จะทำเป็นชื่อก็ได้เช่นกัน เช่น ตั้งชื่อว่า item และใส่ refer to เป็น Sales!$Y$2 คือถ้าเราเลือก cell โดยการคลิก มันจะใส่ $ กำกับให้โดยอัตโนมัติ ก็เพื่ออ้างอิงตำแหน่งแบบ Absolute คือช่วงที่เฉพาะเจาะจง แต่ถ้าเราอยากจะกำหนดเป็น Relative ก็ได้เช่นกัน สมมติ ใส่เป็น item เป็น Sales!Y2 ถ้าเราคลิกลากสูตร จาก Y2 จะเปลี่ยนไปตามทิศทางที่เราลาก

การสร้างสูตรในชีตอื่น หรือแม้แต่ เวิร์กบุ๊กอื่น เราแค่ใช้ชื่อที่อ้างอิงที่ทำไว้นี่แหละ หรือ ในไฟล์อื่น ถ้าเราสร้างชื่อแบบเดียวกันไว้ แค่ copy สูตรมาก็ใช้งานได้เหมือนกัน

ถ้าใช้ dynamic named ranges (ซึ่งจะเขียนถึงอีกครั้งหนึ่งในตอนต่อไป) เราสามารถทำการคำนวณอัตโนมัตได้ง่ายมาก โดยไม่ต้องปรับปรุงช่วงข้อมูลทีละครั้งเลย

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

สำหรับ Named Range นี้ เราสามารถตั้งชื่อได้ทั้ง cell เดี่ยว ๆ หรือจะหลายคอลัมน์ก็ได้ โดย การตั้งชื่อ หรือ Defined Name นี้ มีอยู่ 3 หนทางง่าย ๆ คือ

1. ใช้ Name Box
2. กด Define Name
3. Name Manager

ใช้ Name Box

วิธีนี้ง่ายที่สุด คือพิมพ์ไปที่ Name Box ตรง ๆ เลย ว่าแต่รู้จัก Name Box มั้ยจ๊ะ? ลองกลับไปอ่าน Excel Workspace ประกอบก็ได้นะ

1. เลือก cell หรือ ช่วงข้อมูล ที่ต้องการจะตั้งชื่อ
2. พิมพ์ชื่อที่ต้องการใน Name Box
3. กด Enter

เสร็จแล้วจ้า

ใช้ Define Name

1. เลือก Cell หรือ ช่วง Cell ที่ต้องการ
2. มีสองทางเลือก ว่าจะเปิดหน้าต่างเพื่อ Named Range จากไหน (ผลลัพธ์เดียวกัน)
   • คลิกขวา แล้วเลือก Define Name…
   • ไปที่แท็บ formular คลิก Define Name
3. ตั้งชื่อ ซึ่งจะมีอยู่ 3 ช่องที่แก้ไขได้
   • ช่อง New Name ตั้งชื่อที่ต้องการ ที่สื่อความหมายหน่อยนะ
   • ใน Scope ค่ามาตรฐานที่เลือกมาให้คือ Workbook ไม่จำเป็นต้องแก้ไข เพราะเราจะเอาไปใช้ทั้ง workbook แต่ถ้าท่านจะเลือกใช้งานเฉพาะชีต ก็เลือกจาก dropdown ได้ แต่ส่วนใหญ่ (เกือบทั้งหมด) จะเลือก workbook เพราะจะได้ใช้งานได้ทั้ง Workbook
   • Rofers to ช่องนี้ ปกติ ถ้าเราเลือก Cell หรือ ช่วงข้อมูลก่อนกด define name มันจะขึ้นตามที่เราเลือกให้โดยอัตโนมัติ แต่ถ้าต้องการแก้ไขปรับแต่งก็สามารถทำได้เลย
4. กด OK เสร็จแล้วจ้า

ใช้ Name Manager

อันนี้ต้องบอกก่อนว่า ปกติ จะใช้ Name Manager ในการแก้ไขปรับปรุง “ชื่อช่วงข้อมูล” ที่ได้สร้างขึ้นแล้ว เพราะการตั้งชื่อโดยสองวิธีแรกมันง่ายกว่า (โดยส่วนตัว จะใช้วิธีที่ 2 Define Name) อย่างไรก็ตาม ในเมื่อใช้ Name Manager ในการตั้งชื่อได้ ก็เลยต้องอธิบายให้ครบถ้วน (เท่าที่จะนึกได้)

1. ไปที่แท็บ formulas ดูที่ Defined Names กดเลือก Name Manager (คีย์บอร์ดช็อคคัตคือ Ctrl + F3)
2. ในหน้าต่าง Name Manager คลิกปุ่ม New
3. ตั้งชื่อ ซึ่งจะมีอยู่ 3 ช่องที่แก้ไขได้ (ย้อนกลับไปดูข้อ 2 Define Name) นะ

กฎการตั้งชื่อ

สำหรับการตั้งชื่อ ก็มีข้อระวัง/ข้อจำกัด ดังนี้

• เริ่มต้นด้วยตัวอักษร underscore (_) หรือ backslash (\) เท่านั้น ถ้าขึ้นต้นด้วยอย่างอื่นนอกเหนือจากนี้ จะถือว่าเป็น error
• จำนวนอักขระสูงสุดไม่เกิน 255 อักขระ
• ห้ามมีช่องว่าง (space)
• ห้ามใช้สัญลักษณ์หรืออักขระพิเศษ เช่น # : ; ฯลฯ
• ตัวพิมพ์เล็ก พิมพ์ใหญ่ ถือเป็นตัวเดียวกัน เช่น data ก็คือ Data หรือ DATA ถือเป็นชื่อเดียวกัน
• ใช้ตำแหน่งของเซลล์เป็นชื่อไม่ได้ เช่น B1 Z123

จะตั้งชื่อด้วยตัวอักษรเพียงตัวเดียวก็ได้ ยกเว้นตัวอักขระที่สงวนไว้ 4 ตัว คือ r R c และ C เพราะคำเหล่านี้ใช้เพื่อเลือก แถว และ คอลัมน์ เวลาที่พิมพ์ลงใน NameBox

ตั้งชื่อแต่ละคอลัมน์ (หรือแถว) ตามชื่อคอลัมน์ (หรือแถว) โดยอัตโนมัติ

เชื่อว่า เวลาทำงานจริง หลายคนคงพบว่า ตัวเองทำงานกับตารางข้อมูลที่มีหลายคอลัมน์ ซึ่งจะมีหัวชื่อคอลัมน์กำกับ และอยากจะตั้งชื่อแต่ละคอลัมน์ ตามชื่อหัวคอลัมน์ที่ตั้งไว้

Excel มีตัวช่วย เรียกว่า Create from Selection อยู่ในแท็บ Formulas ในกลุ่ม Defined Names เวลาเราจะเลือกข้อมูลที่เป็น “ตาราง” เราสามารถตั้งชื่อแต่ละคอลัมน์ หรือ แถว ตามชื่อที่ตั้งไว้ได้โดยอัตโนมัติ ลองทำตามขั้นตอนตามนี้

1. เลือกตารางทั้งหมดที่ต้องการ
2. ไปที่แท็บ Define Names แล้วคลิก Create from Selection หรือ ใช้คีย์บอร์ดช็อตคัต Ctrl + Shift + F3
3. เมื่อเลือกตารางทั้งหมดแล้ว ให้คลิก Create from Selection จะมีหน้าต่างขึ้นมา มีตัวเลือกว่า จะให้เลือก (พร้อมชื่อ) จากไหน 4 ทิศทาง คือ Top Row Left Column Bottom Row Right Column

Name Manager

Name Manager เป็นเครื่องมือที่ทำงานเหมือนชื่อ คือ จัดการทุกอย่างเกี่ยวกับ “ชื่อ” หรือ Named Range ทั้งเรื่องการเปลี่ยนชื่อภายหลัง การจัดการแก้ไขปรับปรุงช่วง การสร้าง การลบ ฯลฯ

เราเรียก Name Manager มาใช้งานโดยไปที่แท็บ formulas ดูที่ Defined Names กดเลือก Name Manager หรือกด Ctrl+F3 ก็ได้ เมื่อเรียกขึ้นมาแล้ว จะเห็นปุ่มด้านบน 4 ปุ่ม คือ New สำหรับสร้างชื่อใหม่ Edit สำหรับแก้ไขปรับปรุง Delete สำหรับลบทิ้ง และ Filter สำหรับกรอง

ด้านล่าง ถ้ามีการสร้างชื่อเอาไว้แล้ว จะมีรายชื่อทั้งหมดขึ้นมา ซึ่งถ้าทำเอาไว้เยอะ ใช้ filter ในการคัดกรองก่อนก็ได้ สำหรับ New ได้เขียนถึงข้างบนไปแล้ว คงไม่ต้องเขียนซ้ำอีก

TIP: ถ้าต้องการลิสต์รายชื่อทั้งหมด พร้อมช่วงข้อมูล ลงในชีต (อาจจะเอาไว้อ้างอิง หรือเอาไว้ตรวจทาน)

1. ให้คลิก ที่ cell ที่ต้องการแสดงรายชื่อทั้งหมด
2. ไปที่ Formulas tab > Define Names คลิกที่ Use in Formulas แล้วคลิก Paste Names (หรือกด F3 ก็ได้)
3. จะมีหน้าต่าง Paste Names ขึ้นมา พร้อมรายชื่อให้ดู กด Paste List รายชื่อและช่วงข้อมูลของแต่ละรายชื่อ จะมาปรากฏใน Cell เรียงตามลำดับ

Edit

ในกรณีที่เราต้องการแก้ไข “ชื่อช่วง” ไม่ว่าจะเป็นการแก้ไขชื่อ แก้ไข ช่วงข้อมูล สิ่งที่เราต้องทำคือคลิกที่ชื่อที่ต้องการแก้ไข แล้วกด Edit จะขึ้นหน้าต่าง Edit Name ขึ้นมา ซึ่งเราสามารถแก้ไขได้ทั้ง Name Scope และ Refer to…

สำหรับการแก้ไข “ช่วง” เราอาจจะคลิกเลือกที่ชื่อ แล้ว แก้ไขที่ช่อง Refer to… ใน Name Manager ได้เลย เมื่อคลิก Close จะมีหน้าต่างถามว่าจะ save หรือไม่ กด ok ไปเลยจ้า

Delete

อันนี้ไม่มีอะไรมาก ต้องการลบชื่อไหนทิ้ง ก็คลิกที่ชื่อนั้น แล้วกด Delete ได้เลย ถ้าต้องการเลือกหลายชื่อ กด Ctrl ร่วมตอนคลิกชื่อ หรือกด Shift ถ้าเลือกต่อเนื่องกันหลายชื่อ เช่น ถ้าต้องการลบทิ้งทั้งหมด คลิกที่ชื่อแรกสุด แล้วกด Shift และคลิกชื่อท้ายสุด กดปุ่ม delete เท่านี้เอง

Filter

สำหรับการ filter หรือการกรอง ใช้เวลาที่เรามีชื่อเป็นจำนวนมาก เราจะใช้ filter ช่วยในการคัดเอาเฉพาะที่เกี่ยวข้องขึ้นมา ซึ่งพอคลิก filter จะขึ้นตัวเลือกว่า

• Names scoped to worksheet
• Names scoped to workbook
• Names with errors
• Names without errors
• Defined names
• Table names

โดยส่วนตัว มักจะใช้ Name with errors เป็นวิธีเลือกดูว่าชื่อไหนที่การอ้างอิงมีปัญหา หรือ เปลี่ยนแปลงต้นทางของช่วงไป ซึ่งเราอาจจะลบทิ้งไปเมื่อไม่จำเป็น

ถ้าเขียนสูตรไว้แล้ว อยากจะตั้งชื่อ

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

1. เลือก Cell ที่มีสูตรนั้นอยู่
2. ไปที่ Formulas tab > Define Names คลิก Define Name > Apply Names…
3. ในหน้าต่าง Apply Names คลิกที่ชื่อที่ต้องการตั้ง ซึ่งปกติ จะใช้ชื่อคอลัมน์ หรือ แถว ในการตั้งชื่อให้อัตโนมัติ จะมีช่องให้เลือก ว่าจะ Ignore Relative/Absolute ตรงนี้ติ๊กไว้เพื่อปรับให้เป็นการเฉพาะเจาะจง ว่าจะให้เป็นประเภทเดียวกัน (คือ Relative หรือ Absolute) และช่อง Use row and column names ตรงนี้คือ จะใช้ชื่อหัวคอลัมน์ หรือ แถว มาเป็นชื่อ และยังมี Options ให้เลือกรายละเอียดด้วย

Refer To… ไม่ได้มีไว้แค่อ้างอิงตำแหน่ง

ปกติ เราจะใช้การตั้งชื่อ เพื่ออ้างอิงตำแหน่งกันใช่มั้ย? แต่ความจริง Named Range ทำได้มากกว่านั้น อย่างเช่น เราอาจใช้ Name Manager ในการสร้าง “ค่าคงที่” ก็ได้ เช่นเราตั้งชื่อ “disco” โดยกำหนด ตรง Rofers to เป็น  =0.543 เวลาที่เขียนสูตรแล้วต้องอ้างอิงตัวเลข 0.543 ก็ใช้ชื่อ disco แทนได้เลย

และแน่นอน ถ้ามันสามารถใช้แทน “ค่าคงที่” ได้ ก็หมายความว่า เราสามารถใช้เป็นสูตรได้เช่นกัน ตัวอย่างเช่น เราจะใช้สูตร SumIF อย่างเดียวกับตัวอย่างแรก เราเก็บสูตรไว้ที่ “ชื่อ” ไปเลยก็ได้ เช่น เราตั้งชื่อว่า SumPro ตรง Refer To เราจะใส่เป็นสูตร

=SUMIF(Sales!B:B, Sales!Y2, Sales!O:O)

เราก็ไม่ต้องเขียนสูตรอีก ต่อไปเราแค่เขียนว่า

=SumPro

เท่านี้ก็มีค่าเท่ากับเขียนสูตรแล้ว ถ้าเป็นการเขียนสูตรซ้อนสูตร ทำแบบนี้ก็สะดวกดีเหมือนกัน

อ้างอิงจาก https://support.microsoft.com/en-us/office/define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64