명명된 범위는 유용하지만 종종 잘 활용되지 않는 Microsoft Excel 의 기능입니다 . 명명된 범위를 사용하면 수식을 더 쉽게 이해(및 디버그)하고, 복잡한 스프레드시트 생성을 단순화하고, 매크로를 단순화할 수 있습니다.
명명된 범위는 이름을 지정하는 범위(단일 셀 또는 셀 범위)입니다. 그런 다음 수식, 매크로 및 그래프 또는 데이터 유효성 검사의 소스를 정의하는 데 일반 셀 참조 대신 해당 이름을 사용할 수 있습니다.
Sheet2 !$C$11 과 같은 표준 셀 참조 대신 TaxRate 와 같은 범위 이름을 사용 하면 스프레드시트를 더 쉽게 이해하고 디버그/감사할 수 있습니다.
Excel에서 명명된 범위 사용
예를 들어 간단한 주문 양식을 살펴보겠습니다. 파일 에는 배송 방법을 선택하기 위한 드롭다운이 있는 기입 가능한 주문 양식과 배송비 및 세율 표가 있는 두 번째 시트가 포함되어 있습니다.
버전 1(명명된 범위 없음)은 수식에서 일반 A1 스타일(A1-style) 셀 참조를 사용합니다(아래 수식 입력줄에 표시됨).
버전 2는 명명된 범위를 사용하므로 공식을 훨씬 더 쉽게 이해할 수 있습니다. 또한 명명된 범위를 사용하면 수식 에 이름을 입력하기 시작할 때마다 선택할 수 있는 함수 이름을 포함한 이름 목록이 Excel 에 표시 되므로 수식을 더 쉽게 입력 할 수 있습니다. 선택 목록에서 이름을 두 번 클릭(Double-click) 하여 수식에 추가합니다.
수식 탭에서 (Formulas)이름 관리자(Name Manager) 창을 열면 범위 이름 목록과 참조하는 셀 범위가 표시됩니다.
그러나 명명된 범위에는 다른 이점도 있습니다. 예제 파일 에서 Sheet1 의 (Sheet1)B13 셀에 있는 드롭다운(데이터 유효성 검사)을 사용하여 배송 방법을 선택합니다 . 그런 다음 선택한 방법을 사용하여 Sheet2(Sheet2) 에서 운송비를 조회합니다 .
명명된 범위가 없으면 데이터 유효성 검사를 통해 다른 시트에서 소스 목록을 선택할 수 없으므로 드롭다운 선택 항목을 수동으로 입력해야 합니다. 따라서 모든 선택 항목은 두 번 입력해야 합니다. 한 번은 드롭다운 목록에, 한 번은 Sheet2 에 입력해야 합니다 . 또한 두 목록이 일치해야 합니다.
두 목록의 항목 중 하나에 오류가 있는 경우 잘못된 선택을 선택하면 배송 비용 공식에서 #N/A 오류가 생성됩니다. Sheet2 의 목록 이름 을 ShippingMethods 로 지정 하면 두 문제가 모두 제거됩니다.
예를 들어 소스 필드에 =ShippingMethods 를 입력하여 드롭다운 목록에 대한 데이터 유효성 검사를 정의할 때 명명된 범위를 참조할 수 있습니다 . 이렇게 하면 다른 시트에 있는 선택 목록을 사용할 수 있습니다.
그리고 드롭다운이 조회에 사용된 실제 셀을 참조하는 경우(운송비 공식의 경우) 드롭다운 선택 항목은 항상 조회 목록과 일치하여 #N/A 오류를 방지합니다.
Excel에서 명명된 범위 만들기
이름이 지정된 범위를 만들려면 이름을 지정하려는 셀 또는 셀 범위를 선택한 다음 이름 상자 (일반적으로 선택한 셀 주소가 ( Name Box)수식 입력줄(Formula Bar) 바로 왼쪽에 표시되는 위치 )를 클릭 하고 사용할 이름을 입력합니다 , Enter 키(Enter) 를 누릅니다 .
이름 관리자(Manager) 창 에서 새로(New) 만들기 버튼을 클릭하여 명명된 범위를 만들 수도 있습니다 . 새 이름 을 입력할 수 있는 새 이름(New Name) 창이 열립니다 .
기본적으로 이름을 지정할 범위는 새로 만들기(New) 버튼을 클릭할 때 선택한 범위로 설정되지만 새 이름을 저장하기 전이나 저장한 후에 해당 범위를 편집할 수 있습니다.
범위 이름에는 밑줄과 마침표를 포함할 수 있지만 공백을 포함할 수 없습니다. 일반적으로 이름은 문자로 시작하고 문자, 숫자, 마침표 또는 밑줄만 포함해야 합니다.
이름은 대소문자를 구분하지 않지만 TaxRate 또는 December2018Sales 와 같이 대문자로 된 문자열을 사용하면 이름을 더 쉽게 읽고 인식할 수 있습니다. Dog26 과 같이 유효한 셀 참조를 모방하는 범위 이름을 사용할 수 없습니다 .
이름 관리자(Manager) 창 을 사용하여 범위 이름을 편집하거나 참조하는 범위를 변경할 수 있습니다 .
또한 각 명명된 범위에는 정의된 범위가 있습니다. 일반적으로 범위는 기본적으로 Workbook 으로 설정되며 , 이는 통합 문서 내의 모든 위치에서 범위 이름을 참조할 수 있음을 의미합니다. 그러나 동일한 통합 문서 내에서 별도의 시트에 동일한 이름을 가진 두 개 이상의 범위를 가질 수도 있습니다.
예를 들어, 1월 , 2월 , 3월 등에 대한 별도의 시트가 있는 판매 데이터 파일이 있을 수(January) 있습니다 .(February) 각 시트 에는 (March)MonthlySales 라는 셀(이름이 지정된 범위)이 있을 수 있지만 일반적으로 각 이름의 범위는 다음을 포함하는 시트입니다. 그것.
따라서 공식 =ROUND(MonthlySales,0) 은 수식이 2월(February) 시트 에 있는 경우 가장 가까운 정수 달러로 반올림된 2월 매출을 제공하고 (February)3월(March) 시트 등에 있는 경우 3월 매출을 제공합니다.(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 코드에서 범위 이름을 쉽게 참조할 수 있습니다. (VBA)예를 들어 ThisWorkbook.Sheets (1).Cells(2,3) 대신 [ (ThisWorkbook.Sheets)SalesTotal ] 이름이 해당 셀을 참조하는 경우 간단히 사용할 수 있습니다 .
(Start)Excel 워크시트 에서 명명된 범위를 사용하기 시작 하면 이점을 빠르게 알게 될 것입니다! 즐기다!
Why You Should Be Using Named Ranges in Excel
Named ranges are a useful, but oftеn underutilized, feature of Microsoft Excel. Named ranges can make formulas easier to understand (and debug), sіmplify the creation of complicated spreadsheets, and simplify your mаcros.
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!
