엑셀 자동으로 업데이트 되는 다이나믹 목록 만들기
엑셀 다이나믹 목록 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 엑셀 다이나믹 목록상자 만들기예제파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
FILTER + "#" 기호로 하위 목록상자 만들기
엑셀로 대량의 데이터를 관리하다 보면 마주하는 가장 큰 고민 중 하나가 바로 '연결된 드롭다운 목록'을 만드는 것입니다. 상위 항목을 선택하면 그에 맞는 하위 항목을 필터링해서 선택하는 기능인데요.
오늘 강의를 마치면 상위-하위로 연결되는 다이나믹 목록상자를 만들 수 있습니다. 과거에는 이런 다이나믹 목록(=다중 유효성 검사)을 만들려면 복잡한 함수나 VBA 매크로가 필요했지만, M365 버전부터는 기본 함수만으로 쉽게 구축할 수 있게 되었습니다.
- FILTER 함수로 하위목록 만들기 : 예제파일을 실행하고 [목록상자 기초] 시트로 이동합니다. 먼저 좌측에 "구분, 제품, 가격"으로 작성된 데이터에서 구분을 선택하면, 선택한 구분의 제품 목록이 출력되는 목록 상자를 만들어보겠습니다.
선택한 구분의 제품 목록이 출력되는 다이나믹 목록을 만듭니다. - E2셀의 구분 선택으로 "과일"을 입력한 후, 오른쪽 I2셀의 제품목록으로는 다음과 같이 FILTER 함수를 작성합니다.
=FILTER(B2:B13,A2:A13=E2)
구분을 입력하고 I2셀에 FILTER 함수를 작성합니다. 오빠두Tip : FILTER 함수의 기초 사용법은 아래 진짜쓰는 실무엑셀 영상 강의에서 알기 쉽게 정리했으니 참고하세요!👇
- 이제 E2셀의 값을 "과일", "육류", "해산물"로 바꾸면 입력한 구분의 하위 목록이 표시됩니다.
구분을 바꾸면 하위 목록이 갱신됩니다. - 하위 목록 상자 만들기 : 하위 목록 상자를 적용할 F2셀을 선택하고 [데이터] 탭 - [데이터 유효성 검사]를 실행합니다.
F2셀에 데이터 유효성검사를 적용합니다. - 제한 대상으로 [목록]을 선택한 후, 원본 범위로는 FILTER 함수를 작성한 시작셀을 선택하고 뒤에 "#" 기호를 붙여서 분산 범위를 선택합니다.
FILTER 함수의 시작셀을 선택하고 뒤에 #를 붙여서 분산 범위를 선택합니다. 오빠두Tip : "#" 기호는 함수가 반환하는 배열을 동적으로 선택하는 기호입니다. "#" 기호의 자세한 사용법은 아래 기초 입문 영상을 참고하세요!👇
- [확인] 버튼을 클릭해서 데이터 유효성 검사를 적용하면, 선택한 상위 구분의 하위 제품이 표시되는 목록 상자가 완성됩니다.
구분과 연결되는 하위 목록 상자가 완성되었습니다.
XLOOKUP 함수로 여러 목록 동적으로 반환하기
FILTER 함수로 기본적인 다이나믹 목록을 만드는 방법을 알아보았습니다. 이제 한 단계 더 나아가서, XLOOKUP 함수를 사용해 여러 개의 다이나믹 목록을 동시에 만드는 방법을 알아보겠습니다.
- 목록 상자에 사용할 데이터 준비하기 : 먼저 상위 항목인 "구분" 목록에 사용할 고유값 범위를 만듭니다. 예제파일의 [목록상자 기초] 시트에서 K2셀에 다음과 같이 UNIQUE 함수를 사용해 구분 목록을 생성합니다.
=UNIQUE(A2:A13)
UNIQUE 함수로 구분 고유 목록을 생성합니다. - 이후 오른쪽 L2셀에는 FILTER 함수를 사용해 구분 목록의 첫번째 값을 기준으로 필터링 된 제품 목록을 생성합니다.
=FILTER($B$2:$B$13,$A$2:$A$13=K2,"")
// 수식을 자동채우기 했을 때, 범위가 이동하지 않도록 절대참조로 변경합니다.
// 3번째 인수로 "" 를 입력해서, 결과 없음으로 "#CALC!" 오류대신 빈 칸을 출력합니다.
구분 목록의 첫번째 값을 기준으로 필터링 된 제품 목록을 생성합니다. - 작성한 FILTER 함수를 UNIQUE 함수로 묶어서 고유값만 반환한 후, 다시 TRANSPOSE 함수로 묶어서 반환된 목록을 가로 방향으로 전환합니다.
=TRANSPOSE(UNIQUE(FILTER($B$2:$B$13,$A$2:$A$13=K2,"")))
제품 목록을 가로 방향으로 정렬합니다. - 이제 완성된 공식을 아래로 자동채우기하면 각 구분의 하위 제품 목록이 완성됩니다.
수식을 자동채우기하면 구분, 제품 목록이 완성됩니다. - 여러 개의 다이나믹 목록상자 만들기 : 이제 상위 목록과 하위 목록을 각각 선택할 목록상자를 적용합니다. 먼저 구분 목록을 적용할 E2:E7 범위를 선택한 후, [데이터] 탭 - [데이터 유효성 검사] 에서 제한 대상으로 목록을 선택하고 원본 범위로는 구분이 작성된 A2:A13 범위를 선택해서 목록 상자를 적용합니다.
구분 선택 범위에 데이터 유효성 검사로 목록을 적용합니다. 오빠두Tip : M365 버전에서는 목록의 중복된 값이 자동으로 제거됩니다. - 제품 선택의 첫번째 셀인 F2셀을 선택하고, 기존에 적용된 데이터 유효성 검사를 초기화합니다. 이후 선택한 구분의 제품 목록을 출력하기 위해 아래와 같이 XLOOKUP 함수를 작성합니다.
=XLOOKUP(E2,K:K,L:L)#
// XLOOKUP 함수는 뒤에 # 기호를 붙여 분산 범위를 동적으로 반환할 수 있습니다.
선택한 구분의 제품 목록을 반환하도록 XLOOKUP 함수를 작성합니다. - 아래 그림과 같이 목록이 잘 반환되는 것을 확인하였으면 작성한 XLOOKUP 함수를 선택해서 잘라낸 후, 하위 목록을 적용할 F2:F6 범위를 선택하고 [데이터] - [데이터 유효성 검사]로 이동합니다.
목록이 잘 반환되는 것을 확인했으면, 제품 선택 범위에 데이터 유효성 검사를 적용합니다. - 제한 대상으로 목록을 선택한 후, 원본 범위로 방금 잘라내기 한 XLOOKUP 함수를 붙여넣고 [확인] 버튼을 클릭해서 목록을 적용합니다.
데이터 유효성 검사의 원본 범위로 XLOOKUP 함수를 입력합니다. - 이제 구분을 선택하고 제품 목록을 펼치면, 선택한 구분의 하위 목록만 표시되는 다이나믹 목록이 완성됩니다.
선택한 구분의 제품이 출력되는 다이나믹 목록 상자가 완성됩니다.
여러 단계로 이어지는 다이나믹 목록상자 만들기
이제 마지막 과정으로, "대분류 → 중분류 → 소분류 → 등등.." 여러 단계로 이어지는 다이나믹 목록상자 시스템을 구축해보겠습니다. 이번 실습에서는 3단계로 진행하지만, 필요에 따라 5단계, 6단계 또는 그 이상의 목록으로도 구축할 수 있습니다.
- 목록 상자에 사용할 데이터 준비하기 : 예제파일의 [목록시트] 로 이동한 후, 이전 단계에서 알아본 UNIQUE 함수 + FILTER 함수 + TRANSPOSE 함수를 사용해 대분류와 중분류의 데이터를 생성합니다.
· 대분류(F2셀) : =UNIQUE(제품[대분류])
· 중분류(G2셀) : =TRANSPOSE(UNIQUE(FILTER(제품[중분류],제품[대분류]=F2,"")))
이전 단계에서 알아본 내용으로 대분류/중분류 목록을 생성합니다. - 이제 2단계에서 3단계로 이어지는 목록 데이터를 만듭니다. 목록을 만드는 과정은 이전과 동일하지만, 3단계 목록을 만들 때에는 1단계와 2단계를 모두 만족하는 경우의 값만 표시하는 것을 주의해야 합니다. 예를 들어 중분류로 "주스"가 선택되었을 때 "식품 → 주스", "음료 → 주스"로 대분류가 서로 다를 수 있으므로, 대분류와 중분류를 모두 고려한 소분류 목록을 표시합니다.
상위 항목이 중복되는 경우를 고려해서 고유 목록을 생성합니다. - 먼저 대분류와 중분류의 고유값 목록을 생성합니다. F12셀에 다음과 같이 UNIQUE 함수를 입력하면 대분류와 중분류의 고유목록이 출력됩니다.
=UNIQUE(제품[[대분류]:[중분류]])
대분류와 중분류의 고유 목록을 생성합니다. - 이제 BYROW 함수로 UNIQUE 함수를 묶어주면, 대분류와 중분류가 결합된 고유 목록이 완성됩니다.
=BYROW(UNIQUE(제품[[대분류]:[중분류]]),CONCAT)
대분류와 중분류가 한 단어로 결합된 고유 목록을 생성합니다. 오빠두Tip : BYROW 함수는 M365 버전에서만 제공됩니다. 엑셀 2021 버전 사용자는 다음과 같이 UNIQUE 함수를 작성해서 동일한 결과를 얻을 수 있습니다.=UNIQUE(제품[대분류]&제품[중분류]) - 이제 대분류와 중분류를 기준으로 소분류 목록을 필터링합니다. G12셀에 다음과 같이 FILTER 함수를 작성하고 아래로 자동채우기하면 각 대분류&중분류의 소분류 목록이 생성됩니다.
=TRANSPOSE(UNIQUE(FILTER(제품[소분류],제품[대분류]&제품[중분류]=F12,"")))
각 대분류와 중분류를 모두 만족하는 경우의 소분류 목록을 출력합니다. - 다이나믹 목록상자 만들기 : 예제파일의 [발주서] 시트로 이동한 후, 대분류 목록 상자를 적용할 B5:B16 범위를 선택하고 [데이터] - [데이터 유효성 검사]로 목록 시트의 대분류 범위를 목록으로 적용합니다.
대분류 범위에 목록 상자를 적용합니다. - 다음으로 중분류와 소분류 목록 상자를 적용할 C5:C16과 D5:D16 범위에는 아래 XLOOKUP 함수로 목록 상자를 적용하면 다이나믹 목록 상자가 완성됩니다.
· 중분류 범위 : =XLOOKUP(B5,목록시트!F:F,목록시트!G:G)#
· 소분류 범위 : =XLOOKUP(B5&C5,목록시트!F:F,목록시트!G:G)#
각 중분류와 소분류 범위에도 XLOOKUP 함수로 목록상자를 적용합니다. - 마지막으로 단가는 XLOOKUP 함수를 사용해서 대분류, 중분류, 소분류를 모두 만족할 경우 단가를 출력하도록 공식을 작성하면 다이나믹 목록상자를 활용한 발주서가 완성됩니다.
=XLOOKUP(B5&C5&D5,제품[대분류]&제품[중분류]&제품[소분류],제품[가격],0)
대분류, 중분류, 소분류를 모두 만족할 경우 단가를 출력하는 XLOOKUP 함수를 작성합니다. 오빠두Tip : 다이나믹 목록상자를 활용해 발주서를 완성하는 전체 과정은 아래 멤버십 라이브 영상을 참고하세요!👇
- FILTER 함수로 하위목록 만들기 : 예제파일을 실행하고 [목록상자 기초] 시트로 이동합니다. 먼저 좌측에 "구분, 제품, 가격"으로 작성된 데이터에서 구분을 선택하면, 선택한 구분의 제품 목록이 출력되는 목록 상자를 만들어보겠습니다.
