오늘은 다른 통합 문서를 열지 않고도 외부 데이터를 끌어와서 실시간으로 갱신하거나 보고용 시트를 자동화하는 방법을 살펴봅니다.
모두 마이크로소프트 엑셀(Microsoft 365 및 엑셀 2021 이상) 기준으로 설명했으며, 구버전 사용자는 일부 기능이 제한될 수 있습니다.
1. XLOOKUP+INDIRECT 대신 LOOKUP+LET+WEBSERVICE
핵심 아이디어
- 기존에는
INDIRECT("'[외부파일.xlsx]Sheet1'!A2")같이 동적 참조를 써야 했지만, 외부 파일이 열려 있어야 한다는 한계가 있습니다. LET함수와 서버용 OneDrive/SharePoint URL을 활용하면, 파일을 열지 않고서도 값을 당겨올 수 있습니다.
사용 순서
- 외부 통합 문서를 SharePoint(또는 OneDrive for Business)에 저장합니다.
- 주소 복사 ➜
?web=1뒤의 쿼리 매개변수 제거 ➜ 확장자를.xlsx?web=0형태로 유지합니다. - 대상 셀에 아래와 같이 입력합니다.
=LET(
url, "https://contoso.sharepoint.com/sites/팀자료/공유%20문서/외부파일.xlsx?web=0",
tbl, WEBSERVICE(url & "&range=Sheet1!A2"),
XLOOKUP("서울", INDEX(tbl,,1), INDEX(tbl,,2))
)WEBSERVICE는 URL이 반환하는 CSV 또는 JSON을 텍스트로 읽어 옵니다.- Office Script 또는 Power Automate를 통해 JSON 변환 API를 연결하면 범용 데이터 소스로 활용할 수 있습니다.
장점
- 로컬 PC에 파일이 없어도 실시간 참조
- 여러 사용자 동시 참조 시 충돌 최소화
단점- 회사 보안 정책에 따라 외부 호출 제한 가능
2. Power Query: 연결만 만들고 ‘미리 보기’ 차단
핵심 아이디어
Power Query(데이터 ➜ 통합 문서 가져오기)는 데이터 연결만 저장해 두고, 원본 파일을 열지 않고 배경에서 새 값으로 갱신할 수 있습니다.
단계별 설정
- 데이터 탭 ➜
데이터 가져오기➜파일 ➜ 통합 문서 - 외부 파일 선택 후 ‘연결 만들기만’ 클릭
- 쿼리 편집기 → 필요한 열 필터링/변환
- ‘테이블로 로드’ 대신 **‘연결만 유지’**로 완료
갱신은 데이터 ➜ 모두 새로 고침(Ctrl+Alt+F5) 한 번이면 종료.
자동 갱신이 필요하면 파일 → 옵션 → 데이터 → 배경 새로 고침 허용 체크.
장점
- GUI 기반, 함수 암기 불필요
- 수십만 행도 지연 없이 처리
단점- 초기 셋업에 3-4단계 필요
- 엑셀 2010 이하에서는 별도 추가 기능 필요(Power Query Add-in)
3. OLE DB/ODBC 연결로 ‘가상 열기’
핵심 아이디어
엑셀 파일을 데이터베이스처럼 취급하여 SQL 문의 SELECT 절로 필요한 범위만 읽습니다. 연결 객체가 백그라운드에서 파일 핸들을 잠시 잡았다가 닫기 때문에 사용자는 파일을 열지 않았다고 느낍니다.
실전 예제
- 데이터 탭 ➜
데이터 가져오기➜기타 원본 ➜ OLE DB - 공급자: Microsoft ACE OLEDB 16.0 선택
- 파일 경로 입력 후 고급 ➜ SQL 문 넣기
SELECT *
FROM [Sheet1$A1:D100]
WHERE [지역] = '서울'- 결과를 테이블 혹은 피벗으로 삽입 → 필요 시
속성에서 열지 않고 새로 고침 활성화.
장점
- WHERE, JOIN 등 SQL 필터 지원 → 복잡한 조건 쉽게 처리
단점- 32비트 Office 환경에서 드라이버 충돌 가능
- IT 부서 드라이버 설치 권한 필요
기능별 비교
| 항목 | XLOOKUP+WEBSERVICE | Power Query 연결만 | OLE DB/ODBC |
|---|---|---|---|
| 초기 난이도 | 중간 | 가장 낮음 | 높음 |
| 대용량 성능 | 중 | 높음 | 높음 |
| 실시간성 | 높음 | 수동/예약 갱신 | 수동/예약 갱신 |
| 추가 설치 | 없음 | 엑셀 2016+ 기본 | OLE DB 드라이버 |
| 보안 제약 | URL 호출 제한 가능 | 통합 문서 권한만 필요 | 드라이버 설치 정책 |
*정리
- 작은 범위면 함수 기반(방법 1)이 빠르고 간단합니다.
- 정기 보고서·대용량은 Power Query(방법 2)가 안정적입니다.
- SQL 경험이 있다면 OLE DB(방법 3)로 복수 시트를 조인하는 고급 자동화를 고려하세요.
이제 외부 파일을 굳이 열 필요 없이, 원하는 데이터를 깔끔하게 끌어와 업무 효율을 극대화해 보세요.