Excel에서 데이터 필터링 시 하단 합계 행을 제외하고 필터링하는 방법은 무엇인가요?
Excel로 데이터를 처리할 때, 주요 수치(합계, 평균, 개수 등)를 요약하기 위해 데이터셋 하단에 합계 행을 만드는 일이 흔합니다. 그러나 Excel의 필터 기능을 사용하면 합계 행 역시 다른 데이터와 함께 필터링되어 숨겨질 수 있습니다. 특히 요약 보고서, 재무 제표, 또는 지속적인 데이터 분석 등에서는 합계 행이 시트의 나머지에 필터가 적용되더라도 항상 하단에 표시되길 원할 수 있습니다. 아래 스크린샷과 같이 합계 행이 항상 보이도록 해야 할 때가 있습니다.
이 글에서는 필터링 중에도 하단 합계 행이 항상 표시되도록 하는 다양한 실용적인 방법을 안내합니다. 각 방법은 사용 환경과 개인 선호에 맞게 활용할 수 있습니다. Excel의 내장 기능뿐만 아니라, 수식 방법과 프로그래밍을 통한 VBA 솔루션까지 다루어, 필요에 따라 다양한 옵션을 선택하실 수 있습니다.
➤ 테이블을 만들어 하단 합계 행을 제외하고 데이터 필터링하기
➤ 수식을 삽입하여 하단 합계 행을 제외하고 데이터 필터링하기
➤ VBA 코드를 이용하여 하단 합계 행을 제외하고 데이터 필터링하기
테이블을 만들어 하단 합계 행을 제외하고 데이터 필터링하기
합계 행을 필터링 시에도 항상 보이게 유지하는 가장 간단한 방법은 데이터를 공식적인 Excel 테이블로 변환하는 것입니다. Excel 테이블에는 본래 'Total Row'(합계 행) 기능이 내장되어 있어, 필터를 적용해도 데이터 하단에 합계 행이 항상 표시됩니다. 아래에 이 방법에 대한 상세 단계, 주요 사용 상황, 참고 사항을 정리했습니다.
1. 이미 합계 수식이 직접 입력된 행(테이블에 내장된 합계 행이 아닌 경우)이 있다면, 중복을 방지하기 위해 해당 행을 일시적으로 삭제하세요.
2. 실제 데이터 범위(직접 만든 합계 행은 제외)를 선택한 뒤, 삽입 > 테이블을 클릭합니다. 테이블 만들기 대화상자에서 데이터에 열 헤더가 있다면 '내 테이블에 헤더 포함'이 선택되어 있는지 확인하세요. 데이터 영역을 검토하여 모든 행이 포함되어 있는지 다시 한번 확인합니다.
3. 확인을 클릭하면 데이터가 Excel 테이블 형식으로 변환됩니다. 테이블을 선택한 상태에서 디자인 탭(혹은 일부 버전에서는 테이블 디자인 탭)으로 이동합니다. 테이블 스타일 옵션 그룹에서 '합계 행' 박스를 체크하세요. 그러면 Excel이 자동으로 하단에 새로운 합계 행을 추가합니다.
4. 이제 각 열의 필터 화살표를 이용해 데이터를 필요한 대로 필터링할 수 있습니다. '합계 행'은 항상 테이블의 마지막 행에 표시되며, 어떤 필터를 적용해도 사라지지 않습니다.
적용 가능 상황: 이 방법은 데이터가 자주 업데이트되거나 팀 내에서 공유하거나 주기적으로 보고서를 작성할 때 적합합니다. 특히 밴드 행, 구조화된 참조, 자동 서식 등 테이블의 내장 기능을 활용해야 할 때 유용합니다.
장점:
- Excel의 내장 기능과 원활하게 통합됩니다.
- 데이터가 늘어나거나 줄어들 때마다 수식을 변경할 필요가 없습니다.
- 합계 행이 자동으로 동적으로 업데이트됩니다.
제약 사항:
- 테이블 변환 시 합계 행에 입력해둔 사용자 지정 수식은 다시 설정해야 할 수 있습니다.
- 실시간 서식 처리로 인해 매우 큰 데이터셋에서는 성능이 다소 저하될 수 있습니다.
- 테이블로 변환하기 전에 수동 합계 행을 반드시 삭제하세요. 그렇지 않으면 합계가 중복 표기될 수 있습니다.
- 데이터를 추가하거나 삭제할 때는 필터를 모두 해제한 상태로 진행하면 테이블 구조가 잘 유지됩니다.
Kutools AI로 엑셀의 마법을 풀다
- 스마트 실행: 셀 작업 수행, 데이터 분석 및 차트 생성 - 간단한 명령어로 모든 것을 처리합니다.
- 사용자 정의 수식: 작업을 간소화하기 위한 맞춤형 수식을 생성합니다.
- VBA 코딩: 손쉽게 VBA 코드를 작성하고 실행합니다.
- 수식 해석: 복잡한 수식도 쉽게 이해할 수 있습니다.
- 텍스트 번역: 스프레드시트 내 언어 장벽을 허물어 보세요.
수식을 삽입하여 하단 합계 행을 제외하고 데이터 필터링하기
Excel 테이블을 사용하지 않을 경우, 데이터 하단에 수동 합계 행을 직접 두고 SUBTOTAL 함수로 처리할 수 있습니다. SUBTOTAL 함수는 Excel 필터와 동적으로 연동되며, 계산 결과는 표시된(필터에 남아있는) 행만 반영합니다. 그러나 함수 자체는 필터를 해도 숨겨지지 않아 항상 합계 행이 보여집니다. 이 방식은 고전적인 데이터 범위에도 유연하게 적용됩니다.
1. 데이터 표 바로 아래, 합계 행으로 사용할 첫 번째 셀에 아래와 같이 수식을 입력합니다(예: 값이 B2~B13 셀에 있다면 아래 수식 참고).
=SUBTOTAL(9, B2:B13)B2:B13은 데이터의 실제 열과 행 범위로 바꾸어 사용하세요. 수식의 숫자9는 '합계' 함수 지정이고, 필요에 따라1(평균),2(개수) 등 다른 함수 번호를 입력할 수 있습니다.
2. Enter 키를 누르면 합계 행에 표시된(필터적용된) 행 기준으로 합계가 바로 계산됩니다. Excel의 내장 필터 버튼을 사용하여 필터를 적용하면 합계 행은 항상 하단에 표시되고, 필터에 남아있는 행만 합산하여 현재 값을 보여줍니다.
SUBTOTAL 수식의 범위를 직접 수정해야 합니다. OFFSET이나 INDEX 같은 동적 범위 함수와 함께 사용하면 자동으로 범위가 확장됩니다.요약: SUBTOTAL 함수는 다양한 집계 기능(평균, 개수 등)을 지원하므로, 재무, 재고 관리, 보고서 작성 워크플로우에서 요약 행에 다양하게 활용할 수 있습니다.
VBA 코드를 이용하여 하단 합계 행을 제외하고 데이터 필터링하기
더 큰 데이터셋을 다루거나, 하단 합계 행이 필터에 상관없이 항상 표시되도록 자동화가 필요할 경우 VBA(Visual Basic for Applications)를 통해 프로그래밍 방식으로 필터를 관리할 수 있습니다. 이 방식은 데이터 영역이나 합계 행 위치가 동적으로 변할 때, 또는 모든 사용자의 일관성 유지를 원할 때 특히 효과적입니다.
이 VBA 예제는 데이터 영역의 마지막 행을 확인하여, 필터가 적용되어도 해당 행이 항상 표시되도록 강제합니다. 합계 행이 항상 보이게 하고, 매크로를 버튼에 연결하여 반복적으로 사용할 수도 있습니다.
이 VBA 솔루션 사용 단계:
1. Excel에서 개발자 탭으로 갑니다. 탭이 보이지 않는다면, Excel 옵션에서 '리본 메뉴 사용자 지정'의 '개발자' 항목을 체크하여 활성화해야 합니다.
2. Visual Basic을 클릭하여 VBA 편집기를 엽니다. 새 창에서 삽입 > 모듈을 클릭한 후 아래 코드를 새 모듈에 붙여넣기 합니다.
Sub AlwaysShowTotalRow()
Dim ws As Worksheet
Dim lastRow As Long
On Error Resume Next
Set ws = ActiveSheet
' Get last used row in column A
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
' Ensure total row is visible before applying filter
ws.Rows(lastRow).Hidden = False
' If filtering is active, reapply filter
If ws.FilterMode Then
ws.ShowAllData
End If
' Make sure total row remains visible
ws.Rows(lastRow).Hidden = False
End Sub3. 코드 입력 후 VBA 편집기를 닫고, Excel에서 Alt + F8을 눌러 AlwaysShowTotalRow를 선택한 다음 실행을 클릭합니다. 매크로가 마지막 행(합계 행)이 필터 적용 시에도 항상 표시되도록 만듭니다.
• 매크로는 합계 행이 열 A의 마지막 사용 행에 있다고 가정하므로, 데이터에 빈 행이나 요약 행이 여러 개 있을 경우 로직을 조정해야 합니다.
• 데이터가 자주 변경될 경우, 이 매크로를 버튼에 연결해 손쉽게 반복 사용하세요.
• 워크북에 여러 섹션이 있을 때는 이름 지정 범위 또는 특정 열의 표시 기준을 활용해 정확한 합계 행을 찾도록 설정하세요.
장점: 사용자 행동이나 필터링에 상관없이 합계 행의 표시를 자동으로 관리합니다. 대시보드, 보고서, 공유 워크북에 이상적입니다.
제약 사항: 매크로 사용 워크북과 권한이 필요하며, 복잡한 시트 배치에는 추가 맞춤 설정이 필요할 수 있습니다.
요약: 이 VBA 방식은 합계 행 직접 제어가 번거롭거나 부정확할 때 자동화된 환경에서 권장됩니다. 모든 사용 사례에 걸쳐 요약 데이터를 항상 표시하도록 도와줍니다.
최고의 오피스 생산성 도구
| 🤖 | Kutools AI 도우미: 데이터 분석에 혁신을 가져옵니다. 방법: 지능형 실행 | 코드 생성 | 사용자 정의 수식 생성 | 데이터 분석 및 차트 생성 | Kutools Functions 호출… |
| 인기 기능: 중복 찾기, 강조 또는 중복 표시 | 빈 행 삭제 | 데이터 손실 없이 열 또는 셀 병합 | 반올림(수식 없이) ... | |
| 슈퍼 LOOKUP: 다중 조건 VLOOKUP | 다중 값 VLOOKUP | 다중 시트 조회 | 퍼지 매치 .... | |
| 고급 드롭다운 목록: 드롭다운 목록 빠르게 생성 | 종속 드롭다운 목록 | 다중 선택 드롭다운 목록 .... | |
| 열 관리자: 지정한 수의 열 추가 | 열 이동 | 숨겨진 열의 표시 상태 전환 | 범위 및 열 비교 ... | |
| 추천 기능: 그리드 포커스 | 디자인 보기 | 향상된 수식 표시줄 | 통합 문서 & 시트 관리자 | 자동 텍스트 라이브러리 | 날짜 선택기 | 데이터 병합 | 셀 암호화/해독 | 목록으로 이메일 보내기 | 슈퍼 필터 | 특수 필터(굵게/이탤릭/취소선 필터 등) ... | |
| 15대 주요 도구 세트: 12 가지 텍스트 도구(텍스트 추가, 특정 문자 삭제, ...) | 50+ 종류의 차트(간트 차트, ...) | 40+ 실용적 수식(생일을 기반으로 나이 계산, ...) | 19 가지 삽입 도구(QR 코드 삽입, 경로에서 그림 삽입, ...) | 12 가지 변환 도구(단어로 변환하기, 통화 변환, ...) | 7 가지 병합 & 분할 도구(고급 행 병합, 셀 분할, ...) | ... 등 다양 |
Kutools for Excel과 함께 엑셀 능력을 한 단계 끌어 올리고, 이전에 없던 효율성을 경험하세요. Kutools for Excel은300개 이상의 고급 기능으로 생산성을 높이고 저장 시간을 단축합니다. 가장 필요한 기능을 바로 확인하려면 여기를 클릭하세요...
Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 더욱 간편하게 만듭니다
- Word, Excel, PowerPoint에서 탭 편집 및 읽기를 활성화합니다.
- 새 창 대신 같은 창의 새로운 탭에서 여러 파일을 열고 생성할 수 있습니다.
- 생산성이50% 증가하며, 매일 수백 번의 마우스 클릭을 줄여줍니다!
모든 Kutools 추가 기능. 한 번에 설치
Kutools for Office 제품군은 Excel, Word, Outlook, PowerPoint용 추가 기능과 Office Tab Pro를 한 번에 제공하여 Office 앱을 활용하는 팀에 최적입니다.
- 올인원 제품군 — Excel, Word, Outlook, PowerPoint 추가 기능 + Office Tab Pro
- 설치 한 번, 라이선스 한 번 — 몇 분 만에 손쉽게 설정(MSI 지원)
- 함께 사용할 때 더욱 효율적 — Office 앱 간 생산성 향상
- 30일 모든 기능 사용 가능 — 회원가입/카드 불필요
- 최고의 가성비 — 개별 추가 기능 구매 대비 절약