뛰어나다

Excel의 명명된 범위

Named Ranges Excel

명명된 범위는 소수의 사용자가 이해하지 못하는 Excel의 이러한 딱딱한 오래된 기능 중 하나입니다. 새로운 사용자는 그것들이 이상하고 무섭게 느껴질 수 있으며, 나이가 많은 사람들도 무의미하고 복잡해 보이기 때문에 피합니다.



그러나 명명된 범위는 실제로 매우 멋진 기능입니다. 수식을 *훨씬* 더 쉽게 만들고, 읽고, 유지 관리할 수 있습니다. 그리고 보너스로 공식을 더 쉽게 재사용할 수 있습니다(이동성 향상).

사실 저는 수식을 테스트하고 프로토타이핑할 때 항상 명명된 범위를 사용합니다. 공식이 더 빨리 작동하도록 도와줍니다. 나는 또한 게으르고 복잡한 참조를 입력하는 것을 좋아하지 않기 때문에 명명된 범위를 사용합니다. :)





Excel에서 명명된 범위의 기본 사항

명명된 범위란 무엇입니까?

명명된 범위는 Excel의 셀 범위에 대해 사람이 읽을 수 있는 이름일 뿐입니다. 예를 들어 범위 이름을 A1:A100 'data'로 지정하면 MAX를 사용하여 간단한 공식으로 최대값을 얻을 수 있습니다.

 
 = MAX (data) // max value

라는 간단한 명명된 범위



명명된 범위의 장점은 셀 참조에 대해 생각하지 않고도 수식에서 의미 있는 이름을 사용할 수 있다는 것입니다. 명명된 범위가 있으면 셀 참조처럼 사용하십시오. 이 모든 수식은 명명된 범위 'data'에서 유효합니다.

 
= MAX (data) // max value = MIN (data) // min value = COUNT (data) // total values = AVERAGE (data) // min value

동영상: 명명된 범위를 만드는 방법

명명된 범위를 만드는 것은 쉽습니다.

명명된 범위를 만드는 것은 빠르고 쉽습니다. 셀 범위를 선택하고 이름 상자에 이름을 입력하기만 하면 됩니다. Return 키를 누르면 이름이 생성됩니다.

이름 상자로 이름이 지정된 범위를 빠르게 생성

새 범위를 빠르게 테스트하려면 이름 상자 옆에 있는 드롭다운에서 새 이름을 선택합니다. Excel에서 워크시트의 범위를 선택합니다.

Excel에서 이름을 자동으로 생성할 수 있음(ctrl + shift + F3)

레이블이 있는 잘 구조화된 데이터가 있는 경우 Excel에서 명명된 범위를 만들도록 할 수 있습니다. 레이블과 함께 데이터를 선택하고 리본의 수식 탭에서 '선택 항목에서 만들기' 명령을 사용하기만 하면 됩니다.

리본의 선택 명령에서 이름 만들기

키보드 단축키 컨트롤 + shift + F3을 사용할 수도 있습니다.

이 기능을 사용하여 한 단계에서 12개 주의 인구에 대한 명명된 범위를 만들 수 있습니다.

Excel에서 중복을 제거하려면 어떻게합니까

선택한 데이터 및 레이블을 사용하여 선택 항목에서 이름 만들기

확인을 클릭하면 이름이 생성됩니다. 이름 상자 옆에 있는 드롭다운 메뉴에서 새로 생성된 모든 이름을 찾을 수 있습니다.

새 이름은 이름 상자 드롭다운 메뉴에도 나타납니다.

이름을 생성하면 다음과 같은 수식에서 사용할 수 있습니다.

 
= SUM (MN,WI,MI)

이름 관리자에서 명명된 범위 업데이트(Control + F3)

명명된 범위를 만든 후에는 이름 관리자 (Control + F3) 필요에 따라 업데이트합니다. 작업할 이름을 선택한 다음 참조를 직접 변경하거나(예: '참조' 편집) 오른쪽에 있는 버튼을 클릭하고 새 범위를 선택합니다.

이름 관리자로 명명된 범위 업데이트

참조를 업데이트하기 위해 편집 버튼을 클릭할 필요가 없습니다. 닫기를 클릭하면 범위 이름이 업데이트됩니다.

참고: 워크시트에서 전체 명명된 범위를 선택하는 경우 새 위치로 끌면 참조가 자동으로 업데이트됩니다. 그러나 워크시트에서 직접 클릭하고 끌어 범위 참조를 조정하는 방법을 모르겠습니다. 방법을 아시는 분은 아래를 눌러주세요!

명명된 모든 범위 보기(control + F3)

통합 문서의 모든 명명된 범위를 빠르게 보려면 이름 상자 옆에 있는 드롭다운 메뉴를 사용합니다.

더 자세한 내용을 보려면 참조가 있는 모든 이름을 나열하고 필터도 제공하는 이름 관리자(Control + F3)를 엽니다.

이름 관리자는 새로 생성된 모든 이름을 표시합니다.

참고: Mac에는 이름 관리자가 없으므로 대신 이름 정의 대화 상자가 표시됩니다.

명명된 모든 범위 복사 및 붙여넣기(F3)

통합 문서의 명명된 범위에 대한 보다 지속적인 기록을 원하는 경우 원하는 위치에 전체 이름 목록을 붙여넣을 수 있습니다. 공식 > 공식에서 사용(또는 단축키 F3 사용)으로 이동한 다음 이름 붙여넣기 > 목록 붙여넣기를 선택합니다.

이름 붙여넣기 대화 상자

목록 붙여넣기 버튼을 클릭하면 워크시트에 붙여넣은 이름과 참조가 표시됩니다.

명명된 범위를 워크시트에 붙여넣은 후

워크시트에서 직접 이름 보기

확대/축소 수준을 40% 미만으로 설정하면 Excel에서 워크시트에 직접 범위 이름을 표시합니다.

확대/축소 수준 <40%에서 Excel은 범위 이름을 표시합니다.

이 팁을 주셔서 감사합니다, Felipe!

이름에는 규칙이 있습니다

명명된 범위를 만들 때 다음 규칙을 따르세요.

  1. 이름은 문자, 밑줄(_) 또는 백슬래시()로 시작해야 합니다.
  2. 이름에는 공백과 대부분의 구두점을 사용할 수 없습니다.
  3. 이름은 셀 참조와 충돌할 수 없습니다. 범위 이름을 'A1' 또는 'Z100'으로 지정할 수 없습니다.
  4. 단일 문자는 이름('a', 'b', 'c' 등)에 사용할 수 있지만 문자 'r' 및 'c'는 예약되어 있습니다.
  5. 이름은 대소문자를 구분하지 않습니다. 'home', 'HOME' 및 ​​'HoMe'는 Excel에서 모두 동일합니다.

수식의 명명된 범위

명명된 범위는 수식에서 사용하기 쉽습니다.

예를 들어 통합 문서의 셀 이름을 '업데이트됨'이라고 가정해 보겠습니다. 아이디어는 현재 날짜를 셀에 넣고(Ctrl + ) 통합 문서의 다른 곳에서 날짜를 참조할 수 있다는 것입니다.

텍스트 수식 내에서 명명된 범위 사용

B8의 공식은 다음과 같습니다.

 
='Updated: '&  TEXT (updated, 'ddd, mmmm d, yyyy')

이 수식은 통합 문서의 아무 곳에나 붙여넣을 수 있으며 올바르게 표시됩니다. '업데이트됨'에서 날짜를 변경할 때마다 수식이 사용되는 곳마다 메시지가 업데이트됩니다. 보다 이 페이지 더 많은 예를 보려면.

수식을 입력할 때 명명된 범위가 나타납니다.

명명된 범위를 만든 후에는 이름의 첫 글자를 입력할 때 수식에 자동으로 나타납니다. 일치하는 항목이 있고 Excel에서 이름을 입력하도록 하려면 탭 키를 눌러 이름을 입력합니다.

수식을 입력할 때 명명된 범위가 나타납니다.

명명된 범위는 상수처럼 작동할 수 있습니다.

명명된 범위는 중앙 위치에 생성되기 때문에 셀 참조 없이 상수처럼 사용할 수 있습니다. 예를 들어 고정 값을 사용하여 'MPG'(갤런당 마일) 및 'CPG'(갤런당 비용)와 같은 이름을 만들고 할당할 수 있습니다.

명명된 범위는 셀 참조 없이 상수처럼 작동할 수 있습니다.

그런 다음 수식에서 원하는 모든 위치에서 이러한 이름을 사용하고 하나의 중앙 위치에서 값을 업데이트할 수 있습니다.

수식에서 상수처럼 명명된 범위 사용

명명된 범위는 기본적으로 절대적입니다.

기본적으로 명명된 범위는 절대 참조처럼 작동합니다. 예를 들어 이 워크시트에서 연료를 계산하는 공식은 다음과 같습니다.

 
=C5/$D

절대 주소가 있는 표준 공식

D2에 대한 참조는 절대(잠금)이므로 D2를 변경하지 않고 공식을 복사할 수 있습니다.

D2의 이름을 'MPG'로 지정하면 공식은 다음과 같습니다.

 
=C5/MPG

수식에서 상수처럼 명명된 범위 사용

MPG는 기본적으로 절대값이므로 공식을 그대로 D열에 복사할 수 있습니다.

명명된 범위는 상대적일 수도 있습니다.

명명된 범위는 기본적으로 절대적이지만 상대적일 수도 있습니다. 상대적 명명된 범위는 활성 셀의 위치에 상대적인 범위를 나타냅니다. 범위가 생성될 때 . 결과적으로 상대적으로 명명된 범위는 이동되는 모든 위치에서 작동하는 일반 수식을 작성하는 데 유용합니다.

예를 들어, 다음과 같이 이름이 range인 일반 'CellAbove'를 만들 수 있습니다.

  1. A2 셀 선택
  2. 이름 관리자를 열려면 Ctrl + F3
  3. '참조' 섹션으로 탭한 다음 다음을 입력합니다. =A1

CellAbove는 이제 위의 셀이 사용되는 모든 위치에서 값을 검색합니다.

중요: 이름을 만들기 전에 활성 셀이 올바른 위치에 있는지 확인하십시오.

기존 수식에 명명된 범위 적용

명명된 범위를 사용하지 않는 기존 수식이 있는 경우 Excel에 수식에 명명된 범위를 적용하도록 요청할 수 있습니다. 업데이트할 수식이 포함된 셀을 선택하여 시작합니다. 그런 다음 수식 > 이름 정의 > 이름 적용을 실행합니다.

이름 적용 대화 상자

그러면 Excel에서 해당 명명된 범위가 있는 참조를 이름 자체로 바꿉니다.

찾기 및 바꾸기를 사용하여 이름을 적용할 수도 있습니다.

찾기 및 바꾸기로 이름 범위 적용하기

중요: 워크시트의 백업을 저장하고 수식에서 찾기 및 바꾸기를 사용하기 전에 변경할 셀만 선택합니다.

명명된 범위의 주요 이점

명명된 범위를 사용하면 수식을 더 쉽게 읽을 수 있습니다.

명명된 범위의 가장 큰 단일 이점은 수식을 더 쉽게 읽고 유지 관리할 수 있다는 것입니다. 이는 암호 참조를 의미 있는 이름으로 대체하기 때문입니다. 예를 들어, 우리 태양계의 행성에 대한 데이터가 있는 이 워크시트를 고려하십시오. 명명된 범위가 없으면 테이블에서 'Position'을 가져오는 VLOOKUP 공식은 매우 복잡합니다.

 
= VLOOKUP ($H,$B:$E,2,0)

명명된 범위가 없으면 공식이 모호할 수 있습니다.

일치하는 항목이 여러 개인 경우 vlookup

그러나 'data'라는 이름의 B3:E11과 'planet'이라는 이름의 H4를 사용하여 다음과 같은 수식을 작성할 수 있습니다.

 
= VLOOKUP (planet,data,2,0) // position = VLOOKUP (planet,data,3,0) // diameter = VLOOKUP (planet,data,4,0) // satellites

명명된 범위를 사용하면 수식이 간단해질 수 있습니다.

열 인덱스에서 이러한 수식의 유일한 차이점을 한 눈에 볼 수 있습니다.

명명된 범위는 수식을 이식 가능하고 재사용 가능하게 만듭니다.

명명된 범위를 사용하면 다른 워크시트에서 수식을 훨씬 쉽게 재사용할 수 있습니다. 워크시트에서 미리 이름을 정의하는 경우 이러한 이름을 사용하는 수식을 붙여넣으면 '그냥 작동'됩니다. 이것은 수식을 빠르게 작동시키는 좋은 방법입니다.

예를 들어 다음 수식은 숫자 데이터 범위에서 고유한 값을 계산합니다.

 
= SUM (--( FREQUENCY (data,data)>0))

이 수식을 자신의 워크시트로 빠르게 '포팅'하려면 범위 이름을 'data'로 지정하고 수식을 워크시트에 붙여넣습니다. '데이터'에 숫자 값이 포함되어 있는 한 수식은 바로 작동합니다.

팁: 대상 통합 문서에서 *먼저* 필요한 범위 이름을 만든 다음 수식을 텍스트로만 복사하는 것이 좋습니다(예: 다른 워크시트에 수식이 포함된 셀을 복사하지 말고 수식 텍스트만 복사하세요 ). 이렇게 하면 Excel이 즉석에서 이름을 만드는 것을 중지하고 l 이름 생성 프로세스를 완전히 제어할 수 있습니다. 수식 텍스트만 복사하려면 수식 입력줄에서 텍스트를 복사하거나 다른 응용 프로그램(예: 브라우저, 텍스트 편집기 등)을 통해 복사합니다.

명명된 범위를 탐색에 사용할 수 있습니다.

명명된 범위는 빠른 탐색에 적합합니다. 이름 상자 옆에 있는 드롭다운 메뉴를 선택하고 이름을 선택하기만 하면 됩니다. 마우스를 놓으면 범위가 선택됩니다. 명명된 범위가 다른 시트에 있으면 자동으로 해당 시트로 이동합니다.

명명된 범위를 통해 간단한 탐색 가능

명명된 범위는 하이퍼링크와 잘 작동합니다.

명명된 범위는 하이퍼링크를 쉽게 만듭니다. 예를 들어, Sheet1에서 A1의 이름을 'home'으로 지정하면 다른 곳에 하이퍼링크를 생성하여 그곳으로 돌아갈 수 있습니다.

명명된 범위에 대한 하이퍼링크 만들기

워크시트의 명명된 범위 하이퍼링크 예

HYPERLINK 함수 내에서 명명된 범위를 사용하려면 명명된 범위 앞에 파운드 기호를 추가합니다.

 
= HYPERLINK ('#home','take me home')

참고: 이상하게도 일반 범위 이름처럼 테이블에 하이퍼링크할 수 없습니다. 그러나 테이블과 동일한 이름(예: =Table1)을 정의하고 이에 대한 하이퍼링크를 지정할 수 있습니다. 테이블을 직접 연결하는 방법을 아는 사람이 있다면 알려주세요!

데이터 유효성 검사를 위한 명명된 범위

이름 범위는 논리적으로 명명된 참조를 사용하여 드롭다운 메뉴로 입력의 유효성을 검사할 수 있으므로 데이터 유효성 검사에 적합합니다. 아래에서 G4:G8 범위의 이름은 'statuslist'로 지정되고 다음과 같이 연결된 목록으로 데이터 유효성 검사를 적용합니다.

목록을 사용하여 데이터 유효성 검사에 명명된 범위 사용

결과는 명명된 범위의 값만 허용하는 열 E의 드롭다운 메뉴입니다.

명명된 범위 예제를 사용한 데이터 유효성 검사

동적 명명된 범위

이름 범위는 워크시트의 새 데이터에 자동으로 조정될 때 매우 유용합니다. 이렇게 설정된 범위를 '동적 명명 범위'라고 합니다. 범위를 동적으로 만드는 방법에는 수식과 표의 두 가지가 있습니다.

테이블이 있는 동적 명명 범위

테이블은 동적 명명된 범위를 만드는 가장 쉬운 방법입니다. 데이터에서 아무 셀이나 선택한 다음 단축키 Control + T를 사용합니다.

엑셀 테이블 만들기

Excel 표를 생성하면 자동으로 이름이 생성되지만(예: Table1), 원하는 대로 표의 이름을 변경할 수 있습니다. 테이블을 생성하면 데이터가 추가되면 자동으로 확장됩니다.

표는 자동으로 확장되며 이름을 바꿀 수 있습니다.

수식이 있는 동적 명명된 범위

OFFSET 및 INDEX와 같은 함수를 사용하여 수식으로 동적 명명된 범위를 만들 수도 있습니다. 이러한 수식은 다소 복잡하지만 테이블을 사용하지 않으려는 경우 간단한 솔루션을 제공합니다. 아래 링크는 전체 설명이 포함된 예를 제공합니다.

  • INDEX가 있는 동적 범위 공식의 예
  • OFFSET이 있는 동적 범위 공식의 예

데이터 유효성 검사의 테이블 이름

Excel 표는 자동 동적 범위를 제공하므로 항상 변경될 수 있는 목록에 대해 유효성을 검사하는 것이 목표인 데이터 유효성 검사 규칙에 자연스럽게 적합합니다. 그러나 테이블의 한 가지 문제는 구조적 참조를 직접 사용하여 데이터 유효성 검사 또는 조건부 서식 규칙을 만들 수 없다는 것입니다. 즉, 조건부 서식 또는 데이터 유효성 검사 입력 영역에서 테이블 이름을 사용할 수 없습니다.

그러나 해결 방법으로 테이블을 가리키는 명명된 범위를 정의한 다음 데이터 유효성 검사 또는 조건부 서식에 명명된 범위를 사용할 수 있습니다. 아래 비디오는 이 접근 방식을 자세히 설명합니다.

동영상: 테이블에 명명된 범위를 사용하는 방법

명명된 범위 삭제

참고: 명명된 범위를 참조하는 수식이 있는 경우 이름을 제거하기 전에 먼저 수식을 업데이트할 수 있습니다. 그렇지 않으면 #NAME이 표시되나요? 삭제된 이름을 계속 참조하는 수식 오류. 문제가 있어 원본으로 되돌려야 하는 경우에 대비하여 명명된 범위를 제거하기 전에 항상 워크시트를 저장하십시오.

명명된 범위는 셀을 삭제하고 삽입할 때 조정됩니다.

명명된 범위의 *일부*를 삭제하거나 명명된 범위 내에 셀/행/열을 삽입하면 범위 참조가 그에 따라 조정되고 유효한 상태로 유지됩니다. 그러나 명명된 범위를 묶는 모든 셀을 삭제하면 명명된 범위에서 참조가 손실되고 #REF 오류가 표시됩니다. 예를 들어 A1의 이름을 'test'로 지정하고 A열을 삭제하면 이름 관리자는 '참조'를 다음과 같이 표시합니다.

 
=Sheet1!#REF!

이름 관리자로 이름 삭제

통합 문서에서 명명된 범위를 수동으로 제거하려면 이름 관리자를 열고 범위를 선택한 다음 삭제 버튼을 클릭합니다. 동시에 둘 이상의 이름을 제거하려면 Shift + 클릭 또는 Ctrl + 클릭을 사용하여 여러 이름을 선택한 다음 한 번에 삭제할 수 있습니다.

오류가 있는 이름 삭제

참조 오류가 있는 이름이 많은 경우 이름 관리자의 필터 버튼을 사용하여 오류가 있는 이름을 필터링할 수 있습니다.

이름 관리자 필터 메뉴

그런 다음 Shift+클릭하여 모든 이름을 선택하고 삭제합니다.

명명된 범위 및 범위

Excel의 명명된 범위에는 명명된 범위가 지정된 워크시트에 대해 로컬인지 또는 전체 통합 문서에서 전역인지를 결정하는 '범위'라는 항목이 있습니다. 전역 이름의 범위는 '통합 문서'이고 로컬 이름의 범위는 시트 이름과 동일합니다. 예를 들어, 로컬 이름의 범위는 'Sheet2'일 수 있습니다.

범위의 목적

전역 범위가 있는 명명된 범위는 통합 문서의 모든 시트에서 특정 데이터, 변수 또는 상수에 액세스할 수 있도록 하려는 경우에 유용합니다. 예를 들어, 여러 워크시트에서 사용되는 글로벌 명명된 범위의 세율 가정을 사용할 수 있습니다.

로컬 범위

로컬 범위는 이름이 작성된 시트에서만 작동함을 의미합니다. 즉, 동일한 통합 문서에 모두 같은 이름을 사용하는 여러 워크시트가 있을 수 있습니다. 예를 들어, 모두 로컬로 범위가 지정된 동일한 이름의 명명된 범위를 사용하는 월별 추적 시트(한 달에 하나씩)가 있는 통합 문서가 있을 수 있습니다. 이렇게 하면 다른 시트에서 동일한 공식을 재사용할 수 있습니다. 로컬 범위를 사용하면 각 시트의 이름이 다른 시트의 이름과 충돌하지 않고 올바르게 작동할 수 있습니다.

로컬 범위로 이름을 참조하려면 시트 이름을 범위 이름 앞에 접두어로 붙일 수 있습니다.

 
Sheet1!total_revenue Sheet2!total_revenue Sheet3!total_revenue

로 생성된 범위 이름 이름 상자 자동으로 전역 범위를 갖습니다. 이 동작을 재정의하려면 이름을 정의할 때 시트 이름을 추가합니다.

 
Sheet3!my_new_name

글로벌 범위

전역 범위는 이름이 통합 문서의 모든 위치에서 작동함을 의미합니다. 예를 들어 셀 이름을 'last_update'로 지정하고 셀에 날짜를 입력할 수 있습니다. 그런 다음 아래 수식을 사용하여 워크시트에서 마지막으로 업데이트된 날짜를 표시할 수 있습니다.

 
=last_update

전역 이름은 통합 문서 내에서 고유해야 합니다.

로컬 범위

로컬 범위의 명명된 범위는 로컬 가정에 대해서만 명명된 범위를 사용하는 워크시트에 적합합니다. 예를 들어, 모두 로컬로 범위가 지정된 동일한 이름의 명명된 범위를 사용하는 월별 추적 시트(한 달에 하나씩)가 있는 통합 문서가 있을 수 있습니다. 로컬 범위를 사용하면 각 시트의 이름이 다른 시트의 이름과 충돌하지 않고 올바르게 작동할 수 있습니다.

명명된 범위 범위 관리

기본적으로 이름 상자를 사용하여 생성된 새 이름은 전역적이며 생성된 후에는 명명된 범위의 범위를 편집할 수 없습니다. 그러나 해결 방법으로 원하는 범위의 이름을 삭제하고 다시 만들 수 있습니다.

한 번에 여러 이름을 전역에서 지역으로 변경하려는 경우 이름이 포함된 시트를 복사하는 것이 좋습니다. 명명된 범위가 포함된 워크시트를 복제하면 Excel에서 명명된 범위를 두 번째 시트에 복사하고 동시에 범위를 로컬로 변경합니다. 로컬 범위의 이름을 가진 두 번째 시트가 있으면 선택적으로 첫 번째 시트를 삭제할 수 있습니다.

얀 카렐 피에테르세와 찰스 윌리엄스 는 명명된 범위에 대해 많은 유용한 작업을 제공하는 Name Manager라는 유틸리티를 개발했습니다. 당신은 할 수 있습니다 여기에서 이름 관리자 유틸리티를 다운로드하십시오. .

저자 데이브 브런스


^