요약 06. 이름정의를 이용한 참조범위 지정하기

요약 기능 06. 이름정의를 이용한 참조범위 지정하기

엑셀에서 많은 수식을 작성하거나 복잡한 수식을 만들게 되면 어느 영역이 어떤 목록이었는지 알기가 힘든 경우가 있는데, 엑셀에서 지원하는 ‘이름정의’를 이용하면 수식을 간략하게 작성할 수 있고 수식의 내용을 이해하기가 쉽습니다. 이름정의란 특정 셀이나 범위 수식 등을 알기 쉽게 별명을 지정하는 것입니다.

① 분기별 금액을 구하기 위해서 [E5]셀을 선택하고 [삽입-이름-정의] 메뉴를 선택합니다.

엑셀 2007 에서는 수식 탭의 정의된 이름그룹에 있습니다. 그리고 수정/편집도 쉽습니다.

② [이름 정의] 대화상자가 나오면 ‘통합 문서에 있는 이름’ 입력상자에 ‘매출1기’를 입력,  ‘참조 대상’의 입력란의 참조 대상 영역을 [B5:B7]영역을 선택하고, 추가 단추()를 클릭합니다.

(1) 통합 문서에 있는 이름 : 이름정의로 사용하고자 하는 문자열을 입력합니다. 이름(별명)은 문자나 밑줄로 시작되어야 하며, 나머지 글자는 문자, 숫자, 마침표, 밑줄 등이 될 수 있지만 Z$100이나 R1C1과 같은 셀 주소를 사용할 수 없습니다.

(2) 참조 대상 : 이름으로 사용할 영역, 수식을 입력합니다. 반드시 ‘=’ 기호로 시작합니다.

(3) (확인) : 마지막에 지정한 이름을 추가로 저장하고 [이름정의] 대화상자를 닫습니다.

(4) (닫기) : 마지막에 지정한 이름은 추가하지 않고 현재 상태에서 이름정의 대화상자를 닫습니다.

(5) (추가) : 현재 지정한 ‘이름정의’ 내용을 저장하고 [이름정의] 대화상자를 닫지 않습니다.

(6) (삭제) : 선택한 이름정의 내용을 삭제합니다.

③ 같은 방법으로 이름정의를 추가합니다.

이름 매출2기 매출3기 매출4기

참조 대상 월별 매출액 시트의 [B8:B10] 영역 월별 매출액 시트의 [B11:B13] 영역 월별 매출액 시트의 [B14:B16] 영역

④ 1/4분기의 합계를 구할 [E5]셀을 선택하고 ｢=SUM(매출1기)｣로 입력합니다.

｢=SUM(매출1기)｣의 수식은 ‘매출1기’ 영역의 합계를 구하는 수식으로

[B5:B7]영역을 ‘매출1기’로 이름정의를 하였으므로 ｢=SUM(B5:B7)｣로 계산을 합니다.

※ [이름정의] 의 편리함

단순하게 셀에 사용할 수식 ｢=SUM(B5:B7)｣을 살펴보면 어떤 값을 계산을 한 것인지 알기 어렵지만

｢=SUM(매출1기)｣의 수식은 ‘매출1기’ 영역의 합계를 구하는 수식임을 쉽게 알 수 있습니다.

⑤ [E6]셀에 ｢=SUM(매출2기)｣, [E7]셀에 ｢=SUM(매출3기)｣, [E8]셀에 ｢=SUM(매출4기)｣로 입력해서 각 분기별 합계금액을 구한 다음,

⑥ 분기별 평균 매출액을 구하기 위해서 [E12]셀을 선택하고, 표준 도구 모음의 자동합계 의  드롭다운 단추()를 클릭해서 [평균]메뉴를 선택합니다.

⑦ 수식이 자동으로 =AVERAGE(E5:E11)로 만들어 지면 [삽입-이름-붙여 넣기] 메뉴를 선택합니다. 엑셀 2007 에서는 수식 탭의 정의된 이름그룹- 수식에서 사용을 선택합니다.

⑧ 이름 붙여넣기 대화상자가 나오면 ‘매출1기’를 선택하고 [확인] 단추를 클릭합니다.

※ Tip 이름정의를 많이 한 경우나 이름정의 목록이 기억이 나지 않을 경우 간편하게 이름을 입력할 수 있습니다.

⑨ [E12]셀의 수식이 ｢=AVERAGE(매출1기)｣로 지정된 것을 확인하고 엔터를 입력합니다.

⑩ [E13]셀에 ｢=AVERAGE(매출2기)｣, [E14]셀에 ｢=AVERAGE(매출3기)｣, [E15]셀에 ｢=AVERAGE(매출4기)｣로 입력해서 각 분기별 평균 매출액을 구한 다음,

⑪ [삽입-이름-정의] 메뉴를 선택하고 [이름 정의] 대화상자가 나오면, 이름을 ‘총매출’로 지정하고 참조 대상을 ｢=SUM(매출1기,매출2기,매출3기,매출4기)｣로 입력하고 [확인] 단추를 클릭합니다.

※ Tip : 이름정의는 수식을 계산해서 결과를 기억할 수 있습니다.

⑫ [E17]셀을 선택하고, ｢=총매출액｣을 입력해서 총 매출액을 계산합니다.

엑셀 2007 에서는 (1) 이름을 수정하면 그 이름이 사용된 셀의 수식도 함께 변경됩니다. (2) 이름정의 목록을 보는 이름상자의 크기 조정이 가능합니다. (3) 여러 개의 이름을 선택하고 한 번에 지울 수 있습니다.

=가= 이름정의와 문자열 구분하기

이름정의가 된 하나의 셀 [AL13]을 선택하면 이름상자에 이름이 나타납니다.

이름정의가 된 영역 [AK4:AL9]을 선택하면 이름상자에 이름이 나타납니다.

이름정의를 수식입력줄 혹은 셀의 수식을 보면 정의된 이름에는 "" 기호가 붙지 않지요. 그리고 함수마법사 단추를 누르면 이름정의된 영역의 값이 차례차례 보이게 되지요.

공급가액을 이름정의한 [AL13]과 세액을 이름정의한 [AL14]를 이름정의 없이 사용하면 셀 주소를 잘못 사용하기 쉽겠습니다.

게다가 공급가액은 [G10~Q10]에서 사용하지만 세액은 [R10~AA10]셀에서 사용되어 바로 옆 셀에 엉뚱한 수식을 넣을 수 있습니다.

=나= 이름정의를 잘 사용하고 관리하기

이름 목록을 출력해서 보려면 삽입-이름 메뉴에서 붙여넣기를 선택합니다.

비슷한 영역 혹은 인접 영역은 머리글을 같게 함으로써 효율적인 관리가 쉽습니다.

=다= 매우 유용한 경우

고급필터를 사용할 경우 필터의 원본영역을 알아보기가 매우 어렵지요 대화상자가 너무 작아서 ^^ 이럴 경우 의미 있는 이름정의를 사용하면 매우 효율적입니다.

시트의 개수가 적을 경우는 무리가 없으나 시트의 수가 많을 경우 일일이 드래그 해서 사용하기에는 매우 번거로움이 따릅니다. -- 이를 해소할 수 있습니다

수식에서 셀 주소로 기록되는 것은 참조영역의 값이 무엇인지 기억하기가 곤란 할 수 있습니다. -- 이름을 적당히 짓기만 하면 오히려 나을 수 있지요

수식에 일정한 비율을 곱하고자 할 경우 -- 이름을 “곱할비율”, 참조대상을 “35.236%” 로 지정할 경우 수식 작성이 더욱 편리합니다.

무엇보다도 수식의 의미를 알기가 매우 용이하다는 점 이겠지요.

게다가 수식의 길이 또한 엄청 줄어 들 수 있을 거구요

아래와 같이 유동적인 범위를 설정하기도 매우 간단합니다.

정의할 이름 - 참조내용 _참조영역 =OFFSET(상품매입매출단가표!$A$4,0,0,_DATA행수,_DATA열수) _DATA행수 =COUNTA(상품매입매출단가표!$A:$A) _DATA열수 =6

보신 내용이 정리된 PDF 파일과 예제파일을 다운로드 후 보실 수 있습니다.
위 내용이 정리된 PDF 파일 invalid-file	필요한 예제 파일 및 기타 관련파일 invalid-file
따로 궁금하신 내용이나 기타 내용을 아래의 NO COMMENT 를 눌러서 글을 달아주시면 감사하겠습니다.