ช่วงที่มีชื่อเป็นคุณลักษณะที่มีประโยชน์ แต่มักใช้งานไม่ได้ของMicrosoft Excel ^{(Microsoft Excel)}ช่วงที่มีชื่อสามารถทำให้สูตรเข้าใจง่ายขึ้น (และแก้ไขข้อบกพร่อง) ลดความซับซ้อนในการสร้างสเปรดชีตที่ซับซ้อน และทำให้มาโครของคุณง่ายขึ้น

ช่วงที่มีชื่อเป็นเพียงช่วง (ทั้งเซลล์เดียวหรือช่วงของเซลล์) ที่คุณกำหนดชื่อ จากนั้น คุณสามารถใช้ชื่อนั้นแทนการอ้างอิงเซลล์ปกติในสูตร ในมาโคร และสำหรับการกำหนดแหล่งที่มาสำหรับกราฟหรือการตรวจสอบข้อมูล

การใช้ชื่อช่วง เช่นTaxRateแทนการอ้างอิงเซลล์มาตรฐาน เช่นSheet2 !$C$11 จะทำให้สเปรดชีตเข้าใจง่ายขึ้นและแก้ไขข้อบกพร่อง/ตรวจสอบ

การใช้ Named Ranges ใน Excel

ตัวอย่างเช่น ลองดูที่แบบฟอร์มการสั่งซื้ออย่างง่าย  ไฟล์ของเราประกอบด้วยแบบฟอร์มคำสั่งซื้อที่สามารถกรอกได้ โดยมีเมนูดร็อปดาวน์เพื่อเลือกวิธีการจัดส่ง บวกกับแผ่นงานที่สองพร้อมตารางค่าจัดส่งและอัตราภาษี

เวอร์ชัน 1 (ไม่มีช่วงที่มีชื่อ) ใช้ การอ้างอิงเซลล์ แบบ A1^(A1-style) ปกติ ในสูตร (แสดงในแถบสูตรด้านล่าง)

เวอร์ชัน 2 ใช้ช่วงที่มีชื่อ ทำให้เข้าใจสูตรได้ง่ายขึ้นมาก ช่วงที่มีชื่อยังช่วยให้ป้อนสูตรได้ง่ายขึ้น เนื่องจากExcelจะแสดงรายการชื่อ รวมทั้งชื่อฟังก์ชัน ซึ่งคุณสามารถเลือกได้ทุกครั้งที่คุณเริ่มพิมพ์ชื่อในสูตร  คลิกสองครั้ง^{(Double-click)}ที่ชื่อในรายการหยิบเพื่อเพิ่มลงในสูตรของคุณ

การเปิด หน้าต่าง Name Managerจาก แท็บ Formulasจะแสดงรายการชื่อช่วงและช่วงเซลล์ที่อ้างอิง

แต่ช่วงที่มีชื่อมีประโยชน์อื่นๆ ด้วย ในไฟล์ตัวอย่างของเรา วิธีการจัดส่งจะถูกเลือกโดยใช้ดรอปดาวน์ (การตรวจสอบความถูกต้องของข้อมูล) ในเซลล์B13บนSheet1 จากนั้นใช้วิธีการที่เลือกเพื่อค้นหาค่าจัดส่งในSheet2

หากไม่มีช่วงที่มีชื่อ คุณต้องป้อนตัวเลือกดรอปดาวน์ด้วยตนเอง เนื่องจากการตรวจสอบความถูกต้องของข้อมูลจะไม่อนุญาตให้คุณเลือกรายการแหล่งที่มาในชีตอื่น ดังนั้นต้องป้อนตัวเลือกทั้งหมดสองครั้ง: หนึ่งครั้งในรายการดรอปดาวน์ และอีกครั้งในSheet2 นอกจากนี้ ทั้งสองรายการต้องตรงกัน

หากมีข้อผิดพลาดเกิดขึ้นในรายการใดรายการหนึ่งในรายการใด สูตรหนึ่งของค่าจัดส่งจะสร้างข้อผิดพลาด #N/A เมื่อเลือกตัวเลือกที่ผิดพลาด การตั้งชื่อรายการในSheet2เป็นShippingMethodsช่วยขจัดปัญหาทั้งสอง

คุณสามารถอ้างอิงช่วงที่มีชื่อเมื่อกำหนดการตรวจสอบความถูกต้องของข้อมูลสำหรับรายการดรอปดาวน์โดยเพียงแค่ป้อน=ShippingMethodsในช่องต้นทาง เป็นต้น ซึ่งช่วยให้คุณใช้รายการตัวเลือกที่อยู่บนแผ่นงานอื่นได้

และหากรายการดรอปดาวน์อ้างอิงถึงเซลล์จริงที่ใช้ในการค้นหา (สำหรับสูตรค่าจัดส่ง) ตัวเลือกดรอปดาวน์จะตรงกับรายการค้นหาเสมอ หลีกเลี่ยงข้อผิดพลาด #N/A

สร้างช่วงที่มีชื่อใน Excel

ในการสร้างช่วงที่มีชื่อ ให้เลือกเซลล์หรือช่วงของเซลล์ที่คุณต้องการตั้งชื่อ จากนั้นคลิกในกล่องชื่อ^{( Name Box)} (ซึ่งปกติแล้วที่อยู่ของเซลล์ที่เลือกจะแสดงอยู่ ทางด้านซ้ายของแถบสูตร^{(Formula Bar)} ) พิมพ์ชื่อที่คุณต้องการใช้และกดEnter

คุณยังสามารถสร้างช่วงที่มีชื่อได้โดยคลิก ปุ่ม ใหม่^(New) ใน หน้าต่างตัวจัดการ^(Manager)ชื่อ ซึ่งจะเปิด หน้าต่าง ชื่อใหม่^{(New Name)}  ซึ่งคุณสามารถป้อนชื่อใหม่ได้

ตามค่าเริ่มต้น ช่วงที่จะตั้งชื่อจะถูกตั้งค่าเป็นช่วงใดก็ตามที่เลือกไว้เมื่อคุณคลิก ปุ่ม ใหม่^(New)แต่คุณแก้ไขช่วงนั้นก่อนหรือหลังบันทึกชื่อใหม่ได้

โปรดทราบว่าชื่อช่วงต้องไม่มีการเว้นวรรค แม้ว่าจะสามารถรวมขีดล่างและจุดได้ โดยทั่วไป ชื่อควรขึ้นต้นด้วยตัวอักษรและประกอบด้วยตัวอักษร ตัวเลข จุด หรือขีดล่างเท่านั้น

ชื่อไม่คำนึงถึงขนาดตัวพิมพ์ แต่การใช้สตริงของคำที่เป็นตัวพิมพ์ใหญ่ เช่นTaxRateหรือDecember2018Salesทำให้อ่านและจดจำชื่อได้ง่ายขึ้น คุณไม่สามารถใช้ชื่อช่วงที่เลียนแบบการอ้างอิงเซลล์ที่ถูกต้อง ได้เช่นDog26

คุณสามารถแก้ไขชื่อช่วงของคุณหรือเปลี่ยนช่วงที่อ้างถึงโดยใช้หน้าต่างตัวจัดการ ชื่อ^(Manager)

โปรดทราบว่าแต่ละช่วงที่มีชื่อมีขอบเขตที่กำหนดไว้ โดยปกติ ขอบเขตจะมีค่าเริ่มต้นเป็นWorkbookซึ่งหมายความว่าชื่อช่วงสามารถอ้างอิงได้จากทุกที่ภายในเวิร์กบุ๊ก อย่างไรก็ตาม เป็นไปได้ที่จะมีสองช่วงขึ้นไปที่มีชื่อเดียวกันในชีตแยกกัน แต่อยู่ในเวิร์กบุ๊กเดียวกัน

ตัวอย่างเช่น คุณอาจมีไฟล์ข้อมูลการขายที่มีแผ่นงานแยกต่างหากสำหรับเดือน^(February)มกราคม^(January)กุมภาพันธ์มีนาคมเป็นต้น แต่ละแผ่นงานอาจมีเซลล์ (ช่วงที่ตั้งชื่อ) เรียกว่าMonthlySales ^(March)แต่^{(MonthlySales)}โดยปกติขอบเขตของแต่ละชื่อเหล่านั้นจะมีแต่แผ่นงานที่มี มัน.

ดังนั้น สูตร=ROUND(MonthlySales,0)จะให้ ยอดขายใน เดือนกุมภาพันธ์^(February)ปัดเศษเป็นจำนวนเต็มที่ใกล้ที่สุด ถ้าสูตรอยู่ใน แผ่นงาน เดือนกุมภาพันธ์^(February)แต่ให้ ยอดขายใน เดือนมีนาคม^(March)ถ้าอยู่บน แผ่นงานของ เดือนมีนาคม^(March)เป็นต้น

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

นอกจากนี้ยังทำให้ชื่อช่วงแต่ละช่วงไม่ซ้ำกัน เพื่อให้ชื่อทั้งหมดมีขอบเขตเวิ ร์กบุ๊กได้ ^(Workbook)ตัวอย่างเช่นJanuary_MonthlySales , February_MonthlySales , Budget_Date , Order_Dateเป็นต้น

ข้อควรระวังสองประการเกี่ยวกับขอบเขตของช่วงที่มีชื่อ:^{(Two cautions regarding the scope of named ranges:)} (1) คุณไม่สามารถแก้ไขขอบเขตของช่วงที่มีชื่อหลังจากสร้างแล้ว และ (2) คุณสามารถระบุขอบเขตของช่วงที่มีชื่อใหม่ได้หากคุณสร้างโดยใช้ ปุ่ม ใหม่^(New)ในหน้าต่างตัวจัดการชื่อ^{( Name Manager)}

หากคุณสร้างชื่อช่วงใหม่โดยพิมพ์ลงในช่องชื่อขอบเขต^(Box)จะมีค่าเริ่มต้นเป็นเวิ^(Workbook) ร์กบุ๊ก (หากไม่มีช่วงอื่นที่มีชื่อเหมือนกัน) หรือแผ่นงานที่มีการสร้างชื่อ ดังนั้น ในการสร้างช่วงที่มีชื่อใหม่ซึ่งมีขอบเขตจำกัดเฉพาะแผ่นงาน ให้ใช้ปุ่ม "ใหม่" ของตัวจัดการชื่อ

สุดท้าย สำหรับผู้ที่เขียนมาโคร ชื่อช่วงสามารถอ้างอิงได้อย่างง่ายดายใน โค้ด VBAโดยเพียงแค่ใส่ชื่อช่วงไว้ในวงเล็บ ตัวอย่างเช่น แทนที่จะใช้ ThisWorkbook.Sheets^{(ThisWorkbook.Sheets)} (1).Cells(2,3) คุณสามารถใช้ [ SalesTotal ] หากชื่อนั้นอ้างอิงถึงเซลล์นั้น

เริ่ม^(Start)ใช้ช่วงที่มีชื่อใน เวิร์กชีต Excel ของคุณ และคุณจะรู้สึกซาบซึ้งในประโยชน์ที่ได้รับอย่างรวดเร็ว! สนุก!

Why You Should Be Using Named Ranges in Excel

Namеd ranges are a useful, but often underutilized, feature of Microsoft Εxcel.  Named ranges can make formulas easier to understand (and debug), simplify the creation of complicated spreadsheets, and simplify yoυr macros.

A named range is just a range (either a single cell, or a range of cells) to which you assign a name.  You can then use that name in place of normal cell references in formulas, in macros, and for defining the source for graphs or data validation.

Using a range name, like TaxRate, in place of a standard cell reference, like Sheet2!$C$11, can make a spreadsheet easier to understand and debug/audit.

Using Named Ranges in Excel

For example, let’s look at a simple order form.  Our file includes a fillable order form with a dropdown to select the shipping method, plus a second sheet with a table of shipping costs, and the tax rate.

Version 1 (without named ranges) uses normal A1-style cell references in its formulas (shown in the formula bar below).

Version 2 uses named ranges, making its formulas much easier to understand.  Named ranges also make it easier to enter formulas, since Excel will display a list of names, including function names, that you can pick from, whenever you start to type a name in a formula.  Double-click the name in the pick list to add it to your formula.

Opening the Name Manager window from the Formulas tab displays a list of the range names and the cell ranges they reference.

But named ranges have other benefits also.  In our example files, the shipping method is selected using a dropdown (data validation) in cell B13 on Sheet1.  The selected method is then used to lookup the shipping costs on Sheet2.

Without named ranges, the dropdown choices must be manually entered since data validation will not allow you to select a source list on a different sheet.  So all of the choices must be entered twice: once in the dropdown list, and again on Sheet2.  In addition, the two lists must match.

If an error is made in one of the entries in either list, then the shipping cost formula will generate an #N/A error when the erroneous choice is selected.  Naming the list on Sheet2 as ShippingMethods eliminates both problems.

You can reference a named range when defining the data validation for a dropdown list by simply entering =ShippingMethods in the source field, for example.  This allows you to use a list of choices that are on another sheet.

And if the dropdown is referencing the actual cells used in the lookup (for the shipping cost formula), then the dropdown choices will always match the lookup list, avoiding #N/A errors.

Create a Named Range in Excel

To create a named range, simply select the cell or range of cells you want to name, then click in the Name Box (where the selected cell address is normally displayed, just left of the Formula Bar), type the name you want to use, and press Enter.

You can also create a named range by clicking the New button in the Name Manager window.  This opens a New Name window where you can enter the new name.

By default, the range to be named is set to whatever range is selected when you click the New button, but you can edit that range before or after saving the new name.

Note that range names cannot include spaces, although they can include underscores and periods.  Generally, names should begin with a letter and then contain only letters, numbers, periods, or underscores.

Names are not case-sensitive, but using a string of capitalized words, such as TaxRate or December2018Sales, makes the names easier to read and recognize.  You cannot use a range name that mimics a valid cell reference, such as Dog26.

You can edit your range names or change the ranges they refer to using the Name Manager window.

Note also that each named range has a defined scope.  Normally, the scope will default to Workbook, which means the range name can be referenced from anywhere within the workbook.  However, it is also possible to have two or more ranges with the same name on separate sheets, but within the same workbook.

For example, you might have a sales data file with separate sheets for January, February, March, etc.  Each sheet could have a cell (named range) called MonthlySales, but normally the scope of each of those names would only be the sheet containing it.

Thus, the formula =ROUND(MonthlySales,0) would give February sales, rounded to the nearest whole dollar, if the formula is on the February sheet, but March sales if on the March sheet, etc.

To avoid confusion in workbooks having multiple ranges on separate sheets with the same name or simply complicated workbooks with dozens or hundreds of named ranges, it can be helpful to include the sheet name as part of each range name.

This also makes each range name unique, so that all the names can have a Workbook scope.  For example, January_MonthlySales, February_MonthlySales, Budget_Date, Order_Date, etc.

Two cautions regarding the scope of named ranges: (1) You cannot edit the scope of a named range after it is created, and (2) you can only specify the scope of a new named range if you create it using the New button in the Name Manager window.

If you create a new range name by typing it in the Name Box, the scope will default to either Workbook (if no other range with the same name exists), or to the sheet where the name is being created.  Therefore, to create a new named range whose scope is limited to a particular sheet, use the Name Manager “New” button.

Finally, for those who write macros, range names can be easily referenced in VBA code by simply placing the range name within brackets.  For example, instead of ThisWorkbook.Sheets(1).Cells(2,3) you can simply use [SalesTotal] if that name refers to that cell.

Start using named ranges in your Excel worksheets and you will quickly being to appreciate the benefits! Enjoy!

Related posts

• วิธีการลบบรรทัดว่างใน Excel

• วิธีใช้ฟีเจอร์ Speak Cells ของ Excel

• วิธีแทรกแผ่นงาน Excel ลงใน Word Doc

• วิธีใช้การวิเคราะห์แบบ What-If ของ Excel

• วิธีแก้ไขแถวใน Excel

• วิธีสร้าง Pivot Table แบบง่ายใน Excel

• 5 วิธีในการแปลงข้อความเป็นตัวเลขใน Excel

• วิธีจัดเรียงตามวันที่ใน Excel

• วิธีแก้ไขข้อผิดพลาด #N/A ในสูตร Excel เช่น VLOOKUP

• วิธีการเรียงตามตัวอักษรใน Excel

• ใช้ชื่อช่วงไดนามิกใน Excel สำหรับดรอปดาวน์ที่ยืดหยุ่น

• วิธีการค้นหาใน Excel

• เมื่อใดควรใช้ Index-Match แทน VLOOKUP ใน Excel

• จัดกึ่งกลางข้อมูลเวิร์กชีตของคุณใน Excel สำหรับการพิมพ์

• 10 เคล็ดลับและเทคนิค Excel สำหรับปี 2019

• การใช้เครื่องมือค้นหาเป้าหมายการวิเคราะห์แบบ What-If ของ Excel

• วิธีสร้างมาโครหรือสคริปต์ VBA ใน Excel

• วิธีแชร์ไฟล์ Excel เพื่อการทำงานร่วมกันอย่างง่ายดาย

• วิธีลบเส้นตารางใน Excel

• สะกดตัวเลขอย่างรวดเร็วใน Word และ Excel