Excel에서 필터 드롭다운 목록 복사하는 5가지 방법

Excel에서 필터 드롭다운 목록을 효과적으로 복사하고 싶으신가요? 이 글에서는 다양한 상황에 맞춰 필터 드롭다운 목록을 복사할 수 있는 5가지 방법을 자세히 소개합니다.
그럼 바로 시작해 보겠습니다.

워크북 다운로드

Excel에서 필터 드롭다운 목록을 복사하는 5가지 방법

먼저 아래와 같이 영업 사원 이름과 제품별 판매액이 담긴 데이터셋을 준비했습니다. 이 데이터에 필터(Filter)를 적용한 상태에서, 여러 조건에 따라 필터 드롭다운 목록을 복사하는 방법을 하나씩 살펴보겠습니다.

본 가이드는 Microsoft Excel 365 버전을 기준으로 작성되었으며, 사용 중인 다른 버전에서도 동일하게 적용할 수 있습니다.

방법 1: 고급 필터 옵션 활용하기

Product(제품) 열에는 여러 제품이 나열되어 있지만, 정렬이 되어 있지 않고 Blackberries, Broccoli 같은 중복 항목도 포함되어 있습니다.

반면 필터 드롭다운 기호를 클릭하면 목록이 A~Z 순으로 정렬되고 중복 값도 제거된 상태로 표시됩니다. 우리의 목표는 이 드롭다운 목록을 Filtered List 열로 복사하는 것이며, 여기서는 고급 필터(Advanced Filter) 옵션을 활용합니다.

실행 단계:
➤ 데이터(Data) 탭 → 정렬 및 필터(Sort & Filter) 그룹 → 고급(Advanced) 옵션으로 이동합니다.

그러면 고급 필터 대화상자가 열립니다.
➤ 다른 위치로 복사(Copy to another location)와 유일한 레코드만(Unique records only) 옵션을 체크합니다.
➤ 제품 열을 목록 범위(List range)로 지정하고, 결과를 출력할 위치를 복사 위치(Copy to) 상자에 입력한 후 확인을 누릅니다.

이제 Filtered List 열에 중복 없는 제품 목록이 생성되었지만, 아직 정렬은 되어 있지 않습니다.

➤ 정렬을 위해 데이터 범위를 선택한 후 데이터 탭 → 정렬 및 필터 그룹 → 정렬(Sort) 옵션으로 이동합니다.

정렬 대화상자가 나타나면 다음과 같이 설정합니다.
정렬 기준 → Filtered List
정렬 기준(Sort On) → 셀 값
순서(Order) → A에서 Z
➤ 데이터에 머리글 포함(My data has headers) 옵션을 체크하고 확인을 누릅니다.

이렇게 하면 목록이 정렬되며, 필터 드롭다운 목록이 Filtered List 열에 그대로 복사됩니다.

방법 2: UNIQUE 함수 활용하기

이번에는 Product 열의 필터 드롭다운 목록을 UNIQUE 함수와 SORT 함수를 조합하여 Filtered List 열로 복사해 보겠습니다.

실행 단계:
먼저 데이터 범위를 표(Table)로 변환합니다.
➤ 삽입(Insert) 탭 → 표(Table) 옵션을 클릭합니다.

표 만들기(Create Table) 대화상자가 나타나면,
➤ 범위를 선택하고 테이블에 머리글 포함(My table has headers) 옵션을 체크한 뒤 확인을 누릅니다.

그러면 Table2라는 표가 생성됩니다.

➤ 이제 E4 셀에 아래 수식을 입력하여 Product 열의 고유 값을 추출합니다.

여기서 Table2[Product]는 Table2의 Product 열 범위를 의미하고, 첫 번째 FALSE는 행 단위 고유 값 반환, 두 번째 FALSE는 각 개별 항목 반환 옵션입니다. UNIQUE 함수가 중복 없는 제품 목록을 만들면, SORT 함수가 이를 정렬해 줍니다.

ENTER 키를 누르면 Product 열의 필터 드롭다운 목록이 Filtered List 열에 나타납니다.

참고: UNIQUE 함수는 Microsoft Excel 365 버전에서만 사용할 수 있습니다.

방법 3: 중복된 항목 제거 옵션 활용하기

이 방법에서는 중복된 항목 제거(Remove Duplicates) 기능으로 제품의 중복 값을 삭제한 후, 정렬 옵션으로 드롭다운 목록과 동일한 형태의 목록을 만듭니다.

실행 단계:
먼저 Product 열의 목록을 Filtered List 열로 복사해야 합니다.
➤ Product 열 범위를 선택하고 CTRL+C를 누릅니다.

➤ CTRL+V를 눌러 목록을 Filtered List 열에 붙여넣습니다.

이제 중복을 제거하여 고유 값만 남깁니다.
➤ 데이터 범위를 선택한 후 데이터 탭 → 데이터 도구(Data Tools) 그룹 → 중복된 항목 제거 옵션으로 이동합니다.

중복된 항목 제거 대화상자가 나타나면,
➤ Filtered List 옵션을 체크하고 확인을 누릅니다.

그러면 중복 값 2개가 제거되었다는 메시지 상자가 표시되는데, 확인을 누르면 됩니다.

방법 1과 동일하게 A~Z 순으로 정렬하면, Product 열의 필터 드롭다운 목록이 Filtered List 열에 완성됩니다.

방법 4: FILTER 함수 활용하기

이번에는 Product 열을 기준으로 데이터를 필터링한 상황을 가정합니다. 여기서는 Blackberries와 Broccoli 제품에 해당하는 값들만 표시하고자 합니다.

필터링 후 Salesperson 열의 드롭다운 목록에는 영업 사원 이름이 A~Z 순으로 표시됩니다.

마찬가지로 Sales 열의 드롭다운 목록에는 판매액이 낮은 값부터 높은 값 순으로 정렬되어 있습니다. 이 두 목록을 FILTER 함수로 복사하는 것이 목표입니다.

4.1: FILTER 함수 사용하기

➤ B14 셀에 아래 수식을 입력합니다.

여기서 B7:D11은 검색 범위이며, FILTER 함수는 B7:B11=B7 조건을 통해 B7 셀의 값(Blackberries)을 찾습니다. 일치하지 않는 빈 셀에는 공백을 반환합니다.

ENTER 키를 누르면 Blackberries 제품의 영업 사원 이름과 판매액이 표시됩니다.

같은 방식으로 Broccoli 제품의 값을 추출하려면 B16 셀에 아래 수식을 입력합니다.

➤ ENTER를 누르면 영업 사원 이름이 Filtered List1 열에, 판매액이 Filtered List2 열에 표시됩니다.

참고: FILTER 함수 역시 Microsoft Excel 365 버전에서만 사용 가능합니다.

4.2: 값 복사 후 정렬하기

이제 앞서 설명한 것처럼 드롭다운 목록 순서대로 정렬해야 하지만, 배열 수식으로 결과가 출력되므로 직접 정렬할 수 없습니다.
따라서 먼저 CTRL+C로 목록을 복사합니다.

➤ 붙여넣을 셀을 선택하고 마우스 오른쪽 버튼을 클릭한 후 값 붙여넣기(Paste Values) 옵션을 선택합니다.

이렇게 하면 Filtered List1과 Filtered List2의 값이 새 데이터셋에 저장됩니다.

마지막으로 영업 사원 이름은 A~Z 순으로, 판매액은 낮은 값부터 높은 값 순으로 정렬합니다.
➤ Filtered List1 열 범위를 선택한 후 데이터 탭 → 정렬 및 필터 그룹 → 정렬 옵션으로 이동합니다.

정렬 대화상자가 나타나면 다음과 같이 설정합니다.
정렬 기준 → Filtered List1
정렬 기준 → 셀 값
순서 → A에서 Z
➤ 데이터에 머리글 포함 옵션을 체크하고 확인을 누릅니다.

Filtered List1의 값이 정렬되면, 이제 Filtered List2 열의 판매액을 처리합니다.
➤ Filtered List2 열 범위를 선택하고 같은 경로로 정렬 옵션을 엽니다.

정렬 대화상자에서 다음과 같이 설정합니다.
정렬 기준 → Filtered List2
정렬 기준 → 셀 값
순서 → 오름차순(Smallest to Largest)
➤ 데이터에 머리글 포함 옵션을 체크하고 확인을 누릅니다.

결과적으로 두 열 모두 원하는 순서대로 정렬된 것을 확인할 수 있습니다.

최종적으로 Salesperson 열의 필터 드롭다운 목록이 Filtered List1 열에,

Sales 열의 필터 드롭다운 목록이 Filtered List2 열에 각각 복사되었습니다.

방법 5: SUBTOTAL, INDEX, MATCH 함수 조합하기

이 방법은 Product 열의 특정 제품으로 데이터를 필터링하면 Salesperson 열의 드롭다운 목록도 함께 업데이트되는 점을 활용합니다. SUBTOTAL 함수, INDEX 함수, MATCH 함수를 조합하면 필터가 변경될 때마다 최신 목록이 항상 Filtered List 열에 반영됩니다.

5.1: 자동 업데이트되는 일련번호 만들기

먼저 Helper(보조) 열에 필터링 시 자동으로 갱신되는 일련번호를 생성합니다.
➤ D4 셀에 아래 수식을 적용합니다.

여기서 3은 COUNTA 기능을 의미하고, C$4:C4는 범위입니다. 행 번호 4 앞에 $ 기호를 넣어 첫 번째 한계를 고정했기 때문에, 예를 들어 8행에서는 C$4:C8처럼 범위가 자동으로 확장됩니다.

➤ ENTER를 누른 후 채우기 핸들(Fill Handle) 도구를 아래로 드래그합니다.

이렇게 하면 Helper 열에 일련번호가 채워집니다.

이제 Product 열을 기준으로 표를 필터링합니다. 드롭다운 목록에서 Apple, Beet Greens, Blackberries, Cherry 제품을 체크했습니다.

필터링된 표가 표시되면, 이제 Salesperson 열의 드롭다운 목록을 Filtered List 열로 복사합니다.

5.2: INDEX 및 MATCH 함수로 목록 추출하기

➤ Serial No(일련번호) 열에 번호를 입력합니다.

➤ D14 셀에 아래 수식을 입력합니다.

여기서 $C$4:$C$11은 추출하려는 Salesperson 열의 범위이고, C14는 Helper 열의 번호와 일치시킬 일련번호입니다.

• MATCH(C14,$D$4:$D$11,0) → C14 셀 값(1)이 Helper 열에서 위치한 행 인덱스를 반환합니다.
  결과 → 1

• INDEX($C$4:$C$11,MATCH(C14,$D$4:$D$11,0)) 는 다음과 같이 계산됩니다.
  INDEX($C$4:$C$11,1) → 범위 $C$4:$C$11에서 행 인덱스 1에 해당하는 값을 확인합니다.
  결과 → Michael

➤ ENTER를 누르고 채우기 핸들 도구를 아래로 드래그합니다.

그러면 Filtered List 열에 영업 사원 이름이 채워지며, 마지막 작업은 A~Z 순으로 정렬하는 것입니다.
➤ 데이터 범위를 선택한 후 데이터 탭 → 정렬 및 필터 그룹 → 정렬 옵션으로 이동합니다.

정렬 대화상자가 열리면 다음과 같이 설정합니다.
정렬 기준 → Filtered List
정렬 기준 → 셀 값
순서 → A에서 Z
➤ 데이터에 머리글 포함 옵션을 체크하고 확인을 누릅니다.

이제 목록이 정렬되어, Salesperson 열의 필터 드롭다운 목록 복사본이 Filtered List 열에 완성됩니다.

연습 섹션

직접 연습해 볼 수 있도록 Practice 시트에 아래와 같은 연습 섹션을 준비했습니다. 배운 내용을 스스로 실습해 보세요.

결론

이번 글에서는 Excel에서 필터 드롭다운 목록을 손쉽게 복사하는 5가지 방법을 다루었습니다. 고급 필터부터 UNIQUE, FILTER, SUBTOTAL·INDEX·MATCH 함수 조합까지 상황에 맞는 방법을 골라 활용해 보시기 바랍니다. 제안이나 궁금한 점이 있다면 언제든지 댓글로 공유해 주세요.

관련 글

• Excel에서 조건부 드롭다운 목록 만들기, 정렬 및 활용법
• IF 문으로 Excel 드롭다운 목록 만드는 방법
• 다른 시트 데이터로 Excel 드롭다운 목록 만들기 (2가지 방법)
• Excel에서 VLOOKUP과 드롭다운 목록 함께 사용하기
• Excel 드롭다운 목록 편집하는 4가지 기본 방법