엑셀 SUBTOTAL함수 활용법과 다양한 예제

엑셀 SUBTOTAL함수 활용법과 다양한 예제

엑셀에서 데이터를 조합하고 조건부로 집계하는 데 SUBTOTAL 함수가 얼마나 강력한지 알고 계신가요?
이 블로그에서는 SUBTOTAL 함수의 작동 방식과 다양한 용도를 자세히 살펴보고 사용자 정의된 다양한 예제를 제공해 드리겠습니다. 이 함수를 마스터하면 데이터 분석 및 보고 작업을 강화하고 시간을 절약할 수 있습니다.

---

---

엑셀 SUBTOTAL함수 기본 이해하기 개요 및 구문 설명

엑셀 SUBTOTAL함수는 데이터 목록 또는 범위 내의 수치 데이터를 다양한 조건에 따라 계산하는 강력한 도구입니다. 통계적 분석, 데이터 피벗, 요약 작업에 널리 사용됩니다. SUBTOTAL 함수의 구문은 다음과 같습니다.

=SUBTOTAL(합계_함수, 참조1, 참조2, ... )

여기서

• 합계_함수 SUM, COUNT, AVERAGE와 같은 계산에 사용할 함수입니다.
• 참조1, 참조2, ... 계산에 포함할 데이터 범위입니다.

SUBTOTAL 함수는 데이터 범위에 필터나 조건 서식이 적용되었을 때 유용합니다. 숨겨진 행이나 열을 포함하거나 제외하고 계산할 수 있습니다. 또한, 데이터 표에 대역 헤더 및 요약 열이 있는 경우 피벗 테이블을 생성하지 않고도 중간 계산을 수행할 수 있습니다.

SUBTOTAL함수 활용 사례 구역별 데이터 합계 및 평균 계산

시나리오	SUBTOTAL 함수	설명
구역별 매출 합계 계산	`=SUBTOTAL(9, A2A10)`	"숨김" 상태(필터된) 데이터를 포함하여 A2A10 범위의 매출값을 합계
특정 구역의 매출 합계 계산	`=SUBTOTAL(109, A2A10, 3)`	A2A10 범위에서 "동북" 구역의 매출값을 합계
구역별 평균 매출 계산	`=SUBTOTAL(101, A2A10, 3)`	A2A10 범위에서 "동북" 구역의 매출값 평균
여러 구역 매출 합계 및 평균 계산	`=SUBTOTAL(109, A2A10, 3) + SUBTOTAL(109, A2A10, 6)`	"동북" 및 "서부" 구역의 매출값을 합계 및 평균
숨김 데이터 제외한 구역별 합계 계산	`=SUBTOTAL(3, A2A10)`	A2A10 범위에서 필터로 숨겨진 데이터를 제외하고 매출값 합계
특정 조건에 따른 구역별 합계 계산	`=SUBTOTAL(109, A2A10, 3)*ISNUMBER(B2B10)`	"동북" 구역의 매출값 합계를 필터로 "True" 값만 포함

조건부 SUBTOTAL함수 활용 특정 조건에 따른 데이터 집계

SUBTOTAL 함수를 사용하여 특정 조건에 따라 데이터를 집계할 수 있습니다. 다음은 이를 위한 간단한 방법입니다.

• "특정 조건에 해당하는 값의 합계를 계산하려면 SUBTOTAL(10, 범위, [기준1], 기준2, ...)] 함수를 사용하세요. 첫 번째 인수인 10은 조건부 합계를 나타냅니다."
• "결과 표시의 비어 있는 셀을 선택하고 식 입력란에 다음과 같이 식을 입력하세요." =SUBTOTAL(10, A2A100, ">"&B1)
• "이 식은 B1 셀의 값보다 큰 A2A100 범위의 값을 합산합니다."

조건부 SUBTOTAL함수는 다음과 같은 실제 상황에 유용하게 사용할 수 있습니다.

• "판매 데이터에서 특정 날짜 범위 내에 발생한 매출을 집계합니다.예를 들어, 범위가 A2A100이고 B1 셀에 시작 날짜, B2 셀에 종료 날짜를 입력한 경우, 다음과 같이 식을 사용하여 해당 범위 내의 매출 합계를 계산할 수 있습니다." =SUBTOTAL(10, A2A100, ">"&B1, "<"&B2)

SUBTOTAL 함수의 조건부 사용은 복잡한 데이터 분석에서 강력한 도구가 될 수 있으며, 데이터 내부의 패턴과 통찰력을 파악하는 데 도움이 될 수 있습니다.

SUBTOTAL함수와 PivotTable 병행 복잡한 데이터 분석 및 그래피컬 표현

SUBTOTAL 함수는 PivotTable과 병행하여 더욱 복잡한 데이터 분석을 수행하고 그래피컬 표현을 제공할 수 있습니다. 다음은 단계별 방법입니다.

1. PivotTable 생성 데이터 범위를 선택하고 '삽입' 탭에서 'PivotTable'을 클릭합니다.
2. 데이터 필드 추가 행 또는 열 영역에 데이터 필드를 드래그 앤 드롭하여 PivotTable 구조를 만듭니다.
3. SUBTOTAL 함수 사용 계산 영역에서 '합계' 드롭다운 메뉴를 클릭하고 '값과 함께 함수' > 'SUBTOTAL'을 선택합니다.
4. 함수 매개변수 지정 다음 창에서 적용할 함수(예 SUM, AVERAGE, COUNT)를 선택하고 데이터가 포함된 범위를 지정합니다.
5. 함수 선택 필요에 맞는 필터 처리를 위해 '함수 대분류' 드롭다운 메뉴에서 '포함' 또는 '제외'를 선택합니다.
6. 데이터 그래피컬 표현 PivotTable의 데이터 영역에서 숫자 값을 마우스 오른쪽 버튼으로 클릭하고 '차트 삽입'을 선택하여 차트나 그래프를 생성합니다.

이러한 단계를 통해 데이터의 복잡한 분석을 수행하고 그래피컬하게 표현할 수 있습니다. 예를 들어, 지역별 매출 데이터를 PivotTable에 표시하고 지역별 총 매출이나 특정 날짜 동안 제외된 매출과 같은 다른 데이터 포인트를 계산하고 시각화할 수 있습니다.

SUBTOTAL함수 활용 팁 계산식 보호, 필터 적용 등 실무 활용법

Q SUBTOTAL함수에서 계산식을 보호하는 방법은 무엇입니까?

A SUBTOTAL함수는 다른 계산식을 참조하는데, 이러한 계산식이 변경되면 SUBTOTAL 함수 결과에 영향을 미칠 수 있습니다. 이를 방지하려면 SUBTOTAL 함수의 인수로 ABSOLUTE 함수를 사용하여 참조 셀을 절대 참조로 지정하세요. 예를 들어, SUM(A1A10)를 절대 참조로 만들려면 SUM(ABSOLUTE(A1A10))을 사용합니다.

Q 필터링된 데이터 집합에 SUBTOTAL함수를 적용할 수 있나요?

A 예, SUBTOTAL함수는 필터링된 데이터 집합에 적용할 수 있습니다. FILTER 함수를 사용하여 원하는 데이터를 필터링한 다음, 필터링된 범위를 SUBTOTAL함수의 인수로 지정하면 됩니다. 예를 들어, 열 B에 매출이 있는 행만 필터링하여 합계를 구하려면 SUBTOTAL(9, FILTER(B1B10, B1B10>0))을 사용합니다.

Q 빈 셀이나 오류를 계산에 포함하지 않으려면 어떻게 해야 하나요?

A SUBTOTAL함수의 마지막 인수로 104를 지정하여 빈 셀과 오류를 건너뛰도록 하세요. 이렇게 하면 해당 값이 계산에 영향을 미치지 않습니다.

Q 여러 조건을 사용하여 데이터를 합계하려면 어떻게 해야 하나요?

A SUMIFS 함수를 사용하여 여러 조건을 지정하고 해당 조건을 충족하는 데이터를 합계할 수 있습니다. 예를 들어, 열 A에 국가가 미국이고 열 B에 매출이 100달러 이상인 행만 합산하려면 SUMIFS(B1B10, A1A10, "=미국", B1B10, ">100")을 사용합니다.

Q 다른 SUBTOTAL함수 인수를 활용하여 어떤 유형의 계산이 가능한가요?

A SUBTOTAL함수 인수는 다양한 계산을 지원합니다. 인수 2를 4로 설정하면 평균, 5로 설정하면 최대값, 6으로 설정하면 최소값을 계산할 수 있습니다.

---

감성과 지식을 담은, 요약으로의 작은 여행 🛤️

이 글에서 SUBTOTAL 함수의 다양한 활용법과 실제 예제를 살펴보았습니다. SUM, COUNT, AVERAGE와 같은 다양한 통계 계산을 수행할 수 있고, 조건을 필터링하여 필터링되지 않은 데이터에서 특정 데이터를 선택적으로 계산할 수 있습니다.

SUBTOTAL 함수는 대규모 데이터 세트로 작업하고 특정 요구 사항에 부합하는 통찰력 있는 결과를 추출하는 데 큰 도움이 됩니다. 데이터 분석과 리포팅의 효율성을 향상시키고 데이터 기반 의사 결정을 지원하는 중요한 기법입니다.

따뜻한 어드바이스를 드리자면, Excel의 다른 유용한 함수와 SUBTOTAL 함수를 결합하여 분석을 한 차원 높이기 바랍니다. 비교하고 실험하여 데이터에서 최대한의 가치를 이끌어 내십시오. Excel의 기능을 마스터하면 인사이트 제공, 의사 결정 지원, 복잡한 데이터 세트 관리에 있어서 자신감 있고 효율적인 사용자가 될 수 있습니다.

Related Photos