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

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

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

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

영상강의
큰 화면으로 보기

예제파일 다운로드

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

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

강의 소개

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

이번 강의에서는 아래 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 17 투표
게시글평점
guest
26 댓글
Inline Feedbacks
모든 댓글 보기
yotabyte
yotabyte
2020년 2월 5일 6:53 오후
게시글평점 :
     

마지막셀로 이름정의하고 이미지 클릭 시, 제대로 이동하는 것을 확인했는데, 링크 도형을 실수로 삭제하고 신규 도형으로 만들고 링크를 만들려고 하는데 "정의된 링크"에 "마지막셀"이 보이지 않습니다. 분면 이름관리자에서 보면 "마지막셀"이 정의되어 있습니다.… 더보기 »

yotabyte
yotabyte
2020년 2월 6일 3:55 오후
답글 남기기  오빠두엑셀

감사합니다. 많은 도움 됐습니다.

dyhbt
dyhbt
2020년 2월 12일 11:52 오후
게시글평점 :
     

예제 엑셀 파일이 다른 내용의 엑셀 파일인 것 같습니다. 아마도 대시보드 1강의 엑셀 파일인 것 같네요.
PDF 파일은 맞는 것 같구요. 확인 부탁드립니다.

준아아빠
준아아빠
2020년 3월 2일 7:24 오후
게시글평점 :
     

감사합니다.. ^^

엑셀종배
엑셀종배
2020년 4월 6일 10:07 오후
게시글평점 :
     

예~전에 보고 한동안 잊고 있었는데, 좋은 강의를 통해 더 깨달은 바가 있는 것 같습니다. 좋은 강의 감사드려요~

갓쩌리
갓쩌리
2020년 4월 9일 7:08 오후
게시글평점 :
     

정말 유용한 정보이네요!!

이혜
이혜
2020년 4월 11일 4:31 오후
게시글평점 :
     

형님

구독자
구독자
2020년 4월 20일 5:27 오후
게시글평점 :
     

연도를 다중목록에 넣으려고 하니 표이름을 연도(숫자로 시작)로 할 수 없을때는 어떻게 해야 하나요?

빛돌이
2020년 5월 9일 12:56 오전
게시글평점 :
     

재미있네요

f9****
2020년 5월 13일 2:01 오후
게시글평점 :
     

3번째 강의 수강완료.
다중조건목록상자 이제 완전히 이해를 했습니다. 정말 감사합니다.
많은 도움이 될 것 같습니다.

맛있는 감자
맛있는 감자
2020년 5월 23일 5:53 오후
게시글평점 :
     

강사님 설명이 조금 빨라서 그렇지 ..아주 요용한 강의 잘 배우고 있습니다. 감사합니다. 하나 질문이 있습니다. 상기와 같이 서식을 일자별로 다른 시트로 만들어서 사용하고 있습니다 그런데 월말에 각 계정과목별로 누적합계를 구하고… 더보기 »

므나두
므나두
2020년 6월 23일 1:19 오후
게시글평점 :
     

저는 왜 예제와 완성파일 모두 다운로드하면
'시트이동 버튼 만들기' 완성파일이 뜰까요..?
혹시 이메일로 예제와 완성파일을 따로 받을 수 있을까요?

Last edited 1 년 전 by 므나두
광호님만만세
광호님만만세
2020년 11월 3일 1:08 오전
게시글평점 :
     

안녕하세요? 매번 업무에 크게 도움 받고 있어 고맙습니다. 한번 더 큰 도움 부탁드립니다.^^ 표를 생성한 뒤, 표가 선택된 상태에서 [디자인] – [표 이름] 에서 표이름에 공란을 삽입하여 구분하고 싶은데 오류로 작성이 불가… 더보기 »

Last edited 1 년 전 by 광호님 만만세
정상호
정상호
2021년 1월 11일 3:03 오후
게시글평점 :
     

항상 도움받고있습니다 감사합니다.
위에 예제파일 및 E-BOOK 이라고 다운로드 공유해주셨는데
예제파일은 다운했으나 E-BOOK은 어디서 다운할수있나요?
매번 양질의 강의 올려주셔서 감사합니다

달빛따라
달빛따라
2021년 1월 12일 11:04 오후
게시글평점 :
     

열심히 따라하고 있습니다~
무작정 사용했던 엑셀이 이렇게 사랑스러울 수가...
재밌어서 계속 따라해봅니다. 날새는 줄 모르고...
감사합니다!

하이랜더
하이랜더
2021년 3월 18일 8:56 오전
게시글평점 :
     

넘나 좋은 강의

뭉뭉이
뭉뭉이
2021년 4월 7일 4:23 오후
게시글평점 :
     

유용한 강의 항상 감사하게 보고 있습니다. 문의사항이 하나 있는데요, 기존에 입력(선택된) 다중 목록 상자에서 상위 항목을 바꾸면 하위 항목은 그대로 남아 있는데, 초기화(빈셀) 시키는 방법은 없을까요? 예를 들어 완성 파일에서… 더보기 »

이현주
이현주
2021년 6월 3일 4:05 오후
게시글평점 :
     

안녕하세요 선생님, 강의해 주신 내용을 바탕으로 업무에 사용할 report 를 만들고 있는데, '원본은 현재 오류상태입니다. 계속하시겠습니까?' 라는 오류가 계속 나타나고 있습니다. 유효성 조건에서 목록으로 선택시 원본이 되는 '표'가 갖고 있어야… 더보기 »

26
0
여러분의 생각을 댓글로 남겨주세요.x