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

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

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

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

실습용 엑셀파일 다운로드 : 02-5.다른파일참조-집계-실습.xlsx, 02-5.다른파일참조-집계-완성.xlsx, 02-5.1월판매.xlsx, 02-5.2월판매.xlsx

파일 연결해서 집계하기

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) 참조하는 파일명이 바뀐 경우에는 다시 연결해야 한다

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

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

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

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

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

엑셀 수식 강좌

[ 수식 다루기 ]

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

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

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

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

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