Excel 절대 참조(만드는 방법 및 사용법)

Excel 에서 수식 내 셀을 참조할 때 기본 참조 유형은 상대 참조입니다。 이 참조는 수식이 다른 셀로 복사될 때 열과 행의 상대적 위치에 따라 변경됩니다。 수식이 복사되는 위치와 관계없이 참조를 고정하려면 절대 참조를 사용해야 합니다。

• 절대 참조란 무엇인가요？
• 절대 참조를 만드는 방법
• 예제와 함께 절대 참조 사용하기
• 총계 대비 백분율 계산
• 값을 찾아 해당하는 일치 값을 반환
• 2 번의 클릭으로 Kutools 로 셀 참조를 일괄적으로 절대 참조로 만들기
• 상대 참조 및 혼합 참조
• 기억할 사항

샘플 파일 무료 다운로드

동영상： 절대 참조

절대 참조란 무엇인가요？

절대 참조는 Excel 에서 사용하는 셀 참조 유형 중 하나입니다。

수식이 다른 셀로 복사될 때 상대적 위치에 따라 변경되는 상대 참조와 달리， 절대 참조는 수식이 복사되거나 이동되는 위치와 관계없이 항상 일정하게 유지됩니다。

절대 참조는 수식 내 열과 행 참조 앞에 달러 기호($)를 추가하여 생성합니다. 예를 들어, 셀 A1 에 대한 절대 참조를 만들려면 $A$1 로 표시해야 합니다。

절대 참조는 수식을 여러 셀에 복사할 때 특정 셀이나 범위를 고정된 상태로 참조하고 싶을 때 유용합니다。

예를 들어, 범위 A4:C7 에는 제품 가격이 포함되어 있고, 셀 B2 에 있는 세율을 기준으로 각 제품의 납부 세액을 계산하려고 합니다。

「=B5*B2」와 같이 수식에서 상대 참조를 사용하면 자동 채우기 핸들을 아래로 드래그해 수식을 적용할 때 잘못된 결과가 반환됩니다. 이는 셀 B2 에 대한 참조가 수식 내 다른 셀들과의 상대적 위치에 따라 변경되기 때문입니다. 이제 셀 C6 의 수식은 「=B6*B3」, 셀 C7 의 수식은 「=B7*B4」가 됩니다。

하지만 「=B5*$B$2」와 같이 셀 B2 에 대해 절대 참조를 사용하면 자동 채우기 핸들을 아래로 드래그해도 세율이 모든 셀에서 동일하게 유지되어 올바른 결과를 얻을 수 있습니다。

상대 참조 사용		절대 참조 사용

절대 참조를 만드는 방법

Excel 에서 절대 참조를 만들려면 수식 내 열과 행 참조 앞에 달러 기호($)를 추가하면 됩니다。 절대 참조를 만드는 방법은 두 가지가 있습니다：

셀 참조에 직접 달러 기호 추가하기

셀에 수식을 입력할 때 절대 참조로 만들고자 하는 열과 행 참조 앞에 직접 달러 기호($)를 추가할 수 있습니다。

예를 들어, 셀 A1 과 B1 의 숫자를 더하면서 둘 다 절대 참조로 만들려면 「=$A$1++$B$1」처럼 수식을 입력하면 됩니다。 이렇게 하면 수식을 다른 셀로 복사하거나 이동할 때 셀 참조가 고정된 상태로 유지됩니다。

또는 이미 작성된 수식의 참조를 절대 참조로 변경하려면 해당 셀을 선택한 후 수식 표시줄에서 달러 기호($)를 추가할 수 있습니다。

단축키 F4 를 사용해 상대 참조를 절대 참조로 변환하기

1. 수식이 포함된 셀을 두 번 클릭하여 편집 모드로 진입합니다；
2. 절대 참조로 만들려는 셀 참조에 커서를 놓습니다；
3. 키보드에서 “F4" 키를 눌러 열 및 행 참조 앞에 달러 기호($)가 모두 추가될 때까지 참조 유형을 전환합니다；
4. 편집 모드를 종료하고 변경 사항을 적용하려면 「Enter」 키를 누릅니다。

F4 키는 참조를 상대 참조， 절대 참조， 혼합 참조 사이에서 순환적으로 전환합니다。

A1 → $A$1 → A$1 → $A1 → A1

수식 내 모든 참조를 절대 참조로 만들려면 수식 표시줄에서 전체 수식을 선택한 후 “F4" 키를 눌러 열과 행 참조 모두 앞에 달러 기호가 추가될 때까지 참조 유형을 전환하면 됩니다。

A1+B1 → $A$1++$B$1 → A$1++B$1 → $A1+$B1 → A1+B1

예제와 함께 절대 참조 사용하기

이 부분에서는 Excel 수식에서 절대 참조를 언제， 어떻게 사용하는지 보여주는 2 예제를 제공합니다。

예제 1 전체 대비 백분율 계산하기

각 과일의 판매량이 포함된 데이터 범위(A3:B7)과 총 판매액이 포함된 셀 B8 이 있다고 가정합니다。 이제 각 과일 판매액이 전체에서 차지하는 백분율을 계산하려고 합니다。

전체 대비 백분율을 계산하는 일반적인 수식：

Percentage = Sale/Amount

다음과 같이 수식에 상대 참조를 사용해 첫 번째 과일의 백분율을 구합니다：

=B4/B8

자동 채우기 핸들을 아래로 드래그해 다른 과일의 백분율을 계산하면 #DIV/0! 오류가 반환됩니다。

자동 채우기 핸들을 아래로 드래그해 수식을 아래 셀에 복사하면 상대 참조 B8 이 상대적 위치에 따라 다른 셀 참조(B9, B10, B11)로 자동 조정됩니다. 그런데 셀 B9, B10, B11 은 비어 있으므로(값이 0), 나누는 값이 0 이 되어 수식에서 오류가 발생합니다。

이 오류를 해결하려면 수식 내 셀 참조 B8 을 절대 참조($B$8)로 만들어 수식을 어디로 이동하거나 복사하더라도 변경되지 않도록 해야 합니다。 이제 수식은 다음과 같이 업데이트됩니다：

=B4/$B$8

그런 다음 자동 채우기 핸들을 아래로 드래그해 다른 과일의 백분율을 계산합니다。

예제 2 값을 찾아 해당하는 일치 값 반환하기

범위(A4:B8)에 제공된 직원 이름과 연봉을 기준으로 D4:D5 의 이름 목록을 찾아 각각의 연봉을 반환하려고 합니다。

값을 조회하는 일반적인 수식：

=VLOOKUP(lookup_value, table_range, column_index, logical)

다음과 같이 수식에서 상대 참조를 사용해 값을 조회하고 해당하는 일치 값을 반환합니다：

=VLOOKUP(D4,A4:B8,2,FALSE)

그런 다음 자동 채우기 핸들을 아래로 드래그해 아래의 값을 조회하면 오류가 반환됩니다。

채우기 핸들을 아래로 드래그해 수식을 아래 셀에 복사하면 수식 내 참조가 자동으로 한 행씩 아래로 조정됩니다. 그 결과 테이블 범위 A4:B8 이 A5:B9 로 변경됩니다. A5:B9 범위에서 Lisa 를 찾을 수 없으므로 수식에서 오류가 반환됩니다。

오류를 방지하려면 수식에서 상대 참조 A4:B8 대신 절대 참조 $A$4:$B$8 를 사용하세요：

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

그런 다음 자동 채우기 핸들을 아래로 드래그해 Lisa 의 연봉을 가져옵니다。

2 번의 클릭으로 Kutools 로 일괄적으로 셀 참조를 절대 참조로 만들기

직접 입력하거나 F4 단축키를 사용하는 경우 Excel 에서는 한 번에 하나의 수식만 변경할 수 있습니다. 수백 개의 수식에서 셀 참조를 절대 참조로 만들고 싶다면 「Kutools for Excel」의 「셀 참조 변환」 도구를 통해 2 번의 클릭으로 작업을 처리할 수 있습니다。

여러 수식에서 셀 참조를 절대 참조로 만들려면 수식이 포함된 셀을 선택한 후 「Kutools」 > 「더 보기」 > 「셀 참조 변환」을 클릭합니다。 그런 다음 「절대 참조로」 옵션을 선택하고 「확인」 또는 「적용」을 클릭하면 선택한 수식의 모든 셀 참조가 절대 참조로 변환됩니다。

상대 참조 및 혼합 참조

절대 참조 외에도 상대 참조와 혼합 참조라는 두 가지 다른 참조 유형이 있습니다。

상대 참조는 Excel 의 기본 참조 유형으로， 열과 행 참조 앞에 달러 기호($)가 없습니다。 상대 참조가 포함된 수식을 다른 셀로 복사하거나 이동하면 참조가 상대적 위치에 따라 자동으로 변경됩니다。

예를 들어， 셀에 「=A1+1」과 같은 수식을 입력한 후 자동 채우기 핸들을 아래로 드래그해 다음 셀에 수식을 채우면 수식이 자동으로 「=A2+1」로 변경됩니다。

혼합 참조는 절대 참조와 상대 참조가 결합된 형태입니다。 즉， 수식을 복사하거나 채울 때 행 또는 열 중 하나만 달러 기호($)로 고정합니다。

곱셈표를 예로 들면, 행과 열 데이터에는 1 부터 9 까지의 숫자가 있어 서로 곱하게 됩니다。

시작하려면 셀 C3 에 「=B3*C2」 수식을 사용해 B3 셀의 1 과 첫 번째 열의 숫자(1)를 곱할 수 있습니다。 그러나 자동 채우기 핸들을 오른쪽으로 드래그해 다른 셀을 채우면 첫 번째 결과를 제외한 나머지 결과가 모두 잘못된 것을 알 수 있습니다。

이는 수식을 오른쪽으로 복사할 때 행 위치는 변경되지 않지만 열 위치가 B3 에서 C3, D3 등으로 변경되기 때문입니다。 그 결과 오른쪽 셀(D3, E3 등)의 수식이 「=C3*D2」， 「=D3*E2」 등으로 변경되는데， 실제로 원하는 수식은 「=B3*D2」， 「=B3*E2」 등입니다。

이 경우 “B3"의 열 참조를 고정하기 위해 달러 기호($)를 추가해야 합니다。 다음 수식을 사용하세요：

=$B3*C2

이제 수식을 오른쪽으로 드래그하면 결과가 올바르게 표시됩니다。

다음으로 C2 셀의 1 을 아래 행의 숫자들과 곱해야 합니다。

수식을 아래로 복사할 때 C2 셀의 열 위치는 변경되지 않지만 행 위치가 C2 에서 C3, C4 등으로 변경됩니다。 그 결과 아래 셀의 수식이 「$B4C3」， 「$B5C4」 등으로 변경되어 잘못된 결과를 생성합니다。

이 문제를 해결하려면 수식을 채우기 위해 자동 채우기 핸들을 아래로 끌 때 행 참조가 고정되도록 “C2”를 “C$2”으로 변경하세요。

=$B3*C$2

이제 자동 채우기 핸들을 오른쪽이나 아래로 끌어 모든 결과를 얻을 수 있습니다。

기억할 사항

• 셀 참조 요약

  유형	예시	요약
  절대 참조	$A$1	수식을 다른 셀로 복사할 때 절대 변경되지 않음
  상대 참조	A1	수식을 다른 셀로 복사할 때 행과 열 참조가 상대적 위치에 따라 모두 변경됨
  혼합 참조	$A1/A$1	수식을 다른 셀로 복사할 때 행 참조는 변경되지만 열 참조는 고정됨/열 참조는 변경되지만 행 참조는 고정됨；

• 일반적으로 절대 참조는 수식을 이동할 때 절대 변경되지 않습니다。 그러나 워크시트의 맨 위나 왼쪽에 행이나 열을 추가하거나 삭제하면 절대 참조가 자동으로 조정됩니다。 예를 들어 수식 「=$A$1+1」에서 시트 맨 위에 행을 삽입하면 수식이 자동으로 「=$A$2+1」로 변경됩니다。

  

• “F4" 키를 사용하면 상대 참조， 절대 참조， 혼합 참조 간에 전환할 수 있습니다。