[8편: 수식 복사할 때 자꾸 깨지는 이유? 엑셀 상대참조와 절대참조($)의 비밀]

 

[8편]: 수식 복사할 때 자꾸 깨지는 이유? 엑셀 상대참조와 절대참조($)의 비밀

엑셀을 처음 배울 때 우리를 가장 당황스럽게 만드는 순간이 있습니다. 분명 첫 번째 칸에는 계산 결과를 완벽하게 구했는데, 그 셀을 잡고 아래로 드래그(자동 채우기)를 했더니 아래 칸들이 전부 0으로 변하거나, 이상한 숫자가 나오거나, 심지어 #VALUE! 같은 무시무시한 에러 메시지를 뿜어내는 상황입니다.

"수식은 똑같이 복사했는데 왜 결과가 다르게 나올까?"

이 문제의 원인은 99% 엑셀의 '참조 방식'을 이해하지 못했기 때문입니다. 엑셀은 기본적으로 수식을 복사할 때 위치를 자동으로 이동시키려는 성질을 가지고 있습니다. 이를 '상대참조'라고 합니다. 그리고 특정 셀의 위치를 고정하는 기술을 '절대참조'라고 부릅니다. 오늘 이 두 가지 개념과 함께, 키보드에서 F4 키 하나로 이 문제를 평생 해결하는 방법을 알아보겠습니다.

## 1. 처음엔 누구나 겪는 실수: 단가와 수량 계산하기

이해를 돕기 위해 아주 간단한 예시를 생각해보겠습니다.

노트북(단가 1,000,000원)을 3대 샀습니다. 그럼 총금액은 단가 셀과 수량 셀을 곱하면 됩니다. 엑셀 창에 =A 2*B 2라고 입력하면 아주 예쁘게 3,000,000원이라는 결과가 나옵니다.

그 아래 줄에는 마우스(단가 30,000원) 5대를 산 내역이 있습니다. 첫 번째 칸의 수식을 그대로 복사해서 아래로 붙여넣거나 마우스로 드래그하면, 엑셀은 똑똑하게도 =A 3*B 3으로 수식을 변경하여 150,000원이라는 올바른 값을 찾아줍니다.

이것이 바로 상대참조입니다. 내가 수식을 한 칸 아래로 내리면, 수식 안에 들어있는 셀의 주소들도 같이 한 칸 아래로 내려가는 방식입니다. 일반적인 가로/세로 표를 계산할 때는 이 상대참조 덕분에 수백 수천 개의 행을 일일이 계산하지 않고 마우스 드래그 한 번으로 끝낼 수 있습니다.

## 2. 문제가 발생하는 순간: 고정된 '할인율' 적용하기

진짜 문제는 모든 계산 행이 1대1로 매칭되지 않을 때 발생합니다.

위에서 계산한 총금액에 '기획전 10% 할인'을 일괄적으로 적용해야 한다고 가정해봅시다. 이 10%라는 할인율은 표 안에 매번 적혀있는 것이 아니라, 저 멀리 외딴 셀인 D 1 칸에 딱 한 번만 적혀 있습니다.

처음 노트북 행에 수식을 작성합니다. =C 2*D 1 (노트북 총금액 * 할인율 10%). 결과는 정확하게 300,000원 선으로 잘 나옵니다.

이제 신나게 이 수식을 잡고 아래 마우스 행으로 드래그합니다. 그런데 결과가 0원이나 에러로 바뀝니다. 깜짝 놀라서 마우스 행의 수식을 더블클릭해 보면 수식이 이렇게 변해 있습니다. =C 3*D 2.

엑셀은 상대참조 원칙에 충실하게 수식을 한 칸 내리면서 할인율 셀도 D 1에서 D 2로 한 칸 내려버린 것입니다. 하지만 D 2 칸은 텅 빈 빈칸이거나 전혀 다른 텍스트가 적혀있을 확률이 높습니다. 그래서 엑셀은 빈칸을 0으로 인식하여 결과값을 0으로 만들어 버립니다.

## 3. 해결책은 달러 기호($): "너는 움직이지 마!" 절대참조

바로 이때 엑셀에게 "다른 애들은 밑으로 내려가도 좋지만, 할인율이 있는 D1 셀만큼은 절대로 움직이지 말고 꽉 붙잡고 있어!"라고 명령을 내려야 합니다. 셀 주소를 고정하는 이 역할을 하는 것이 바로 달러 기호($)입니다.

주소를 고정하는 방법은 간단합니다. 수식 입력창에서 D 1이라고 적힌 부분에 달러 기호를 붙여 $D$1 형태로 만들어 주는 것입니다.

• D 1: 수식을 복사하면 위치에 따라 주소가 변함 (상대참조)

• $D$1: 수식을 어디로 복사하든 무조건 D 1 셀만 바라봄 (절대참조)

이제 첫 칸의 수식을 =C 2*$D$1로 수정하고 아래로 드래그해보면, 아래 칸의 수식은 =C 3*$D$1로 바뀝니다. 앞의 금액 셀(C 2 -> C 3)은 자연스럽게 내려갔지만, 뒤의 할인율 셀($D$1)은 미동도 하지 않고 고정되어 완벽한 할인 금액을 계산해 냅니다.

## 4. 마우스로 타이핑하지 마세요, 단축키는 'F 4'

달러 기호를 키보드로 일일이 Shift + 4를 눌러 입력하는 것은 매우 번거롭습니다. 엑셀에서는 이를 위한 아주 강력한 단축키를 제공합니다. 바로 키보드 맨 상단에 있는 F 4 키입니다.

1. 수식을 입력하는 도중(예: =C 2*D 1까지 입력한 상태)에 커서가 D 1 뒤에 있을 때 F 4 키를 한 번 눌러봅니다.

2. 자동으로 $D$1로 변환됩니다.

3. 이 상태에서  F 4를 한 번 더 누르면 D$1 (행만 고정)이 되고, 한 번 더 누르면 $D 1 (열만 고정)이 되며, 한 번 더 누르면 다시 D 1으로 돌아옵니다.

이를 '혼합참조'라고 부르는데, 초보자 단계에서는 우선 "수식을 작성하다가 고정해야 할 셀이 나오면 고민하지 말고 F 4 키를 딱 한 번 누른다"는 공식만 기억하셔도 실무 오류의 90% 이상을 예방할 수 있습니다.

## 5. 절대참조를 써야 하는 대표적인 실무 상황 체크리스트

업무를 하실 때 아래와 같은 상황을 마주한다면, '아, 여기선 F4를 눌러야겠구나!' 하고 떠올리시면 좋습니다.

• 환율 계산할 때: 수많은 외화 금액에 상단의 고정된 '오늘의 환율' 셀을 곱해야 할 때

• 목표 달성률 구할 때: 각 개인별 실적을 회사의 '전체 목표치(단 하나의 셀)'로 나눌 때

• V LOOKUP 함수 쓸 때: 참조하고자 하는 원본 데이터의 '전체 범위'를 지정할 때 (범위가 틀어지는 것을 방지)

처음에는 이 달러 기호가 시각적으로 복잡해 보일 수 있습니다. 하지만 원리는 간단합니다. 움직여야 하는 주소는 그대로 두고, 붙박이처럼 고정되어야 하는 주소에는 $(달러) 자물쇠를 채워준다고 생각하세요. 이 개념만 확실히 내 것으로 만들면, 더 이상 엑셀 수식을 복사할 때마다 결과가 깨질까 봐 가슴 졸이는 일은 없을 것입니다.

3줄 핵심 요약

• 상대참조는 수식을 복사할 때 셀 주소가 복사하는 방향에 맞추어 자동으로 같이 움직이는 방식이다.

• 절대참조($)는 수식을 어디로 복사하든 특정 셀의 위치를 자물쇠처럼 단단히 고정하는 방식이다.

• 수식을 입력할 때 고정하고 싶은 셀 주소 뒤에서 키보드 F 4 키를 누르면 자동으로 달러($) 기호가 붙어 고정된다.

댓글로 함께 나눠요: 오늘 배운 상대참조와 절대참조 개념 중 혹시 헷갈리거나, 수식을 복사하다가 겪었던 나만의 에러 경험이 있다면 댓글로 남겨주세요! 함께 고민해 드리겠습니다.