엑셀에서 대중소 자동 연결되는 드롭다운 만들기 VBA 한 번으로 설정 완료

엑셀 동적 다중 목록상자 만드는법

 

 

회사에서 매출 정리나 비용 정산하다 보면 선택한 항목에 맞춰 다음 단계 선택지가 바뀌는 구조가 필요할 때가 많습니다.
대분류를 고르면 중분류가 달라지고, 다시 중분류를 고르면 소분류가 바뀌는 방식입니다.

보통 이걸 구현하려고 검색하면 이름 정의부터 OFFSET, INDIRECT, MATCH, FILTER 같은 함수 조합이 끝도 없이 따라옵니다.
버전마다 동작도 달라 시간만 계속 흘러갑니다.

여기서는 이런 복잡한 절차를 전부 배제합니다.

한 번만 VBA 코드를 넣어두면 이후로는 카테고리 표만 적고 버튼을 누르면 대중소 분류 목록이 자동으로 연결되는 구조가 만들어집니다.
엑셀 초보도 바로 적용할 수 있는 방식으로 흐름을 다시 정리해드립니다.

 

https://youtu.be/d_8aGZDLOHA?si=pHtmqnsKOwpOAnh4

 

 

 

1. 우리가 만들 최종 모습

예제로 “매출 시트” 하나를 가정해 볼게요.

• B열: 날짜
• C열: 구분
• D열: 내용
• E열: 금액
• F열: 대분류 선택용 목록 상자
• G열: F열에서 고른 대분류에 맞는 중분류 목록 상자
• H열: G열에서 고른 중분류에 맞는 소분류 목록 상자

최종적으로는 이렇게 동작합니다.

• F3 셀을 눌러서 대분류(예: 근로소득)를 선택
• G3 셀을 눌러보면, 근로소득에 해당하는 “기본급 / 상여금 / 수당”만 목록에 뜸
• G3에서 “상여금”을 선택
• H3 셀을 눌러보면, “명절 상여금 / 근속 상여금 / 성과 상여금”처럼 상여금에 해당하는 소분류만 딱 맞게 뜸

중간에 데이터가 늘어나거나 바뀌어도, 버튼만 한 번 다시 눌러주면 목록이 알아서 다시 세팅됩니다.

---

2. VBA 코드 넣기 (생각보다 진짜 간단함)

먼저 코드부터 넣어야 합니다. 이 부분에서 겁먹으실 수 있는데, 실제로 하는 행동은 복사–붙여넣기 딱 두 번입니다.

1) VBA 편집기 열기

• 키보드에서 Alt + F11을 눌러서 VBA 창을 엽니다.

2) 1번 코드 붙여넣기 (일반 모듈)

• 왼쪽 프로젝트 창에서 현재 파일을 선택
• 마우스 오른쪽 클릭 → 삽입 → 모듈
• 빈 화면에 “1번 코드”를 그대로 붙여넣기

3) 2번 코드 붙여넣기 (ThisWorkbook)

• 왼쪽에서 “ThisWorkbook(또는 ‘이 통합 문서’로 보일 수 있음)”을 더블 클릭
• 거기에 “2번 코드”를 그대로 붙여넣기

코드 내용은 글 하단이나 설명란에 따로 정리해서 두시면 됩니다. 실제 작업은 그냥 복붙이 전부예요.

---

3. 카테고리 시트 자동 생성하기

코드를 넣었으면 이제 엑셀로 돌아옵니다.

1. 매크로 실행

• 개발 도구 탭 → 매크로 → “카테고리 시트 자동 생성” 실행
• (이 이름은 코드에 따라 다를 수 있는데, 영상/예제 기준으로는 이런 식으로 되어 있음)

2. “카테고리” 시트 확인

실행하면 새로운 시트가 하나 생기는데, 이름은 “카테고리”로 되어 있을 겁니다.

이 시트에는 위쪽에 회색 표 하나, 아래쪽에 초록색 표 여러 개가 보일 거예요.

• 위쪽 회색 표: 설정값을 관리하는 관제탑
• 아래쪽 초록색 표: 실제 대분류/중분류/소분류를 적는 구간

이 두 가지만 잘 이해하시면 끝입니다.

---

4. 관제탑(회색 표) 설정하기

회색 표에는 대략 이런 정보들이 들어갑니다.

• 적용할 시트 이름: 매출 시트에 적용하려면 “매출”
• 사용할 카테고리 세트 이름: 예를 들어 “카테고리1”
• 대분류가 들어갈 범위: 예) F3:F100
• 중분류가 들어갈 범위: 예) G3:G100
• 소분류가 들어갈 범위: 예) H3:H100

중요한 포인트 몇 가지만 짚어볼게요.

• 시트 이름은 실제 시트 이름과 정확히 일치해야 합니다.
• 범위는 본인이 원하는 만큼 잡으시면 됩니다. F3:F100이든 F3:F1000이든 상관 없음.
• 만약 “소분류까지는 필요 없고, 대분류–중분류 2단계까지만 쓰겠다”면, 소분류 범위를 비워두면 됩니다. 그러면 2단계까지 동적으로 움직이는 목록만 만들어져요.

---

5. 카테고리 입력 규칙 이해하기

이제 아래쪽 초록색 표에서 “카테고리1”이라고 적힌 부분을 찾습니다. 여기가 실제 계층 구조를 적는 구간입니다.

예를 들어 이런 식입니다.

• 대분류: 근로소득
• 그 아래 줄들: 기본급 / 상여금 / 수당
• 중분류 상여금 아래: 명절 상여금 / 근속 상여금 / 성과 상여금

여기서 중요한 규칙은 “공란으로 구분한다”는 점입니다.

예를 들어,

• W라는 대분류를 적고
• 그 아래에 중분류들을 쭉 적다가
• 다른 대분류로 넘어갈 때 한 줄을 비워주면, 그 비어 있는 줄이 “여기까지가 W의 중분류”라는 경계 역할을 합니다.

마찬가지로, 중분류 아래 소분류를 적을 때도 다음 중분류로 넘어가기 전에 공란 한 줄을 두면,

코드가 그걸 보고 “아, 여기까지가 이 중분류의 소분류구나” 하고 인식합니다.

정리하면,

• 대분류 → 바로 아래 줄부터 해당 중분류들
• 중분류 → 바로 아래 줄부터 해당 소분류들
• 각각의 그룹 끝에는 공란 한 줄

이 패턴만 지켜주시면 됩니다.

---

6. 버튼 한 번으로 목록 상자 적용하기

카테고리까지 다 적었으면 이제 진짜 마무리입니다.

• 카테고리 시트 오른쪽에 보시면 “목록 상자 적용” 같은 버튼이 하나 있을 거예요.
• 이 버튼을 한 번만 클릭하면, 방금 설정한 내용 기준으로 매출 시트(F, G, H열)에 데이터 유효성 검사(목록 상자)가 자동으로 걸립니다.
• “설정이 완료되었습니다” 같은 메시지가 뜨면 끝난 겁니다.

이제 매출 시트로 돌아가서 F3, G3, H3 셀을 하나씩 눌러보시면 됩니다.

• F열: 대분류 목록
• G열: F열에서 선택한 값에 맞는 중분류만 표시
• H열: G열에서 선택한 값에 맞는 소분류만 표시

중간에 카테고리를 수정하거나 추가하셨다면,

카테고리 시트에서 내용을 고치고 다시 “목록 상자 적용” 버튼만 눌러주시면 바로 반영됩니다.

함수 고칠 필요도 없고, 이름 정의 다시 손댈 필요도 없습니다.

---

이 방식은 이런 분들께 특히 유용합니다.

• 회사에서 매출/비용/계정과목을 층층이 나눠서 관리해야 하는 분
• 엑셀 버전이 제각각이라 FILTER 함수, 동적 배열 함수를 쓰기 애매한 팀
• INDIRECT, OFFSET 같은 함수 보고 머리 아파서 포기해 보신 분
• “나중에 구조 바뀔 때마다 함수 다시 손대기 싫다” 하는 분

​

한 번 세팅해 두면, 나중에는 “카테고리만 채우고 버튼만 누른다”는 루틴으로 끝나기 때문에, 실무에서 반복 작업 줄이는 데 꽤 큰 도움 됩니다.

나중에 기회 되면, 이 구조에 맞춰 예제 파일과 코드도 같이 정리해서 공유드리겠습니다.

복잡한 함수 때문에 야근하지 마시고, 이런 자동화 한 번씩 써먹어 보시면 좋겠습니다!