Excel에서 범위 이름을 셀 참조로 바꾸는 방법은 무엇입니까?

Excel에서 명명된 범위를 사용할 때, 이러한 범위는 가독성과 수식 관리에 유용할 수 있습니다. 그러나 파일을 공유하거나 문제를 해결하거나 템플릿을 표준화해야 할 경우와 같이 범위 이름을 제거하고 실제 셀 참조를 복원하려는 상황이 있을 수 있습니다. 이 문서에서는 Excel에서 명명된 범위를 해당하는 셀 참조로 바꾸는 다양한 방법을 안내합니다.

VBA 코드를 사용하여 범위 이름을 셀 참조로 바꾸기

Kutools for Excel을 사용하여 빠르고 쉽게 범위 이름을 셀 참조로 바꾸기

예를 들어, 이 워크시트에서 A2:A6 범위를 saleprice로, B2를 discount로 이름을 지정한 다음, 두 범위 이름을 모두 사용하여 새 가격을 계산하는 수식을 작성하겠습니다. 스크린샷을 참조하세요:

빠른 팁을 따르면 아래와 같이 범위 이름이 셀 참조로 바뀝니다:

VBA 코드를 사용하여 범위 이름을 셀 참조로 바꾸기

수식의 범위 이름을 셀 참조로 바꾸기 위해 VBA를 사용하면 다음과 같은 단계를 거쳐 작업을 완료할 수 있습니다:

1. 이름을 셀 참조로 바꾸려는 수식이 포함된 범위를 선택합니다.

2. Alt + F11을 눌러 새 Microsoft Visual Basic for Applications 창을 열고, Insert > Module을 클릭한 다음, 다음 코드를 모듈에 복사하여 붙여넣습니다.

VBA 코드: 절대 참조로 범위 이름 바꾸기

Sub AbsoleteNamesWithRelativeRefs()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
For Each Rng In WorkRng
    For Each xName In ThisWorkbook.Names
        If InStr(Rng.Formula, xName.Name) > 0 Then
            Rng.Formula = VBA.Replace(Rng.Formula, xName.Name, VBA.Replace(VBA.Replace(xName.RefersTo, "=", ""), "$", ""))
        End If
    Next
Next
End Sub

VBA 코드: 상대 참조로 범위 이름 바꾸기

Sub ReplaceNamesWithRelativeRefs()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
For Each Rng In WorkRng
    For Each xName In ThisWorkbook.Names
        If InStr(Rng.Formula, xName.Name) > 0 Then
            Rng.Formula = VBA.Replace(Rng.Formula, xName.Name, VBA.Replace(xName.RefersTo, "=", ""))
        End If
    Next
Next
End Sub

3. 그런 다음 F5 키를 눌러 코드를 실행하면 대체할 범위를 선택하기 위한 대화상자가 나타납니다. 스크린샷을 참조하세요:

4. 그런 다음 확인을 클릭합니다. 그러면 선택한 모든 수식의 범위 이름이 바뀝니다.

Kutools for Excel을 사용하여 빠르고 쉽게 범위 이름을 셀 참조로 바꾸기

수동으로 또는 VBA를 통해 이 작업을 수행하는 것이 시간이 많이 걸리고 오류가 발생하기 쉬운 반면, Kutools for Excel은 전체 프로세스를 간소화하는 원클릭 솔루션을 제공합니다. Kutools for Excel이 설치되어 있다면, Convert Name to Reference Range 유틸리티를 사용하여 범위 이름을 빠르고 쉽게 셀 참조로 바꿀 수 있습니다.

1. Kutools > 이름 도구 > Convert Name to Reference Range를 클릭하여 이 유틸리티를 적용합니다.

2. 대화상자가 나타나면 범위 탭을 클릭하고 Base Range에서 셀 참조로 바꾸려는 범위를 선택한 다음, Replace 버튼을 클릭합니다. 스크린샷을 참조하세요:

3. 수정된 수식과 변경되지 않은 수식의 개수를 알려주는 또 다른 알림 상자가 나타납니다. 확인을 클릭하면 선택한 범위의 이름이 셀 참조로 바뀝니다.

🔚 결론

몇 개의 수식을 처리하든 전체 통합 문서를 처리하든, 명명된 범위를 실제 셀 참조로 바꾸는 것은 투명성, 호환성 및 협업 용이성을 향상시키는 좋은 방법입니다.

• 고급 사용자의 경우 VBA는 교체 프로세스를 자동화할 수 있는 유연하고 프로그래밍 가능한 솔루션을 제공합니다.
• 그 밖의 모든 사용자에게 Kutools for Excel은 몇 번의 클릭만으로 작업을 완료할 수 있는 가장 빠르고 사용자 친화적인 방법을 제공합니다 — 코딩이나 수식 편집이 필요하지 않습니다.

자신의 작업 방식에 가장 적합한 방법을 선택함으로써 수식을 효율적으로 정리하고 스프레드시트가 이해하고 유지하기 쉽게 유지할 수 있습니다. 더 많은 Excel 팁과 요령을 탐색하려면 당사 웹사이트에서 수천 개의 튜토리얼을 제공합니다.

관련 기사:

Excel에서 수식의 셀 참조를 범위 이름으로 변경하기