절대 참조로 단일 셀 고정하기

하나의 공유 값을 여러 수식에서 참조해야 하는 경우가 매우 많습니다. 세율, 환율, 목표값 또는 고정 요금 등이 그 예입니다.

이 단일 값은 하나의 셀에 저장되며, 다른 모든 수식은 어디에 복사되더라도 계속 그 셀을 참조해야 합니다. 이를 가능하게 하는 것이 절대 참조입니다.

절대 참조는 열과 행 앞에 모두 달러 기호를 사용하며, $A$1처럼 표시됩니다. 참조 전체가 고정됩니다.

$A$1이 포함된 수식을 시트의 어디에 복사하더라도 해당 부분은 항상 A1 셀을 가리킵니다. 수식에서 고정되지 않은 참조만 이동합니다.

판매세율 0.08이 E1 셀에 있고 주문 합계가 B열에 있다고 가정해 보겠습니다. 각 주문에 세금을 더하려면 C2에 다음을 입력합니다:

=B2*$E$1

B2는 상대 참조이므로 각 행을 따라 이동하고, $E$1은 절대 참조이므로 항상 세율을 가리킵니다.

C2의 수식을 아래로 채워 C3, C4로 복사하면서 각 부분이 어떻게 바뀌는지 확인해 보세요:

• B2는 B3, 그다음 B4가 됩니다 (상대 참조, 이동함)
• $E$1은 매번 $E$1로 유지됩니다 (절대 참조, 고정됨)

따라서 3행에는 =B3*$E$1이 표시되고 4행에는 =B4*$E$1이 표시됩니다. 세율을 잃어버리는 일이 없습니다.

달러 기호를 빼고 =B2*E1이라고 입력해 보세요. 아래로 복사하면 E1이 E2, E3으로 바뀌는데, 이 셀들은 비어 있습니다.

세율 열이 0으로 채워집니다. 숫자가 잘못된 것처럼 보이지만 원인은 간단합니다. 비율 참조가 절대 참조여야 하는데 상대 참조로 되어 있었던 것입니다.

E1을 빠르게 $E$1로 바꾸려면 수식 입력줄에서 E1을 클릭하고 F4를 한 번 누르세요. Excel이 두 달러 기호를 대신 입력해 줍니다.

특히 긴 수식에서는 기호를 직접 입력하는 것보다 훨씬 빠르고 오류도 적습니다.

$B$1에 0.92의 USD에서 EUR로 환산하는 환율이 저장되어 있고, 달러 금액이 A열에 아래로 입력되어 있다고 가정해 보겠습니다. 각 금액을 다음과 같이 환산합니다.

=A2*$B$1

수백 개의 행에 걸쳐 복사해도 모든 환산에 같은 환율이 사용됩니다. B1의 환율을 한 번만 업데이트하면 열 전체가 즉시 다시 계산됩니다.

두 부분이 모두 고정되어 있으므로 절대 참조는 아래로, 가로로 또는 양쪽 방향으로 복사해도 그대로 유지됩니다.

=B2*$E$1을 오른쪽으로 복사하면 =C2*$E$1이 됩니다. B는 C로 바뀌었지만 $E$1은 그대로입니다. 이 고정은 모든 방향에서 작동합니다.

절대 참조는 함수 안에서도 큰 효과를 발휘합니다. B1에 있는 고정된 전체 값에서 각 값이 차지하는 비율을 구하려면 다음과 같이 합니다.

=A2/$B$1

또는 D1에 저장된 목표값과 각 점수를 비교하려면 다음과 같이 합니다.

=IF(A2>=$D$1,"Pass","Fail")

고정된 셀은 모든 행에서 참조 기준점으로 유지됩니다.

절대 참조의 진정한 장점은 하나의 제어 지점에 있습니다. 모든 수식이 같은 고정 셀을 읽기 때문에 해당 셀 하나를 변경하면 열 전체가 한 번에 업데이트됩니다.

E1의 세율을 0.08에서 0.09로 올리면 모든 =B2*$E$1 수식이 즉시 다시 계산됩니다. 수십 개의 수식을 일일이 수정할 필요 없이 입력값 하나가 모두를 바꿉니다.

숙련자는 상수, 비율, 목표값을 시트 상단의 작고 이름이 붙은 영역에 모아 둔 다음 절대 참조로 참조하는 경우가 많습니다.

이렇게 하면 수식을 읽기 쉬워지고 입력값 하나를 변경해 보고서 전체를 업데이트할 수 있습니다. 달러 기호가 모든 수식이 해당 입력값을 가리키도록 유지해 줍니다.

절대 참조를 얼마나 잘 이해했는지 확인해 보세요.

$A$1과 같은 절대 참조는 열과 행을 모두 고정하므로 복사해도 항상 같은 셀을 가리킵니다.

• 비율, 수수료, 목표값처럼 여러 곳에서 공유하는 값에 사용합니다
• 상대 참조와 조합해 필요한 부분만 이동하도록 합니다
• F4를 누르면 두 달러 기호가 즉시 추가됩니다

다음에는 혼합 참조를 사용해 한 번에 한 부분만 고정해 보겠습니다.