Excel 절대 참조 (만들고 사용하는 방법)

Excel에서 수식에서 셀을 참조할 때 기본 참조 유형은 상대 참조입니다. 이러한 참조는 수식이 복사될 때 해당 열과 행에 따라 변경됩니다. 수식이 어디로 복사되더라도 참조를 고정하고 싶다면 절대 참조를 사용해야 합니다.

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

샘플 파일 무료 다운로드

비디오: 절대 참조

절대 참조란 무엇인가요?

절대 참조는 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 값 찾기 및 해당 일치 값 반환하기

D4:D5에 있는 이름 목록을 검색하고 범위(A4:B8)에 제공된 직원 이름 및 연봉에 따라 해당 연봉을 반환하려고 한다고 가정해 봅시다.

검색을 위한 일반적인 수식은 다음과 같습니다:

=VLOOKUP(lookup_value, table_range, column_index, logical)

값을 검색하고 해당 일치 값을 반환하기 위해 수식에서 상대 참조를 사용하는 경우:

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

그런 다음 자동 채우기 핸들을 아래로 드래그하여 아래 값들을 검색하면 오류가 반환됩니다.

자동 채우기 핸들을 아래로 드래그하여 수식을 아래 셀로 복사하면 수식의 참조가 자동으로 한 행씩 아래로 조정됩니다. 그 결과, 테이블 범위 A4:B8에 대한 참조는 A5:B9가 됩니다. Lisa가 범위 A5:B9에서 발견되지 않으므로 수식은 오류를 반환합니다.

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

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

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

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

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

여러 수식에서 셀 참조를 절대 참조로 만들기 위해 수식 셀을 선택하고 "Kutools" > "More" > "Convert Refers"를 클릭합니다. 그런 다음 "To absolute" 옵션을 선택하고 "Ok" 또는 "Apply"를 클릭합니다. 이제 선택한 수식의 모든 셀 참조가 절대 참조로 변환되었습니다.

상대 참조와 혼합 참조

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

상대 참조 는 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" 키는 상대, 절대 및 혼합 참조 간에 전환할 수 있습니다.