[Excel] 배열수식 사용시에 데이터 처리속도가 많이 느릴 경우

배열수식을 사용하면, 2~3중의 sumif문의 결합형태를 좀 더 단순하게 처리할 수 있다.

가령, A열에서 '사과' 라는 구분자가 있고(위치는 A2셀) 1행에 6/1~6/30의 날짜별로
표가 만들어져 6월동안 일별의 사과 판매량을 채워야 하는 경우..

이 표를 채우기 위해 준비된 데이터가(시트 'B')
A열 과일이름, B열 6/1~6/30의 날짜, C열 각각의 판매량으로 구성이 되어있다.
(날짜별로 한 개의 데이터가 존재하지 않고, 시간 구분없이 개별 판매건으로 구성이 되어있다)

이 경우 배열수식으로 6/1의 사과 판매량을 구하려면

=sum((B!A:A=A2)*(B!B:B=B1)*(C:C)) 라고 입력 후, Ctrl+Shift+Enter를 누르면
배열수식으로 값을 구할 수 있게 된다.

배열수식화 된 셀을 채우기 핸들로 끌어서 나머지 셀을 채우기만 하면 되는데,
이와 같이 작업하기 전, 참조 셀 중 일부 고정을 해줄 필요가 있다.
따라서 수식은 다음과 같이 기록해야 한다.
(표가 있는 워크시트를 A라고 할때)

=SUM((B!$A:$A=A!$A2)*(B!$B:$B=A!B$1)*(B!$C:$C)) 라고 입력 후,
Ctrl+Shift+Enter 을 누르면,
{=SUM((B!$A:$A=A!$A2)*(B!$B:$B=A!B$1)*(B!$C:$C))} 라고 나오고
채우기 핸들로 필요한 날짜까지 끌어 채워주면 된다.

여기까지는 배열수식에 대한 사용 용례고, 정작 본론은 지금부터...

데이터 량이 적을때는 이와 같이 수식을 써도 무방하지만,
몇백 단위로만 데이터가 축적이 되어도 위와 같이 조건을 3~4개 정도 붙이게 되면
위와 같은 수식으로는 상당히 심한 부하가 걸리게 된다.
위와 같은 형태의 배열 수식에 데이터량만 조합해서 작업을 한 결과 중,
과일명 200여개 / 날짜는 90일 / 준비된 데이터는 약 3만행 으로 작업한 결과,
배열수식 작성 후, 데이터 붙여넣기를 하면 듀얼코어PC에서도 약 1시간 20여분의
작업시간이 소요되는 문제가 발생한다.

이와 같은 문제는, 배열수식에서 지정된 행의 지정 방식이 A열 모두, B열 모두를 의미하는
A:A, B:B와 같은 형태로 지정이 되어 읽지 않아도 되는 데이터까지 모두 읽으면서 발생한다.

많지 않은듯한 데이터임에도 불구하고 시간이 오래 걸리는 경우에는
데이터 량 만큼의 행 지정을 해주면 배열수식 계산시에 많은 시간을 단축할 수 있다.

위에서 A:A, B:B와 같이 범위를 지정해줬지만,
예를 들어 데이터 량이 5천행이라 할 경우 기존의 A:A를 A1:A5000과 같이
유효 데이터범위를 정확히 지정해주면 읽지 않아도 되는 데이터 처리에 대한 부하가 없어서
연산에 소요되는 시간이 많이 단축되는 효과를 볼 수 있다.