[함수] 컴활 2급 실기 엑셀 찾기와 참조 함수입니다.

이번에는 엑셀 찾기 참조 함수를 살펴보겠습니다.
엑셀 찾기와 참조함수는 컴퓨터활용능력 3급에서는 볼 수 없는 함수들 입니다. 이 함수들은 아래와 같습니다.(2016년 현재 컴활 3급 자격 사라짐)

- VLOOKUP, HLOOKUP,CHOOSE, INDEX

1. 엑셀 VLOOKUP 함수

  정의: 범위를 수직으로(열로) 검색하여 조건을 만족하는 값을 찾습니다.
  형식: =VLOOKUP(검색값, 범위, 열번호, [검색유형])
  사용방법: =VLOOKUP("123", A1:B10, 2)
  사용결과: 문자열 "123" 을 A1에서 B10 범위 중 첫 번째 열에서 수직으로 찾습니다. 그러니까 검색 범위가 A1:A10이 되는 거네요. 이 범위에서 찾아서 "123"이 검색된다면 A1:B10 에서 2번째 열의 값을 반환합니다. 즉 B1:B10 이 반환할 값들의 집합이 되는 거네요. 이해되시나요..좀 힘드시지요. 예제를 하나 풀어 봅시다.

 

기출 예제 : 면접 등급[C3:C10]과 면접표[G4:H8]를 이용하여 평가 점수[E3:E10]를 구하시오.(컴퓨터활용능력 2급 13회 E형)

  -평가점수 = 평점 + 필기점수

  기출 풀이 : E3 셀에 셀 포인트를 놓고 다음 수식을 입력합니다.

  =VLOOKUP(C3, $G$4:$H$8, 2) + D3

  엑셀 채우기 핸들(점끌기탭)로 E10까지 끌어서 수식 복사합니다.

  자, 그럼 수식을 풀어 볼까요? C3셀을 G4:H8 범위인 면접 등급표의 가장 왼쪽 열에서 검색합니다. 검색 후 검색 범위 G4:H8의 2열 에서 값을 취합니다. C3셀 같은 경우 평점은 50이 되겠네요. 등급표는 엑셀 절대 참조로 고정을 해야 채우기 핸들로 끌었을 때 함께 움직이지 않겠지요.^^.

* 엑셀 절대 참조와 상대 참조, 혼합 참조에 대해 다룹니다.
-이 링크에 절대 참조, 상대 참조, 혼합 참조의 설명의 버전업 되었습니다.

아시는 분들도 계시겠지만 왜 G4:H8 의 범위에 $가 추가 되었는지 궁금하신 분들도 계실겁니다.
그 전에 엑셀 상대 참조와 절대 참조, 또 혼합 참조라고도 있습니다.
엑셀에서 참조라 함은 엑셀의 셀들의 배열에서 특정 셀의 위치를 열 번호(알파벳)와 행 번호(아라비아 숫자)의 조합으로 나타낸 것을 말하지요.

위의 문제에서 "성명" 의 참조는 열 번호(A)와 행 번호(2)의 조합, A2가 되지요.
참조를 아셨으니 상대 참조를 알아 봅니다.

상대 참조, 대부분의 엑셀에서 쓰이는 셀 참조가 상대 참조이지요. "상대" 서로 대응한다... 그런 뜻인가요?, 하여튼 ,
위에서 E3의 수식 중 C3가 필요하지요.
그렇게 구한 수식을 E4에 다시 복사를 합니다. 엑셀 수식 복사지요.
엑셀에서 대부분의 수식 복사는 셀 선택기의 오른쪽 아래에 있는 점, "채우기 핸들"이라 불리는 이 점을 이용하여 E10까지 수식 복사 합니다. 이렇게 셀의 이동 방향이 아래로 움직이면 상대 참조로 설정한 C3의 방향도 C10을 향해 아래로 움직이지요. E열의 수식에 대응 되게 말이지요..  바로 이것이 엑셀 상대 참조입니다.

엑셀 상대 참조가 이해되면 엑셀 절대 참조는 쉽습니다. 상대 참조의 반대 개념이지요. "절대" 라는 말에서 감이 오셨듯이 수식 복사하였을때 다른 참조가 어떻게 움직이든 그 참조를 고정시킬 필요가 있을 때도 있습니다. 위의 면접등급표(G4:G8) 처럼 말이지요. 이렇게 고정된 참조를 엑셀에서 절대 참조라 합니다. 만약 위에서 C3셀이 아래로 수식 복사될 때마다 참조 중인 면접등급표의 참조도 아래로 내려 간다면 정확한 결과를 얻을수 없겠지요?
그럼, 어떻게 절대 참조로 만드는가 참조의 열 번호와 행 번호 앞에 '$'를 추가 하면 절대 참조로 인식합니다. 단축키는 (F4번입니다.)

혼합 참조는 상대 참조와 절대 참조를 섞어서 사용하는 참조를 말합니다. (예: $A1:$A10 , 이 경우 열을 절대 참조로 고정하고 행은 상대 참조가 되겠지요.)

- 참, 엑셀 VLOOKUP 함수에서 [검색 유형] 인수를 설명드린다는 것을 잊었네요.. 지금 설명드립니다. 이 검색 유형은 옵션형 인수 입니다. 생략하면 기본값으로 인식됩니다.
그럼 기본값은 뭘까요? 기본값은 1 혹은 TRUE입니다. 이는 범위에서 검색값을 검색하는 방법을 말하는 것인데 0 혹은 FALSE 는 검색값과 정확히 일치하는 값을 찾겠다는 옵션잆니다.
 1 혹은 TRUE 는 비슷하게 일치하는 값을 찾겠다는 말이구요..

비슷한게 일치하는 값을 찾을 때는 첫 번째 열이 반드시 오름 차순 정렬되어 있어야 합니다. 그렇지 않으면 정확한 값을 찾을 수 없습니다.

중간에 엑셀 절대 참조 때문에 엑셀 찾기 함수를 모두 살펴드리지 못했습니다. 나머지 - HLOOKUP 함수, CHOOSE 함수, INDEX 함수 는 다음 게시물에서 함께 하지요.
열심히 ..!!