Excel에서 여러 시트의 조건을 만족하는 값을 합산하는 방법 설명

Excel은 비즈니스 및 개인 용도로 데이터를 관리하는 데 필수적인 도구입니다. 조건을 충족하는 데이터를 여러 시트에서 합산하는 효율적인 방법을 알면 매우 유용합니다. 이 기사에서는 여러 시트에서 데이터를 합산하기 위해 SUMIFS 함수를 사용하는 방법, 계산 시간을 단축하기 위해 셀 범위를 제한하는 방법, 데이터를 단일 시트로 통합하는 방법을 설명합니다. 이러한 기술을 이해하면 Excel 기술을 한 단계 높일 수 있습니다.

SUMIFS 함수 사용하기

조건을 충족하는 데이터를 여러 시트에서 합산하기 위해 Excel에서 SUMIFS 함수를 사용하는 것이 편리합니다. SUMIFS 함수는 여러 기준을 충족하는 셀의 합을 계산하는 함수입니다. 여기에서는 각 시트에 대해 SUMIFS 함수를 사용한 후 결과를 합산하는 방법을 설명합니다.

SUMIFS 함수의 기본 사용법

SUMIFS 함수의 기본 구문은 다음과 같습니다:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

예를 들어, “이번 달” 및 “지난달” 시트에서 사과의 값을 합산하려면 다음과 같이 사용할 수 있습니다:

=SUMIFS(ThisMonth!C:C, ThisMonth!A:A, "fruit", ThisMonth!B:B, "apple") + SUMIFS(LastMonth!C:C, LastMonth!A:A, "fruit", LastMonth!B:B, "apple")

여러 시트에서 SUMIFS 적용하기

여러 시트에서 조건을 충족하는 데이터를 합산할 때는 각 시트에 대해 SUMIFS 함수를 사용한 후 결과를 합산합니다. 다음은 예제입니다:

=SUM(SUMIFS(ThisMonth!C:C, ThisMonth!A:A, "fruit", ThisMonth!B:B, "apple"), SUMIFS(LastMonth!C:C, LastMonth!A:A, "fruit", LastMonth!B:B, "apple"))

이와 같이 각 시트에서 조건을 충족하는 데이터를 SUMIFS 함수를 사용하여 효율적으로 합산할 수 있습니다. 다음으로 셀 범위를 제한하여 계산 시간을 단축하는 방법을 설명합니다.

셀 범위 제한으로 계산 시간 단축

대량의 데이터를 처리할 때 전체 열을 지정하여 계산하는 것은 오랜 시간이 걸릴 수 있습니다. 셀 범위를 제한함으로써 계산 효율성을 크게 향상시킬 수 있습니다.

지정된 범위로 SUMIFS 사용하기

SUMIFS 함수를 사용하여 특정 셀 범위를 지정하면 계산 시간을 단축할 수 있습니다. 다음은 예제입니다:

=SUMIFS($C$1:$C$100, $A$1:$A$100, "fruit", $B$1:$B$100, "apple") + SUMIFS(LastMonth!$C$1:$C$100, LastMonth!$A$1:$A$100, "fruit", LastMonth!$B$1:$B$100, "apple")

특정 범위를 지정하면 계산이 해당 셀로 제한되어 처리 속도가 빨라집니다.

효율적인 셀 범위 설정

범위를 설정할 때 데이터 양에 따라 적절한 범위를 선택합니다. 예를 들어, 데이터가 A1에서 A100까지만 있을 경우 전체 열을 지정할 필요가 없습니다. 이는 계산 효율성을 크게 향상시킵니다.

다음으로 계산을 위해 데이터를 단일 시트로 통합하는 방법을 설명합니다.

데이터를 단일 시트로 통합

여러 시트의 데이터를 처리할 때 계산 전에 데이터를 단일 시트로 통합하면 수식이 단순해지고 관리가 쉬워집니다.

데이터 통합의 이점

데이터를 단일 시트로 통합하면 다음과 같은 이점이 있습니다:

수식이 짧아지고 이해하기 쉬워집니다
데이터 무결성 유지가 쉬워집니다
관리 및 수정이 용이해집니다

데이터 통합 단계

데이터를 통합하는 구체적인 단계는 다음과 같습니다:

새 시트 만들기
“통합 데이터” 시트를 만들고 다른 시트에서 데이터를 복사하여 붙여넣습니다.
데이터 정리
열 A에서 C까지 데이터를 정리하고 각 시트에서 데이터를 연속적으로 붙여넣습니다. 예를 들어:

열 A: 카테고리 (예: 과일)
열 B: 항목 이름 (예: 사과)
열 C: 값 (예: 수량)

수식 만들기
SUMIFS 함수를 사용하여 통합 데이터에 대한 합계를 계산합니다. 다음은 예제입니다:

=SUMIFS(C:C, A:A, "fruit", B:B, "apple")

통합 데이터 시트 사용 예

예를 들어, “이번 달” 및 “지난달” 시트에서 데이터를 “통합 데이터” 시트로 통합하면 다음과 같은 수식을 사용할 수 있습니다:

=SUMIFS(ConsolidatedData!C:C, ConsolidatedData!A:A, "fruit", ConsolidatedData!B:B, "apple")

데이터를 단일 시트로 통합하면 수식이 단순해지고 데이터 관리가 쉬워집니다.

다음으로 여러 시트를 처리할 때의 복잡성과 이에 대한 대책을 설명합니다.

여러 시트 처리의 복잡성

여러 시트를 처리할 때 수식이 복잡해지는 경향이 있습니다. 이는 나중에 수정이 어려워지고 오류의 위험이 증가할 수 있습니다.

복잡성의 주요 원인

여러 시트를 처리할 때 복잡성의 주요 원인은 다음과 같습니다:

수식이 길어지고 읽기 어려워짐
시트 간의 데이터 무결성 유지
시트 수가 증가함에 따라 관리가 어려워짐

복잡성을 줄이기 위한 대책

복잡성을 줄이기 위해 다음 대책을 고려하십시오:

수식을 나누어 관리하기

긴 수식을 별도의 셀에 나누어 계산하면 가독성을 향상시킬 수 있습니다. 예를 들어, 각 시트에서 개별적으로 계산한 후 최종 셀에서 결과를 합산합니다.

이름 지정 범위 사용

이름 지정 범위를 사용하면 수식의 셀 참조를 더 쉽게 이해할 수 있습니다. 예를 들어, 범위 A1:A100을 “fruit”로 정의하고 해당 이름을 수식에서 사용합니다.

=SUMIFS(fruit, category, "apple")

통합 시트 만들기

앞서 언급했듯이 데이터를 단일 시트로 통합하면 수식이 단순해지고 관리가 쉬워집니다.

시트 수 증가 처리

시트 수가 증가함에 따라 개별 시트 데이터를 관리하는 것이 어려워집니다. 다음 방법을 고려하십시오:

정기적으로 데이터를 통합하여 단일 시트로 관리
VBA(Visual Basic for Applications)를 사용하여 자동 데이터 집계를 위한 스크립트 생성

여러 시트를 처리하는 복잡성을 이해하고 적절한 대책을 취하면 Excel을 더 효율적으로 사용할 수 있습니다.

다음으로 SUM 및 IF 함수를 결합한 배열 수식을 사용하는 방법을 설명합니다.

배열 수식 사용하기

이 섹션에서는 여러 조건에 따라 값을 합산하기 위해 SUM 및 IF 함수를 결합한 배열 수식의 사용을 소개합니다. 이 방법은 단일 수식에서 복잡한 조건을 처리할 수 있어 강력합니다.

배열 수식이란?

배열 수식은 여러 값을 한 번에 처리하는 수식입니다. 일반 수식과 달리 배열 수식은 여러 셀을 참조하고 단일 값으로 결과를 반환합니다. 배열 수식을 입력하려면 Ctrl + Shift + Enter 키를 누릅니다.

배열 수식의 기본 예제

예를 들어, “이번 달” 시트에서 사과의 값을 합산하려면 다음 배열 수식을 사용합니다:

=SUM(IF((A:A="fruit")*(B:B="apple"), C:C))

이 수식을 입력한 후 Ctrl + Shift + Enter 키를 눌러 수식을 {}로 감싸 배열 수식으로 인식하고 올바르게 계산되도록 합니다.

여러 시트에서 배열 수식 사용하기

배열 수식을 사용하여 여러 시트의 데이터를 처리하는 것도 가능합니다. 그러나 배열 수식은 일반적으로 단일 시트 내 계산으로 제한됩니다. 데이터를 단일 시트로 통합한 후 배열 수식을 사용하는 것이 편리합니다.

=SUM(IF((ConsolidatedData!A:A="fruit")*(ConsolidatedData!B:B="apple"), ConsolidatedData!C:C))

배열 수식 사용 시 주의사항

배열 수식을 사용할 때 주의사항은 다음과 같습니다:

배열 수식은 더 많은 계산이 필요하므로 대량의 데이터에서는 성능에 영향을 줄 수 있습니다.
배열 수식을 입력할 때는 항상 Ctrl + Shift + Enter 키를 눌러야 합니다. Enter 키만 누르면 올바르게 계산되지 않습니다.
Excel 버전에 따라 배열 수식을 처리하는 방식이 다를 수 있습니다. 최신 버전을 사용하는 것이 좋습니다.

배열 수식을 효과적으로 사용하면 복잡한 조건 합산을 쉽게 구현할 수 있습니다. 다음으로 실제 비즈니스 시나리오에서의 응용 예제를 설명합니다.

응용 예제

여기에서는 실제 비즈니스 시나리오에서 Excel의 SUMIFS 함수와 배열 수식을 사용하여 여러 시트의 데이터를 효율적으로 처리하는 방법을 소개합니다.

판매 데이터 집계

회사가 월별 판매 데이터를 별도의 시트로 관리하는 경우 특정 제품의 연간 판매를 쉽게 집계할 수 있습니다. 각 월별 시트를 “1월”, “2월”, …, “12월”로 이름을 지정하고 기준을 사용하여 제품의 판매를 합산합니다.

=SUM(SUMIFS(January!C:C, January!A:A, "Product A"), SUMIFS(February!C:C, February!A:A, "Product A"), ..., SUMIFS(December!C:C, December!A:A, "Product A"))

이와 같이 하면 단일 수식으로 Product A의 연간 판매를 쉽게 계산할 수 있습니다.

재고 관리 최적화

재고 관리를 할 때 각 창고의 재고를 월별 시트로 관리하면 특정 제품의 전체 창고 재고를 합산할 수 있습니다.

=SUM(SUMIFS(Warehouse1!C:C, Warehouse1!A:A, "Product B"), SUMIFS(Warehouse2!C:C, Warehouse2!A:A, "Product B"), ..., SUMIFS(WarehouseN!C:C, WarehouseN!A:A, "Product B"))

이 방법을 사용하면 모든 창고의 총 재고를 한 번에 확인할 수 있어 재고 관리 효율성을 높일 수 있습니다.

프로젝트 예산 관리

프로젝트 예산을 별도의 시트로 관리하는 경우 특정 항목의 총 예산을 모든 프로젝트에 대해 합산할 수 있습니다. 예를 들어, 각 프로젝트 시트에서 “인건비” 예산을 합산합니다.

=SUM(SUMIFS(ProjectA!C:C, ProjectA!A:A, "Labor Costs"), SUMIFS(ProjectB!C:C, ProjectB!A:A, "Labor Costs"), ..., SUMIFS(ProjectN!C:C, ProjectN!A:A, "Labor Costs"))

이 수식으로 모든 프로젝트의 총 인건비 예산을 쉽게 파악할 수 있습니다.

고객 데이터 분석

고객 데이터를 월별 시트로 관리하는 경우 지역 또는 세그먼트별 고객 수를 합산할 수 있습니다. 예를 들어, 매월 “동부 지역”의 고객 수를 합산합니다.

=SUM(SUMIFS(January!C:C, January!A:A, "East"), SUMIFS(February!C:C, February!A:A, "East"), ..., SUMIFS(December!C:C, December!A:A, "East"))

이 방법을 사용하면 지역별 고객 동향을 쉽게 분석할 수 있습니다.

이러한 응용 예제를 참고하여 Excel의 SUMIFS 함수와 배열 수식을 사용하여 여러 시트의 데이터를 효율적으로 관리하고 분석하는 기술을 개발할 수 있습니다.

다음으로 학습한 내용을 확인하기 위한 연습 문제를 제공합니다.

연습 문제

여기서는 학습한 내용을 기반으로 시도해 볼 연습 문제를 제공합니다. 이러한 문제를 해결하면 Excel의 SUMIFS 함수와 배열 수식에 대한 이해가 깊어질 것입니다.

연습 문제 1: 월별 판매 집계

“1월” 및 “2월” 시트에 판매 데이터가 있다고 가정합니다. 각 시트에는 “제품 이름” 및 “판매 금액” 데이터가 포함되어 있습니다. 1월과 2월 동안 특정 제품 “Product A”의 판매를 합산하는 수식을 작성하십시오.

January 시트:
열 A: 제품 이름
열 B: 판매 금액

February 시트:
열 A: 제품 이름
열 B: 판매 금액

필요한 수식을 작성하십시오.

연습 문제 2: 재고 총합

“Warehouse1” 및 “Warehouse2” 시트에 재고 데이터가 있다고 가정합니다. 각 시트에는 “제품 이름” 및 “재고 수량” 데이터가 포함되어 있습니다. 모든 창고에서 특정 제품 “Product B”의 총 재고를 합산하는 수식을 작성하십시오.

Warehouse1 시트:
열 A: 제품 이름
열 B: 재고 수량

Warehouse2 시트:
열 A: 제품 이름
열 B: 재고 수량

필요한 수식을 작성하십시오.

연습 문제 3: 여러 조건의 배열 수식

“Data” 시트에 “지역”, “세그먼트”, “고객 수”에 대한 정보가 있다고 가정합니다. 특정 “서부 지역” 및 “VIP” 세그먼트의 고객 수를 합산하는 배열 수식을 작성하십시오.

Data 시트:
열 A: 지역
열 B: 세그먼트
열 C: 고객 수

필요한 배열 수식을 작성하십시오.

연습 문제 4: 예산 관리

“Project A” 및 “Project B” 시트에 예산 데이터가 있다고 가정합니다. 각 시트에는 “비용 항목” 및 “예산 금액” 데이터가 포함되어 있습니다. 모든 프로젝트에서 특정 항목 “마케팅”에 대한 총 예산을 합산하는 수식을 작성하십시오.

Project A 시트:
열 A: 비용 항목
열 B: 예산 금액

Project B 시트:
열 A: 비용 항목
열 B: 예산 금액

필요한 수식을 작성하십시오.

이 연습 문제를 해결하면 여러 시트의 데이터를 합산하고 조건부로 합산하는 기술을 더욱 향상시킬 수 있습니다. 문제를 풀면서 답을 확인하십시오.

다음으로 지금까지 배운 내용을 요약합니다.

요약

여러 시트에서 조건을 충족하는 데이터를 합산하는 방법을 자세히 설명했습니다. SUMIFS 함수와 배열 수식을 사용하여 복잡한 데이터 집계를 효율적으로 수행할 수 있습니다. 또한 셀 범위를 제한하거나 데이터를 단일 시트로 통합하여 계산 효율성을 높이는 방법도 배웠습니다. 이러한 기술을 활용하면 Excel에서 데이터 관리 및 분석이 더욱 효과적이 됩니다. 실제 비즈니스 시나리오와 일상적인 작업에서 이러한 기술을 시도해 보십시오.