생각났을 때 정리해둔다. vlookup으로 끙끙대다가 sumproduct로 해결됐기 때문이다.
가끔 우리가 테이블에서 특정 항목을 찾아 같은 열에서 돌려주는 것을 할 때가 있다.
이럴 때는 vlookup을 보통 사용한다. 아래 그림처럼 말이다.
1. VLOOKUP 사용 / VLOOKUP을 사용했을 때 한계
잘 살펴보면 아래와 같다.
=VLOOKUP(찾을내용, 값을 찾을 범위, 돌려줄 N번째행, 일치여부TF)
이런식으로 해서 원하는 항목을 찾아 해당 테이블에서 특정 열의 값을 돌려준다. 행을 돌려주는 hlookup과 함께 자주쓰이는 함수 중의 하나이다.
그런데 가끔 우리가 바나나처럼 1개만 있는 게 아니라 항목별로 "수량"과 "단가"를 "곱"해서 "더"해야 할 때가 있다. 항목별 판매금액과 같은 것이다.
예를 들면 과일 항목의 사과, 바나나, 파인애플 전부 다 판매금액을 다 합쳐야 하는 것이다.
이 때 그냥 vlookup을 써서 '과일'을 찾아 수량*단가를 곱하게 되면 맨 첫번째 있는 사과의 수량*단가로 아래처럼 6,000이라는 값만 나오게 된다.
2. 기본적인 SUMPRODUCT 사용법
이 때 쓰는 것이 sumproduct이다. sumproduct는 원래 두 배열을 곱해서 하나값으로 돌려주는거다.
해당 부분에서는 수량과 단가를 가로로 모두 곱해서 더해주는 역할을 한다.
(추가 설명) : https://crossall.tistory.com/3
근데 여튼 우리가 하고 싶은 것은 "과일"만의 총 판매금액을 알고 싶은 것이다.
이럴 때는 SUMPRODUCT를 써야 한다.
3. SUMPRODUCT로 항목에 속하는 여러 값의 곱-합 구하기
=SUMPRODUCT(((A1:A10)="과일")*1
라고 하면 어떤 일이 벌어지냐면, 위의 예제에서 3이 나온다.
그 말인 즉슨 A1부터 A10까지 중에서 과일이 있으면 True(1)을 반환, 없으면 False(0)을 반환하는 것이다.
| 항목 | (An="과일") |
| 과일 | 1 |
| 채소 | 0 |
| 채소 | 0 |
| 과일 | 1 |
| 생선 | 0 |
자 그럼 이제 쉽게 항목에 있는 모든 과일 부분에서 수량과 단가를 곱해 합을 구할 수 있게 된다.
=SUMPRODUCT(((A2:A10)=F2)*1,C2:C10,D2:D10)
=SUMPRODUCT(((판별할 항목 열)=찾을항목)*1, 열2, 열3)
즉, (1*2*3,000) + (1*1*5,000) + (1*1*6,000) 해서 17,000원이라는 값이 나오게 된다.
맨 아래에서 음료의 경우 A 열에 없기 때문에
첫번째 곱해야 할 값이 모두 0이 되어 0원이 나오게 된다.
한참 vlookup 가지고 씨름하다 sumproduct로 완료하게 되어 신나서 올려본다.