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

표 기능을 활용한 이중유효성 검사 다중조건 목록상자의 원리와 실전 사용법을 단계별로 살펴봅니다.

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

작성자 :
오빠두엑셀
최종 수정일 : 2022. 08. 24. 00:00
URL 복사
메모 남기기 : (29)

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

영상강의

큰 화면으로 보기

예제파일 다운로드

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

  • [대시보드강의] 다중조건 목록상자 만들기 (데이터유효성 검사)
    완성파일
  • [대시보드강의] 다중조건 목록상자 만들기 (데이터유효성 검사)
    예제파일

강의 소개

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

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

  1. 가계부의 각 계정과목 선택 시, 소계정 항목이 자동으로 반영되는 이중유효성검사 목록상자 만들기
  2. 수입/지출 항목에 따라 위/아래 화살표가 출력되는 아이콘집합 사용법
  3. 버튼을 클릭하면 시트의 마지막셀로 자동 이동하는 버튼 만들기

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

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

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

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

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

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

도형 변하지 않음 설정
도형 속성에서 ‘변하지 않음’을 체크합니다.

2. 엑셀 가계부 항목 설정 (계정과목 및 소계정 항목)

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

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

입출금계좌 및 계정과목 구분
입출금계좌 수입 지출 이동
국민은행-급여통장 회사급여 교통비 계좌이동(+)
신한은행-생활비통장 주식배당금 식비 계좌이동(-)
우리은행-비상금통장 기타소득 주거통신비 예적금(+)
우체국-적금통장 건강문화비 예적금(-)
신한은행-Fun신용카드(7010) 경조사회비
등등..
계정과목별 소계정 구분
회사급여 주식배당금 기타소득 교통비 식비
월급 S사 배당금 유튜브수익 대중교통 주식
상여금 H사 배당금 광고수익 택시비 커피/음료
A사 배당금 주류비 간식
.. 기타교통비 기타식비
주거통신비 건강문화비 경조사회비 세금/이자 교육육아비
월세 운동/레저 경조사비 법인세 학원/교재비
관리비 여행모임 모임회비 소득세 육아용품
공과금 문화/생활 데이트비용 기타세금 등록금
인터넷비 병원비 선물구매비 대출이자 기타
기타통신비

3. 범위를 표로 변경 / 표 이름설정

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

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

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

범위를 표로 변경하는 단축키 : Ctrl + T
엑셀 표 만들기
입력한 범위를 모두 표로 변환합니다.

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

엑셀 표 이름 설정
표이름을 해당 표의 머릿글과 동일하게 변경합니다.

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

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

가계부 Raw Data 항목
날짜 구분 계정과목 소계정 입출금계좌 증감 금액 비고

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

엑셀 가계부 항목 입력 셀서식 변경
가계부 데이터 입력부분의 셀서식을 변경합니다

5. 이중 데이터유효성 검사 목록상자 만들기

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

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

  1. 구분 : 수입,지출,이동
  2. 계정과목 : =INDIRECT(동일한 행의 구분 셀 [숫자앞 $ 표시 제거])
  3. 소계정 : =INDIRECT(동일한 행의 계정과목 셀 [숫자앞 $ 표시 제거])
  4. 입출금계좌 : =INDIRECT(“입출금계좌”)
다중 데이터유효성 검사 설정
데이터유효성 기능을 통해 다중조건 목록상자를 생성합니다.

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

엑셀 다중조건 목록상자 완성
다중 데이터 유효성검사 / 다중조건 목록상자가 완성되었습니다.

6. 증감 아이콘 만들기

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

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

=IF(셀="수입",1,IF(셀="지출",-1,IF(셀="이동",0,"")))

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

  • 수입 : 1
  • 지출 : -1
  • 이동 : 0
  • N/A : 빈칸

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

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

엑셀 아이콘 집합 환경설정
증감의 아이콘 설정을 위 그림처럼 변경합니다.

7. 마지막셀 자동 이동버튼 만들기

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

마지막셀 이동버튼에 사용된 사용자지정범위 수식
=INDEX(가계부입력!$B$7:$B$1000,MATCH(9.999999999E+307,가계부입력!$B$7:$B$1000,1)+1)

4.9 19 투표
게시글평점
29 댓글
Inline Feedbacks
모든 댓글 보기
29
0
여러분의 생각을 댓글로 남겨주세요.x