엑셀 sumif 원리 및 로직 파악하기
질문은 나의 원동력 - 엑셀(Excel)에서 sumif 조건 처리
Shopee에 입점한 한 회사에 다니는 친구에게 엑셀 관련 질문을 받았다.
아래 사진과 같이 sumif를 여러개 사용하려다 보니, 수식이 너무 어려워져서 보기가 힘든데 이것을 좀 짧게 바꿀 수 있냐는 것이었다.(아래 노란색 칠해진 부분이 수식 부분이다. 총 3줄이 들어가있다...)
sumif에 대해 간단히 설명하면,
sumif/s는 함수 단어가 말하는 대로 if, 즉 어떤 열의 어떤 조건에 해당하는 곳에서 조건을 확인한 후 숫자로 이루어진 다른 열의 모든 값을 sum, 더해주세요라고 명령하는 것이다.
나는 개인적으로는 sumif는 쓰지 않고, sumifs만 쓰는데, if는 조건을 1개만 쓸 수 있고, ifs는 조건을 한개 및 여러개 쓸 수 있으니 굳이 if는 쓰지 않는 주의이다.
질문자가 만든 수식 내용을 살펴보면 다음과 같다.
정말 보기만 해도 엄청나게 긴 수식이다.
=SUMIF('RB_ SG_INVOICE'!J:J,'RB_SG 최종 정산(인보이스)'!D7,'RB_ SG_INVOICE'!U:U)+SUMIF('RB_ SG_INVOICE'!J:J,'RB_SG 최종 정산(인보이스)'!D7,'RB_ SG_INVOICE'!V:V)+SUMIF('RB_ SG_INVOICE'!J:J,'RB_SG 최종 정산(인보이스)'!D7,'RB_ SG_INVOICE'!W:W)+SUMIF('RB_ SG_INVOICE'!J:J,'RB_SG 최종 정산(인보이스)'!D7,'RB_ SG_INVOICE'!X:X)+SUMIF('RB_ SG_INVOICE'!J:J,'RB_SG 최종 정산(인보이스)'!D7,'RB_ SG_INVOICE'!Y:Y)+SUMIF('RB_ SG_INVOICE'!J:J,'RB_SG 최종 정산(인보이스)'!D7,'RB_ SG_INVOICE'!Z:Z)
즉 아래 대시보드 시트-RB SG 최종정산(인보이스)에서 Invoice날짜인 "2023-9-20","2023-9-28" 외 기타 중 Raw데이터 시트-RB SG INVOICE J열에서 "2023-9-20" 해당하는 조건을 확인 후 비용(U,V,W,X,Y,Z열)을 sumif로 더하여 하나씩 집어넣었는데, 비용 항목 열이 워낙 많다보니 sumif를 여러개 쓰게 되어 수식이 매우 길어지게 된 것이다.
사실 처리하는 방법은 Raw데이터에서 한번 데이터 가공한 뒤 하는 방법도 있고, 여러가지가 있겠지만 Raw 데이터는 건들고 싶지 않다고 그냥 수식 줄일 수 있는 법을 알려달라고 한다;;(나도 RAW 데이터는 어지간하면 안 건드리지만 안쓰는 맨 오른쪽 열에 수식 하나 만들어 두는건 괜찮다는 주의이다)
RB SG 최종정산(인보이스) 시트 - 질문자의 데이터 보호를 위해 필요없는 부분은 모두 삭제하였다Raw 데이터를 살펴보면 J열은 날짜열이고 R열부터 X열까지는 모두 Shopee 비용이다.(원래는 U,V,W,X,Y,Z열인데 설명 편의를 위해 R부터 하도록 하겠다)
질문자는 특정 날짜의 모든 비용(U,V,W,X,Y,Z열)들을 더해서 하나의 셀에 넣고 싶었던 것이다.
RB SG INVOICE 시트 - 편의를 위해 불필요한 열은 숨겼다이것을 좀 더 쉽게 넣기 위해서는 어떻게 해야할까?
나 역시 30분 정도 고민을 해보다 배열을 사용하여 진행해 보기로 하였다.
우선 배열에 대한 간단한 설명이 필요할 것 같다.
배열 데이터 타입은 사실 프로그래밍을 배우지 못했다면, 생소한 단어일 수는 있다. 그래서 프로그래밍 비경험자들에게 간단히 설명하자면 데이터가 그냥 한개가 아니고 여러개있다고 생각하면 될 것이다.
(배열내 Index라던가 기타 여러가지 설명들이 있겠지만 넘어가도록 하겠다.)
앞으로 진행하는 것을 이해할 수 있다면, sumif/s가 엑셀 내에서 어떻게 작동해서 값을 추출하는지 어느정도 로직을 이해할 수 있게 될 것이다.
배열을 이용한 sumif 여러개 합치기
배열값을 활용하기 위해 한번 다음 수식을 넣어보도록 하자.
='RB_ SG_INVOICE'!J1:J100='RB_SG 최종 정산(인보이스)'!D15
RB_SG 최종 정산(인보이스) D15열에 따로 날짜 타입 "2023-9-20"(아래 사진 참고)을 넣어 RB_SG 최종 정산(인보이스) 시트 J1부터 J100까지 "2023-9-20"과 일치하는 것을 배열로 가져오라라는 수식의 위 함수는 보는 것과 같이 제목인 헤더값만 False를 리턴하고 이를 제외한 2열부터 True값을 가져오는 것을 확인 할 수 있다
True 값을 가져오는 이유는 Raw 시트 J열 1~100행까지 "2023-9-20"이라는 값과 같은 값이 있다면 True로 반환하라라고 명령을 줬기 때문에 "2023-9-20"을 지닌 값은 True를 반환하고 그 값이 아닌 값은 False를 반환한다.
RB_SG 최종 정산(인보이스)
RB SG INVOICE-J열 1행만 Payout completed Date 헤더값이고 그 아래는 9월 20일 데이터가 쭉 이어진다. 그 밑에는 10월 값도 존재한다.(참고로 Google Spreadsheet에서는 저렇게 할 경우 배열값으로 인식하지 못하니 Arrayfomula를 활용해 배열 타입으로 바꾸어줘야 한다.)
우리는 True 값을 활용하여, R열 Shipping Rebate를 배열로 또 한번 불러오도록 해보자.
앞에 전개했던 배열에 곱하기 표시를 이용해 다른 열과 덧붙이게 되면 어떤 일이 발생하게 되는걸까?
만약 2번째 행을 예로 들게 되면 J2인 "2023-9-20" 값과 R2인 1.84를 곱하게 되는데, 현재 J열은 True/False인 Boolean 타입으로 되어있어 둘이 곱한 값은 True를 1로 인식하여, 숫자 값이 나와야 하는데, False만 나와 있는 것을 볼 수 있다.
(맨 첫행의 경우 R1이 헤더값을 불러오기 때문에 Value를 불러오지 못하는 것을 확인할 수 있다.)
이것을 해결하기 위해서 각 식 앞에 괄호를 씌어보도록 하자.
수식에 괄호를 붙인것이 보이는가? 괄호를 붙이게 되면 True/False가 아닌 1/0으로 값이 치환되게 된다.
True = 1
False = 0
두 가지 값으로 바꿔주기 때문에, 숫자로 치환하여 "2023-9-23"에 해당하는 값은 True에서 1로 변환되고, R열의 숫자를 각 행마다 곱해주는 것이다.
즉 J2 = 1 , R2 = 1.84가 되어 배열의 2번째 값이 1.84가 되고, 그 아래로 쭈욱 내려가는 것이다.
여기서 중요한 것은 범위인데, 범위는 항상 일정해야 한다.
Array 배열의 Lengh가 다르다는 것은 배열에 속해 있는 값의 범위가 다르다는 뜻이다. 가령 범위가 1~100일 경우 한 Array 데이터의 길이가 100이기 때문에, 그것을 곱해주는 다른 모든 배열의 범위도 똑같이 100개의 길이를 지니고 있어야 하는 것이다.
현재 Raw시트의 데이터가 455개가 있어 나는 455개의 행을 불러올건데, 1행의 경우는 헤더값이므로 제외하고 2~455개의 행을 불러올 것이다. 하지만 앞으로 데이터가 늘어날 것을 대비하여 행의 갯수를 9,999개로 늘리도록 하겠다.
범위를 잘 살펴보도록 하면, 앞서 말했듯이 1행의 경우 헤더값으로 인해 Value를 불러오지 못해 J2:J9999 총 9,998개의 J열 범위와 R2:R9999의 범위가 잡혀있고, 1.84라는 값이 잘 나온 것을 확인할 수 있다.
그럼 여기서 다른 행도 추가하려면 어떻게 하면 될까? 그건 옆에다가 하나씩 이제 더하기만 하면 된다.
아래와 같이 R2:R9999 옆에 +를 넣고 똑같은 범위의 S열을 넣어주었다. 또한 R열과의 곱셈 계산이 겹치기 때문에 괄호를 하나 더 넣어서 따로 구분지어 주었다. 그럴 경우 아래와 같이 9월 20일 발생한 R열 비용과 S열 비용 합이 배열로 쭉 나오는 것을 확인할 수 있다.
그럼 나머지 비용을 추가하려면? 그냥 더하기와 범위만 추가해주면 된다.
(프로그래밍일 경우 For문을 써서 한번에 자동으로 연산을 할 수도 있겠지만, 엑셀에서는 그게 안되는 점을 프로그래머들이라면 아쉬워할 것이다. 아니면 그냥 매크로로 돌릴수는 있는데 그렇게 하게 될 경우 리소스를 너무 잡아먹어서 굳이 그럴 필요는 없어 보이기는 한다.)
아래를 살펴보면 내가 원하는 비용을 모두 모두 더하기로 집어넣은 것을 확인할 수 있다. 열이 몇개가 되든 간에 집어 넣으면 된다. 그럼 마지막으로 이 배열로 정리된 친구들을 한 개의 셀에 더해서 집어넣어야 하는데 어떻게 해야할까?
당연히 너무 쉽게도 Sum 함수를 맨 앞에 집어 넣으면 된다.(사실 sumif/s 를 코드로 짜면 저걸 코드로 구현하면 된다)
Sum을 조건 범위 열과 내가 원하는 비용을 더한 열 앞에 붙여주면 한 개의 셀안에 모두 더한 값을 넣을 수 있고, 아래에 보면 위에 sumif를 모두 더한 값과 똑같은 값이 나오는 것을 확인할 수 있다
참고로 나는 밑에 열을 Unique 함수로 Raw 데이터에 있는 모든 날짜를 불러왔기 때문에, 10월 27일이 추가된 걸 볼 수 있다.
모든 수식은 아래와 같은데, 처음 수식과 비교해보면 굉장히 많이 짧아진 것을 볼 수 있다.
=SUM(('RB_ SG_INVOICE'!$J$2:$J$9999='RB_SG 최종 정산(인보이스)'!D14)*('RB_ SG_INVOICE'!$U$2:$U$9999+'RB_ SG_INVOICE'!$V$2:$V$9999+'RB_ SG_INVOICE'!$W$2:$W$9999+'RB_ SG_INVOICE'!$X$2:$X$9999+'RB_ SG_INVOICE'!$Y$2:$Y$9999+'RB_ SG_INVOICE'!$Z$2:$Z$9999))
마치며
이렇게 sumif/s는 특정 조건에 맞는 값을 조건 범위에서 찾아 True/False를 가려낸 뒤, True에 해당하는 값들을 1로 치환하여, 합계 범위와 곱하여 모두 더한 로직으로 함수가 제작되었음을 알 수 있을 것이다.
이렇듯 함수가 어떻게 작동하는지 그 로직을 알면, 어떤 식으로든 함수를 사용할 수 있다.
엑셀에서는 배열을 알아서 불러오기 때문에 저렇게 할 수 있지만, Google Spreadsheet에서는 배열 값을 특별한 방식으로 처리해야 한다.
과연 Google Spreadsheet 에서는 sumif 여러 개를 한번에 더하기 위해서 어떻게 해야할지 다음 시간에 같
이 이야기해보도록 하겠다.
현재 코멘토라는 사이트에서 "데이터로 소통하고 싶은 PM을 위한 구글 스프레드시트 - SQL" 강의를 개설해 엑셀/구글스프레드시트/SQL 고수!가 되고 싶은 분들에게 토요일 오전 11시~2시마다 강의중입니다.
엑셀이나 스프레드시트에서 우리가 쓰는 함수가 백그라운드에서는 어떤 식으로 작동하는지 설명도 해드리기 때문에, 엑셀과 스프레드시트 고수가 되고 싶으신 분도 환영입니다.
PM 경력이 있어, 실제로 현재 서비스 되고 있는 서비스의 프로젝트 데이터 분석 기획과 엑셀/구글 스프레드시트/SQL을 묶어서 강의하고 있으니 관심 있으신 분들은 많은 참여 바랍니다.
https://comento.kr/class/pt/list
