엑셀 절대참조(채우기에서 범위 고정하기)

지난 포스팅에서는 엑셀의 VLOOKUP과 자동 채우기를 하여 업무시간을 단축시키는 방법에 대해 알아봤습니다.

엑셀 VLOOKUP을 이용하여 성적 등급 찾기

엑셀 VLOOKUP을 이용하여 성적 등급 찾기

 

해당 포스트 마지막에 절대 참조에 대해 말씀드린 적이 있는데 이제야 포스팅합니다.

엑셀에서  채우기 핸들을 이용하여 빈 셀을 자동으로 채우면 상대 참조로 데이터를 참조하기 때문에 생각처럼 결과가 나오지 않습니다.

 

엑셀 채우기 핸들을 이용하여 채웠을 때 범위 오류

 

L열이 결과고 M열에는 L열에 들어가는 수식이 있습니다.

L열을 보시면 세 번째 행부터 #N/A가 나오고 있습니다.

등급에 맞는 데이터를 못 찾았기 때문입니다.

L열에서 VLOOKUP의 인자로 입력되는 내용의 값이 자동으로 증가했기 때문에 빨간 사각형 안의 조회하려 하는 값의 좌표는 증가하는 것이 맞습니다.

그러나 등급에 따른 점수가 있는 표의 범위는 H2:I6로 고정되어야 하는데 증가함으로써 참조해야 하는 표가 엉뚱한 곳을 가리키고 있는 것을 보실 수 있습니다.

이런 경우 절대 참조를 사용합니다.

 

---

엑셀 절대 참조를 이용한 VLOOKUP

절대 참조는 $만 기억하면 됩니다.

=VLOOKUP(K2,$H$2:$I$6,2,TRUE)

엑셀에서 절대 참조를 하는 경우 $표시를 사용합니다.

예시에서 VLOOKUP에서 참조하는 테이블의 범위가 변하면 안 되므로 $을 행과 열에 붙여주었습니다.

$은 변하면 안 되는 좌표에 붙여주시면 됩니다. 열이 변하면 안 된다면 $H2, 행이 변하면 안된다면 H$2등으로 응용할 수 있습니다.

위 식을 적용시키고 자동 채우기를 하면 아래와 같이 정상적으로 동작하는 것을 확인할 수 있습니다.

엑셀 절대 참조를 이용한 VLOOKUP

 

---

HLOOKUP과 XLOOKUP

지금까지는 세로로 데이터를 찾았기 때문에 VLOOKUP(Vertical)을 사용했으며 가로로 된 데이터는 HLOOKUP(Horizontal)을 사용합니다. 문법은 VLOOKUP과 유사합니다.

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

인수의 의미 또한 VLOOKUP과 같으니 생략 하겠습니다.

V/HLOOKUP은 index가 숫자로 고정되어 있기 때문에 행이나 열이 추가되면 index를 일일이 변경해 주어야 합니다.

또한 lookup_value는 첫 번째 행이나 열에서 조회해야 하고 테이블 전체가 table_array가 되므로 참조하는 테이블 구성에 한계가 있습니다. 그에 따른 성능과 가독성 저하는 덤입니다.

다음 포스팅에서는 이러한 문제를 해결할 수 있는 XLOOKUP에 대해 알아보겠습니다.