[Excel] 11. 엑셀로 능력있는 직장인이 되는 법. 초급편 (SUMPRODUCT, SUM 배열함수 활용)

안녕하세요! @gbgg 입니다. 능력있는 직장인이 되는법! 열 한번째 시간입니다!

지난 초급편에서 가장 많이 쓰이고 중요한 VLOOKUP, INDEX, MATCH 함수 등에 대해 알아보았는데요.

오늘은 약간 고급용 함수라고 할 수 있는 다중조건 함수들을 살펴보고자 합니다.

다중조건함수는 특정 데이터를 뽑아내거나 통계를 내기 전 데이터를 추릴 때 많이 사용됩니다.

업무용으로 많이 사용되므로 고급함수지만 알아두시면 좋습니다!

활용 예제와 함께 잘 설명 드릴게요!

다중조건을 통해 조건에 맞는 값들만 더하고 싶을 때 if문을 여러 개 써야 합니다.

물론 if문을 쓰면 값만 더하는 것이 아니고 다양한 작업을 할 수 있지만

일반 엑셀 작업에서 조건에 맞는 값들만 더할 때는 굳이 조건문을 여러 개 쓸 필요가 없습니다.

SUMPRODUCT 함수는 여러 조건을 검사한 뒤 조건에 맞는 숫자 합을 찾아줍니다.

이러한 품목이 있습니다. 여기서 오늘 판매한 노트북의 총 가격이 얼마인지 알고싶습니다.

찾아야 할 조건은 '노트북', '판매' 입니다. 이 조건이 맞다면 단가를 더해야겠지요?

이럴 땐 SUMIFS 함수를 쓰면 되지않나요? 왜 굳이 SUMPRODUCT 함수를 사용하나요?

여기서 하나 짚고 넢어가겠습니다.

SUMPRODUCT 함수가 SUMIFS 함수보다 좋은 점 한 가지를 꼽자면

바로 SUMPRODUCT는 논리연산을 할 수 있다는 점 입니다.

논리연산은 논리합(OR, +), 논리곱(AND, *), 부정(NOT) 등이 있는데 조건식을 적용할 때 유용합니다.

조건1과 조건2를 모두 만족하는 경우는 논리 곱을 사용하여 표현할 수 있습니다.

논리곱 -> (조건1)*(조건2)

조건1과 조건2 중 하나만 만족하는 경우는 논리 합을 사용하여 표현할 수 있습니다.

논리합 -> (조건1)+(조건2)

=SUMPRODUCT((조건1)+(조건2),(더할 셀 범위))

조건1이나 조건2중 만족하는 것이 있을 경우 더할 셀 범위에 있는 값을 더하게 됩니다.

핸드폰 구매 총 단가를 찾아보겠습니다. 위 그림과 같이 셋팅해주세요.

조건1에 해당되는 '핸드폰'과 조건2에 해당되는 '구매'가 '값'과 일치하면 '더할 셀 범위' 에 있는 값을 더해줍니다.

=SUMPRODUCT((A2:A8=F2)*(B2:B8 = G2),C2:C8)

위와 같이 함수를 사용하면 되겠죠?

그렇다면 이번에는 '핸드폰'과 '노트북'이 있는 단가를 모두 더해보겠습니다.

핸드폰과 노트북이 있는 단가를 모두 찾는 것이기 때문에 (+) 논리합을 사용하겠습니다.

=SUMPRODUCT((A2:A8=F2)+(A2:A8 = G2),C2:C8)

(A2:A8=F2)*(B2:B8 = G2) => (A2:A8=F2)+(A2:A8 = G2)

논리곱을 논리합으로 변경하고 조건2의 범위를 B2:B8에서 A2:A8로 변경하였습니다.

위 그림처럼 한 가지 조건이라도 일치하면 숫자를 더해주는 것을 확인할 수 있습니다.

혹시 엑셀 배열함수(배열수식)라는 말 들어보셨나요?

이해하기 쉽게 말씀드리자면 )함수의 기본 양식?을 깨버린다_ 라고 보시면 되겠습니다.

이게 무슨말인지 SUM 함수로 예를 들어 설명해드리겠습니다.

제품명과 구매/판매, 단가, 수량이 있는 표가 있습니다.

함수를 사용하여 이 제품들의 단가 * 수량의 총 합계를 구해볼까요?

총 합계를 구하려면 위 그림과 같이 우선 각각의 합계(단가*수량)을 구해야합니다.

그리고 =SUM(E2:E8) 으로 각각 구한 합계를 더해야 합니다.

총 합계를 구하려 쓸데없이 각각의 합계 셀을 만들어야만 했습니다.

우리가 구하고자 하는 것은 총 합계인데 왜 지저분하게 각각 합계 셀이 생겼을까요?

아까 위에서 배운 논리연산을 사용하면 단가*수량 한 값들을 모두 더할 수 있지 않을까요?

굉장히 간단합니다! =SUM(C2:C8*D2:D8) 을 해주면 됩니다.

C2:C8*D2:D8 C2:C8의 값과 D2:D8의 값을 1:1로 매칭하여 곱한뒤

=SUM(C2:C8*D2:D8) =SUM() 더한다 입니다. 정말 쉽죠?

하지만 결과는? 에러가납니다. 왜 그럴까요?

=SUM(num1,num2.....) 이 함수는 인수들의 합을 구하는 함수입니다.

엑셀에서 인수들의 값을 넣게 만들었지 SUMPRODUCT처럼 논리연산 값을 넣을 수 있도록 만들지 않았습니다.

그렇다면 SUM 함수 인수부분에 논리연산을 넣을 수 있도록 바꾸면 되지 않을까요?

이것이 바로 '배열함수(배열수식)' 입니다.

함수에 배열수식을 입히는 법은 간단합니다. 한번 적용해보겠습니다.

위와 같이 함수 양 끝에 { } 가 자동으로 붙게 됩니다.

이처럼 E열(각각의 합계)을 참조하지 않았음에도 총 합계를 구할 수 있습니다.

참조하지 않고 있는 필요없는 E열은 지워버립니다.

계산 과정 살펴보는 법

수식 - 수식 계산을 클릭합니다.

위와 같이 계산 과정을 확인할 수 있습니다.

배열 수식이라고 부르는 이유는 =SUM(C2:C8*D2:D8) 에서 C2:C8 * D2:D8 연산한 값을 메모리(배열)에 저장해 두었다가 사용하기 때문입니다. 이렇게 배열에 저장해두고 SUM 함수 연산을 수행하기 때문에 각각의 셀 합을 따로 쓸 필요가 없습니다.

왼쪽은 일반적인 방식(각각의 셀 합을 구하고 더함)이고 오른쪽은 배열함수를 사용한 방식입니다.

차이가 확실히 느껴지시나요?

참조만 할 값을 화면상에 표시하는것은 너무 지저분하겠죠?
귀찮다면 야매로 셀 숨기기를 사용해도 됩니다.....

엑셀 자동화

이번 포스팅에서는 중,고급 함수인 SUMPRODUCT 함수와 배열함수(배열수식)에 대해 알아보았습니다.

배열 함수의 경우 참조할 내용이 많으면 많을 수록 효과가 극대화되고 수식 수정/관리 하기에도 몹시 편합니다.

엑셀에는 엄청나게 많은 함수가 존재합니다. 하지만 업무용으로 사용하는 함수는 몇개 되지 않습니다.

이 몇개 안되는 함수들을 시간을 조금만 투자해서 익혀둔다면 자신의 전산작업을 좀 더 편하고 빠르게 진행할 수 있습니다. 초급편에서 다루었던 모든 함수들을 한번씩 연습해보시고 적용하신다면 업무에 많은 도움이 될 것이라 생각합니다.

vba 자동화 강의로 시작했던 엑셀 강의가 벌써 11번째가 되었습니다. 앞으로 알려드리고 싶은 것도 많고 그림판처럼 신기한 것들도 많이 구현할 예정이니 많이 기대해주세요!

준비한 초급편 시즌1? 은 여기서 마무리하고 추후에 필요한 내용이 있으면 따로 포스팅 해 보겠습니다.

혹시 강의에 추가되었으면 하는 기능이 있다거나

조언해주실 부분이 있으시다면 댓글로 거침없이 피드백 주세요! 쓴소리 좋아합니다!

능력있는 직장인이 되기 위해 엑셀로 전산프로그램을 만들 때 까지 한번 열심히 달려봅시다!

다음 포스팅에서 뵙겠습니다!

지난 포스팅

[Excel] 1. 능력있는 직장인이 되는 법. 엑셀 자동화란?

[Excel] 2. 능력있는 직장인이 되는 법. 다른 파일 불러오기!

[Excel] 3. 능력있는 직장인이 되는 법. 물품 입고시키기!

[Excel] 4. 엑셀로 능력있는 직장인이 되는 법. 간편한 UI 만들기!

[Excel] 5. 엑셀로 능력있는 직장인이 되는 법. 로그인 + 접속기록 만들기!

[Excel] 6. 엑셀로 능력있는 직장인이 되는 법. 재고(자산) 관리용 현황판 만들기!

[Excel] 7. 엑셀로 능력있는 직장인이 되는 법. 초급편 (기초함수 소개)

[Excel] 8. 엑셀로 능력있는 직장인이 되는 법. 초급편 (VLOOKUP, INDEX, MATCH 등)

[Excel] 9. 엑셀로 능력있는 직장인이 되는 법. 그림판 만들기 ! (매크로 기록 활용)

[Excel] 10. 엑셀로 능력있는 직장인이 되는 법. 엑셀 그림판에 애니메이션 기능 추가하기!