엑셀에서 함수와 데이터 유효성 검사 등을 이용하여 재직증명서 등의 양식을 만들면,

이름, 주민등록번호 검색으로 양식의 내용이 자동으로 채워져 일의 능률을 올릴 수 있다.

 

 

이 예제에서는 데이터 유효성 검사, LOOKUP 함수, DATEDIF 함수가 사용되니 잘 모른다면 미리 함 보자~!

엑셀 데이터 유효성 검사 실습 (조건부 서식 자동으로 지정하기) by Y

엑셀 함수 4 (찾기/참조 함수) by Y

엑셀 함수 20 - DATEDIF 함수 (경과일수, 근속일수 구하기)

 

 

유의할 점

1. LOOKUP 함수는 두 번째 인수에 사용될 범위는 반드시 오름차순으로 정렬되어 있어야 한다.

이 예제에서는 주민등록번호로 데이터를 찾고 있으므로 주민등록번호를 오름차순으로 정렬해야한다.

2. 본인이 가지고 있는 데이터로 재직증명서를 만들 경우 [이름 지정하기] 작업을 꼭 다시 해야한다.

만약 준비 파일을 수정하고 싶은 경우 [수식]-[이름 관리자] 명령을 실행하여 모든 이름을 제거한 후 다시 이름을 지정해야 한다.

 

이미 지정된 이름을 제거하는 방법을 더 자세히 알고 싶다면~!  엑셀 이름정의, 이름삭제, 이름 활용 방법 by Y

 

 

 

 

 

준비 파일과 완성 파일

 

  준비 파일.xlsx  완성 파일.xlsx

 

'준비 파일'을 열어보면 [재직증명서] 시트와 [직원명단] 시트의 2개의 시트가 있는데,

[재직증명서] 시트는 재직증명서 표가 2페이지에 인쇄되도록 만들어져 있고,

[직원명단] 시트에는 직원들의 정보가 입력되어 있다.

 

 

 

 

 

 

1. 주민등록번호를 오름차순으로 정렬하기

 

 

 

1. [직원명단] 시트의 [C2] 셀을 선택한 후 [데이터]-[정렬 및 필터]-[오름차순] 명령을 클릭한다.

첫 번째 데이터인 [C3] 셀을 선택한 후 작업해도 된다.

 

 

 

 

2. 주민등록번호가 '작은수->큰수'로 정렬된다.

 

 

 

 

 

 

2. 이름 지정하기

 

 

 

[재직증명서] 시트에서 [직원명단] 시트의 내용을 참조할 때 'B3:B34'가 아닌 '성명'처럼 문자 방식으로 참조하면 매우 직관적으로 식을 작성할 수 있게 되어 이해하기 편하고, 여러 사람과 공동 작업을 할 경우 관리가 편리해진다.

아래와 같이 [직원명단] 시트의 성명, 주민등록번호, 소속, 직위, 주소, 입사일 범위의 이름을 표의 제목으로 지정해보자.

 

 

 

 

1. [B2:G34] 범위를 지정한 후 [수식]-[정의된 이름]-[선택 영역에서 만들기] 명령을 클릭한다.

 

 

 

 

2. [선택 영역에서 이름 만들기] 대화 상자가 나타나면 '첫 행'을 선택한 후 [확인] 버튼을 클릭한다.

 

 

 

 

 

 

 

3. 주민등록번호를 선택할 수 있도록 유효성 검사 적용하기

 

 

 

1. [재직증명서] 시트의 [D2] 셀에서 주민등록번호를 선택할 수 있도록 데이터 유효성 검사를 적용해보자.

[D2] 셀을 선택한 후 [데이터]-[데이터 도구]-[데이터 유효성 검사] 명령을 클릭한다.

 

 

 

 

2. [데이터 유효성] 대화 상자가 나타나면 '제한 대상'을 '목록'으로 변경한 후 '원본'에 '=주민등록번호'를 입력한다.

'주민등록번호'는 [직원명단] 시트의 '주민등록번호' 범위를 의미한다.

 

 

 

 

3. 이제 아래와 같이 목록 버튼이 생성되어 버튼을 클릭하면 주민등록번호를 쉽게 선택할 수 있게 된다.  

 

 

 

 

 

 

 

4. LOOKUP 함수로 해당 직원 정보 가져오기/

DATEDIF 함수로 재직기간(근속일수) 구하기

 

 

 

1. 재직증명서 양식의 성명, 주민등록번호, 주소, 소속, 직급을 수식을 이용하여 구한다. 

 

 

=LOOKUP(D2,주민등록번호,성명) 식의 의미는?

LOOKUP 함수는 첫 번째 인수값을 두 번째 인수인 범위에서 찾아 세 번째 인수 범위 중에서 같은 행에 위치한 값을 찾아주는 함수다. 즉, 주민등록번호 범위에서 D2값을 찾은 후 같은 행에 위치한 성명을 찾아준다.

 

 

 

2. 재직기간을 구하기 위해 먼저 입사일과 오늘 날짜를 수식으로 구한다.

 

 

=LOOKUP(D2,주민등록번호,입사일) => 입사일을 구한다.

=TODAY() => 오늘 날짜를 구한다.

 

 

 

3. 입사일의 표시형식을 'YYYY"년" M"일" D"일부터"'로 지정하여 '○○○○년 ○월 ○일부터'로 표시되도록 표시 형식을 수정해준다.

 

 

 

 

4. 오늘 날짜의 표시형식을 'YYYY"년" M"일" D"일까지"'로 지정하여 '○○○○년 ○월 ○일까지'로 표시되도록 표시 형식을 수정해준다.

 

 

 

 

5. DATEDIF 함수를 이용하여 입사일로부터 오늘 날짜까지의 기간을 구하여 재직기간을 구해준다. 

 

 

=DATEDIF(D10,D11,"Y")&"년 "&DATEDIF(D10,D11,"YM")&"개월 "&DATEDIF(D10,D11,"MD")&"일" 식의 의미는?

입사일(D10)로부터 오늘 날짜(D11)까지의 기간 년수(Y)에 '년 '을 붙이고.

입사일(D10)로부터 오늘 날짜(D11)까지의 1년 미만의 개월(YM)에 '개월 '을 붙이고.

입사일(D10)로부터 오늘 날짜(D11)까지의 1개월 미만의 일수(MD)에 '일'을 붙였다.

 

 

 

 

 

 

재직증명서 인쇄하기

 

 

 

1. 재직증명서를 작성할 직원의 주민등록번호를 선택하면, 재직증명서의 직원 정보가 자동으로 채워지게 된다.

'용도'는 따로 입력해준다.

 

 

 

 

2. [재직증명서] 시트에서 재직증명서 표만 따로 2페이지로 지정되어 있으니 인쇄할 경우 2페이지만 인쇄하도록 한다.

 

 

 

 

3. 아래와 같이 인쇄된다. 

 

 

 

 

 
 
 
 

 

Posted by 두여자 Y&S

댓글을 달아 주세요

  1. 비밀댓글입니다

    2014.07.18 17:03 [ ADDR : EDIT/ DEL : REPLY ]
  2. kim

    처음볼때가 엊그제 같은데 마지막 엑셀블로그까지 다봤네요. 쉽게 설명 해주셔서 더 이해가 잘되었습니다. 두여자님 복받으세요~ 전 다시한번 복습하겠습니다.

    2014.09.17 15:06 신고 [ ADDR : EDIT/ DEL : REPLY ]
    • 열심히 공부하시는 모습에서 열정이 느껴집니다.
      그렇게 공부하시면 곧 엑셀 다 마스터하실 듯~화이팅이요^^

      2014.09.18 10:05 신고 [ ADDR : EDIT/ DEL ]
  3. 고마워요

    이해가 잘 되게 작성해 주셔서 감사해요

    2014.09.18 11:14 신고 [ ADDR : EDIT/ DEL : REPLY ]