[스프레드시트] SUM IF - 조건별 합계 를 구해보자

SUM 함수 는 함수 사용도, 쓰는방법도 굉장히 간단하여 설명이 필요없이 누구든지 아주 간단하게 사용들 합니다.

이때 데이터의 조건을 걸어 합계를 낼때 SUMIF 함수를 사용하게됩니다.

오늘의 예시는 야채가게에서 야채를 판매한 금액, 수량을 합산한 데이터를 도출시켜보도록 하겠습니다.

 

자 우선 스프레드시트를 열어줍니다.

저는 연습장이라는 시트를 준비해봤습니다

 

자 데이터를 예시로 만들어보도록 하겠습니다.

 

우선 예시이여도 판매를 할 품목을 몇가지 적어두고 판매가도 선정해봤습니다

[주의!] 야채가게와는 전혀 무관합니다. 예시데이터니 데이터로만 봐주시면 되겠습니다.

판매상품 단가표

위와같이 상품을 나열하고 상품 판매가를 지정하여 판매할 상품의 단가표를 만들어보았습니다.

 

자! 그리고 판매내역을 만들어보도록하겠습니다.

자 A열~B열은 판매상품단가표를, D열~G열은 판매내역표를 만들어보았습니다.

판매내역을 만드는데, 상품명에 대입하여 판매수량 x 판매가 하여 매출금액을 만들어야하는데

저번시간에 배운 VLOOKUP 함수를 응용하여 만들도록하겠습니다.

 

https://crone.tistory.com/13

[스프레드시트] VLOOKUP 함수 - 데이터를 비교하여 원하는 값을 찾자

이번시간에는 SUMIF 함수에 대해서 포스팅을 하므로 VLOOKUP에 대해서는 위의 주소를 확인부탁드립니다.

 

함수는 =vlookup(E3,$A$3:$B$12,2,0) * F3 처럼입력하였습니다, E3을 기준으로 비교하는데 기준열은 A3부터 B12가 기준열이되고 고정하여 사용하기위해 F4번인 셀고정 을 사용하도록하겠습니다.

 - 셀고정 대해서는 다음시간에 포스팅하도록하겠습니다.

그리고, E열에 적혀져있는 상품에 해당하는 판매가를 가져온뒤, F열의 판매수량을 곱해 매출금액을 산정했습니다.

 

자 값이 산출되었습니다. 마찬가지로 다른 판매일자에 거래가이뤄졌던 데이터들도 매출금액을 산정하기위해 아래와같이 데이터를 긁습니다.

 

마우스로 드래그합니다.

 

데이터 준비는 이제 끝났습니다.

준비까지 서론이 길었습니다. 이제 본론으로 SUMIF 함수에 대해서 알아보도록하겠습니다.

저희는 이제 먼저 2019-11-01 일자에 총 매출금액을 합산해보도록하겠습니다.

 

sumif 설명

sumif 함수를 먼저 입력해줍니다.

범위 : 말그대로 범위입니다. 저희는 2019-11-01 일자와 동일한데이터에서 총 합계를 구해야하니 일자가 기준이됩니다.

기준 : 기준은 조건이 되는값을 말합니다. 2019-11-01 라는 일자가 조건이되겠네요.

범위합계 : 매출금액합계를 구해야하니 범위합계는 매출금액이 됩니다.

 

아래와같이 함수를 사용하겠습니다.

 

           범위    /    기준      /  범위합계

=sumif(D3:D12,"2019-11-01",G3:G12) 

근데 저희는 이걸로 부족합니다. 예시인경우만 이렇게 사용되고 실제로는 제 경험상 업무에사용할때는 일자별 매출금액을 보고싶어서 이 함수를 사용했을겁니다.

그럼 저희는 일자를 나열하여 해당하는데이터를 다 합산해주는 수식의 결과가 나오도록 만들어보도록하겠습니다.

먼저 모든일자를 중복되지않게 나열해줍니다.

 

 

데이터중복을 제거했습니다.

 

일자별 매출금액 란을 I열부터 J열까지하여 만들어냈습니다. 그다음 J열의 매출금액에 SUMIF를 대입하여 넣어보도록하겠습니다. 이때 사용되는건 SUMIF와 함께 위에서 언급된 셀고정(절대참조)를 사용하도록하겠습니다.

 

     범위(절대참조) / 기준 (상대참조) / 범위_합계 (절대참조)

=sumif($D$3:$D$12,I3,$G$3:$G$12)

 

절대참조를 사용하는 이유는, 저희는 J3열에만 사용되는것이아니라 모든J열에 사용할 예정이므로 값이 이동되면 범위와 범위합계도 동일하게 이동되기때문에 절대참조를 사용합니다.

 

값이 산정되어 나왔습니다.

그리고 일자별 매출을 전부구하기위해 데이터를 아까와같이 긁어보도록하겠습니다. (드래그)

 

매출금액이 전부산정되었습니다.

이를 응용하여 하나더, 동일한 내용으로 매출일자별 판매된 판매수량을 이번에는 합산하고싶습니다.

K열에 판매수량항목을 추가했습니다.

이미 J열에 매출금액을 구하는 함수를 만들어뒀기때문에 더더욱 간단합니다!

범위합계만 변경하면끝입니다. -> =sumif($D$3:$D$12,I3,F3:F12)

그리고 모든열에 사용하기위해 절대참조 (F4 버튼누름) 를 사용하겠습니다.

절대참조 사용 -> =sumif($D$3:$D$12,I3,$F$3:$F$12)

 

자 판매수량을 구해봤습니다.

마찬가지로 긁어줍니다. (드래그)

 

판매수량을 위와같이 구해봤습니다.

 

자 이번에는 완전하게 습득하셨는지 보기위해 문제를 내드릴게요!

문제는 상품별 매출수량 매출금액을 구하기입니다.

상품이 기준이되어 총 매출수량 매출금액을 구하는것입니다.

 

I10열부터 K16열에 셀을만들었습니다.

 

상품별 판매수량 매출금액

위에서 언급드린 데이터 중복제거, 절대참조, SUMIF를 활용하여 꼭 만들어보세요.!

결과 값은 아래에 적어드리도록하겠습니다

 

꼭 스스로 한번 풀어보시고 풀어보시다가 안되시는점이있다면 댓글남겨주세요~

 

이번시간에는 SUMIF에 대해서 알아봤습니다. VLOOKUP과 SUMIF를 함께 같이사용하게되면 훌륭한 리포트를 만들어낼수있습니다.

그리고 리포트의경우 빈번하게 사용되지않고 가끔가다가 사용되므로 SUMIF의 함수를 포스팅해봤습니다.