Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
Stefania23 pe Simpatie
 | Femeie
24 ani
Dolj
cauta Barbat
25 - 46 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 18 ani
Postari: 2308
|
|
Excel 2024 does not have the GROUPBY function as it only exists in Excel 365.
But it does have LAMBDA, and LAMBDA can be leveraged to create a GROUPBY similar function.
The following LAMBDA does just that. It only lacks a couple of optional arguments of Excel 365 GROUPBY, for now. Those omitted arguments are [total_depth] and [field_relationship].
Instead of [total_depth] it will automatically add the Grand Totals at the bottom.
=LAMBDA(row_fields;values;function;[field_headers];[sort_order];[filter_array];
LET(
filtered_rows; IF(ISOMITTED(filter_array); row_fields; FILTER(row_fields; filter_array));
filtered_vals; IF(ISOMITTED(filter_array); values; FILTER(values; filter_array));
groups; UNIQUE(filtered_rows);
aggFunc; LAMBDA(arr;
SWITCH(UPPER(function);
"SUM"; SUM(arr);
"AVERAGE"; AVERAGE(arr);
"COUNT"; COUNTA(arr);
"MAX"; MAX(arr);
"MIN"; MIN(arr);
"MEDIAN"; MEDIAN(arr);
NA()
)
);
result; MAP(groups; LAMBDA(g;
LET(
vals; FILTER(filtered_vals; filtered_rows=g);
aggFunc(vals)
)
));
grandTotal; HSTACK("Grand Total"; SUM(result));
sorted; IF(ISOMITTED(sort_order);
HSTACK(groups; result);
SORT(HSTACK(groups; result); 1; sort_order)
);
final; IF(ISOMITTED(field_headers);
VSTACK(sorted; grandTotal);
VSTACK(TOROW(field_headers); sorted; grandTotal)
);
final
)
)(Table1[Name];Table1[Value];"SUM";{"Product";"Value"};1) |
You can save the gold-colored part into the Name Manager (Formulas tab) and name it GROUPBY, then the usage is just like in the aqua-colored part above (which must not be copied in Name Manager) or in the original GROUPBY function.
Didn't test all arguments though.
Source: ChatGPT
Inspiration:
_______________________________________
|
|
| pus acum 3 saptamani |
|