엑셀 여러열 그룹화하여 합산하는 방법 (GROUPBY함수)

Excel GROUPBY함수를 사용하여 여러열을 그룹화하고 합산하는 방법에 대해 알아보겠습니다. 

 

Excel GROUPBY 함수는 행을 그룹화하고 값을 집계하여 데이터를 요약하도록 설계되었습니다. 결과는 단일 수식으로 생성된 요약 테이블입니다.

 

 

반응형

 

1. GROUPBY 함수 

=GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array],[field_relationship])

• row_fields - 그룹화를 위한 값입니다.
• values - 집계할 값입니다.
• function 함수 - 집계할 때 실행할 계산입니다.
• field_headers - [선택] 0 = 아니오, 1 = 예, 보여주지 않음, 2 = 아니오, 생성, 3 = 예, 보여주세요.
• total_depth - [선택 사항] 합계 및 소계. 0 = 아니오, 1 = 총합계, 2 = 모두, -1 = 위쪽 총합계, -2 = 둘 다 위쪽.
• sort_order - [선택 사항] 인덱스 번호순으로 정렬합니다. 음수 = 내림차순.
• filter_array - [선택 사항] 특정 행을 제외하는 논리입니다.
• field_relationship - [선택 사항] 여러 열이 행 필드로 제공되는 경우의 필드 관계입니다. 0 = 계층(기본값), 1 = 테이블.

 

GROUPBY 함수는 행을 그룹화하고 값을 집계하여 데이터를 요약하도록 설계되었습니다. 그 결과 단일 수식으로 생성된 동적 요약 테이블이 생성됩니다. GROUPBY 함수의 출력은 피벗 테이블의 출력과 유사하지만 서식이 없습니다. GROUPBY 함수에서 반환된 요약은 완전히 동적이며 원본 데이터가 변경되면 즉시 다시 계산됩니다.

 

 

다음은 GROUPBY 기능 및 제한 사항에 대한 간략한 목록입니다.

• 수식으로 요약 테이블을 만드는 간단하고 유연한 방법입니다.
• SUM, AVERAGE, COUNT, COUNTA 등과 같은 집계 함수를 적용할 수 있습니다.
• 피벗 테이블 또는 도우미 열이 필요하지 않습니다.
• 둘 이상의 수준(예: Region 및 Color 등)으로 그룹화할 수 있습니다.
• 데이터가 변경될 때 자동으로 업데이트되는 동적 배열을 반환합니다.
• 논리 표현식을 사용하여 소스 데이터의 특정 행을 제외할 수 있습니다.
• 서식을 적용하지 않습니다. 서식을 수동으로 적용해야 합니다.
• Excel 365에서만 사용할 수 있습니다.

 

 

반응형

 

2. GROUPBY 기본 예제

GROUPBY 함수는 8개의 인수를 사용하지만 처음 3개만 필요합니다. 아래 워크시트에서는 GROUPBY 함수를 사용하여 도시별 판매량을 요약합니다. F5 셀의 수식은 다음과 같습니다.

 

=GROUPBY(B5:B16,D5:D16,SUM)

 

 

 

 

이 예에서 GROUPBY는 다음과 같이 세 개의 인수로 구성됩니다.

• row_fields - 도시 이름을 포함하는 B5:B16 범위로 제공됩니다.
• values - 판매액을 포함하는 D5:D16 범위로 제공됩니다.
• function - 집계 중에 수행할 계산인 SUM으로 제공됩니다.

 

위의 입력을 사용하여 GROUPBY 함수는 도시별 Sales의 합계를 구하고 한 단계로 F4:G8의 테이블을 출력합니다. row_fields 또는 values에 헤더 행을 포함하지 않았는데, 이는 GROUPBY에 헤더를 자동으로 생성하도록 요청하지 않기 때문입니다. 대신 F4:G4에 머리글 행을 수동으로 입력했습니다. 또한 GROUPBY에는 기본적으로 Total 행이 포함됩니다. 아래 예에서는 소스 데이터의 필드 헤더를 출력하도록 GROUPBY를 구성하는 방법을 살펴보겠습니다.

 

 

반응형

 

3. 필드 헤더가 있는 GROUPBY

GROUPBY의 네 번째 인수인 field_headers는 헤더 행이 GROUPBY 함수에 제공된 데이터의 일부일 때 헤더 행이 처리되는 방식을 제어합니다. field_headers에 사용할 수 있는 값은 다음과 같습니다.

 

값	설명
<없음>	자동 헤더 감지(기본값).
0	헤더는 데이터에 제공되지 않습니다.
1	헤더가 포함되지만 표시되어서는 안 됩니다.
2	헤더는 포함되지 않지만 생성되어야 합니다.
3	헤더가 포함되어 표시되어야 합니다.

 

 

field_header 인수는 선택 사항입니다. field_header 생략하면 GROUPBY는 값을 테스트하여 소스 데이터의 헤더를 자동으로 감지합니다. 첫 번째 값이 텍스트이고 두 번째 값이 숫자인 경우 GROUPBY는 첫 번째 행에 헤더가 포함되어 있다고 가정합니다. 위의 예에서 자동 감지 동작을 볼 수 있으며, 여기서 row_fields 및 값에 사용되는 범위에는 헤더 행이 포함되지 않습니다.

 

첫 번째 값은 텍스트가 아닌 숫자이므로 GROUPBY는 헤더가 원본 데이터의 일부가 아니라고 가정합니다(올바름). 그러나 자동 감지는 헤더가 값으로 처리되는 것만 방지합니다. 헤더를 표시하지 않습니다. 원본 데이터에 필드 헤더를 표시하려면 field_header 3으로 설정하여 필드 헤더를 구체적으로 활성화해야 합니다. 아래 예에서 이것이 어떻게 작동하는지 확인할 수 있으, row_fields 및 값에 제공된 범위에는 헤더 행이 포함되고 field_header는 3으로 제공됩니다.

 

=GROUPBY(B4:B16,D4:D16,SUM,3) // field headers enabled

 

 

 

 

GROUPBY에 대한 입력은 다음과 같습니다.

• row_fields - 도시 이름과 헤더 행을 포함하는 B4:B16 범위로 제공됩니다.
• values - 판매액과 헤더 행을 포함하는 D4:D16 범위로 제공됩니다.
• function - 집계 중에 수행할 계산인 SUM으로 제공됩니다.
• field_headers - 이제 데이터에 표시되어야 하는 헤더 행이 포함되므로 3으로 제공됩니다.

이 예제의 데이터는 이전 예제와 동일합니다. 그러나 행 필드 및 값에 사용되는 범위에는 이제 행 4의 헤더가 포함됩니다. 결과적으로 출력 테이블을 동일한 위치에 유지하기 위해 F5 대신 F4 셀에 수식이 입력됩니다.

 

 

반응형

 

4. GROUPBY 계산 옵션

GROUPBY의 세 번째 인수는 값이 그룹화될 때 수행할 계산을 지정하는 함수입니다. 사용 가능한 계산에는 SUM, COUNT, COUNTA, MAX, MIN 등과 같은 Excel 함수가 포함됩니다. 이 함수는 eta lambda 구문으로 호출되며, 이는 괄호와 인수가 없는 함수 이름일 뿐입니다. 아래 워크시트에는 서로 다른 부서에 있는 100명의 직원에 대한 식사 선호도 목록과 식사당 비용이 있습니다. GROUPBY 함수를 사용하여 다양한 방법으로 이 데이터를 분석할 수 있습니다.

 

 

아래의 첫 번째 예에서는 GROUPBY를 사용하여 각 식사(쇠고기, 닭고기 및 채소)에 대한 개수를 생성합니다. 이 경우 텍스트 값인 식사를 계산하려고 하므로 함수에 COUNTA를 제공합니다. G5의 수식은 다음과 같습니다.

 

 

=GROUPBY(D4:D104,D4:D104,COUNTA,1)

 

 

 

• row_fields - D4:D104 (식사), 식사별로 그룹화.
• values - D4:D104 (식사) 식사 카운팅
• function - COUNTA, 텍스트 값을 계산
• field_headers - 1, 데이터에 표시하고 싶지 않은 헤더 행이 포함

필드 머리글은 원본 데이터 범위에 포함되지만 H4 셀에서 계산된 식사에 대해 "Count"라는 단어를 사용하려고 하기 때문에 필드 머리글을 표시하지 않으려고 합니다. 결과적으로 field_headers에 대해 1을 제공합니다.

 

다음 예제에서는 아래와 같이 식사별 총 비용을 생성하도록 계산을 변경했습니다. 이제 G5의 수식은 다음과 같습니다.

 

=GROUPBY(D4:D104,E4:E104,SUM,1)

 

 

 

 

• row_fields - D4:D104 (식사), 식사별로 그룹화
• 값 - E4:E104 (비용) 비용을 합산
• function - SUM, 합계
• field_headers - 1, 데이터에 표시하고 싶지 않은 헤더 행이 포함

여기서도 H4에서 "Total"이라는 단어를 사용하기 때문에 필드 헤더를 표시하고 싶지 않으므로 field_headers에 대해 1을 제공합니다.

 

 

반응형

 

5. 여러 열이 있는 GROUPBY

GROUPBY 함수는 row_fields 및 값 인수에 대한 여러 열을 처리할 수 있습니다. 여러 열을 포함하면 출력에 여러 행 그룹 수준이 있습니다. 작동 방식을 설명하기 위해 아래 예를 고려하십시오. 첫 번째 예제에서는 row_fields에 대해 하나의 열을 제공하고 값에 대해 1개의 열을 제공합니다. G4의 수식은 다음과 같습니다.

 

=GROUPBY(C4:C226,E4:E226,SUM,3)

 

 

 

 

GROUPBY에 대한 입력은 다음과 같습니다.

• row_fields - 머리글 행을 포함한 색상(C4:C226)입니다.
• values - 머리글 행을 포함한 Sales(E4:E226)입니다.
• function - 각 색상에 대한 합계를 생성하는 SUM.
• field_headers - 3, 원본 데이터에는 표시되어야 하는 헤더 행이 포함되어 있기 때문입니다.

 

이 구성을 사용하면 GROUPBY는 위와 같이 색상별로 Sales를 그룹화하고 Red, Green, Blue 및 Silver에 대한 합계를 생성합니다. 아래 예에서는 row_fields에 대해 Region(지역) 및 Color(색상)를 포함하도록 수식을 수정했습니다.

 

=GROUPBY(B4:C226,E4:E226,SUM,3)

 

 

• row_fields - 헤더 행을 포함한 지역 및 색상(B4:C226).
• values - 머리글 행을 포함한 Sales(E4:E226)입니다.
• function - 각 지역/색상에 대한 합계를 생성하는 SUM.
• field_headers - 3, 원본 데이터에는 표시되어야 하는 헤더 행이 포함되어 있기 때문입니다.

 

반응형

 

그 결과 GROUPBY는 지역 및 색상별로 Sales를 그룹화하고 각 조합에 대한 총 Sales를 반환합니다. 값은 둘 이상의 열이 있는 범위로 제공될 수도 있습니다. 아래 예에서 행 필드와 값은 두 개의 열을 포함하는 범위로 제공됩니다. G4의 공식은 다음과 같습니다.

 

=GROUPBY(B4:C226,D4:E226,SUM,3)

 

• row_fields - 헤더 행을 포함한 지역 및 색상(B4:C226).
• values - 머리글 행을 포함한 단위 및 판매량(D4:E226)입니다.
• function - 각 지역/색상에 대한 합계를 생성하는 SUM.
• field_headers - 3, 원본 데이터에는 표시되어야 하는 헤더 행이 포함되어 있기 때문입니다.

여기서는 Units(단위)와 Sales(판매)를 모두 포함하도록 Values 범위를 수정했습니다. 이 구성에서 GROUPBY 함수는 각 지역/색상 조합에 대해 두 개의 합계, 즉 단위에 대한 합계와 판매에 대한 합계를 생성합니다.