우리는 이전 강좌 41에서 여러 인수의 곱을 취한 값을 더하는
SUMPRODUCT 에 관해서 배웠다.
이 SUMPRODUCT 는 각 행의 계산 결과값을 저장할 열을 따로 만든 후
이 열을 더하는 번거러움을 줄이고 쓸데없이 열을 늘어나지 않도록
하는데 사용하는데 이 SUMPRODUCT 함수도 배열 함수로 사용할 수 있다.
위의 시트는 개별 판매 실적의 통계를 내는 것으로 이런 형태는 실무에서 많이 필요로 할 것이다.
문제는 하단의 각 지점별 상품별 판매금액의 집계를 내어야 하는데 이때 개인별 판매금액은 별도로
계산을 하지 않고 조건에 맞는 열의 판매금액만 골라 계산해서 각 셀에 넣어야 한다는 것이다.
이때 사용되는 것이 배열함수이고 SUMPRODUCT 함수를 사용하면 간단하게 처리할 수 있다.
C23 셀 즉 제1점의 MP3 판매금액의 수식을 보면
{=SUMPRODUCT((대리점=$B23)*(제품명=C$22),판매수량,단가)}이다.
{=SUMPRODUCT( )} 내부의 수식을 보자.
(대리점=$B23) * (제품명=C$22) 즉 두개의 조건이 둘다 참이면 이 결과는 1이고 하나라도 거짓이면 0이 된다.
다음 인수는 이름정의된 판매수량과 단가의 영역으로 각 행의 위 조건결과와 판매수량 단가를 곱한 값을
배열에 집어 놓고 최종 적으로 SUMPRODUCT 함수를 통해 배열의 모든 값을 더한 결과를 반환하게 된다.
이 수식을 제4점 (26행)까지 적용시키고 갤럭시S (E열) 까지 적용 시키면 통계가 완성된다.
여기서 첫 조건식 대리점=$B23 에서 열을 고정시켰다.
즉 다른열에 해상 식을 적용시킬 때 열도 따라 증가되는 것을 방지하기 위함이다.
즉 D23에 이 식을 긁어 적용 시키면 조건식이 C23 으로 같이 열 값이 자동 늘어나게 되므로
항상 B23 을 유지해야 한다.
또 두번째 조건도제품명=C$22 이렇게 해서 다른 행에 식을 적용 시킬 때 행이 증가되는 것을 막았다.
즉 이 식을 C24에 적용 시킬 때 제품명 = C23 이 되면 안되기 때문에 항상 C22 가 되도록 고정 시킨 것이다.
여기서 SUMPRODUCT 의 수식형은 SUMPRODUCT(ARRAY1, ARRAY2, ARRAY3....) 인데
SUMPRODUCT((대리점=$B23),(제품명=C$22),판매수량,단가)) 하면 안된다.
각각의 조건식은 배열이 아니고 조건식의 결과 값이 배열이므로 두 조건식은 반드시 * 로 연결해야 한다.
덧글