엑셀 함수 중에서 가장 많이 사용하는 함수는 Vlookup 함수입니다. 오늘은 Vlookup 함수에 대해 알아보겠습니다.
회사의 단합을 위해 가장 좋은 것은 운동회를 통해 단합을 꽤하는 것입니다. 직원 수는 30명으로 각 팀 5명씩 3팀으로 운동회 명부를 작성하려고 합니다. 함수는 Vlookup, Replace 함수를 이용해서 명부를 만들어 보겠습니다.
▣ Vlookup 함수
| Vlookup 함수 =Vlookup(검색값, 검색할 표의 범위, 열번호, False) |
(주)행복한 세상의 직원면부에서 2021년 가을 운동회 명부를 만들겠습니다.
(주)행복한 세상 직원 명부는 [sheet2]에 위와 같이 입력되어 있습니다.
위 시트는 가을 운동회 명부가 [sheet1]에 만들어 놓았고 부서, 주민번호, 직위, 연락처를 불러오겠습니다.
부서명을 불러오겠습니다. 찾는 값은 [류정군]이므로 [D10] 셀을 선택하고 함수 식을 입력하겠습니다. [ =VLOOKUP(C10,]을 입력하고 찾는 값의 찾는 범위는 아주 중요한 부분입니다. 찾는 값이 이름이므로 찾는 값의 범위는 이름이 1열의 시작이 되어야 합니다.
표의 범위를 선택 하기 위해 [sheet2]로 이동하여 이름이 1열이 되게 설정해야 하므로 [C4 : I34]까지 드래그하여 설정합니다. 다음은 열 번호입니다. 직원 명부에서 이름은 1 열이므로 부서는 3열이 되고 정확한 값과 일치해야 하므로 [false]를 입력하고 괄호 닫고 엔터를 누릅니다.
[D10] 셀에 기획부가 나타납니다. 그러면 D10셀을 선택하고 채우기 핸들이 나타나도록 D10셀에 갖다되면 자동 채우기핸들이 나타남과 동시에 더블클릭하면 수식이 복사되며 위 시트처럼 부서명이 차례로 입력됩니다.
다음은 직위와 연락처를 불러오겠습니다.
[F10] 셀을 선택하고 수식을 입력하겠습니다. [ =VLOOKUP(C10, 직원 명부!$C$4:$J$34,4, FALSE)를 입력하고 엔터를 치면 직위가 입력됩니다.
직위가 표시된 [F10] 셀을 선택하고 자동 채우기 핸들이 나타나면 더블클릭하여 수식을 복사하면 직위가 셀에 채워집니다. 이번에는 연락처를 가져오겠습니다. [G10] 셀을 선택하고 함수식을 동일 방법으로 입력하되 열 번호만 [ 6 ]으로 하고 입력하면 연락처가 입력됩니다.
연락처가 입력이 되었으나 사랑팀의 [항태민]의 이름이 정확하게 입력되지 않아 수정 후 바로 잡았습니다
▣ 엑셀 Replace 함수
주민번호를 vlookup 함수로 불러올 수 있지만 개인정보 보호에 의해서 주민번호 뒷자리 6자리를 [******] 표시로 숨기겠습니다.
| 엑셀 Replace 함수 = REPLACE(대상셀, 문자열의 시작 위치,변경할 문자 갯수, 변경할 문자열) |
동일 방법으로 주민번호를 불러오겠습니다. 입력값은 모두 동일하나 열 번호만 [ 2 ]로하여 입력하면 주민번호가 나타납니다. 이제 마지막으로 주민번호 뒷자리 6자리를 별 표시로 숨기겠습니다.
Replace함수에서 [ 9 ]는 숨기려고 하는 주민번호의 위치입니다. [ - ]도 포함입니다. [ 6 [ 은 숨기려고하는 숫자의 개수이고 "******"은 숨기려는 문자열의 형식입니다. [E10] 셀을 선택하고 다음과 같이 수식을 입력합니다
[=REPLACE(VLOOKUP(C10, 직원 명부! C4:J34,2, FALSE),9,6, "******")]입력하고 엔터를 누르면 아래와 같이 주민번호가 숨겨진 것을 볼 수 있습니다.
이상 가을 운동회 명부를 vlookup함수와 Replace함수를 이용해 만들어 보았습니다.
