엑셀 참조기능을 이용해 업무 효율 높이기(기본편)

엑셀은 기본적으로 데이터를 다루는 것에 특화되어 있기 때문에 참조기능은 아주 많은 곳에서 쓰입니다.

참조란, 엑셀에서 데이터를 저장하고 있는 위치를 나타내는 것입니다. 엑셀에서는 참조를 이용해 데이터를 연산하거나 수식을 작성할 때 사용합니다.

기존에 함수를 설명할 때 참조에 대해서는 별도로 설명을 하지 않았지만 참조에 대해 잘 모르시는 분들이나 오랜만에 사용해서 헷갈리는 분들을 위해 참조에 대해 정리해 봤습니다.

참조는 기본적으로 상대참조, 절대참조, 혼합참조가 있습니다.

조금 더 심화하면 3차원 참조, 외부 참조, 구조적 참조가 있습니다.

이번 포스팅에서는 기본적인 참조 3가지에 대해 알아보고 심화된 참조는 다음 포스팅에서 알아보겠습니다.

 

 

 

---

1. 엑셀 상대참조(Relative Reference)

상대참조는 가장 기본적인 참조 방식입니다. 

상대참조를 사용하면 셀에 입력된 수식에서 상대적인 위치에 따라 참조하는 셀의 주소가 변경됩니다. 

예를 들어, A1셀에서 "=(B1+C1)"이라는 수식을 입력했다면, 이 수식을 C2셀로 복사하면 "=(B2+C2)"로 바뀝니다. 

이렇게 셀에 입력된 수식에서 상대적인 위치에 따라 참조하는 셀의 주소가 변경되는 것을 상대참조라고 합니다.

 

엑셀 상대참조(Relative Reference)

 

위와 같은 엑셀에서 제임스의 1월 지급액을 알고 싶으면 제임스의 시급(B2)와 1월 근무시간(B7)을 곱하면 될 것입니다.

이렇게 하나의 셀에 직접 입력을 한다면 상대, 절대 참조에 의미는 없지만 채우기 핸들을 이용하여 수식을 2월, 3월에도 적용하고자 한다면 이야기가 달라집니다.

 

엑셀 상대참조(Relative Reference) 채우기

 

채우기 핸들을 드래그 하면 2,3월의 지급액도 자동으로 계산이 될 줄 알았는데 0이 나옵니다.

채우기 핸들로 빈 칸을 채우면 셀의 참조값도 자동으로 증가시켜 주기 때문에 아래와 같이 참조하게 되고 그래서 0이 됩니다.

 

엑셀 상대참조(Relative Reference) 참조 오류

 

참조값이 증가되어 0(C2) * 60(C7)이 되어 2월 지급액이 0이 되었습니다.

이런 일이 발생하지 않도록 채우기 핸들을 이용해도 제임스는 시급이 11,000원으로 계산될 수 있도록 하는 것이 절대 참조입니다.

 

 

 

---

2. 엑셀 절대참조(Absolute Reference)

절대참조는 상대참조와는 달리, 셀에 입력된 수식에서 참조하는 셀의 주소를 고정시키는 방식입니다. 

예를 들어, $A$1은 A1셀을 절대참조하는 것을 의미합니다. 

이렇게 절대참조를 사용하면 수식이 복사될 때 참조하는 셀의 주소가 변경되지 않습니다. 

즉, 수식을 다른 셀로 복사해도 항상 $A$1을 참조하게 됩니다.

위 설명에서 보듯이 절대 참조에서는 "$"가 중요한 키워드입니다.

"$"가 뒤에 오는 셀의 좌표는 절대참조합니다.

 

엑셀 절대참조(Absolute Reference)

 

제임스의 시급인 B2를 절대 참조하기 위해 B2 -> $B$2로 변경해 주었습니다.

이제 채우기 핸들을 드래그하여 2,3월 지급액도 수식을 자동으로 입력해 보겠습니다.

 

엑셀 절대참조(Absolute Reference) 채우기

 

이제 채우기 핸들을 드래그 하여 자동으로 입력한 수식도 제대로 값이 나오고 있습니다.

 

엑셀 절대참조(Absolute Reference) 자동 채우기에서 참조하는 셀 위치

 

위 그림을 보시면 각 지급액이 계산될 때 참조한 셀을 한눈에 볼 수 있습니다.

시급은 고정이 되었지만 근무시간은 월마다 바뀌고 있습니다.

이제 톰과 제니도 자동 채우기로 지급액을 계산해 보겠습니다.

 

엑셀 절대참조(Absolute Reference) 채우기(세로)

 

예상과 다르게 지급액이 0이 나옵니다.

두 가지 문제가 있습니다.

1. 제임스의 시급으로 절대 참조가 되어 톰, 제니의 시급이 반영되지 않음
2. 상대 참조로 인해 좌표가 증가하여 값이 없는 셀을 참조

 

 

채우기 핸들을 아래로 드래그하니 상대참조하는 좌표도 아래로 증가한 것입니다.

근무 시간은 위아래 이동은 고정하고 좌우만 움직이게 하면 해결될 것 같습니다.

반대로 시급은 아래로 채울 때만 변하고 좌우로 채울 때는 변하지 않아야 합니다.

이렇게 하는 것이 바로 혼합참조입니다.

 

 

 

---

3. 엑셀 혼합참조(Mixed Reference)

혼합참조는 상대참조와 절대참조를 혼합한 것입니다. 

예를 들어, $A1은 A열을 절대참조하고 행은 상대참조하는 것을 의미합니다. 

이렇게 혼합참조를 사용하면 열이나 행 중 하나만 고정하고 나머지는 상대적인 위치에 따라 참조할 수 있습니다.

우리가 원하는 방식입니다.

바로 적용해 보겠습니다.

 

엑셀 혼합참조(Mixed Reference)

 

그리고 자동 채우기를 해보겠습니다.

 

엑셀 혼합참조(Mixed Reference) 채우기

 

이제 우리가 원하는 대로 지급액이 사람별로 계산된 것을 볼 수 있습니다.

 

엑셀 혼합참조(Mixed Reference) 완성