Excel에서 조건에 따라 표시되는 셀만 합산하는 방법은 무엇입니까?
Excel에서는 일반적으로 사용자가 SUMIFS 함수를 사용하여 특정 조건에 따라 셀을 합산할 수 있습니다. 그러나 필터링된 데이터를 다룰 때, 단순히 SUMIFS를 적용하면 계산에 표시되는 셀과 숨겨진 셀이 모두 포함됩니다. 아래 스크린샷에 나와 있는 것처럼 특정 조건과 일치하는 표시되는(즉, 필터링되지 않은) 셀만 합산해야 하는 경우 이는 종종 잘못된 결과로 이어질 수 있습니다.
일일 보고서 작성 및 데이터 분석 작업에서 필터가 적용된 표에서 데이터를 정확하게 집계하는 것은 매우 일반적인 요구입니다. 예를 들어, 일부 필터를 적용한 후 특정 제품 또는 카테고리에 대한 판매 금액을 계산할 때 잘못된 방식으로 처리하면 의도하지 않은 데이터가 포함된 합계가 발생할 수 있으므로 화면에 표시되는 표시 데이터만 합산하는 기술을 사용하는 것이 중요합니다.
이 문서에서는 다양한 시나리오와 숙련도 수준에 적합한 몇 가지 실용적인 방법을 소개합니다. 각 방법은 장점과 가능한 한계를 가지고 있으며, 워크시트 크기, 데이터 구조 및 작업 습관에 가장 적합한 솔루션을 선택할 수 있습니다. 각 솔루션에 대한 자세한 단계와 잠재적인 오류 설명 및 더 신뢰할 수 있는 결과를 위해 계산 프로세스를 최적화하는 방법이 아래에 제공됩니다.
보조 열을 사용하여 하나 이상의 조건에 따라 표시되는 셀만 합산하기
특정 조건에 따라 표시되는 셀을 합산하는 가장 직관적이고 안정적인 방법 중 하나는 보조 열을 사용하여 표시되는 행에 대해서만 값을 반환하고, 원하는 조건과 함께 SUMIFS 함수를 활용하는 것입니다. 이 방법은 데이터 세트가 여러 방식으로 자주 필터링되거나 동료들이 쉽게 이해하거나 수정할 수 있는 계산을 설정해야 할 때 특히 효과적입니다.
장점: 설정이 간단함; 모든 논리와 계산이 워크시트에 표시됨; 소규모에서 중간 규모 테이블에 적합; 공식을 조정하거나 감사할 때 견고함.
제한 사항: 추가 열을 생성함; 행 레이아웃이 변경될 경우 수식 업데이트가 필요할 수 있음; 매우 큰 데이터 세트에서는 광범위한 사용이 번거로울 수 있음.
예를 들어, 필터링된 범위에서 "후디"라는 제품의 주문 값만 합산하려면:
1. 다음 수식을 데이터 세트 옆의 빈 열에 입력하거나 복사하세요 (예: D가 값 열이라고 가정하고 E2 셀에 입력).
채우기 핸들을 드래그하여 데이터 범위의 모든 행에 이 수식을 채웁니다. 이 수식은 행이 표시되는 경우 D열의 값을 반환하고, 행이 필터링으로 숨겨진 경우 0을 반환합니다.
2. E열에 보조 값을 생성한 후, SUMIFS 함수를 사용하여 조건에 따라 표시되는 값만 합산하세요. 예를 들어 A열에서 "후디"에 대해 합산하려면:
다음 형식으로 SUMIFS 인수를 확장하여 더 많은 조건을 추가할 수 있습니다: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], [criteria_range3, criteria3], ...). 항상 범위를 확인하여 올바른 정렬과 예상 결과를 보장하세요.
주의: 수식을 설정한 후 행을 재배치하거나 삽입 또는 삭제하면 모든 참조가 여전히 데이터 구조와 일치하는지 다시 확인하세요. 때때로 범위가 맞지 않거나 조건 셀을 업데이트하는 것을 잊어서 오류가 발생할 수 있습니다.
수식을 사용하여 조건에 따라 표시되는 셀만 합산하기
보조 열을 추가하지 않고 수식 기반 솔루션을 선호하는 경우, SUMPRODUCT, SUBTOTAL, OFFSET, ROW 및 MIN 함수를 결합하여 특정 조건에 따라 표시되는 셀을 합산할 수 있습니다. 이 접근법은 배열 수식에 익숙한 숙련된 Excel 사용자에게 적합하며, 추가 열 없이 시트를 깔끔하게 유지하고자 할 때 특히 유용합니다.
장점: 추가 워크시트 열이 필요 없음; 유연하고 동적임; 필터를 적용하거나 조건을 변경할 때 즉시 수식이 업데이트됨.
제한 사항: 배열 함수에 익숙하지 않은 사용자는 수식을 읽거나 디버깅하기 어렵게 느낄 수 있음; 매우 큰 테이블에서는 성능이 저하될 수 있음.
빈 셀(예: A2:A12에서 "후디"에 대해 표시되는 셀을 합산하고, 실제 값은 D2:D12에 있고, 조건은 A17에 있을 경우)에 다음 수식을 복사하거나 입력하세요:
수식을 입력한 후 Enter를 눌러 원하는 결과를 얻으세요, 아래와 같습니다:
주의: 이 접근법은 지정된 범위에 민감합니다 - 불일치하거나 겹치는 범위는 오류나 예상치 못한 결과를 초래할 수 있습니다. 특히 필터링이 표시되는 행의 수나 위치를 변경할 때 경계 사례를 테스트하세요.
VBA 코드를 사용하여 조건에 따라 표시되는 셀만 합산하기
고급 사용자의 경우 VBA를 사용하면 표준 수식이 성능 병목 현상을 겪거나 단일 수식으로 표현하기 어려운 다중 조건 논리를 포함한 복잡한 시나리오나 대규모 데이터 세트를 처리할 때 유연하게 표시되는 셀만 합산할 수 있습니다. VBA는 각 표시되는 행을 반복하고 조건을 테스트하며 효율적으로 합산을 계산할 수 있습니다. 이는 반복적인 보고 작업이나 요약 계산을 자동화할 때 특히 적합합니다.
장점: 대규모 데이터 세트, 여러 개 또는 동적 조건, 그리고 복잡한 논리를 쉽게 처리 가능; 수천 개의 행에서도 처리가 빠름; 수동 수식 변경으로 인한 오류 위험 감소.
제한 사항: 매크로 활성화가 필요; 일부 사용자는 VBA에 익숙하지 않거나 충분한 권한이 없을 수 있음; 변경 사항은 매크로 에디터에 접근해야 함. 중요한 데이터 세트에서 VBA를 실행하기 전에 항상 백업을 수행하세요.
1. 시작하려면 개발 도구 > Visual Basic을 클릭하여 VBA 편집기를 엽니다. 나타나는 창에서 삽입 > 모듈로 이동하여 새 모듈에 다음 코드를 붙여넣습니다:
Sub SumVisibleByCriteria()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim criteriaColumn As Range
Dim sumColumn As Range
Dim criteriaValue As Variant
Dim total As Double
Dim lastRow As Long
Dim criteriaColNum As Integer
Dim sumColNum As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
' Prompt user for criteria column and sum column
Set criteriaColumn = Application.InputBox("Select the criteria range (e.g., A2:A100):", xTitleId, Type:=8)
Set sumColumn = Application.InputBox("Select the values range to sum (e.g., D2:D100):", xTitleId, Type:=8)
criteriaValue = Application.InputBox("Enter the criteria value to match:", xTitleId, Type:=2)
If criteriaColumn Is Nothing Or sumColumn Is Nothing Or criteriaValue = "" Then
MsgBox "Operation cancelled.", vbInformation, xTitleId
Exit Sub
End If
If criteriaColumn.Rows.Count <> sumColumn.Rows.Count Then
MsgBox "Criteria and sum ranges must be the same number of rows.", vbCritical, xTitleId
Exit Sub
End If
total = 0
For Each cell In criteriaColumn
If Not cell.EntireRow.Hidden Then
If cell.Value = criteriaValue Then
total = total + sumColumn.Cells(cell.Row - criteriaColumn.Cells(1).Row + 1).Value
End If
End If
Next cell
MsgBox "The sum of visible cells matching the criteria is: " & total, vbInformation, xTitleId
End Sub2클릭하세요 
"실행" 버튼 (또는 누르세요 F5) 코드를 실행합니다. 대화 상자가 나타나서 기준 범위(예: 제품 이름), 합산할 값 범위, 그리고 필터로 사용할 값을 선택하라고 요청합니다. 매크로는 기준이 충족된 표시되는 행들만 합산하고 결과를 팝업 메시지로 보여줍니다.
실용적인 팁: 데이터나 필터를 변경한 후 합계를 다시 계산해야 하는 경우 이 VBA 코드를 사용하세요. 더 많은 입력 프롬프트 또는 논리 조건을 추가하여 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일 모든 기능 사용 가능 — 회원가입/카드 불필요
- 최고의 가성비 — 개별 추가 기능 구매 대비 절약