■ 목차
1. 엑셀 팁/질문 : 엑셀에서 만 나이를 구하는 함수를 알고 싶어요
2. 만 나이 구하기 - 원리 이해하기
3. 만 나이 구하기 - 생년월일 나이변환
4. 연령대 구하기
** 핵심 요약
1. 엑셀 팁/질문 : 엑셀에서 만 나이를 구하는 함수를 알고 싶어요
- 엑셀에서 만 나이를 구하는 함수, 수식이 있을 가요?
- 주민번호의 생년월일을 기준으로 만 나이는 어떻게 만들어야 할까요?
- 연령대별로 인원현황을 뽑고 싶은데 간단하게 만들 수 있는지요?
2. 만 나이 구하기 - 원리 이해하기
- 법적 나이가 만 나이 계산법으로 바뀌었습니다. 아직 음주 나이, 군대가는 나이 등은 조금 차이가 있지만
- 만 나이라고 하면 1년을 온전히 살아야 1살을 인정해 주는 나이계산법으로
- 태어난 날로부터 1년 후 생일이 되어야 1살이 되는 계산법입니다.
- 즉 생일이 되는 날 1살을 더 먹게 되는 계산법으로 생일이 언제 인지가 중요하게 되었습니다.
- 태어난 날이 2024년 01월 01일인 경우 다음 해 생일인 2025년 01월 01일 00시 00분부터 만 1살이 되게 됩니다.
- 이를 계산수식이나 엑셀의 계산함수로 설명하면 아래와 같습니다.
- 1)만 나이 계산 수식
. 생일이 지난 경우 : 년도 2 - 년도 1(=태어난 연도)
. 생일이 지나지 않은 경우 : 년도 2 - 년도 1 - 1
- 2)만 나이 함수 : Datedif 함수의 "Y" 옵션 사용
- Datedif 함수는 두 날짜사이의 전체 연도 수나 일자, 개월 수 등을 구할 수 있는 함수이고
- "Y" 옵션의 경우 Year라고 해서 두 날짜 사이에 포함된 전체 연도 수를 구할 수 있는 함수입니다.
- 온전하게 1년이 되는 년도를 구해주기 때문에 우리가 구하고자 하는 만 나이와 동일하게 사용이 가능합니다.
- 다만 날짜함수이기 때문에 시작날짜, 종료날짜 모두 날짜형식의 데이터를 입력해줘야 합니다.
- 3) 기타
. 주민번호 등의 생년월일에서 날짜 분리 함수 : Left, Mid, Right 함수
. 년/월/일의 데이터로 날짜 형식으로 만들어 주는 함수 : Date 함수
. 오늘 날짜를 생성해 주는 함수 : Today 함수
- 주민번호의 생년월일(031215)은 숫자 혹은 텍스트 형식의 데이터인 경우가 많습니다.
- Datedif 함수와 같이 날짜 함수를 정확하게 사용하기 위해서는 숫자/텍스트 형식의 데이터를 반드시 날짜형식의 데이터로 변경해줘야 합니다.
- 이경우 년(2024), 월(12), 일(11)의 숫자 데이터를 입력하여 온전한 날짜형식의 데이터를 만들어 주는 함수가 Date 함수입니다.
- 연월일(2024.12.11)의 텍스트 데이터에서 년(2024), 월(12), 일(11)를 분리하는 작업은 Left, Mid, Right 함수를 사용할 수 있습니다.
3. 만 나이 구하기 - 생년월일 나이변환
- 주민번호의 생년월일과 같은 데이터에서 년/월/일을 분리하고 날짜 데이터를 만드는 방법입니다.
- 먼저 Left, Mid, Right 함수를 사용하여 년/월/일을 분리하고
- Date 함수를 사용하여 날짜형식의 데이터 만들어 보도록 하겠습니다.
- 1) 주민번호에서 연도 분리하기
- 먼저 주민번호 체계를 조금 이해하고 계시면 좋을 것 같습니다.
- 주민번호는 년(2자리)+월(2자리)+일(1자리)로 생년월일이 구성되어 있고
- 주민번호의 뒷자리 첫 번째 숫자는 1900년도 생의 경우 1(남자), 2(여자)이고 2000년도 생의 경우 3(남자), 4(여자)로 되어 있습니다.
- 따라서 연도의 경우 뒷자리 첫 번째 숫자가 1, 2인 경우는 "19"+년(2자리) 형식으로 아닌 경우는 "20"+년(2자리) 형식으로 구성이 되어야 합니다.
- 적용 수식 : =IF( OR(RIGHT(C3,1)*1=1, RIGHT(C3,1)*1=2), "19", "20")&LEFT(C3,2)
- 수식이 조금 복잡해 보이지만 주민번호에서 남녀 성별을 구분하는 오른쪽 첫 번째 숫자가 1 혹은 2인 경우는 "19"을
- 아닌 경우 (3, 4인 경우)는 "20"을 주민번호의 연도에 해당하는 2자리 숫자에 붙여서 완전한 연도 4자리를 만드는 수식입니다.
- 여기서 주의하실 점은 "990130-1"이라는 데이터는 "텍스트"입니다. 그리고 여기서 추출된 숫자처럼 보이는 데이터도 텍스트입니다.
- 모두 비슷하게 보이지만 (숫자 1)과 (텍스트 1)은 서로 다른 값이고 엑셀은 다르게 인식을 합니다.
- 그래서 텍스트 함수를 사용하여 추출한 "숫자처럼 보이는" 텍스트는 항상 다른 숫자와 비교하기 위해서는 숫자 형식으로 변형이 필요하며
- 가장 간단한 숫자 형식변환 방법은 곱하기 숫자 1 ( * 1)를 해주는 방법이 있습니다.
- 엑셀이 알아서 잘해주면 안 되는지 하는 의문이 들기도 하겠지만 원래 컴퓨터가 0과 1로 이루어진 거라 이런 경우는 정말이지 꼼꼼하게 따지는 편입니다. ^^
- 2) 주민번호에서 월, 일 분리하기
- 적용 수식 (월 분리) : =MID(C3,3,2)
- 적용 수식 (일 분리) : =MID(C3,5,2)
- 일단 여기까지는 텍스트 함수인 Right, Left, Mid 함수에 대한 기본적인 사용법에 대한 이해가 필요합니다.
- 크게 어려운 함수는 아니기 때문에 자세한 사용법은 생략하도록 하겠습니다.
- 이렇게 우리는 날짜를 구성하는 년, 월, 일에 대한 데이터를 주민번호에서 모두 추출하게 됩니다.
- 3) 분리된 년, 월, 일 데이터로 날짜 만들기 (=생일 정보)
- 적용 수식 : =DATE(D3, E3, F3)
- 년, 월, 일 정보로 날짜를 만들어 주는 함수는 Date 함수입니다. 연, 월, 일에 해당하는 숫자를 인수로 입력해 주시면 됩니다.
- 날짜 데이터를 날짜함수 등을 활용하여 더하거나 빼 거나한 연산의 경우 가장 중요한 부분은 "데이터 형식"을 반드시 날짜 형식으로 준수하는 것입니다.
- 그리고 우리가 인식하는 날짜처럼 보이는 많은 것들은 엑셀이 날짜로 인식하지 못하고 단순히 텍스트로 인식하여
- 날짜 함수에서 종종 오류를 일으키곤 하기 때문에 반드시 날짜 함수로 연산을 하는 경우 반드시 날짜 형식을 입력해 주시기 바랍니다.
- 진짜 중요하고 날짜 함수 오류의 99%는 모두 여기에서 발생되는 문제입니다.
- 4)만 나이 구하기 - Datedif 함수, "Y" 옵션
- 적용 수식 : =DATEDIF(G3, TODAY(), "Y")
- Datedif 함수는 날짜와 날짜의 차이를 구해주는 함수(Date different)입니다.
- 옵션 "Y"의 경우 두 날짜에서 온전한 Year(연도)의 차이를 구해주기 때문에 만 나이 계산이 가능합니다.
- 참고로 Today() 함수는 현재, 오늘 날짜를 구해주는 함수로 아무런 인수 없이 사용할 수 있습니다,
- 그리고 혹시 만 나이 구하기 결괏값이 날짜 형식으로 보이는 경우 셀서식을 이용하여 숫자형식으로 변경해 주시기 바랍니다.
- 보통은 날짜 함수 등을 사용하게 되거나 날짜형식의 데이터가 셀 바로 옆에 있는 경우 엑셀이 자동으로 날짜 형식으로 데이터를 보여주는 경우가 있습니다.
- 아래 수식은 모든 수식을 한 개의 수식으로 합쳐서 표현한 소위 메가 포뮬러입니다.
- 하나의 수식으로 만드는 방법은 지금처럼 각각의 단계를 나누어서 수식으로 표현한 부분을 복사하여 하나의 수식에 붙여 넣기를 해주시면 됩니다.
- 복잡한 수식을 만드는 경우 단계별로 수식을 구현하고 마지막에 하나의 수식으로 합치는 방식으로 많이 작업을 하게 됩니다.
- 날짜 함수를 이용한 만 나이 등을 구하는 문제에서 가장 중요한 부분은 일단 먼저 생일에 해당하는 날짜 데이터를 확보하는 부분이고
- 날짜 데이터의 경우 조금 특이한 형식이기 때문에 우리가 인식하는 형식이 아닌 엑셀이 인식이 가능한 형식으로 작성이 되어야 합니다.
- 이 부분만 주의를 하신다면 날짜 형식의 데이터를 연산하는 것도 일반 숫자를 연산하는 거랑 크게 다르지 않기 때문에
- 오류 없이 쉽게 작업이 가능합니다.
4. 연령대 구하기
- 각각의 나이를 10대(10 ~ 19살), 20대(20 ~ 29살)와 같이 연령대로 분류해 보도록 하겠습니다.
- 이 경우 우리가 사용해야 할 함수로는 Rounddown 함수와 Countif 함수를 사용할 수 있으며
- Roundown 함수로 1의 자리를 버림 함으로써 연령대를 만들 수 있으며
- Countif 함수로 각각의 연령대에 인원이 몇 명인지를 구할 수 있게 됩니다.
- 1) 생년월일 날짜 형식으로 변환
- 만 나이 계산에서 날짜 수식 Datedif 함수를 사용하기 위해서는 먼저 날짜형식의 생년월일 데이터가 필요합니다.
- 아래와 같이 년도, 월, 일을 Left, Mid, Right 함수를 사용해서 분리해 주고
- Date 함수로 날짜형식의 데이터를 만들어 줍니다.
- 사용 수식 : =DATE(LEFT(C3,4), MID(C3,6,2), RIGHT(C3,2))
- 2)만 나이 구하기 - Datedif 함수
- 적용 수식 : =DATEDIF(D3, TODAY(), "Y")
- Datedif 함수를 사용하여 생년월일과 오늘날짜(Today 함수)를 인수로 "Y"의 연도 차이에 해당하는 만 나이를 구해 줍니다.
- 이때 Today함수는 Today()와 같이 사용하고 인수 없이 항상 오늘날짜를 날짜형식으로 만들어 주는 함수입니다.
- 생일과 오늘 날짜의 두 날짜 사이에 온전한 1년이 경과한 연도 ( = 만 나이)를 구해줘라는 수식이 되게 됩니다.
- 3) 연령대 구하기 - Rounddown 함수
- 연령대를 구하기 이해서 버림 함수인 Rounddown 함수를 사용하게 되는데
- Rounddown 함수로 1의 자리에 해당하는 숫자를 버림 함으로써 연령대(23 → 20)를 구할 수 있게 됩니다.
- 이때 1의 자리를 버리기 위해서 지정하는 인수는 "-1"로 보통은 소수점 몇 째 자리를 버릴 것인가를 많이 지정하지만
- 일, 십, 백의 자리를 버림 하기 위해서 지정하는 경우 -1, -2, -3과 같이 음수를 사용하게 됩니다.
- 사용 수식 : =ROUNDDOWN(E3,-1)
- 4) 연령대별 인원 구하기 - Countif 함수
- 마지막으로 연령대별 인원은 연령대를 기준으로 Countif 함수를 활용하여 구해보도록 하겠습니다.
- 사용 수식 : =COUNTIF($F$3:$F$9, B12)
- Countif 함수는 범위와 조건을 지정하게 되면 해당 범위에서 조건을 만족하는 해당 건들의 개수를 구할 수 있는 함수입니다.
- 주의하실 점은 수식을 복사하여 사용하기 위해서는 범위에 해당하는 셀주소는 절대참조($F$3:$F$9) 형식으로 F4 키를 사용해서 변경해서 사용해 주시기 바랍니다.
- 날짜 함수 (Year, month, day, Datedif 함수 등)을 사용하기 위해서는
- 반드시 엑셀이 인식이 가능한 날짜형식으로 데이터를 만들어 줘야 합니다.
- 입력은 "2020/12/12" 혹은 "2020-12-12"와 같은 방식으로 입력을 하게 되면 엑셀이 자동으로 날짜형식의 데이터를 취급을 하고
- 년월/일 각각의 숫자로 날짜 형식을 만들기 위해서는 Date 함수를 사용하시기를 추천드립니다.
- 그리고 날짜에서 년/월/일에 해당하는 숫자를 분리하고 싶은 경우는 Year, Month, Day 함수를 사용하시면 되고
- 날짜처럼 보이는 텍스트(2020.12.12, 201212) 형식의 경우 우선 년/월/일을 분리하고 Data 함수로 날짜형식으로 만드시기 바랍니다.
- 엑셀은 조금 독특하게 날짜, 시간 형식의 데이터를 관리하고 있기 때문에 우선 이에 대한 이해를 먼저 해주시고
- 날짜함수를 사용해야만 정확하게 우리가 원하는 결괏값을 얻을 수가 있습니다.
- 오늘도 수고 많으셨습니다. 즐거운 하루 보내시기 바랍니다.
** 핵심 요약 : 만 나이 계산- 주민번호, 생년월일 나이변환, 연령대 계산
1. 엑셀 팁/질문 : 엑셀에서 만 나이를 구하는 함수를 알고 싶어요
2. 만 나이 구하기 - 원리 이해하기
- 만 나이는 태어난 날로부터 1년 후 생일이 되어야 1살이 되는 계산법으로 생일이 언제인지가 중요하다.
- 1)만 나이 계산 수식
. 생일이 지난 경우 : 년도 2 - 년도 1(=태어난 연도)
. 생일이 지나지 않은 경우 : 년도 2 - 년도 1 - 1
- 2)만 나이 함수 : Datedif 함수의 "Y" 옵션 사용
. Datedif 함수의 "Y" 옵션을 사용할 수 있다.
- 3) 기타
. 주민번호 등의 생년월일에서 날짜 분리 함수 : Left, Mid, Right 함수
. 년/월/일의 데이터로 날짜 형식으로 만들어 주는 함수 : Date 함수
. 오늘 날짜를 생성해 주는 함수 : Today 함수
3. 만 나이 구하기 - 생년월일 나이변환
- 1) 주민번호에서 연도 분리하기
- 연도의 경우 뒷자리 첫 번째 숫자가 1, 2인 경우는 "19"+년(2자리) 형식으로 아닌 경우는 "20"+년(2자리) 형식으로 구성한다.
- 적용 수식 : =IF( OR(RIGHT(C3,1)*1=1, RIGHT(C3,1)*1=2), "19", "20")&LEFT(C3,2)
- 2) 주민번호에서 월, 일 분리하기
- 적용 수식 (월 분리) : =MID(C3,3,2)
- 적용 수식 (일 분리) : =MID(C3,5,2)
- 3) 분리된 년, 월, 일 데이터로 날짜 만들기 (=생일 정보)
- 적용 수식 : =DATE(D3, E3, F3)
- 4)만 나이 구하기 - Datedif 함수, "Y" 옵션
- 적용 수식 : =DATEDIF(G3, TODAY(), "Y")
4. 연령대 구하기
- Roundown 함수로 1의 자리를 버림 함으로써 연령대를 만들 수 있으며
- Countif 함수로 각각의 연령대에 인원이 몇 명인지를 구할 수 있다.
- 1) 생년월일 날짜 형식으로 변환
- 사용 수식 : =DATE(LEFT(C3,4), MID(C3,6,2), RIGHT(C3,2))
- 2)만 나이 구하기 - Datedif 함수
- 적용 수식 : =DATEDIF(D3, TODAY(), "Y")
- 3) 연령대 구하기 - Rounddown 함수
- 사용 수식 : =ROUNDDOWN(E3,-1)
- 4) 연령대별 인원 구하기 - Countif 함수
- 사용 수식 : =COUNTIF($F$3:$F$9, B12)
- 날짜 함수 (Year, month, day, Datedif 함수 등)을 사용하기 위해서는
- 반드시 엑셀이 인식이 가능한 날짜형식으로 데이터를 만들어 줘야 한다.
- 입력은 "2020/12/12" 혹은 "2020-12-12"와 같은 방식으로 입력을 하게 되면 엑셀이 자동으로 날짜형식의 데이터를 취급을 하고
- 년/월/일 각각의 숫자로 날짜 형식을 만들기 위해서는 Date 함수를 사용하시기를 추천한다.
- 날짜에서 년/월/일에 해당하는 숫자를 분리하고 싶은 경우는 Year, Month, Day 함수를 사용하면 되고
- 날짜처럼 보이는 텍스트(2020.12.12, 201212) 형식의 경우 우선 년/월/일을 분리하고 Data 함수로 날짜형식으로 만들 수 있다.
[세상의 모든 엑셀 (엑셀팁, 질문)] - 999. 세상의 모든 엑셀(엑셀팁, 질문) 목차
999. 세상의 모든 엑셀(엑셀팁, 질문) 목차
[세상의 모든 엑셀 (엑셀팁, 질문)] - 001. (엑셀 팁, 질문) 누적 합계 구하기 - 절대참조/상대참조, 빠른 분석 도구 [세상의 모든 엑셀 (엑셀팁, 질문)] - 002. (엑셀 팁, 질문) 날짜 칸 나누어 입력하기
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글