티스토리 뷰
지난 시간에는 엑셀의 '자동채우기' 기능에 대해 알아보았습니다. 자동채우기는 많은 데이터를 일정한 규칙으로 '가공'할 때 무척 유용하게 활용할 수 있는 기능입니다. 오늘은 셀의 절대참조와 상대참조를 활용하여 '자동채우기'를 자유자재로 사용하는 방법에 대해 알아보겠습니다.
참조란 무엇인가
엑셀을 하다보면 '참조'란 말을 참 많이 씁니다. 그러나 정작 '참조'가 무엇인지 설명해주는 곳은 많지 않습니다.
참조는 쉽게 말하자면 무언가를 '가리키는 것'을 두 글자로 표현한 말입니다. 예를 들어, 'A1에 있는 값에 2를 곱한 숫자'를 표현하는 수식은 아래와 같습니다.
- =A1*2
이때, A1의 값을 가져다 썼기 때문에(가리켰기 때문에), 이 수식은 A1 셀을 참조한 것이 됩니다.
자동채우기는 참조를 자동으로 고쳐주는 행위
지난 시간에 배운 자동채우기는 참조를 자동으로 고쳐주는 편리한 기능이었습니다.
예를 들어, 위에 적혀 있는 수식은 [B2], [C2], [D2] 3개의 셀을 참조하고 있습니다. 이때 자동채우기를 해보면 참조가 되고 있는 3개의 셀 주소가 순차적으로 바뀝니다.
이때, 셀 주소는 자동채우기를 하는 최초의 셀(E2)과 몇 칸 떨어진 셀인지 '상대적인 위치'를 비교하여 자동으로 변경되기 때문에 '상대참조'를 사용하였다고 말합니다.
셀의 상대참조를 사용하면 안 되는 경우
그런데 때때로 자동채우기를 할 때, 상대참조를 사용하고 싶지 않을 때가 있습니다.
예를 들어 구구단의 2단을 만들기 위해 아래와 같이 숫자들에 2를 곱하는 예를 들어보겠습니다.
첫번째 셀인 B2에 들어갈 수식은 =B1 * A2 가 될 것입니다. 그래야 2 x 2 를 계산할 테니까요. 그리고 여기에 자동채우기 기능을 활용하여 나머지 부분에도 수식을 모두 채울 수 있으면 참 좋을 것 같습니다.
그러나 실제로 자동채우기를 해보면 엉뚱한 결과를 얻게 됩니다.
예를 들어, B9 셀에 입력된 수식을 보시면 =B8*A9 가 보입니다. 2 x 9 가 계산되어야 하므로 B1 * A9 가 계산되어야 하는데, 상대참조가 적용되어 첫번째 셀에 있던 B1이 자동으로 B8로 변경된 것입니다.
수식 표시모드를 끄고 보면 얼마나 엉뚱한 값이 계산되었는지 알 수 있습니다.
계속해서 위에 있는 값에 숫자를 곱한 것이 되므로, B9셀에 계산된 값은 2 x 9가 아니라 2 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 가 되어버린 것입니다.
이런 사태를 방지하기 위해서 셀의 절대참조 기능을 사용해야합니다.
셀의 절대참조 사용 방법
셀의 절대참조는 셀주소 행/열 각각에 $ 표시를 붙여줘 사용할 수 있습니다.
구구단의 첫번째에 곱해져야할 숫자 2가 저장되어 있는 [B1] 셀에 절대참조 기호($)를 붙여 [$B$1] 이라 표시하였습니다. 이 상태로 자동채우기를 해보겠습니다.
아까와 달라진 모습이 보이시나요? 아까 상대참조를 사용했을 때는 이런 모습이었습니다. 비교해 보시죠.
상대참조에서는 B1이 자동채우기를 하며 '상대적'으로 참조하는 셀주소가 바뀌어 B1 ~ B8 로 입력된 것을 보실 수 있습니다. 반면, 절대참조에서는 $ 표시를 붙여놓은 [B1] 셀의 주소가 처음부터 끝까지 바뀌지 않고 유지되는 모습을 볼 수 있습니다.
절대참조의 수식 표시모드를 꺼보겠습니다. 구구단의 2단이 아주 자연스럽게 입력된 것을 보실 수 있습니다.
행/열 각각에 절대참조 사용하기
이때, 행/열 각각에 절대참조/상대참조를 다르게 적용할 수가 있습니다. 사용하는 방법은 간단합니다. 절대참조 기호인 $를 적용하고 싶은 부분에만 적어주면 됩니다.
예를 들면 이런 식입니다.
| 예시 | 행(1) | 열(A) |
| A1 | 상대참조 | 상대참조 |
| $A1 | 상대참조 | 절대참조 |
| A$1 | 절대참조 | 상대참조 |
| $A$1 | 절대참조 | 절대참조 |
이때, 행이 절대참조이면 행방향(위아래)로 자동채우기를 해도 셀 주소의 숫자부분이 바뀌지 않습니다. 반대로 열이 절대참조이면 열방향(좌우)로 자동채우기를 해도 셀 주소의 알파벳부분이 바뀌지 않습니다.
그래서 간단하게 생각하면, 절대참조 기호($)가 붙은 부분은 자동채우기를 해도 바뀌지 않는다고 인식하셔도 좋습니다.
만약, 구구단의 2단이 아니라 9단 전체를 표현해야 한다면 어떻게 될까요?
먼저, 상대참조만 사용한다 가정하고 행 방향을 생각해보겠습니다.
상대참조로 둔 채로 자동채우기를 하면 B1은 B8 까지, A2는 A9까지 자동으로 상대적으로 셀 주소가 바뀐 것을 보실 수 있습니다.
- 이때, A2 ~ A9 가 된 부분은 문제가 없습니다. 구구단의 2단을 표현하기 위해서 숫자가 2, 3, 4 ... 9까지 커진 부분을 가리키니까요.
- 그러나, B1셀이 B8 까지 바뀐 것은 문제입니다. 따라서 B1셀에 절대참조를 적용해야 합니다.
그럼 B1 부분에 절대참조를 적용하면 어떻게 될까요?
- 세로로 보면 B1 셀이 잘 고정되어 숫자 2에, 2,3,4,5,6,7,9 숫자를 차례대로 잘 곱해주고 있습니다.
- 그러나 가로로 보면, B1셀이 C1, D1, E1 등으로 차례대로 커져 I1 까지 가야함에도 계속 B1으로 고정되어 있습니다.
- 반대로 A2 셀은 우측으로 가더라도 계속 A2 셀로 남아있어야하는데 B2 ~ H2 까지 점점 커지는 것을 볼 수 있습니다.
따라서, 이와 같은 경우에는 고정시킬 부분에만 $ 표시를 해주어야 합니다.
행과 열에 필요한 부분에만 절대참조($) 기호를 붙여주었습니다.
- 2x2 부분에 B1 이란 주소는 행방향(아래로 끌때) 행(숫자)가 바뀌지 않아야합니다. 따라서 행을 표시하는 숫자 부분에 $를 붙여주었습니다.
- 2x2 부분에 A2 란 주소는 열방향(우측으로 끌때) 열(알파벳)이 바뀌지 말아야합니다. 따라서 열을 표시하는 알파벳 부분에 $를 붙여주었습니다.
이렇게 가득 채우고 표시모드를 꺼보겠습니다.
구구단이 완성되었습니다.
상대참조, 절대참조에 익숙해져야 하는 이유
엑셀은 데이터를 가공해서 표현하는 프로그램이라 말씀드렸습니다. 이때 데이터가 아무리 많더라도 함수 하나만 입력하고 자동채우기를 하면 저절로 뚝딱 '모든 데이터'에 대한 가공 결과를 볼 수 있기에 우리가 엑셀을 사용하는 것입니다. 그런데 이 '자동채우기' 기능을 제대로 활용하기 위해서 상대참조와 절대참조를 알차게 사용해야하는 경우가 몹시 많습니다.
처음에 익숙치 않으시겠지만 본격적으로 함수를 익히기 전에 구구단 예제등을 통해서 상대참조, 절대참조의 개념에 대해 확실히 감을 잡으신다면 나중에 큰 도움이 되실 것입니다.
예제파일