[엑셀 함수 5탄] 조회 및 참조함수 > 컴퓨터상식

1. COLUMN : 참조범위의 열번호를 구한다.

<형식>  =COLUMN(범위)

<예제> 

<설명>

 =COLUMN() 현재 셀의 열번호를 출력하라고 했으니 G열은 7열이다.

=COLUMN(A2) A2열의 열번호 즉 A열은 1열이다.

2. ROW : 참조범위의 행번호를 구한다.

<형식>  =ROW(범위)

<예제> 

<설명>

=ROW() 현재 셀의 행번호를 출력하라고 했으니 G3 즉 3이 출력된다.

=COLUMN(A4) A4의 행번호 즉 4가 출력된다. 

3. HLOOKUP : 배열의 첫번째 행을 조회하여 지정한 행의 값을 가져온다.

<형식> =HLOOKUP(찾으려는값,참조범위,참조범위의행번호,range_lookup))

- range_lookup 값이 TRUE이면 비슷한 값을 가져오고

- range_lookup 값이 FALSE이면 정확하게 일치하는 값을 가져온다.

<예제> 다음 표의 이름을 참고하여 답을 구하시오.

<설명>  

=HLOOKUP(B1,$A$1:$E$4,2,FALSE) 

: 전도환(B1)을 찾아라. $A$1:$E$4 범위내에서.,..정확한 값을 찾았으면 2행의 값(나이)을 출력하라.

=HLOOKUP(C1,$A$1:$E$4,3,FALSE) 

: 노대우(C1)을 찾아라. $A$1:$E$4 범위내에서.,..정확한 값을 찾았으면 3행의 값(소속)을 출력하라.

=HLOOKUP(E1,$A$1:$E$4,4,FALSE) 

: 노무연(E1)을 찾아라.  $A$1:$E$4 범위내에서.,..정확한 값을 찾았으면 4행의 값(고향)을 출력하라. 

※ 실제 업무에서 데이터를 위와 같이 입력하는 경우는 드물다. 즉, HLOOKUP 함수보다 VLOOKUP 함수가 더 많이 사용된다.

4. VLOOKUP : 배열의 첫째열을 조회하여 지정한 열의 값을 가져온다

<형식> =HLOOKUP(찾으려는값,참조범위,참조범위의행번호,range_lookup))

- range_lookup 값이 TRUE이면 비슷한 값을 찾아서 가져오고

- range_lookup 값이 FALSE이면 정확하게 일치하는 값을 찾아서 가져온다.

<예제> 다음 표의 이름을 참고하여 답을 구하시오.

=VLOOKUP(A2,$A$1:$E$5,2,FALSE) 

: 전도환(A2)을 찾아라. $A$1:$E$5 범위내에서.,..정확한 값을 찾았으면 2열의 값(나이)을 출력하라.

=VLOOKUP(A3,$A$1:$E$5,3,FALSE) 

: 노대우(A3)을 찾아라. $A$1:$E$5 범위내에서.,..정확한 값을 찾았으면 3열의 값(소속)을 출력하라.

=VLOOKUP(A5,$A$1:$E$5,5,FALSE) 

: 노무연(A5)을 찾아라. $A$1:$E$5 범위내에서.,..정확한 값을 찾았으면 4열의 값(출신)을 출력하라.

※ HLOOKUP은 가로(Horizontal)로 입력된 데이터에서 값을 찾아(Lookup) 행의 값을 출력하고,VLOOKUP은 세로(Vertical)로 입력된 데이터에서 값을 찾아(Lookup) 열의 값을 출력한다.

5. MATCH :  참조영역이나 배열에서 지정된 값의 상대 위치를 구한다.

<형식> =MATCH(찾으려는값,참조범위, MATCH_TYPE)

- MATCH_TYPE이 1일 경우  : lookup_value보다 작거나 같은 값 중에서 최대값을 찾는다.

- MATCH_TYPE이 0일 경우  : lookup_value와 같은 첫째 값을 찾는다.

- MATCH_TYPE이 -1일 경우  : lookup_value보다 크거나 같은 값 중에서 최소값을 찾는다.

<예제> 다음 표를 참고하여 질문에 답하시오.

=MATCH(A7,$A$1:$E$1,0) 

: 소속 즉 A7셀과 같은 셀(0)이 몇번째 열에 있는가?  A1셀에서 E1셀 중에서.... 결과는 3번째 열

=MATCH(A8,$A$1:$A$5,0) 

: 김영남 A8셀과 같은 셀(0)이 몇번째 행에 있는가?  A1셀에서 A5셀 중에서.... 결과는 4번째 행

※ 상기 수식에서 $A$1:$E$1와 $A$1:$A$5처럼 절대주소를 사용하는 이유는 채우기를 해도 검색할 범위를 고정하기 위해서다.

그러나 위의 예제처럼 한개의 수식으로 결과를 얻고자 한다면 굳이 절대주소를 사용할 이유는 없다.

6. INDEX : 참조영역이나 배열에서의 값을 구한다.

<형식> =INDEX(참조범위,행번호,열번호)

<예제> 다음 표를 참고하여 질문에 답하시오.

- 2 문제 모두 참조범위는 $A$2:$E$5로 동일하다.

- 2 문제 모두 열번호도 이름으로 동일하며 첫번째 열(1)이다.

- 그렇다면 작은 값(MIN)과 많은 값(MAX)을 찾아 행번호(MATCH)를 구해야한다.

※ 가장 작은 값의 행번호를 찾기 위한 수식 : =MATCH(MIN($B$2:$B$5)

※ 가장 큰 값의 행번호를 찾기 위한 수식 : =MATCH(MAX($B$2:$B$5)

위의 결과를 INDEX 수식에 적용해 보면 다음과 같다.

- 나이가 제일 작은 사람은 누구인가 

: =INDEX($A$2:$E$5,MATCH(MIN($B$2:$B$5),$B$2:$B$5,0),1) => 노무연

- 나이가 제일 많은 사람은 누구인가 

: =INDEX($A$2:$E$5,MATCH(MAX($B$2:$B$5),$B$2:$B$5,0),1) => 김영남

※ 위 문제처럼 INDEX 함수 단독으로 사용되는 경우는 거의 없다. 다른 함수와 혼합하여 사용되므로 엑셀 기초함수 사용법을 충분히 숙지해 두어야한다.

7. 조회 & 참조 함수 활용하기

<예제> 다음 표를 참고하여 질문에 답하시오.

행의 값과 열의 값을 이용하여 값을 찾고자 할때에는 INDEX 함수를 활용한다.

=INDEX(참조범위,행번호,열번호)

여기서 참조범위는 노무연이라는 행번호와 출신이라는 열번호를 알아야 함으로  $A$1:$E$5이다.

그렇다면 노무연이 몇번째 행에 있는지 찾아야 함으로 MATCH함수를 활용한다.

= MATCH($A$5,$A$1:$A$5,0) : A5셀 즉 "노무연"과 같은 값이 몇번째 행에 있는가? A1에서 A5사이에서...결과는 5다.

그리고 출신이 몇번째 열인지 찾기위한 MATCH 함수식은 다음과 같다.

= MATCH($E$1,$A$1:$E$1,0) :  E1셀 즉 "출신"과 같은 값이 몇번째 열에 있는가? A1에서 E1사이에서... 결과는 5다.

이것을 조합하면 INDEX 함수 수식은 다음과 같다.

=INDEX($A$1:$E$5,MATCH($A$5,$A$1:$A$5,0),MATCH($E$1,$A$1:$E$1,0)) => 변호사 

이상으로 조회와 참조함수에 대해서 살펴봤습니다. 업무에 많이 활용되는 함수이오니 반복 실습을 통해 충분히 익혀 두시기 바랍니다.

감사합니다.