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

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

작성일2018-07-30

0731_김택상 엑셀 10강_01
안녕하세요! 김과장입니다. :smile:

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

그러나 정확하게 이 표시가 어떤 기능을 하는지, 어떨 때 참조를 써야 하는지 아직 막막하시죠? 걱정 마세요, 저 김과장이 있습니다. 그럼 하나씩 예시들을 보면서 얘기해 볼까요?0731_김택상 엑셀 10강_02작업

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

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

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

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

0731_김택상 엑셀 10강_03

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

0731_김택상 엑셀 10강_04

0731_김택상 엑셀 10강_05작업

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

0731_김택상 엑셀 10강_06

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

0731_김택상 엑셀 10강_07

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

0731_김택상 엑셀 10강_08작업

0731_김택상 엑셀 10강_09

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

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

0731_김택상 엑셀 10강_10
1) 번의 경우 열과 행이 모두 고정되기 때문에 C3 셀이 들어있는 수식을 ‘상하좌우’로 이동하더라도 C3 셀의 열과 행 모두 바뀌지 않습니다. 즉 $C$3의 경우 절대적으로 값이 변하지 않는데요, 이처럼 참조 값의 열과 행을 모두 고정한 형태‘절대참조’ 라고 합니다.

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

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

0731_김택상 엑셀 10강_11작업

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

참참참 주문


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

0731_김택상 엑셀 10강_13작업

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

0731_김택상 엑셀 10강_14

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

0731_김택상 엑셀 10강_15

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

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

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

김택상 프로필

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