엑셀 다른 시트 값 가져오기

목차

• 다른 시트의 셀 값 참조하기
• INDIRECT 함수 활용하기
• VLOOKUP 함수로 다른 시트의 데이터 찾기
• 실생활 예시: 학생 성적 관리
• Power Query란?
• 여러 시트의 데이터를 자동으로 합치는 방법
• 데이터 정리 및 분석을 쉽게 하는 방법
• 실전 예제: 월별 매출 데이터를 자동으로 통합하기

 

안녕하세요, 여러분! 오늘은 엑셀에서 다른 시트의 값을 가져오는 방법에 대해 알아보려고 해요. 엑셀을 사용하다 보면 여러 시트를 활용하게 되는데, 이때 다른 시트에 있는 데이터를 가져와야 할 때가 많죠. 중학생 수준에서 쉽게 이해할 수 있도록 설명해 드릴게요. 자, 그럼 시작해볼까요?

다른 시트의 셀 값 참조하기

엑셀에서 다른 시트의 셀 값을 가져오는 가장 기본적인 방법은 해당 셀을 직접 참조하는 거예요. 예를 들어, ''Sheet1'' 시트의 A1 셀에 있는 값을 ''Sheet2'' 시트의 B1 셀에서 사용하고 싶다면, 다음과 같이 입력하면 돼요:

 

=Sheet1!A1

이렇게 입력하면 ''Sheet2'' 시트의 B1 셀에 ''Sheet1'' 시트의 A1 셀 값이 표시됩니다. 참 쉽죠?

엑셀 데이터 분석 작업 title영문 : ExcelAnalyze

INDIRECT 함수 활용하기

하지만 시트 이름이 자주 변경되거나 동적으로 참조해야 할 경우에는 INDIRECT 함수를 사용하는 것이 좋아요. INDIRECT 함수는 텍스트 문자열로 셀이나 범위를 참조할 수 있게 해주는 함수예요. 사용 방법은 다음과 같아요:

 

=INDIRECT(Sheet1!A1)

이렇게 하면 ''Sheet1'' 시트의 A1 셀 값을 가져올 수 있어요. 만약 시트 이름을 셀에 입력하고, 그 셀을 참조하여 값을 가져오고 싶다면 다음과 같이 할 수 있어요:

 

=INDIRECT('' & A1 & ''!B1)

여기서 A1 셀에는 시트 이름이 들어가고, B1 셀에는 가져오고 싶은 셀 주소가 들어갑니다. 이렇게 하면 시트 이름을 변경하더라도 쉽게 값을 가져올 수 있어요.

VLOOKUP 함수로 다른 시트의 데이터 찾기

또한, VLOOKUP 함수를 사용하여 다른 시트에서 데이터를 찾을 수도 있어요. 예를 들어, ''제품목록'' 시트에서 제품 코드를 찾아 해당 제품의 이름을 가져오고 싶다면, 다음과 같이 할 수 있어요:

 

=VLOOKUP(A2, 제품목록!A:B, 2, FALSE)

여기서 A2 셀에는 찾고자 하는 제품 코드가 들어가고, ''제품목록'' 시트의 A열과 B열에서 해당 코드를 찾아 B열의 값을 가져옵니다.

실생활 예시: 학생 성적 관리

예를 들어, 여러 반의 학생 성적을 각각의 시트에 관리하고 있다고 가정해볼게요. 각 시트에는 반 이름이 시트 이름으로 되어 있고, 학생들의 성적이 입력되어 있어요. 이때, 전체 학생의 평균 성적을 구하기 위해 각 시트의 성적 데이터를 하나의 시트로 모아야 할 때가 있겠죠? 이럴 때 위에서 배운 방법들을 활용하면 쉽게 데이터를 가져올 수 있어요.

 

Power Query란?

Power Query는 엑셀에서 데이터를 불러오고, 변환하고, 통합하는 데 도움을 주는 강력한 도구입니다. 이를 사용하면 데이터 정리를 자동화하고, 여러 시트 또는 파일에서 데이터를 가져와 하나의 테이블로 합칠 수 있습니다.

여러 시트의 데이터를 자동으로 합치는 방법

(1) 데이터 준비

각 시트에 동일한 형식의 데이터가 존재해야 합니다. 예를 들어, 여러 시트에 각각 다른 달의 판매 데이터가 들어 있는 경우, 모든 시트가 같은 열 구조(예: "날짜", "제품명", "매출액")를 가져야 합니다.

(2) Power Query에서 데이터 불러오기

1. 엑셀에서 Power Query 실행
   • 엑셀에서 데이터 탭을 클릭합니다.
   • "데이터 가져오기" → "기타 원본에서" → "Excel에서"를 선택합니다.
   • 현재 문서에서 데이터를 가져올 경우 "통합 문서에서"를 선택합니다.
2. 시트 선택 및 가져오기
   • 여러 시트에서 데이터를 불러오기 위해 각 시트를 개별적으로 선택하여 가져옵니다.
   • 불러온 후 "쿼리 편집기"에서 데이터를 확인할 수 있습니다.
3. 데이터 변환 및 병합
   • 열 이름 확인: 모든 시트에서 동일한 열 이름을 유지하고 있는지 확인합니다.
   • "쿼리 추가" 기능 사용: 여러 시트에서 가져온 데이터를 하나의 테이블로 합치려면 "쿼리 추가"를 사용합니다.
     • Power Query 편집기에서 홈 → 추가 쿼리 → 병합을 선택합니다.
     • 기준이 되는 쿼리를 선택하고, 추가할 다른 시트의 데이터를 선택하여 합칩니다.

(3) 병합된 데이터 테이블을 엑셀로 가져오기

1. Power Query에서 모든 데이터가 올바르게 합쳐졌다면 닫기 및 로드를 클릭합니다.
2. 새 시트에 병합된 데이터가 자동으로 채워집니다.
3. 데이터가 변경되었을 경우 "데이터 새로 고침" 버튼을 눌러 최신 데이터를 반영할 수 있습니다.

데이터 정리 및 분석을 쉽게 하는 방법

Power Query를 활용하면 데이터 정리도 매우 편리합니다. 다음과 같은 기능을 활용하면 데이터를 보다 쉽게 가공할 수 있습니다.

(1) 불필요한 열 제거

• Power Query에서 열 제거 기능을 사용하여 필요 없는 데이터를 삭제할 수 있습니다.

(2) 데이터 변환

• 날짜 형식을 변경하거나, 텍스트 데이터를 숫자로 변환할 수 있습니다.

(3) 중복 데이터 제거

• "홈" 탭에서 중복 제거를 선택하여 동일한 데이터가 여러 번 포함되지 않도록 정리할 수 있습니다.

(4) 정렬 및 필터링

• 데이터 정렬 기능을 활용하면 분석이 쉬워지고, 특정 조건에 맞는 데이터만 필터링하여 볼 수도 있습니다.

(5) 여러 데이터 소스 통합

• 다른 엑셀 파일 또는 외부 데이터베이스의 데이터를 가져와 하나의 테이블로 통합할 수 있습니다.

실전 예제: 월별 매출 데이터를 자동으로 통합하기

예를 들어, 한 회사에서 각 월별로 별도의 시트에 매출 데이터를 저장한다고 가정해 보겠습니다. Power Query를 사용하여 여러 시트의 데이터를 자동으로 합치는 방법은 다음과 같습니다.

1. 각 월의 데이터가 있는 시트를 선택하여 Power Query로 가져옵니다.
2. 모든 시트의 데이터를 "추가 쿼리" 기능을 사용하여 병합합니다.
3. 불필요한 열을 제거하고, 데이터 형식을 정리합니다.
4. 최종적으로 닫기 및 로드를 클릭하여 엑셀 시트에 결과를 삽입합니다.
5. 이후 새로운 데이터가 추가될 경우 "데이터 새로 고침" 버튼만 클릭하면 자동으로 최신 데이터가 반영됩니다.

 

엑셀 행과 열 쉽게 구분하는 방법 알아보기

 

 

엑셀차트만들기 쉽게 따라하는 방법