GROUPBY function. Summary table by grouping by one axis.(Microsoft Excel)
The GROUPBY function is a function that was newly added in the September 2024 update. This function is similar to a pivot table and is useful for aggregating data.
As it requires no data updates and is more lightweight than pivot table, the GROUPBY function is more suitable for tasks that involve regularly updating tables and aggregating data using the same rules.
On the other hand, pivot table are more suitable for ad hoc aggregation tasks and for aggregating data using multiple different axes and checking the results.
If you have two aggregation axes, use the PIVOTBY function.
How it works
GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array],[field_relationship])
Name | Omission | Description |
---|---|---|
row_fields | Required argument. | Specify the axis for grouping. Whether or not to include the header row is optional, but it must match the values. |
values | Required argument. | Specify the values to be totaled. Whether or not to include the header row is optional, but it must match the row_fields. |
function | Required argument. | Specify the aggregation method. |
field_headers | Specifies the header line handling. | |
total_depth | Specify whether to display subtotals and totals. | |
sort_order | Specify the order of the items. | |
filter_array | Specify the filter conditions. | |
field_relationship | Controls the results of sorting when there are multiple axes. |
Basic Usage Examples
The simplest way to use it is to specify three arguments.
- First argument: The range that will be used as the grouping axis
- Second argument: The range of values to be aggregated
- Third argument: The aggregation method (e.g. SUM; see below)
The result of the function is automatically expanded as an Spill.
Arguments
3rd argument: function
Specifies the aggregation method.
- SUM: Total value
- PERCENTOF: Ratio from total value
- AVERAGE: Average value
- MEDIAN: Median value
- COUNT: Number of non-blank values
- COUNTA: Number of values including blanks
- MAX: Maximum value
- MIN: Minimum value
- PRODUCT: Multiplication
- ARRAYTOTEXT: Character concatenation. Comma-separated text
- CONCAT: Character concatenation
- STDEV.S: Standard deviation (sample)
- STDEV.P: Standard deviation (entire population)
- VAR.S: Variance (sample)
- VAR.P: Variance (entire population)
- MODE.SNGL: Most frequent value
- LAMBDA: Arbitrary aggregation method
PERCENTOF: Ratio from total value
It is possible to easily calculate the percentage composition using PERCENTOF.
The result will be as follows.
ARRAYTOTEXT and CONCAT: String concatenation
String concatenation will result in the following.
This is useful for debugging formulas.
LAMBDA: Arbitrary aggregation method
Use the LAMBDA function to perform your own calculations. It is difficult to use and not often used, but it is possible to perform advanced calculations.
For example, the following formula will only sum up the numbers that are multiples of 3.
=GROUPBY(A3:A20,D3:D20,LAMBDA(a,SUM(IF(MOD(a,3)=0,a,0))))
4th argument: field_headers
This is an argument that specifies how to handle the header.
The most frequently used values are probably either omit or 3.
Selected | Handling of the first line of the specified range. | The header row of the results. |
---|---|---|
Omitted | Automatic identification. | Invisible. |
0 | Data row. | Invisible. |
1 | Header row. | Invisible. |
2 | Data row. | Visible. |
3 | Header row. | Visible. |
If the first row of row_fields and values is a header row, specify either omission, 1, or 3.
If the first row is a data row, specify either omission, 0, or 2.
If it is 2, the “Row field” and “value” become the header.
5th argument: total_depth
Specify whether to display subtotals and totals.
To display subtotals, the range of row_fields must be two or more columns.
If total_depth is omitted, subtotals appear to be automatically determined when displaying totals, but we were unable to confirm that subtotals are displayed.
In addition, if the 8th argument: field_relationship is 1, subtotals will not be displayed.
Selected | Display position | Subtotal |
---|---|---|
Omitted | Bottom. | Automatic identification? |
0 | Invisible. | Invisible. |
1 | Bottom. | Invisible. |
2 | Bottom. | Visible. |
-1 | Top. | Invisible. |
-2 | Top. | Visible. |
This is an example of subtotals for 2 and -2. The subtotals are the parts where “Month” and “Category” are left blank.
6th argument: sort_order and 8th argument: field_relationship
The sort order is specified using the 6th argument, sort_order.
The number of columns to be sorted is specified. If it is a positive number, the sort order is ascending, and if it is a negative number, the sort order is descending.
For example, if -2 is specified, the second column of the result will be in descending order.
=GROUPBY(A1:C19,D1:D19,SUM,3,0,-2,,)
However, because the hierarchy is maintained, the first column is sorted.
You can specify sorting for multiple columns by specifying an array with {}.
If you specify 1 for the 8th argument field_relationship, the hierarchy will be removed.
The subtotal will no longer be displayed, but you will be able to sort the data without the hierarchy.
=GROUPBY(A1:C19,D1:D19,SUM,3,0,-2,,1)
7th argument: filter_array
Specifies the filter conditions.
A simple way to use this is to prepare a column for the filter and set it to TRUE or FALSE.
Only TRUE is included in the calculation.
=GROUPBY(A1:C19,D1:D19,SUM,3,1,,E1:E19)
You can set this using the same rules as the FILTER function.
The formula will become more complex, but it will reduce the amount of work required for regular cell operations.
For example, if you set it as follows, only the cells in category B with a year of 2021 or later will be included in the calculation.
=GROUPBY(A1:C19,D1:D19,SUM,3,,,(A1:A19>=2021)*(C1:C19="B"))
---
Discussion
New Comments
No comments yet. Be the first one!