다중조건 목록상자, 표로 간단하게 만들기 :: 이중유효성 검사

엑셀 다중조건 목록상자 (이중 유효성 목록상자) 만들기

영상강의

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

강의 소개

이번 강의에서는 표기능을 활용한 '엑셀 다중조건 목록상자' 제작방법에 대해 알아봅니다. 표기능을 활용하면 이중유효성 목록상자를 함수를 사용하지 않고 아주 간단하게 제작할 수 있는데요. 이번 강의 예제에서는 '다중조건 목록상자' 뿐만 아니라, 엑셀의 아이콘집합 기능과 마지막셀 이동 버튼 제작 방법을 같이 소개해드렸습니다.

이번 강의에서는 아래 3가지 내용을 살펴봅니다.

이번 강의에서 소개해드린 표기능을 활용한 이중 유효성 목록상자는 많은 항목을 다뤄야 할 경우 다소 불편할 수 있습니다.

따라서 많은 항목을 동시에 다뤄야 할 경우에는 함수를 활용한 다중 데이터유효성 검사 목록상자를 사용하면 더욱 광범위하게 자동화할 수 있는데요. 함수를 활용한 다중 데이터유효성 검사 목록상자에 대한 자세한 내용은 아래 관련강의를 참고해주세요.

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

가계부 시트의 셀 넓이를 변경할 경우, 해당 셀에 위치한 도형의 넓이가 같이 변하게 됩니다. 따라서 셀 넓이가 변하더라도, ‘도형’의 크기는 고정되도록 설정합니다.

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

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

가계부의 입출금계좌, 계정과목 및 소계정 항목 관리를 위한 환경설정 시트를 작성합니다. 아래 예시를 적어드렸습니다.

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

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

계정과목별 소계정 구분

'다중조건 목록상자'를 만들기 위해 동적범위를 만들어줍니다.

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

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

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

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

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

가계부 Raw Data 항목

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

[구분] 과 [계정과목] 그리고 [소계정]에 따라 선택된 항목의 하위 값만 목록상자에 나타나도록 다중조건 목록상자를 만들어줍니다.

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

위와 같이 설정을 완료하면 각 상위그룹에 선택된 값에 따라 목록상자의 값이 변하는 다중조건 목록상자가 완성됩니다.

수입/지출/이동에 따라 증감을 보여주는 화살표가 나오도록 [조건부서식]의 [아이콘집합]을 적용합니다.

증감을 나타내는 셀에 아래 IF함수를 사용한 공식을 입력합니다.

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

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

증감을 나타낼 때에는 숫자 없이 아이콘만 표시되도록 ‘아이콘만 표시’ 항목에 체크해주세요. 아이콘집합 사용법에 대한 더 다양한 실전예제는 이전 관련강의에서 자세히 설명해드렸습니다..

이전 강의에서 알아본 ‘마지막셀을 자동으로 찾아 이동하는 버튼’을 생성합니다. 사용자가 새로운 가계부 항목을 추가하더라도, 버튼을 클릭하면 자동으로 마지막셀을 찾아 이동하게 되어 편의성이 향상됩니다. 이와 관련된 내용은 관련 강의를 참고하세요.

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