열 전체, 행 전체 범위를 적극 사용하라! 단, 주의해서!

안녕하세요. 실전 직장인 엑셀 꿀팁 두번째 글입니다. 앞선 글에서는 VLOOKUP 대신 INDEX-MATCH를 사용하라는 내용으로 설명을 드렸는데요,

 

VLOOKUP 대신 INDEX-MATCH를 써라

(혹시 안 보신 분들을 위한 링크)

위 글에서 제가 INDEX 함수를 사용하면서 $A:$A, $B:$B와 같은 열 전체 범위를 사용했었습니다. 이번 시간에는 $A:$A, $B:$B와 같은 열 전체 범위 혹은 $1:$1, $2:$2와 같은 행 전체 범위를 사용했을 때 얻을 수 있는 장점과 위험성에 대해서 말씀드리겠습니다.

 

장점

장점은 뭐니뭐니해도 유연함입니다. 이렇게 전체 범위를 사용하게 되면 뒤에 데이터가 추가된다 하더라도 굳이 수식을 수정하지 않아도 새로운 데이터를 포함하여 계산을 할 수 있습니다. 작은 엑셀파일일 땐 이 장점을 잘 못 느끼실 수 있지만, 데이터가 커지고 변화가 다양해질수록 처음부터 유연하게 설계한 엑셀과 그렇지 않은 엑셀의 차이가 확연하게 드러납니다.

단점

하지만 날카로운 칼은 또 다루기 힘든 법이죠. 전체 범위를 사용할 때 경험할 수 있는 두 가지 함정을 소개합니다.

SUMPRODUCT

SUMPRODUCT라는 함수는 행렬과 행렬을 곱하는 아주 독특한 함수입니다. 잘 활용하면 아주 까다로운 일을 우아하게 해낼 수 있습니다. 자세한 사용법은 나중에 따로 말씀드리고, 전체 범위에 관련되어서만 설명드리면, SUMPRODUCT를 전체 범위로 잘못 사용하게 되면 엑셀 계산이 매우매우매우 느려질 수 있습니다. 엑셀이 잘 못하는 것이 행렬과 행렬을 곱하는 것인데, 전체범위를 잘못 사용하게 되면 수만개 길이의 행렬과 수만개 길이의 행렬을 곱하게 됩니다. 이럴 경우에 엔터 한 번 누를 때마다 1분 씩 기다리는 상황이 될 수 있습니다. 따라서 SUMPRODUCT의 사용법은 알아두되, 가능하면 COUNTIFS나 SUMIFS와 같은 수식으로 최대한 대체하시고, 어쩔 수 없이 사용한다 하더라도 작은 범위 내에서만 사용하시기 바랍니다.

잘못된 영역 참조

잘못된 영역 참조 예시.xlsx

0.01MB

좀 바보같지만 어떤 식으로 잘못된 영역을 참조할 수 있는지 보여드리기 위해 간단한 예시를 준비했습니다. A1:C5 영역(주황색)은 근무자의 인적사항에 대한 정보가 있고, 그 근무자가 근무했던 총 시간을 요약해서 보여주려고 합니다. A7:C11 영역(파란색)은 근무자가 출근한 날과 근무시간을 나타내는 영역입니다. 그런데 딱 봐도 근무시간의 값이 이상합니다. 모두 8이 나와야 하는데 33, 56, 27, 50이네요. 수식을 살펴보죠.

김갑수의 근무시간의 수식입니다. =SUMIFS($B:$B,$A:$A,$A2) 두번째 열(B열)을 합칠건데, 첫번째 열(A열)이 '김갑수'인 것을 합치라고 하네요. 그런데 문제가 이렇게 열 전체 영역을 사용하다보니 나이까지 근무시간으로 잘못 합산해버렸습니다. 그래서 8이 아니라 8+25=33이 근무시간으로 된 것이죠.

이번 예는 사실 딱 봐도 잘못된 것이 보이고 금새 원인을 찾을 수 있습니다만, 수 백, 수 천 줄의 거대한 엑셀파일을 다루다보면 실수를 해도 자신이 실수한 줄조차 모를 수 있습니다. 애초에 이런 실수를 원천차단하는 것이 최선이지요. 그렇다면 어떻게 엑셀을 설계해야 할까요?

뷰(View)와 데이터(Data)를 분리하라

위 예시에서 근본적인 설계의 문제는 보여주고자 하는 것(뷰)와 기초가 되는 정보(데이터)가 한 시트 안에 있는 것입니다. 만약 제가 엑셀을 설계했다면 애초에 3개의 시트로 엑셀을 설계했을 것입니다.

1. Main: 사용자가 최종적으로 볼 표(이름, 나이, 근무시간)

2. 인적사항: 개개인의 인적사항(이름, 나이)

3. 출근부: 굉장히 길게 늘어날 수 있는 출근부(출근자, 근무시간, 출근일)

이렇게 하고 Main 시트에서 이름을 키(Key)로 삼아 INDEX-MATCH 함수로 인적사항 시트에서 나이를 가져오고, SUMIFS 함수로 출근부 시트에서 근무시간의 합을 가져올 것입니다. 그러면 INDEX-MATCH 함수와 SUMIFS 함수에서 열 전체 범위를 사용해도 에러가 나지 않고, 새로운 근무자와 출근기록이 늘어나도 함수를 바꿀 필요 없이 Main의 이름만 추가하면 됩니다.

 

감사합니다.