누계(또는누적 합계)는 많은 경우에 매우 일반적으로 사용됩니다.지금까지 값의 합이 얼마인지 알려주는 지표입니다.
예를 들어, 월별 판매 데이터가 있는 경우 누계는 해당 월의 XNUMX일부터 특정 날짜까지 발생한 판매 수를 알려줍니다.
은행 거래 명세서/원장의 현금 잔액 계산, 식사 계획의 칼로리 계산 등과 같이 합계가 자주 사용되는 다른 상황이 있습니다.
Microsoft Excel에는 누계를 계산하는 여러 가지 방법이 있습니다.
선택하는 방법도 데이터 구조에 따라 다릅니다.
예를 들어 간단한 표 형식의 데이터가 있는 경우 간단한 SUM 수식을 사용할 수 있지만 Excel 표가 있는 경우 구조적 참조를 사용하는 것이 좋습니다.파워 쿼리를 사용하여 이 작업을 수행할 수도 있습니다.
이 튜토리얼에서는 모든 것을 다룰 것입니다Excel에서 누계 계산다른 방법.
시작하겠습니다!
내용
테이블 형식 데이터를 사용하여 누계 계산
표 형식의 데이터(즉, Excel 표로 변환되지 않은 Excel의 표)가 있는 경우 몇 가지 간단한 수식을 사용하여 누계를 계산할 수 있습니다.
더하기 연산자 사용
날짜별 판매 데이터가 있고 다음을 원한다고 가정합니다.在 C열에계산하다누적 합계.
이를 수행하는 단계는 다음과 같습니다.
1 단계 – 합계를 실행하려는 첫 번째 셀인 C2 셀에 다음을 입력합니다.
= B2
이것은 B2 셀에서 동일한 판매 가치를 얻습니다.
2단계– C3 셀에 다음 수식을 입력합니다.
= C2 + B3
3단계– 전체 열에 수식을 적용합니다.채우기 핸들을 사용하여 선택하고 끌거나 C3 셀을 나머지 모든 셀에 복사하여 붙여넣을 수 있습니다(이렇게 하면 참조가 자동으로 조정되고 올바른 결과가 제공됨).
그러면 아래에 표시된 결과가 표시됩니다.
이것은 대부분의 경우 잘 작동하는 매우 간단한 방법입니다.
논리는 간단합니다. 각 셀은 그 값(전일까지의 누적 합계)을 취하고 인접한 셀의 값(현재 날짜의 판매 가치)을 추가합니다.
단 하나의 단점이 있습니다. 이 데이터세트에서 기존 행을 삭제하면 아래의 모든 셀이 참조 오류(#REF!)를 반환합니다.
데이터 세트에 이러한 가능성이 있는 경우 SUM 공식을 사용하여 다음 방법을 사용하십시오.
부분적으로 잠긴 셀 참조와 함께 SUM 사용
날짜별 판매 데이터가 있고 C 열에서 누계를 계산하려고 한다고 가정합니다.
아래는 누계를 제공하는 SUM 공식입니다.
= SUM ($ B $ 2 : B2)
이 공식이 어떻게 작동하는지 설명하겠습니다.
위의 SUM 수식에서 참조를 사용하여 $B$2:B2로 추가했습니다.
- $B$2 - 이것은 절대 참조입니다. 즉, 아래 셀에서 동일한 수식을 복사해도 이 참조가 변경되지 않습니다.따라서 아래 셀의 수식을 복사하면 수식이 SUM($B$2:B3)으로 변경됩니다.
- B2 - 이것은 참조의 두 번째 부분이며 상대 참조입니다. 즉, 공식을 아래 또는 오른쪽으로 복사하면 조정됩니다.따라서 아래 셀의 수식을 복사하면 값이 B3이 됩니다.
이 접근 방식의 좋은 점은 데이터 세트에서 행을 삭제하면 이 공식이 조정되고 여전히 올바른 누계를 제공한다는 것입니다.
Excel 시트에서 누계 계산
Excel에서 표 형식의 데이터로 작업할 때는 Excel 표로 변환하는 것이 가장 좋습니다.데이터를 더 쉽게 관리할 수 있고 파워 쿼리 및 파워 피벗과 같은 도구를 쉽게 사용할 수 있습니다.
Excel 표를 사용하면 구조적 참조(표의 데이터를 매우 쉽게 참조하고 수식에서 사용할 수 있음) 및 표에서 데이터를 추가하거나 제거할 때 참조 자동 조정과 같은 여러 이점이 있습니다.
Excel 시트에서 보여 드린 위의 공식을 계속 사용할 수 있지만 더 나은 방법을 보여 드리겠습니다.
아래와 같은 Excel 테이블이 있고 C 열의 누계를 계산하려고 한다고 가정합니다.
이를 수행하는 공식은 다음과 같습니다.
=SUM(판매 데이터[[#헤더],[판매]]:[@판매])
위의 공식은 다소 길어 보일 수 있지만 직접 작성할 필요는 없습니다.합계 수식에 표시되는 것을 구조적 참조라고 하며 Excel에서 Excel 표의 특정 데이터 요소를 참조하는 효율적인 방법입니다.
예를 들어 SalesData[[#Headers],[Sale]]는 SalesData 테이블의 Sales 헤더를 나타냅니다(SalesData는 테이블을 만들 때 지정한 Excel 테이블의 이름입니다).
반면 [@Sale]은 Sale 열의 같은 행에 있는 셀의 값을 나타냅니다.
이해를 돕기 위해 여기에서 설명하고 있지만 구조적 참조에 대해 전혀 모르더라도 이 수식을 쉽게 만들 수 있습니다.
이를 수행하는 단계는 다음과 같습니다.
- C2 셀에 =SUM(
- 판매 가치가 있는 열의 머리글인 B1 셀을 선택합니다.마우스를 사용하거나 화살표 키를 사용할 수 있습니다.Excel은 셀에 대한 구조적 참조를 자동으로 입력합니다.
- 추가: (콜론)
- B2 셀을 선택합니다.Excel은 자동으로 셀에 대한 구조적 참조를 다시 삽입합니다.
- 대괄호를 닫고 Enter 키를 누릅니다.
또한 전체 열의 수식을 복사할 필요가 없으며 Excel 시트에서 자동으로 복사합니다.
이 접근 방식의 또 다른 이점은 이 데이터 세트에 새 레코드를 추가하면 Excel 시트가 모든 새 레코드에 대한 누계를 자동으로 계산한다는 것입니다.
수식에 열 머리글을 포함했지만 수식은 머리글 텍스트를 무시하고 열의 데이터만 고려합니다.
파워 쿼리로 누계 계산
파워 쿼리는 데이터베이스에 연결하고, 여러 원본에서 데이터를 추출하고, Excel에 넣기 전에 변환하는 데 있어 놀라운 도구입니다.
이미 파워 쿼리를 사용하고 있다면 파워 쿼리 편집기 자체에서 데이터를 변환할 때 누계를 추가하는 것이 더 효율적입니다(먼저 Excel에서 데이터를 가져온 다음 위의 방법 중 하나를 사용하여 누계를 추가하는 것보다).
파워 쿼리에는 누계를 추가하는 기능이 기본 제공되지 않지만(있으면 좋겠음) 간단한 수식으로 수행할 수 있습니다.
다음과 같은 Excel 테이블이 있고 이 데이터에 누계를 추가하려고 한다고 가정합니다.
이를 수행하는 단계는 다음과 같습니다.
- Excel 표에서 아무 셀이나 선택
- 클릭 데이터
- 가져오기 및 변환 탭에서 테이블/범위에서 아이콘을 클릭합니다.그러면 Power Query 편집기에서 테이블이 열립니다.
- [선택 사항] 날짜 열이 아직 정렬되지 않은 경우 날짜 열에서 필터 아이콘을 클릭한 다음 오름차순 정렬을 클릭합니다.
- 파워 쿼리 편집기에서 열 추가 탭을 클릭합니다.
- 일반 그룹에서 색인 열 드롭다운 메뉴를 클릭합니다(색인 열 아이콘을 클릭하지 말고 그 옆에 있는 작은 검은색 경사 화살표를 클릭하여 더 많은 옵션을 표시함).
- "1부터" 옵션을 클릭합니다.이렇게 하면 1에서 시작하고 열 전체에 1씩 증가하는 숫자를 입력하는 새 인덱스 열이 추가됩니다.
- 열 추가 탭에서도 사용자 지정 열 아이콘을 클릭합니다.
- 열리는 사용자 정의 열 대화 상자에서 새 열의 이름을 입력합니다.이 예에서는 "누계"라는 이름을 사용합니다.
- 사용자 정의 열 수식 필드에 다음 수식을 입력합니다.List.Sum(List.Range(#"추가된 인덱스"[세일],0,[인덱스]))
- 대화 상자 하단에 "구문 오류가 감지되지 않았습니다"라는 확인란이 있는지 확인하십시오.
- 확인을 클릭합니다.새 누계 열이 추가됩니다.
- 인덱스 열 삭제
- 파일 탭을 클릭한 다음 닫기 및 로드를 클릭합니다.
위의 단계를 수행하면 누적 합계가 있는 테이블이 있는 통합 문서에 새 워크시트가 삽입됩니다.
이제 간단한 공식을 사용하는 이전 접근 방식에 비해 이러한 단계가 너무 많다고 생각한다면 맞습니다.
이미 데이터 세트가 있고 누계를 추가하기만 하면 되는 경우 파워 쿼리를 사용하지 않는 것이 가장 좋습니다.
파워 쿼리를 사용하는 것은 데이터베이스에서 데이터를 추출하거나 여러 다른 통합 문서의 데이터를 결합하고 프로세스에서 누적 합계를 추가해야 하는 상황에서 의미가 있습니다.
또한 파워 쿼리로 이를 자동화하면 다음에 데이터 세트가 변경될 때 다시 할 필요가 없습니다. 쿼리를 새로 고치기만 하면 새 데이터 세트를 기반으로 결과가 제공됩니다.
어떻게 작동합니까?
이제 이 접근 방식으로 어떤 일이 발생하는지 빠르게 설명하겠습니다.
파워 쿼리 편집기에서 가장 먼저 하는 일은 1에서 시작하여 셀 아래로 내려가면서 증가하는 인덱스 열을 삽입하는 것입니다.
다음 단계에서 삽입되는 다른 열의 누계를 계산할 때 이 열을 사용해야 하기 때문에 이 작업을 수행합니다.
그런 다음 사용자 정의 열을 삽입하고 다음 수식을 사용합니다.
List.Sum(List.Range(#"추가된 인덱스"[세일],0,[인덱스]))
이것은 지정된 범위의 합계를 제공하는 List.Sum 수식입니다.
범위는 List.Range 함수를 사용하여 지정됩니다.
List.Range 함수는 판매 열에 지정된 범위를 출력으로 취하며 범위는 Index 값에 따라 달라집니다.예를 들어 첫 번째 레코드의 경우 범위는 첫 번째 판매 값입니다.이 범위는 셀 아래로 내려갈수록 확장됩니다.
따라서 첫 번째 셀에 대해.List.Sum은 첫 번째 판매 값의 합계만 제공하고 두 번째 셀의 경우 처음 두 판매 값의 합계를 제공하는 식입니다.
이 접근 방식은 잘 작동하지만 대규모 데이터 세트(수천 행)의 경우 매우 느려질 수 있습니다.
기준에 따라 누계 계산
지금까지 열에 있는 모든 값의 누계를 계산하는 예를 보았습니다.
그러나 경우에 따라 특정 레코드에 대한 누계를 계산해야 할 수도 있습니다.
예를 들어, 아래에는 두 개의 다른 열에 있는 프린터와 스캐너의 누계를 계산하려는 데이터 세트가 있습니다.
이는 지정된 조건이 충족되는지 확인하면서 누계를 계산하는 SUMIF 공식을 사용하여 수행할 수 있습니다.
다음은 프린터 열에 대해 이 작업을 수행하는 공식입니다.
=SUMIF($C$2:C2,$D$1,$B$2:B2)
마찬가지로 스캐너의 누계를 계산하려면 다음 공식을 사용하십시오.
=SUMIF($C$2:C2,$E$1,$B$2:B2)
위의 공식에서 지정된 조건이 충족될 때 범위의 합계를 제공하는 SUMIF를 사용했습니다.
수식은 세 가지 매개변수를 사용합니다.
- 범위: 지정된 조건에 대해 검사할 조건의 범위입니다.
- 기준: 이것은 이 기준이 충족되는 경우에만 확인되는 기준이며, 세 번째 매개변수의 값인 합계 범위가 추가됩니다.
- [합계 범위]: 조건을 만족했을 때 더할 값의 합 범위
또한범위和합계 _ 범위매개변수에서 참조의 두 번째 부분을 잠가서 셀 아래로 이동할 때 범위가 계속 확장됩니다.이를 통해 우리는 해당 범위(따라서 누계)까지만 값을 고려하고 추가할 수 있습니다.
이 수식에서는 헤더 열(프린터 및 스캐너)을 기준으로 사용합니다.열 머리글이 표준 텍스트와 정확히 동일하지 않은 경우 표준을 하드코딩할 수도 있습니다.
여러 조건을 확인해야 하는 경우 SUMIFS 공식을 사용할 수 있습니다.
피벗 테이블의 누적 합계
피벗 테이블 결과에 누계를 추가하려는 경우 피벗 테이블의 기본 제공 기능을 사용하여 이 작업을 쉽게 수행할 수 있습니다.
한 열에 날짜가 있고 다른 열에 판매 값이 있는 아래와 같은 피벗 테이블이 있다고 가정합니다.
날짜별 누적 판매를 표시하는 추가 열을 추가하는 단계는 다음과 같습니다.
- 판매 필드를 끌어 값 영역에 놓습니다.
- 이렇게 하면 판매 가치가 있는 다른 열이 추가됩니다.
- 값 영역에서 Total Sales 2 옵션을 클릭합니다.
- 값 필드 설정 옵션을 클릭하십시오.
- 값 필드 설정 대화 상자에서 사용자 지정 이름을 누계로 변경합니다.
- "값 표시" 탭을 클릭합니다.
- 값 표시 형식 드롭다운 메뉴에서 "누적 합계" 옵션을 선택합니다.
- 기본 필드 옵션에서 날짜가 선택되어 있는지 확인합니다.
- 확인을 클릭하십시오
위의 단계는 두 번째 판매 열을 "누계" 열로 변경합니다.
따라서 다음은 Excel에서 누계를 계산하는 데 사용할 수 있는 몇 가지 방법입니다.표 형식의 데이터가 있는 경우 간단한 수식을 사용할 수 있고 Excel 표가 있는 경우 구조적 참조를 활용하는 수식을 사용할 수 있습니다.
또한 파워 쿼리와 피벗 테이블을 사용하여 누계를 계산하는 방법도 다루었습니다.
이 튜토리얼이 유용했기를 바랍니다.
