{엑셀} 월별 매출 집계표 만들기 - 실전 활용

안녕하세요? 제임스입니다. 지난번 피벗테이블을 해 보았지만, 입력하는 순간 바로 집계표가 Update되는 방법이 더 쓰기에 좋더라고요. 저는 회사 일할 때 피벗 테이블 방법은 급할 때만 쓰고 평소엔 바로 통계가 나오게 만들어 쓰는 편입니다.

지난번에 문방구 매출대장을 만들어 연습을 해 보았는데, 이번엔 날짜를 수정해서 월별 집계표를 만들어 보겠습니다.

 

---

매출장 이름 정의

---

일단 작업을 편하게 하기 위해 매출장의 이름을 만들어 넣겠습니다. 이름은 "매출장"으로 하겠습니다.

설명 안 드리고 화면 두 장 넣었습니다. 만일 기억이 안나시면 아래 링크를 따라가 보시면 되겠습니다.

https://jamesohn.tistory.com/211

---

월별 집계표 만들기

---

일단 이렇게 2021년 1월부터 12월까지 만들었네요. 만일 년도별로 월별 매출을 집계하실 거면 앞의넌도는 필요없겠죠?

 

계산을 더 빠르게 하기 위해 년, 월 보조 셀을 매출장에 만들어 놓겠습니다. B열을 만들고 =YEAR(A1) 이렇게 입력하여 년도 값을 가져옵니다. 그리고 C열을 만들어 =MONTH(A1)이렇게 하여 월 값을 가져옵니다. 그런데 값이 이상하게 보이지요?

 

이건 {엑셀} 날짜의 숫자적 의미와 계산 방법 에서 설명 드렸죠? 당황하지 마시고 숫자 형식으로 바꾸세요.

자 이렇게 되었나요? 그리고 보기 좋게 둥앙정렬을 해 놓았습니다.

그리고 나머지 다 복사해 넣으면 되겠네요. Ctrl + C하고 붙여 넣을 영역 전체를 선택한 후에 마우스 오른쪽 버튼을 누르고 붙여넣기 옵션을 수식으로 하면 되겠네요.

이제 실제 입력하는 데이터가 아니라는 뜻으로 약간 회색을 넣어 두고 숨기기를 해 놓고 씁니다만 강의를 위해 그냥 보여지도록 놓고 진행하겠습니다. 대신 열 너비는 좁게 만들었습니다.

---

SUMIF 사용

---

앞에 있는 월에 해당되는 조건을 매출장에서 찾아 해당 금액의 합을 구하면 되겠습니다. 함수는 SUMIF()를 사용했어요.

 

함수 식

=SUMIF(매출!C2:C37, 월별매출!B2, 매출!H2#) <- 아래에서 수정될 것임

 

함수 설명

매출 시트의 C열의 값 중에 월별매출 시트의 B2 즉 1월에 해당되는 값을 찾고 맞으면 매출 시트의 금액을 합하라는 함수입니다.

이것을 아래로 복사만 하면 시트가 완성되겠죠? 아닙니다. 이것을 그대로 복사해 붙여넣으면 붙여 넣는 위치가 위에서 아래로 붙여넣게 되잖아요? 그럼 각 셀값들이 이동 할 때마다 한개씩 위에서 아래로 증가하게 됩니다. 따라서 원하는 답을 얻을 수 없겠지요.

위에서 아래로 마우스 자동 입력 방법을 이용해 아래로 붙여 넣었더니 에러가 났네요.

 

셀의 식을 보니

=SUMIF(매출!C3:C38, 월별매출!B3, 매출!H3#)

 

이렇습니다. 무엇이 잘 못됬지요? C3:C38 이부분이 잘 못되었네요. 자동 붙여넣기를 했더니 발생된 오류입니다. 마우스를 드레그해서 입력하는 방법은 위에서 아래로 이동하며 붙여넣는 방법을 썼기 때문에 모든 셀의 열번호가 자동으로 증가하게 됩니다.

 

이런 경우를 방지하기위하여 절대값을 사용합니다. 즉 C2의

 

=SUMIF(매출!C2:C37, 월별매출!B2, 매출!H2#) 을

 

=SUMIF(매출!$C$2:$C$37, 월별매출!B2, 매출!$H$2#)

 

이렇게 바꾸어 주고 마우스 장동 복사를 해보죠.

잘 되었네요. 자 이제 12월까지 붙어넣기를 해 보겠습니다.

자동 붙여넣기로 완성되었네요. 하지만 한가지 문제가 위에서 아래로 마우스로 자동 붙여넣기를 하면 맨 아래 셀의 태두리가 바뀐 것을 볼 수 있습니다. 위 속성을 그대로 붙여넣기 하였기 때문입니다.

 

그래서 전 수식 붙여넣기를 합니다. 그럼 테두리 속성은 바뀌지 않고 식만 붙여넣기가 되어 테두리가 깨지지 않습니다.

끝부분의 테두리가 바뀌지 않았지요? 이제 혹시나 식이 잘 못되었을 경우를 고려하여 매출 시트의 총합계와 월별매출 합계 금액을 계산해 보면 검산이 완료되겠네요.

매출 시트의 금액 부분 전체를 마우스로 드레그 하여 보면 아래 합계가 표시가 됩니다. 7,985,000 라고 보이네요.

월별 매출에도 똑 같이 해 보면 7,985,000 라고 보이시죠? 금액이 같으니 검산도 됐네요.

 

수고하셨습니다. 지금까지 제임스였습니다. 감사합니다.

---

자료 요청, 질문, 의견이 있으시면

---

본 문에서 사용된 자료가 필요하시면, turboson+excel@gmail.com으로 요청하세요. 또 궁금한점이 있으시면 질문하시고 의견이 있는 분도 연락주시기 바랍니다. 아래 베너를 클릭하시면 메일을 발송하실 수 있습니다. 안되시면 메일주소를 직접 입력하셔도 됩니다.