이번 강좌에서는 엑셀 수식을 읽기 쉽게 만드는 이름에 대해 알아보겠습니다.
보통은 셀 또는 범위에 이름을 정의하여 많이 사용하지만 숫자, 텍스트, 수식에도 이름을 정의 할 수 있습니다. 이름은 간단하게 만들 수 있고 생각보다 응용할 수 있는 방법이 많은 기능이므로 잘 익혀두면 실무에서 많은 도움이 됩니다.
1. 이름이란?
실습: [셀,범위 이름정의] 시트
엑셀에서 '이름'이란 셀, 범위 등에 붙이는 이름표라고 할 수 있습니다. 셀이나 범위의 복잡한 주소 대신 이름표를 붙이면 복잡한 수식을 알아보기 쉽도록 할 수 있습니다.
회사에서 영업사원에게 판매금액의 5%를 인센티브로 지급한다고 할 때 보통 다음과 같이 판매금액에 인센티브 지급율을 곱해서 계산합니다.
=C8*$C$4
수식 =C8*$C$4에서 '인센티브 지급율' 5%가 입력된 [$C$4] 범위 대신 '인센티브율'이라는 이름을 만들어 다음과 같이 계산할 수 있습니다. 훨씬 알아보기 쉬운 수식이 됩니다.
=C14*인센티브율
엑셀의 이름은 셀을 포함하여 다양한 곳에 붙일 수 있습니다.
- 셀, 범위
- 상수(숫자, 문자)
- 수식
2. 이름 만들고 사용하기
실습: [셀,범위 이름정의] 시트
위에서 사용한 이름 '인센티브율'을 어떻게 만드는지 알아보겠습니다.
[이름 상자]에서 만들기
엑셀 시트의 왼쪽 상단에 있는 '이름 상자'에서 만들 수 있습니다.
01 ① [C4]셀을 선택하고 ②이름 상자에 '인센티브율'이라고 입력합니다.
02 이름이 만들어졌습니다. 이제 [C4]라는 셀 주소 대신 '인센티브율'이라는 이름을 수식에서 사용할 수 있습니다.
=C14*인센티브율
[이름 정의] 메뉴로 만들기
01 ① 이름을 정의할 셀을 선택하고 ② [수식] 탭 → [정의된 이름] 그룹 → [이름 정의]를 눌러 [새 이름] 대화상자를 실행합니다.
02 ① [이름] 필드에 '인센티브율'을 입력하고 ② <확인>을 누르면 이름이 만들어 집니다.
※ [참조 대상]에는 앞에서 선택한 셀의 주소가 입력되어 있습니다. 필요 시 오른쪽 버튼을 누른 후 셀을 선택하여 참조 대상을 바꿀 수 있습니다.
03 이름이 만들어지고 [이름 관리자] 대화상자가 나타납니다. 이름이 정상적으로 만들어 졌는지 확인하고 <닫기>를 누릅니다.
이름을 만드는 규칙
이름을 만들 때는 '숫자로 시작하는 이름을 만들 수 없는' 등 다음과 같은 몇 가지 규칙이 있습니다.
- 문자 또는 밑줄로 시작해야 함
- 공백 또는 특수문자 불가
- 255자까지만 사용
- A1, B2, R1C1 등 셀범위를 표현하는 값을 이름으로 사용 불가
- 영어 대소문자 구분은 하지 않음
이름의 범위
이름을 처음 만들 때 범위를 별도로 지정하지 않는 한 통합 문서 범위로 만들어집니다. 하나의 엑셀 파일 내의 어느 시트에서나 통합문서 범위로 만들어 이름을 참조할 수 있습니다.
[이름관리자]를 보면 앞에서 만든 이름 '인센티브율'이 통합 문서 범위로 만들진 것을 확인할 수 있습니다.
'이름이 참조하고 있는 시트'를 복사하면 '통합 문서' 범위의 이름이 워크시트 범위의 이름으로 복사됩니다. 복사되지만 동일한 이름으로 만들어집니다.
이 이름을 다른 시트에서 참조하려면 이름 앞에 시트명과 !를 붙여야 합니다.
=C14*'셀,범위 이름정의 (2)'!인센티브율
범위가 달라서 문제가 되지는 않지만 이름이 동일하므로 혼란스러울 수 있습니다. 이름이 있는 시트를 복사할 때 이러한 점을 미리 고려해야 합니다. 워크시트 범위의 이름이 필요 없다면 삭제하는 것이 좋습니다.
3. 응용하기
범위에 이름 붙이기
실습: [셀,범위 이름정의] 시트
다음 예시는 VLOOKUP 함수로 값을 찾아올 때 함수의 두 번째 인수 table_array에 [$B$22:$C$31] 범위를 지정하는데 범위가 눈에 잘 들어오지 않습니다.
=VLOOKUP(B35,$B$22:$C$31,2,FALSE)
[$B$22:$C$31] 범위를 VLOOKUP함수에 직접 입력하는 대신 '상품정보'라는 이름을 정의해서 사용해 보겠습니다.
01 ① [셀,범위 이름정의] 시트에서 [$B$22:$D$31] 범위를 선택하고 ② [수식] 탭 → [정의된 이름] 그룹 → [이름 정의]를 눌러 [새 이름] 대화상자를 실행한 후 ③ [이름] 필드에 '상품정보'를 입력하고 ④ <확인>을 눌러 이름을 만듭니다.
02 이제 다음과 같이 [$B$22:$D$31] 범위 대신 '상품정보'라는 이름을 입력하여 상품명을 가져올 수 있습니다.
범위를 직접 입력하는 수식보다 훨씬 이해하기 쉬운 수식이 되었습니다.
=VLOOKUP(B43,상품정보,2,FALSE)
상수(숫자, 텍스트)에 이름 붙이기
실습: [상수,수식 이름정의] 시트
이름은 셀, 범위 뿐만 아니라 숫자, 텍스트 등 상수 값에도 붙일 수 있습니다. 해마다 바뀌는 최저시급(2023년에는 9620원)을 이름으로 정해서 사용해 보겠습니다.
01 ① [수식] 탭 → [정의된 이름] 그룹 → [이름 정의]를 눌러 [새 이름] 대화상자를 실행한 후 ② [이름] 필드에 '최저시급2023'을 입력하고 ③ [참조 대상]에 =9620을 입력하고 ④ <확인>을 눌러 이름을 만듭니다.
02 만들어진 이름은 =최저시급2023을 셀에 입력하여 바로 참조할 수 있습니다. 이렇게 구해진 시급에 근무시간을 곱하면 근무자별 시급 합계를 구할 수 있습니다.
03 업무에서 많이 입력하는 회사주소도 이름으로 만들어 놓고 사용할 수 있습니다.
① [수식] 탭 → [정의된 이름] 그룹 → [이름 정의]를 눌러 [새 이름] 대화상자를 실행한 후 ② [이름] 필드에 '나나문구주소'를 입력하고 ③ [참조 대상]에 주소를 입력한 후 ④ <확인>을 눌러 이름을 만듭니다.
04 만들어진 이름은 다음과 같이 =나나문구주소를 셀에 입력하여 바로 참조할 수 있습니다.
수식에 이름 붙이기
실습: [상수,수식 이름정의] 시트
이번에는 수식에 이름을 붙여보겠습니다.
01 ① [수식] 탭 → [정의된 이름] 그룹 → [이름 정의]를 눌러 [새 이름] 대화상자를 실행한 후 ② [이름] 필드에 '월주차'를 입력하고 ③ [참조 대상]에 다음 수식을 입력하고 ④ <확인>을 눌러 이름을 만듭니다.
=MONTH(TODAY()) & "월 " & WEEKNUM(TODAY(),1)-WEEKNUM(DATE(YEAR(TODAY()),MONTH(TODAY()),1),1)+1 & "주차"
02 =월주차를 셀에 입력하면 현재 날짜 기준(예시는 2022년 12월 27일)으로 ‘월 주차'를 구해줍니다.
엑셀 수식 강좌
[ 수식 다루기 ]
[ 엑셀에서 입력과 표시방법 ]
[ 수식에서 셀과 범위 참조 방식 ]
[ 엑셀을 제대로 쓰는 데이터처리 ]
- 엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
- 수식으로 한 번에 텍스트 나누기
- 엑셀 고급필터로 다양하게 자료 필터링하기
- 정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
- 엑셀 오류데이터 처리하기
- 파워 쿼리(Power Query) 소개
- 파워쿼리로 외부 데이터 가져오기
- 엑셀에서 중복 데이터 제거하기
- 엑셀 텍스트 나누기의 모든 것
- 이동옵션으로 원하는 셀 한번에 선택하고 처리하기
- 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
- 사용자 지정 수식으로 데이터 유효성 검사하기
- 데이터 유효성 검사로 허용된 데이터만 입력
- 엑셀 데이터 다루기 첫 단추는 데이터 구조화
[ 엑셀 이름정의와 표기능 ]
[ 수식을 분석하고 오류 해결하기 ]
- 엑셀 순환참조 문제 해결하기
- 수식 계산 단계 알아보기
- 엑셀 수식 오류 검사하고 추적하기
- 엑셀에서 참조 셀 추적하기
- 엑셀 오류 유형(#DIV/0!, #N/A, #NAME?, #REF! 등)과 해결방법
셀 이름 정의와 관련된 규칙을 확인할 수 있어서 감사했습니다.