엑셀 함수 고정하려면 이렇게 잡으면 됩니다
엑셀 함수 고정이 헷갈리는 순간
얼마 전 지인 사무실 PC를 새로 맞춰주고 엑셀 파일까지 같이 봐준 적이 있습니다. CPU나 램보다 더 오래 잡아먹은 게 의외로 엑셀 함수 고정이었습니다. 수식은 맞게 쓴 것 같은데 아래로 드래그하면 값이 틀어지고, 옆으로 복사하면 참조 셀이 엉뚱한 곳으로 밀리는 상황이었죠.
엑셀에서 함수를 고정한다는 말은 대부분 셀 참조를 고정한다는 뜻입니다. 예를 들어 =A2*B1 같은 수식이 있을 때, 이걸 아래로 복사하면 엑셀은 알아서 =A3*B2처럼 참조 위치를 움직입니다. 이 자동 이동이 편할 때도 있지만, 할인율이나 환율처럼 한 셀을 계속 물고 가야 하는 계산에서는 바로 문제가 됩니다.
PC 조립으로 치면 메인보드에 CPU를 정확히 꽂았는데 쿨러 고정을 안 한 상태와 비슷합니다. 처음엔 돌아가는 것처럼 보여도 부하가 걸리면 바로 티가 납니다. 엑셀 수식도 참조 고정을 제대로 안 하면 데이터가 많아질수록 오류가 눈덩이처럼 커집니다.
$ 기호가 하는 일
엑셀 함수 고정에서 가장 자주 쓰는 기호는 $입니다. 이 기호는 셀 주소에서 행이나 열을 움직이지 못하게 잡아줍니다. A1은 자유롭게 움직이는 참조이고, $A$1은 열 A와 1행을 둘 다 고정한 참조입니다.
- A1: 행과 열이 모두 이동합니다.
- $A$1: 행과 열이 모두 고정됩니다.
- $A1: 열 A만 고정되고 행은 이동합니다.
- A$1: 행 1만 고정되고 열은 이동합니다.
예를 들어 상품 가격이 A열에 있고, 할인율이 E1 셀에 있다면 수식은 보통 =A2*(1-$E$1)처럼 씁니다. 이 상태로 아래로 드래그하면 A2는 A3, A4로 바뀌지만 E1은 계속 E1로 남습니다. 할인율은 한 곳에만 입력하고, 계산식은 여러 줄에 복사하는 구조가 되는 겁니다.
여기서 실수하는 분들이 많습니다. =A2*(1-E1)로만 쓰면 첫 줄은 맞습니다. 그런데 아래로 내리는 순간 E1이 E2, E3으로 밀립니다. E2나 E3이 비어 있으면 계산값이 이상해지고, 다른 숫자가 들어 있으면 더 무섭습니다. 오류처럼 보이지 않는데 결과만 틀리거든요.
F4 키로 빠르게 고정하는 방법
직접 $를 입력해도 되지만, 실무에서는 F4 키를 쓰는 게 훨씬 빠릅니다. 수식 입력 중에 셀 주소에 커서를 둔 상태에서 F4를 누르면 참조 방식이 순서대로 바뀝니다.
- A1 상태에서 F4 한 번: $A$1
- F4 두 번: A$1
- F4 세 번: $A1
- F4 네 번: A1
노트북에서는 F4가 화면 밝기나 기능키로 묶여 있는 경우가 있습니다. 이때는 Fn + F4를 눌러야 할 수 있습니다. 윈도우 세팅 오래 하다 보면 제조사마다 Fn 키 동작이 달라서 이런 부분에서 시간을 잡아먹는 경우가 꽤 있습니다. 엑셀 문제가 아니라 키보드 기능키 설정 문제인 셈이죠.
제가 가장 많이 쓰는 방식은 이렇습니다. 먼저 수식을 대충 입력합니다. 그다음 고정해야 할 셀 주소를 클릭하거나 커서를 올리고 F4를 누릅니다. 드래그 복사 후 몇 줄을 직접 눌러 참조가 의도대로 유지되는지 확인합니다. 이 확인 한 번이 은근히 중요합니다.
언제 전체 고정이고 언제 일부 고정일까
처음에는 전부 $A$1처럼 전체 고정만 써도 됩니다. 할인율, 부가세율, 환율, 기준값처럼 하나의 셀을 계속 참조해야 하는 경우는 전체 고정이 가장 안전합니다. 초보자 입장에서는 이 방식만 알아도 엑셀 함수 고정 문제의 절반 이상은 해결됩니다.
그런데 표가 가로와 세로로 동시에 확장될 때는 일부 고정이 필요합니다. 예를 들어 왼쪽에는 제품명이 있고, 위쪽에는 월별 기준값이 있는 표를 만든다고 해보겠습니다. 아래로 내려갈 때는 제품 행이 바뀌어야 하고, 오른쪽으로 갈 때는 월 열이 바뀌어야 합니다. 이런 경우에는 $A1이나 A$1처럼 열만 고정하거나 행만 고정하는 참조가 필요합니다.
실제 업무 파일에서는 단가표, 근무표, 판매량 집계표에서 많이 나옵니다. 특히 VLOOKUP, XLOOKUP, SUMIF, COUNTIF 같은 함수와 같이 쓰면 고정 여부가 결과를 크게 좌우합니다. 찾을 범위는 고정해야 하는데 찾을 값은 이동해야 하는 식이 많기 때문입니다.
예를 들어 =VLOOKUP(A2,$F$2:$H$100,2,FALSE)처럼 쓰면 A2는 아래로 내려가며 A3, A4로 바뀌고, 검색 범위인 F2:H100은 그대로 유지됩니다. 이걸 고정하지 않으면 아래로 복사할수록 검색 범위도 같이 내려가서 앞쪽 데이터가 빠집니다. 처음 몇 줄은 맞고 중간부터 틀어지는 전형적인 패턴입니다.
오류를 줄이는 확인 순서
엑셀 함수 고정은 외우는 것보다 확인 습관이 더 중요합니다. 저는 수식을 만든 뒤 바로 대량 복사하지 않습니다. 먼저 3줄 정도만 복사하고, 각 셀을 눌러 참조 위치를 봅니다. 이때 고정되어야 할 셀이 움직였으면 바로 수정합니다.
- 기준값 셀은 $가 양쪽에 붙었는지 확인합니다.
- 아래로 복사할 수식은 행 번호가 어떻게 변하는지 봅니다.
- 오른쪽으로 복사할 수식은 열 문자가 어떻게 변하는지 봅니다.
- 범위 참조는 시작점과 끝점이 같이 밀리지 않는지 확인합니다.
또 하나 좋은 방법은 기준값 셀에 일부러 눈에 띄는 숫자를 넣어보는 겁니다. 예를 들어 할인율을 10%가 아니라 잠깐 50%로 바꿔보면 계산값 변화가 확실히 보입니다. 테스트가 끝나면 원래 값으로 돌리면 됩니다. 하드웨어 테스트할 때 온도와 클럭을 일부러 모니터링하는 것처럼, 엑셀도 수식이 실제로 어디를 보고 있는지 눈으로 확인해야 합니다.
수식이 길어졌다면 이름 정의를 쓰는 것도 괜찮습니다. 예를 들어 E1 셀에 할인율이 있다면 그 셀 이름을 할인율로 지정하고 =A2*(1-할인율)처럼 쓸 수 있습니다. 다만 여러 사람이 같이 쓰는 파일이라면 너무 많은 이름 정의는 오히려 관리가 어려워질 수 있습니다. 저는 개인 파일이나 반복 양식에는 쓰고, 전달용 파일에서는 $ 참조를 더 선호합니다.
엑셀 함수 고정은 처음엔 기호 하나 차이처럼 보이지만, 실제로는 계산표의 신뢰도를 잡아주는 기본 세팅입니다. 수식이 이상하게 변한다 싶을 때는 함수 이름부터 의심하기보다 셀 참조가 움직였는지 먼저 보는 게 빠릅니다. 제 경험상 엑셀 오류의 상당수는 어려운 함수 때문이 아니라, 고정해야 할 셀을 그냥 흘려보낸 데서 시작했습니다.
