중괄호 안에 값을 넣어 배열 만들기 (5)

이번에는 VLookup을 사용하는데,

조견표를 배열로 입력하는 방법에  대해 알아보겠습니다.

 

vlookup 배열.xlsx

0.01MB

 

A열과 B열에 지점명과 성과급 지급률이 있고,

오른쪽에 성명과 소속 지점이 있을 때 이에 맞는 지급률을 왼쪽 표에서 찾아 입력하려고 합니다.

 

1. VLookup 함수의 구문

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

로서

 

① lookup_value는 찾으려는 값,

 

②table_array는 찾으려는 값이 첫 번째 열에 있고, 구하고자 하는 값이 들어 있는 표 형식의 배열 또는 범위입니다. 표 형식이라고 해서 삽입 탭 아래에 있는 표여야 하는 것은 아니고(표라도 상관은 없음),

 

우리가 일반적으로 많이 접하는 가로와 세로로 구성된 표, 위 지점별 상여금 지급률 표를 생각하면 됩니다.

 

③col_index_num는 표에서 구하고자 하는 값이 있을 열의 순번으로 왼쪽부터 1,2,3,4가 됩니다.

 

④ [range_lookup]은 대괄호로 둘러싸여 있기 때문에 옵션인데, 기본값(default option)이 근삿값 찾기이므로, 정확한 값을 찾을 때는 생략하면 안 되고 False 또는 0이라고 입력해야 합니다.

 

2. 일반적인 VLookup 함수 사용법

위에서 개인별로 상여금 지급률을 구하고자 한다면

 

=VLOOKUP(E2,$A$2:$B$4라고,

 

=vlookup( 다음에

찾으려고 하는 값인 지점명이 들어 있는 E2셀을 입력하고, table_array(표_범위)를 지점명과 지급률이 있는 A2셀에서 B4셀까지를 마우스로 끌어서 선택하는데, 수식을 다른 셀에 붙여 넣을 때 범위가 변경되면 안 되므로 F4키를 눌러 절대 참조 형식으로 입력해야 합니다.

 

그리고, 세 번째 인수로 표_범위에서 지급률을 지정하는데 두 번째이므로 2를 입력하고,

 

네 번째 인수는 유사 일치와 정확히 일치인데, 정확히 일치하는 지점명을 찾아야 하므로 False로 이동한 다음 탭 키를 누르거나 마우스로 더블 클릭해서 입력하고 (괄호를 닫고) 엔터키를 누릅니다.

완성된 수식은

=VLOOKUP(E2,$A$2:$B$4,2,FALSE)

입니다.

 

A지점에 해당하는 지급률 70%에 해당하는 0.7이 구해졌습니다.

 

숫자 표시를 백분율 표시로 바꾸기 위해 

홈 탭 > 표시 형식 그룹에서 % 아이콘(명령)을 누릅니다.

 

표시 형식이 0.7에서 70%로 변경되었습니다.

 

이제 F2셀의 오른쪽 아래 네모 모양의 채우기 핸들을 더블 클릭하면 6명의 지급률이 모두 구해집니다.

 

3. table_array를 중괄호를 이용해 입력하기

중괄호 안에 값을 입력할 때 가로 방향은 쉼표(,)를 사용하고,

세로로 바꿀 때는 세미 콜론(;)을 사용한다는 것을 아래 글에서 살펴본 바 있습니다.

 

https://lsw3210.tistory.com/547

중괄호 안에 값을 넣어 배열 만들기 (1)

 

따라서, 위 지점별 지급률 표를 배열로 입력하면

{"A",70%;"B",80%;"C",90%}가 됩니다.

 

지점명과 지급률은 옆으로 되어 있으므로 쉼표를 이용해 입력하고

지점이 바뀔 때 세미 콜론을 사용했고, 마지막에는 줄이 바뀌지 않으니 세미 콜론이 없습니다(오히려 마지막에 세미 콜론을 넣으면 에러 발생).

 

이제 table_array 자리에 위 배열을 복사해서 붙여 넣고

=VLOOKUP(E2,{"A",70%;"B",80%;"C",90%},2,FALSE)

엔터 키를 누르면 아래와 같이 '수식에 문제가 있습니다.'란 에러 메시지가 표시되고, 

확인 버튼을 누르면

 

70%를 가리키고 있습니다. 시트에서는 70%가 되는데, 배열에서는 70%라고 하면 안 되는 듯합니다.

70%, 80%, 90%를 0.7, 0.8, 0.9로 바꿔서

 

=VLOOKUP(E2,{"A",.7;"B",.8;"C",.9},2,FALSE)라고 수정한 후 엔터 키를 누르면

(0.7을 .7로 0을 생략해도 됨)

 

70%가 잘 구해집니다.

 

다시 F2셀의 채우기 핸들을 더블 클릭하면 F7셀까지 수식이 모두 바뀌고 값이 동일하게 구해집니다.

 

셀 주소를 참고하지 않고 값이 직접 들어가 있으므로 절대, 상대 참조의 개념도 없습니다.

 

이제 A2셀에서 B4셀까지를 참고하지 않으니 지워도 F열의 값이 오류가 발생하지 않고 그대로 있습니다.

 

4. 배열을 쉽게 만들기

위와 같이 쉼표와 세미 콜론을 이용해 배열을 입력할 수도 있지만 너무 어렵습니다.

그래서 쉬운 방법을 찾아보겠습니다.

 

먼저, 아래와 같이 table_array가 범위로 되어 있는 이전의 상태로 돌아가기 위해

 

Ctrl + Z키를 여러 번 누릅니다.

 

그리고, 수식에서 $A$2:$B$4를 마우스로 끌어서 선택한 후

 

F9 키를 누릅니다.

 

그러면 위에서 입력했던 내용인

{"A",0.7;"B",0.8;"C",0.9} 으로 바뀝니다.

 

이제 엔터 키를 누르고, F2셀의 채우기 핸들을  누르면 F7셀까지 수식이 모두 배열 형식으로 쉽게 바꿀 수 있습니다.

 

마찬가지로 A열부터 C열까지 선택한 후 마우스 오른쪽 버튼을 누르고 삭제 메뉴를 눌러

조견표가 포함된 열을 삭제해도 지급률을 배열을 이용해 구했기 때문에 변하지 않습니다.

 

오히려 깔끔하지 않습니까?

 

단점이라면 조견표를 이용하면

간단하게 표에서 값을 수정하면 되는데,

배열인 경우는 수식의 배열 값을 일일이 바꿔줘야 한다는 것입니다.

따라서, 추천하지는 않습니다.

 

vlookup 배열(완성).xlsx

0.01MB