다른 엑셀 파일을 연결하여 한번에 실적 집계하기

엑셀에서 자료를 집계할 때 '다른 시트'의 내용을 참조해서 자료를 집계하는 방법을 이용할 수도 있지만 '다른 파일'을 연결해서 자료를 집계하는 방식도 있습니다. 엑셀 파일을 목적별로 나누어서 관리하고 집계용 파일을 따로 만들어서 나누어진 파일을 연결하여 집계하는 경우에  유용한 기능입니다.

예를 들어 다음 그림과 같이 2개의 월별 판매실적 엑셀 파일(1월판매.xlsx, 2월판매.xlsx)이 있을 때 집계엑셀 파일(집계.xlsx)에서 이 2개의 파일을 연결하여 월별 합계를 작성할 수 있습니다.

아래의 실습용 엑셀파일을 다운로드해서 따라해 보세요.

 

파일 연결해서 집계하기

1) 파일을 연결하여 값을 표시하기

다른 파일을 연결하여 값을 단순히 표시하는 방법을 알아보겠습니다.

먼저 실습에 사용할  다음 2개의 엑셀 파일을 모두 엽니다.

• 집계용 파일(02-5.다른파일참조-집계-실습.xlsx)
• 연결할 파일(02-5.1월판매.xlsx)

'02-5.다른파일참조-집계-실습.xlsx' 파일의 [C3] 셀에 등호 '='를 입력합니다.

 

마우스로 '02-5.1월판매.xlsx'  파일의 [판매이력] 시트의 [E2] 셀을 선택하고 [Enter]를 누르면

 

다음과 같이 집계용 파일에 값이 표시됩니다. 다른 파일을 연결하여 값을 표시한 것입니다.

수식입력줄을 살펴보면 ='[02-5.1월판매.xlsx]판매이력'!$E$2 로 입력되어 있습니다.

=[파일명]시트명!셀주소 형식으로 시트명 앞에 파일명이 붙는 방식입니다.

 

2) 파일을 연결하여 SUM함수로 값을 집계하기

위의 예처럼 다른 파일을 연결하여 값을 단순히 참조할 수도 있지만 함수나 수식을 이용하여 계산하는 것도 가능합니다.

파일을 연결하여 SUM함수로 값을 집계해 보겠습니다. 먼저 따라 해본 '파일을 연결해서 값을 표시하는 것'과 거의 동일합니다.

실습에 사용할  다음 3개의 엑셀 파일을 모두 엽니다.

• 집계용 파일(02-5.다른파일참조-집계-실습.xlsx)
• 연결할 파일(02-5.1월판매.xlsx, 02-5.2월판매.xlsx)

'02-5.다른파일참조-집계-실습.xlsx' 파일의 [C5] 셀에 '=SUM('을 입력합니다.

 

마우스로 '02-5.1월판매.xlsx'  파일의 [판매이력] 시트의 [E2:E12] 범위를 선택하고, 수식에서 괄호를 닫고 [Enter]를 누르면

 

다음과 같이 집계용 파일에 SUM 함수의 결과가 표시됩니다.

수식입력줄을 살펴보면 =SUM('[02-5.1월판매.xlsx]판매이력'!$E$2:$E$12)로 입력되어 있습니다. 일반적인 수식과 크게 다르지 않습니다. 시트명 앞에 파일명이 표시된다는 것만 다릅니다.

 

마지막으로 1월 판매실적을 가져오는 방법과 같은 방법으로 2월 판매실적을 가져오면 다음과 같이 1월, 2월 판매실적 집계가 완성됩니다.

 

Tip!)연결된 파일이 닫힌 경우의 수식표시

연결된 파일이 열려 있을 경우와 닫혀 있을 경우에 '수식 입력줄' 표시방식이 다릅니다.

파일이 열린 경우 : =SUM('[02-5.1월판매.xlsx]판매이력'!$E$2:$E$12)

파일이 닫힌 경우 : =SUM('C:\Users\thjung\Downloads\[02-5.1월판매.xlsx]판매이력'!$E$2:$E$12)

파일이 닫힌 경우에는 아래 그림과 같이 파일의 전체 경로가 표시됩니다.

 

파일 연결시 주의할 점

1) 파일이 열려 있을 경우에만 변경된 내용이 즉시 반영된다

다음 그림과 같이 연결된 파일(1월판매.xlsx, 2월판매.xlsx)이 열려 있고 그 파일에서 값을 변경하면 변경된 내용이 연결한 파일(집계.xlsx)에 즉시 반영됩니다.

 

만약, 연결한 파일(집계.xlsx)이 닫혀 있을 경우에서 연결된 파일(1월판매.xlsx, 2월판매.xlsx)을 변경해도 변경된 내용이 연결한 파일(집계.xlsx)에 반영되지 않습니다.

 

 

2) 참조하는 파일명이 바뀐 경우에는 다시 연결해야 한다

만약에 참조하는 파일명이 바뀐 경우에 '연결한 파일'을 열면 다음과 같은 [경고] 대화상자가 나타납니다.

<연결편집>버튼을 클릭하여 아래와 같이 바뀐 파일명으로 다시 연결해야 합니다.

 

[연결 편집] 대화상자가 나타나면 <원본 변경>버튼을 누릅니다.

 

[파일 탐색기]에서 변경된 파일을 찾아서 선택한 후 <확인> 버튼을 누릅니다.

 

파일이 연결되면 다음과 같이 오류메시지가 사라지고 바뀐 파일로 연결됩니다.

 

 

---

엑셀 수식 강좌 

[ 수식 다루기 ]

• 엑셀 수식 알아보기(수식의 입력, 수정, 구성)
• 엑셀 수식 연산자와 연산 우선순위
• 엑셀에서 와일드카드가 뭔가요?

[ 엑셀에서 입력과 표시방법 ]

• 엑셀에서 값을 입력하고 표시하는 원리
• 셀서식을 지정하여 원하는 형태로 표시하기
• 엑셀에서 복사하고 붙여넣기의 모든 것
• 엑셀 자동 채우기의 모든 것

[ 수식에서 셀과 범위 참조 방식 ]

• 엑셀 상대참조/절대참조/혼합참조 완벽 정리
• 엑셀에서 다른 시트 참조하기
• 엑셀에서 3차원 참조로 한번에 자료 집계하기
• 다른 엑셀 파일을 연결하여 한번에 실적 집계하기

[ 엑셀을 제대로 쓰는 데이터처리 ]

• 엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
• 수식으로 한 번에 텍스트 나누기
• 엑셀 고급필터로 다양하게 자료 필터링하기
• 정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
• 엑셀 오류데이터 처리하기
• 파워 쿼리(Power Query) 소개
• 파워쿼리로 외부 데이터 가져오기
• 엑셀에서 중복 데이터 제거하기
• 엑셀 텍스트 나누기의 모든 것
• 이동옵션으로 원하는 셀 한번에 선택하고 처리하기
• 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
• 사용자 지정 수식으로 데이터 유효성 검사하기
• 데이터 유효성 검사로 허용된 데이터만 입력
• 엑셀 데이터 다루기 첫 단추는 데이터 구조화

[ 엑셀 이름정의와 표기능 ]

• 직관적인 엑셀 표 구조적 참조
• 엑셀 표 기능으로 자료 관리하기
• 엑셀 동적 범위에 이름 정의하기
• 엑셀 이름표, 이름 정의

[ 수식을 분석하고 오류 해결하기 ]

• 엑셀 순환참조 문제 해결하기
• 수식 계산 단계 알아보기
• 엑셀 수식 오류 검사하고 추적하기
• 엑셀에서 참조 셀 추적하기
• 엑셀 오류 유형(#DIV/0!, #N/A, #NAME?, #REF! 등)과 해결방법