안녕하세요! gbgg입니다. 엑셀 자산관리용 가계부 만들기 4편입니다!
이번 포스팅에서는 상위 콤보박스의 값에 따라 하위 콤보박스의 항목이 변경되도록 구현해보겠습니다.
아래 자료는 자산관리용 가계부 만들기 4~5편의 예제입니다.
상위 콤보박스의 선택 값에 따라 하위 콤보박스 항목을 변경하기
엑셀의 콤보박스 항목들은 입력 범위 설정을 통해 쉽게 지정할 수 있습니다.
그렇다면 상위 콤보박스 값에 따라 하위 콤보박스 값이 변경되도록 하려면 어떻게 구현해야 할까요?
먼저 INDEX 함수로 현재 선택한 콤보박스의 INDEX 값을 실제 항목 값으로 변경해보겠습니다.
지난 포스팅에서 콤보박스의 입력 범위와 셀 연결을 사용하여 콤보박스에서 선택한 값이 어떤 것인지 판별해보았습니다.
아래 사진을 보면 콤보박스에서 선택한 값의 INDEX가 표시되고 있는 것을 확인하실 수 있습니다.
그렇다면 현재 선택한 값이라고 표시되어 있는 '2'를 실제 항목 이름인 '출금'으로 바꾸려면 어떻게 해야할까요?
INDEX 함수를 사용하여 구현해보겠습니다.
* INDEX 함수 사용법이나 활용법은 아래 포스팅을 참조해주세요.
[Excel] 9. 엑셀로 능력있는 직장인이 되는 법. 중급편 1 (VLOOKUP, HLOOKUP, INDEX, MATCH)
=INDEX(반환 값 범위, 검색할 값)
반환 값 범위는 '구분' 항목 전체 범위를 선택하고 검색할 값은 콤보박스의 셀 연결로 지정한 셀을 선택합니다.
위와 같이 설정해준 뒤 결과를 살펴봅시다.
콤보박스에서 선택한 '출금' 값이 셀 연결을 통해 INDEX 값(2)이 셀에 전달되었고
이 셀 값을 다시 INDEX 함수를 사용하여 실제 항목 값인 '출금'으로 변환하였습니다.
이렇게 하면 콤보박스에서 선택한 항목이 어떤 것인지 확인할 수 있습니다.
이런 작업을 진행하는 이유는 콤보박스의 셀 연결로 전달된 값이 선택한 값의 INDEX 값을 반환하기 때문입니다.
이름 관리자
여기서 잠시 함수를 작성할 때 유용하게 사용할 수 있는 '이름 관리자' 기능을 한번 살펴보겠습니다.
이름 관리자를 사용하는 이유 :
엑셀 함수에서 셀에 대한 위치나 범위를 참조할 때 아래처럼 함수 내에 셀에 대한 참조가 표시됩니다.
이름 관리자는 자주 참조하는 셀에 대한 위치나 범위를 '이름'으로 지정함으로써 빠르고 간편하게 사용할 수 있으며
함수의 가독성도 올려주는 매우 유용한 기능입니다.
C언어로 표현하자면 #define처럼 정의해주는 역할이라고 생각하시면 됩니다.
복잡한 함수를 작성할 때 이름 관리자를 사용하면 보다 편하게 함수를 관리할 수 있습니다.
| =INDEX(C12:C28,C29) |
위 함수에서 빨간색으로 표시한 셀 범위를 이름 관리자 기능을 통해 참조해보겠습니다.
'이름 관리자'는 수식 메뉴에 존재합니다. 먼저 이름 관리자를 클릭해 봅시다.
위와 같은 창이 나오게 됩니다.'새로 만들기'를 선택해봅시다.
-
이름
이름을 설정합니다. 함수 내에서 A1~B2까지의 범위를 '범위 1'이라는 이름으로 쉽게 사용하고 싶다면 이 부분에 '범위 1'이라고 쓰면 됩니다. -
참조 대상
'범위 1'이 참조하고자 하는 셀의 참조대상을 설정합니다.
'구분'이라는 이름으로 다음과 같이 설정해 보겠습니다.
위와 같이 설정되었다면 '닫기'를 누릅니다.
이제 함수에서 범위를 따로 지정할 필요 없이 '구분'이라는 문구를 함수 내에서 사용하면
이름 관리자에서 설정한 C12:C28의 셀 값을 참조하게 됩니다.
위와 같이 함수 내에서의 '구분'이라는 이름이 내가 설정한 C12:C28 셀을 참조하고 있는 것을 확인할 수 있습니다.
자 이제 다음 작업으로 넘어가 보겠습니다.
위와 같이 '입금'을 선택하였을 때와 '출금'을 선택하였을 때 하위 콤보 박스의 값이 변경되도록 하려면
어떻게 해야 할까요?
오른쪽에 '선택 구분'이라는 셀을 만든 뒤 IF 함수를 사용하여 구현해보겠습니다.
| =IF($C$29=1,D12,E12) |
만약 C29 값(입금/출금)이 1(입금)이라면 D12(외식)을 출력하고 아니라면 E12(외식)을 출력합니다.
C29 셀은 절댓값을 취해주고 아래 셀에 모두 적용시킨 뒤 '구분 2' 콤보 박스와 연결시켜보겠습니다.
입금을 선택하면 구분2 항목의 입금에 대한 내용이 항목으로 설정되게 됩니다.
아래 이미지와 같은 구조로 구성됩니다.
1. 콤보박스에서 출금을 선택한다.
2. 콤보박스의 셀 연결에 연결되어있는 셀 값이 '출금'의 INDEX 값인 '2'로 출력된다.
3. IF 함수를 사용하여 콤보박스 값이 '출금'인 경우 '선택 구분'에 '출금 구분'에 있는 항목을 출력한다.
4. 하위 콤보박스에서 참조하는 입력 범위가 '선택 구분'으로 되어있으므로 하위 콤보박스 값이 변경되게 됩니다.
이름 관리자를 사용하여 작업을 완료한 모습입니다.
엑셀 자동화
이번 포스팅에서는 UI 중 많이 사용하는 콤보박스의 항목 값을 상위 콤보박스 선택 값에 따라 변경하는 방법을 작성해 보았습니다.
조금 복잡해 보이지만 위와 같이 구성하면 상위/하위 콤보박스 항목들의 값을 관리하기도 편리하며,
다른 콤보박스나 컨트롤 개체와의 관계도 보기 쉽게 정리할 수 있습니다.
궁금한 점이나 추가 요청사항은 댓글로 남겨주시면 답변 달아드리겠습니다!
능력 있는 직장인이 되기 위해 엑셀로 전산프로그램을 만들 때까지 한번 열심히 달려봅시다!
다음 포스팅에서 뵙겠습니다!
최근댓글