[함수]VLOOKUP 함수 이용하여 경력증명서 발급하기

찾기/참조함수 VLOOKUP 기본익히기
테이블이나 범위에서 행별로 항목을 찾아야할때 사용하는 함수

직원동아리 회비 입금현황에서, 개인별 자료 추출하기

직원 동아리 회비 입금 현황표가 있다. 전체 표 중에서 개인별 자료를 추출하고 싶다면, 성명을 찾은뒤, 성별, 부서, 직위, 납입액을 확인하게 된다.

내가 원하는 성명을 입력하면 성별, 부서, 직위, 납입액이 자동으로 나올수 있는 방법이 없을까???
인원이 적다면 모를까 명단이 많으면 찾는데도 시간이 걸릴테니말이다.

그래서 이때 사용할수 있는 함수는 vlookup

VLOOKUP 함수 사용방법은
=vlookup(찾으려는값,찾을표범위,구하는열번호,방법)
1. 찾으려는 값 : 내가 표에서 찾고자하는 데이터가 된다, 값이나, 셀주소, 텍스트를 입력한다.
2. 찾을표범위 : 데이터를 검색하고 추출하려는 표를 선택한다.
3. 내가 구하고자 하는것이 추출하려는 표에서 몇번째 열에 있는지 수치를 입력한다.
4. 방법은 0(false) 또는 1(true)를 선택한다. 
정확하게 일치하는 값을 찾을때는 0(false)를, 비슷하게 일치하는값을 찾을때는 1(ture)를 입력한다.

=VLOOKUP(H6,B5:F22,2,0)
H6 : H6셀이 적혀져있는 성명에 대한 값을 찾아야 하기에 성명 민정석 셀을 선택한다.
B5:F22 : 민정석 이름에 대한 데이터를 찾기 위해 회비입금현황표를 범위지정한다. 표는 A열부터 시작하지만, 내가 찾고자하는것이 성명이므로, 번호를 제외하고 성명부터 범위를 잡아줘야한다.
2 : 회비입금현황 표에서 범위 잡은것중에 지금 구하고자하는 성별이 2번째에 있기에 2를 입력한다.
0 : 민정석이라는 이름을 표에서 정확하게 일치하는 데이터를 찾고자 0을 입력한다.

평균점수에 따른 평가를 구하고자 할때, 평가표를 참조하여 평가를 구해본다.

=VLOOKUP(J4,$N$8:$O$12,2,1)
J4 : 평균점수에 따라 평가를 구하기위해 해당 평균점수를 선택한다
$N$8:$O$12 : 평가표에 있는 데이터를 참조하여 구하기 위해 표범위를 선택한다. 첫번째 계산식을 이용하여 나머지들고 계산하기 위해, 평가표범위는 절대주소로 만들어준다(F4이용)
2 : 평가표에서  구하고자 하는 평가가 2번째 열에 속하기에 2 입력
1 : 구한 평균점수가 87점, 이는 점수에 적혀있지 않는 숫자이고, 80~89 사이에 속하는 숫자이기에, 0을 쓰면 정확하게 일치하는 값을 찾기에 오류가 생긴다. 1을 써서 비슷하게 일치하는 값을 찾아야 정확한 결과값이 나온다.

이렇게 VLOOKUP 함수를 이용하여 원하는 데이터를 쉽고 빠르게 찾아서 사용할수 있다

 

VLOOKUP 함수를 이용하여 경력증명서 발급하기

직원명부와, 경력증명서 2개의 파일 준비

각각 다른시트에 자료를 준비하고, 필요한 데이터를 추출해오자

작업하기 편하기 위해, 이름과, 직원명부 전체 범위에 대해 이름정의를 해두자
-  제목을 제외한 이름범위 블럭 지정 후, 왼쪽 위 '이름상자'를 클릭 후 '이름'이라고 입력 후 엔터
-  제목을 제외한 직원명부 전체 블럭 지정 후, 왼쪽 위 '이름상자'를 클릭 후 '직원명부'라고 입력 후 엔터

경력증명서 성명 자리는 유효성검사를 이용하여 '이름'으로 이름정의한 데이터가 선택될 수 있도록 지정
- 성명자리 클릭 후 [데이터]-[데이터유효성검사]-[설정]-제한대상 [목록] - 원본 자리에 '=이름' 이라고 쓰고 확인한다
성명자리에 목록단추가 생기고, 목록을 선택하여도 되고, 직접 이름을 입력하여도 된다.

이제 생년월일, 주소, 근무부서 등.. 해당하는 사람의 자료가 추출될수 있도록 vlookup 함수를 이용하여 설정한다

=VLOOKUP(D10,직원명부,3,1)
D10 : 성명에 해당하는 생년월일을 구하고자, 성명 클릭
직원명부 : 직원명부시트의 전체 표범위를 지정한다. 미리 이름정의를 해놨기때문에 셀주소대신 이름이 뜬다.(범위지정하여도 되고, 이름을 직접 써도 된다)
3 : 생년월일이 직원명부 표에서 3번째 위치하고 있어서 열번호 3을 쓴다
0 : 정확한 데이터를 찾기 위해 0 입력

같은 방법으로, 주소, 근무부서, 직위, 담당업무 를 각각 적용한다. 식은 같으므로, 식에서 구하는 열번호만 변경해주면 된다.

일일이 자료를 찾아서 입력해도 되지만, 한번 vlookup 함수를 이용하여 만들어 놓으면 다음부턴 쉽고 빠르게 경력증명서 발급 가능!!!

아래 파일을 참조하여, 데이터만 바꿔서 사용해도 된다.

경력증명서.xlsx

0.03MB