엑셀 상대 참조, 절대 참조, 혼합 참조 이해하기

수식을 복사하면 엑셀은 복사된 수식이 자기 행이나 열에서 제대로 동작하도록 셀 참조를 알아서 바꿔 줘요. 대부분은 그게 맞는 동작이에요. 그렇지 않을 때는 $ 기호로 참조의 어느 부분을 고정할지 엑셀에 알려 주면 돼요. 달러 기호를 읽고 제자리에 붙일 줄 알면, 수식을 한 번만 쓰고 표 전체에 안심하고 채울 수 있어요.

수식을 복사하면 무슨 일이 생길까?

예시로 간단한 주문표를 써 볼게요. 품목마다 단가와 수량이 있고, 모든 행에 똑같이 적용할 세율 8%가 F2에 따로 들어 있어요.

fx

	A	B	C	D	E	F
1	품목	단가	수량	합계		세율
2	공책	4	10			8%
3	파일철	2	25
4	펜 세트	3	15
5	스테이플러	9	6
6	메모지	3	8

B2처럼 평범한 참조를 상대 참조(relative reference)라고 해요. 엑셀은 실제로 "B2"라는 주소를 기억하는 게 아니라, 수식이 있는 셀을 기준으로 한 상대적인 위치를 기억해요. D2에 있는 =B2*C2는 "같은 행에서 왼쪽으로 두 칸 떨어진 셀 곱하기 왼쪽으로 한 칸 떨어진 셀"이라는 뜻이에요.

이 수식을 D3으로 복사하면 같은 지시가 B3과 C3을 가리켜요. 오른쪽 E2로 복사하면 C2와 D2를 가리키죠. 그래서 수식 하나를 열 아래로 채우면 행마다 자기 값으로 계산된 결과가 나와요.

참조가 바뀌는 건 복사할 때뿐이에요. 잘라내기와 붙여넣기로, 또는 셀 테두리를 끌어서 수식을 옮기면 참조는 원래 그대로 남아요.

$로 셀 고정하기 (절대 참조)

세율은 상대 참조로는 문제가 생겨요. 모든 행이 F2를 써야 하는데, 상대 참조 F2를 아래로 복사하면 F3, F4처럼 바뀌고 그 셀들은 비어 있어요. 엑셀은 빈 셀을 0으로 계산하기 때문에 둘째 행부터는 세금이 슬그머니 빠져요. 오류 메시지도 없이 숫자만 틀리게 나와요.

D3fx=B3*C3*(1+F3)

	A	B	C	D	E	F
1	품목	단가	수량	합계		세율
2	공책	4	10	43.2		8%
3	파일철	2	25	50
4	펜 세트	3	15	45
5	스테이플러	9	6	54
6	메모지	3	8	24

해결책은 절대 참조(absolute reference) $F$2예요. F 앞의 $는 열을, 2 앞의 $는 행을 고정해요. 그래서 어디로 복사해도 계속 F2를 가리켜요.

1. 첫 행의 수식을 쓰세요

   D2를 클릭하고 다음과 같이 입력하세요.

   =B2*C2*(1+$F$2)복사

   B2*C2는 단가 곱하기 수량이고, (1+$F$2)는 여기에 8%를 더해요. Enter를 누르면 D2에 43.2(4 × 10 × 1.08)가 표시돼요.

2. 수식을 아래로 채우세요

   D2를 다시 클릭하고 셀 오른쪽 아래 모서리의 작은 사각형인 채우기 핸들(fill handle)을 더블클릭하세요. 엑셀이 옆 열의 데이터가 끝나는 6행까지 수식을 복사해 줘요. 채우기 핸들을 끌어도 되고, D2:D6을 선택한 다음 Ctrl+D를 눌러도 돼요.

3. 복사된 행을 확인하세요

   D6을 클릭하고 수식 입력줄을 보세요. =B6*C6*(1+$F$2)가 보여요. 단가와 수량 참조는 6행으로 옮겨 갔지만 세율은 F2에 그대로 있어요. D6에는 25.92가 표시돼요.

   D6fx=B6*C6*(1+$F$2)

   	A	B	C	D	E	F
   1	품목	단가	수량	합계		세율
   2	공책	4	10	43.2		8%
   3	파일철	2	25	54
   4	펜 세트	3	15	48.6
   5	스테이플러	9	6	58.32
   6	메모지	3	8	25.92

F2를 10%로 바꾸면 모든 합계가 한꺼번에 바뀌어요. 모두 같은 셀을 읽고 있으니까요. 수식마다 1.08을 직접 쓰지 않고 세율을 따로 한 셀에 두는 이유가 바로 이거예요.

행이나 열 중 하나만 고정하기 (혼합 참조)

혼합 참조(mixed reference)는 절반만 고정하고 나머지 절반은 자유롭게 둬요.

• $A2는 열을 고정해요. 어디로 복사해도 A열에 머물지만 행은 바뀌어요.
• B$1은 행을 고정해요. 어디로 복사해도 1행에 머물지만 열은 바뀌어요.

수식 하나로 가로와 세로 두 방향의 표를 채워야 할 때 필요해요. 아래 표는 단가 네 가지(A열)와 수량 네 가지(1행)를 조합한 주문 금액을 보여 줘요.

fx

	A	B	C	D	E
1	단가 \ 수량	5	10	25	50
2	2
3	3
4	4
5	9

1. 왼쪽 위 셀에 수식 하나를 쓰세요

   B2를 클릭하고 다음과 같이 입력하세요.

   =$A2*B$1복사

   Enter를 누르면 B2에 10(2 × 5)이 표시돼요.

2. 오른쪽과 아래로 채우세요

   B2의 채우기 핸들을 오른쪽으로 E2까지 끄세요. 그런 다음 B2:E2가 선택된 상태에서 채우기 핸들을 5행까지 아래로 끄세요. 이제 B2:E5의 모든 셀에 수식이 복사돼요.

3. 복사된 수식 하나를 읽어 보세요

   E5를 클릭하세요. 수식은 =$A5*E$1이에요. 단가는 여전히 A열에서(이번엔 5행), 수량은 여전히 1행에서(이번엔 E열) 가져와요. E5에는 450(9 × 50)이 표시돼요.

   E5fx=$A5*E$1

   	A	B	C	D	E
   1	단가 \ 수량	5	10	25	50
   2	2	10	20	50	100
   3	3	15	30	75	150
   4	4	20	40	100	200
   5	9	45	90	225	450

달러 기호 없이 같은 표를 만들어 보면 왜 필요한지 알 수 있어요. 평범한 =A2*B1을 C2로 복사하면 =B2*C1이 돼서 B2의 결과에 수량을 곱해요. 20이 나와야 할 자리에 100이 나오죠. 반대로 =$A$2*$B$1처럼 모두 고정하면 표의 모든 셀에 10이 나와요.

수식을 한 행 아래, 한 열 오른쪽으로 복사했을 때 참조 유형별로 어떻게 바뀌는지 정리하면 이래요.

F4로 $ 붙이기

달러 기호를 직접 입력할 필요는 없어요. 수식을 입력하거나 고치는 중에 커서를 참조 안이나 바로 뒤에 두고 F4를 누르세요. 누를 때마다 다음 유형으로 바뀌어요.

F2 → $F$2 → F$2 → $F2 → 다시 F2

가장 빠른 방법은 =B2*C2*(1+F2까지 입력하고 F4를 한 번 눌러 $F$2로 만든 다음 나머지를 입력하는 거예요. 이미 있는 수식을 고치려면 셀을 클릭하고 F2를 눌러 편집 상태로 만든 다음, 참조 안을 클릭하고 F4를 누르세요. 수식을 만드는 중에 B2:B6 같은 범위를 막 선택했다면 F4 한 번으로 양쪽 끝이 모두 고정돼요. $B$2:$B$6처럼요.

$F$2 대신 이름 쓰기

이름은 절대 참조와 같은 역할을 하면서 수식을 읽기 쉽게 만들어 줘요. =B2*C2*(1+TaxRate)는 보기만 해도 뜻이 통하지만, =B2*C2*(1+$F$2)는 F2에 뭐가 있는지 직접 찾아봐야 하죠.

1. 셀을 선택하세요

   세율이 들어 있는 F2를 클릭하세요.

2. 이름 상자에 이름을 입력하세요

   수식 입력줄 왼쪽에 F2가 표시된 이름 상자(Name Box)를 클릭하세요. TaxRate를 입력하고 Enter를 누르세요. 수식› then 정의된 이름› then 이름 정의를 써도 돼요. 이름 정의(Define Name)를 클릭하면 새 이름 대화 상자가 열리고 설명도 함께 적을 수 있어요.

3. 수식에 이름을 쓰세요

   D2에 =B2*C2*(1+TaxRate)를 입력하고 아래로 채우세요. 이름은 항상 같은 셀을 가리키기 때문에 달러 기호가 필요 없어요. 결과도 똑같아서 D2는 43.2, D6은 25.92예요.

이름에는 공백을 넣을 수 없고 셀 주소처럼 보여서도 안 돼요. 그래서 TaxRate나 Tax_Rate는 되지만 Tax Rate나 TR1은 안 돼요. 나중에 이름을 확인하거나 고치거나 지우려면 이름 관리자(Name Manager)를 여세요. 위치는 수식› then 정의된 이름› then 이름 관리자예요.

자주 하는 실수

• 공통 셀을 고정하지 않기. 첫 행은 맞게 나와서 수식이 멀쩡해 보이지만, 그 아래 행은 모두 빈 셀을 가리켜요. 수식을 채운 열을 믿기 전에 마지막 행을 클릭해서 수식 입력줄을 확인하세요.
• 엉뚱한 쪽 고정하기. $F2는 열만 고정하기 때문에 아래로 복사하면 $F3이 돼요. 모든 행과 열이 함께 쓰는 셀 하나라면 양쪽을 모두 고정하세요. $F$2처럼요.
• 너무 많이 고정하기. =$B$2*$C$2를 아래로 복사하면 모든 행에 2행의 결과인 40이 반복돼요. "이 행의 값"을 가리키는 참조는 상대 참조로 두세요.
• 범위도 고정해야 한다는 걸 잊기. 품목별로 전체 합계에서 차지하는 비율을 구하는 =D2/SUM($D$2:$D$6)은 범위를 고정해야 해요. $가 없으면 복사할 때마다 범위가 한 행씩 내려가서 위쪽 품목이 빠져요.
• 편집 중이 아닐 때 F4 누르기. 수식을 편집하는 중이 아니면 F4는 $를 붙이는 대신 서식 지정이나 행 삽입 같은 마지막 작업을 반복해요. 먼저 F2를 눌러 셀을 편집 상태로 만드세요.

연습

연습

공통 세율을 쓰는 가격표 만들기

1. 엑셀에서 A열에 품목을 다섯 개 이상 적고, B열에 단가, C열에 수량을 입력하세요. 1행에는 머리글을 넣으세요.
2. 목록에서 떨어진 셀 하나에 세율이나 할인율을 입력하고, 옆에 이름표를 붙이세요.
3. 첫 품목의 합계 수식을 한 번만 쓰면서 F4로 세율 셀을 고정하고, 열 아래로 채우세요.
4. Ctrl+`를 눌러 모든 행이 같은 세율 셀을 참조하는지 확인하세요. 그런 다음 세율을 바꾸고 모든 합계가 바뀌는지 보세요.
5. 이름 상자에서 세율 셀에 이름을 붙이고, 그 이름으로 수식을 다시 써 보세요.

그다음 아래 두 시트로 연습해 보세요. 첫 번째 시트에서는 세율을 고정하고, 두 번째 시트에서는 혼합 참조로 금액 표를 채워요.

직접 해 보기2개 중 0개 완료

1. 1.D2에 단가와 수량을 곱하고 F2의 세율만큼 세금을 더해 보세요. F2는 $로 고정하세요.힌트 보기
2. 2.D6에는 D2의 수식을 6행까지 복사했을 때 생기는 수식을 그대로 입력해 보세요.힌트 보기

D2fx수식 입력 셀 D2Enter ↵

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	E	F
1	품목	단가	수량	합계		세율
2	공책	4	10			8%
3	파일철	2	25
4	펜 세트	3	15
5	스테이플러	9	6
6	메모지	3	8

직접 해 보기3개 중 0개 완료

1. 1.B2에 A열의 단가와 1행의 수량을 곱해 보세요. A 앞과 1 앞에 $를 붙이세요.힌트 보기
2. 2.D4에는 B2의 수식을 D4로 복사했을 때 바뀌는 수식을 입력해 보세요.힌트 보기
3. 3.E5에 표의 마지막 칸을 채워 보세요. A5의 단가에 E1의 수량을 곱하면 돼요.힌트 보기

B2fx수식 입력 셀 B2Enter ↵

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	E
1	단가 \ 수량	5	10	25	50
2	2
3	3
4	4
5	9

실력 점검

• =B2*C2를 열 아래로 복사할 때와 오른쪽으로 복사할 때 무엇이 바뀌는지 설명할 수 있나요?
• 다른 참조는 그대로 움직이게 두고 $F$2로 공통 셀 하나만 고정하는 수식을 쓸 수 있나요?
• $A2와 B$1이 각각 무엇을 고정하고 무엇을 자유롭게 두는지 설명할 수 있나요?
• 참조를 고정하지 않아서 복사한 수식이 엉뚱한 행을 가리키는 경우를 찾아낼 수 있나요?