엑셀에서 함수를 아래로 복사했더니 범위가 자동으로 바뀌지 않아 아래 셀마다 같은 값만 나온 적 있으신가요.
대부분은 셀 참조 앞에 달러 기호($)가 붙은 절대참조 상태이거나, 계산 옵션이 수동으로 바뀌어 있는 경우입니다.
예를 들어 B2셀에 =A2*$C$1 이라는 수식을 넣고 B10까지 복사하면, A2는 A3, A4로 한 칸씩 자연스럽게 내려가는데 $C$1은 어디로 복사해도 항상 C1만 가리킵니다. 이건 오류가 아니라 절대참조가 의도대로 동작하는 것이지만, 반대로 A2까지 고정할 생각이 없었는데 고정돼 있다면 그게 바로 범위가 안 바뀌는 원인입니다.
이 글을 끝까지 보시면 어디를 눌러야 참조가 다시 자동으로 바뀌는지, 그래도 안 될 때 확인할 원인 4가지까지 바로 확인하실 수 있습니다.
엑셀 함수 복사, 범위가 안 바뀌는 이유부터 확인하세요
참조가 안 바뀌는 원인은 대부분 절대참조($)이며, 참조 부분을 클릭한 뒤 F4를 눌러 상대참조로 되돌리면 바로 해결됩니다.
수식이 들어간 셀을 선택하고 수식 입력줄에서 참조 부분(예: $A$1)을 드래그해 선택한 다음 F4 키를 눌러보세요. 누를 때마다 $A$1 → A$1 → $A1 → A1 순서로 바뀝니다.
$ 표시가 완전히 사라진 A1 상태(상대참조)로 만든 뒤 다시 복사하면, 아래 셀로 내려갈수록 참조가 한 칸씩 자동으로 이동합니다.
실제 업무 예시로 보면, 매출 목록에서 단가(B열)에 부가세율 10%를 곱하는 수식을 C2셀에 =B2*1.1로 넣고 C10까지 복사하면 별도로 $ 표시를 쓰지 않았기 때문에 B2, B3, B4 순서로 자동으로 바뀝니다. 반대로 세율을 D1셀 하나에 따로 두고 각 행에서 그 값을 참조하려면 C2에 =B2*$D$1처럼 D1 앞에만 $를 붙여야, 아래로 복사해도 D1은 고정되고 B열만 움직입니다.
수식이 예상과 다르게 동작할 때는 Ctrl + `(물결표)를 눌러 화면에 계산값 대신 수식 자체를 표시해두면, 어느 셀이 고정돼 있고 어느 셀이 움직이는지 한눈에 비교하며 확인할 수 있어 편리합니다.
| F4 누른 횟수 | 참조 형태 | 복사할 때 동작 |
|---|---|---|
| 0회(기본) | A1 | 행과 열 모두 이동(상대참조) |
| 1회 | $A$1 | 행과 열 모두 고정(절대참조) |
| 2회 | A$1 | 행만 고정(혼합참조) |
| 3회 | $A1 | 열만 고정(혼합참조) |
복사 범위 자동 변경, 단계별로 따라 하기
메뉴 경로를 그대로 따라가시면 화면 캡처 없이도 순서대로 해결하실 수 있습니다.
- 참조가 고정된 셀을 클릭하고 수식 입력줄에서 고치고 싶은 참조 부분을 드래그해 선택합니다.
- F4 키를 눌러 $ 표시를 지우고 A1 형태(상대참조)로 바꾼 뒤 Enter로 확정합니다.
- 수정한 셀의 우측 아래 채우기 핸들(작은 초록 사각형)에 마우스를 올리고 커서가 십자 모양(+)으로 바뀌면 아래로 드래그합니다.
- 드래그 후 우측 하단에 뜨는 자동 채우기 옵션 아이콘을 눌러 [셀 복사]가 아니라 [수식 복사]가 선택돼 있는지 확인합니다.
- 계산 결과가 셀마다 똑같다면 수식 탭 > 계산 옵션에서 [자동]으로 되어 있는지 확인하고, [수동]이면 [자동]으로 바꾸거나 F9를 눌러 강제로 재계산합니다.
- 그래도 안 바뀌면 파일 > 옵션 > 고급 > 편집 옵션에서 [채우기 핸들 및 셀 끌어서 놓기 사용]에 체크가 되어 있는지 확인합니다.
참조를 지정할 때부터 습관을 들이고 싶다면, 수식을 처음 작성하는 시점에 F4로 원하는 형태를 미리 정해두는 방법도 있습니다. 특히 VLOOKUP처럼 조회 범위 전체를 고정해야 하는 함수는 =VLOOKUP(A2,$E$2:$F$100,2,0)처럼 조회표 범위에만 $를 붙이고 찾는 값(A2)은 상대참조로 남겨야, 아래로 복사했을 때 찾는 값만 바뀌고 조회표는 흔들리지 않습니다. 반대로 조회표에까지 $를 빼먹으면 아래로 내려갈수록 조회 범위 자체가 밀려서 #N/A 오류가 뜨는 경우가 많습니다.
더 자세한 공식 설명은 마이크로소프트 공식 지원 문서에서도 확인하실 수 있습니다.
안 될 때 원인별 해결 표
엑셀 함수 복사가 뜻대로 안 될 때는 대부분 이 표 안에서 원인을 찾을 수 있습니다. 지금 상황과 가장 비슷한 줄의 해결법을 그대로 따라 하세요.
| 증상 | 원인 | 해결법 |
|---|---|---|
| 복사해도 숫자가 똑같이 반복 | 참조에 $ 표시(절대참조) | 참조 부분 클릭 후 F4로 A1 형태로 변경 |
| 자동 채우기해도 값이 안 바뀜 | 계산 옵션이 수동으로 설정됨 | 수식 탭 > 계산 옵션 > 자동으로 변경 또는 F9 |
| 셀에 수식 대신 글자 그대로 표시됨 | 셀 서식이 텍스트로 지정됨 | 서식을 표준·숫자로 바꾸고 수식 다시 입력 |
| 표(테이블) 안에서만 이상하게 동작 | 구조적 참조(엑셀 표) 사용 중 | 표 밖 일반 셀에서 작성하거나 @ 기호로 같은 행만 참조되는지 확인 |
| 값 붙여넣기 후 전혀 안 바뀜 | 수식이 아니라 값으로 붙여넣음 | 원본 수식을 복사해 일반 붙여넣기(Ctrl+V)로 다시 시도 |
| 다른 시트를 참조했더니 항상 같은 셀만 나옴 | 시트 간 참조에도 $ 표시가 남아있음 | Sheet1!$A$1처럼 시트명 뒤 참조도 F4로 형태를 확인 후 수정 |
| 정렬·필터 후 수식 결과가 뒤섞임 | 상대참조라서 정렬 시 참조 위치가 함께 이동 | 고정해야 할 범위는 정렬 전에 미리 절대참조로 바꿔둠 |
같이 알아두면 좋은 팁
참조 방식만 이해하면 여러 상황에서 반복해서 써먹을 수 있는 응용 팁 몇 가지를 정리했습니다.
실전 예시로 확인하는 참조 방식
가로 5개 품목, 세로 5개 지점의 단가표를 예로 들어보겠습니다. 맨 위 행(B1:F1)에는 품목명, 맨 왼쪽 열(A2:A6)에는 지점명이 있고, 두 값을 곱해 표 전체를 한 번에 채우려면 B2셀에 =$A2*B$1 을 넣고 이 수식 하나만 표 전체(B2:F6)에 복사하면 됩니다.
| 수식 위치 | 입력할 수식 | 이유 |
|---|---|---|
| B2(표의 첫 칸) | =$A2*B$1 | 열(A)은 고정, 행(1)은 고정해 가로·세로 모두 자동 확장 |
| 오른쪽으로 복사 시 | B$1 → C$1 → D$1 | 열만 이동, 행 1은 계속 고정 |
| 아래로 복사 시 | $A2 → $A3 → $A4 | 행만 이동, 열 A는 계속 고정 |
구조적 참조(엑셀 표)에서는 F4가 다르게 동작합니다
데이터를 삽입 > 표로 만든 뒤 수식을 넣으면 참조가 [표이름[열이름]] 형태의 구조적 참조로 자동 전환됩니다. 이 상태에서는 F4를 눌러도 일반 셀처럼 $ 표시가 붙지 않고, 같은 행만 계산하려면 참조 앞에 @ 기호가 붙은 [@열이름] 형태가 되어야 합니다. 표 전체 열을 고정해서 참조하고 싶다면 @ 기호를 빼고 [표이름[열이름]]으로 직접 입력하면 됩니다.
애초에 왜 절대참조가 걸렸는지 알아두면 다음에 안 헷갈립니다
여러 문서에서 직접 확인해보니, 처음 SUM이나 VLOOKUP을 만들 때 범위를 드래그로 지정하면 엑셀이 자동으로 $ 표시를 붙여주는 경우가 많았습니다. 특히 다른 사람이 만든 서식을 그대로 가져와 쓸 때 이미 절대참조로 걸려 있는 셀을 그대로 복사하면서 문제가 시작되는 경우가 흔했습니다.
새 수식을 만들 때는 참조를 입력한 직후 수식 입력줄에서 $ 표시가 있는지 한 번만 확인하는 습관을 들이면, 나중에 범위 전체를 다시 고치는 수고를 줄일 수 있습니다.
이름정의(수식 탭 > 이름 관리자)로 범위를 이름 붙여 쓰는 경우에도 기본적으로 절대참조처럼 고정되니, 여러 행에 그대로 적용할 표라면 이름정의보다 일반 셀 참조가 더 편합니다.
| 동작 | 단축키 |
|---|---|
| 참조 형태 전환(상대·절대·혼합) | F4 |
| 전체 재계산 | F9 |
| 수식 표시·숨기기 | Ctrl + ` |
| 선택 영역 아래로 수식 채우기 | Ctrl + D |
| 선택 영역 오른쪽으로 수식 채우기 | Ctrl + R |
자주 묻는 질문
Q. 수식을 복사했는데 값이 전부 똑같이 나와요.
참조 앞에 $ 표시가 있는지 먼저 확인하세요. $A$1처럼 되어 있으면 절대참조라 어디로 복사해도 같은 셀만 가리킵니다. F4로 A1 형태로 바꾸면 해결됩니다.
Q. 채우기 핸들을 아무리 끌어도 자동 채우기가 안 돼요.
파일 > 옵션 > 고급 > 편집 옵션에서 채우기 핸들 및 셀 끌어서 놓기 사용 항목이 꺼져 있을 수 있습니다. 체크를 켜고 다시 시도하세요.
Q. 표(테이블) 기능을 쓰는데 참조가 이상하게 움직여요.
엑셀 표 안에서는 구조적 참조가 기본이라 일반 셀 참조와 동작 방식이 다릅니다. 표 밖에서 수식을 작성하거나 참조 앞에 @ 기호가 붙었는지 확인하세요.
Q. VBA 매크로로 만든 수식도 똑같은 원리인가요?
네, 원리는 같습니다. R1C1 스타일에서는 R[1]C[1]이 상대참조, R1C1이 절대참조에 해당하며, FormulaR1C1 속성으로 채워 넣을 때 이 차이를 구분해서 지정하면 됩니다.
Q. 여러 시트에 같은 수식을 한 번에 붙여넣었는데 참조가 이상하게 바뀝니다.
시트 탭을 여러 개 선택한 상태(그룹 편집)에서 입력하면 각 시트의 같은 위치에 똑같은 수식이 들어가는데, 이때도 참조 안에 $ 표시가 없으면 시트마다 상대참조 기준으로 다시 계산되어 값이 달라 보일 수 있습니다. 시트마다 같은 셀을 고정해서 봐야 한다면 참조에 $ 표시를 붙여 절대참조로 통일하세요.
정리하면, 참조가 안 바뀔 때는 F4로 참조 형태부터 확인하고, 그래도 안 되면 계산 옵션과 채우기 핸들 설정, 시트 간 참조까지 차례로 점검하시면 됩니다.
다음 글에서는 엑셀 자동 채우기로 반복되는 문서 작업을 통째로 줄이는 실전 서식 만들기를 다뤄보겠습니다.
혹시 여러분은 참조 문제 때문에 애먹었던 다른 경험이 있으신가요? 댓글로 알려주시면 다음 글에 반영하겠습니다.
출처: Editlab, https://editlab.luvpp.com
EDITLAB이 매체는 Editlab이 발행합니다분야별 전문 매체 8곳과 무료 데이터 도구를 함께 운영합니다.네트워크 보기 ›
