조건에 따라 Excel에서 고유한 값을 합산하려면 어떻게 해야 하나요?

주문 로그, 재무 기록 또는 설문 조사 결과와 같은 Excel 데이터 세트를 다룰 때, 한 열에서 필터 또는 다른 열의 조건에 따라 고유한 값들의 합을 계산해야 하는 경우가 많습니다. 예를 들어, 이름과 주문을 포함하는 두 개의 열로 구성된 데이터 테이블을 생각해보세요. 각 이름에 대해 중복된 값을 무시하고 고유한 주문 값만 합산하려는 경우, 이를 Excel에서 효율적으로 수행하려면 어떻게 해야 할까요? 이는 단순히 모든 일치하는 항목을 합하면 중복으로 인해 부풀려진 결과를 초래할 수 있는 많은 비즈니스나 데이터 분석 시나리오에서 공통적인 요구사항입니다.

아래 샘플 스크린샷은 일반적인 시나리오를 보여줍니다: 중복을 포함하여 이름 목록과 해당 주문 값이 제공될 때, 각 이름별로 고유한 주문 값을 합산하여 데이터를 요약하고자 합니다.

이 작업에서의 일반적인 도전 과제는 특정 기준에 따라 고유한 항목을 식별하는 것, 첫 번째 항목만 계산되도록 하는 것, 그리고 필터링된 데이터를 복사 및 붙여넣기하는 동안 발생할 수 있는 수동 오류를 방지하는 것을 포함합니다. 배열 수식, Kutools, Power Query 등 여러 실용적인 Excel 접근 방식들이 이 문제를 해결하는 데 도움이 될 수 있으며, 각각은 다양한 사용 사례에 적합합니다.

• 배열 수식을 사용하여 하나 이상의 기준에 따라 고유한 값을 합산하기
• Kutools for Excel의 고급 행 결합을 사용하여 기준에 따라 고유한 값을 합산하기
• 기타 내장 Excel 방법: 고유 합계 분석을 위해 피벗 테이블 사용하기

효율적이고 유연한 접근 방식 중 하나는 배열 수식을 사용하는 것입니다. 배열 수식을 통해 특정 기준을 충족하는 고유한 값을 요약할 수 있습니다. 데이터나 기준이 변경될 때 자동으로 계산이 업데이트되기를 원한다면 특히 효과적입니다.

다른 열의 필터 또는 조건에 따라 열에서 고유한 값만 합산하려면 다음 수식을 적용할 수 있습니다:

1. 빈 셀(예: E2)에 다음 수식을 입력합니다:

=SUM(IF(FREQUENCY(IF($A$2:$A$12=D2,MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW($B$2:$B$12)-ROW($B$2)+1),$B$2:$B$12))

수식을 확인하기 전에 다음 사항을 신중히 확인하세요:

• A2:A12: 기준(이 경우 이름)을 포함하는 범위.
• D2: 대상 조건(특정 이름 등)이 위치한 셀.
• B2:B12: 고유하게 합산하려는 값의 범위.

실제 데이터 구조에 따라 이러한 범위를 필요에 맞게 조정할 수 있습니다. 수식 오류를 방지하려면 모든 범위의 길이가 동일한지 확인하세요.

2. 이 배열 수식을 활성화하려면 입력 후 Ctrl + Shift + Enter를 동시에 누릅니다. 중괄호가 수식 주변에 나타나며, 이것이 배열 수식임을 나타냅니다. 그런 다음 요약 열의 각 해당 값에 대해 수식을 복사하도록 채우기 핸들을 드래그하세요. 이렇게 하면 각 항목에 대해 자동으로 올바른 고유 합계가 적용됩니다.

실용적인 팁: Excel 365 또는 Excel 2021을 사용 중이라면 UNIQUE 및 SUMIFS와 같은 새로운 동적 배열 함수가 이러한 계산을 더 간단하게 만들어줄 수 있지만, 위의 수식은 많은 Excel 버전에서 안정적으로 작동합니다.

=SUMIF(A2:B12, UNIQUE(D2), B2:B12)

추가 기준 처리: =SUM(SUMIFS(sum_range, criteria_range1, UNIQUE(criteria_range1), [criteria_range2, criteria2], ...)

주의 사항:

• Excel 2019 이전 버전을 사용 중이라면 배열 수식 입력(Ctrl + Shift + Enter)을 사용해야 합니다. Excel 365/2021에서는 동적 수식의 경우 일반적인 Enter로도 충분할 수 있습니다.
• 데이터 범위가 특히 큰 경우 배열 접근 방식이 느려질 수 있으므로, 데이터를 먼저 필터링하거나 매우 큰 데이터 세트의 경우 다른 방법을 사용하는 것을 고려하세요.
• 불필요한 공백이나 데이터 형식 일관성을 주의 깊게 확인하세요 — 불균일한 텍스트 또는 숫자 서식은 매칭되지 않는 오류를 유발할 수 있습니다.

팁: 두 가지 기준에 따라 모든 고유한 값을 합산해야 하는 경우 다음 확장된 배열 수식을 사용할 수 있습니다:

=SUM(IF(FREQUENCY(IF($A$2:$A$12=E2,IF($B$2:$B$12=F2,MATCH($C$2:$C$12,$C$2:$C$12,0))),ROW($C$2:$C$12)-ROW($C$2)+1),$C$2:$C$12))

이 수식은 원칙적으로 비슷하지만, B 열에서 추가적인 필터(F2를 보조 조건으로 비교)를 지원하며 C 열에서 고유한 값을 합산합니다. 선택한 요약 셀에 이 수식을 입력한 후 Ctrl + Shift + Enter를 눌러 확인하고, 필요에 따라 다른 요약 행에 적용하세요.

요약 제안: 배열 수식은 대부분의 시나리오에서 정확한 결과를 제공하지만, 항상 숨겨진 중복(예: 추가 공백이나 텍스트 형식 차이)을 다시 확인하고 요약 영역이 정확히 필터링된 목록에서 가져오는지 확인하세요.

Kutools AI로 엑셀의 마법을 풀다

• 스마트 실행: 셀 작업 수행, 데이터 분석 및 차트 생성 - 간단한 명령어로 모든 것을 처리합니다.
• 사용자 정의 수식: 작업을 간소화하기 위한 맞춤형 수식을 생성합니다.
• VBA 코딩: 손쉽게 VBA 코드를 작성하고 실행합니다.
• 수식 해석: 복잡한 수식도 쉽게 이해할 수 있습니다.
• 텍스트 번역: 스프레드시트 내 언어 장벽을 허물어 보세요.

AI 기반 도구로 엑셀 활용 능력을 강화하세요. 지금 다운로드하여 그 어느 때보다 높은 효율성을 경험해 보세요!

Kutools for Excel의 고급 행 결합을 사용하여 기준에 따라 고유한 값을 합산하기

Kutools for Excel의 고급 행 결합 기능을 사용하면 특정 조건에 따라 고유한 값만 합산하는 것이 매우 간편합니다! 몇 번의 클릭만으로 데이터를 지능적으로 그룹화하고 사용자 정의 요약 논리를 적용할 수 있습니다 — 수식 없이, 번거로움 없이, 정확한 결과만 얻을 수 있습니다.

1단계: 데이터 테이블 선택하기

헤더를 포함한 전체 테이블을 강조 표시하세요.

2단계: Kutools > 텍스트 > 고급 행 결합으로 이동하기

3단계: 그룹화 열 설정하기

팝업 대화 상자에서 그룹화하려는 열(예: 과일)을 선택하고 '작업' 섹션에서 기본 키로 설정하세요.

4단계: 고유 합계 필드 설정하기

매출 열을 선택하고 '작업' 섹션에서 원하는 계산(예: 합계)을 설정하세요.

팁: 대화 상자에서 결합된 결과를 미리 볼 수 있습니다.

5단계: 확인 클릭 고객별로 테이블이 그룹화되고 각각 고유한 제품 금액의 합계를 얻습니다.

??Kutools for Excel은 생산성을 극대화할 수 있는 300개 이상의 강력한 기능을 제공하며, 모든 기능을 30일 동안 무료로 사용해볼 수 있습니다!

기타 내장 Excel 방법: 고유 합계 분석을 위해 피벗 테이블 사용하기

Excel의 피벗 테이블 기능은 조건에 따라 데이터를 요약하는 또 다른 강력한 내장 접근 방식을 제공합니다. 피벗 테이블은 기본적으로 고유한 값을 직접 합산하지 않지만, Excel 2013 이후에는 고유 카운트 계산을 지원하여 지정된 필드의 고유 항목 수를 분석하는 데 도움이 됩니다. 이는 직접적으로 고유 값 합계를 계산하지는 않지만, 고유 카운트를 수동 조정 또는 계산된 필드와 함께 사용하여 유사한 요약을 가능하게 합니다.

장점: 피벗 테이블은 수식 암기나 VBA 코딩이 필요하지 않으며, 매우 유연한 드래그 앤 드롭 인터페이스를 제공합니다. 주기적인 보고서 작성, 그룹 분석, 빠른 개요 또는 팀 간 협력에 적합합니다. 그러나 피벗 테이블은 추가 계산이나 자동화를 위한 수식 작성보다는 요약과 분석에 가장 적합합니다.

피벗 테이블을 사용하여 고유 합계 분석을 수행하는 방법은 다음과 같습니다:

1. 데이터 범위를 선택합니다(예: A1:B12, 헤더 포함). 삽입 > 피벗 테이블로 이동합니다. 대화 상자에서 피벗 테이블을 새 워크시트 또는 기존 워크시트에 배치할지 선택합니다.
2. 피벗 테이블 필드 목록에서 이름을 행 영역으로 드래그하고, 주문을 값 영역으로 드래그합니다.
3. 값 영역의 주문 항목에 대해 드롭다운 화살표 > 값 필드 설정 > 합계로 설정합니다(중복 포함된 총 주문 합계를 표시함).

제한 사항:

• 고유 카운트 기능은 Excel 2013 이상에서만 사용 가능하며, 이전 버전에서는 수동 작업이 더 필요합니다.

요약하자면, 피벗 테이블은 대화형 요약 및 분석에 탁월하지만, 진정한 고유 합계 계산이 필요한 경우 수식 또는 VBA 기반 접근 방식과 함께 사용하는 것을 고려하세요.

관련 기사 더 보기: