PIVOTBY function. Summary table by grouping by two axes.(Microsoft Excel)
The PIVOTBY 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 one aggregation axis, use the GROUPBY function.
- 1. How it works
- 2. Basic Usage Examples
- 3. Arguments
- 4. Links
How it works
PIVOTBY(row_fields,col_fields,values,function,[field_headers],[row_total_depth],[row_sort_order],[col_total_depth],[col_sort_order],[filter_array],[relative_to])| Name | Omission | Description |
|---|---|---|
| row_fields | Required argument. | Specify the axis(row) for grouping. Whether or not to include the header row is optional, but it must match the values. |
| col_fields | Required argument. | Specify the axis(column) 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. | |
| row_total_depth | Specify whether to display the grand total and the subtotal for rows. | |
| row_sort_order | 行Specifies the order of the rows. | |
| col_total_depth | Specify whether to display the grand total and the subtotal for columns. | |
| col_sort_order | Specifies the order of the colmuns. | |
| filter_array | Specify the filter conditions. | |
| relative_to | 0 | If the function requires two numbers, select the second number. |
Basic Usage Examples
The simplest way to use it is to specify four arguments.
- First argument: The range that will be used as the grouping axis (row)
- Second argument: The range that will be used as the grouping axis (column)
- Third argument: The range of values to be aggregated
- Fourth argument: The aggregation method (e.g. SUM; see below)
The result of the function is automatically expanded as an Spill.
Arguments
4th argument: function, 11th argument: relative_to
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.
In the initial state, the denominator is the sum of the columns.
The 11th argument: relative_to specifies which sum to use as the denominator.
The result will be as follows.
- 0: Column total (default)
- 1: Row total
- 2: Grand total
- 3: Parent column total
- 4: Parent row total
If it is 3 or 4, the subtotal becomes the denominator.
relative_to=3. The subtotal of the row is the denominator.
relative_to=4. The subtotal of the column is the denominator.
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.
=PIVOTBY(A1:A19,B1:B19,D1:D19,LAMBDA(a,SUM(IF(MOD(value,3)=0,value,0))) )
The LAMBDA function can take a second argument.
In this case, the second argument is the total value.
11th argument: relative_to specifies which total to use (same as PERCENTOF).
- 0: Column total (default)
- 1: Row total
- 2: Grand total
- 3: Parent column total
- 4: Parent row total
=PIVOTBY(A1:A19,B1:B19,D1:D19,LAMBDA(value,total,SUM(total)),,0,,0)
5th 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,col_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”, “Column field” and “value” become the header.
6th argument: row_total_depth and 7th argument: col_total_depth
Specify whether to display subtotals and grand totals.
To display subtotals, the axis range must be two or more columns.
If omitted, it seems that subtotals are automatically determined by displaying grand totals, but we were unable to confirm that subtotals are displayed.
Also, if the 11th argument: field_relationship is 1, subtotals will not be displayed.
| 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. |
This is an example of subtotals for 2 and -2. The subtotals are the parts where “Month” and “Category” are left blank.
7th argument: row_sort_order and 9th argument: col_sort_order
The order is specified by the 7th and 9th arguments.
The number of columns is specified.
If it is a positive number, it is in ascending order, and if it is a negative number, it is in descending order.
For example, if -2 is specified, the second column of the result will be in descending order.
=PIVOTBY(A2:A19,B2:C19,D2:D19,SUM,,,,,-2)However, because the hierarchy is maintained, the first column is sorted first.
You can specify sorting for multiple columns by specifying an array with {}.
=PIVOTBY(A2:A19,B2:C19,D2:D19,SUM,,,,,{-1,-2})
10th 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.
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.
=PIVOTBY(A2:A19,B2:C19,D2:D19,SUM,,,,,,(A2:A19>=2021)*(C2:C19="B"))
---