Excel 수식에 $를 빠르게 추가하는 방법은 무엇입니까?

Excel에서 수식의 셀 참조는 기본적으로 상대 참조입니다. 예를 들어 "A1"과 같은 참조는 수식을 다른 위치로 복사하거나 드래그할 때 해당 참조가 새로운 위치에 맞춰 자동으로 업데이트됩니다. 예를 들어, 다음과 같은 수식을 입력하면 =A1+B1 C1 셀에 입력하고 이를 H12 셀로 복사하면, 수식은 자동으로 다음처럼 조정됩니다. =F12+G12이와 같은 동작은 많은 경우 유용하지만, 수식이 어디에 있든 특정 셀을 항상 참조해야 할 때는 오류를 발생시킬 수 있습니다. 이러한 자동 조정을 방지하려면 셀 참조를 절대 참조로 변경해야 합니다. 이때 달러 기호($)를 추가하여, 예를 들어 $A$1과 같이 표기합니다. 절대 참조는 복사하거나 드래그해도 변하지 않으므로, 고정 값이나 범위를 사용한 계산에 필수적입니다.

이 가이드에서는 Excel 수식에 $ 기호를 빠르게 추가하는 몇 가지 실용적인 방법을 소개합니다. 적합한 방법을 선택하는 것은 워크시트 구조, 변환해야 할 수식의 수, Excel 버전 및 개인적인 작업 흐름에 따라 달라집니다. 아래는 이 가이드에서 다루는 방법의 요약입니다:

단축키를 사용하여 수식에 $ 추가하기

Kutools for Excel을 사용하여 수식에 $ 추가하기

VBA 매크로를 사용하여 셀 참조를 일괄적으로 절대 참조로 변환하기

단축키를 사용하여 수식에 $ 추가하기

상대 참조, 절대 참조 및 혼합 참조 간 전환을 단축키를 사용하여 쉽게 수행할 수 있습니다. 셀 참조를 변경하려면, 대상 셀을 선택하고 수식 표시줄을 클릭한 후 수정하려는 셀 참조 옆 또는 내부에 커서를 놓습니다. 그런 다음 F4 키를 눌러 사용 가능한 참조 유형을 순환합니다. F4를 누를 때마다 참조는 다음 순서로 업데이트됩니다: 상대 참조 (A1), 절대 참조 ($A$1), 혼합 참조 (열 고정) (A$1), 그리고 혼합 참조 (행 고정) ($A1).

이 방법은 한 개의 수식 내에서 개별 참조를 빠르게 업데이트하는 데 적합합니다. 그러나 각 셀 참조를 수동으로 선택해야 하므로 여러 수식이나 복잡한 수식을 처리할 때 시간이 많이 걸릴 수 있습니다. 또한 반드시 수식 표시줄이나 셀 내에서 직접 편집 중인지 확인하세요. 단축키가 작동하지 않는 경우 일부 키보드에서는 Fn(Funtion) Lock 키 설정이 필요할 수 있으므로 'Fn + F4'를 사용해 보세요.

Kutools for Excel을 사용하여 수식에 $ 추가하기

F4 단축키는 한 번에 하나의 셀 참조만 처리할 수 있기 때문에 여러 셀이나 전체 범위의 수식을 업데이트해야 할 때 비효율적입니다. 여러 수식을 처리하거나 워크시트 전반에 걸쳐 참조 유형을 표준화해야 하는 경우, Kutools for Excel의 Convert Refers 유틸리티가 효율적인 솔루션을 제공합니다.

무료로 Kutools for Excel을 설치한 후, 다음 단계를 따를 수 있습니다:

1. $ 기호를 추가하고자 하는 모든 수식 셀(참조를 절대 참조로 만들기)을 선택하고, Kutools > More > Convert Refers로 이동합니다. 스크린샷 보기:

2. 나타나는 대화 상자에서 원하는 참조 유형을 선택합니다. 모든 셀 참조를 절대 참조로 만들려면 “To absolute” 옵션을 선택합니다.

3. 확인버튼을 클릭하면, 선택된 모든 수식 셀 참조가 지정된 대로 업데이트됩니다.
수식이 외부 시트나 통합 문서의 데이터를 참조하는 경우, 연결된 데이터가 여전히 정확한지 확인하세요. 일부 시나리오에서는 특정 참조를 상대 참조로 유지해야 할 수도 있습니다. Kutools를 사용하면 언제든지 참조를 상대 참조 또는 혼합 참조로 쉽게 되돌릴 수도 있습니다.

이 접근법은 대량의 수식을 처리하거나 일괄 작업이 필요한 경우에 유용합니다. Kutools는 또한 실수로 인한 작업을 쉽게 되돌릴 수 있는 실행 취소 기능도 제공합니다. 

VBA 매크로를 사용하여 셀 참조를 일괄적으로 절대 참조로 변환하기

범위 또는 전체 워크시트의 모든 셀 참조를 자주 절대 참조($A$1 형식)로 변환해야 하는 경우 VBA 매크로를 사용하는 것이 효율적이고 유연한 솔루션입니다. 특히 고급 Excel 사용자나 대규모 데이터 세트를 다룰 때 이 방법은 변환 프로세스를 자동화하며 복잡한 수식 구조도 한 번의 작업으로 처리할 수 있습니다. 수식이 외부 시트나 통합 문서를 참조하는 경우, 절대 참조가 해당 시나리오에 적합한지 확인하세요. 경우에 따라 수식 유연성을 지원하기 위해 혼합 참조가 더 적합할 수 있습니다.

1. Excel 리본 메뉴에서 개발 도구 > Visual Basic으로 이동합니다. Microsoft Visual Basic for Applications 창에서 삽입 > 모듈을 클릭한 후, 새 모듈에 다음 매크로를 붙여넣습니다:

Sub ConvertToAbsoluteReferences()
    Dim rng As Range
    Dim cell As Range
    Dim formulaStr As String
    Dim i As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select the range to convert formulas to absolute references", xTitleId, rng.Address, Type:=8)
    
    Application.ScreenUpdating = False
    
    For Each cell In rng
        If cell.HasFormula Then
            formulaStr = cell.Formula
            For i = 1 To 10 ' Protects from infinite looping
                formulaStr = Replace(formulaStr, "([A-Za-z]+)([0-9]+)", "$$1$$2") ' Basic pattern replacement
            Next i
            cell.Formula = Application.ConvertFormula(formulaStr, xlA1, xlA1, xlAbsolute)
        End If
    Next
    
    Application.ScreenUpdating = True
End Sub

2. 그런 다음 실행을 클릭하여 코드를 실행합니다. 대상 범위를 선택하라는 대화 상자가 나타납니다. 확인 후, 선택된 범위 내의 모든 유효한 셀 참조는 자동으로 절대 참조 형식(달러 기호 포함)으로 변환됩니다.

오류가 발생하면 선택한 범위에 수식이 포함되어 있는지 확인하세요. 이 매크로는 일반적인 Excel 수식의 참조를 변환합니다. 특수 배열 수식이나 비표준 함수는 예상대로 처리되지 않을 수 있으므로 실행 후 영향을 받는 셀을 검토하세요. 특히 중요한 데이터나 민감한 정보가 포함된 경우에는 매크로를 실행하기 전에 항상 백업을 저장하세요.

VBA를 사용하면 완전한 일괄 처리가 가능하므로, 고급 사용자, IT 직원 또는 설치가 제한된 환경에서 작업하는 사람들에게 적합합니다. 하지만 보안 설정으로 인해 일부 시스템에서는 매크로 실행이 비활성화될 수 있으며, 이 경우 Excel 신뢰 센터 설정에서 매크로를 활성화해야 합니다.

요약 제안: Excel에서 셀 참조를 절대 참조로 변환하는 방법을 결정할 때는 변환해야 할 수식의 수와 Excel 기능에 대한 익숙함을 고려하세요. 단일 수식이나 임시 편집에는 단축키를 사용하는 것이 빠르고 편리합니다. 일괄 작업에는 Kutools for Excel과 VBA 매크로가 많은 수작업을 절약할 수 있습니다. 변환 후 결과를 항상 두 번 확인하세요. 특히 복잡한 워크시트나 자동화 도구를 사용할 때 더욱 그렇습니다. 오류나 예상치 못한 결과가 발생하면 백업에서 복원하거나 실행 취소 기능을 사용하세요.