[구글 스프레드 시트 활용] 출퇴근 시간 기록 문서 만들기-4(매크로, 다른 시트 값 불러오기, SUM, AVERAGE 함수 활용)

이전 포스팅에서 이어집니다.

[구글 스프레드 시트 활용] 출퇴근 시간 기록 문서 만들기-3(IF, COUNTA, NOW 함수 활용)

[구글 스프레드 시트 활용] 출퇴근 시간 기록 문서 만들기-3(IF, COUNTA, NOW 함수 활용)

 

 

이전 포스팅들에 이어 구글 스프레드 시트 활용하여 출퇴근시간 기록하는 시트를 만들어 보겠습니다.

이번에는 월간 시트의 합을 보여주는 전체통계 시트를 만들어보겠습니다.

 

전체 통계 시트에는 크게 8가지 항목을 입력해주겠습니다.

 

월, 월/연 총 출근일, 월/연 평균 출근시간, 월/연 평균 퇴근시간, 월/연 평균 근무시간, 월/연 총 초과 근무시간, 월/연 총 연차 사용일

 

각 항목은 다음과 같은 의미를 가지고 있습니다.

월 : 말 그대로 해당 정보의 월 값을 가지고 올 것입니다.

월/연 총 출근일 : 총 출근한 일수를 계산할 것입니다.

월/연 평균 출근시간 : 평균 출근시간입니다.

월/연 평균 퇴근시간 : 평균 퇴근시간입니다.

월/연 평균 근무시간 : 평균 근무시간입니다

월/연 총 초과 근무시간 : 총 초과 근무시간입니다.

월/연 총 연차 사용일 : 총 연차 사용일입니다.

 

 

그럼 위의 값을 입력할 틀을 만들어줍니다.

 

값을 채우기 전,

우선 다른 시트의 값을 가져오는 법을 알아보겠습니다.

 

'셀이름'!셀주소

위의 식을 활용하면 됩니다.

셀 이름에는 공백이 없는 것이 좋습니다.

시트의 이름을 바꿀 경우, 식에 적용된 이름도 함께 변경됩니다.

 

 

 

 

그럼 1월의 값을 가지고 와 보도록 하겠습니다.

일단 '1월'부터 적기 귀찮으니,

1월 시트에서 적혀있는 값을 가져옵니다.

아래의 함수를 적어줍니다.

나중에 열 방향 채워주기를 위해 열방향 값을 $기호를 이용하여 고정해줍니다.

 

='1월'!B$1

 

 

 

 

월간 통계 값은 이미 각 월 시트마다 계산되어있습니다.

그대로 긁어서 가져올 수 있습니다.

일단 월 총출근일 셀 주소를 입력해줍니다.

 

='1월'!C$2

 

그러고 나서 행방향으로 드래그해주면, C$3>D$3로 알아서 채워줍니다.

 

 

이제 12월까지 채워주면 되는데...

1월 시트를 11개 복사할 생각을 하니 정신이 아찔해집니다.

 

 

 

이럴 땐 매크로를 사용하면 됩니다.

시트 안에 1월을 2월로 바꿔주는 것 까지는 어렵지만...

단순 시트 복사 정도의 행위는 매크로로 가능합니다.

 

도구 > 매크로 > 매크로 기록을 선택합니다.

 

 

 

그럼 화면 하단에 매크로를 기록하는 창이 뜹니다.

 

 

 

오늘은 셀에 적용되는 매크로가 아닌 시트에 적용되는 매크로이니,

절대 참조를 사용하도록 합니다.

 

 

 

매크로가 기록되고 있는 상태에서 시트를 복사한 후, 저장을 눌러줍니다.

 

 

 

 

 

저장 버튼을 누르면 저장 안내창이 제공됩니다.

 

 

 

적당한 이름과 단축키를 하사합니다.

단축키를 굳이 지정할 필요 없이 매크로 탭에서 선택해서 적용시킬 수도 있지만

11번 반복해야 하니 적당한 단축키를 부여해줍니다.

 

 

 

 

저장한 후, 단축키를 눌러주면 저절로 시트가 복사되는 기적을 보실 수 있습니다.

 

 

 

 

물론 이후 시트 이름 변경과 날짜 변경은 매크로 사용이 어려우니

한 땀 한 땀 해야 합니다.

 

 

 

이제 시트에 1월부터 12월까지 채워볼 차례입니다.

 

1,2,3,4월로 증가했으니 시트 명도 자동 채우기가 되지 않을까?

하는 헛된 기대를 품을 수 있습니다.

 

응 아니야

 

 

일단 드래그 채워 넣기를 통해 셀 주소라도 복사한 후,

시트 이름은 하나하나 차곡차곡 바꿔줍니다.

 

 

자동화 하는 방법 꼭 알아내고 말테다...

 

 

 

 

월간 통계를 만들었으니 연간 통계도 만들어봅니다.

 

연간 총 출근일의 합, 연 평균 출근율, 연 평균 출근시간, 연 평균 퇴근시간, 연 평균 근무시간, 연간 총 연차 사용일이 필요합니다.

 

총 합의 경우 SUM 함수를,

평균의 경우 AVERAGE 함수를 사용해줍니다.

 

총 출근일 =SUM(C5:C11)
연 평균 출근율 =AVERAGE(D5:D11)
연 평균 출근시간 =AVERAGE(E5:E11)
연 평균 퇴근시간 =AVERAGE(F5:F11)
연 평균 근무시간 =AVERAGE(G5:G11)
연간 총 연차 사용일 =SUM(H5:H11)

 

 

 

출퇴근 기록표 완성입니다.

 

엑셀 파일로 공유드리려고 했는데

이번 활용에서 사용된 수식이 엑셀과 맞지 않은 부분이 많아

엑셀에서 제대로 표현되지 않기에 과감히 생략합니다.

누구 맘대로...?

 

 

그럼 다음 포스팅으로 찾아뵙겠습니다.

감사합니다.