엑셀 – 그래프: 선택에 따라 참조 범위가 자동으로 변하는 그래프_offset_vlookup_유효성 검사

선택에 따라 참조 범위가 변하는 그래프.xlsx

0.02MB

대시 보드 만들다가, 공간이 부족한 관계로,

 

그래프는 하나만 그려 놓고, 보는 사람이 선택한 Cost Category 에 따라 자동으로 변하는 그래프를 그려 보았다. 간단한데… 엑셀을 잘 모르는 분들에게는 일단 그림이 자동으로 움직이니깐… "오오~ 꽤 하네?" 라는 반응을 이끌어 낼 수도 있다...

일단 아래와 같이 Cost category A, B, C 에 해당하는 표가 있다고 하자.  

 

 

 

가장 먼저, 위 그림에 나온 것처럼, B18:D18 영역에 Cost category 를 선택하는 란을 만들었다. 그리고 페이지에서 벗어난 곳에다 Cost Category A, B, C 를 입력하고 셀 주소란에다 Cost 라고 쳐서 간단히 이름 정의를 하였다.

 

 

 

그 후, 메뉴에서 데이터 à 데이터 유효성 검사를 클릭하여, 위 그림과 같이, 제한 대상에 목록을 원본에는 아까 정의한 이름인 cost 를 입력하여 주면…

​

 

 

D18 을 클릭하게 되면 A, B, C 목록 중에서 선택할 수 있게 된다.

 

이제, 또 페이지에서 벗어난 구역으로 가서… 아래와 같이 그래프 용의 표를 또 하나 만들자. 여기서 핵심 포인트는 이 표의 값들이 선택에 따라 변하게 만들어야, 그래프도 자동으로 변한다는 것이다. 따라서 R23:AC23 범위에는 다음과 같이 offset 과 vlookup 을 조합한 함수를 사용하였다.

 

 

 

=OFFSET($C$4,VLOOKUP($D$18,$Q$17:$R$19,2,FALSE),COLUMN()-18)

Offset 함수의 가장 간단한 용법인데… 시작할 셀과, 행에 해당하는 숫자, 열에 해당하는 숫자를 차례로 입력해서 엑셀 위에 참조할 셀 위치의 좌표를 찍는다고 생각하면 좀 이해하기 편하다.

 

위의 수식을 해석해보면, C4에서 시작하여, vlookup 으로 나올 숫자 만큼 아래로 행을 건너뛰고… column()-18 로 나올 숫자만큼 오른쪽으로 열을 건너뛴… 그 자리에 위치한 셀이 가지고 있는 값을 참조하라는 것이다.

예를 들어, Cost Category 에서 C를 선택했다면, C4 에서 vlookup 이 참조하는 표에 따라 10 행 만큼 아래로 내려올테니… C14가 되고… R23 에서의 column()-18 은 결국 18-18=0 이 되므로, 열은 제자리… 따라서 C14 셀의 값을 가져오게 되는 것이다.

R24:V24 범위의 식은 첨부 파일을 보면서 연습해 보시길…​

​

 

참, 페이지 밖에 있는 글자들은 배경색과 같은 색을 선택하여 안 보이게 하여, 깔끔하게 마무리 하도록 하자. 혹시 찾는 데 어려움을 겪는 분들이 있을까 하여 첨부 파일에서는 그냥 검은색으로 놔두었다.