실무 적용

주간보고 월간보고 쉽게하는 방법

excelcopycat 2020. 10. 21. 15:56

주간/월간 보고서 빠르게 작성하기
1. 정기보고서 작성시 어려움
  ㄴ 한 주가 월말에 걸린 경우
2. 해결방법
  ㄴ RAW DATA를 정리하라
  ㄴ MONTH 함수, WEEKNUM 함수, SUMIF 함수의 조합

주간 월간 보고서 예시.xlsx

1. 정기보고서 작성시 어려움

회사 생활 시작한지 얼마 안 되었는데 제 상사가 일감을 하나 주었어요. 지난 한 주간 생산 관련 데이타 보고서를 매주 월요일에 제출하라는.... 그리고 월 초에는 지난 달의 생산 보고서를 작성하라고... 뭐 일일 data들을 주간으로 종합하고 월 단위로 종합하는 일이었죠. 

상사가 그 일을 주면서 이러더라고요. 뭐, 어려운 일은 아니야, 일일 data를 1주일 단위로 종합하면 주간 보고서고, 그 주간보고 4개를 모으면 월간 보고야. 함 해봐.

"네 알겠습니다" 그러고 시작했죠. 주간 보고는 생산파트 특히사항이나 data의 경향을 볼 수 있고, 전주에 개선사항이 있으면 그 효과 등을 확인하기 위해 필요하죠. 월마감은 당연히 해야 하는거고.. 아무튼 좋은 취지가 있으니 열심히 매주 했어요. 

월요일에 전주 1주일치 일일 생산 파일을 열고 합계부분을 새로운 시트에 복사 붙여넣고, 그걸 6일치 반복하고 합계칸 넣으면 그게 전 주 주간보고 자료가 되었죠.
그 다음주엔 전주에 만들었던 주간보고 양식을 사용해서 자료 만들고 다른이름으로 파일 저장하고요. 이렇게 4주를 했어요.

근데 월말이 수요일이네요. 4주 주간보고 더하면 월마감이라 했는데....ㅜㅜ 4주는 28일이니, 4주 주간 자료들에다가 2일치를 더 더해야 월간보고가 되네요. 어쨌든 이렇게 월간 보고를 만드니 그 다음주에 주간 보고 만들 때는 전월 2일치랑 금월 4일치를 더해서 주간 보고를 해야 했습니다. 솔직히 어려운 일은 아니죠. 근데 완전 귀찮은 일입니다 이거... 하면 할수록 노가다 하는거 같고, 하는 일이 점점 무의미해지는 느낌이 들었죠... 이래선 안되겠다. 뭔가 다른 방법을 찾아야지 안 그러면 정신 건강에 안 좋을 것 같은 느낌을 많이 받았었습니다.


2. 해결방법

  ㄴ MONTH 함수, ㄴ WEEKNUM 함수, SUMIF 함수

1단계 : RAW DATA를 한 시트에 계속 모으

엑셀에서 가장 중요한건 RAW DATA의 정형화라고 생각해요. data가 잘 갖춰저 있으면 피벗이든, 함수는 이런 기능들을 이용해서 여러가지 필요한 정보를 빼낼 수 있어요. 그래서 일단 시작한 것이 일일 data를 한개의 시트에 복사 붙여넣었습니다. 주의사항은 가끔 생산시트 양식의 줄 순서나 항목이 추가/삭제 되는 경우 그것을 반영해 주서야 합니다. 

아래는 9월과 10월 raw data 붙여넣은 시트입니다. 9/28~10/3 기간은 40주차인데요. 보시듯이 한쪽은 9월 다른 한쪽은 10월입니다. 이제 주간 보고랑 월간 보고 자료가 바로 나오도록 틀을 만들겠습니다.


2단계 : 월간 보고 (MONTH 함수)

Raw data는 잘 정리되었고, 이제 2단계로 2행의 날짜가 월로 바꿀 수 있으면, 그리고 그 월에 해당하는 것들을 더하면 되겠죠? 날짜는 월로 변환하는 함수는 MONTH 입니다.
=MONTH(날짜셀선택)
9/1은 9로, 10/1은 10으로, 10/31도 10으로 값이 나왔죠? (11,12행 참조)

자 이제 SUMIF를 사용하여 요약표를 만들겠습니다.
=SUMIF(범위,조건,합계범위)
=SUMIF($11:$11,AH$14,3:3)

$11:$11은 11번 행 전부라는 뜻입니다. 11번 행 번호를 클릭하시면 행을 다 지정할 수 있어요. 일일 data를 앞으로도 계속 오른쪽으로 붙여 넣을거기 때문에 특정범위로 하면 매번 범위 수정해야 하는 불편함이 생기죠. 그래서 줄 전부를 선택했어요.
그리고 11번행은 고정되어야 합니다. 안 그러면 불량 등 밑으로 복사할 때 범위가 따라서 내려가요.F4키 한번 눌러서 달러를 기입합니다. 달러를 직접 써도 무방하나 좀 귀찮으니 F4 키를 눌러주세요.

AH$14 는 10 이라는 조건이 있는 셀입니다. 달러가 14행에 있으니 위아래로는 안 움직이고 좌우로는 움직힐 겁니다. 10월에 만든 수식을 11월에 붙여넣으면 AH$14 부분은 AI$14, 즉 11 이 적힌 셀로 이동할 거예요. 그럼 수식 조정하고 그런거 안 해도 되죠.

3:3 은요. 값들이 있는 범위입니다. 수식을 밑으로 복사하면 같이 따라 내려와줘야 하는 부분이죠. 그래서 달러 삽입 안 했습니다.

이렇게 수식 입력하고 복사해서 16~20번행에 붙여넣어주세요. 그럼 월간 보고 자료가 완성됩니다. 양품율에는 수식 넣으면 안됩니다. 이건 합계가 아니고 %이니까 따로 양품/총생산으로 수식입혀주세요. 위 예시에는 11월 raw data가 없으니 11월 칸에는 data가 0으로 나오네요. 11월 data 추가하시면 값이 자동으로 나올겁니다.


3단계 : 주간 보고 (WEEKNUM 함수)

월간보고에서 했던 로직과 같습니다. MONTH라는 함수 대신에 주차 번호를 가져오는 WEEKNUM 함수를 쓰시면 됩니다. 바로 아래 예시를 봐주세요.
=WEEKNUM(날짜셀클릭)

이제 날짜들을 주 번호로 그룹화시켰죠. 이제 또 SUMIF로 각 주의 합계들을 구하겠습니다.
=SUMIF(범위,조건,합계범위)
=SUMIF($11:$11,AH$14,3:3)

수식 작성법은 위에서 설명한 것도 동일합니다. 대신 요약표 상단을 월 대신에 주차 번호로 만들었죠.


화면에서는 한번에 보여드리기 위해 raw data 밑에 넣었지만, 실제로는 Raw data로 시트 하나 쓰시고, 월간보고로 시트하나, 주간보고로 시트하나 만드시면 좋습니다. 매일매일 raw data 값 붙여넣으시면 주간/월간 자료 바로바로 계산되요. 12월 만드시려면 11월 열 복사해서 옆에 삽입하시고 제목에 11 대신 12를 적으시면 바로 또 계산이 되죠.

이렇게 시간 절약하고 실수 줄이고 엑셀 기능도 배우고 참 좋죠? 부작용이요? 너무 빨리 일하면 상사가 다른 일감을 주는 경우가 있으니 보고일정은 적당히 알아서 맞춰주세요 ㅋㅋㅋ

여유있는 직장생활 하시길 바라며,
읽어주셔서 감사합니다!!

[관련글]
2020/10/12 - [Excel_Functions] - SUMIF 더하기 (항목/날짜/조건별 합계)

[추천글]

2020/11/04 - [실무 적용] - 붙어있는 셀값 일정 간격으로 띄우기

2020/10/26 - [실무 적용] - 일정간격 띄엄띄엄 떨어진 셀값 가져오기/합계