xls - 이름정의에서 배열 사용(VLOOKUP)

이름정의에서 배열 사용(VLOOKUP)

유효성 검사로 드랍다운 메뉴를 사용하는 때를 생각해 보자. 다른 값이 들어오는 것을 막을 때 주로 사용하지만, 특정값이 들어왔을때, VLOOKUP 함수를 사용하여, 다른 테이블을 참조한 값을 넣어줄 때, 많이 사용한다.

주로 참조를 해야하는 값의 수가 적고, 자주 변하지 않는 경우에 사용하게 되는데, 이 과정이 귀찮으므로, 대안을 검토할 가치가 있다.

아래 예시를 보자.
학생 이름이 있고, 학점이 있다. 학점은 그 수가 많지 않으므로, 드랍다운 메뉴든 뭐든 처리를 한다고 치자. 옆 열에 기재된 영문 학점에 대해 4점 환산을 하고 싶다면, IF 함수를 쓰는 방법도 있고, 간이 참조 테이블을 만들어서, VLOOKUP처리를 하는 방법도 있다.

myimg

간이테이블을 참조하는 VLOOKUP은 그 어딘가에서 간이테이블을 유지해야한다는 부담이 있다. 다른 방법인 IF 함수는 수식이 길다는 단점이 있다. 또 다른 방법을 찾아야 한다.

myimg

요즘 배열수식에 빠져있는터라, 아래 배열상수를 사용한 방법을 어디선가 보게 되었다. 이름정의를 통해 배열로 간이 테이블을 만들어 주는 방법이다. 이름정의 참조 영역에 아래와 같이 배열로 테이블은 만든다.

={“A”,4;“B”,3;“C”,2;“D”,1}

열구분은 , 행구분은 ; 로 해준다. 4행 2열로 위의 이미지와 동일한 테이블이 만들어졌다. 정의된 이름은 학점이다.

myimg

수식을 담은 이미지가 날아갔는데, 기존 VLOOKUP 함수에서 참조영역을 정의된 이름, 학점으로 변경해주연 아래와 같이 동일한 값을 얻을 수 있다.

=VLOOKUP(C6, 학점, 2, FALSE)

myimg

이로서 IF안의 IF안의 IF안의 IF 함수의 수렁에서 잠시 빠져나올 수 있다.

이름정의에서 배열상수 사용

이름정의에서 배열상수를 넣어서, 별도의 참조테이블을 사용하지 않는 방법을 얼마전 글에서 적은 적이 있다. 이를 사용하여 점수별 등급을 손쉽게 넣을 수 있다. 이는 VLOOKUP의 마지막 상수(RANGE_LOOKUP)값을 TRUE 또는 FALSE로 설정하는 것으로 가능한데, 이에 대한 이해가 필요하다.

VLOOKUP 함수의 RANGE_LOOKUP 값을 FALSE로 사용하는 경우, 완전히 값이 동일할 때 값을 반환한다. TRUE로 사용하면 유사값을 반환하는데, 이를 사용하기 위해 참조 테이블의 값이 오름차순으로 “정렬” 되어있어야 한다. 그 말은 엑셀이 순서를 예측할 수 있을 때만 사용이 가능하다는 것이다. 찾는 값은 최소값이 됨을 알고 있어야 한다.

예를 들어, 아래와 같은 표가 있다고 치면, 기준값 열은 보이는 것처럼 오름차순으로 정렬이 되어있어야한다. 내림차순으로 정렬이 되어 있을 경우, VLOOKUP에 TRUE상수를 넣었을때, 원치 않는 값을 반환한다.

myimg