엑셀 수식 고정, 복사해도 기준 셀 안 바뀌는 $ 사용법

엑셀 수식 고정은 수식 전체를 얼려 두는 기능이 아니다. 수식을 복사할 때 계속 같은 곳을 봐야 하는 셀 주소에 $를 붙이는 것이다.

예를 들어 I1에 부가세율 10%가 있고 E열의 공급가액마다 부가세를 계산한다면 F2 수식은 다음처럼 쓴다.

=E2*$I$1

아래로 복사하면 E2는 E3, E4로 바뀌지만 $I$1은 움직이지 않는다. 이것이 절대참조다. 핵심은 모든 주소를 고정하는 게 아니라 바뀌면 안 되는 기준 셀만 고정하는 데 있다.

1. 첫 행만 맞고 아래부터 틀리는 이유

엑셀에서 $가 없는 셀 주소는 기본적으로 상대참조다. 수식이 이동한 거리만큼 참조 주소도 함께 이동한다.

F2에 다음 수식이 있다고 보자.

=E2*I1

이를 F3으로 한 칸 내리면 다음처럼 변한다.

=E3*I2

E2가 E3으로 바뀌는 것은 맞다. 두 번째 품목의 공급가액을 써야 하기 때문이다. 하지만 부가세율은 여전히 I1에 있는데 I1까지 I2로 내려가면 빈 셀을 곱해 0이 나오거나 예상과 다른 결과가 생긴다.

따라서 처음 수식을 다음처럼 고쳐야 한다.

=E2*$I$1

수식을 복사한 뒤에는 결과만 보지 말고 F3이나 F4를 선택해 수식 입력줄을 확인하는 편이 안전하다. 공급가액 주소는 행에 맞춰 바뀌고, $I$1은 그대로 남아 있어야 한다.

2. $는 바로 뒤의 주소를 고정한다

셀 주소는 열 문자와 행 번호로 이뤄진다. $가 어느 부분 앞에 붙었는지를 보면 무엇이 고정됐는지 바로 알 수 있다.

A1: 행과 열이 모두 움직인다.

$A$1: A열과 1행을 모두 고정한다.

A$1: 1행만 고정하고 열은 움직인다.

$A1: A열만 고정하고 행은 움직인다.

A1을 아래로 복사하면 A2가 되고, 오른쪽으로 복사하면 B1이 된다.

$A$1은 어느 방향으로 복사해도 그대로다.

A$1은 아래로 복사해도 A$1이지만 오른쪽으로 복사하면 B$1이 된다.

$A1은 오른쪽으로 복사해도 $A1이지만 아래로 복사하면 $A2가 된다.

외우기보다 $가 바로 앞의 열 문자 또는 행 번호를 붙잡는다고 이해하면 편하다.

3. F4로 참조 형태 바꾸기

Windows용 Excel에서는 다음 순서로 바꾼다.

수식이 들어 있는 셀을 선택한다.

F2를 누르거나 셀을 두 번 클릭해 편집 상태로 들어간다.

수식 입력줄에서 고정할 셀 주소를 선택한다.

F4를 눌러 원하는 형태가 나오면 Enter로 확정한다.

상대참조 A1에서 시작하면 F4를 누를 때마다 다음 순서로 순환한다.

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

Microsoft의 Excel 바로 가기 키 안내도 수식에서 셀 참조나 범위를 선택한 경우 F4가 절대·상대 참조 조합을 차례로 전환한다고 설명한다. Mac용 Excel은 Command+T 또는 F4를 지원한다.

https://support.microsoft.com/ko-KR/Accessibility/excel/keyboard-shortcuts-in-excel

F4를 눌렀는데 직전 작업이 반복된다면 수식 편집 상태가 아니거나 참조가 선택되지 않은 경우가 많다. F4는 일반 상태에서는 마지막 작업 반복 기능으로도 쓰이기 때문이다.

노트북에서 밝기나 음량 기능이 실행되면 Fn+F4를 시도한다. 웹용 Excel에서 브라우저가 키를 가로채거나 키보드 배열 때문에 다르게 동작한다면 $를 직접 입력해도 결과는 같다. 편집 상태는 F2, 셀 더블클릭 또는 수식 입력줄 선택으로 들어갈 수 있다.

https://support.microsoft.com/ko-kr/excel/edit-cell-contents

4. 범위와 가로세로 표 고정하기

절대참조는 한 셀뿐 아니라 범위에도 적용된다. VLOOKUP 수식을 아래로 복사하면서 조회표가 밀리지 않게 하려면 범위의 시작과 끝을 모두 고정한다.

=VLOOKUP(A2,$D$2:$F$100,3,FALSE)

이 수식을 아래로 복사하면 찾을 값인 A2는 A3, A4로 바뀐다. 조회 범위 $D$2:$F$100은 그대로 유지된다. SUMIF나 SUMIFS의 조건 범위와 합계 범위도 같은 방식으로 고정할 수 있다.

가로와 세로 방향으로 동시에 복사하는 표에서는 혼합참조가 필요하다. B열에 상품 가격이 있고 2행에 할인율이 가로로 놓였다면 다음과 같이 쓸 수 있다.

=$B3*(1-C$2)

$B3은 가격이 있는 B열을 고정하되 상품별 행은 바뀌게 한다. C$2는 할인율이 있는 2행을 고정하되 오른쪽으로 복사할 때 C열이 D열, E열로 움직이게 한다. 이 수식 하나를 오른쪽과 아래로 채우면 각 상품과 할인율의 조합을 계산할 수 있다.

누적 합계처럼 시작점만 고정할 수도 있다.

=SUM($A$2:A2)

아래로 복사하면 끝점만 A3, A4로 늘어나 범위가 $A$2:A3, $A$2:A4로 확장된다. $는 한 셀을 붙잡는 데만 쓰는 기호가 아니라 복사될 범위의 움직임을 설계하는 기호에 가깝다.

5. 고정했는데 결과가 이상할 때

모든 주소에 $를 붙이는 것이 안전한 방법은 아니다. 행마다 바뀌어야 할 공급가액까지 $E$2로 고정하면 모든 행이 첫 번째 공급가액만 계산한다. 여러 셀에 같은 값이 반복된다면 변해야 할 참조까지 고정하지 않았는지 살핀다.

$는 복사하거나 채울 때 주소가 이동하는 것을 막는다. 행이나 열을 삽입·삭제해도 주소 문자를 영구적으로 유지한다는 뜻은 아니다. 이런 구조 변경에서는 Excel이 실제 셀 위치에 맞춰 절대참조도 조정할 수 있다.

‘고정’이라는 말이 무엇을 가리키는지도 구분해야 한다.

수식을 복사해도 기준 셀을 유지하려면 $ 절대참조를 쓴다.

현재 계산 결과만 남기려면 복사 후 값 붙여넣기를 쓴다. 이때 원래 수식은 사라진다.

다른 사람이 수식을 수정하지 못하게 하려면 시트 보호가 필요하다.

스크롤해도 머리행이나 첫 열을 보이게 하려면 보기 메뉴의 틀 고정을 쓴다.

수식 복사 문제라면 먼저 “다음 행이나 열에서도 같은 셀을 봐야 하는가?”를 확인하면 된다. 같아야 하는 행·열에만 $를 붙이고, 한 셀에서 결과를 확인한 뒤 나머지 범위로 채우는 순서가 가장 덜 헷갈린다.