안녕하세요! gbgg 입니다. 능력 있는 직장인이 되는 법! 열 번째 시간입니다!
지난 중급편에서는 VLOOKUP, INDEX, MATCH 함수 등에 대해 알아보았는데요.
이번 포스팅에서는 다중 조건 함수들을 살펴보겠습니다.
다중 조건 함수는 특정 데이터를 뽑아내거나 통계를 내기 전 데이터를 정리할 때 많이 사용됩니다.
이 함수들 또한 업무용으로 많이 사용되므로 알아두시면 좋습니다!
활용 예제와 함께 잘 설명드릴게요!
다중 조건을 통해 조건에 맞는 값들만 더하고 싶을 때 if문을 여러 개 사용해야 합니다.
물론 다중 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 함수를 사용하여 이 제품들의 단가 * 수량의 총합계를 구해볼까요?
총합계를 구하려면 위 그림과 같이 우선 각각의 합계(단가*수량)를 구해야 합니다.
그리고 =SUM(E2:E8)으로 각각 구한 합계를 더해야 합니다.
총합계를 구하려면 각각의 품목에 대해 합계를 만들고 이들을 더해야 합니다.
우리가 구하고자 하는 것은 전체 총합계인데 왜 각각 합계 셀을 추가했을까요?
아까 위에서 배운 논리 연산을 사용하면 단가*수량 한 값들을 모두 더할 수 있지 않을까요?
굉장히 간단하지 않을까요? SUM 함수로 다음과 같이 작성해봅시다.
| =SUM(C2:C8*D2:D8) |
C2:C8*D2:D8 : C2:C8의 값과 D2:D8의 값을 1:1로 매칭 하여 곱한 뒤
=SUM(C2:C8*D2:D8) SUM 함수로 묶어서 더합니다. 간단하죠?
하지만 결과는 에러가 납니다. 왜 그럴까요?
=SUM(num1, num2.....) 이 함수는 인수들의 합을 구하는 함수입니다.
SUM 함수의 인수에는 값만 넣을 수 있도록 만들어져 있어 SUMPRODUCT처럼 논리 연산 값을 넣을 수가 없습니다.
그렇다면 SUM 함수 인수부분에 논리 연산을 넣을 수 있도록 수정하면 되지 않을까요?
이것이 바로 '배열 함수(배열 수식)'입니다.
함수에 배열 수식을 입히는 법은 위와 같이 매우 간단합니다. 한번 적용해보겠습니다.
위와 같이 함수 양 끝에 { }가 자동으로 붙게 됩니다.
위와 같이 배열 함수를 사용하면 E열(각각의 합계)을 참조하지 않았음에도총합계를 구할 수 있습니다.
배열 함수를 알고 있다면 참조하지 않고 있는 필요 없는 E열은 지워버려도 되겠죠?
- 계산 과정 살펴보는 법
수식 - 수식 계산을 클릭합니다.
위와 같이 계산 과정을 확인할 수 있습니다.
- 배열 수식이라고 부르는 이유는 =SUM(C2:C8*D2:D8) 에서 C2:C8 * D2:D8 연산한 값을 메모리(배열)에 저장해 두었다가 사용하기 때문입니다. 이렇게 배열에 저장해 두고 SUM 함수 연산을 수행하기 때문에 각각의 셀 합을 따로 구하여 더할 필요가 없습니다.
왼쪽은 일반적인 방식(각각의 셀 합을 구하고 더함)이고 오른쪽은 배열 함수를 사용한 방식입니다.
차이가 확실히 느껴지시나요?
참조만 할 값을 화면상에 표시하는 것은 너무 지저분하겠죠?이 과정도 귀찮다면 꼼수로 셀 숨기기를 사용해도 됩니다.
엑셀 자동화
이번 포스팅에서는 중급 함수인 SUMPRODUCT 함수와 배열 함수(배열 수식)에 대해 알아보았습니다.
배열 함수의 경우 참조할 내용이 많으면 많을수록 효과가 극대화되고 수식 수정/관리 하기에도 몹시 편합니다.
엑셀에는 엄청나게 많은 함수가 존재하지만 업무용으로 사용하는 함수는 몇 종류 되지 않습니다.
이 몇 종류 되지 않는 함수들을 시간을 조금만 투자해서 익혀둔다면 자신의 전산작업을 좀 더 편하고 빠르게 진행할 수 있을 것입니다.
능력 있는 직장인이 되기 위해 엑셀로 전산프로그램을 만들 때 까지 한번 열심히 달려봅시다!
다음 포스팅에서 뵙겠습니다!
최근댓글