Скачать с ютуб PivotTables but better, using new Excel function: GROUPBY PIVOTBY в хорошем качестве

PivotTables but better, using new Excel function: GROUPBY PIVOTBY

Excel launched two functions that emulate what PivotTables can do that are easier to write than SUMIFS. GROUPBY aggregates a value in rows and PIVOTBY aggregates values by rows and columns. Superior to PivotTables, they refresh automatically and can do more functions such as MEDIAN and my favourite ARRAYTOTEXT to create a comma-separated list of a text field: e.g. France: Lyon, Marseille, Paris UK: London, Bristol. There are optional fields like headers, totals, sort, and filter plus I also walk through how to only aggregate unique values like DISTINCTCOUNT or distinct comma-separated lists, here is the formula for the latter: =GROUPBY({Categorical column},{Text_value column},LAMBDA(x,ARRAYTOTEXT(SORT(UNIQUE(x))))) I reference my other videos in this one: LAMBDA:    • Excel's custom function editor: Creat...   Dynamic array functions:    • 24 Dynamic array functions in Excel i...   You can download the example files here: https://www.xlconsulting-asia.com/you... Follow Solve & Excel's monthly newsletter for monthly Excel updates: https://solveandexcel.ca/excel_news/ 00:00 Introduction 00:55 Group by 02:26 Group by non-adjacent columns 03:46 Groupby to make comma-separated list 04:53 Row and column pivot by with PIVOTBY 05:59 Filter 06:45 Count unique and text unique