이 글에서는 Excel에서 외부 데이터 연결(External Data Connections)을 찾는 방법을 소개합니다. 외부 데이터 연결이란 다른 통합 문서에 저장된 데이터를 현재 통합 문서로 가져와 참조하는 것을 말합니다. 이를 활용하면 원본 통합 문서의 데이터가 변경될 때마다 내 데이터 세트도 함께 최신 상태로 업데이트할 수 있습니다. 다만 연결된 링크를 제대로 파악하지 못하면 파일 관리나 오류 해결이 어려워지기 때문에, 외부 연결 위치를 확인하고 추적하는 방법을 반드시 알아두는 것이 좋습니다.
Excel에서 외부 데이터 연결을 찾는 5가지 방법
이번 글에서는 외부 데이터 연결을 확인하는 5가지 유용한 방법을 다룹니다. 첫째, 찾기(Find) 기능으로 링크를 검색하고, 둘째, 이름 관리자(Name Manager)를 활용하며, 셋째, 링크 편집(Edit Links) 명령으로 연결을 확인합니다. 넷째, VBA 코드를 사용해 연결 목록을 자동으로 추출하고, 마지막으로 Excel 4.0 매크로를 이용해 모든 외부 연결을 한 번에 가져오는 방법까지 살펴보겠습니다.
예제 데이터 세트에는 'Number of Sale.xlsx'와 'Unit Price.xlsx'라는 두 개의 서로 다른 시트가 연결되어 있습니다.
1. 찾기(Find) 기능 활용하기
첫 번째 방법은 Excel의 찾기(Find) 기능을 사용해 데이터 세트의 모든 셀에서 외부 연결을 검색하는 것입니다. 찾기 기능은 특정 값이나 데이터를 빠르게 찾아주는 도구일 뿐만 아니라, 외부 통합 문서를 참조하는 수식도 손쉽게 찾아낼 수 있습니다.
단계별 진행:
- 먼저 홈(Home) 탭으로 이동합니다.
- 편집(Editing) 그룹에서 찾기 및 선택(Find & Select)을 클릭합니다.
- 드롭다운 메뉴에서 찾기(Find)를 선택하면 대화상자가 화면에 나타납니다.
- 대화상자의 찾을 내용(Find What) 입력란에 '.xl'을 입력합니다. 외부 파일은 확장자가 .xlsx 또는 .xls이므로 이 키워드로 검색하면 됩니다.
- 찾을 범위(Within)를 통합 문서(Workbook)로 설정합니다.
- 찾을 위치(Look in)를 수식(Formulas)으로 지정합니다.
- 모두 찾기(Find All) 버튼을 클릭합니다.
- 그러면 통합 문서 내 모든 셀에 걸려 있는 외부 연결이 한눈에 표시됩니다.
이렇게 하면 Excel 통합 문서에 포함된 모든 외부 데이터 연결을 간단히 찾아낼 수 있습니다.
2. 이름 관리자(Name Manager) 사용하기
이름 관리자는 통합 문서에 정의된 모든 이름 범위(Named Range)를 관리하는 도구입니다. 사용자들은 수식을 효율적으로 적용하기 위해 다른 통합 문서의 이름 범위를 현재 통합 문서로 가져오는 경우가 많습니다. 이 예제 역시 'Number of Sales.xlsx' 통합 문서의 이름 범위를 현재 통합 문서에 연결한 상태입니다.
단계별 진행:
- 먼저 'Number of Sales.xlsx' 통합 문서를 열고 B5:B10 셀 범위를 선택합니다.
- 이름 상자(Name Box)에 'Fruits'라고 입력해 해당 범위에 이름을 지정합니다.
- 다시 원래 통합 문서로 돌아와 B5 셀에 '=' 기호를 입력한 뒤, 'Number of Sales.xlsx'의 Fruits 범위를 선택해 두 통합 문서를 연결합니다.
- 수식(Formulas) 탭으로 이동해 이름 정의(Define Name)를 선택하면 대화상자가 나타납니다.
- 이름(Name)란에 'Fruit_Items'를 입력합니다.
- 참조 대상(Refers to)란에 'Number of Sales.xlsx' 통합 문서의 Fruits 범위를 지정합니다.
- 확인(OK)을 클릭해 완료합니다.
- 이어서 수식(Formulas) 탭의 정의된 이름(Defined Names) 그룹에서 이름 관리자(Name Manager)를 선택합니다.
- 대화상자의 참조 대상(Refer to) 영역에서 해당 이름 범위가 참조하는 외부 데이터 연결 경로를 확인할 수 있습니다.
이 경로를 복사해 Windows 탐색기 주소창에 붙여넣으면 'Number of Sales.xlsx' 파일이 저장된 위치로 바로 이동할 수 있습니다.
함께 읽으면 좋은 글: 다른 Excel 파일과 데이터 연결 만드는 방법
3. 링크 편집(Edit Links) 기능 사용하기
링크 편집(Edit Links) 옵션은 외부 연결을 점검하고 수정하거나 변경하는 데 매우 유용한 도구입니다. 여기서는 이 기능을 사용해 연결된 링크를 찾아보겠습니다.
단계별 진행:
- 먼저 데이터(Data) 탭을 선택합니다.
- 쿼리 및 연결(Queries & Connections) 그룹에서 링크 편집(Edit Links) 옵션을 클릭합니다.
- 그러면 대화상자가 화면에 나타납니다.
- 대화상자의 원본(Source) 열에서 데이터 출처가 되는 파일 이름을 확인할 수 있습니다.
- 원본 열기(Open Source) 버튼을 클릭하면 원본 통합 문서를 곧바로 열 수 있습니다.
- 클릭하면 'Number of Sales.xlsx' 워크시트가 열립니다.
이처럼 링크 편집 기능을 활용하면 외부 데이터 연결을 확인하고 원본 파일에 바로 접근할 수 있습니다.
4. VBA 코드 적용하기
VBA 코드는 여러 작업을 한 번에 처리할 수 있는 강력한 도구입니다. 이 방법에서는 간단한 VBA 코드를 사용해 통합 문서의 모든 외부 연결을 가져오고, 새 시트에 목록으로 출력해 보겠습니다.
단계별 진행:
- 먼저 개발 도구(Developer) 탭으로 이동합니다.
- Visual Basic 옵션을 선택하면 새 창이 열립니다.
- Visual Basic 창에서 삽입(Insert) 메뉴를 클릭합니다.
- 드롭다운 목록에서 모듈(Module)을 선택하면 새 모듈 창이 나타납니다.
- 모듈에 아래 코드를 입력하고 저장합니다.
Sub FindExternalConnections()
Dim am_link As Variant
am_link = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(am_link) Then
Sheets.Add
For a = 1 To UBound(am_link)
Cells(a, 1).Value = am_link(a)
Next a
End If
End Sub
- 마지막으로 초록색 삼각형 실행 버튼을 눌러 코드를 실행합니다.
- 그러면 통합 문서의 외부 연결 목록이 새 시트에 자동으로 정리되어 표시됩니다.
앞서 소개한 링크 편집 방법과 마찬가지로, 이렇게 추출한 링크를 통해 원본 통합 문서의 저장 위치로 바로 이동할 수 있습니다.
함께 읽으면 좋은 글: Excel VBA로 모든 데이터 연결 새로 고치는 방법
5. Excel 4.0 매크로로 모든 외부 연결 추출하기
Excel 4.0 매크로는 VBA의 전신에 해당하는 기능으로, VBA가 도입되기 전부터 반복 작업을 자동화하는 데 사용되었습니다. 지금도 일부 4.0 매크로 함수는 사용할 수 있으며, 이 예제에서는 이를 활용해 외부 데이터 연결을 추출한 뒤 TRANSPOSE(전치) 함수로 목록을 가로 방향으로 변환해 보겠습니다.
단계별 진행:
- 먼저 수식(Formulas) 탭으로 이동합니다.
- 정의된 이름(Defined Names) 그룹에서 이름 관리자(Name Manager)를 선택합니다.
- 대화상자가 화면에 나타나면 새로 만들기(New)를 클릭합니다.
- 먼저 매크로의 이름(Name)을 지정합니다.
- 참조 대상(Refer to) 입력란에 =LINKS()를 입력합니다.
- 확인(OK)을 클릭해 저장합니다.
- 이제 B5 셀을 선택하고 위에서 정의한 이름을 수식으로 입력한 뒤 Enter 키를 누릅니다.
- 그러면 통합 문서에 연결된 모든 외부 데이터 연결 목록이 표시됩니다.
추출된 링크를 Windows 검색창에 붙여넣으면 해당 외부 데이터 파일의 저장 위치를 즉시 확인할 수 있습니다.
함께 읽으면 좋은 글: [해결 방법] Excel에서 외부 데이터 연결이 비활성화되었을 때
연습용 통합 문서 다운로드
위 예제를 직접 따라 해볼 수 있도록 연습용 통합 문서를 제공합니다.
마치며
지금까지 Excel에서 외부 데이터 연결을 찾는 다양한 방법을 살펴보았습니다. 이 방법들을 활용하면 통합 문서에 걸려 있는 기존 링크를 효율적으로 점검하고, 업데이트하거나, 필요 없는 연결을 끊거나 삭제할 수 있습니다. 본문 내용에 대해 궁금한 점이 있다면 언제든지 댓글로 알려주세요.
함께 보면 좋은 관련 글
- 파일을 열지 않고 Excel 데이터 연결 새로 고치는 방법
- Excel 연결(Connections) vs 쿼리(Queries) 차이점
- Excel에서 데이터 원본 만드는 방법
- Excel에서 데이터 연결이 새로 고침되지 않을 때 해결법
- Excel 쿼리 및 연결이 작동하지 않을 때 대처 방법
- Excel에서 쿼리 및 연결 창 닫는 방법
<< Excel 데이터 연결 | Excel 데이터 가져오기 | Excel 배우기로 돌아가기