구조적 참조에 대하여

우리가 흔히 만드는 다음과 같은 표가 있습니다.

지점명	배정예산	1월 집행	2월 집행	3월 집행	집행합계	집행률
동부	2,000	723	629	630	1,982	99.1%
서부	2,200	863	816	786	2,465	112.0%
남부	3,000	519	550	893	1,962	65.4%
북부	2,500	789	135	742	1,666	66.6%
중부	3,300	1,030	1,084	1,650	3,764	114.1%

작업을 하다 보면 행이나 열을 계속 추가하거나 삭제하는 일이 빈번하게 발생하는데 그럴 때마다 작성된 수식을 매번 수정해야 한다면 갑갑한 노릇입니다.

이런 경우에 생각해 볼 수 있는 대안이 몇 가지 있습니다. (1) 수식에 이름을 정의해 두고 사용하거나 (2) 동적 이름 정의(Dynamic Naming)를 활용하는 것 등이 그것이지요.

엑셀 2007 버전에서는 일반적인 셀 참조(Reference) 방법 외에 구조적 참조를 이용하면 한결 편리하게 이런 류의 작업을 처리할 수 있습니다.

먼저 표를 만드는 방법에 대해 살펴보죠. 표를 만드는 방법은 크게 두 가지가 있습니다.

표 만들기

방법 1

데이터가 입력된 영역의 아무 셀이나 선택하고 '삽입 탭 - 표 그룹'에서 '표' 아이콘을 클릭합니다.

입력 범위와 '머리글 포함' 항목에 체크 표시를 한 다음 '확인' 버튼을 클릭합니다.

데이터 영역이 표 형태로 전환됩니다.

방법 2

'홈' 탭의 '스타일' 그룹에서 '표 서식' 아이콘을 클릭하면 갤러리 화면이 나타나는데 여기서 적당한 표 스타일을 선택해도 됩니다.

요약 행 만들기

표 내부의 셀을 하나 선택한 다음 '디자인' 탭의 '표 스타일 옵션' 그룹에서 '요약 행'을 클릭하여 체크 표시를 하면 표 아래쪽에 요약 행이 나타납니다.

요약 셀을 선택하면 드롭다운 버튼이 나타나며, 요약에 사용할 여러 가지 함수 중 하나를 선택할 수 있습니다.

구조적 참조를 이용한 수식 만들기

그냥 참조도 아니고 '구조적 참조'라고 하니까 뭔가 거창하고 복잡해 보이는데 하나도 어려운 것이 없으니까 지레 겁먹지 마시기 바랍니다. ^^

표의 데이터 범위가 자주 변경됨에 따라 셀 참조도 자동적으로 변경되는 것이 구조적 참조입니다.

쉽게 말해서 구조적 참조를 이용하여 수식을 만들어 두면, 행이나 열을 추가 또는 삭제할 때 수식을 매번 다시 작성할 필요가 없다는 얘기입니다.

말로 하니까 먼 소린지 잘 모르시겠지요?

Workplace 시트의 G열에는 집행금액 합계를 표시하고자 합니다. 따라서 일반적인 셀 참조의 경우라면 다음과 같은 수식이 사용됩니다.

=SUM(D4:F4)

그런데 Workplace 시트의 G12 셀에는 듣도 보도 못한 희안한 수식이 입력되어 있습니다.

=SUM(표2[@[1월 집행]:[3월 집행]])

구성 요소	의미
표2	현재 표의 이름을 의미합니다. 표를 삽입할 때마다 표1, 표2 등과 같은 표 이름을 엑셀이 알아서 자동으로 만들어줍니다.
@	현재 선택된 행을 뜻합니다. 엑셀 2007 버전까지는 "[#이 행]"이라고 표기되었으나 2010버전으로 넘어오면서 표기 방법이 변경되었습니다.
[1월 집행]:[3월 집행]	열 머리글의 이름입니다. 따라서 '1월 집행' 열부터 '3월 집행' 열까지의 합계를 구하라는 의미가 됩니다.

표 이름은 '디자인' 탭의 '속성' 그룹에 있는 '표 이름' 부분을 보면 알 수 있습니다.

만약 엑셀 2007이라면 위 수식은 이런 식으로 표현되었을 겁니다.

=SUM(표2[[#이 행],[1월 집행]:[3월 집행]])

다음으로, 집행률은 집행합계를 배정금액으로 나눈 것인데 우리가 사용하는 일반적인 방식으로 수식을 작성하면 엑셀이 알아서 다음과 같은 형태로 수식을 작성합니다.

=[@집행합계]/[@배정예산]

해석을 하자면, "현재 행의 집행합계 금액을 현재 행의 배정예산으로 나누어라."라고 할 수 있겠지요?

이 수식 또한 엑셀 2007에서라면 아래와 같이 표현되었을 겁니다.

=표2[[#이 행],[집행합계]]/표1[[#이 행],[배정예산]]

이렇게 만들면 뭐가 좋은가?

일반 셀 참조 방식으로 작성한 수식은 데이터의 범위가 변할 때마다 수시로 수식의 내용을 검토하여 수정해야 하나 구조적 참조로 작성된 수식은 자동으로 조정되므로 편리합니다.

특히 절대 주소나 혼합 주소가 포함되는 수식을 작성할 경우, 일반 셀 참조 방식에서는 일일이 <F4> 키를 눌러서 주소 형태를 변경해 주어야 하나 구조적 참조를 사용한 수식에서는 주소의 형태를 신경쓸 필요가 없습니다.

Workplace 시트의 K4 셀에 들어있는 수식과 다음 수식을 비교해 보세요.

=VLOOKUP(J12,표2,2,FALSE)

표 끝에 행 추가하기

요약 행이 없는 경우

요약 행이 없는 경우에는 다음과 같이 B17 셀에 내용을 입력하면 표의 범위가 자동으로 확장됩니다.

요약 행이 있는 경우

요약 행이 있는 경우라면 H16 셀을 선택한 다음 <Tab> 키를 누르면 됩니다.

표의 행이나 열을 삭제하는 방법은 일반적인 방법과 다르지 않습니다.

삭제할 행이나 열 전체를 선택한 다음 <Ctrl>키와 -키를 함께 누르거나 '홈' 탭의 '셀' 그룹에서 '삭제' 아이콘을 클릭한 다음 적합한 명령을 선택하시면 되겠습니다.

강좌에서 미처 설명드리지 못한 부분에 대해서는 엑셀의 도움말을 참고하세요. '구조적 참조'라는 단어로 검색해 보시면 됩니다.

오늘은 여기까지…

정리 — 표와 구조적 참조 활용법

작업	핵심 동작
표 만들기	삽입 탭 - 표, 또는 홈 탭 - 표 서식 갤러리에서 스타일 선택
요약 행 추가	디자인 탭 - 표 스타일 옵션 - 요약 행 체크
구조적 참조 수식	표 이름[@[열 이름]] 형태로 현재 행 기준 계산
표 끝에 행 추가	요약 행 없으면 바로 아래 셀 입력, 있으면 마지막 셀에서 Tab
표의 행/열 삭제	선택 후 Ctrl+- 또는 홈 탭 - 삭제에서 표 행/열 삭제 선택

자주 묻는 질문 (FAQ)

마치며

VBA에 대한 기초 지식을 공부하실 분은 아이엑셀러 닷컴 사이트 상단 메뉴에서 [Excel 강의] - [Excel 입문]을 먼저 보시면 이해하기 쉽습니다.