by
Nov 22. 2023
엑셀 - 조건이 여러개인 sumifs 배열로 처리하기
조건이 복수 혹은 여러 개인 열들을 한 식에서 처리해보기
조건이 여러개인 sumifs를 배열로 처리하기
저번 시간에는 조건 값을 한 개만 가지는 sumif를 배열로 가져오는 법을 같이 배워보았습니다.
(아래 링크 확인!)
https://brunch.co.kr/@chunja07/122
글을 작성해보고 생각해보니 복수 조건, 즉 sumifs를 어떻게 배열로 처리할 것인지에 대한 이야기를 하지 않았기 때문에, 이 글에서 한번 정리해보려고 합니다.
(1개 조건 및 배열과 관련된 이야기는 위 링크에서 이미 한번 하였으니, 궁금하신 분은 윗 글을 참조해주세요)
1. Sheet1 - Raw 데이터 확인
우선 Sheet1에 들어있는 Raw 데이터를 보도록 하겠습니다.
A열 Payout Completed Date 날짜, B열 Division 부서, C,D,E,F,G,H,I열은 각종 비용들입니다.
현재 구하고 싶은 값은 2023-09-20일에 해당하고, Division이 "AP"에 해당하는 C~K열의 합을 모두 구하고 싶습니다.
2. Sheet2 대시보드 만들기
우선 대시보드(Sheet2)를 만들면 아래와 같이 되겠네요.
우리가 이제 하려고 하는 것은, 부대 비용에 2023-9-20일 날짜에 해당하고 Division이 AP인 비용들을 한번에 계산해서 넣는 것입니다. 그리고 그 식을 복붙하면 2023-10-4일 역시 자동으로 계산되게 만들도록 하겠습니다.
3. 조건 값 True/False 배열 불러오기
우선 조건 값이 2023-9-20일에 해당하는 배열 값을 Sheet1에서 True/False 값으로 불러오도록 하겠습니다.
아래 수식을 살펴보면 Sheet1의 A열(날짜데이터열) 중에서 Sheet2의 B3(2023-9-23)와 같은 행을 모두 불러왔습니다.
=Sheet1!$A$2:$A$32=Sheet2!B3
이렇게 하게 되면 날짜 조건 1개인 sumif에 대한 배열 값을 모두 불러온 것입니다. 하지만 Division이라는 조건이 추가되었기 때문에 조건이 하나 더 추가되어야 합니다.
즉 Sheet1의 B열에서 Sheet2의 'AP' 혹은 C3값과 일치하는 배열들을 모두 불러와야 하는 것이죠. 이렇게 하려면 어떻게 할까요? (먼저 한번 시도해보신 후 밑의 사진을 봐주시면 실력 향상에 도움이 됩니다.)
조건을 하나 더 추가하기 위해서는 곱하기를 사용합니다.
한가지 주의할 점은 두 개의 식을 괄호로 꼭 감싸줘야 에러가 뜨지 않는다는 것입니다.
이 수식을 잘 살펴보면 Sheet2 A열에서에서 2023-09-20(B3)과 같은 배열 값을 가져오고, 그 배열의 모든 행들을 Sheet2 B열에서 AP(C3)와 같은 배열 값을 곱한 것을 확인할 수 있습니다.
=(Sheet1!$A$2:$A$32=Sheet2!$B3)*(Sheet1!$B$2:$B$32=Sheet2!$C3)
이렇게 괄호로 감싼 이유는 괄호로 감싸지 않을 경우 True X 문자열이 되어 값이 성립하지 않게 되기 때문입니다. 하지만 각 식을 괄호로 감싸면서 True 값을 1, False 값을 0으로 만들어 1과 0의 값만 가져오게 되었습니다.
만약 조건이 3개가 된다면, 곱하기를 추가하고 추가 열을 괄호로 감싸주기만 하면 AND 조건에 따라 3조건을 만족하는 행을 1값으로 반환합니다.
4. 합산할 비용 열과 곱셈하기
이후부터는 sumif와 같습니다.
2023-09-20일에 해당하고, Division이 AP에 속하는 값의 배열을 가져오고 싶다면 그 열의 값만 곱하기로 추가하면 됩니다.
주의: 범위 열 숫자는 모두 같아야 합니다
수식으로 나타내면 아래와 같이 나오네요.
이것은 C열 비용만 배열로 가져온 것입니다.
=((Sheet1!$A$2:$A$32=Sheet2!$B3)*(Sheet1!$B$2:$B$32=Sheet2!$C3))*(Sheet1!$C$2:$C$32)
이제 C열부터 I열까지 차례로 가져오도록 하겠습니다.
아무래도 7개의 열을 모두 더하려고 하다보니, 수식이 조금 길어지긴 합니다.
=((Sheet1!$A$2:$A$32=Sheet2!$B3)*(Sheet1!$B$2:$B$32=Sheet2!$C3))*((Sheet1!$C$2:$C$32)+(Sheet1!$D$2:$D$32)+(Sheet1!$E$2:$E$32)+(Sheet1!$F$2:$F$32)+(Sheet1!$G$2:$G$32)+(Sheet1!$H$2:$H$32)+(Sheet1!$I$2:$I$32))
여기서 포인트는 조건절의 범위(밑줄친 부분)과 합을 해야하는 부분의 열을 하나의 큰 덩어리로 생각해서 괄호로 묶어 주어야 한다는 점입니다.
이제 마지막으로 이 배열값들을 모두 더하기 위해서 SUM 함수를 맨 왼쪽에 붙여주도록 하겠습니다.
=SUM(((Sheet1!$A$2:$A$32=Sheet2!$B3)*(Sheet1!$B$2:$B$32=Sheet2!$C3))*((Sheet1!$C$2:$C$32)+(Sheet1!$D$2:$D$32)+(Sheet1!$E$2:$E$32)+(Sheet1!$F$2:$F$32)+(Sheet1!$G$2:$G$32)+(Sheet1!$H$2:$H$32)+(Sheet1!$I$2:$I$32)))
위 수식에서 보는 것과 같이 배열을 불러온 함수 왼쪽에 Sum 함수를 붙여 모든 배열 값들을 더해주면 조건 1인 2023-9-20과 조건2인 "AP"에 해당하는 모든 비용의 합을 손쉽게 불러온 것을 확인할 수 있습니다.
위 식을 아래로 복사 붙여넣기 한다면, 2023-10-4일과 "FWD" 부서에 해당하는 모든 비용도 쉽게 불러오는 것을 확인할 수 있네요.
5. 마무리
