Depivot
The Depivot() function turns a block of data with row and column headers into an array of three columns, where the row headers becomes the first column, the column headers become the second column, and the values where the row and column headers intersect become the third column. This is useful for performing calculations on data.
Related functions: Pivot
Syntax
Depivot (Range)
| Range | The range of Excel cells to manipulate |
Note:
Input parameters can consist of Quintessence functions. This includes functions that return ranges in cases where a parameter value can be a range.
Examples
Example 1. Depivoting a block of data with headers into three columns
Example 2. Using Depivot with Groupby and Sum
Example 3. Using Depivot with GroupBy and Average
Example 4. Using Depivot with multiple columns
Note:
How your system administrator configured Quintessence determines which function parameters are required. In most implementations, administrators configure functions so that certain parameters default to specific values when left blank.
Example 1. Depivoting a block of data with headers into three columns
The Depivot() function is used to transpose a range of rows into columns. The row headers becomes the first column. The column headers become the second column. The data at the intersection of the source rows and columns forms the third column.
| Range | Depivot | |||
| Factor 1 | Factor 2 | Factor 3 | =Depivot(A2:D6) | |
| QGW | 3.79 | 3.88 | 3.01 | |
| QJU | 6.98 | 6.98 | 6.98 | |
| XDU | 5.55 | 5.77 | 5.55 | |
| VRR | 6.17 | 0.95 | 9.73 | |
| Range | Output | |||||
| Factor 1 | Factor 2 | Factor 3 | QGW | Factor 1 | 3.79 | |
| QGW | 3.79 | 3.88 | 3.01 | QGW | Factor 2 | 3.88 |
| QJU | 6.98 | 6.98 | 6.98 | QGW | Factor 3 | 3.01 |
| XDU | 5.55 | 5.77 | 5.55 | QJU | Factor 1 | 6.98 |
| VRR | 6.17 | 0.95 | 9.73 | QJU | Factor 2 | 6.98 |
| QJU | Factor 3 | 6.98 | ||||
| XDU | Factor 1 | 5.55 | ||||
| XDU | Factor 2 | 5.77 | ||||
| XDU | Factor 3 | 5.55 | ||||
| VRR | Factor 1 | 6.17 | ||||
| VRR | Factor 2 | 0.95 | ||||
| VRR | Factor 3 | 9.73 | ||||
Example 2. Using Depivot with Groupby and Sum
Both the Groupby() and Depivot() functions are used to calculate the sum of the input columns. The second column of the output from the Depivot() function (see Example 1 above) is used to group the results, and the third column from the output from the Depivot() function (see Example 1 above) is used to sum the grouped results.
| Range | Depivot | |||
| Factor 1 | Factor 2 | Factor 3 | =GroupBy(Depivot(A2:D6),”C2″,”sum(C3)”) | |
| QGW | 3.79 | 3.88 | 3.01 | |
| QJU | 6.98 | 6.98 | 6.98 | |
| XDU | 5.55 | 5.77 | 5.55 | |
| VRR | 6.17 | 0.95 | 9.73 | |
| Range | Output | ||||
| Factor 1 | Factor 2 | Factor 3 | Factor 1 | 22.49 | |
| QGW | 3.79 | 3.88 | 3.01 | Factor 2 | 17.58 |
| QJU | 6.98 | 6.98 | 6.98 | Factor 3 | 25.27 |
| XDU | 5.55 | 5.77 | 5.55 | ||
| VRR | 6.17 | 0.95 | 9.73 | ||
Example 3. Using Depivot with GroupBy and Average
Both the Groupby() and Depivot() functions are used to calculate the average of the input columns. The second column of the output from the Depivot() function (see Example 1 above) is used to group the results, and the third column from the output from the Depivot() function (see Example 1 above) is used to determine the average of the grouped results.
| Range | Depivot | |||
| Factor 1 | Factor 2 | Factor 3 | =GroupBy(Depivot(A2:D6),”C2″,”AVERAGE(C3)”) | |
| QGW | 3.79 | 3.88 | 3.01 | |
| QJU | 6.98 | 6.98 | 6.98 | |
| XDU | 5.55 | 5.77 | 5.55 | |
| VRR | 6.17 | 0.95 | 9.73 | |
| Range | Output | ||||
| Factor 1 | Factor 2 | Factor 3 | Factor 1 | 5.62 | |
| QGW | 3.79 | 3.88 | 3.01 | Factor 2 | 4.40 |
| QJU | 6.98 | 6.98 | 6.98 | Factor 3 | 6.32 |
| XDU | 5.55 | 5.77 | 5.55 | ||
| VRR | 6.17 | 0.95 | |||
Example 4. Using Depivot with multiple columns
Depivot() can create multiple columns from multiple rows.
| Input range | Depivot | ||||
| Minerals | Minerals | Gas | Gas | =Depivot(A2:E6,”C1″,2) | |
| 2017 | 2018 | 2017 | 2018 | ||
| QGW | 40 | 45 | 200 | 190 | |
| QJU | 88 | 80 | 67 | 60 | |
| XDU | 55 | 50 | 70 | 75 | |
| Input range | Output | |||||||
| Minerals | Minerals | Gas | Gas | QGW | Minerals | 2017 | 40 | |
| 2017 | 2018 | 2017 | 2018 | QGW | Minerals | 2018 | 45 | |
| QGW | 40 | 45 | 200 | 190 | QGW | Gas | 2017 | 200 |
| QJU | 88 | 80 | 67 | 60 | QGW | Gas | 2018 | 190 |
| XDU | 55 | 50 | 70 | 75 | QJU | Minerals | 2017 | 88 |
| QJU | Minerals | 2018 | 80 | |||||
| QJU | Gas | 2017 | 67 | |||||
| QJU | Gas | 2018 | 60 | |||||
| XDU | Minerals | 2017 | 55 | |||||
| XDU | Minerals | 2018 | 50 | |||||
| XDU | Gas | 2017 | 70 | |||||
| XDU | Gas | 2018 | 75 | |||||
| QGW | Minerals | 2017 | 40 | |||||
Example 5. Using Depivot with multiple columns and GroupBy
Depivot() can create multiple columns from multiple rows.
| Input range | Depivot and GroupBy | ||||
| Minerals | Minerals | Gas | Gas | =GroupBy(Depivot(A2:E6,”C1″,2),”C2,C3″,”SUM(C4)”) | |
| 2017 | 2018 | 2017 | 2018 | ||
| QGW | 40 | 45 | 200 | 190 | |
| QJU | 88 | 80 | 67 | 60 | |
| XDU | 55 | 50 | 70 | 75 | |
| Input range | Output | ||||||
| Minerals | Minerals | Gas | Gas | Gas | 2017 | 337 | |
| 2017 | 2018 | 2017 | 2018 | Gas | 2018 | 325 | |
| QGW | 40 | 45 | 200 | 190 | Minerals | 2017 | 183 |
| QJU | 88 | 80 | 67 | 60 | Minerals | 2018 | 175 |
| XDU | 55 | 50 | 70 | 75 | |||