엑셀 다른 시트 값 여러 개 연속 가져오기

목차

• VLOOKUP 함수 활용하기
• INDEX와 MATCH 함수 조합하기
• INDIRECT 함수 활용하기
• 실전 예제: 여러 시트의 같은 셀 값 가져오기

 

안녕하세요, 여러분! 오늘은 엑셀에서 다른 시트의 값을 한꺼번에 연속으로 가져오는 방법에 대해 알아보겠습니다. 

VLOOKUP 함수 활용하기

엑셀에서 다른 시트의 값을 가져올 때 가장 많이 사용하는 함수 중 하나가 바로 VLOOKUP입니다. 이 함수를 사용하면 원하는 값을 손쉽게 찾아올 수 있어요.

VLOOKUP 함수란?

VLOOKUP은 ''Vertical Lookup''의 약자로, 세로 방향으로 데이터를 검색하여 원하는 값을 반환하는 함수입니다. 예를 들어, 한 시트에 제품 목록이 있고, 다른 시트에서 해당 제품의 가격을 가져오고 싶을 때 유용하게 사용할 수 있습니다.

VLOOKUP 함수 사용법

1. 기본 문법:
   • 검색값: 찾고자 하는 값입니다.
   • 범위: 검색할 데이터의 범위입니다.
   • 열 번호: 반환하고자 하는 값이 위치한 열의 번호입니다.
   • [정확도]: 정확한 일치를 원하면 FALSE, 유사한 일치를 원하면 TRUE를 입력합니다.
2.  
   =VLOOKUP(검색값, 범위, 열 번호, [정확도])
3. 다른 시트에서 값 가져오기:
   • 예를 들어, ''제품정보'' 시트에서 ''A2'' 셀의 값을 기준으로 ''가격정보'' 시트에서 해당 제품의 가격을 가져오고 싶다면:여기서 가격정보!A:B는 ''가격정보'' 시트의 A열부터 B열까지의 범위를 의미합니다.
   •  
     =VLOOKUP(A2, 가격정보!A:B, 2, FALSE)
4. 여러 개의 값 연속 가져오기:
   • 만약 여러 개의 값을 한꺼번에 가져오고 싶다면, 해당 셀에 위의 VLOOKUP 함수를 입력한 후, 아래로 드래그하여 복사하면 됩니다. 이렇게 하면 각 행에 대한 값을 자동으로 가져올 수 있습니다.

INDEX와 MATCH 함수 조합하기

VLOOKUP 함수도 좋지만, 경우에 따라 INDEX와 MATCH 함수를 조합하여 사용하는 것이 더 유연할 수 있습니다. 특히 검색하려는 값이 왼쪽에 있을 때 유용합니다.

INDEX와 MATCH 함수란?

• INDEX 함수: 지정한 행과 열의 교차점에 있는 값을 반환합니다.
• MATCH 함수: 특정 값이 범위 내에서 몇 번째 위치에 있는지를 반환합니다.

INDEX와 MATCH 함수 조합 사용법

1. 기본 문법:
   • 반환할 범위: 결과로 반환하고자 하는 값들의 범위입니다.
   • 검색값: 찾고자 하는 값입니다.
   • 검색 범위: 검색값을 찾을 범위입니다.
   • [정확도]: 정확한 일치를 원하면 0을 입력합니다.
2.  
   =INDEX(반환할 범위, MATCH(검색값, 검색 범위, [정확도]))
3. 다른 시트에서 값 가져오기:
   • 예를 들어, ''제품정보'' 시트에서 ''A2'' 셀의 값을 기준으로 ''가격정보'' 시트에서 해당 제품의 가격을 가져오고 싶다면:여기서 가격정보!B:B는 반환할 가격이 있는 열이고, 가격정보!A:A는 검색할 제품명이 있는 열입니다.
   •  
     =INDEX(가격정보!B:B, MATCH(A2, 가격정보!A:A, 0))
4. 여러 개의 값 연속 가져오기:
   • VLOOKUP과 마찬가지로, 첫 번째 셀에 위의 공식을 입력한 후 아래로 드래그하여 복사하면 여러 개의 값을 연속으로 가져올 수 있습니다.

INDIRECT 함수 활용하기

INDIRECT 함수는 셀 참조를 동적으로 만들어주는 함수로, 여러 시트에서 값을 가져올 때 유용하게 사용할 수 있습니다.

INDIRECT 함수란?

INDIRECT 함수는 텍스트 문자열로 지정된 참조를 실제 셀 참조로 변환합니다. 이를 통해 동적인 참조를 만들 수 있습니다.

INDIRECT 함수 사용법

1. 기본 문법:
   • 텍스트_참조: 참조하고자 하는 셀 주소를 텍스트로 입력합니다.
2.  
   =INDIRECT(텍스트_참조)
3. 다른 시트에서 값 가져오기:
   • 예를 들어, ''제품정보'' 시트에서 ''A2'' 셀에 ''가격정보'' 시트의 ''B2'' 셀 값을 가져오고 싶다면:여기서 작은따옴표는 시트 이름에 공백이 있을 경우 필요합니다.
   •  
     =INDIRECT(''가격정보''!B2)
4. 여러 개의 값 연속 가져오기:
   • 만약 여러 개의 값을 가져오고 싶다면, 셀 주소를 동적으로 생성하도록 설정하면 됩니다. 예를 들어:이렇게 하면 현재 행 번호에 따라 참조하는 셀이 달라지게 되어 연속된 값을 가져올 수 있습니다.
   •  
     =INDIRECT(''가격정보''!B & ROW(A2))

실전 예제: 여러 시트의 같은 셀 값 가져오기

가끔 여러 시트에서 동일한 위치에 있는 값을 한 시트에 모아야 할 때가 있습니다. 예를 들어, 각 월별 매출 데이터를 각각의 시트에 기록해두고, 이를 한 곳에서 모아보고 싶을 때 말이죠.

방법 1: 수동으로 참조하기

가장 간단한 방법은 각 셀에 직접 참조를 입력하는 것입니다. 예를 들어, ''1월'' 시트의 A1 셀 값을 가져오고 싶다면:

 

=''1월''!A1

이렇게 입력하면 됩니다. 하지만 시트가 많아지면 이 방법은 비효율적이겠죠?

방법 2: INDIRECT 함수와 셀 주소 조합하기

INDIRECT 함수를 사용하면 시트 이름을 동적으로 참조할 수 있습니다.

1. 시트 이름 목록 만들기:
   • 먼저, 값을 가져오고 싶은 시트 이름들을 한 열에 나열합니다. 예를 들어, C1부터 C12까지 ''1월'', ''2월'', ..., ''12월''과 같이 입력합니다.
2. INDIRECT 함수로 값 가져오기:
   • 그런 다음, 해당 시트의 A1 셀 값을 가져오고 싶다면:이렇게 입력하면 C1 셀에 있는 시트 이름을 기반으로 해당 시트
   •  
     =INDIRECT('' & C1 & ''!A1)

이렇게 하면 C1 셀에 있는 시트 이름을 기반으로 해당 시트의 A1 셀 값을 불러올 수 있습니다. 이 수식을 아래로 복사하면, 각 시트의 A1 셀 값들이 한눈에 정리되어 나타납니다. 예를 들어 C1부터 C12까지 ‘1월’부터 ‘12월’까지 입력되어 있다면, 각 월별 시트의 A1 셀에 있는 값이 연속적으로 정리되어 나옵니다.

유용한 팁: 오류 처리하기

가끔 시트 이름이 잘못되었거나, 해당 셀에 값이 없을 경우 오류가 발생할 수 있습니다. 이럴 때는 IFERROR 함수를 함께 사용해보세요:

excel

복사편집

=IFERROR(INDIRECT("'" & C1 & "'!A1"), "")

이렇게 하면 오류 대신 빈 셀이 표시되어 보기에도 깔끔하고 실수도 줄일 수 있습니다.

---

마무리하며

지금까지 엑셀에서 다른 시트의 값을 한꺼번에 연속으로 가져오는 다양한 방법을 살펴봤습니다.

• VLOOKUP 함수는 간단하고 직관적인 방법으로 다른 시트의 값을 가져올 수 있고,
• INDEX + MATCH 조합은 더 유연한 검색이 가능하며,
• INDIRECT 함수는 동적으로 시트나 셀을 참조할 수 있어 많은 시트를 관리할 때 매우 유용합니다.

이러한 함수들을 잘 활용하면 복잡한 데이터도 손쉽게 정리하고 분석할 수 있어요.

 

윈도우 버전 업그레이드: 쉽고 재미있게 따라하기!

 

 

엑셀 도형 삽입 방법