우리는 지금까지 다양한 수식과 함수를 응용하는 방법에 대해서 알아봤습니다. 수식뿐만 아니라 문자열이나 날짜 등도 중요하게 다뤄진다는 것도 아시겠죠? 찾기 및 참조 함수는 함수 사용에 있어서 고급 분야에 속합니다. 초보자들은 잘 사용하지 않는 함수이기도 하구요. 찾기 및 참조함수는 기본적으로 다른 셀의 값을 참조해서 실제 원하는 범위에서 조건에 맞는 데이터를 찾아주는 함수입니다. 엑셀에서 이런 역할을 하는 함수는 LOOKUP, VLOOKUP, HLOOKUP 이렇게 세 함수가 있는데, 일반적으로 조금 정교한 찾기/참조 작업을 할 때는 주로 INDEX, MATCH 함수와 연동해서 사용하는 경우가 많습니다.
잠깐! 지금 진행하는 강좌에서는 여러분들의 수식 이해를 돕고자 "이름 정의"를 하지 않고 진행하고 있습니다만, 여러분들은 실제 작업에서는 반드시 이름을 정의하고 사용하는 습관을 들이시기 바랍니다!
VLOOKUP과 HLOOKUP 함수 형식
VLOOKUP과 HLOOKUP은 가장 많이 사용되는 찾기 함수로 V는 Vertical(새로), H는 Horizontal(가로)을 의미합니다. VLOOKUP과 HLOOKUP 함수의 사용방법은 동일합니다. 우선 그 구문을 살펴보면요,
VLOOKUP/HLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
- Lookup_value : 배열의 첫째 열에서 찾을 값입니다. Lookup_value는 숫자, 참조 영역 또는 문자열을 지정할 수 있습니다.
- Table_array : 데이터를 찾을 원본 표입니다. 데이터베이스나 목록과 같은 범위의 참조 영역 또는 범위이름을 사용합니다. range_lookup이 TRUE이면 table_array의 첫 열에 있는 값은 오름차순(…, -2, -1, 0, 1, 2, … , A-Z, FALSE, TRUE)으로 정렬되어 있어야 하며 그렇지 않으면 VLOOKUP은 정확한 값을 찾지 못할 수 있습니다.
- Col_index_num : 비교값과 같은 행에 있는 값을 표시할 table_array의 열 번호입니다.
- Range_lookup : 정확하게 일치하는 값을 찾을 것인지, 근사값을 찾을 것인지를 결정하는 논리값입니다. TRUE이거나 생략되면 정확한 값이 없는 경우 근사값 즉 lookup_value보다 작은 값 중에서 최대값을 찾습니다. FALSE이면 정확하게 일치하는 값을 찾으며 만일 일치하는 값이 없으면 #N/A 오류값을 표시합니다.
다른 함수에 비해서 인수가 복잡해 보이지만, 실제 예를 통해서 보면 별로 어렵지 않습니다. 이 함수는 전체 데이터에서 일부 데이터를 추출해서 다른 작업과 연관시킬 때 많이 사용됩니다.
VLOOKUP으로 세율 찾기
다음과 같은 데이터 구조가 있을 경우 기준 표를 참고로 해서 연봉에 대한 세율을 자동으로 찾는 식을 만들어 보겠습니다. 찾을 값이 열 구조로 되어 있으므로 VLOOKUP을 사용합니다.
연봉에 대한 세율표가 D2:F5에 입력되어져 있습니다. 물론 단순 예입니다. ^^ 이 기준표를 참고로 해서 B1에 연봉이 입력되면 B2에 세율을 자동으로 찾는 수식을 만들어 본 것입니다. 수식을 한번 볼까요?
=VLOOKUP(B1,D2:F5,3)
위에서 배운 함수 사용법과 비교해서 한번 분석해보세요. B1이 해당하는 값을 D2:F5 범위에서 찾아서 3번째 열(F열)에 있는 값을 찾으라는 수식입니다. 하나씩 풀어서 생각하니까 어렵지 않으시죠?
현재 B1셀에 아무 값이 없으니 F2 값을 찾아서 10%를 반환한 것입니다. 그렇다면 B1셀에 30,000,000을 입력하면 세율은 어떻게 나올까요? 30%로 나와야 정상이겠죠?
참조 함수에 대해서도 바로 감이 잡히시죠? 기준표나 데이터가 있을 경우 원하는 값을 그 표에서 비교하고 찾아내는 것입니다.
HLOOKUP으로 세율찾기
VLOOKUP과 HLOOKUP은 기본적으로 사용법이 동일하다고 했습니다. 찾기의 기준이 되는 표나 데이터의 형식에 따라서 맞는 함수를 사용하시면 됩니다. 위 세율 찾기 예제와 동일한 데이터가 아래와 같은 형식으로 작성되었다고 가정하면 HLOOKUP으로 동일한 값을 찾을 수 있을겁니다.
수식은 바로 이해가 되시죠?
MATCH 함수로 내신등급 구하기
찾기 및 참조에서 LOOKUP 함수와 많이 사용되는 것이 배열에서 찾는 MATCH 함수입니다. MATCH 함수로 성적표 데이터에서 내신등급을 구하는 수식을 한번 만들어보도록 하죠. MATCH 함수는 지정한 방법으로 지정한 값과 일치하는 배열 요소를 찾아 상대 위치를 표시해주는 기능을 가지고 있으므로 항목의 위치를 알아낼 때 유용하게 사용할 수 있습니다.
MATCH(lookup_value,lookup_array,match_type)
- Lookup_value : 표에서 찾으려는 값입니다. 예를 들어 전화 번호부에서 어떤 사람의 번호를 찾으려고 할 때 그 사람의 이름을 사용하여 찾지만 원하는 데이터는 그 사람의 전화 번호입니다. Lookup_value는 숫자, 텍스트, 논리값과 같은 값 또는 값의 셀 참조 영역을 지정할 수 있습니다.
- Lookup_array : 찾으려는 값을 포함하는 셀 범위로 배열 또는 배열 참조 영역입니다.
- Match_type : lookup_array에서 lookup_value를 찾는 방법을 지정하는 숫자로 -1, 0, 1 세 가지가 있습니다. match_type이 1이면 MATCH는 lookup_value보다 작거나 같은 값 중에서 최대값을 찾습니다. 따라서 이 경우에 Lookup_array는 반드시 오름차순(…-2, -1, 0, 1, 2,…A-Z, FALSE, TRUE)으로 정렬되어 있어야 합니다. match_type이 0이면 MATCH는 lookup_value와 같은 첫째 값을 찾습니다. 이 때는Lookup_array가 임의의 순서라도 상관없습니다. match_type이 -1이면 MATCH는 lookup_value보다 크거나 같은 값 중에서 최소값을 찾습니다. 따라서 Lookup_array는 반드시 내림차순(TRUE, FALSE, Z-A,…2, 1, 0, -1, -2,…)으로 정렬되어 있어야 합니다. 참고로 match_type을 생략하면 1로 간주합니다.
자 그럼 내신 등급을 한번 구해봅시다. 내신 등급은 상대적으로 구하는 것으로 하겠습니다. 전체 인원을 기준으로 상대적인 순위를 구하고, 이를 내신 성적 기준표에서 찾아서 등급을 찾으면 되겠죠. 이렇게 하려면 상대 순위가 나와야 하는데 아래와 같은 데이터 구조를 만들어봤습니다.
A2:A6에 내신 등급 기준을 만들었습니다. E2:E7에 상대 순위를 구하는 것이 먼저 필요하겠죠. 상대 순위는 D열의 평균점수를 기준으로 그 점수가 전체 범위에서 차지하는 상대 비율을 찾아서 값을 구하는 원리입니다. 이렇게 백분율 순위를 구할 때는 PERCENTRANK 라는 함수를 사용하는데, 엑셀 2010에서는 PERCENTRNK.INC와 PERCENTRNK.EXC 함수로 대체되었습니다. 아래 방법으로 상대 순위를 한번 구해보세요.
참조 영역인 D2:D7은 항상 고정된 참조 영역이므로 절대 참조로 변경한 것도 유심히 보시구요. 자 이제 상대 순위가 나왔으므로 이 순위를 내신 등급 기준표를 이용해 MATCH 함수로 조합하면 내신 등급이 나오게 됩니다.
내신등급이 정확하게 구해졌죠? 함수 마법사를 이용하지 않고 함수를 셀에 직접 입력할 때 엑셀이 실시간으로 함수에 대한 설명과 인수에 대한 형식 등을 도움말로 표시해주기 때문에 처음 사용하는 경우라도 비교적 쉽게 인수를 유추해서 수식을 완성할 수 있습니다.
함수도 많이 어렵지는 않으시죠? 엑셀의 함수를 모두 하나씩 예제를 만들고 살펴보는 것은 시간 낭비입니다. 함수와 수식, 그리고 엑셀의 기본 기능을 잘 조합하는 응용력을 키우는 것이 제일 중요합니다!
