함수를 사용하면서 가장 헷갈리는 것이 바로 절대참조 일 것이다. 단순히 셀에서 사용할 때 보다 조건부서식, 유효성 검사 등에서 사용할 때 많이 헷갈린다. 하지만 그 만큼 강력한 기능을 만들 수 있다는 뜻이기도 하다.

이번 글에서는 셀에서의 참조에 대해서만 이야기 하려고 한다. 다른 곳(조건부서식, 유효성검사 등)에서의 참조는 해당 주제 글에서 다룰 예정이다.

절대참조는 수식을 쓸 때 사용되며, 하나의 셀에만 수식을 쓸 때는 전혀 필요 없는 기능이다. 작성한 수식을 끌어서 채우거나, 다른 셀로 복사할 때 필요한 기능이다. 참조하고 있는 셀의 주소가 바뀌면 안되거나, 적절하게 바뀌어야 할 때 효력을 발휘한다. 내 경험으로는 절대참고가 자주 사용되는 함수는 match, vlookup, sumifs, 배열함수, offset 함수 등이 있었던 것 같다. 물론 다른 함수들도 절대참조를 사용할 수 있다. 그리고 함수가 아니고 일반 사칙연산 중에도 사용할 수 있다.

다시 강조하지만 끌어서 채울 때 유용한 기능이다. 반대로 끌어서 채울 땐 꼭 절대참조를 생각해야 한다.

이번 주제는 이론적으로 간단히 소개하고, 예제 몇 개를 소개하면서 마치려고 한다.

절대참조

절대참조는 $라는 키워드를 사용해서 지정할 수 있다. $가 붙은 참조는 끌어서 채울 때 변하지 않는다.

  • =A1 일반적인 참조. 끌어서 채우기를 하게 되면 모든 행, 열 모두 변한다.
  • =$A$1 행,열 절대 참조. 끌어서 채우더라도 항상 A1 셀을 가리킨다.
  • =$A1 열 절대 참조. 끌어서 채우면 행은 바뀌지만 A열은 바뀌지 않는다.
  • =A$1 행 절대 참조. 끌어서 채우면 열은 바뀌지만 1행은 바뀌지 않는다.

SUMIFS

SUMIFS 함수는 더할 영역 중 조건에 맞는 셀의 값만 더하는 함수이다. SUMIFS(더할영역, 조건범위, 조건) 이라고만 알아두자. 사실 조건을 더 적을 수 있다. 아래 그림에서 보면 팀 별 점수가 있고, 각 팀 별로 점수의 합계를 내려고 한다. 먼저 A팀에 대한 점수를 구해보자.

SUMIFS 상대참조1

수식을 말로 풀어보면 B3:B11 값이 E3 과 같으면 C3:C11 을 더하라는 뜻이다. 곧 A 팀의 점수를 더하는 수식이 되는 것이다. 이제 B, C 팀의 점수를 구하기 위해 수식을 아래로 끌어내려보자.

SUMIFS 상대참조2

다시 수식을 해석해보자. B4:B12 값이 E4 와 같으면 C4:C12 를 더하라는 뜻이다. 영역을 표시해주니 참 좋다. 딱 봐도 표의 첫 번째 행의 B 팀 71 점이 더해지지 않았다. 여기서 올바른 것은 B 를 가리키는 E4 뿐 이다.

여기서 어떤 교훈을 얻을 수 있을까? 함수를 끌어서 채웠을 때 영역이 변해야 하는 것이 있고, 변하지 말아야 하는 것이 있다는 것이다. 왼쪽 표는 대상이 되는 영역이므로 참조가 변하면 안 된다. 반면, 팀을 가리키는 것처럼 조건이 되는 영역은 참조가 변해야 한다. 이럴 때 사용하는 것이 절대참조(Absolute Reference)이다.

SUMIFS 상대참조3

E3 셀을 제외하고 모두 절대참조를 붙였다. 이제 끌어서 채우더라도 참조 영역이 변하지 않게 된다.

단축키

절대참조의 단축키는 F4 이다. 이거 하나 알려주려고 한 단락을 쓰는 건가? 라고 생각했다면 오산이다. 아직 조금 더 있다. 함수 작성 중 절대참조를 사용해야 할 경우는 자주 등장한다. 어떻게 하면 가장 적은 타이핑으로 수식을 완성할 수 있을까? 사실 이것이 핵심 내용이다.

함수를 작성하면서 F4를 눌러주는 것이 가장 좋다. =SUMIFS(C3:C11 까지 타이핑 했다고 해보자. 현재 커서는 C11 뒤에 있는것이다. 이 때 F4를 누르면 =SUMIFS($C$3:$C$11 으로 변한다. 이제 ,B3:B11을 쓰면 된다. (쓴다기 보다 영역을 선택 할 것이다.) 이런 식으로 함수 작성 과정에서 F4를 누르는 것이 효과적이다. 반대로 함수를 모두 작성하게 되면 중간으로 커서를 옮겨야 하고 C3:C11를 선택해서 F4를 눌러야 하는데 이 과정이 앞의 과정에 비하면 상당히 귀찮은 일이다.

단축키 설명을 들으면 함수 작성하기도 벅찬데 어떻게 실시간으로 절대참조까지 신경쓰나? 라고 생각할 텐데 맞는 말이다. 익숙하지 않은 상황에선 일단 함수부터 작성하고 나중에 절대참조를 걸면 된다. 이것이 나쁜 것은 아니니까. 익숙한 함수나 자주 쓰던 표현이면 그때그때 절대참조를 걸어주는 것이 효과적이라는 것을 얘기하고 싶었다.

마무리

이번 예제에선 행,열 절대참조에 대해서만 다뤘다. 상황에 따라서 행 또는 열에만 절대참조를 걸어야 하는 경우가 나타난다. 그것은 조건부 서식, 유효성 검사, 다른 함수들을 다룰 때 같이 다룰 예정이다.

정말 간단한 예제로 절대참조의 사용법을 마쳤다. 여러 가지 사용법에 대해서 설명하려고도 했으나 독자 스스로 상황에 맞는 수식을 생각해 보길 바란다. 꼭 함수가 아니더라도 간단한 수식에도 쓰일 수 있다. 한가지만 생각하면 된다. 끌어서 채울 때 뭔가 이상해지면 절대참조를 생각하자.