본부·지점별 직급별로 평정자 구하기

1. 문제

조건에 따른 평정자 구하기.xlsx

0.03MB

 

아래와 같이 부서별, 성명별 구분(본부·지점)별, 직급별 평정 대상자 시트가 있고,

 

  본부·지점별 , 직급별로 1,2차 평정자와 조정평정자를 입력한 시트가 있을 때

 

  부서별로 1,2차 평정자와 조정평정자를 구하는 것을 해보겠습니다.

 

2. 논리

  VLookup 함수의 구문은

  VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])인데,

  2번째 인수인 table_array(찾을 범위)를 지정하는 것이 중요합니다.

 

  본부라면 B5셀에서 E20셀을 참고하고,

  지점이고 직급이 3급 이상이면 G5셀에서 J12셀을 참고하고

  지점이고 직급이 4급이하면 L5셀에서 O12셀을 참고하도록 해야 합니다.

 

3. 해법

 

가. 찾을 범위를 이름으로 지정하기

본부에 해당하는 B5셀에서 E20셀까지 선택한 다음

이름 상자에 본부라고 입력하고 엔터키를 누릅니다.

 

그러면 B5셀에서 E20셀까지 범위의 이름이 '본부'로 정해집니다.

 

그리고, 수식 탭에서 이름 관리자를 클릭하면

 

본부란 이름으로 아래 부분에 참조대상이 =평정자!$B$5:$E$20으로 지정되어 있고,

값은 {"감사실","-","-...식으로 보이는데, 값의 경계선을 오른쪽으로 밀면

 

열별로는 ,(쉼표)로 구분되고, 줄별로는 ;(세미콜론)으로 구분되는 것을 알 수 있습니다.

 

닫기 버튼을 누른 후 범위를 잡은 후 이름 상자에 '지점3급이상', '지점4급이하'라고 입력하고 엔터키를 눌러 3개의 이름을 지정합니다.

 

참고로 이름상자 오른쪽의 세로 점 3개를 오른쪽으로 밀면

아래와 같이 이름 전체를 볼 수 있습니다.

 

나. 본부, 지점 및 직급에 따라 찾을 범위 달리하기

=vl까지 입력하면 VLOOKUP이 표시되므로 탭키를 누르고,

 

찾을 값인 B2셀 주소를 입력하는데, 오른쪽으로 수식을 복사할 때 B열이 바뀌면 안 되므로 B앞에만 $표시가 붙도록 F4키를 3번 누릅니다. 그리고, ,(쉼표)를 누른 후 조건에 따른 범위를 지정합니다.

 

본부이면 본부를 반환하고, 아니면 직급의 첫 번째 숫자가 3보다 작으면 '지점3급이상'을 참고하고, 아니면 '지점4급이하'를 참고하면 됩니다.

 

따라서, 수식은

=VLOOKUP($B2,IF($D2="본부",본부,IF(VALUE(LEFT($E2,1))<=3,지점3급이상,지점4급이하))

이 됩니다.

마찬가지로 D열과 E열이 고정되도록 $표시를 앞에 붙여야 하는데,

Value함수를 사용한 것은 Left함수를 이용해 숫자를 하나를 가져와도 문자로 인식하기 때문에 숫자로 바꾸기 위한 것입니다.

 

다. index_num을 column함수를 이용해서 지정하기

그리고, 세 번째 인수인 index_num을 입력하는데, 오른쪽으로 복사할 때 자동으로 2,3,4가 되도록

column 함수를 이용하면 되는데,

G열의 열 번호가 7이므로 2가 되려면 5를 빼야 합니다.

 

따라서, 완성된 수식은

=VLOOKUP($B2,IF($D2="본부",본부,IF(VALUE(LEFT($E2,1))<=3,지점3급이상,지점4급이하)),COLUMN()-5,0)

입니다.

 

네 번째 인수의 경우 정확히 일치는 원래 False인데,

 

False는 0이므로 0이라고 한 것입니다.

 

라. G2셀의 수식을 모든 셀에 복사해서 붙여 넣기

G2셀의 수식을 복사한 후 G2셀에서 맨 마지막 셀인 i469셀까지 붙여 넣어도 되지만

G2셀의 채우기 핸들을 i2셀까지 끈 후

 

i2셀의 채우기 핸들을 더블 클릭해서 맨 아랫줄까지 채워도 됩니다.

 

마. 평정자가 0인 경우 0 표시하지 않기

G2셀부터 i열 맨 아래까지 선택한 후

마우스 오른쪽 버튼을 누르고,

셀 서식 - 사용자 지정을 클릭하고

G/표준 다음에 ;;라고 세미콜론 2개를 입력해서 음수와 0일 때 값을 공백으로 바꾸면 됩니다.

 

바. 평정자가 -인 경우 - 표시하지 않기

-은 여전히 없어지지 않습니다.

 

Ctrl + H키를 누른 후 찾을 내용에 -을, 바꿀 내용은 아무것도 입력하지 않아 공백으로 한 다음, '모두 바꾸기' 버튼을 눌러도

 

"이 수식에 문제가 있습니다"라는 에러 메시지만 표시되고 바뀌지 않으므로 다른 방법을 찾아야 합니다.

 

따라서, 결괏값이 -(하이픈)이면 ""(공백)으로 바꾸고, 아니면 결괏값을 표시하도록 아래와 같이 수식을 수정해야 합니다.

=IF(VLOOKUP($B2,IF($D2="본부",본부,IF(VALUE(LEFT($E2,1))<=3,지점3급이상,지점4급이하)),COLUMN()-5,0)="-","",VLOOKUP($B2,IF($D2="본부",본부,IF(VALUE(LEFT($E2,1))<=3,지점3급이상,지점4급이하)),COLUMN()-5,0))

 

다만 수식이 복잡해 보이는 것이 흠입니다.

조건에 따른 평정자 구하기(완성).xlsx

0.04MB