방대한 데이터 중에서 원하는 조건에 맞는 데이터를 정확히 찾는 것은 쉽지 않습니다. 하지만 Excel의 필터(Filter)를 이용하면 빠르게 찾을 수 있습니다. 필터는 Excel에서 데이터를 다룰 때 반드시 알아야 하는 기능입니다. 준비되셨나요? 출발합니다.
(이미지: 아이엑셀러 닷컴)
Excel에서 자동 필터 사용하는 방법
자동 필터 기본 사용법
자동 필터(Autofilter)는 사용하기 쉽지만 강력합니다. 간단한 조건을 적용하면서 기본 사용법을 익혀보도록 하죠.
(1) 데이터 내부의 셀을 하나 선택하고 [데이터] 탭 - [정렬 및 필터] 그룹 - [필터]를 선택합니다.
(이미지: 아이엑셀러 닷컴)
(2) 각 필드명 오른쪽에 필터 버튼이 추가됩니다. 이 버튼을 이용하여 필터를 할 수 있습니다. 예를 들어 '동부' 팀 실적만 표시하고 싶다면 [팀명] 필드의 필터 버튼을 클릭합니다. '(모두 선택)' 앞의 체크 표시를 해제한 다음 '동부'에만 체크 표시를 하고 [확인]을 누릅니다.
(이미지: 아이엑셀러 닷컴)
(3) '동부' 팀의 실적만 표시되고 나머지는 숨겨집니다. 필터된 데이터는 행 머리글이 파란색으로 표시되므로 쉽게 알 수 있습니다.
(이미지: 아이엑셀러 닷컴)
필터 지우기
일부 필터 지우기
필터가 적용된 필드(여기서는 '팀명')는 버튼 모양을 보면 알 수 있습니다(깔때기 모양). 버튼을 클릭하고 ["팀명"에서 필터 해제]를 클릭합니다.
(이미지: 아이엑셀러 닷컴)
모든 필터 제거하기
하나의 필드에 필터가 적용된 상태에서 다른 필드에도 필터를 추가할 수도 있습니다. 여러 필드에 필터가 적용되어 있을 때 각각의 필드에 적용된 모든 필터를 한꺼번에 지우려면 [데이터] 탭 - [정렬 및 필터] 그룹 - [지우기]를 선택합니다.
(이미지: 아이엑셀러 닷컴)
필터 해제하기
필터 상태 자체를 해제하려면 [데이터] 탭 - [정렬 및 필터] 그룹 - [필터]를 클릭합니다. [필터]는 한 번 누를 때마다 필터가 설정 또는 해제됩니다. 이와 같은 키를 토글(Toggle) 키라고 합니다.
텍스트, 숫자, 날짜 필터 사용
Excel 데이터에 자동 필터를 적용하고 해제하는 방법에 대해 알아보았습니다. 보고 싶은 항목에 조건을 추가하여 표시할 수도 있습니다. 여기에는 텍스트, 숫자, 날짜 등이 해당됩니다. 텍스트 필터는 앞에서 살펴보았으니 숫자와 날짜 필터에 대해 살펴봅니다.
숫자 필터
필드의 데이터가 숫자인 경우, 같거나 같지 않거나, 크거나 작거나, 평균보다 크거나 작거나, 혹은 상위 10위까지의 데이터를 필터링 할 수 있습니다.
(1) [데이터] 탭 - [정렬 및 필터] 그룹 - [지우기]를 클릭해서 필터를 초기화합니다.
(2) '수량'이 100개 이상인 데이터를 필터링 해보죠. '수량' 필드의 필터 버튼을 클릭하고 [숫자 필터] - [크거나 같음]을 선택합니다.
(이미지: 아이엑셀러 닷컴)
(3) [사용자 지정 자동 필터] 대화상자에서 100을 입력하고 [확인]을 클릭합니다.
(이미지: 아이엑셀러 닷컴)
(4) '수량'이 100 이상인 데이터들만 표시됩니다.
(이미지: 아이엑셀러 닷컴)
만약 '수량'이 100개 이상 ~ 200개 미만인 데이터만 필터링하려면 앞의 (2) 단계에서 [숫자 필터] - [해당 범위]를 선택하고 다음과 같이 조건을 지정하면 됩니다.
(이미지: 아이엑셀러 닷컴)
[참고] '상위 10' 필터 [숫자 필터] - [상위 10]을 선택하면 다음과 같은 대화상자가 나타납니다. 이것을 이용하여 Top 10 '항목' 또는 Top 10 '%'에 해당하는 데이터도 쉽게 추출할 수 있습니다. (이미지: 아이엑셀러 닷컴)
날짜 + 텍스트 필터
텍스트나 숫자와 마찬가지로 날짜 데이터에 필터 조건을 추가할 수 있습니다. 이전, 이후, 해당 범위, 오늘, 어제, 주, 달, 분기 등 다양한 조건을 선택할 수 있습니다.
(이미지: 아이엑셀러 닷컴)
두 가지 조건을 동시에 충족하는 데이터를 필터링해 볼까요? '1월'의 '서부' 팀 실적을 표시해 보겠습니다.
(1) 데이터 내부의 셀을 하나 선택하고 [데이터] 탭 - [정렬 및 필터] 그룹 - [지우기]'를 클릭하여 이전의 필터링 결과를 초기화합니다.
(2) [날짜] 필드의 필터 버튼을 누르고 '(모두 선택)' 항목 앞에 있는 체크 표시를 해제합니다. '1월'을 클릭하여 체크 표시를 하고 [확인]을 누릅니다.
(이미지: 아이엑셀러 닷컴)
(3) 1월 데이터만 필터링됩니다. 계속해서 '서부' 팀 실적을 다시 필터링합니다. 방법은 앞에서와 거의 비슷합니다. 필드만 다를 뿐이죠. [팀명] 필드의 필드의 필터 버튼을 누르고 '(모두 선택)' 항목 앞에 있는 체크 표시를 해제합니다. '서부'를 클릭하여 체크 표시를 하고 [확인]을 누릅니다.
(이미지: 아이엑셀러 닷컴)
(4) 1월의 서부팀 데이터만 표시됩니다.
(이미지: 아이엑셀러 닷컴)
Excel에서 고급 필터 사용하는 방법
보다 복잡하고 정확한 필터가 필요하거나 기존 데이터가 있는 곳이 아닌 별도의 장소에 필터링된 결과를 표시하려면 Excel의 고급 필터(Advanced Filter)를 사용합니다.
조건 범위 만들기
고급 필터를 사용하려면 '조건 범위'를 작성해 두어야 합니다. 조건 범위는 시트의 빈 영역에 작성할 수도 있지만 원본 데이터 상단에 만들어 두면 편리합니다.
(1) 1:3행 머리글을 이용하여 행 전체를 범위로 지정합니다.
(이미지: 아이엑셀러 닷컴)
(2) 선택한 행 머리글을 우클릭하고 [삽입] 메뉴를 선택하거나 단축키인 Ctrl + Plus(+) 키를 누릅니다.
(이미지: 아이엑셀러 닷컴)
(3) 원본 데이터의 필드명 부분(B5:F5)을 복사해서 B1:F1 영역에 붙여넣기 합니다. 손으로 직접 입력해도 됩니다만, '조건 범위'의 제목과 원본 데이터의 제목이 조금이라도 다르면(예를 들어 스펠링이나 띄어 쓰기) 원하는 결과를 얻을 수 없으므로 복사해서 사용하는 것이 좋습니다.
(이미지: 아이엑셀러 닷컴)
이렇게 해서 고급 필터를 사용할 준비가 끝났습니다.
필터 기준 입력하기
'동부' 팀의 'A0001' 품목 중에서 '수량'이 100 미만인 데이터를 필터링해 볼까요? 조건이 이 정도만 되어도 자동 필터로는 쉽지 않겠죠?
'필터 기준'을 작성할 때에는 지정할 조건이 '그리고(AND)'로 연결되는 지, 아니면 '또는(OR)'으로 연결되는 지 이해하는 것이 필수적입니다. 위의 경우, '동부'와 'A0001'은 AND, 수량이 100 미만은 OR로 연결되어야 합니다. '필터 기준'에서 AND는 가로 방향(그림에서 파란선), OR는 세로 방향(보라색 선)에 작성합니다.
(이미지: 아이엑셀러 닷컴)
처음 접하면 '이게 먼 소린가?' 할 수도 있지만 조건을 달리 지정해 가면서 몇 번만 반복해 보면 어렵지 않게 이해가 될 겁니다.
고급 필터 적용하기
(1) 데이터 내부의 셀을 하나 선택하고 [데이터] 탭 - [정렬 및 필터] 그룹 - [고급]을 선택합니다.
(2) [고급 필터] 대화상자가 나타나면 다음과 같이 지정합니다.
(이미지: 아이엑셀러 닷컴)
(3) [확인] 버튼을 클릭하면 지정한 조건에 해당하는 데이터가 지정한 장소에 복사됩니다. '동부' 팀의 'A0001' 품목에 해당하는 데이터는 수량에 상관없이 표시되어 있고, 이 조건에 해당하지 않는 데이터 중에서는 수량이 100 미만인 것만 추출되었음을 알 수 있습니다.
(이미지: 아이엑셀러 닷컴)
'조건 범위'만 제대로 작성하면 다양한 조건에 해당하는 데이터를 필터링할 수 있을 것 같은 느낌이 들죠? 앞에서 소개한 것은 고급 필터의 가장 기본적인 내용입니다. 이것 말고도 고급 필터를 활용하는 방법은 많이 있습니다만 그것은 다른 기회에 소개하도록 하고 이 정도만 알아도 많은 일들을 할 수 있으니 AND와 OR 조건에 대해 잘 정리해 두세요.
보너스: Filter 함수로 필터링하기
Excel 2019 버전 이상에서는(예: Excel 2021, Microsoft 365) Filter 함수를 사용할 수 있습니다. 이 함수를 이용하면 셀 참조를 이용하여 숫자, 텍스트, 날짜로 필터링 할 수 있습니다.
단일 조건 필터링
FIlter 함수는 다음과 같은 형식으로 사용합니다.
=FILTER(배열, 포함, [if_empty])
복잡해 보이지만 실제 사용 예를 보면 이해하기 쉽습니다. '품목'이 'A0001'인 데이터를 필터링해 보겠습니다. 필터 결과를 표시할 셀(H6)에 다음 수식을 작성합니다.
=filter(B6:F205,C6:C205="A0001")
수식을 우리 말로 번역(?)하자면, "B6:F205 영역 중에서, C6:C206 셀의 값이 "A0001"에 해당하는 자료를 필터링하라" 정도가 되겠군요.
(이미지: 아이엑셀러 닷컴)
이렇게 입력하고 Enter를 누르면 지정한 조건에 해당하는 데이터가 실시간으로 표시됩니다.
(이미지: 아이엑셀러 닷컴)
여러 조건 필터링 ***
*** 아래 수식은 입문자용 강의의 범위를 벗어나는 수식입니다. 앞에서 다룬 고급 필터를 FILTER 함수로도 구현할 수 있음을 보여드리기 위한 것이므로 초보님들은 '이렇게도 할 수 있구나' 정도로 보고 넘어가세요. 괜히 수식을 이해하려고 하다가 스트레스 받지 마시기 바랍니다.
고급 필터를 설명할 때 AND(가로 방향, 파란색 선)와 OR(세로 방향, 보라색 선) 조건에 대해 소개했습니다. Filter 함수로도 가능합니다. '동부' 팀의 'A0001' 품목 중에서 '수량'이 100 미만인 데이터를 Filter 함수로 필터링하려면 다음과 같이 합니다.
