엑셀에서 시트별로 통합 시트에 불러오고 싶습니다

포스트 목차.

• 01. 문제
• 02. 문제해결방법
• 03. 예제

 

 

01. 문제

Q. 엑셀에서 시트별로 통합 시트에 불러오고 싶습니다. 시트에 A열은 카테고리명, B열은 상세 내용입니다.

1번 시트(Apple I)의 내용

2번 시트(MacBook Pro)의 내용

통합시트의 1열에는 1, 2 시트에 있는 카테고리가 있는데요, 가로정렬입니다. A열에 해당 시트명을 기재하면 위 1, 2 번 시트에 있는 내용들이 자동으로 카테고리명에  맞게 불러오는 수식이 있을까요?

 

문제를 정리하면 다음과 같다.

1. 카테고리열과 상세정보열로 구성되는 데이터 시트가 여러 개 있다.

2. 통합시트의 A열에는 시트명이 들어간다.

3. 통합시트의 1열에는 데이터 시트에 있는 카테고리 명이 들어간다.

4. 시트명과 카테고리명을 참고하여 데이터 시트를 조회하여 상세정보를 가져오는 수식을 만들자

 

02. 문제 해결 방법

시트가 하나만 있으면 lookup 계열의 함수 또는 Index/Match로 해결할 수 있을 것이지만, 이 문제의 경우 데이터가 시트로 구분되어 있다. 통합시트의 A열의 정보에 따라 다른 시트를 조회해야 한다.

 

일단 데이터 조회는 Vlooup으로 해결해보자. Vlookup으로 해결한다는 가정하에, Vlookup의 두번째 인자인 Array 부분을 통합시트 A열의 값에 따라 동적으로 구성해야 한다.

 

(1) 동적으로 주소만들기

주소는 문자열로 작성해도되고 ADDRESS 함수를 사용하여 작성해도된다.

 

- Row_num: 행번호

- Column_num: 열번호

- Abs_num: 1:절대주소, 2: 절대행상대열주소 , 3: 상대행절대열주소, 4: 상대주소

- A1: 주소타입 (True일 경우 A1형태, False일 경우 R1C1형태) 

- Sheet_text: 주소에 들어갈 시트명

 

(2) 주소에 있는 값 참조하기

Vlooup 함수의 두 번째 인자는 array로 입력해야 된다. 즉 시작주소:끝주소 형태의 Range 형태로 입력해야된는데, 이 부분에 텍스트 값을 그대로 사용하면 오류가 발생한다. 

 

수식에 Range 형태(Array)로 입력하기 위해서는 Indirect함수를 사용할 수 있다.

- Ref_text: 주소로 들어갈 텍스트

- A1: 주소타입 (True일 경우 A1형태, False일 경우 R1C1형태) 

 

(3) Vlookup으로 조회하기

Vlookup은 특정 값을 특정 배열에서 찾아오는 역할을 수행한다.

 

참조할만한 포스트

https://eggdrop.tistory.com/89

XLOOKUP 함수 사용 방법 (Excel 2021~)

 

https://eggdrop.tistory.com/14

Excel [지식인 해결 시리즈] 001. lookup 함수를 이용한 양도세 구간 구하기

 

03. 예제

(1) 주소 만들기 예제

Vlookup함수의 두번째 인자로 조사할 배열이 입력되어야 한다.

여기서는 각각의 시트이 A1:B100의 영역을 입력한다고 가정하자.

 

시작주소

종료 주소

 

 

(2) 주소 참조 예제

 

VLOOKUP 함수의 두번째 인자로 미리 제작해 둔 주소 두개를 INDIRECT함수를 이용하여 입력할 수 있다.

단 여기서 배열(Range)형태로 입력해야 되기 때문에 두개의 INDIRECT함수 사이에 콜론을 입력해주자

 

(3) 하나의 수식으로 표현하기

 

VLOOKUP 함수를 완성해보자

ADDRESS 함수의 입력으로 시트명 정보가 있는 A열 셀이 입력된다.

또한 1행에 있는 카테고리 정보가 찾을 값으로 들어간다.

=VLOOKUP(E1,INDIRECT(ADDRESS(1,1,1,1,$A2)):INDIRECT(ADDRESS(100,2,1,1,$A2)),2,FALSE)

 

추출한 값은 숫자형태이지만, Introduced 의 상세정보는 날짜로 표기해야 되기때문에 셀서식의 표기형식을 날짜로 변경해주면 Apple I 시트에 있는 Idtroduced 값이 정상적으로 조회되는것을 확인할 수 있다.