1. 엑셀 동적참조 그래프 예제파일
엑셀 동적참조 그래프 – 선택에 따라 참조범위가 변하는 그래프- 는 위에 링크를 통해 다운로드 받아서 연습해보세요. Offset 함수와 유효성검사를 조합해 만들어 보았습니다.
예제 파일에 있는 그래프 처럼, 그래프는 하나만 그려 놓고, 사용자가 선택한 Cost Category 에 따라 자동으로 차트가 변하게 만들 수 있다. 생각보다 간단하게 만들 수 있지만… 엑셀을 잘 모르는 분들에게는 일단 차트 부분이 내 선택에 따라 자동으로 업데이트 되니… 이런 엑셀 동적참조 차트를 만들면 이제 엑셀 활용에 있어 회사에서 “오오~ 꽤 하네?” 라는 반응을 이끌어 낼 수도 있습니다.
2. 예제파일 설명
예제파일을 여시면 아래와 같이 Cost category A, B, C 에 해당하는 표가 있습니다.
가장 먼저, 아래 그림에 보이는 것 처럼, B18:D18 영역에 Cost category 를 선택하는 란을 만들었습니다. 노란색으로 하이라이트 했으니 쉽게 찾으실 수 있을 거예요. 그리고 페이지에서 벗어난 곳에다 Cost Category A, B, C 를 입력하고 셀 주소란에다 Cost 라고 쳐서 간단히 이름 정의를 해주었습니다.
그리고 난 후, 메뉴에서 데이터 – 데이터 유효성 검사를 클릭하면
아래 그림과 같이, 데이터 유효성 창이 뜹니다. 여기서 제한 대상에는 목록을 선택해주시고, 원본에는 아까 정의한 이름인 cost 를 입력해 주고 확인을 누릅니다.
그럼, 이제 부터는, 셀 D18 을 클릭하게 되면 A, B, C 목록 중에서 선택할 수 있게 됩니다.
이제, 다시 한 번 페이지에서 벗어난 구역으로 가서… 아래와 같이 그래프 용의 표를 또 하나 만듭니다. 여기서 핵심 포인트는 이 표의 값들이 셀 D18 값 선택에 따라 변하게 만들어야, 그래프도 자동으로 변한다는 것 입니다. 따라서 R23:AC23 범위의 테이블에는 다음과 같이 offset 과 vlookup 을 조합한 함수를 사용해야 합니다.
=OFFSET($C$4,VLOOKUP($D$18,$Q$17:$R$19,2,FALSE),COLUMN()-18)
Offset 함수의 가장 간단한 용법입니다. 시작할 셀과, 행에 해당하는 숫자, 열에 해당하는 숫자를 차례로 입력해서 엑셀 위에 참조할 셀 위치의 좌표를 찍는다고 생각하신다면 좀 이해하기 편할 겁니다.
위의 수식을 찬찬히 해석해보면, C4에서 시작하여, vlookup 으로 나올 숫자 만큼 아래로 행을 건너뛰고… column()-18 로 나올 숫자만큼 오른쪽으로 열을 건너뛴… 그 자리에 위치한 셀이 가지고 있는 값을 참조하라는 것입니다.
예를 들어, D18 Cost Category 에서 C를 선택했다면, C4 에서 vlookup 이 참조하는 표에 따라 10 행 만큼 아래로 내려올테니… C14가 되고… R23 에서의 column()-18 은 결국 18-18=0 이 되므로, 열은 제자리… 따라서 C14 셀의 값을 가져오게 되는 것입니다.
여기까지 이해하셨다면, 거의 90%는 이해하신 겁니다. 이제는 예제 파일을 직접 보면서 R24:V24 범위의 식은 직접 만들어 이해해보세요!
3. 마무리
참, 페이지 밖에 있는 글자들은 되도록 배경색과 비슷한 색을 선택하여 잘 안 보이도록 하는 게 깔끔합니다. 하지만 예제 파일에서는 공부해야 하는 데 잘 못찾는 분들이 있을까봐, 그냥 검은색으로 두었습니다.
엑셀 동적참조그래프 이외에 다른 쉽고 유용한 엑셀 팁들을 보고 싶으다면 아래 링크 클릭해주세요!