Microsoft Excel은 강력한 소프트웨어로, 다양한 도구와 기능을 활용해 데이터에 여러 작업을 수행할 수 있으며, 기본 제공되는 Excel 함수를 사용해 수식을 만들 수도 있습니다. 많은 교육기관과 기업이 소중한 데이터를 저장하기 위해 엑셀 파일을 활용합니다. 때때로 여러 엑셀 파일을 서로 연결하여 다양한 출처의 데이터를 입력하기도 하는데, 이때 여러 가지 이유로 링크가 끊어질 수 있습니다. 그러면 작업 중인 워크시트에 오류가 발생하기 때문에 해당 링크를 제거해야 합니다. 이 글에서는 Excel에서 끊어진 링크를 제거하는 3가지 간단한 방법을 소개합니다.
아래 통합 문서를 내려받아 직접 실습해 보세요.
Excel의 끊어진 링크란 무엇일까요?
우리가 다루는 데이터에는 다른 워크시트나 통합 문서로 연결되는 링크가 포함된 경우가 많습니다. 하지만 몇 가지 이유로 인해 이러한 링크는 끊어질 수 있습니다. 주요 원인은 원본 워크시트 또는 통합 문서의 이름 변경, 원본 파일 위치 변경, 파일 삭제 등입니다. 이런 상황이 발생하면 링크가 활성 워크시트에서 제대로 작동하지 않고 오류가 발생합니다. 이처럼 작동하지 않게 된 링크를 끊어진 링크(broken link)라고 합니다.
다음 데이터 집합에서 D5 셀은 Sample 파일의 Sheet1에 있는 D5 셀과 연결되어 있습니다.
그런데 Sample 파일에서 원본 시트 이름이 변경되었고, 그 결과 아래 그림과 같이 #REF! 오류가 표시됩니다. 이번 글에서는 이런 종류의 링크를 제거하는 방법을 알아보겠습니다.
Excel에서 끊어진 링크를 제거하는 3가지 간단한 방법
엑셀에서 끊어진 링크를 제거하는 방법은 여러 가지가 있습니다. 여기서는 세 가지 방법을 모두 설명하니 차근차근 따라 해 보세요.
1. [링크 편집] 명령으로 끊어진 링크 제거하기
가장 간단한 방법은 [링크 편집(Edit Links)] 명령을 사용하는 것입니다. 아래 단계를 따라 작업을 진행해 보세요.
절차:
- 먼저 [데이터] 탭으로 이동합니다.
- [쿼리 및 연결] 그룹에서 [링크 편집] 옵션을 선택합니다.
- [링크 편집] 대화 상자가 나타납니다.
- 목록에서 끊어진 링크를 선택합니다.
- 그런 다음 [링크 끊기(Break Link)] 버튼을 클릭합니다.
- 경고 대화 상자가 표시됩니다.
- [링크 끊기]를 누릅니다.
- 이제 엑셀 워크시트의 모든 끊어진 링크가 제거됩니다.
더 읽어보기: Excel에서 링크 끊기 (3가지 빠른 방법)
2. Excel VBA로 끊어진 링크 제거하기
또한 VBA 코드를 활용해서도 이 작업을 수행할 수 있습니다. 다음 과정을 살펴보세요.
절차:
- 먼저 [개발 도구] 탭으로 이동합니다.
- [코드] 그룹에서 [Visual Basic] 옵션을 선택합니다.
- VBA 창에서 삽입 ➤ 모듈을 클릭합니다.
- 아래 코드를 복사하여 모듈 창에 붙여넣습니다.
Sub RemoveLinks()
alinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(alinks) Then
Sheets.Add
shtName = ActiveSheet.Name
Set summaryWS = ActiveWorkbook.Worksheets(shtName)
summaryWS.Range("A1") = "sheet"
summaryWS.Range("B1") = "location"
summaryWS.Range("C1") = "function"
summaryWS.Range("D1") = "file"
summaryWS.Range("E1") = "outcome"
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> summaryWS.Name Then
For Each Rng In ws.UsedRange
If Rng.HasFormula Then
For j = LBound(alinks) To UBound(alinks)
filePath = alinks(j) 'LinkSrouces returns full file path with file name
Filename = Right(filePath, Len(filePath) - InStrRev(filePath, "\")) 'extract just the file name
filePath2 = Left(alinks(j), InStrRev(alinks(j), "\")) & "[" & Filename & "]" 'file path with brackets
If InStr(Rng.Formula, filePath) Or InStr(Rng.Formula, filePath2) Then
nextrow = summaryWS.Range("A" & Rows.Count).End(xlUp).Row + 1
summaryWS.Range("A" & nextrow) = ws.Name
summaryWS.Range("B" & nextrow) = Replace(Rng.Address, "$", "")
summaryWS.Hyperlinks.Add Anchor:=summaryWS.Range("B" & nextrow), Address:="", SubAddress:="'" & ws.Name & "'!" & Rng.Address
summaryWS.Range("C" & nextrow) = "'" & Rng.Formula
summaryWS.Range("D" & nextrow) = filePath
summaryWS.Range("E" & nextrow) = linkStatusDescr(ActiveWorkbook.LinkInfo(CStr(filePath), xlLinkInfoStatus))
Exit For
End If
Next j
For Each namedRng In Names
If InStr(Rng.Formula, namedRng.Name) Then
filePath = Replace(Split(Right(namedRng.RefersTo, Len(namedRng.RefersTo) - 2), "]")(0), "[", "") 'remove =' and range in the file path
nextrow = summaryWS.Range("A" & Rows.Count).End(xlUp).Row + 1
summaryWS.Range("A" & nextrow) = ws.Name
summaryWS.Range("B" & nextrow) = Replace(Rng.Address, "$", "")
summaryWS.Hyperlinks.Add Anchor:=summaryWS.Range("B" & nextrow), Address:="", SubAddress:="'" & ws.Name & "'!" & Rng.Address
summaryWS.Range("C" & nextrow) = "'" & Rng.Formula
summaryWS.Range("D" & nextrow) = filePath
summaryWS.Range("E" & nextrow) = linkStatusDescr(ActiveWorkbook.LinkInfo(CStr(filePath), xlLinkInfoStatus))
Exit For
End If
Next namedRng
End If
Next Rng
End If
Next
Columns("A:E").EntireColumn.AutoFit
lastrow = summaryWS.Range("A" & Rows.Count).End(xlUp).Row
For r = 2 To lastrow
If ActiveSheet.Range("E" & r).Value = "Source unavailable" Then
countBroken = countBroken + 1
End If
Next
If countBroken > 0 Then
sInput = MsgBox("Do you want to remove broken links of status 'Source unavailable'?", vbOKCancel + vbExclamation, "Warning")
If sInput = vbOK Then
For r = 2 To lastrow
If ActiveSheet.Range("E" & r).Value = "Source unavailable" Then
Sheets(Range("A" & r).Value).Range(Range("B" & r).Value).ClearContents
dummy = MsgBox(countBroken & " broken links removed", vbInformation)
End If
Next
End If
End If
Else
MsgBox "links absent"
End If
End Sub
Public Function linkStatusDescr(statusCode)
Select Case statusCode
Case xlLinkStatusCopiedValues
linkStatusDescr = "data copied"
Case xlLinkStatusIndeterminate
linkStatusDescr = "status unavailable"
Case xlLinkStatusInvalidName
linkStatusDescr = "wrong name"
Case xlLinkStatusMissingFile
linkStatusDescr = "Source unavailable"
Case xlLinkStatusMissingSheet
linkStatusDescr = "worksheet absent"
Case xlLinkStatusNotStarted
linkStatusDescr = "yet to start"
Case xlLinkStatusOK
linkStatusDescr = "all okay"
Case xlLinkStatusOld
linkStatusDescr = "expired"
Case xlLinkStatusSourceNotCalculated
linkStatusDescr = "yet to compute"
Case xlLinkStatusSourceNotOpen
linkStatusDescr = "inactive source"
Case xlLinkStatusSourceOpen
linkStatusDescr = "active souorce"
Case Else
linkStatusDescr = "status undetected"
End Select
End Function
- 다음으로 F5 키를 눌러 코드를 실행합니다.
- [매크로] 대화 상자가 나타납니다.
- RemoveLinks를 선택하고 [실행]을 누릅니다.
- 그러면 새 워크시트가 생성됩니다.
- 여기서 '결과(outcome)' 열에 Source unavailable(원본 사용 불가)이라고 표시된 항목을 확인할 수 있습니다.
- 동시에 같은 워크시트에서 경고 대화 상자가 나타납니다.
- [확인]을 누릅니다.
- 마지막으로 끊어진 링크가 모두 제거됩니다.
더 읽어보기: Excel에서 원본을 찾을 수 없을 때 링크 끊기 (4가지 방법)
3. 이름 관리자에서 끊어진 링크 제거하기
이름이 정의된 범위(named range)에도 끊어진 링크가 남아 있을 수 있습니다. 아래 과정을 따라 해당 링크를 삭제하세요.
절차:
- 먼저 [수식] 탭으로 이동합니다.
- [정의된 이름] 드롭다운에서 [이름 관리자]를 선택합니다.
- [이름 관리자] 대화 상자가 나타납니다.
- 끊어진 링크를 선택합니다.
- 그런 다음 [삭제]를 누릅니다.
- 이렇게 하면 끊어진 링크를 손쉽게 제거할 수 있습니다.
더 읽어보기: Excel에서 끊어진 링크 찾기 (4가지 빠른 방법)
결론
지금까지 소개한 방법을 활용하면 Excel에서 끊어진 링크를 손쉽게 제거할 수 있습니다. 위 방법들을 꾸준히 활용해 보고, 더 좋은 방법이 있다면 알려주세요. 이와 같은 유용한 글이 더 필요하시면 ExcelDemy 웹사이트를 확인해 주세요. 궁금한 점이나 제안, 질문이 있다면 아래 댓글란에 자유롭게 남겨주세요.
관련 글
- 파일 열기 전에 Excel에서 링크 끊기 (쉬운 단계별 방법)
- Excel 링크가 자꾸 끊어지는 이유 (3가지 원인과 해결책)
- Excel에서 끊어진 하이퍼링크 수정하는 방법 (5가지)
- [해결 완료!] Excel에서 링크 끊기가 작동하지 않을 때 (7가지 해결책)
- 값은 유지하면서 Excel 링크 끊기 (3가지 쉬운 방법)