Situatie
Solutie
Microsoft Excel now offers GROUPBY and PIVOTBY aggregation functions, which make it easier to collect and organize data into compact summaries or tables. Plus, a new PERCENTOF function allows you to quickly return percentages from raw data. These functions are still in beta and are currently limited to Microsoft 365 Insider builds.
The GROUPBY function is extremely simple and requires just three arguments—what to group by, the values that you want to aggregate, and the function that you’d like to use for aggregation. In an example provided by Microsoft, the function =GROUPBY(tbl[Category],tbl[Sales],SUM) reduces some complicated sales information into a two-column table. The left side of the table lists product categories, while the right side contains sales numbers aggregated by the SUM function.
Microsoft’s demonstration of the GROUPBY function is shown below.
Excel’s new PIVOTBY function is extremely similar to GROUPBY, but it takes four arguments instead of three. Using the same dataset from the previous example, Microsoft uses the function =PIVOTBY(tbl[Category],tbl[Year],tbl[Sales,MAX) to show the largest sales by product category and year. Total sales for each product category are also included.
Notice the use of the MAX function in this example. GROUPBY and PIVOTBY allow you to select from a list of Excel’s lambda functions, which can remove some of the guesswork when aggregating data. Microsoft plans to expand etc lambda support to all functions that support lambda, meaning that some large functions will become more streamlined and legible.