This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Problem description & analysis:
There's a tree structure Excel table, where the Epic column is the highest data layer.
Task: Group rows by Epic, the highest data layer, and summarize the Hours column while keeping the Code column. Below is the expected result:
Solution:
Use SPL XLL to perform the computation:
=spl("=E(?1).group@i(Epic!=null).new(Code,Epic,ifn(~.sum(Hour),0):Hours)",A1:E10)
As shown in the picture below:
Explanation:
group@i function performs the conditional grouping. Symbol ~ represents the current group; new()function creates a new table; ifn() function returns the first non-null member (return 0 when the aggregation result on the current group is null).
This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Judith-Excel-Sharing | Sciencx (2024-08-07T02:26:41+00:00) #40 — Group And Summarize A Tree Structure Table. Retrieved from https://www.scien.cx/2024/08/07/40-group-and-summarize-a-tree-structure-table/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.