다중조건 목록상자 만들기

실습 가이드

강의 소개

이번 강의에서는 표 기능을 활용한 ‘엑셀 다중조건 목록상자’ 제작 방법을 살펴봅니다. 표 기능을 활용하면 이중유효성 목록상자를 함수 없이도 매우 간단하게 구현할 수 있습니다. 이번 예제에서는 다중조건 목록상자뿐 아니라 엑셀의 아이콘 집합 기능과 마지막셀 이동 버튼 제작 방법까지 함께 다룹니다.

이번 강의에서 다루는 핵심 내용은 다음 세 가지입니다.

이번 강의에서 소개하는 표 기능 기반의 이중 유효성 목록상자는 다뤄야 할 항목 수가 많아질수록 다소 불편해질 수 있습니다.

따라서 항목이 많은 환경에서는 함수를 활용한 다중 데이터유효성 검사 목록상자를 사용하면 더 폭넓은 자동화가 가능합니다. 함수를 활용한 다중 데이터유효성 검사 목록상자에 대한 자세한 내용은 아래 관련 강의를 참고하세요.

1. 도형 옵션의 ‘변하지 않음’ 속성 설정

가계부 시트에서 셀 너비를 변경하면 해당 셀에 배치된 도형의 너비도 함께 변합니다. 이러한 동작을 막기 위해 셀 크기 변화와 무관하게 ‘도형’의 크기가 고정되도록 설정합니다.

따라서 이전 강의에서 만들었던 시트 이동용 ‘도형’의 속성을 변경해 주겠습니다.

도형을 선택한 뒤 [도형 서식]을 클릭합니다. 이어서 [도형 옵션]의 [속성] 항목에서 [변하지 않음]을 선택합니다.

다음으로 가계부의 입출금계좌, 계정과목 및 소계정 항목 관리를 위한 환경설정 시트를 작성합니다. 아래 예시를 참고할 수 있도록 정리했습니다.

예시를 참고하여 본인의 소득/지출 패턴에 맞게 계정과목과 소계정 항목을 작성해 보세요.

입출금계좌 및 계정과목 구분

계정과목별 소계정 구분

이제 ‘다중조건 목록상자’를 만들기 위해 동적범위를 설정합니다.

OFFSET 함수 동적범위를 활용하는 방법도 있지만, 이번 강의에서는 더 간단한 방법인 표 기능을 활용하여 진행합니다. 따라서 방금 입력한 각 계정과목 및 소계정 범위를 모두 표로 변환합니다.

각 범위에는 ‘머릿글’이 포함되어 있으므로, 표로 변경하는 안내창에서 ‘머릿글 포함’ 옵션에 반드시 체크해 주세요.

표를 생성한 뒤, 표가 선택된 상태에서 [디자인] – [표 이름] 항목으로 이동합니다. 이어서 각 표의 머릿글과 동일한 이름으로 표 이름을 변경합니다.

4. 가계부 자료 입력을 위한 표 작성하기

가계부 데이터가 입력될 표를 작성합니다. [가계부입력] 시트로 이동한 뒤, 아래 항목을 추가해 주세요. (아래 항목은 가계부 사용 목적에 따라 다른 항목으로 대체하여 사용할 수도 있습니다.)

가계부 Raw Data 항목

항목을 입력한 뒤, 아래 그림과 같이 표의 셀 서식을 변경합니다. 셀 서식을 변경하는 방법과 빠른 작업을 위한 단축키에 대한 자세한 설명은 영상강의를 참고하세요.

이제 [구분]·[계정과목]·[소계정] 항목에 따라 선택된 상위 값에 해당하는 하위 값만 목록상자에 표시되도록 다중조건 목록상자를 구성합니다.

각 항목별로 데이터가 입력될 범위를 선택한 후, [데이터] – [데이터 유효성 검사]로 이동합니다. [제한대상]은 ‘목록’으로 선택한 다음, [원본]에 아래 수식을 입력합니다.

위와 같이 설정을 마치면 각 상위 그룹에서 선택한 값에 따라 목록상자의 표시 항목이 자동으로 변하는 다중조건 목록상자가 완성됩니다.

이번에는 수입/지출/이동 구분에 따라 증감 화살표가 표시되도록 [조건부서식]의 [아이콘집합]을 적용합니다.

증감을 표시할 셀에 아래 IF 함수를 사용한 공식을 입력합니다.

위 공식을 입력하면 증감값에 따라 결과값이 다음과 같이 출력됩니다.

위 수식을 증감을 나타내는 모든 범위에 입력한 뒤, 해당 범위가 선택된 상태에서 [홈] – [조건부서식] – [아이콘집합] – [기타규칙]으로 이동하여 아래와 같이 아이콘집합 규칙을 설정합니다.

증감을 시각화할 때에는 숫자 없이 아이콘만 표시되도록 ‘아이콘만 표시’ 항목에 체크해 주세요. 아이콘 집합 사용법에 대한 다양한 실전 예제는 이전 관련강의에서 자세히 다루고 있습니다.

마지막으로 이전 강의에서 살펴본 ‘마지막셀을 자동으로 찾아 이동하는 버튼’을 생성합니다. 사용자가 새로운 가계부 항목을 추가하더라도 버튼을 클릭하면 자동으로 마지막셀을 찾아 이동하므로 작업 편의성이 크게 향상됩니다. 자세한 제작 방법은 관련 강의를 참고하세요.

마지막셀 이동버튼에 사용된 사용자지정범위 수식