Home > 강좌 > 엑셀수식강좌 > 05. 엑셀 이름정의와 표기능 > 이름 정의로 엑셀 수식을 읽기 쉽게!

이름 정의로 엑셀 수식을 읽기 쉽게!

  • by

오늘은 엑셀 수식을 읽기 쉽게 만드는 이름에 대해 알아 보겠습니다.

보통은 셀 또는 범위에 이름을 정의하여 많이 사용하지만 숫자, 텍스트, 수식에도 이름을 정의 할 수 있습니다. 이름은 간단하게 만들 수 있고 생각보다 응용할 수 있는 방법이 많은 훌륭한 기능이므로 잘 익혀두면 실무에서 많은 도움이 됩니다.

실습용 엑셀파일 다운로드 : 엑셀이름정의.xlsx

1. 이름이란?

엑셀에서 '이름'이란 셀, 범위 등에 붙이는 이름표라고 할 수 있습니다. 셀이나 범위의 복잡한 주소 대신 이름표를 붙이면 복잡한 수식을 알아보기 쉽도록 할 수 있습니다.

어떤 회사에서 영업사원에게 판매금액의 5%를 인센티브로 지급한다고 할 때 일반적으로 아래 그림과 같이 판매금액에 인센티브 지급율을 곱해서 계산합니다.

=C8*$C$4 

엑셀 수식을 읽기 쉽게 만드는 이름 사용하기

 

=C8*$C$4 수식에서 '인센티브 지급율' 5%가 입력된 [$C$4] 범위 대신 '인센티브율'이라는 이름을 만들어서 다음과 같이 계산할 수 있습니다. 훨씬 알아보기 쉬운 수식이 됩니다.

=C14*인센티브율

절대참조 셀에 이름정의 하기

 

엑셀의 이름은 셀을 포함하여 다양한 곳에 붙일 수 있습니다.

  • 셀, 범위
  • 상수(숫자, 문자)
  • 수식

 

2. 이름 만들고 사용하기

위에서 사용한 이름 '인센티브율'을 어떻게 만드는지 따라해 보겠습니다.

몇 가지 방법이 있습니다.

1) [이름 상자]에서 만들기

엑셀 시트의 왼쪽 상단에 있는 '이름 상자'에서 만들 수 있습니다.

이름상자에서 이름 정의하기

① [C4]셀을 선택하고 ②이름 상자에 '인센티브율'이라고 입력하면 이름이 만들어 집니다.

이제 [C4] 라는 셀 주소 대신 다음과 같이 '인센티브지급율'이라는 이름을 수식에서 사용할 수 있습니다.

=C14*인센티브율

 

2) [이름 정의] 메뉴로 만들기

① 리본 메뉴에서 [수식] 탭 → [정의된 이름] 그룹 → [이름 정의]를 누르면 [새 이름] 대화상자가 나타납니다.

② 대화상자에서 [이름] 필드에 '인센티브지급율'을 입력

③ [참조 대상]의 오른쪽 버튼을 클릭하여 [C4] 셀을 지정하면 그림과 같이 =Sheet1!$C$4 이 입력됩니다.

④ <확인> 버튼을 누르면 이름이 만들어 집니다.

리본메뉴에서 이름 정의하기

 

이름이 만들어지고  마지막으로 [이름 관리자] 대화상자가 나타납니다. 이름이 정상적으로 만들어 졌는지 확인하고 <닫기> 버튼을 누릅니다.

[이름관리자]에서는 이름 새로만들기, 이미 만들어진 이름 편집, 삭제 등을 할 수 있습니다.

이름관리자에서 이름 정의하기

 

3) 이름을 만드는 규칙

이름을 만들 때는 '숫자로 시작하는 이름을 만들 수 없는' 등 다음과 같은 몇가지 규칙이 있습니다.

  • 문자 또는 밑줄로 시작해야 함
  • 공백 또는 특수문자 불가
  • 255자까지만 사용
  • A1, B2, R1C1 등 셀범위를 표현하는 값을 이름으로 사용불가
  • 영어 대소문자 구분은 하지 않음

 

4) 이름의 범위

이름을 처음 만들 때 범위를 별도로 지정하지 않는한 통합문서 범위로 만들어집니다. 하나의 엑셀파일 내의 어느 시트에서나 통합문서 범위로 만들어지 이름을 참조할 수 있습니다.

[이름관리자]를 보면 앞에서 만든 이름 '인센티브율'은 통합 문서 범위로 만들진 것을 확인할 수 있습니다.

엑셀 이름의 범위

 

만약 아래 그림과 같이 '이름이 참조하고 있는 시트'를 복사하면 '통합 문서' 범위의 이름이 워크시트 범위의 이름으로 복사됩니다.  복사되지만 동일한 이름으로 만들어집니다.

이 이름을 다른 시트에서 참조하려면 이름 앞에 시트명과 !를 붙여야 합니다.

=C14*Sheet1 (2)'!인센티브율

엑셀 이름의 범위

범위가 달라서 문제가 되지는 않지만 이름이 동일하므로 혼란스러울 수 있습니다. 이름이 있는 시트를 복사할 때 이러한 점을 미리 고려하여야 합니다. 만약 워크시트 범위의 이름이 필요 없다면 삭제하는 것이 좋습니다.

 

3. 응용하기

1) 범위에 이름 붙이기

아래 예와 같이 VLOOKUP함수로 값을 찾아올 때 함수의 두번째 인수 table_array에 [$B$22:$C$31] 범위를 지정하는데 범위가 눈에 잘 들어오지 않습니다.

=VLOOKUP(B35,$B$22:$C$31,2,FALSE)

범위에 이름 정의하기

 

[$B$22:$C$31] 범위를 VLOOKUP함수에 직접 입력하는 대신 '상품정보'라는 이름을 정의해서 사용해 보겠습니다.

[이름] 필드에 '상품정보'를 입력하고 [참조 대상]에 =Sheet1!$B$21:$D$31를 입력해서 이름을 만듭니다.

범위에 이름 정의하기

 

이제 다음과 같이 [$B$22:$C$31] 범위 대신 '상품정보'라는 이름을 입력하여 상품명을 가져올 수 있습니다.

범위를 직접 입력하는 수식보다 훨씬 이해하기 쉬운 수식이 되었습니다.

=VLOOKUP(B43,상품정보,2,FALSE)

범위에 이름 정의하기

 

2) 상수(숫자, 텍스트)에 이름 붙이기

이름은 셀, 범위 뿐만 아니라 숫자, 텍스트 등 상수값에도 붙일 수 있습니다.

해마다 바뀌는 최저시급(2021년에는 8720원)을 이름으로 정해서 사용해 보겠습니다.

[이름] 필드에 '최저시급2021'을 입력하고 [참조 대상]에 =8720을 입력해서 이름을 만듭니다.

숫자에 이름 정의하기

 

만들어진 이름은 다음과 같이 =최저시급2021을 셀에 입력하여 바로 참조할 수 있습니다. 이렇게 구해진 시급에 근무시간을 곱하면 근무자별 시급합계를 구할 수 있습니다.

숫자에 이름 정의하기

 

숫자뿐만 아니라 텍스트 상수도 이름으로 정의할 수 있습니다.

다음과 같이 업무에서 많이 입력하는 회사주소를 이름으로 만들어 놓고 사용할 수 있습니다.

텍스트에 이름 정의하기

 

만들어진 이름은 다음과 같이 =나나문구주소를 셀에 입력하여 바로 참조할 수 있습니다.

텍스트에 이름 정의하기

 

 

3) 수식에 이름 붙이기

마지막으로 살펴볼 이름 유형입니다.

다음과 같이 수식에도 이름을 붙일 수 있습니다.

[이름] 필드에 '월주차'를 입력하고 [참조 대상]에 다음 수식을 입력하면 오늘이 현재월의 몇주차인지를 구해주는 이름이 만들어 집니다.

=MONTH(TODAY()) & "월 " & WEEKNUM(TODAY(),1)-WEEKNUM(DATE(YEAR(TODAY()),MONTH(TODAY()),1),1)+1 & "주차"

수식에 이름 정의하기

 

다음과 같이 =월주차를 셀에 입력하면 2021년 11월 9일 현재 오늘 기준으로 '11월 2주차'를 구해줍니다.

수식에 이름 정의하기

 

 

[ 엑셀 수식 강좌  - 엑셀 이름정의와 표기능]

 

답글 남기기

이메일 주소는 공개되지 않습니다.