최근 엑셀의 요약 함수를 활용해 대량의 데이터를 손쉽게 정리하는 방법에 대한 글을 소개했는데요, 그 글에서는 워크시트의 모든 데이터를 기준으로 계산했습니다. 그렇다면 전체 데이터 중 일부만 골라서 보고, 그 일부에 대해서만 요약하고 싶다면 어떻게 해야 할까요?
엑셀에서는 열(column)에 필터를 적용해 조건에 맞지 않는 행을 숨길 수 있습니다. 또한 특수 함수를 사용하면 필터링된 데이터만으로 요약 계산을 수행할 수도 있습니다.
이 글에서는 엑셀에서 필터를 만드는 방법과, 내장 함수를 활용해 필터링된 데이터를 요약하는 방법까지 차근차근 살펴보겠습니다.
엑셀에서 간단한 필터 만들기
엑셀에는 간단한 필터와 고급 필터 두 가지가 있습니다. 먼저 간단한 필터부터 시작해 보겠습니다. 필터를 사용할 때는 맨 위에 레이블(제목) 행을 하나 두는 것이 좋습니다. 반드시 필요한 것은 아니지만, 필터 작업이 훨씬 수월해집니다.
아래 예시는 임의로 만든 데이터이며, City(도시) 열에 필터를 적용해 보겠습니다. 방법은 아주 간단합니다. 리본 메뉴에서 데이터 탭을 클릭한 다음 필터 버튼을 클릭하기만 하면 됩니다. 시트에서 데이터 범위를 선택하거나 첫 번째 행을 클릭할 필요도 없습니다.
필터 버튼을 클릭하면 첫 번째 행의 각 열 오른쪽 끝에 작은 드롭다운 버튼이 자동으로 추가됩니다.
이제 City 열의 드롭다운 화살표를 클릭해 보세요. 몇 가지 옵션이 나타나는데, 하나씩 설명해 드리겠습니다.
맨 위에서는 City 열의 값을 기준으로 모든 행을 빠르게 정렬할 수 있습니다. 이때 주의할 점은, 정렬 시 City 열의 값뿐 아니라 해당 행 전체가 함께 이동한다는 것입니다. 덕분에 데이터의 구조가 그대로 유지됩니다.
또한 워크시트 맨 앞에 ID라는 열을 추가하고 1번부터 행 개수만큼 번호를 매겨두면 좋습니다. 이렇게 해두면 원래 순서로 되돌리고 싶을 때 언제든 ID 열 기준으로 정렬하면 됩니다.
보시는 것처럼 이제 스프레드시트의 모든 데이터가 City 열 값을 기준으로 정렬되었습니다. 아직은 숨겨진 행이 없습니다. 이제 필터 대화상자 하단의 체크박스를 살펴보겠습니다. 이 예시에서 City 열에는 고유값이 3개뿐이라 목록에 3개가 표시됩니다.
두 도시의 체크를 해제하고 하나만 남겨두면, 이제 8개 행의 데이터만 표시되고 나머지는 숨겨집니다. 필터링된 상태인지 확인하려면 맨 왼쪽의 행 번호를 보면 됩니다. 숨겨진 행이 있으면 행 번호 사이에 굵은 가로선이 생기고, 번호 색상이 파란색으로 바뀝니다.
이번에는 두 번째 열로 필터를 더 적용해 결과를 줄여보겠습니다. C열에는 각 가족의 총 인원수가 들어 있는데, 여기서 3인 이상인 가족만 보고 싶다고 가정해 보겠습니다.
C열의 드롭다운 화살표를 클릭하면 동일한 체크박스 목록이 나타납니다. 하지만 이번에는 숫자 필터를 클릭한 후 보다 큼을 선택합니다. 그 외에도 다양한 옵션이 있다는 것을 확인할 수 있습니다.
새 대화상자가 열리면 여기에 필터 조건값을 입력합니다. AND 또는 OR 조건으로 여러 기준을 추가할 수도 있습니다. 예를 들어 "2보다 크고 5와 같지 않은" 행만 표시하도록 설정할 수 있습니다.
이제 데이터가 5개 행으로 줄었습니다. New Orleans 출신이면서 3인 이상인 가족만 남은 것이죠. 어렵지 않죠? 참고로 특정 열의 필터를 해제하려면 드롭다운을 클릭한 후 "열 이름"에서 필터 지우기 링크를 클릭하면 됩니다.
지금까지 엑셀의 간단한 필터를 알아봤습니다. 사용법이 쉽고 결과도 직관적입니다. 이제 고급 필터 대화상자를 사용한 복잡한 필터링 방법을 살펴보겠습니다.
엑셀에서 고급 필터 만들기
더 복잡한 필터를 만들려면 고급 필터 대화상자를 사용해야 합니다. 예를 들어 "New Orleans에 사는 3인 이상 가족 또는 Clarksville에 사는 4인 이상 가족 중 .EDU로 끝나는 이메일 주소를 가진 경우만" 보고 싶다고 해봅시다. 간단한 필터로는 이런 조건을 처리할 수 없습니다.
이를 위해서는 시트를 약간 다르게 구성해야 합니다. 데이터 위에 몇 개의 행을 삽입하고, 첫 번째 행에 데이터의 제목 레이블을 그대로 복사해 붙여넣습니다.
고급 필터의 작동 방식은 다음과 같습니다. 먼저 상단 열에 조건을 입력한 다음, 데이터 탭의 정렬 및 필터에서 고급 버튼을 클릭합니다.
그럼 셀에 무엇을 입력해야 할까요? 예시로 돌아가 보겠습니다. 우리는 New Orleans 또는 Clarksville의 데이터만 보고 싶으므로, E2와 E3 셀에 각각 입력합니다.
다른 행에 값을 입력하면 OR 조건이 됩니다. 이제 "New Orleans의 3인 이상 가족"과 "Clarksville의 4인 이상 가족" 조건을 추가해야 합니다. C2에는 >2, C3에는 >3을 입력합니다.
>2와 New Orleans가 같은 행에 있으므로 AND 연산자로 처리됩니다. 3행도 마찬가지입니다. 마지막으로 .EDU로 끝나는 이메일 주소를 가진 가족만 남기려면 D2와 D3에 *.edu를 입력합니다. * 기호는 임의의 문자열을 의미합니다.
조건 입력이 끝나면 데이터 집합 내 아무 곳이나 클릭한 후 고급 버튼을 클릭합니다. 고급 버튼을 누르기 전에 데이터를 클릭했기 때문에 목록 범위가 자동으로 인식됩니다. 이제 조건 범위 입력창 오른쪽의 작은 버튼을 클릭합니다.
A1부터 E3까지 선택한 후 같은 버튼을 다시 클릭해 고급 필터 대화상자로 돌아갑니다. 확인을 클릭하면 데이터가 필터링됩니다!
결과적으로 모든 조건에 부합하는 3개의 결과만 남았습니다. 참고로 조건 범위의 레이블은 데이터 집합의 레이블과 정확히 일치해야 이 기능이 정상적으로 작동합니다.
이 방식으로 훨씬 더 복잡한 쿼리도 만들 수 있으니, 직접 실험해 보며 원하는 결과를 얻어보세요. 마지막으로 필터링된 데이터에 합계 함수를 적용하는 방법을 알아보겠습니다.
필터링된 데이터 요약하기
이제 필터링된 데이터에서 가족 구성원 수의 합계를 구하고 싶다면 어떻게 해야 할까요? 먼저 리본 메뉴의 지우기 버튼을 클릭해 필터를 해제합니다. 걱정하지 마세요. 고급 버튼을 클릭하고 확인만 누르면 언제든 고급 필터를 다시 적용할 수 있습니다.
데이터 집합 하단에 Total(합계)이라는 셀을 만들고, 가족 구성원 총합을 구하는 SUM 함수를 입력합니다. 이 예시에서는 =SUM(C7:C31)을 입력했습니다.
모든 가족을 기준으로 하면 총 78명입니다. 이제 고급 필터를 다시 적용하면 어떻게 될까요?
이런! 올바른 값인 11이 아니라 여전히 78이 표시됩니다. 왜 그럴까요? SUM 함수는 숨겨진 행을 무시하지 않기 때문에 모든 행을 대상으로 계산하기 때문입니다. 다행히 숨겨진 행을 제외하고 계산할 수 있는 함수가 몇 가지 있습니다.
첫 번째는 SUBTOTAL입니다. 이러한 특수 함수를 사용하기 전에 먼저 필터를 해제한 후 함수를 입력하는 것이 좋습니다.
필터를 해제한 상태에서 =SUBTOTAL(을 입력하면 여러 옵션이 담긴 드롭다운 목록이 나타납니다. 이 함수에서는 먼저 사용할 요약 함수의 종류를 숫자로 지정합니다.
이 예시에서는 SUM을 사용할 것이므로 숫자 9를 입력하거나 드롭다운에서 클릭하면 됩니다. 그다음 쉼표를 입력하고 셀 범위를 선택합니다.
Enter 키를 누르면 이전과 동일하게 78이 표시됩니다. 하지만 이제 필터를 다시 적용하면 11이 나타납니다!
훌륭합니다! 바로 원하던 결과입니다. 이제 필터를 조정할 때마다 값이 현재 표시되는 행만 기준으로 항상 갱신됩니다.
SUBTOTAL과 거의 동일하게 작동하는 두 번째 함수는 AGGREGATE입니다. 유일한 차이점은 AGGREGATE 함수에는 숨겨진 행을 무시하도록 지정하는 매개변수가 하나 더 있다는 점입니다.
첫 번째 매개변수는 사용할 요약 함수이며, SUBTOTAL과 마찬가지로 9가 SUM 함수를 의미합니다. 두 번째 매개변수에는 숨겨진 행을 무시하라는 의미의 5를 입력합니다. 마지막 매개변수는 동일하게 셀 범위입니다.
AGGREGATE 함수와 MODE, MEDIAN, AVERAGE 등 다른 요약 함수를 더 자세히 알고 싶다면 이전에 작성한 요약 함수 관련 글도 참고해 보세요.
이 글이 엑셀에서 필터를 만들고 활용하는 데 좋은 출발점이 되기를 바랍니다. 궁금한 점이 있다면 언제든 댓글로 문의하세요. 즐거운 엑셀 되세요!