6. 데이터 선별하기(1) - VLOOKUP 2 (★)

 

 

 

 

이제 앞서서 살펴보았던 VLOOKUP 활용 예제에서 조금 더 발전을 시켜보겠습니다. 우선 우리는 각 임직원 별로 직원의 정보와 연차, 수당, 연봉 상승률 등 다양한 개인별 데이터를 확보해 둔 상태입니다. 이제 이 raw data에서 중요 내용만 선별하여 별도의 보고 자료를 만드는 연습을 해보겠습니다.

 

 

6. 데이터 선별하기 - VLOOKUP(1) (★)

 

인사고과 raw data 예시

 

우선 raw data에서 상부 보고 자료로 사용할 결과 요약 문서에는 임직원 개개인의 매출 등급, 매출 이익률 등급, 원가 절감률 등급을 보여주고 종합 등급과 순위만을 보여주려고 합니다. 그리고 결과적으로는 각각의 등급은 몇 명씩 분포하는지도 보여주고자 합니다. 

 

 

 

기본적으로 위와 같은 형태의 요약 보고서가 존재할 것입니다. 여기서 임직원의 이름만으로, 우리가 작성했던 raw data에서 값을 찾아 자동으로 채워주는 문서를 만들려고 합니다. 

 

여기서는 당연히 VLOOKUP을 사용해야 하겠죠? 하나씩 짚어보겠습니다. 이번에도 첫 번째 인물의 신규 매출을 먼저 작성하도록 하겠습니다. 

 

=VLOOKUP($E8,'raw_data_시트명'!$E$7:$Q$27,F$6,0)

 

인사고과 raw data 예시

 

위 함수의 파라미터 값들을 위의 raw data와 함께 살펴보겠습니다. 우선, 첫 번째 파라미터 $E8은 대상 시트의 이름 칼럼을 의미합니다. 당연하지만 이 이름과 동일한 row를 찾고, 거기서 column을 찾아서 값을 불러오는 것이니까요. 참고로, 이 VLOOKUP 수식은 복사하여 다른 셀에도 붙여 넣을 예정입니다. 그러므로, 해당 영역은 row는 바뀌더라도 이름이 있는 열은 변경되어서는 안 됩니다. 그래서 열 E에 절대 참조 $를 붙였습니다. 

 

그리고 이 다음부터는 raw data 영역에서 찾아야 하는 파라미터 값들입니다. '시트명'!$E$7:$Q$27은 값을 찾을 raw data 전체 영역으로, 해당 영역은 절대 참조를 합니다. 물론, 여기서 가장 첫 번째 열은 우리가 찾고 있는 기준 열인 '이름'이 되어야 합니다.

 

그럼 다음 파라미터는, 이름으로부터 몇 번째 열인지 숫자를 입력하는 부분입니다. 그런데 이 부분을 직접 '3'이라고 입력하게 되면 다른 열에 해당 수식을 복사할 경우 해당 넘버를 일일이 수정해야 합니다. 몇 개 정도면 가능하겠지만, 수십 수백 개의 열이 있다면 이 역시 문제가 됩니다. 

 

그래서 해당 열의 기준 이름 열 부터 몇 번째 열인지를 해당 열 위에 작성하고, 이를 참조하도록 만듭니다. 아래 이미지를 참고하시면 됩니다. 그리고 이 경우 열은 변경되지만 row는 고정되어야 하므로 F$6와 같이 행 고정이 진행되었습니다. 그리고 마지막으로 0을 입력합니다. 

 

 

이제 해당 셀의 수식을 복사하여 수식 붙여 넣기(ALT + 2)를 전체 셀에 진행하면 아래와 같이 완성된 셀을 확인할 수 있습니다. 

 

 

 

각 등급별 인원 수 카운팅은, 아래와 같이 COUNTIF를 사용합니다. 범위, 카운트 대상의 순서로 파라미터를 작성하면 됩니다.