Excel에서 셀 값에 따라 행을 자동 필터링하려면 어떻게 해야 합니까?

Excel에서 표준 필터 기능은 선택한 조건에 따라 데이터를 빠르게 필터링할 수 있는 방법을 제공합니다. 그러나 일부 작업 환경에서는 특정 셀에 값을 입력하거나 조건을 설정하는 즉시 데이터가 자동으로 새로 고쳐지기를 원할 수 있습니다. 예를 들어, 다른 셀에 키워드나 매개변수를 입력함으로써 데이터 세트가 즉시 필터링되도록 하고자 하는 경우, 이는 Excel의 기본 필터링 기능을 넘어선 것입니다. 이러한 동적 또는 “자동 필터링”은 필터 조건을 셀 값에 직접 연결하여 데이터 검토 프로세스를 간소화하고, 수동 필터링에 소요되는 시간을 줄이며, 정확성을 높이는 데 도움이 될 수 있습니다. 셀 입력을 기반으로 한 자동 필터링 메커니즘을 구현하려는 경우, 이를 달성하기 위한 여러 실용적인 솔루션이 있습니다.

Excel 수식 솔루션을 사용하여 셀 값에 따라 행 자동 필터링

VBA 코드를 사용하여 입력한 셀 값에 따라 행 자동 필터링

Excel 수식 솔루션을 사용하여 셀 값에 따라 행 자동 필터링

보안 설정이나 협업 문제로 인해 매크로 사용을 피해야 하는 경우 또는 비매크로 접근 방식을 선호하는 경우, Excel에 내장된 수식과 필터링 기능을 결합하여 "자동 필터" 효과를 만들 수 있습니다. 이 솔루션은 Microsoft 365, Microsoft 2019 및 FILTER와 같은 동적 배열 함수를 지원하는 이후 버전에서 잘 작동합니다. 이는 매크로를 사용할 수 없는 동료들과 공유하거나 설치 없이 자동화가 필요한 가벼운 템플릿에 특히 적합합니다.

1. 필터링된 결과를 시작할 빈 셀을 선택하세요(예: G2 셀에 커서를 놓으세요).

=FILTER(A2:C20, (A2:A20=E1) * (B2:B20=E2), "No match")

2. G2에 수식을 입력한 후 Enter를 누르세요. 필터링된 데이터 세트는 G2부터 시작하는 범위에 나타납니다. E1 또는 E2의 값을 변경하면 필터링된 목록이 즉시 새로 고쳐집니다.

이 수식 예제는 주요 데이터가 A2:C20에 있고 필터 조건이 E1(A열과 일치) 및 E2(B열과 일치)에 입력된다고 가정합니다. 하나의 열만으로 필터링하려면 논리 조건을 그에 따라 단순화할 수 있습니다. 일치하는 행이 없으면 "일치하지 않음" 메시지가 표시됩니다.

팁: FILTER 함수는 Microsoft 365 및 일부 Microsoft 19+ 버전에서 사용 가능합니다. 이전 Excel 버전에서는 수식을 통한 동적 필터링이 덜 간단하며, 보조 열을 사용한 전통적인 자동 필터 또는 고급 필터 기능을 살펴볼 수 있습니다. 호환성을 위해 버전을 확인하세요.

이 솔루션을 사용할 때 결과는 워크시트의 새로운 영역을 차지하여 원본 데이터를 보존하는 데 도움이 됩니다. 하지만 원본 데이터는 영향을 받지 않으며, 필터는 표시된 결과에만 적용되고 원본 데이터 표시 상태에는 적용되지 않습니다.

잠재적 오류: #NAME? 또는 #SPILL! 오류가 발생하면 호환 가능한 Excel 버전을 사용하고 있는지, 그리고 결과 범위에 병합된 셀이 없는지 확인하세요. 또한 스플 범위에 다른 데이터를 배치하지 않도록 하여 차단을 방지하세요.

입력한 셀 값에 따라 VBA 코드를 사용하여 행 자동 필터링

특정 셀에 입력한 조건에 따라 데이터를 자동으로 필터링하려고 한다고 가정해 봅시다. 예를 들어, E1과 E2 셀에 원하는 조건을 입력하면 워크시트의 데이터가 자동으로 해당 값과 일치하도록 필터링됩니다. 아래 그림과 같습니다.

이러한 방식으로 필터링을 자동화하려면 간단한 VBA 솔루션을 설정할 수 있습니다. 이 방법은 지정된 필터 셀의 값을 업데이트할 때마다 트리거됩니다. 이는 대시보드, 상호작용형 보고서 또는 중앙 파라미터 셀에서 동적 필터링을 기대하는 사용자를 위한 템플릿에 특히 유용합니다.

1. 셀에 입력된 값을 기반으로 행 필터링을 자동화하려는 워크시트로 이동하세요.

2. Excel 창 하단의 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 컨텍스트 메뉴에서 코드 보기(View Code)를 선택하세요. 나타난 Microsoft Visual Basic for Applications 창에 다음과 같은 VBA 코드를 큰 빈 공간(코드 창 또는 워크시트 모듈이라고 함)에 붙여넣으세요.

VBA 코드: 입력된 셀 값에 따라 데이터 자동 필터링

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160606
   If Target.Address = Range("E2").Address Then
       Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
   End If
End Sub

참고: 이 VBA 코드에서 A1:C20은 필터를 적용할 데이터 범위를 나타냅니다. E2는 필터 값을 입력하는 셀(목표 조건)이고, E1:E2는 필터 조건으로 사용되는 셀입니다. 필요에 따라 이러한 범위를 수정할 수 있지만, 특정 워크시트 구조에서 데이터와 필터 조건이 위치한 곳을 정확히 반영하도록 해야 합니다. 또한 정확한 필터링을 위해 병합된 셀은 피하고 데이터 범위에 헤더가 포함되어 있는지 확인하세요.

3. 이제 E1 및/또는 E2 셀에 필터 조건을 입력하거나 변경하세요. Enter를 누르면 VBA 코드가 자동으로 실행되어 입력한 조건에 맞는 행만 표시되도록 지정된 데이터 범위를 필터링합니다.

값을 입력한 후 필터가 즉시 작동하지 않는 것 같다면, 워크북에서 매크로 기능이 활성화되어 있는지 확인하고 VBA 코드의 범위가 현재 워크시트 레이아웃과 올바르게 일치하는지 확인하세요. 다중 사용자 환경에서는 매크로를 활성화해야 이 기능이 제대로 작동한다는 점을 다른 사용자에게 알려주세요.

이 VBA 방법은 동적 파라미터 기반 대시보드, 데이터 입력을 위한 상호작용형 템플릿 또는 수동으로 필터 조건을 다시 적용하는 것이 비효율적인 모든 시나리오에 특히 유리합니다. 그러나 사용자가 매크로를 비활성화하면 문제가 발생할 수 있으며, VBA 기반 솔루션은 코드가 추가된 파일에 특정됩니다.

다양한 Excel 보안 설정을 가진 여러 사용자가 자주 조건을 변경해야 하거나 파일을 광범위하게 공유할 계획인 경우, 대체 수식 기반 솔루션이나 Excel 추가 기능을 고려해야 할 수 있습니다.

데모: VBA 코드를 사용하여 입력한 셀 값에 따라 행 자동 필터링