13강. 컴활1급 실기 엑셀-찾기/참조 함수

-찾기/참조 함수는 실무에서도 자주 쓰이고, 시험에도 자주 출제되는 함수다.

혼자 사용될 때도 많고 VLOOKUP, HLOOKUP, MATCH, INDEX는 혼합해서 사용될 때도 많다.

정확하게 이해해야 응용이 가능하므로 사용법을 확실하게 알아두자

 

 

1. VLOOKUP

인수로 정한 영역의 맨 왼쪽부터 특정 값을 찾은 후,

그 자료가 속한 행에서 필요한 값이 있는 열의 위치의 값을 반환한다.

옵션은 TRUE와 FLASE가 있다.

 

TRUE 또는 생략은 근삿값을 찾는데 숫자 데이터의 경우 해당 값보다 크지 않은 값의 근삿값을 찾는다.

FLASE는 정확하게 일치하는 값을 찾는다. 일치하는 값이 없을 경우 오류를 반환한다.

 

 

사용법을 자세히 설명하자면 첫 번째 인수인 과장의 값을 범위로 정한 아래의 직위별 지급 기준에서 찾은 다음 원하는 열의 번호인 2번째 열에 있는 상여금의 값을 반환하는 것을 알 수 있다.

 

 

다른 예시에서는 옵션을 생략하여 대출액이 5000일 때, 근삿값인 5800의 우수등급을 반환한 것을 볼 수 있다.

추후 다른 함수와의 혼합해도 많이 사용되니 자세히 알아두도록 하자

 

=VLOOKUP(찾을 값, 범위, 열 번호, 옵션)

 

 

 

2. HLOOKUP

열에서 데이터를 찾는 VLOOKUP과 달리 행에서 데이터를 찾아 반환한다.

그 밖의 옵션은 VLOOKUP과 같다.

자신이 원하는 데이터가 열에 있는지, 행에 있는지 파악한 다음 두 가지 함수를 골라서 사용하도록 하자

 

 

=HLOOKUP(찾을 값, 범위, 행 번호, 옵션)

 

 

 

3. LOOKUP

찾을 범위로 정한 영역에서 찾을 값을 이용해 기준을 찾고,

값을 구할 범위에서 구한 기준에 있는 위치에 있는 값을 반환한다.

 

 

위의 예시를 보면 B11:B14에서 찾을 값인 과장을 찾은 후 행의 위치 1을 계산한다.

그다음 영역에서 계산된 1행에 있는 값 5,500을 반환한 것을 볼 수 있다.

 

=LOOKUP(찾을 값, 범위 1, 범위 2)

 

 

 

4. INDEX

셀 범위나 데이터 배열에서 행 번호와 열 번호가 교차하는 곳에 있는 값을 반환한다.

주로 MATCH함수와 같이 사용하고, 그 밖의 다른 함수와도 많이 혼합되어 출제된다.

 

 

INDEX만을 사용한 예시는 위와 같다. 급여 기준표에서 직급과 호봉이 2일 때, 2행 2열의 값인 620,000의 값을 반환하는 것을 볼 수 있다.

 

 

위의 예시는 VLOOKUP과의 혼합 예시이다.

이렇게 INDEX에 들어가야 할 행과 열의 번호를 다른 함수를 이용하여 찾는 방식으로 혼합하면 된다.

 

범위는 최종적으로 찾을 값이 있는 매장 구분이 있는 범위로 정한 다음,

매장 번호를 이용해 VLOOKUP으로 구분코드 1,2,3,4를 찾는다.

이를 INDEX에서 행의 값이 되도록 한다. 최종적으로 알고 싶은 값은 매장 구분의 값이므로 원하는 열은 2열로 한다.

 

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

 

 

5. MATCH

지정된 범위에서 기준값과 같은 데이터를 찾아 상대적인 위치를 반환한다.

값이 아닌 위치를 반환하므로, 값을 원하면 LOOKUP함수를 사용한다.

위치를 나타내는 특성상 INDEX와 자주 사용된다.

 

 

옵션에 따라 위치를 찾은 방법이 다르다.

-1의 경우 찾을 값 보다 크거나 같은 값 중 가장 작은 값을 찾는다. 범위는 내림차순으로 정렬한다.

0의 경우 정확하게 일치하는 값을 찾는다. 정렬은 따로 필요 없다.

1의 경우 찾을 값 보다 작거나 같은 값 중 가장 큰 값을 찾는다. 범위는 오름차순으로 정렬한다.

 

실험 결과의 범위에서 59의 근삿값인 56의 위치인 1이 입력되는 걸 볼 수 있다.

=MATCH(찾을 값, 범위, 옵션)

 

 

 

6. CHOOSE

인수의 값에 따라 특정 번째의 값을 반환한다.

인수가 1일 경우 첫 번째 값을 2일 경우 두 번째 값을 반환한다.

최대 254개의 값을 인수로 할 수 있다. 

 

 

 

=CHOOSE(인수, 첫 번째, 두 번째,... )

 

 

 

7. OFFSET

기준 셀에서 지정한 행 수와 열 수만큼 떨어진 위치에 있는 셀의 데이터를 반환한다.

범위를 지정하지 않으면 1개의 셀 값을 반환한다.

 

행이 양수이면 기준 셀의 아래로, 음수면 위로 올라간다.

열이 양수면 기준 셀의 오른쪽으로, 음수면 왼쪽으로 이동한다.

 

 

=OFFSET(범위, 행, 열, 높이, 너비)

 

 

 

8. TRANSPOSE

셀 범위의 가로와 세로를 서로 바꿔서 표시한다.

반드시 변경 전 데이터 범위의 행과 열 개수에 대해 동일한 열과 행의 개수를 갖는 범위를 선택한 다음,

배열 수식으로 작성해야 한다.

 

 

 

=TRANSPOSE(배열)

 

 

 

9. ADDRESS

행 번호와 열 번호를 인수로 하여 셀 주소를 만든다.

만들어진 셀 주소는 INDIRECT 함수를 이용해 해당 셀 주소의 값을 참조할 수 있다.

 

 

=ADDRESS(행 번호, 열 번호, 참조 유형)

참조 유형의 경우 1은 절대 참조, 2는 행만 절대 참조, 3은 열만 절대참조, 4는 상대 참조이다.

 

 

 

10. INDIRECT

주소 형식을 갖춘 문자열을 셀 주소로 변환하여 해당 주소의 값을 참조할 수 있게 한다.

수식 자체는 변경하지 않고, 수식 안에 있는 셀에 대한 참조를 변경하려는 경우 사용한다.

 

 

=INDIRECT(텍스트)

 

 

 

11. AREAS

범위 내부에서 영역의 수를 계산한다.

영역이 겹치는 경우 겹치는 영역 하나만 계산하여 1을 반환한다.

 

 

=AREAS(범위)

 

 

 

12. HYPERLINK

하드디스크 드라이브, 네트워크 서버, 인터넷 등에 지정된 문서로 바로 이동할 수 있는 하이퍼링크를 만든다.

 

 

=HYPERLINK(위치, 텍스트)

 

 

 

13. COLUMN/COLUMNS

 

COLUMN은 셀 주소에 대한 열 번호를 반환한다.

행의 위치와 상관없이 열의 주소만을 계산한다.

 

COLUMNS는 지정된 범위 안에 포함된 열의 개수를 알아낸다.

행의 개수와 상관없이 열의 개수만을 계산한다.

 

=COLUMN(범위)

=COLUMNS(범위)

 

 

 

14. ROW/ROWS

 

ROW은 셀 주소에 대한 행 번호를 반환한다.

열의 위치와 상관없이 행의 주소만을 계산한다.

 

ROWS는 지정된 범위 안에 포함된 행의 개수를 알아낸다.

열의 개수와 상관없이 행의 개수만을 계산한다.

 

=ROW(범위)

=ROWS(범위)

 

 

 

찾기/참조 함수는 응용이 중요하기 때문에, 중요한 예제들을 모아서 풀어보는 것이 중요하다. 

다음은 텍스트 함수를 공부하고 정리하도록 한다.

텍스트 다음 통계 함수까지 공부하면 긴 엑셀 함수 공부는 마무리하도록 한다.