Microsoft Excel에서 SUBTOTAL 함수를 사용하는 방법

Microsoft Excel에서는 관련 항목 그룹에 대한 소계를 쉽게 구할 수 있습니다. 사실, 이를 수행하는 두 가지 방법이 있습니다. SUBTOTAL 기능이나 Subtotal 기능 중 가장 잘 작동하는 기능을 사용할 수 있습니다. 방법을 알려드리겠습니다.

Excel에서 부분합이란 무엇입니까?

언급했듯이 Excel의 SUBTOTAL을 사용하면 목록에서 동일하거나 관련된 항목을 그룹화하고 함수를 사용하여 값을 계산할 수 있습니다. 예를 들어, 이를 사용하여 월별 매출 합계 또는 학생별 평균 성적을 합산할 수 있습니다. 제품별로 재고를 추가하거나 다음 주에 마감되는 청구서 수를 계산할 수도 있습니다.

관련된: Microsoft Excel에서 COUNT 함수를 사용하는 방법

수식을 생성하거나 부분합 기능을 사용하여 SUBTOTAL 기능을 사용하면 몇 단계 만에 필요한 계산을 얻을 수 있습니다.

Excel SUBTOTAL 함수 사용

숨긴 행을 포함하거나 제외할 수 있는 유연성이 있는 Excel SUBTOTAL 함수를 사용하여 수식을 만들 수 있습니다.

수식의 구문은 다음과 같습니다. SUBTOTAL(function_number, reference 1, reference 2,...) 여기서 처음 두 인수는 필수입니다. 필요에 따라 나머지 인수에 대해 추가 셀 참조 또는 명명된 범위를 사용할 수 있습니다.

그만큼 function_number 인수를 사용하면 해당 번호를 사용하여 11개 함수 중 하나를 삽입할 수 있습니다. 처음 11개에는 숨겨진 행이 포함되고 두 번째 11개에는 제외됩니다. 따라서 필요한 기능과 숨겨진 행을 처리하는 방법에 대한 번호를 삽입하기만 하면 됩니다.

SUBTOTAL 함수의 예로 다음 공식을 사용하여 셀 범위 B2에서 B4까지의 총 판매액을 합산합니다.

=SUBTOTAL(9,B2:B4)

이 다음 예에서는 행 4와 5를 숨겼습니다. 이 첫 번째 공식을 사용하여 숨겨진 데이터를 포함하는 첫 번째 인수에 숫자 9를 사용하여 합계를 얻을 수 있습니다.

=SUBTOTAL(9,B2:B6)

이제 첫 번째 인수로 숫자 109를 사용하여 숨겨진 행을 합계에서 제외합니다.

=SUBTOTAL(109,B2:B6)

소계가 있으면 SUBTOTAL 기능을 한 번 더 사용하여 하단의 총계를 확인할 수 있습니다. 또는 SUM 함수를 사용하여 부분합을 추가할 수 있습니다.

관련된: Microsoft Excel에서 열을 합산하는 방법

다른 예를 들어, 다음 공식으로 숨겨진 행 3과 4를 포함하기 위해 평균 함수를 사용할 것입니다.

=SUBTOTAL(1,C2:C6)

다음으로 다음 공식을 사용하여 숨겨진 행을 제외합니다.

=SUBTOTAL(101,C2:C6)

SUBTOTAL 기능에 대한 참고 사항

다음은 Excel에서 SUBTOTAL 함수를 사용할 때 염두에 두어야 할 몇 가지 사항입니다.

• 이 기능은 행이나 가로 범위가 아닌 열이나 세로 범위에 대해 작동합니다.
• 참조 인수 영역에 중첩된 부분합이 있는 경우 Excel은 부분합 결과에서 이를 무시합니다.
• 필터를 사용하면 필터링된 데이터는 소계 결과에서 제외됩니다.

소계 기능 사용

Excel에서 SUBTOTAL 함수를 사용하는 또 다른 방법은 부분합 기능을 사용하는 것입니다. 이렇게 하면 자동으로 계산을 추가할 수 있습니다. 그리고 항목을 동시에 그룹화합니다. Excel에서는 SUBTOTAL 함수를 사용하여 이 작업을 수행합니다. 몇 가지 예를 살펴보겠습니다.

관련된: Microsoft Excel에서 함수를 사용하여 근무일을 계산하는 방법

월별 매출에 대한 소계 기능을 사용하여 월별 데이터를 그룹화하고 맨 아래에 총계를 사용하여 각 그룹을 합산할 수 있습니다.

그룹화하고 소계할 모든 행을 선택합니다. 데이터 탭으로 이동하여 개요 드롭다운 메뉴에서 “소계”를 선택합니다.

부분합 상자가 열리면 부분합을 추가할 방법을 선택합니다.

• 변경할 때마다: 그룹화에 사용할 열을 선택합니다. 여기에서는 시트에서 월이 변경될 때 새 그룹이 생성되도록 월을 선택했습니다.
• 기능 사용: 합계, 평균, 최소값, 최대값 또는 기타 옵션과 같이 사용하려는 함수를 선택합니다. 이 예에서는 Sum을 선택했습니다.
• 소계 추가: 소계를 계산할 위치에 대한 확인란을 선택합니다. 이 예에서는 Sales를 선택했습니다.

원하는 경우 추가 항목에 대한 하단의 확인란을 선택합니다. “확인”을 클릭합니다.

그러면 그룹에 대한 데이터 업데이트가 표시됩니다. 그리고 행을 소계하고 맨 아래에 총계를 만듭니다. 더하기, 빼기 및 숫자 버튼을 사용하여 보기 쉽게 그룹을 축소하거나 확장합니다.

또 다른 예로 학생 성적을 사용하겠습니다. 소계 및 그룹 기능을 사용하여 각 학생의 평균 성적을 표시합니다. 다음은 프로세스입니다.

행을 선택하고 데이터로 이동한 다음 개요 드롭다운 메뉴에서 “소계”를 선택합니다.

소계 상자의 변경 드롭다운 목록에서 학생을 선택하고 함수 목록에서 평균을 선택합니다. 그런 다음 등급 확인란을 선택합니다. “확인”을 클릭합니다.

이제 행이 학생별로 그룹화되어 각 행의 평균 성적이 맨 아래에 있고 전체 평균이 맨 아래에 있습니다. 다시, 왼쪽에 있는 버튼을 사용하여 그룹을 축소 및 확장할 수 있습니다.

부분합 기능을 사용한 후 행 그룹을 해제하기로 결정하면 행이 정상으로 돌아갑니다. 그러나 Excel에서 사용하는 SUBTOTAL 함수가 포함된 항목은 계속 사용하거나 원하는 경우 삭제하면 됩니다.

많은 그룹을 사용하려는 경우 소계 기능이 복잡해질 수 있습니다. 그러나 이 기본 예제는 이 기능에 관심이 있는 경우 시작하는 데 도움이 될 것입니다.

한 가지 주의할 점은 이 기능을 사용하여 Excel 표 부분합을 추가할 수 없다는 것입니다. 테이블에 데이터가 있는 경우 앞에서 설명한 대로 SUBTOTAL에 대한 Excel 수식을 삽입하거나 테이블을 셀 범위로 변환하여 기능을 사용할 수 있습니다. 후자를 선택하면 테이블 기능을 잃게 됩니다.

관련된Microsoft Excel에서 테이블을 범위로 또는 그 반대로 변환하는 방법

이제 Excel에서 부분합을 삽입하는 방법을 알았으므로 중복 행을 제거하는 방법을 확인하십시오.