엑셀 함수] INDIRECT 함수를 사용하여 특정 시트의 값 조회하기

안녕하세요, 짤막한 강좌 이충욱 강사입니다. 

 

이번 강좌는 구독자 윤**님의 질문을 바탕으로 INDIRECT 함수를 사용하여 특정 시트에서 값을 조회하는 방법을 소개하겠습니다. 

강좌로 만들어 많은 분들과 공유할 수 있게 데이터 사용을 허락해 주셔서 감사합니다. 

 

실습 파일 다운로드

INDIRECT함수_실습파일.xlsx

0.02MB

완성 파일 다운로드

INDIRECT함수_완성파일.xlsx

0.02MB

실습 파일을 다운로드 받아 실습해 보세요.

함수를 쉽게 익힐 수 있습니다. 

 

 

파일을 열어보면 작업 시트와 각 회사명으로 된 4개의 시트가 있습니다. 

시트 형태는 모두 동일하고 회사별 데이터만 다르게 되어 있습니다. 

실제 사용하는 데이터는 200개 정도의 회사가 있다고 하셨는데, 예제 파일은 4개 회사만 예시를 들고 있습니다.

 

 

이제부터 처리하려는 작업은 다음과 같습니다. 

 

1. 작업 시트 [A1] 셀에 회사명을 선택하는 목록 버튼을 만들어 A사에서 D사까지 선택할 수 있도록 하려고 합니다. 

2. 회사명을 선택하면 해당하는 회사 시트에서 영업 이익, 매출액, 유동자산, 유동부채 등의 데이터가 자동으로 조회되어 표시되도록 하려고 합니다. 

 

 

1. 회사명을 선택하는 목록 상자 만들기

 

[A1] 셀을 선택하고 [데이터] 탭 - [데이터 도구] 그룹 - [데이터 유효성 검사]를 선택합니다. 

[제한 대상]을 [목록]으로 선택하고, [원본] 입력창에 회사명을 쉼표로 구분하여 입력한 후 [확인]을 누릅니다. 

 

 

[A1] 셀에 목록이 만들어 졌습니다. 

 

유효성 검사 목록을 설정할 때 원본을 직접 입력하지 않고 다른 방법으로 설정하는 방법도 있습니다. 

200개쯤 되는 회사명을 일일이 원본에 입력해 사용하는 것은 불편하겠죠.

다양한 입력 방법은 아래 링크를 클릭하여 기존 데이터 유효성 검사 강좌를 참고하세요. 

https://hantip.net/64

 

 

2. 수식 작성

 

이제 회사명을 선택할 때마다 값이 바뀌도록 수식을 작성해보겠습니다.

[B2] 셀을 선택하고 이퀄(=)을 입력합니다. 

A사 시트를 선택한 후 [B2] 셀을 선택하고 <Enter> 를 누릅니다.

 

[B2] 셀에 작성된 수식을 확인해 보겠습니다. 

=A사!B2

A사!는 A사 시트를 의미합니다.

수식에서 시트 이름에 !(느낌표)를 붙여 시트를 나타냅니다.

즉, [B2] 셀 수식은  A사 시트 B2 셀의 값을 대입하라는 의미입니다. 

하지만 우리가 처리하려고 하는 작업은 [A1] 셀의 목록에서 B사를 선택하면 B사!B2가 되도록, C사를 선택하면 C사!B2가 되도록 만들어야 합니다. 

 

수식에서 A사 대신 A1 셀로 변경합니다. 

=A1!B2

그럼 수식은 =A1!B2 이 됩니다. 

이렇게 되면 A1이라는 이름의 시트는 없기 때문에 '값 업데이트: A1' 대화 상자가 표시되고 파일을 선택하라고 합니다.

[취소]를 누르면 '#REF!' 주소 선택이 잘못되었다는 오류 메시지가 표시됩니다.

 

 

[B2] 셀의 수식을 수정해보겠습니다. 

목록에서 선택된 회사명이 A사라면 A사와 시트를 의미하는 !, 그리고 B2 라는 텍스트가 표시 되도록 수식을 작성해야 합니다. 

아래와 같이 수식을 수정합니다.

=A1 & "!B2"

[B2] 셀에 =A사!B2로 결과가 구해집니다.  

 

하지만 원하는 결과는 A사!B2(A사 시트에 B2 셀)의 내용이 표시되는 겁니다. 

바로 이경우 INDIRECT 함수를 사용하면 됩니다. 

INDIRECT 함수는 문자열을 셀 주소로 변환해 주는 역할을 합니다.

 

 

다시 수식을 수정하겠습니다. 

=INDIRECT(A1 & "!B2")

 

수식을 수정하고 <Enter>를 누르면 A사 시트 [B2] 셀에 해당하는 영업이익(t)을 표시합니다. 

작성한 수식을 [B3:B6], [E2:E6] 셀에 복사해 나머지 결과도 구하겠습니다.

이때 A1 셀은 <F4>를 눌러 절대 참조합니다. 

수식을 복사하더라도 참조 셀은 변하면 안 됩니다. 

 

=INDIRECT($A$1 & "!B2")

 

수식을 채우기 했더니 모두 [B2] 셀에 해당하는 30이 채워집니다. 

 

수식 내용 중 B2는 셀 주소가 아니라 문자이기 때문에 참조 셀이 변하지 않습니다. 

B2를 B3으로, B4로 직접 변경해줘야 합니다.

 

=INDIRECT($A$1 & "!B3")

 

[E2] 셀에도 수식을 복사해서 열 문자를 E로 변경합니다. 

나머지 수식도 모두 변경해 줍니다. 

 

목록을 선택해 B사로 변경해보면 B사 시트 내용으로 모두 변경되는 것을 확인할 수 있습니다. 

 

필요하신 분들께 도움이 되길 바라며 강좌는 마치겠습니다.