엑셀(excel) - 데이터 검색하여 값 불러오기(VLOOKUP 함수 활용)

 

이번엔 좀 더 상황을 부여해서 vlookup함수를 활용해보려고 한다.

 

당신이 회사의 인사담당자이고, 임직원의 교육훈련을 담당하고 있다고 가정해보자.

핵심 KPI는 임직원이 모두 필요한 학점을 이수하고 교육훈련을 성공적으로 마치는 것이라면, 연말이 되기 전에 각 직원의 이수여부와 미달학점을 알려주는 것이 필요할 것이다.

 

그러나 개인별 학점이수 현황이 민감한 정보는 아니지만 전체 공개할 필요는 없을 것 같다면, vlookup함수를 활용해 간단한 엑셀 프로그램을 만들어볼 수 있다.

 

파일 다운로드

find_values_with_vlookup(Q).xlsx

0.01MB

 

작업 시트 준비

지금 가지고 있는 정보는 각 임직원의 현재 취득학점 정보와 직급별 필요학점 정보이다.

현재 각 임직원의 취득학점 현황('data' sheet)

직급별 필요학점('Criteria' sheet)

마지막으로 검색으로 값을 불러올 수 있도록 보여주는 시트도 필요하다.

개인별 학점이수 현황('Search' sheet)

 

자 이제 뭘 만들거냐면, 사번을 입력하면 모든 정보가 불러와  현재 학점 이수 현황과 초과/미달 여부를 자동으로 보여주는 간단한 프로그램을 만들 예정이다.

 

구현

(1) 현재 각 임직원의 취득학점 현황과 직급별 현황 정보 결합하기

vlookup을 활용해 데이터를 병합해준다.

직급명을 기준으로 필수학점을 찾아내는 vlookup 수식을 작성한다.

앞서 vlookup 함수에 대해 간단하게 설명한 적이 있는데 참고하기 바란다.

2022.04.30 - [Excel] - 엑셀(excel) - VLOOKUP 함수 기본 사용법과 오류 정리

 

 

(2) 초과/미달 여부 기재하기

D열과 E열 차이가 초과/미달 학점이 된다.

취득학점 - 필수학점은 초과/미달 학점이 되는데, 플러스(+)면 초과가 되고 마이너스(-)면 미달이 된다.

즉 차이가 0이상이라면 이수완료라는 의미가 된다.

 

(3) 개인별 학점이수 현황 시트 채우기

 모든 준비는 끝났으니 이제 진짜 검색 시트를 만들어보자.

사번은 사용자의 입력을 받는다고 기획을 하여 배경 색을 달리 준다. 그리고 친절하게 '사번을 입력해주세요.'라는 말도 기입해준다. (항상 사용자 입장에서 다시 한 번 곱씹어 본다.)

그리고 당연히 나머지 값은 사용자 입력이나 수정하지 못하도록 시트를 잠궈야 한다. 

 

이제 사번을 기준으로 이름, 직급, 취득학점을 도출해보자.

C4셀을 기준으로 data 시트에서 각각 맞는 값을 불러오는 수식을 작성한다.

이렇게 함수를 짜두면 값이 있는 경우에는 이쁘게 나오겠지만, 아무것도 입력되지 않았을 때 혹은 존재하지 않는 사번을 입력했을 때 오류 메시지를 보게 된다. 이 역시 사용자 관점에서 좋지 않은 경험이기 때문에 iferror함수로 덧씌어준다.

 

다음은 오른쪽 '결과'창을 구현해본다.

여기는 이수/미이수 여부와 초과/미달 학점을 표시해볼 예정이다.

역시 vlookup 함수로 초과/미달 열에서 값을 가져오되, if함수를 덧붙여 0이상일 때 이수완료, 그렇지 않을 때 미이수라는 값을 반환하도록 수식을 작성한다.

 

=IFERROR(IF(VLOOKUP(C4,data!$A$1:$F$10,6,FALSE)>=0,"이수완료","미이수"),"")

 

마지막 행에는 초과/미달 학점을 기재하는 수식을 작성한다.

 

=IFERROR(IF(VLOOKUP(C4,data!$A$1:$F$10,6,FALSE)>=0

,"초과 학점은 "&VLOOKUP(C4,data!$A$1:$F$10,6,FALSE)&"점 입니다."

,"미달 학점은 "&ABS(VLOOKUP(C4,data!$A$1:$F$10,6,FALSE))&"점 입니다."),"")

 

복잡해보이지만 찬찬히 살펴보면,

0이상이면 초과학점 문구와 값(학점)을 반환하고 그렇지 않으면 절대값으로 변경한 미달학점 문구와 값(학점)을 반환하는 수식이다.

 

이렇게 해서 수식은 모두 완성되었다. 

마지막으로 C4셀을 제외한 나머지는 수정이 불가능하도록 시트를 잠궈준다.

마지막 작업은 C4셀을 잠금을 풀어두고 시트를 보호하는 것이다.

 

결과 화면

실제로 데이터를 넣어보자.

기대했던 대로 매우 잘 작동한다.

한 가지 더 개인 사번이 순차적으로 배열되어 있거나 잘 알려진 상황이라면, 임직원의 개인정보를 좀 더 결합해 보안을 강화할 수 있다. 예를 들면 생년월일까지 입력하여 두 값이 일치하면 값을 반환하도록 만들 수 있다.

 

만들어둔 파일은 아래 참고해주세요.

 

find_values_with_vlookup(A).xlsx

0.01MB

 

 

"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정애그이 수수료를 제공받습니다."