Excel에서 드롭다운 필터로 선택 항목 기반 데이터 추출하기 (4가지 방법)

Excel의 필터 기능은 원하는 조건에 따라 데이터를 추출할 수 있게 해주는 강력한 도구입니다. 하지만 기본 필터 기능에는 행 번호와 셀 참조 값이 그대로 유지되는 단점이 있습니다. 간단한 드롭다운 필터를 만들면 이 문제를 손쉽게 해결할 수 있습니다.

이 글에서는 Excel에서 드롭다운 필터를 만들어 선택한 항목에 따라 데이터를 추출하는 실용적인 4가지 방법을 소개합니다.

설명을 위해 아래와 같은 샘플 데이터셋을 사용하겠습니다. 이 데이터셋은 회사의 영업 사원(Salesman), 제품(Product), 순매출(Net Sales) 정보를 담고 있습니다.

드롭다운 필터로 선택 기반 데이터 추출하는 4가지 방법

1. 보조 열을 활용한 드롭다운 필터 만들기

첫 번째 방법은 보조 열 3개를 추가하여 드롭다운 필터를 구성하는 방식입니다. 다음 단계를 따라 진행해 보세요.

단계별 방법:

• 먼저 셀 D5를 선택하고 다음 수식을 입력합니다:

• Enter 키를 누른 후 자동 채우기(AutoFill) 기능으로 나머지 셀까지 수식을 복사합니다.
• 다음으로 드롭다운 필터를 만들 위치인 셀 G5(또는 원하는 셀)를 선택합니다.
• 데이터 ➤ 데이터 도구 ➤ 데이터 유효성 검사를 차례로 클릭합니다.

• 데이터 유효성 검사 대화상자가 나타나면, 설정 탭에서 제한 대상을 목록으로 선택하고 원본 입력란에 TV, AC를 입력합니다.
• 확인을 누르면 원하는 드롭다운 필터가 완성됩니다.

• 이제 셀 E5를 선택하고 다음 수식을 입력합니다:

• Enter 키를 누른 뒤 자동 채우기로 나머지 범위를 채웁니다.
• 그다음 셀 F5를 선택하고 아래 수식을 입력합니다:

• Enter 키를 누르고 자동 채우기로 마무리합니다.
• 마지막으로 셀 I5를 선택해 다음 수식을 입력합니다:

• Enter 키를 눌러 결과를 확인합니다.

여기서 COLUMNS 함수는 범위 $I$5:I5의 열 개수를 반환하고, INDEX 함수는 F5에 지정된 행 번호와 열 번호가 교차하는 위치의 값을 가져옵니다. IFERROR 함수는 수식에서 오류가 발생하면 빈 셀을 반환하여 깔끔한 결과를 유지해 줍니다.

• 자동 채우기로 시리즈를 완성하면 선택한 제품에 해당하는 데이터만 추출됩니다.
• 마찬가지로 드롭다운 필터에서 AC를 선택하면 데이터셋이 자동으로 업데이트됩니다.

2. FILTER 함수로 드롭다운 필터 만들기

두 번째 방법은 Excel의 FILTER 함수를 활용하는 방식입니다. Microsoft 365 또는 Excel 2021 이상 버전에서 사용할 수 있으며, 가장 간결하게 구현할 수 있는 방법입니다.

단계별 방법:

• 먼저 범위 A4:C10을 선택합니다.
• 삽입 탭에서 표(Table)를 클릭합니다.
• 대화상자가 나타나면 확인을 누릅니다. 그러면 Table1이라는 이름의 표가 자동으로 생성됩니다.
• 새 시트를 열고 셀 B2에 다음 수식을 입력합니다:

• Enter 키를 누르면 중복 없이 고유한 제품명 목록이 자동으로 확장(Spill)됩니다.
• 메인 시트로 돌아와 셀 E5를 선택한 후, 데이터 ➤ 데이터 도구 ➤ 데이터 유효성 검사로 이동합니다.
• 설정 탭에서 제한 대상을 목록으로 지정하고, 원본 입력란에 다음 수식을 입력합니다:

여기서 list는 새로 만든 시트 이름이며, # 기호는 동적 배열 전체 범위를 참조합니다.

• 그다음 셀 G5를 선택하고 아래 수식을 입력합니다:

• Enter 키를 누르면 E5에서 선택한 제품과 일치하는 데이터가 자동으로 표시됩니다.
• 드롭다운 필터를 AC로 변경하면 선택에 따라 데이터가 즉시 갱신됩니다.

여기서 FILTER 함수는 Table1을 필터링하여 셀 E5의 값과 일치하는 데이터셋을 반환합니다.

3. INDIRECT 함수로 여러 시트에서 데이터 추출하기

INDIRECT 함수를 사용하면 여러 시트에서 선택한 항목에 따라 데이터를 가져올 수 있습니다. 예를 들어, 아래 데이터셋에는 데이터가 담긴 Sheet1과 Sheet2 두 개의 시트가 있습니다. 이 방법에서는 시트 선택에 따라 총매출(Total Sales) 값을 추출해 보겠습니다.

단계별 방법:

• 추출된 데이터를 표시할 시트에서 셀 C4를 선택합니다.
• 데이터 ➤ 데이터 도구 ➤ 데이터 유효성 검사를 클릭하고, 대화상자에서 제한 대상을 목록으로 설정한 뒤 원본 입력란에 다음을 입력합니다:

• 확인을 눌러 드롭다운 목록을 생성합니다.
• 이제 셀 C6을 선택하고 다음 수식을 입력합니다:

• Enter 키를 누르면 셀 C4에 지정된 시트의 총매출 값이 자동으로 불러와집니다.
• 드롭다운 필터에서 시트를 변경하면 셀 C6의 값도 함께 바뀌는 것을 확인할 수 있습니다.

4. VBA 매크로로 드롭다운 필터 자동화하기

마지막 방법은 VBA 코드를 활용하여 드롭다운 선택에 따라 다른 시트의 데이터를 자동으로 필터링하는 방식입니다.

단계별 방법:

• 먼저 vba1 시트에 원본 데이터가 있다고 가정합니다.
• vba2 시트에는 드롭다운 필터가 있습니다. 우리의 목표는 vba2 시트의 드롭다운 선택에 따라 vba1 시트의 데이터를 필터링하는 것입니다.
• vba2 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 코드 보기(View Code)를 선택합니다.
• VBA 편집기 창이 열리면 아래 코드를 복사하여 붙여넣습니다:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Range("A2"), Target) Is Nothing Then
        Application.EnableEvents = False
        If Range("A2").Value = "" Then
            Worksheets("vba1").ShowAllData
        Else
            Worksheets("vba1").Range("A2").AutoFilter 1, Range("A2").Value
        End If
        Application.EnableEvents = True
    End If
End Sub

• F5 키를 누르면 매크로 대화상자가 나타납니다. 매크로 이름에 VBA를 입력하고 만들기(Create)를 누릅니다.
• 다시 F5 키를 누른 후 실행(Run)을 클릭합니다.
• 창을 닫고 드롭다운 필터에서 TV를 선택하면, vba1 시트에 필터링된 데이터가 표시됩니다.
• 같은 방식으로 AC를 선택하면 해당 제품의 데이터만 추출됩니다.

마무리

지금까지 Excel에서 드롭다운 필터를 만들어 선택 항목에 따라 데이터를 추출하는 4가지 방법을 살펴보았습니다. 상황에 맞는 방법을 활용하면 데이터 관리 효율이 크게 향상될 것입니다. 추가로 알고 계신 방법이나 궁금한 점이 있다면 댓글로 남겨주세요!

함께 읽으면 좋은 글

• Excel에서 드롭다운 목록으로 양식(Form) 만드는 방법
• 드롭다운 목록 선택에 따라 열 숨기기/숨기기 해제하기
• Excel에서 여러 단어가 포함된 종속 드롭다운 목록 만들기
• Excel 드롭다운 목록에서 사용된 항목 제거하기 (2가지 방법)
• Excel 드롭다운 목록에서 중복 항목 제거하기 (4가지 방법)