엑셀 자동으로 업데이트 되는 다이나믹 목록 만들기 (초보자용 완벽 가이드)

실습 가이드

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브 강의 풀영상을 확인하실 수 있습니다.

FILTER + "#" 기호로 하위 목록상자 만들기

엑셀로 대량의 데이터를 관리하다 보면 자주 마주하는 과제 중 하나가 '연결된 드롭다운 목록'을 만드는 작업입니다. 상위 항목을 선택하면 그에 맞는 하위 항목만 자동으로 필터링되어 표시되는 기능입니다.

과거에는 이런 다이나믹 목록(다중 유효성 검사)을 구현하려면 복잡한 함수나 VBA 매크로가 필요했지만, M365 버전부터는 기본 함수만으로도 손쉽게 구축할 수 있게 되었습니다.

1. FILTER 함수로 하위목록 만들기 : 예제파일을 실행하고 [목록상자 기초] 시트로 이동합니다. 좌측에 "구분, 제품, 가격"으로 정리된 데이터에서 구분을 선택하면 해당 구분의 제품 목록이 출력되는 목록 상자를 만들어보겠습니다.

   

2. E2셀에 구분 값으로 "과일"을 입력한 후, 오른쪽 I2셀의 제품 목록에는 다음과 같이 FILTER 함수를 작성합니다.
   =FILTER(B2:B13,A2:A13=E2)

   

   오빠두Tip : FILTER 함수의 기초 사용법은 아래 진짜쓰는 실무엑셀 영상 강의에서 알기 쉽게 정리했으니 함께 참고하세요!👇
   
3. 이제 E2셀의 값을 "과일", "육류", "해산물"로 바꿔보면 입력한 구분의 하위 목록이 동적으로 갱신되어 표시됩니다.

   

4. 하위 목록 상자 만들기 : 하위 목록 상자를 적용할 F2셀을 선택하고 [데이터] 탭 - [데이터 유효성 검사]를 실행합니다.

   

5. 제한 대상으로 [목록]을 선택한 후, 원본 범위에는 FILTER 함수를 작성한 시작 셀을 선택하고 뒤에 "#" 기호를 붙여 분산 범위를 지정합니다.

   

   오빠두Tip : "#" 기호는 함수가 반환하는 배열을 동적으로 참조하는 연산자입니다. "#" 기호의 자세한 사용법은 아래 기초 입문 영상에서 확인하세요!👇
   
6. [확인] 버튼을 클릭해서 데이터 유효성 검사를 적용하면, 선택한 상위 구분의 하위 제품만 표시되는 목록 상자가 완성됩니다.

   

XLOOKUP 함수로 여러 목록 동적으로 반환하기

앞 단계에서 FILTER 함수로 기본적인 다이나믹 목록을 만드는 방법을 알아보았습니다. 이제 한 단계 더 나아가, XLOOKUP 함수를 활용해 여러 개의 다이나믹 목록을 한 번에 구성하는 방법을 알아보겠습니다.

1. 목록 상자에 사용할 데이터 준비하기 : 먼저 상위 항목인 "구분" 목록에 사용할 고유값 범위를 준비합니다. 예제파일의 [목록상자 기초] 시트에서 K2셀에 다음과 같이 UNIQUE 함수를 사용해 구분 목록을 생성합니다.
   =UNIQUE(A2:A13)

   

2. 이어서 오른쪽 L2셀에는 FILTER 함수를 사용해 구분 목록의 첫 번째 값을 기준으로 필터링된 제품 목록을 생성합니다.
   =FILTER($B$2:$B$13,$A$2:$A$13=K2,"")
   // 수식을 자동채우기 했을 때, 범위가 이동하지 않도록 절대참조로 변경합니다.
   // 3번째 인수로 "" 를 입력해서, 결과 없음으로 "#CALC!" 오류대신 빈 칸을 출력합니다.

   

3. 작성한 FILTER 함수를 UNIQUE 함수로 한 번 더 묶어 고유값만 반환한 뒤, 다시 TRANSPOSE 함수로 감싸 반환된 목록을 가로 방향으로 전환합니다.
   =TRANSPOSE(UNIQUE(FILTER($B$2:$B$13,$A$2:$A$13=K2,"")))

   

4. 완성된 수식을 아래로 자동 채우기하면 각 구분에 해당하는 하위 제품 목록이 모두 생성됩니다.

   

5. 여러 개의 다이나믹 목록상자 만들기 : 이제 상위 목록과 하위 목록을 각각 선택할 목록 상자를 적용합니다. 먼저 구분 목록을 적용할 E2:E7 범위를 선택한 후, [데이터] 탭 - [데이터 유효성 검사]에서 제한 대상으로 [목록]을 선택하고 원본 범위에는 구분이 작성된 A2:A13 범위를 지정해 목록 상자를 적용합니다.

   

   오빠두Tip : M365 버전에서는 목록의 중복된 값이 자동으로 제거됩니다.
6. 제품 선택의 첫 번째 셀인 F2셀을 선택하고, 기존에 적용된 데이터 유효성 검사를 초기화합니다. 이후 선택한 구분의 제품 목록을 반환하기 위해 다음과 같이 XLOOKUP 함수를 작성합니다.
   =XLOOKUP(E2,K:K,L:L)#
   // XLOOKUP 함수는 뒤에 # 기호를 붙여 분산 범위를 동적으로 반환할 수 있습니다.

   

7. 아래 그림과 같이 목록이 정상적으로 반환되는 것을 확인하였다면 작성한 XLOOKUP 수식을 잘라낸 뒤, 하위 목록을 적용할 F2:F6 범위를 선택하고 [데이터] - [데이터 유효성 검사]로 이동합니다.

   

8. 제한 대상으로 [목록]을 선택한 뒤, 원본 범위에 방금 잘라낸 XLOOKUP 함수를 붙여 넣고 [확인] 버튼을 클릭해 목록을 적용합니다.

   

9. 이제 구분을 선택한 뒤 제품 목록을 펼치면, 선택한 구분의 하위 항목만 표시되는 다이나믹 목록이 완성됩니다.

   

여러 단계로 이어지는 다이나믹 목록상자 만들기

이제 마지막 과정으로 "대분류 → 중분류 → 소분류 → 등등.." 여러 단계로 이어지는 다이나믹 목록상자 시스템을 구축해보겠습니다. 이번 실습은 3단계로 진행하지만, 필요에 따라 5단계, 6단계 또는 그 이상의 단계로도 동일한 방식으로 확장할 수 있습니다.

1. 목록 상자에 사용할 데이터 준비하기 : 예제파일의 [목록시트]로 이동한 뒤, 앞 단계에서 알아본 UNIQUE 함수 + FILTER 함수 + TRANSPOSE 함수를 조합해 대분류와 중분류 데이터를 생성합니다.
   · 대분류(F2셀) : =UNIQUE(제품[대분류])
   · 중분류(G2셀) : =TRANSPOSE(UNIQUE(FILTER(제품[중분류],제품[대분류]=F2,"")))

   

2. 이어서 2단계에서 3단계로 이어지는 목록 데이터를 만듭니다. 만드는 절차는 이전과 동일하지만, 3단계 목록을 구성할 때에는 1단계와 2단계 조건을 모두 만족하는 값만 표시해야 한다는 점에 유의합니다. 예를 들어 중분류로 "주스"가 선택되었을 때 "식품 → 주스", "음료 → 주스"처럼 대분류가 다를 수 있으므로, 대분류와 중분류를 모두 고려한 소분류 목록을 표시해야 합니다.

   

3. 먼저 대분류와 중분류의 고유값 목록을 생성합니다. F12셀에 다음과 같이 UNIQUE 함수를 입력하면 대분류와 중분류의 고유 목록이 출력됩니다.
   =UNIQUE(제품[[대분류]:[중분류]])

   

4. 여기에 BYROW 함수로 UNIQUE 함수를 한 번 더 감싸면, 대분류와 중분류가 결합된 고유 목록이 완성됩니다.
   =BYROW(UNIQUE(제품[[대분류]:[중분류]]),CONCAT)

   

   오빠두Tip : BYROW 함수는 M365 버전에서만 제공됩니다. 엑셀 2021 버전을 사용한다면 다음과 같이 UNIQUE 함수를 작성해 동일한 결과를 얻을 수 있습니다.

   =UNIQUE(제품[대분류]&제품[중분류])

5. 이제 대분류와 중분류 조합을 기준으로 소분류 목록을 필터링합니다. G12셀에 다음과 같이 FILTER 함수를 작성하고 아래로 자동 채우기하면 각 대분류&중분류 조합에 해당하는 소분류 목록이 생성됩니다.
   =TRANSPOSE(UNIQUE(FILTER(제품[소분류],제품[대분류]&제품[중분류]=F12,"")))

   

6. 다이나믹 목록상자 만들기 : 예제파일의 [발주서] 시트로 이동한 뒤, 대분류 목록 상자를 적용할 B5:B16 범위를 선택하고 [데이터] - [데이터 유효성 검사]에서 [목록시트]의 대분류 범위를 목록으로 적용합니다.

   

7. 다음으로 중분류와 소분류 목록 상자를 적용할 C5:C16과 D5:D16 범위에는 아래 XLOOKUP 함수로 목록 상자를 적용하면 다단계 다이나믹 목록 상자가 완성됩니다.
   · 중분류 범위 : =XLOOKUP(B5,목록시트!F:F,목록시트!G:G)#
   · 소분류 범위 : =XLOOKUP(B5&C5,목록시트!F:F,목록시트!G:G)#

   

8. 마지막으로 단가는 XLOOKUP 함수를 사용해 대분류, 중분류, 소분류를 모두 만족하는 경우의 단가를 반환하도록 수식을 작성하면 다이나믹 목록상자를 활용한 발주서가 완성됩니다.
   =XLOOKUP(B5&C5&D5,제품[대분류]&제품[중분류]&제품[소분류],제품[가격],0)

   

   오빠두Tip : 다이나믹 목록상자를 활용해 발주서를 완성하는 전체 과정은 아래 멤버십 라이브 영상에서 확인하세요!👇