Grouping in GROUPBY & PIVOTBY Functions!?!? Yes!!!! Frequency Distribution with GROUPBY EMT 1858
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1857-...
Learn how to group in the GROUPBY and PIVOTBY functions to create Frequency Distributions.
Topics:
1. (00:00) Introduction to Frequency Distribution
2. (00:38) Start, Increment and Last Value for frequency distribution using MAX and CEILING.MATH functions
3. (01:12) Build Bins using SEQUENCE function
4. (01:42) Build GROUPBY Formula using LET and GROUPBY
5. (02:37) Look at PIVOTBY Formula
6. (02:43) Understanding Categories
7. (03:00) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #groupby #pivotby
This was great Mike, thanks. Hoping to get GROUPBY and PIVOTBY soon!
I hope very soon!!!!
Thank you amazing Mike for this EXCELlent video.
You are welcome Syed Muzammil Mahasan Shahi!!!!!!!
Thanks Mike!! That was Great. :) :)
You are welcome, Formula Guy John!!!
Thanks Mike, I see you having fun there.🎉
Keep playing soon we will play along.😂
: ) : ) : ) : )
Great video!! Brilliant trick !!✌
Out of everything that GROUPBY and FREQUENCY can do separately, there is only one functionality "intersection": count unique numeric values of a vertical vector.
TAKE(GROUPBY(x,x,COUNT,,0),,-1) DROP(FREQUENCY(x,SORT(UNIQUE(x))),-1)
- groupby can deal with any values, not only numeric (function COUNTA)
- groupby does not create any bins, by default lists the unq values
- frq filters only numeric values
- frq can deal with any shape data_array, not only vectors
- bins_array can be set independent of data_array
- frq count unique always has last value 0 that is irrelevant
- frq does not list its bins
- side note: since introduction of UNIQUE, FREQUENCY(x,x) became obsolete, more useful FREQUENCY(x,sort(unq(x)))
Conclusion:
When we need count unique of a clm vector GROUPBY should be considered: works with other data types, we can visualize the "bins" and it is as fast as FREQUENCY is. No performance issues.
For other scenarios FREQUENCY can be used (upper limit included ) or the custom designed one that does everything including upper limit not included, all data types, bins..etc. Will be published soon.
Now, the trick with CEILING to "simulate" "bins" frq behavior is super cool. The fact that unq ceiling values are the same with sequence bins ones is because of how the sales values are distributed. If we change the first sales value to 600, will lose the 500 bins. Also, to get uneven bins intervals distrib like 500, 1300,1700,2000....we cannot use ceiling trick.
My 2 cents on the matter. ✌😉
Nice set of thoughts, more like $12.50 worth, IMO. ;-)
I think FREQUENCY is more efficient because the bins range can be calculated efficiently, whereas GroupBy needs to transform the entire data vector -which can be big (data)- before the grouping (FREQ does not need that intermediate calculation).
Histograms like these can be most effective to reduce big datasets to the granularity of a required report without loss of information.
So calculations like these can be super useful.
@@GeertDelmulle Of course that freq is "lighter", numeric only. Groupby lot "havier" cannot work with few bins or choose the bins , its "bins" are predetermined by the data structure as its unique values . This makes the difference. For decent data tests was quite fast.
Wow!!!! The dissertation is in on the difference between GROUPBY and FREQUENCY : ) BAM!!!! You are right about ceiling only doing equal bins. I have a few DAX approximate match lookup videos that show both the ceiling lookup and the FILTER/CALCULATE/MAX method that deals with uneven categories. Thank you, Excel Lambda for the clear dissertation for the Team!!!!
I don't have it yet, but everything is getting more dynamic in Excel. Thanks Mike!
You are welcome, Teammate!!!!
Amazing video, as usual
Glad you like it : )
short comment: loving the 💥BAMs💥!! - Back to the channel RAD Mike!
Bam!!!!@!! You are RAD too : ) : ) : ) : )
GROUPBY good!!
Good : )
Perfectly!
in recent EMT1849 I proposed to group by "calculated column" instantly:
=GROUPBY(CEILING.MATH(S[Sales], G2), S[Sales], COUNT)
we can even try grouping "formatted" values, e.g.
=GROUPBY("
LOVE the FLOOR.MATH : ) Such a great idea, Victor!!!
Thank you Mr girvin but we are waiting group by function and pivot by function to be released in Excel 365 that all people have because until now it has not been released 🙁🙁
.... not even in preview channel 😄
Yes, MS is not being very nice.
Awesome, Mike!!! Are GROUPBY/PIVOTBY one of the 10 most important functions in Excel?
They are right up there, that is for sure!!!
Hello! Not sure where to go for advice. I wanted to know if it is possible to create a file in Excel from which changes can be made centrally in other files. For instance. I have a list of customers and a price page created for each one, and I need to change the price of some of the products. If there are many customers, then it is tedious to go through the list to replace e.g. one price.
On the current channel those are still not available like the Python features. Don't know why it is taking so long they have been presented in November 2023 it is over 5 months I wonder when the semiannual channel will get them...
Yes, MS is being bad... : (
And still I think that the FREQUENCY function will be more efficient...
Thoughts?
And more easier 👌👌
True for sure. But I guess having the word total. having a single cell formula, or perhaps add other criteria in row area are advantages that GROUPBY has over FREQUENCY.
@@excelisfun True, too.
And furthermore, GroupBy is how we do in in PQ. And “Excel Classic” (as I like to call it) has the added benefit of PivotBy where you can diversify the frequency calculation in another dimension, where in PQ that would be multiple steps (double GroupBy and then pivot).
@@GeertDelmulle True too too ; )
The real problem was the way I made the video. I did not show examples where GROUPBY could shine lol