[직장인 엑셀 초보 탈출] #10. 엑셀의 기본 – 참참참! 절대 참조, 상대 참조, 혼합 참조 – LG 공식 블로그
본문 바로가기

[직장인 엑셀 초보 탈출] #10. 엑셀의 기본 – 참참참! 절대 참조, 상대 참조, 혼합 참조

작성일2018-07-30

김과장의 엑셀 초보 탈출 강좌 #10. 엑셀의 기본(1). 절대 참조? 상대 참조? 혼합 참조?
안녕하세요! 김과장입니다. :smile:

오늘은 엑셀 배울 때 익혀두면 유용하게 사용할 수 있는 기본기이지만, 쉽게 간과하고 넘어갈 수 있는 삼총사에 대해서 살펴볼 예정입니다. 바로 상대 참조, 절대 참조, 혼합 참조가 그 주인공입니다. 엑셀을 조금이라도 접해보셨다면 절대/상대/혼합 참조라는 단어를 어렴풋이 들어보셨을 겁니다. 더 나아가 셀 번호 앞에 붙어있는 $(달러 표시)가 떠오르신다면 더할 나위 없이 좋습니다.

그러나 정확하게 이 표시가 어떤 기능을 하는지, 어떨 때 참조를 써야 하는지 아직 막막하시죠? 걱정 마세요, 저 김과장이 있습니다. 그럼 하나씩 예시들을 보면서 얘기해 볼까요?엑셀에서 복붙하면 오른쪽 위에 있는 그림처럼 함수가 자동으로 바뀌는 거 정말 편리하죠

D7 셀에는 B7 셀의 수량과 C7 셀의 단가를 곱해 판매금액을 구하는 식이 필요합니다. 즉 D7 = B7*C7 의 수식이 필요한데요. D8 셀의 값을 구할 경우, 수식을 다시 한번 입력해야 할까요? 아닙니다. 간단히 D7 셀을 복사해서 D8 셀에 붙여넣으면 수식도 복사/붙여넣기 되기 때문에 간단하게 계산이 완료됩니다.

왜 그럴까요? 엑셀은 D7 = B7*C7이라는 수식을 기억하고 있습니다. 때문에 D7 셀의 수식을 바로 한 칸 밑인 (+1행) D에 붙여넣게 되면 D8 = B8*C8 처럼 계산을 해야 하는 셀의 값들도 한 칸 밑으로 이동하게 됩니다. 이러한 엑셀의 기능 덕분에 번거롭게 계산을 일일이 할 필요가 없는 것이죠!

이처럼 특정 셀에 있는 수식을 복사하여 이동했을 때, 계산에 사용되는 값. 즉 참조 값들도 따라서 이동하는 것‘상대참조’ 라고 합니다.

그러면 양식을 조금 바꿔볼까요?

C7셀의 함수 복붙하기 --> 결과가 이상해진 수식으로 붙여진 모습

위의 왼쪽 예시는 C3 셀에 있는 단가와 B7 셀에 있는 수량을 곱해 판매금액을 구해야 합니다. C7 = C3*B7 처럼 수식을 써 볼 수 있는데요. C8 셀의 값을 구할 때 위에서 살펴본 예시처럼 C7을 복사해서 고대로 붙여넣었더니… 오른쪽과 같은 대참사가 발생했습니다. (야근을 부르는 엑셀 오류)

이게 뭐냐용~ 이상해진 수식 언제 다 고치냐옹~

함수를 위 - 아래로 복붙했을 때 수량은 B7셀-B8셀-...-B16셀로 바뀌더라도 단가는 항상 C3셀로 고정되게 할 수 있는 방법, 없을까요?

왜 그럴까요? 마찬가지로 C7 셀의 수식을 C8 셀로 붙여넣을 경우, 계산해야 하는 셀들도 똑같이 아래로 한 칸씩 이동하는데요, 이 예시에서 단가를 나타내는 C3 셀의 경우 위→아래로 이동하면 안 됩니다. 즉, 판매금액을 구할 때 수량을 나타내는 B행의 값들은 따라 움직여도 되지만 단가를 나타내는 C3 셀의 경우 고정(행 고정)되어 있어야 하는 겁니다.

그런 방법이 있엉?

이번엔 양식을 아래 그림처럼 좀 더 바꿔서 D6 셀의 수식을 좌 → 우로 복사/붙여넣기(이하 복붙)을 해보겠습니다.

D6셀의 함수를 복붙하기 - 숫자가 모두 0으로 붙여넣어진 모습

대참사 2가 발생했습니다. 표시해둔 빨간색 박스 부분처럼 함수 결과가 모두 숫자 0으로 바뀌어 버리죠? 마찬가지로 수량을 나타내는 5열의 값들은 따라 움직여야 하지만, 단가를 나타내는 C3 셀은 좌→우로 움직이지 않게 고정(열 고정)이 되어야 하는 경우네요.

함수를 좌-우로 복붙할 때 수량은 D5셀-E5셀-...-M5셀로 바뀌고 단가는 항상 C3셀로 고정되게 할 수 있는 방법, 없을까요?

얘기 그만 끌고 이제 좀 알려주시게~

이제 우리가 (사실은 여러분이) 해결해야 할 문제가 뭔지 감이 잡히시나요? 앞에서 말씀드렸던 2가지 예시는 함수를 위 → 아래로 붙여넣느냐, 또는 좌 → 우로 붙여넣느냐의 차이만 있을 뿐입니다. 결국 우리가 하고 싶은 건 ‘함수를 아무리 복사해 붙여넣더라도 ‘함수식 안에 있는 특정 셀의 열 또는 행이 바뀌지 않게 고정’하는 방법이겠죠?

그럼 어떻게 하면 함수식 안에 있는 특정 셀의 주소를 바꾸지 않고 고정할 수 있을까요? 간단합니다. 고정하려는 열 또는 행 앞에 $(달러 표시)를 붙이면 됩니다.

셀 번호 고정 사례, 표현방법, 표현 예시를 나타낸 도표
1) 번의 경우 열과 행이 모두 고정되기 때문에 C3 셀이 들어있는 수식을 ‘상하좌우’로 이동하더라도 C3 셀의 열과 행 모두 바뀌지 않습니다. 즉 $C$3의 경우 절대적으로 값이 변하지 않는데요, 이처럼 참조 값의 열과 행을 모두 고정한 형태‘절대참조’ 라고 합니다.

2) 번과 3) 번은 행,열 중 하나만 고정을 하는 경우입니다. 앞에서 배웠던 ‘상대참조’와 ‘절대참조’를 반반씩 섞어놓은 것 같죠? 이처럼 참조 값의 행 또는 열 하나만 고정한 형태를 ‘혼합참조’라고 합니다.

그런데, 참조식을 만들 때 셀 번호 앞에 일일이 $(달러 표시)를 붙여주려면 매번 Shift 키 + 숫자 4키를 눌러줘야 하는데요, 귀차니즘이 발동합니다. 이런 우리의 수고를 덜어주기 위해 똑똑한 엑셀이 준비한 기능이 있는데요. 바로 F4 단축키입니다.

수식을 입력한 상태에서 특정 셀에 커서를 두고 F4 키를 누르면 셀에  $(달러 표시)가 생김. '수식 입력줄에 '=C3'라고 입력하세요' 이후 나타나는 F4키의 사용법에 대해 쓴 도표

위의 순환 고리 그림처럼 수식을 입력한 상태에서 특정 셀에 커서를 두고 F4 키를 누르면 셀에  $(달러 표시)가 생깁니다. 이제 오늘 배운 것들을 한번에 기억하기 위한 마법의 주문을 외워보겠습니다. 이름하여 ‘참참참’ 주문! (상대 조, 절대 조, 혼합 조)

참참참 주문


F4 키를 1번 누르면 행과 열 번호에 모두 $ 가 붙는 절대 고정(절대 참조)이 된다.
F4 키를 2번 누르면 행 번호에만 $ 가 붙는 행 고정이 된다.
F4 키를 3번 누르면 열 번호에만 $ 가 붙는 열 고정이 된다.
F4 키를 4번 누르면 행과 열 번호에 모두 $가 사라져 고정이 해제(상대 참조) 된다.

F4를 누를 때 $가 붙는 순서(절대행렬)에 대한 도표

자! 그럼 ‘참참참 주문’을 이용해서 처음 보았던 예시들을 다시 살펴볼까요?

C3셀 행 고정으로 바꾸고 수식 복붙하기 --> 숫자가 차례대로 계산되어 붙여넣어진 모습

위의 예시처럼 함수식에서 C3의 행을 고정해야 했던 경우에는 C3 셀을 행 고정(C$3) 하고 수식을 위→아래로 복붙하면, 수량의 경우에는 수식을 따라서 위→아래로 이동하지만, 단가를 나타내는 C3의 경우 행이 고정되어 있어 따라 내려가지 않습니다. 즉 C3 셀은 C$3으로 행이 고정되어 있어 항상 1,000원 이라는 값을 유지할 수 있지요.

C3셀 열 고정으로 바꾸고 수식 복붙하기 --> 수식이 차례대로 계산되어 붙여넣어진 모습

마찬가지로 함수식에서 C3 셀의 열을 고정해야 했던 경우에는 C3 셀을 열 고정($C3) 하고 수식을 좌→우로 복붙하면, 수량의 경우 수식을 따라서 좌→우로 이동하지만 단가를 나타내는 C3의 경우 열이 고정되어 있어 수식을 따라 우측으로 이동하지 않습니다. 즉 C3셀은 $C3으로 열이 고정되어 있어 항상 1,000원의 값을 가지고 올 수 있지요.

이제 참조 3총사 상대참조, 절대참조, 혼합참조가 이해되시나요? 마지막으로 말씀드리자면, 오늘 배웠던 참참참 주문은 달달 외운다고 머리 속에 들어가지 않습니다. 그렇지만 백문이 불여일엑셀! 단 5분이라도 엑셀 창을 켜고 참참참 주문을 복습한다면 외울 필요 없이 참조 3총사를 마스터 하실 수 있으실 거에요!

김과장의 직장인 엑셀 초보탈출, 다음 편에도 더욱 재미있고 유익한 꿀팁을 들고 찾아오겠습니다.  :smile:

김택상 프로필

LG디스플레이에서 경영관리 (Business Management) 업무를 담당하고 있습니다. 엑셀과 친해지고 싶은 분들께 그 동안 갖은 시행착오를 겪으며 익힌 엑셀 실전 기술들을 나눠드리고 있는 엑셀 공작소의 김과장입니다.