Y&S 두 여자의 말랑말랑 생활공감

일련번호는  1, 2, 3,....의 숫자를 의미한다.

일련번호는 주로 엑셀에 데이터가 많을 경우 데이터의 순서를 알기 위해 사용되므로 직접 입력하는 것보다는 자동으로 입력되는 것이 편리한 것은 당연~

난 주로 쇼핑몰 관리 엑셀 파일에서 주문번호에 이 일련번호를 사용하는데 자동으로 입력되도록 함수식을 만들어서 사용했더니 세상 편하다 ㅋ

일련번호가 자동으로 매겨질 때 필수 함수는 ROW 함수다.

ROW 함수는 '=ROW(A3)'처럼 사용되는데 참조된 A3셀의 행번호인 '3'을 결과로 보여주는 행번호 함수다. 하지만, ROW함수만 사용하면 아래처럼 미리 일련번호가 다 매겨져서 한눈에 끝 데이터를 찾기 힘들고 지저분해진다.

<ROW> 함수만 사용한 일련번호

'이름'과 같은 데이터가 있는 경우에만 자동으로 일련번호가 매겨지게 하려면 IF 함수를 같이 사용하면 된다. IF 함수는 '이름'에 데이터가 있는 경우에는 일련번호를 표시하고, 아닐 경우에는 공백으로 표시하기 위해 사용한다.

<IF, ROW> 함수를 사용했을 경우의 일련번호

실습해보기

완성파일.xlsx

1. B3 셀에 '=IF(C3<>"",ROW(C3)-2,"")' 함수식을 입력한다.

=ROW(C3)-2

위의 식을 [B3] 셀에 입력하면 'ROW(C3)'에서 3이 구해지고 2를 빼서 1이 입력된다.

즉, 첫 데이터가 시작하는 3행부터 아래로 1, 2, 3, 4....와 같은 일련번호가 표시되도록 하는 식이다.

위의 식에 IF 함수를 추가해서 식을 완성해보겠다.

=IF(C3<>"",ROW(C3)-2,"")

IF 함수는 'IF(조건, 참, 거짓)'처럼 세개의 인수로 구성되어 있다.

첫번째 인수인 조건이 참이면 두번째 값이 표시되고, 거짓이면 세번째 값이 표시되는 함수다.

여기에서는 조건이 'C3<>""'인데, C3셀 즉, 이름이 ""(공백)이 <>(아니면)...이라는 조건이다.

이 조건으로 인해 이름이 공백일 경우와 공백이 아닐 경우에 다른 결과를 표시할 수 있게 된다.

공백이 아닐 경우 표시할 두번째 인수에는 위의 ROW 함수식을 넣었기 때문에 일련번호가 표시된다.

하지만, 공백일 경우 즉, 조건이 거짓일 경우에는 ""(공백)이 표시되는 것이다.

2. B3 셀에 입력한 함수식을 아래로 드래그하여 자동채우기한다.

드래그를 못할 정도로 데이터가 무진장 많을 경우에는 채우기 핸들에서 더블 클릭해도 된다.

3. 식을 모두 복사해도 데이터(이름)이 없기 때문에 아무것도 표시가 안된다.

4. 하지만, 이름을 입력하면 일련번호가 자동으로 매겨지게 된다.

 

 

 

 

 

 

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

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

 

 

이 예제에서는 데이터 유효성 검사, 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. 아래와 같이 인쇄된다.