[컴퓨터활용능력 1급] 엑셀 찾기와 참조 함수

1. VLOOKUP, HLOOKUP

검색값을 범위에서 찾아서 해당 위치에 있는 값을 추출하는 함수이다.

LOOKUP함수는 이제 시험에 나오지 않기 때문에 생략

 

1) =VLOOKUP(검색값, 범위, 열번호, [검색유형])

범위의 첫 열에서 검색값을 찾아 지정한 열 번호에서 같은 행에 있는 값을 표시한다.

Vertical Lookup이기 때문에 수직 방향으로 세로검색한다고 이해하면 된다.

검색값은 테이블 범위에서 찾고자 하는 실제 값을 의미한다. 검색값을 꼭 테이블 범위의 첫번째 열하고 일치시켜주어야한다.

(테이블)범위는 필드명을 제외하고 지정하며 절대참조를 해주어야한다. 숫자의 경우 반드시 오름차순으로 정렬되어있어야 한다.

검색유형은 true와 false 두 가지가 있는데 true는 생략이 가능하고 정확한 값이 없는 경우 근사값(검색값보다 작은 값 중에서 최대값을 찾음)을 표시한다. 대부분의 숫자값을 계산할 때에는 true조건을 사용하면 된다.

false유형은 0으로도 표시하고, 정확하게 일치하는 값을 표시할 때 사용한다. 정확히 일치하는 값이 없을 때 #N/A 에러값이 표시된다.

검색값이 문자일 때에는 꼭 false유형을 써주어야한다.

 

2) =HLOOKUP(검색값, 범위, 행번호, [검색유형])

VLOOKUP과 같지만 VLOOKUP이 열에서 값을 찾아준다고 하면

HLOOKUP은 행에서 값을 찾아준다.

나머지 조건은 다 같다.

 

설명이 많기 때문에 예상이 되겠지만, 개인적으로는 함수 중에서 가장 어려운 유형이 아닐까 싶다..

그래도 연습을 많이 하다보면 익숙해져서 값을 찾아내는 과정이 재미있는 함수이다.

 

 

2. TRANSPOSE

앞에 통계함수 부분에서 FREQUENCY 함수를 할 때 언급했던 것처럼

TRANSPOSE함수는 행과 열을 바꾸는 함수이다.

자세한 내용은 아래 링크에서 확인할 수 있다.

반드시 먼저 범위를 설정한 후 함수식을 입력해야 한다는 것에 주의하자!!

https://genius-yoon.tistory.com/31

[컴퓨터활용능력 1급] 엑셀 통계함수 정리

 

 

 

3. INDEX

범위(배열)의 값에 대한 참조영역을 구하는 함수이다.

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

행 번호나 열 번호는 각각의 위치값을 의미하는 것으로, 모두 숫자이다.

=INDEX(C4:G9,3,2) 라는 함수는

C4:G9 범위에 대하여 3열 2행의 값을 찾는다는 의미이다.

 

-OFFSET-

OFFSET 함수는 INDEX와 비슷한데

시험에 자주 나오지 않기 때문에 간단하게 설명한다.

=OFFSET(기준,행수,열수,[높이],[폭])

기준으로부터 행 또는 열 수만큼 떨어진 곳에 있는 특정 높이와 너비의 참조 영역을 표시한다.

=OFFSET(A1,1,2,2,2) 라는 함수는

A1으로부터 1행 2열을 이동한 위치에서의 2열 2행( 2*2 ) 만큼의 값을 구하는 것이다.

하나의 값을 구하려면 =OFFSET(A1,1,2,1,1) 이라고 써도 되고,

1,1을 생략해도 된다.

 

 

 

4. CHOOSE

리스트에서 값을 선택하는 함수이다. 엑세스에서도 쿼리 문제로 많이 출제되는 함수이다.

=CHOOSE(인덱스 번호, 값1, 값2 ,,,)

인덱스 번호(일련번호) 위치에 있는 값을 구한다.

만약 인덱스 번호가 1이라면 값1이 결과로 나온다.

직접 셀에서 쓰는 것 보다는 함수마법사를 이용해서 사용하는 편이 간편하다.

 

 

5. MATCH

일치하는 값의 상대위치를 나타내는 함수이다.

=MATCH(검사값, 검사범위, [검사유형])

겁사값을 검사 범위에서 검색하여 대응하는 값이 있는 경우 상대적 위치를 나타낸다.

검사 유형에는 1, 0, -1이 있는데 -1은 잘 사용하지 않는다.

0은 정확히 일치하는 값을 찾고(찾는 값이 문자일 때), 1은 근사값을 찾는다. VLOOKUP에서 설명한 옵션을 떠올리면 쉽게 이해된다.

 

 

6. COLUMN, COLUMNS

=COLUMN(참조)

참조영역의 열 번호를 나타낸다. 참조영역에 아무것도 쓰지 않으면 COLUMN함수를 쓰고 있는 셀의 열번호가 나온다.

필기영역에서도 자주 출제되는 문제이므로 기억해두자.

 

=COLUMNS(배열)

참조 영역의 열 개수를 구한다.

 

 

 

7. ROW, ROWS

=ROW(참조)

=ROWS(배열)

COLUMN함수가 열을 구한다면 ROW는 행을 구한다.

사용은 똑같다.

 

---

 

 

 

 

p.123
출제유형1

 

 

고객등급별 사은품 목록에서 hlookup을 이용하여 표1의 사은품을 채우는 것이다.

=HLOOKUP(LEFT(C3,1),$A$13:$D$14,2,FALSE)

참조범위에 절대참조를 해주고 옵션에 false를 이용해서 정확하게 찾도록 하는 것에 유의한다.

 

자동채우기를 이용하여 해결이 가능하다.

 

---

 

 

이용일수가 가장 많은 회원의 이름을 찾는 것이다.

large 함수를 통해서 이용일수가 가장 많은 회원을 찾고 vlookup함수를 이용해서 그 이용일수에 해당하는 회원명을 찾으면 된다.

숫자이지만 구간을 나타내는 것이 아니라 특정한 값을 찾는 것이기 때문에 옵션에 false를 사용해야함에 주의하자.

=VLOOKUP(LARGE(G3:G8,1),G3:H8,2,FALSE)

---

 

 

아래 표에 주어진 수강과목별 할인율을 참고하여 표3의 수강료를 계산한 것이다. 문제에 나온대로 수식을 사용하고 vlookup과 hlookup 함수 중 알맞은 것을 골라서 사용하는 것이다.

수강과목별 할인율 표가 세로로 되어있기 때문에 vlookup함수를 사용한다.

=L3*60000*(1-VLOOKUP(L3,$L$14:$M$17,2,TRUE))

범위에 절대참조를 해주고, true조건을 사용하는 것에 주의하자.

 

---

choose와 rank함수를 이용해서 평가점수별 결과를 나타낸다.

=CHOOSE(RANK(C18,$C$18:$C$23,0),"성과급","성과급","","","","감봉")

자동채우기를 위해 참조범위는 절대참조해주고, 큰 점수부터 1위로 하기 위해서(내림차순) 조건에 0을 사용한다.

공백도 ""를 사용하여 꼭 조건에 포함시켜야한다.

---

choose함수와 mid함수를 이용하여 사원코드의 숫자부분 첫번째 자리에 따라서 직위를 입력하는 것이다.

=CHOOSE(MID(F16,3,1),"부장","과장","대리","사원")

 

---

choose함수와 weekday함수를 이용하여 요일을 나타내는 것이다.

weekday함수를 이용하여 요일의 일련번호를 나타낸 후, choose함수를 통해서 요일을 지정해준다.

=CHOOSE(WEEKDAY(C2),"일","월","화","수","목","금","토")

weekday함수의 옵션에서 1은 일요일부터 2는 월요일부터 요일이 시작되는데, 1은 생략이 가능하다.

함수를 직접 입력해도 되고 함수마법사를 이용해도 된다.

함수마법사를 이용하는 방법이 조금 더 간단하다.

---

=MATCH(C13,B4:B9,1)

match함수를 이용할 때에는 0과 1 옵션만 사용한다고 생각하면 간단하게 해결된다. 주어진 숫자범위 안에서 찾는 것이기 때문에 옵션에 1을 입력한다.

=MATCH(C12,C3:G3,0)

수량을 찾는 것과는 달리 동일한 항목을 찾아야하기 때문에 옵션에 0을 써준다.

=INDEX(C4:G9,C14,C15)

index함수에서는 행,열 순서로 작성해준다.

위에서 사용한 match함수값을 활용한 것이다.

=INDEX(C4:G9,MATCH(C13,B4:B9,1),MATCH(C12,C3:G3,0))

미리 구한 match함수값을 사용하는 것이 아니라 직접 index함수 안에서 match함수까지 활용하여 값을 구한다.