오랜만에 스프레드시트에 대해서 이야기할까 합니다.

주제는 스프레드시트를 이용해서 여러가지 날짜 계산을 하는 방법으로 주민번호를 이용해서 나이계산하고, 입사일자를 통해서 근속년수를 계산하고자 합니다.

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, "경력", "재직") 이런식으로..

그럼 다음에도 스프레드시트의 유용한 함수를 발견하면 또 글을 올리도록 하겠습니다~

오늘도 스프레드시트와 관련된 유용한 함수를 한가지 소개해 드립니다. ^^

지난번에는 주민번호를 공개용으로(뒷자리 7자리를 별표로 표기) 만드는 법을 확인했었는데..

오늘은 주민번호로 성별을 판단할 수 있는 함수 입니다.

우리나라 주민번호는 뒷자리 7자리 중에서 첫번째 오는 숫자가 "1"이면 남자, "2"면 여자로 구분이 됩니다.

물론 2000년 이후 출생자는 "3"이 남자, "4"가 여자로 되어 있죠.. ^^


위 화면처럼 주민번호를 보고 성별을 입력하는 함수를 만들기 위해서는..

먼저 주민번호의 뒷자리 7번째 숫자를 가져오는 함수가 필요합니다.


저는 MID함수를 사용하겠습니다.

MID함수는 텍스트중에서 원하는 위치의 글자를 원하는만큼 가져올수 있는 함수입니다.

즉 MID(원문텍스트, 가져오고자 하는 글자의 위치, 가져오고 싶은 글자 수) 의 형태로 사용합니다.

주민번호 뒷자리 7자리의 첫번째 숫자만 가져오면 되므로 MID(C2, 8, 1) 이라고 하면 되겠죠? ^^

다음으로 주민번호에서 성별을 나타내는 숫자를 가져왔으니.. 해당숫자로 성별을 입력하면 되는데요..

IF함수를 사용해서 IF(MID(C2, 8, 1)==1,"남","여") 이렇게 할 수도 있지만..

일단 2000년 이후 출생자인 "3", "4"를 고려하려면 여러개의 IF문을 사용해야하므로 수식이 복잡해집니다.

그래서 저는 CHOOSE 함수를 사용하겠습니다.

CHOOSE함수는 CHOOSE(숫자, 값1, 값2, 값3,...,값30) 의 형태로 사용하고..

처음 나오는 숫자에 해당하는 위치에 있는 값을 출력합니다.

따라서 CHOOSE(MID(C2, 8, 1), "남", "여", "남", "여") 로 해주면..

1 또는 3이 나오는 경우 "남" 이고, 2 또는 4 가 나오면 "여" 로 표기가 됩니다.

IF함수를 사용하는 것보단 훨씬 편리하죠? ^^


오늘은 엑셀의 유용한 함수를 소개해드릴까 합니다. ^^

보통 인적사항을 엑셀로 정리하면서 주민번호도 함께 관리를 하지만..

주민번호의 경우 매우 민감한 개인정보이므로 실수로 공개되는일이 없어야 합니다.

위와 같은 인적정보가 있다고 한다면..

공개용 주민번호를 만드는 함수는 =REPLACE(TEXT(C2,"??????-???????"),8,7,"*******") 를 입력하시면 됩니다.

REPLACE함수는 (문자, 변경할문자가 시작하는 위치, 변경할 문자갯수, 변경할 문자) 의 구조로 입력합니다.

따라서 TEXT함수로 주민번호 형식에 맞는 서식을 만들고 주민번호 뒷자리가 시작하는 8번째 글자부터 뒷자리 7자리를 모두 "*"로 만들어주는 것입니다.

하지만 위 방법으로 구글문서도구의 스프레드시트에서는 결과가 정상적으로 보이지 않습니다.


구글문서도구에서는 TEXT함수에서 "??????-???????"를 인식하지 못하기 때문입니다.

좀더 정확히 말한다면 서식에 주민번호와 같은 "??????-???????"의 서식이 없기 때문입니다.

따라서 구글문서도구를 사용하신다면 =REPLACE(C2,8,7,"*******") 를 사용하셔야 합니다.



이상으로 엑셀의 REPLACE함수의 활용법을 주민번호 뒷자리 감추기라는 유용한 팁을 통해 설명드렸습니다.

요즘 스프레드시트의 강력한 기능(정확히 말하면 함수겠죠?)에 빠져 대부분의 문서를 스프레드시트로 만들고 있습니다. ^^

앞으로 스프레드시트의 달인이 될때까지 열심히 달려보겠습니다;;ㅋ



  1. 코난 2011.01.18 18:05 신고

    덕분에 참고가 많이 됐습니다. 제가 원하는 기능은 아이디 뒷부분 두글자를 *표 처리를 하려고 하는데 아이디마다 글자수가 달라서 적용이 가능할까요? 혹시 하시면 답변 부탁드립니다.
    ---------------------------------------
    (자문자답)기능 찾았습니다 ^^
    =REPLACE(C3,LEN(C3)-1,2,"**")

+ Recent posts

티스토리 툴바