여러 시트의 셀 값 불러오는 INDIRECT / ROW 함수

이번 포스팅에서는 여러 시트의 셀 값을 불러오는 INDIRECT / ROW 함수 에 대해 설명하겠습니다.

데이터가 많은 경우 여러 시트에 나눠 관리하는 경우가 많이 있습니다. 소속, 주민번호, 월급여 등의 데이터를 개별 시트에서 관리하게 되는데 이럴 경우 데이터를 하나의 시트에 불러올 때 문제가 발생합니다.

셀 주소를 참조하여 여러 시트의 셀 값을 불러오는 방법에 대해서 설명하겠습니다.

여러 시트의 셀 값 불러오는 INDIRECT / ROW 함수 _일반적인 방법

다른 워크시트에 데이터를 불러오는 방법은 어렵지 않습니다.

함수를 사용하여 할 수도 있고 직접 지정하여 불러올 수도 있습니다. 여기서는 직접 불러오는 방법에 대해 설명드리겠습니다.

1. 입력할 셀에 '='을 입력합니다.
2. 찾고자 하는 데이터가 있는 시트를 클릭합니다.
3. 불러올 데이터를 클릭하고 엔터를 칩니다.

 

이미지와 같아 '=소속!C2'의 값 '영업1팀'을 불러왔습니다.

채우기 핸들을 더블클릭하면 자동으로 비어 있는 셀의 값을 채울 수 있습니다.

다만, 이렇게 데이터가 많지 않은 경우에는 간편하게 사용할 수 있지만 데이터가 많은 경우에는 사용하기 힘듭니다. 또한 '주민번호', '월급여'의 데이터는 다른 시트에 있기에 다시 1~3번을 반복해야 합니다. 이럴 경우 INDIRECT 함수를 사용해야 합니다.

여러 시트의 셀 값 불러오는 INDIRECT / ROW 함수 이해

INDIRECT 함수

셀 주소에 입력된 값을 추출하는 함수입니다. 다른 워크시트의 셀 주소를 입력하려면 셀 주소 앞에 시트의 이름과 느낌표(!)를 같이 입력해야 합니다.

=INDIRECT(시트 이름!셀주소)

 

유재석의 소속, 주민번호, 월급여을 다른 시트에서 불러오겠습니다.

 

1. C2셀에 '=INDIRECT(C$1&"!C2")'을 입력합니다.
   - C$1 : 열 이름 '소속'(C1)은 시트 이름 소속과 같습니다. 채우기 핸들을 사용하기 위해 행을 고정하였습니다.
   - & : 다른 시트의 셀을 참조할 경우 시트 이름 뒤에 느낌표(!)를 추가합니다. 즉, &(앰퍼샌드)로 문자가 입력된 셀을 연결하였습니다.
   - !C2 : 소속 시트에 소속은 C2셀에 입력되어 있습니다. 느낌표와 알파벳이기에 큰 따옴표를 사용하였습니다.
2. 채우기 핸들을 사용하면 소속, 주민번호, 월급여의 값을 채울 수 있습니다.
   다만, 아래는 참조값이 다르기에 채워지지 않습니다. 이럴 때 ROW 함수를 함께 사용해야 합니다.

여러 시트의 셀 값 불러오는 INDIRECT / ROW 함수 _활용

중첩하여 사용하는 방법을 설명드리기 전에 먼저 ROW 함수에 대해서 설명드립니다.

1. ROW 함수

참조하는 행 번호를 구할 수 있습니다.

=ROW()

• ROW() : 수식이 표시되는 행 번호를 반환합니다. 예를 들어 2번째 행에 있으면 2를 반환합니다.
• ROW(B3) : 위의 이미지에서 'ROW(B3)'를 입력하면 결과값 3을 구할 수 있습니다.

2. INDIRECT / ROW 함수의 사용

셀 주소(행 번호)를 ROW 함수로 자동 변경하여 열 값 뿐만 아니라 행 값까지 불러오도록 하겠습니다.

 

1. C3셀에 '=INDIRECT(C$1&"!C"&ROW())'을 입력합니다.
   - 위와 동일한 함수에 셀 주소의 숫자만 변경될 수 있도록 '&ROW()'를 추가하였습니다. ROW()가 행 번호를 자동으로 반환을 하면서 C2, C3, C4와 같이 셀 주소의 값이 변경됩니다.
2. 채우기 핸들로 행/열을 모두 채우면 자동으로 정확한 값을 불러올 수 있습니다.

이상 여러 시트의 셀 값을 불러오는 INDIRECT / ROW 함수 에 대해 설명하였습니다.

이렇게 모든 열의 값을 나눠서 별도의 시트를 만드는 일은 회사 업무 상황에서 많지 않습니다.

감사합니다.