배열수식을 간단하게 정리 해보자.
단계1. 사용이유
아래 <표1>과 같은 데이터 테이블이 있다고 하자. <표1>의 데이터를 <표2>로 요약해서, 각 과일의 판매량 합계를 기재할 예정이다. 과일별로 소팅을 해서 SUM함수를 걸거나, 피벗테이블을 만드는 방법 등이 있다. 배열수식은 어디까지나, 원본 데이터를 건드리지 않고, 요약표를 별도로 만들 때 사용하기 용이한 방법이다.
*피벗테이블 대비 유연한 요약표 작성이 가능하고, 새로고침 과정이 필요없는 것도 장점이라 하겠다.
<표2>에 아래와 같이 IF수식을 사용하였다. F3의 값이 C3와 같다면, 판매량을 반환하는 방식이다. 단일 셀값을 비교한다는 뜻이 되겠다.
그럼, <표2>의 사과를 <표1>과일열 전체와 비교하고, 과일명이 같다면, 각 판매량을 얻어내는 방법은 아래처럼 영역을 찍어주면 될까? 아쉽게도 결과는 첫번째 조건을 만족하는 수, 9,358로 나오게 된다. 다른 방법을 찾아야 한다.
단계2. 작동방식
각 조건을 만족했을 때,(같은 사과일때)의 판매량을 배열(리스트형태)로 임시 저장했다가, 배열의 각 값들을 합하거나, 개수를 세거나 하는 것이 배열 수식이다.
** 이때, 조건은 하나일수도, 여러가지일 수도 있는데, 이걸 보고, SUMIF나 SUMIFs 함수를 떠올렸다면, 아주 제대로 이해하고 있는 것이다. SUMIF/SUMIFs는 합산 배열수식을 사용하기 쉽도록 만들어둔 함수이다.
아래와 같이 수식을 작성하고, CTRL+SHIFT+ENTER를 눌러주면 수식 앞뒤로 { } 가 붙는데, 배열수식이라는 의미이다.
{=SUM(IF(F3=C3:C13,D3:D13))}
단, 우리는 요약표에서 수식을 드래그해서 사용할 예정이므로 참조가 되는 <표1>의 값들은 절대참조 처리 해줘야한다.
{=SUM(IF(F3=$C$3:$C$13,$D$3:$D$13))}
* '이름정의'를 사용하는 것도 좋은 방법이다.
또 드래그를 할때, 동일한 조건값이 두개가 있다면, 같은 값이 붙게 되니, 주의해줘야 한다. 이는 피벗테이블 대비 약점이라 하겠다.
수식 내부에서 벌어지고 있는 일을 확인해보자. <표2>의 사과 판매량에 적힌 배열수식에서, IF함수 부분을 선택하여 F9키를 눌러보면, 생성된 배열인 { } 안에 어떤 일이 벌어졌는지 알 수 있다. <표1>의 첫번째 행값은 사과라는 조건에 부합하므로 판매량이 들어가 있다(9358). 두번째 값은 '배'이므로 IF 함수에서는 FALSE 값이 들어가게 된다.
{9358;FALSE;5820;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;8559;FALSE;}
* 물론 FALSE 대신 다른 값을 넣어줄 수 있다.
이런 방식으로 배열이 작성되며, 배열에 들어가는 개수는 조건으로의 참조영역, 즉 <표1>의 과일 셀의 개수만큼 들어가게 된다. 여기서 주의할 점은 비교대상인 <표1>의 과일셀과 판매량셀은 개수가 동일해야한다. 그래야 1:1 비교가 가능하기 때문이다.
여기까지가 기본이다. 위의 언급했던 SUMIF 처럼, 엑셀의 함수 중에는 기본으로 배열수식을 활용하는 함수들이 있으며, 정확하지는 않지만 쉽게 생각해서, 변수를 영역으로 지정하는 경우, 배열수식이라고 봐도 되겠다.
단계3. 조건의 확장
아래처럼 <표1>에 수확일자가 추가되었고, <표2>는 수확월에 대한 과일별 판매량을 나타내는 표로 변경하였다. 방법은 수확월을 수확일자로부터 찾는 조건1과 과일이름을 찾는 조건2를 합쳐주면 된다.
방법은 간단한데, IF문에서 각 조건을 괄호로 친다음 * 로 묶어주면 된다.
{=SUM(IF((F3=MONTH($B$3:$B$13))*(G3=$C$3:$C$13),$D$3:$D$13))}
* MONTH는 날짜값에서 월을 불러오는 함수이다.
대부분 강의에서 보면 이런식으로 나오는 것 봤을지 모르겠다..
{=SUM(IF((조건1)*(조건2), 합산할 값)}
조건간에는 and 조건은 *, or 조건은 +로 표시를 해야한다!!!! 배열 수식안에 and나 or 함수를 쓸 생각은 하지 말자.
본 예제에서는 이 정도가 되겠다.
{=SUM(IF((수확월)*(과일명), 판매량)}
여기까지 내용은 끝이다. <표2>를 구성하는 방법에 따라, 다른 함수들을 추가해줄 수 있는데, 그건 어디까지나 옵션이다. 르겠다..
아래 그림은 표1의 열을 이름정의해서, 수식을 좀 직관적으로 작성한 것이다.
{=SUM(IF((F3=MONTH(표1_수확일자))*(G3=표1_과일명),표1_판매량))}
이상 끝~
