오랫만에 엑셀 수식계산 방법에 대한

포스팅이다.


엑셀의 용도가 계산도 있지만 특정 데이터로

명부 관리 혹은 다양한 로우 데이터 등

다양한 목적으로 사용하곤 하는데 오늘은 그 중에서

제일 많이 사용할 듯 하면서도 해볼려면

까먹게 되는 생년월일 만나이 계산 방법에 대한 포스팅이다.



직장인들이 제일 많이 사용하는 스프레드시트 중 

하나인 엑셀 프로그램에서 주로 많이 사용하는 게

인력관리 혹은 명부 관리 목적으로도

사용하는 사람들이 많을 것이다.


우선은 오늘은 생년월일오늘 날짜를 이용하여 

만나이 계산 하는 방법을 알려드리도록 하겠다.


이름, 생년월일, 오늘 날짜, 만나이

요 순서대로 되어 있는 예제 데이터를 만들어봤다.







생년월일 데이터의 경우는 기본적으로 

데이터의 자릿수똑같다는 가정하에

(6자리 혹은 8자리의 일정한 값)

생년월일 데이터를 모두 블록으로 선택한 직후

홈 -> 데이터값 변경을 위한 항목을 선택하고

일반으로 선택을 해주도록 한다.


텍스트나 숫자 혹은 이상한 값으로 

인식이 되면 수식계산 도중에 에러가 발생할 수 

있기 때문에 미리 데이터를 변환시켜준다.



그렇게 일반 서식 데이터로 바꿔준 다음에는 

데이터 -> 텍스트 나누기를 클릭하도록 한다.

위에서 얘기했던 것 한번 더 얘기하지만

생년월일 값은 무조건 일정한 자릿수를 유지해야 한다.

그래야 날짜 변환할 때 에러가 발생하지 않는다.







텍스트 나누기 창이 뜬다면 너비가 일정함을 눌러준다.

그 다음에는 그냥 아무 조정도 없이 다음 버튼을 클릭 한다.

그 다음에는 3번째 항목 날짜 항목을 클릭하되

표시 방식은 본인의 취향에 맞게 선택을 하도록 한다.


그 다음에 적용 버튼을 클릭하면 아래와 같다.



이렇게 데이터의 생년월일 값날짜 서식 값으로

정상적으로 전환됨과 동시에 

일정한 간격으로 날짜 값이 배치됐음을 알 수 있다.


단, 여기서 8자리의 생년월일(예 19840125)은 괜찮은데

6자리의 생년월일은 약간의 에러가 발생할 수 있는데

이 점에 대해서는 아래에서 설명해주도록 하겠다.



이제 본격적으로 만나이 계산을 위한 수식을 

작성하도록 하겠다. 

조건은 간단하다. 

계산하고 싶은 날짜 기준이 있어야 하기에

옆 셀에는 오늘 날짜라는 항목을 만들어서 넣어줬다.

계산 수식은 간단하다.


=DATEDIF(생년월일의 셀주소,기준 날짜의 셀주소,"Y")


위에서는 1번 항목의 심영의 만 나이 계산을 위한 수식이다.

=DATEDIF(C2,D2,"Y")




그렇게 계산식을 넣은 후 나머지 항목도

똑같이 조건에 맞게 계산식을 넣으면

위와 같이 2020년 10월 14일 기준으로 된 

만나이가 정확하게 계산됐음을 알 수 있다.






여기서 위에서도 언급한 의문점이 있을 것이다.

왜 8자리로 안쓰고 6자리로 썼을때 어떤 문제가 발생하느냐..

6자리는 2000년대 출신인지 1900년대 출신인지

구분이 안될 수도 있다는 점이다.


가급적이면 그래서 8자리를 권장하지만 여기서 우리는

그래도 6자리로 된 데이터가 있는데 노가다로

언제 그 데이터를 다 전환하냐고 아우성일 것이다.


그래서 기준을 한번 보여주도록 하겠다.


이렇게 29년대의 6자리 생년월일까지는 2000년대로 인식되며,

30년대 이후의 6자리 생년월일부터는 1900년대로 인식이 된다는 점이다.


이런 점 때문에 거의 드물긴 하지만

1920년 9월 3일 생의 만나이를 계산하려고 하면

2000년대 생일로 인식되어 자칫하면 10살로 계산이

될 수도 있다는 점이다.


이러한 점은 분명히 주의하면서 엑셀에서

만나이 생년월을 계산을 한다면

어렵지 않게 만나이 계산 결과값 도출에

큰 도움이 될 것이다.



블로그 이미지

Sweety & Cool 미네스!

일본여행/맛집/마이너컬쳐/정보/쇼핑정보 Contact : luna8606@naver.com

댓글을 달아 주세요

직장에서 매번 자주 사용하는 엑셀.

하지만 매번 하면 할수록 정말 다양한 요구가 많이

생기기 마련이다. 


특히 엑셀 하나만으로 데이터 베이스각종 장부관리를 목적으로

엑셀 파일을 만들어야 하는 사람들의 경우는

다른 사람들에게 엑셀파일을 넘겨서 잠시 맡겨놨다가

특정 자료값만 입력하게끔 만들어야 하는데

꼭 자기 스타일대로 바꿔서 해서 데이터 값들을 

다르게 되어서 정리안되는 경우를 가끔 볼 수 있을 것이다.


오늘은 그런 번거로움을 타파하게끔 해서

유효성 검사펼침 목록 값을 드롭다운

엑셀에 구현하는 방법을 알려주겠다.


엑셀 드롭다운 펼침 목록 만들어서 유효성 검사 하기.



우선 예제를 한번 보도록 한다.

다양한 금액에 대해서 중구난방식으로

제멋대로 적혀진 적요값을 몇가지의 값 만으로

제한을 시켜서 만들고 싶다고 생각을 하도록 하자.


지출금, 지출자금 같은 의미이지만 통일이 필요하다.

용도금과 용도자금도 같은 의미같지만 통일을 시켜야 할 듯 하다.


이렇게 본인의 엑셀 값에서 특정 값을 제한시켜서 

펼침 목록 드롭다으로 만들어야 할 이유가 다양할 것이다.


그럼 드롭다운 메뉴 만드는 방법으로 넘어가 보자.





같은 시트의 조금 먼 곳의 행과 열로 이동을 하자.

그 이유는 조금 더 깔끔하게 자료 처리를 하기 위한 목적이다.


필자는 AA열에 펼침 목록 드롭다운을 만들고

유효성 검사 값을 입력하려고 한다.


약 4가지의 값으로 제한을 시켜서 

다른 값은 입력이 안되게끔 막아 놓으려고 한다.


본인이 제한 시켜놓을

한 열로 입력한 후에

데이터 -> 데이터 유효성 검사 버튼을 클릭한다.




그 다음 데이터 유효성 창이 뜬다면

제한 대상에서 목록을 클릭하자.

그 다음 원본이라는 항목이 새로 생기는데

표 모양의 버튼을 클릭한다.




표 모양의 버튼 클릭하면

위와 같이 창이 작아지는데

그 때 본인이 원하는 값한줄로 쭉 드래그를 한 후에

다시 표 버튼을 눌러주도록 한다.


그 다음 창이 나오면 확인을 눌러주면 끝이다.



그러면 데이터 유효성 확인 버튼을 누르고 난 후에

본인이 설정한 데이터 값의 첫번째 위치펼침목록 드롭다운

만들어져 있을 것이다.


그 값을 복사를 한 후 본인이 적용하고 싶은

셀에 복사해서 붙여넣으면 끝이다.

붙여넣고 드래그로 쭉 끌어 당겨서 

값을 집어넣어도 된다.




이렇게 한 셀에만 시범적으로 적용을 해 보았다.

만약 다른 셀에도 적용을 하고 싶다면 

역시 값을 복사한 후에 붙여넣기만 하면

드롭다운 펼침목록 속성 값이 그대로 복사되어서

붙여넣기가 가능해지기 때문에  쉽게 될 것이다.


생각보다 쉬운데 몰라서 못하는 분들이

굉장히 많은 내용 중에 하나일 것이다.


사무작업 경리 총무 분야에서

굉장히 많이 사용하는 엑셀.


조금 더 효율적이고 통일화 된 방법을

이용하여 능력 좋게 사용하도록 하자.






블로그 이미지

Sweety & Cool 미네스!

일본여행/맛집/마이너컬쳐/정보/쇼핑정보 Contact : luna8606@naver.com

댓글을 달아 주세요

직장인의 필수 프로그램 중 하나인 엑셀

이 엑셀 하나로 문서도 만들 뿐만 아니라

다양한 통계 및 자료 정리

심지어는 각종 글자형 데이터 베이스 및 과거 이력 관리에도

탁월한 프로그램이니 어느 회사에서도 빼놓지 않는 프로그램 중 하나이다.


오늘은 지난번에 이어서 알아 두면 좋은 엑셀 단축키를 소개하려고 한다.


인접 셀과 같은 값을 입력하고 싶다면?




Ctrl + R(좌측의 셀값과 동일한 값)

빨간색으로 표시된 셀을 보면 좌측의 셀값을 우측에 똑같이 하고 싶을때

해당 셀에 단축키를 입력하면 된다.


Ctrl + D (윗쪽의 셀값과 동일한 값)

파란색으로 표시된 셀을 보면 윗쪽의 셀값을 아랫쪽에

똑같이 하고 싶을때 단축키를 누르면 된다.


보통 특정 셀의 값을 입력하다 보면 반복되는 값이 

자주 나오는 경우가 다반사이다.

이럴때 사용하면 조금이라도 더 빠른 작업이 가능하다.


한꺼번에 여러가지 셀에 같은 값을 입력하고 싶다면?


Ctrl + Enter

우선은 본인이 입력을 원하는 셀을 여러개 선택하도록 한다.

그 다음에 본인이 원하는 값을 입력을 한 후 

컨트롤 + 엔터를 해보자

그러면 저렇게 다수의 셀에 똑같은 값이 

한번에 입력된다.


순서는 무조건 확실히 기억해둬야 한다.

1. 본인이 원하는 셀을 여러개 선택하도록 한다.

2. 그 상태에서 입력해야할 셀 값을 입력한다.

3. 입력을 한 후에는 Ctrl + ENTER를 한다.






셀 서식 빠르게 불러오기 


Ctrl + 1

가끔 셀의 값 범주나 셀테두리 및 배경등을

수정해야 하는데 일일히 메뉴 찾기 어렵다면

저 단축키를 눌러보자. 


마법같이 셀 서식이 나오면서 본인 입맛대로

옵션 조정이 가능하다.


특정 셀값을 찾아내기 


Ctrl + F

의외로 많이들 간과하고

잘 못써먹게 되는 부분 중 하나이다.

본인이 찾길 원하는 셀값을 찾으려고 할 때

이 단축키를 입력해 보도록 하자.


순식간에 나도 모르게 후다닥 찾아준다.




특정 셀값들의 내용을 다른 내용으로 바꿔주기



Ctrl + H


특정 셀의 특정 값들을

다수의 셀 혹은 전부 다른값으로

바꿔야 하는 경우가 종종 발생하곤 한다.

그렇다면 Ctrl + H을 누른 후 

모두 바꾸기, 혹은 바꾸기를 해보도록 하자.


모두 바꾸기는 한번에 특정 값을 찾아서 교체해주는 방식

바꾸기는 1개씩 순차적으로 교체해주는 방식이다.



만약 모두 바꾸기를 눌러서 교체를 한다면

위에 처럼 한꺼번에 일괄로 셀값이

바뀐것을 확인할 수 있다.






줄바꾸기


Alt + Enter


한 셀에 몇줄의 값으로

써야 하는경우가 간혹 발생하는데

그냥 엔터를 누르면 다음 셀로 넘어가는

경우가 굉장히 많다.


그럴때는 Alt + Enter를 눌러보자

마법같이 한 셀에서 줄바꿈이 이뤄짐을 

알 수 있을 것이다.


마지막 행과 열로 이동 빠르게 하기

Ctrl + → (마지막 행으로 이동)

Ctrl + ↓ (마지막 열로 이동)


가끔 한 시트에 다양한 값이 입력되어서

마지막 행과 열을 찾아야 하는 경우

왕왕 발생하곤 하는데 일일히 스크롤

하기에는 방대한 양의 경우가 간혹 발생한다.


그럴때는 걱정안해도 된다.

위의 단축키만 누르면 된다.

만약 마지막 행 혹은 열로 이동한 직후

다시 첫 행,열로 돌아오고 싶다면 

반대로 Ctrl + ← 혹은 ↑ 버튼을 누르자. 



이 외에도 다양한 단축키가 존재하지만

실상 다 외우기는 어려우며 실무 과정의 엑셀 작업에서

많이 사용할만한 단축키를 위주로

소개를 해봤다.


단축키가 없이도 잘하는 분들이 다수라고 하지만

그래도 모르는 것 보다는 외우는 편이

더 좋을 경우도 간혹 있곤 하다.


부디 엑셀 격파해서 직장에서

빠르고 효율적인 스마트형 인간이 되도록 하자.



블로그 이미지

Sweety & Cool 미네스!

일본여행/맛집/마이너컬쳐/정보/쇼핑정보 Contact : luna8606@naver.com

댓글을 달아 주세요

직장인에게 있어서 엑셀은

평소 학생때도 사용안하던 프로그램인데

이상스럽게 회사에만 다니면

평생 사용할 양의 엑셀을 회사에서 썼다고 해도

과언이 아니라고 싶을 정도이다.


그만큼 다양한 문서 관리 및 통계자료를 

목적으로 언제든지 빠지지 않고 잘 사용하는

오피스 프로그램인데 각 시트별로 셀값을 

공유하는 방법을 모르는 사람들이 많은거 같아서

알려드릴려고 한다.


- 다른시트에 있는 셀값을 작업중인 셀에 적용 방법



우선적으로 작업된 시트 "직원명단"

이렇게 둔 후에 

"직원별 실적" 시트직원명단 시트에 있는 셀값

적용시키려고 한다.



직원별 실적 시트 내에 직원 명단에 있는 이름 값

가져오게 해야하는데 일일히 타이핑을 하기에는

시간이 오래걸리기도 하고 자동화를 위해서는

다른 시트의 셀값을 가져오는 것이 효율적이라고 느낄 것이다.


본인이 넣고 싶어하는 셀클릭을 우선 하도록 한다.

필자는 B2 셀직원 명단의 이름을 가져오게 하고

하려고 할 것이다.





그 다음 더블클릭을 한 후에 "=" 값을 입력한다.

그 다음 그 상태에서 어떠한 것도 입력도 하지 말고

바로 다른 시트 "직원명단" 시트를 클릭하도록 한다.



그 다음 본인이 가져오고 싶은 이름값에 클릭을 한 후

엔터만 하면 된다.

필자는 A2의 셀값 가져오고 싶기 때문

A2를 클릭한 후 엔터를 하였다.



그러면 결과를 보기 위해서 "직원별 실적" 시트로 이동을 한다.

그 시트에는 "직원 명단" 시트에 있었던 셀값

자동으로 들어간 것을 확인할 수 있다.




만약 이러한 방법도 번거롭다면 수식이 있다.


='가져오고싶은시트명'!셀주소


위의 수식과 대조를 해보자면


='직원 명단'!A2


이렇게 구성이 된다.


간단하지만 꼭 간과하게되는 다른 시트의 셀값 가져오기.

잊지 말고 기억해서 잘 사용하도록 하자.



블로그 이미지

Sweety & Cool 미네스!

일본여행/맛집/마이너컬쳐/정보/쇼핑정보 Contact : luna8606@naver.com

댓글을 달아 주세요