오랜만에 스프레드시트에 대해서 이야기할까 합니다.
주제는 스프레드시트를 이용해서 여러가지 날짜 계산을 하는 방법으로 주민번호를 이용해서 나이계산하고, 입사일자를 통해서 근속년수를 계산하고자 합니다.
MS엑셀에서 지원하는 Datedif함수를 구글문서도구에서는 지원하지 않아 어떻게 해야할까 고민을 좀 했는데..
의외로 단순하게 해결이 되었습니다.^^
먼저 날짜 계산을 위한 자료입니다.
나이, 근무년수 가 비어있는데 이곳을 채우는게 오늘의 목표입니다.ㅋ
일단 주민번호에서 나이를 추출하는 방법을 설명드립니다.
먼저 주민번호 앞자리 6자리는 생년월일 입니다. 따라서 주민번호 앞자리 6자리를 잘 활용하면 생년월일을 통해 나이를 계산할 수 있습니다.
먼저 앞자리 2자리는 태어낸해, 다음 2자리는 태어난달, 마지막 2자리는 태어난날이 됩니다.
따라서 문자열 함수를 활용하면 Left(주민번호가 있는 셀, 2) 를 하면 왼쪽부터 2자리인 태어난 해가 되고,
Mid(주민번호가 있는 셀, 3, 2)를 하시면 주민번호의 3번째 숫자부터 2자리 이므로 달이 됩니다.
그리고 Mid(주민번호가 있는 셀, 5, 2)를 하면 주민번호의 5번째 숫자부터 2자리 이므로 날이 됩니다.
이렇게 추출한 년, 월, 일을 Date함수를 사용해서 묶어주면 Date(Left(C2,2), Mid(C2,3,2), Mid(C2,5,2)) 를 해주시면 생년월일에 있는 날짜를 자동으로 구할 수 있습니다.
생년월일을 구했으니 이제 거의 목표에 도달했습니다.
MS엑셀의 경우 Datedif함수를 사용하면 날짜계산을 할 수 있는데..Datedif(시작일, 종료일, 옵션) 의 형식을 사용합니다.
시작일은 태어난 생년월일이고, 종료일은 현재 날짜이므로 Today()함수를 사용합니다.
그리고 옵션은 "y" 를 주면 날짜계산시 연단위로 계산하고, "m"은 월단위, "d"는 일단위로 계산이 됩니다.
우리는 나이를 구하면 되기때문에 Datedif(E2, Today(), "y") 를 하시면 나이를 구할 수 있습니다.
참고적으로 이렇게 구한 나이는 만으로 구하는 나이가 됩니다. 따라서 우리나라식 나이를 원하신다면 +1을 해주시면 됩니다.
그럼 이번에는 구글문서도구의 스프레드시트의 경우를 보겠습니다.
구글문서도구에는 Datedif라는 함수는 없습니다. 따라서 날짜계산은 그냥 "시작일-종료일" 이렇게 수식으로 하시면 됩니다.. ^^
(이걸 몰라서 한참 고민했습니다;;)
즉 =ROUNDUP((Today()-E2)/365) 를 해주시면 됩니다. 여기서 나이를 위해 365(1년)으로 나누기를 하면 yy.ddd 이런식으로 yy년하고도 좀더 살았다.. 이런식으로 결과가 나오므로 올림을 위해 ROUNDUP()함수를 사용해서 마무리를 합니다.
이렇게 하면 우리나라식 나이로 계산이 됩니다. ^^
제가 보기에는 구글문서도구가 훨씬 직관적으로 보입니다.
이젠 근속년수를 구해보도록 하겠습니다.
근속년수는 나이를 계산하는 것과 별반 다르지 않습니다.
MS엑셀의 경우 Datedif(입사일, 퇴사일, "y") 를 하면 되는데.. 문제는 현재 계속 근무하는 직원도 있고, 퇴사를 한 직원도 있습니다.
따라서 퇴사를 했는지 여부를 확인하기 위해 IF함수를 사용합니다. 사용법은 IF(조건, 참일경우, 거짓일 경우)이므로 IF(퇴사일<>0, Datedif(입사일, 퇴사일, "y"), Datedif(입사일, Today, "y")) 를 해주시면 됩니다.
즉, 퇴사일이 있는지 없는지 확인을 해서 퇴사일이 있으면 입사일과 퇴사일을 계산해서 근속년수를 구하고, 퇴사일이 없는 직원은 현재 재직중이므로 입사일과 현재날짜를 계산해서 근속년수를 구합니다.
이제는 구글문서도구에서 구하는 법을 알아보겠습니다.
IF(퇴사일<>0, ROUNDDOWN((퇴사일-입사일)/365), ROUNDDOWN((Today()-입사일)/365)) 이 답입니다.
MS엑셀과 같이 IF함수를 사용하는데 나이와는 달리 ROUNDDOWN함수를 사용합니다. 즉.. 소수점 이하는 버리는 함수로 1년을 꽉채워 근무해야 근속년수를 올려주겠다는 의미입니다. ^^
이런식으로 직원데이터를 관리하면 매번 근속년수를 계산하지 않아도 자동으로 계산이 되니 편할듯 합니다.
아래는 위와 같은 방법으로 완성한 시트 화면입니다.ㅋ
물론 IF함수를 사용했습니다.. IF(퇴사일<>0, "경력", "재직") 이런식으로..
그럼 다음에도 스프레드시트의 유용한 함수를 발견하면 또 글을 올리도록 하겠습니다~
'공부방 > 스프레드시트' 카테고리의 다른 글
| 주민번호로 성별확인(스프레드시트, 엑셀) (0) | 2009.08.07 |
|---|---|
| 엑셀로 주민번호 뒷자리 별표로 만들기(REPLACE함수) (2) | 2009.08.05 |