[엑셀 실무] 엑셀의 여러 참조 방법을 살펴봅니다

엑셀에서 동일 시트의 셀과 다른 시트의 셀, 더 나아가 다른 워크북의 셀을 참조해 보려 합니다. 어떻게 할 수 있을까요?
이번 게시물에서 다룰 내용은 상대, 절대, 혼합 등의 참조 스타일이 아닙니다. 이점 확인 바랍니다.

며칠전 [엑셀 문답] 게시물에서 잠깐 3차원 참조를 다룬 적이 있습니다. 엑셀에서 동일 시트를 넘어서는 범위를 참조하는 것을 엑셀 3차원 참조라고 부르고 있습니다. 이번 게시물은 이를 주제로 살펴봅니다.

1. 엑셀 셀 참조.
2. 엑셀 열 참조, 행 참조.
3. 엑셀 다른 시트 참조.
4. 엑셀 다른 워크북(통합문서) 참조.

1. 엑셀 셀 참조

엑셀에서 동일 시트 셀 참조는 너무 많이 해 봤을 것입니다. 예를 하나 살펴 봅니다.

문.
"서울 고궁 입장객 현황" 표에서 "경복궁" 의 성인, 청소년, 어린이 입장객 수를 더해봅니다. 합을 구하는 함수 SUM 함수를 사용합니다.

[그림1] 엑셀 일반적인 셀 참조

답.
=SUM( C4, D4, E4 )
=SUM( C4:E4 )

두 수식은 문제에서 요구하는 동일한 결과 39,403 을 얻게 됩니다. 참조 셀들이 동일 표시 형식을 사용하고 있기에 결과 셀도 표시 형식이 복사됩니다. 첫 번째는 합을 구할 각 셀들의 주소를 전달하는 수식이고 두 번째는 합을 구할 범위(시작셀:종료셀)를 전달한 수식입니다.
이것이 엑셀의 셀 참조의 전형적인 예입니다. 대부분의 작업을 이런 형태로 하고 있을 것입니다.

2. 엑셀 열 참조 와 행 참조

이번에는 엑셀에서 열을 참조해 봅니다. 엑셀에서 열 주소와 행 주소가 교차하는 지점이 셀 주소가 됩니다. 이렇게 얻은 셀 주소를 참조할 수 있다면 열 주소도 참조할 수 있지 않을까요? 역시 예제로 살펴봅니다.

문.
"서울 고궁 입장객 현황" 2월 표에서 각 고궁들의 청소년 입장객의 합을 구합니다.

[그림2] 엑셀 열 참조와 행 참조

답.
=SUM( D4:D8 )
=SUM( D:D )

위의 두 수식도 이번의 경우에는 동일한 결과를 얻습니다. 하지만 두 수식은 항상 동일한 결과를 얻는 것은 아닙니다.
첫번째 수식은 D4:D8 의 범위의 합을 구하는 것입니다. 청소년 입장객 수가 나열된 범위입니다.
두번째 수식은 D:D 로 표시되어 있습니다. 셀 주소는 열 주소와 행 주소가 교차하는 지점이라고 말했습니다.
헌데 이 주소에는 행 주소가 없습니다. D:D , D 열 전체를 참조하겠다는 것이지요. 위의 시트에서는 표를 제외한 D 열의 다른 행에는 수치가 없기에 첫번째 수식과 동일한 결과를 얻습니다. ( 참고.. SUM 함수에서 빈셀은 0으로 처리되고 텍스트 문자열은 계산에서 제외됩니다 )

자, 이제 행 참조를 해 볼까요?

문2.
위 [그림2] 에서 창덕궁 총 입장객수를 구해봅니다.

답2.
=SUM( C7:E7 )
=SUM( 7:7 )

위의 두 수식 역시 이번의 시트에서는 동일 결과를 얻습니다.
첫번째 엑셀 수식은 창덕궁 입장객 범위 C7:E7 의 합을 구하는 것이고 두번째 수식의 경우는 7:7 의 엑셀 참조 주소로 7 번 행 전체를 참조합니다. 두번째 엑셀 수식에서 A7은 빈셀이라서 0으로 처리되고 B7 은 문자열이라서 계산에서 제외 되었습니다.

이것이 엑셀 행 참조와 엑셀 열 참조의 방법입니다. 행 참조를 할 때는 행 주소만이 사용됩니다. 열 참조시는 열 주소만이 사용되는 것이구요. 쉽지요?

3. 엑셀 다른 시트 참조하기

드디어 엑셀의 3차원 참조입니다. 엑셀에서 말하는 3차원 참조란 수식이 입력되는 시트의 셀 주소가 아닌 다른 시트의 셀 주소를 참조하는 것을 말합니다. 이를 3차원 참조라 부르는 이유는 각 시트들을 서류 모으듯 포개어 놓으면 그 주소 체계는 동일하기 때문입니다. 시트 한 페이지가 행과 열의 2 차원이기에 시트들을 위에 얻어 놓으면 3차원이 되는 것이지요.

문3.
위의 [그림1], [그림2] 를 참고하여 시트1 (1월) , 시트2 (2월) , 시트3 (3월) 에서 덕수궁의 성인, 청소년, 어린이 입장객의 합계를 구합니다.

[그림3] 엑셀 다른 시트 참조하기 - 3차원 참조

답3.
=SUM( Sheet1!C6:E6, Sheet2!C6:E6, Sheet3!C6:E6 )
=SUM( Sheet1:Sheet3!C6:E6 )

위의 두 수식은 언제나 동일한 결과를 가져옵니다.
첫번째 수식은 시트마다의 참조 범위를 각각 가져왔습니다. 1월, 2월, 3월의 덕수궁 입장객 범위를 콤마( , )로 구분하여 참조하고 합을 구한 것입니다.
두번째 수식을 살펴 봅니다. 이런 형태의 참조는 처음 보는 이가 많을 것입니다. 이것이 진정한 엑셀 3차원 참조입니다. Sheet1 에서 부터 Sheet3 까지의 C6:E6 셀들을 참조하는 것입니다. 셀들의 범위가 C6:E6 로 동일하기에 시트들을 겹쳐놓고 보는 것입니다.

노트1. 엑셀에서 콜론( : ) 은 시작지점과 종료지점을 연결하는 참조 연산자입니다. 이때 지점이란 셀이 될 수도 시트가 될 수도 있습니다. 또 느낌표( ! ) 는 시트와 셀을 가르는 엑셀 참조 연산자 입니다.

4. 엑셀 다른 워크북 참조

워크북이란 말을 처음 듯는 이도 많을 것입니다. 우리가 엑셀을 실행하면 기본으로 제목 표시줄에 Book1 이라고 표시됩니다. WorkBook 의 -Book 입니다. 이는 한글 엑셀에서는 통합 문서로 표현 됩니다. 즉 다른 엑셀 통합 문서를 참조할 수 있다는 것입니다.

문4.
엑셀 메인 메뉴 > 새로 만들기 > 새 문서 > 새 통합 문서 를 선택하여 또 다른 통합문서(WorkBook) 를 만듭니다. 이 시트에서 문제 들어 갑니다. 위 1,2,3 의 표가 입력된 워크북(통합문서)의 시트1,시트2,시트3 의 창경궁 입장객 수를 구해봅니다.

[그림4] 엑셀 다른 워크북 참조

답4.
참조 대상이 되는 워크북이 저장되지 않았을 때는 다음과 같은 수식으로 위의 문제를 해결할 수 있습니다.
=SUM( [Book1]Sheet1:Sheet3!C5:E5 )

먼저 워크북을 구분하는 참조 연산자(기호) 가 대괄호( [ ] ) 라는 것을 기억합니다. 나머지는 다른 워크시트 참조 때와 동일합니다. 이렇게 다른 워크북의 셀을 참조할 수 있습니다.
위의 수식은 BOOK1 이라는 통합문서에 파일명이 부여되기 이전의 수식입니다. 즉 저장전의 워크북을 참조하면 위 수식의 형태로 표시됩니다.
이후 저장하고 나면 위의 수식은 자동으로 다음의 형태로 바뀝니다.
=SUM( '[엑셀 3차원 참조.xlsx]Sheet1:Sheet3'!C5:E5 )

[Book1] 이 [파일명.확장자] 로 바뀌고 워크북과 시트영역에 작은따옴표( ' ' )가 감싸집니다. 이것이 워크북을 참조하는 방법입니다. 직접 수식을 입력하셔도 좋고 함수식 입력시 셀 선택을 하셔도 좋습니다.

이상이 엑셀에서의 셀참조 범위와 방법들입니다. 엑셀에서 우리가 모르고 있는 부분이 많습니다. 조금씩 알아가시면 재미도 있으면서 알게 모르게 엑셀 실무에 많은 업무효율을 가져올 수 있습니다.

비교를 위해 다른 엑셀 참조 방법들이 많이 보였지만 이 게시물의 주제는 엑셀 3차원 참조입니다. 활용하시기 바랍니다.

[엑셀 문답] 엑셀 다른 시트 값을 참조하여 계산하려면
[실무 엑셀] 엑셀의 기본적인 연산자와 기호의 의미

마늘빵.