VLOOKUP과 동적 data 범위를 사용한 실무 예제

제가 실무에서 사용하고 있는 "Vlookup"과 "동적 data 범위"를 설정하는 방법에 관한 포스팅입니다.

 

- VLOOKUP : 조건에 맞는 값을 자동으로 가져옵니다.
- 동적 DATA범위 : 변화(늘어나거나 줄어드는)하는 DATA범위를 자동으로 계산합니다.

 

 

아래 차트와 같이 매번 새로운 Data가 아래로 생성되며, 이 data를 기반으로 여러 보고서(페이지)들이 만들어진다고 가정해 보겠습니다. 

매 보고서마다 범위의 값을 입력할 필요 없이 자동으로 범위의 값이 적용되도록 만들어 보겠습니다.

 

 

 

아래 EX1과 같이 VLOOKUP 함수의 범위를 지정할 때 아주 넓은 범위를 줘도 동작은 합니다만, 만들다 만 레고 같은 느낌으로 이런류의 함수를 많이 사용할 경우 메모리에 영향을 줍니다.

깔끔하지 않아 용납이 안됩니다. ^^

 

EX 1:)

 

=VLOOKUP(B1,data!C2:J9999,3,1)

B1(111) 의 값을 확인 후 data 시트의 범위 C1:J9999 의 3번째 행의 값(E3의 0.37)을 가져옵니다.

경우에 따라 숫자 3을 4,5,6,.....으로 적용해 이용하면 됩니다. 

 

- 참고로 data는 시트 이름입니다. 시트이름을 불러올 때는! 를 사용합니다. 해서 data! 이렇게 사용합니다.

- VLOOKUP함수의 마지막에 사용한 1은 정확하게 일치하는 값을 가져오라는 의미입니다.

 

 

이제 VLOOKUP의 사용방법은 알았으니 동적 DATA를 적용하는 방법에 관하여 설명하겠습니다.

1. "이름 관리자"를 오픈합니다. (Ctrl + F3)

 

 

 

2. "새로 만들기"  이름은 원하는 것으로... 저는 "aggibba"로 했습니다.

 

 

 

참조 대상영역에는 아래의 함수를 입력합니다.

=OFFSET(data!$C$2,0,0,COUNTA(data!$A:$A),8)

 

 

처음? 보는 함수 "OFFSET"과 "COUNTA"가 보이네요... 별것 없습니다. 미리 겁먹지 마세요.

- OFFSET 함수는 전, 후, 좌, 우 어디의 위치 값을 가질지를 정할 때 사용하는 함수입니다.

- COUNTA 함수는 원하는 범위 내 셀 개수를 반환하는 함수입니다. (말이 조금 어렵나요)

 

해서 위의 함수를 말로 설명하자면

data!라는 시트에서 C2를 기준으로 A:A행이 가지고 있는 data숫자만큼 오른쪽으로 8칸까지의 범위가 되겠습니다.

COUNTA함수 덕분에 A의 DATA가 늘어나도 정확한 값을 가져오게 됩니다.

마지막 8의 의미는 C1에서 오른쪽으로 8칸까지가 범위라는 뜻입니다. DATA 범위를 넓히거나 좁히고 싶을 때 값을 바꿔주면 됩니다.

 

이제 =VLOOKUP(B1,data!C2:J9999,3,1)를

=VLOOKUP(B1,aggibba,3,1) 으로 바꿔주면 모든 작업 끝입니다.

DATA 범위가 어떻게 변하더라도 적용 가능하시겠지요 ^^

 

그럼 이만~~

공감과 댓글은 글쓴이에게 많은 힘이 됩니다.