K-means Cluster Analysis With Excel - A Tutorial

Подписаться 75 тыс.

Просмотров 52 тыс.

50% 1

In this video I will teach you how to perform a K-means cluster analysis with Excel. Cluster analysis is a wildly useful skill for ANY professional and K-means a great first clustering algorithm - no VBA code required!
Cluster analysis is used in all areas of business:
👉 In HR analytics for understanding similar employees
👉 In marketing analytics for customer segmentation
👉 In supply chain management to optimize logistics
The list goes on.
The great news is that you can perform K-means clustering in Excel using simple tools like VLOOKUPs and a tiny bit of Power Query.
This video is a fairly in-depth tutorial on K-means clustering, so feel free to jump around if a full tutorial experience is not what you need.
☕ If you found this content useful and would like to support the channel, you can buy me a coffee: www.buymeacoffee.com/DaveOnData
★★★ My online data analysis courses ▶ school.daveondata.com
-----------------------------------------------------------------------------------------------------------------------
Video Resources
-----------------------------------------------------------------------------------------------------------------------
💻 Get the Excel workbook & Notepad file for this video:
github.com/DaveOnData/DataAna...
💥 Learning R programming is easy! 💥
Conduct powerful cluster analyses with R:
📺 • Learning R Programming...
💻 Example of a scree plot for choosing the number of clusters:
rstudio-pubs-static.s3.amazon...
-----------------------------------------------------------------------------------------------------------------------
Excel Analytics Training
-----------------------------------------------------------------------------------------------------------------------
👩‍🔬👨‍🔬 Learn to use your basic Excel skills to analyze the business like a Facebook data scientist:
📺 • Introduction to Busine...
Stay healthy and happy data sleuthing!
#KMeansClusterAnalysisWithExcel #KMeansClusterAnalysis #ClusterAnalysis #KMeans

Наука

Опубликовано:

16 июн 2021

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 74

@DaveOnData 3 года назад

💥 Learning R programming is easy for Excel users! 💥 📺 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-MNpsyjSuR20.html 👩‍🔬👨‍🔬 Learn to use your basic Excel skills to analyze the business like a Facebook data scientist: 📺 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-xIXymabyFIM.html

@caitlinthompson458 2 года назад

This is such a great tutorial. I've been trying to do this all week (started in python but came back to Excel) and this is exactly what I needed. Really thorough, lots to think about and still easy to follow!

@Persianchannel1 2 года назад

dude you are a great teacher. i appreciate your passion of teaching. your explanation was great. thank you so much. your videos deserve more views 🌹🌹

@jissbela 2 года назад

this video is awesome, thank you David!

@PaulAcito-ce2ex Год назад

Just fantastic explanations and approach. Thanks for sharing this.

@sandaranote6979 2 года назад

Thank you Dave for this video! It was interesting and your explanation is really clear and useful =)

@ultimatepravint 2 года назад

Great video, plain and simple language explanation

@abhinav18rana 3 года назад

Dave, you are a master at simplistic explanation of complex topics. I would be coming to your channel more often now and will recommend to my team as well who are new to analytics.

@DaveOnData 3 года назад

Thank you so much for this feedback! As a content creator and teacher, such recommendations are strong motivation to continue my mission.

@strategy_gal 3 года назад

Great tutorial, David! This is very helpful!

@DaveOnData 3 года назад

Wow! Thank you so much for taking the time to offer feedback. I'm flattered.

@willykitheka7618 2 года назад

Great job Dave! I really enjoyed that entire tutorial

@DaveOnData 2 года назад

Thank you for the feedback and so glad you are enjoying my content!

@Dfcameron13 3 года назад

I love the detailed explanation. Dave Langer has a great way of explaining things conversationally. As mentioned, the first problem the algorithm needs to solve is the starting centroids for the proposed clusters. This is actually a critical problem that does not have a good general solution. I do this for a living, and use domain expertise vs. random. My only additional recommendation is to realize that you don't often know what cluster each point belongs to at the start. So, his setup of a "red cluster" and a "green cluster", where you can find the means, as Dave said, is overly simplistic. In real-world situations, it isn't that easy to find means across dimensions.

@DaveOnData 3 года назад

Thank you so much for taking the time to add such a valuable comment to the video - much appreciated! 🙏🙇‍♂️ Also, thank you for the kind words regarding my teaching style, this is also much appreciated. 😁

@swadminstudynwork1360 2 года назад

Thanks!!! I learned a lot with your video :)

@festusdavidoundo6227 Год назад

Thanks Dave, Got it.

@aaronporche6820 5 месяцев назад

DUDE i think you sir are maybe the best. most well spoken teacher i have ever had. THANK YOU FOREVER YOU ARE SPECIAL. I am a logistics coordinator and trying to schedule work in excel with at least 10 dimensions of variables, this is a miracle for me ill figure out how to use R for sure cause this is exactly what I need. My brain hurts so bad this sounds easy hahaha I think its mostly the teacher tho

@DaveOnData 5 месяцев назад

Wow! So glad you found my content useful. If you're interested in k-means clustering, you might want to check out some of my newer videos. K-means using Python in Excel: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-eg-13ffiggI.html Combining k-means and decision trees using Python in Excel: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-O479uWMe_F0.html

@kisholoymukherjee 2 месяца назад

As always, fantastic video Dave.

@DaveOnData 2 месяца назад

Thank you! I am so glad to hear you are finding my content useful.

@vacilando86 Год назад

You're my Hero. God bless you 🤝

@husseinadam4724 Год назад

You gained a new subscriber now thanks Dave

@btomblin386 2 года назад

This was an awesome video!

@ejpeninger 2 года назад

Excellent tutorial, thank you so much Sir!

@DaveOnData 2 года назад

You are most welcome, hope you find the content useful!

@andrew-jones-data-science 3 года назад

k-means is extremely useful - this is great!

@DaveOnData 3 года назад

Agreed! There's a reason why is the de facto standard clustering algorithm - easy to understand, easy to implement, and useful in so many business scenarios.

@pedroserpa2897 Год назад

Great Video. Suggestion1 If in the iteration 1 you calculate de sum of the minimum distances. After that, you use the "excel->Data->Solver" to find your minimum of that sum by changing your initial points. With that, excel will do all the work for you in a glance.

@AlexanderKonig2577 Год назад

Hi David, great video!!! today there are several new formulas, xlookup is an amazing and much easier way than vlookup. and for you huge and monster formula use the SUMXMY2 function. To find the average of each cluster (top rows) use the averageif formula... muuuuch easier and skip the power query step. Finnaly you shouldt run a min solver in order to find the min distance among variables and centroids.

@user-ck9nv5gz7w 10 месяцев назад

Can u tell me the formula how to do it

@topfundus1093 Год назад

Prima, vielen Dank! Konnte nun mein eigenes Programm mit VB zur flexiblen Clusterbildung beliebiger Wertepaare schreiben. Die Daten (x, y) übernehme ich zunächst aus der Tabelle in ein Array. Dieses lasse ich dann mit verschachtelten Schleifen n-mal (=Iterationen) durchlaufen, bis alle Wertepaare auf Basis der kürzesten Entfernungen einem Cluster(-Punkt) zugeordnet sind, ohne das weitere Schleifendurchläufe diese Zuordnungen verändern. Es erfolgt der Ausstieg aus den Schleifen. Die Clusternummern werden dann auf einen Schlag in die Quelltabelle, in eine neue Spalte eingefügt. Fertig! Das geht alles blitzschnell und ohne die vielen, doch ziemlich aufwendigen Tabellen und Formeln, die Du im zweiten Teil Deines Tutorials zeigst. Dazu kommt, dass ich mein k-Mean-Programm universell nutzen kann. Es ist gleich, welche (numerischen) Datenspalten einlesen und clustern kann. Great. Thanks! I was now able to write my own program with VB for flexible clustering of any pairs of values. First, I take the data (x, y) from the table into an array. I then run this through nested loops n times (=iterations) until all pairs of values are assigned to a cluster (point) based on the shortest distances, without further loop runs changing these assignments. There is an exit from the loops. The cluster numbers are then inserted in one fell swoop into a new column in the source table. Finished! It's all lightning fast and without the many, but rather complex tables and formulas that you show in the second part of your tutorial. In addition, I can use my k-mean program universally. It doesn't matter which (numeric) data columns can read and cluster.

@JoseOlivar-sk5rv 11 месяцев назад

Hello, great video! Do you have a video showing how you would do this using R?

@paddies64 2 года назад

A great thank you Dave, don't know where you're from but very clear accent and a great teacher cos the subject is not that easy to teach. Need te review some parts of the video especially the one with the 1km formula ;-) but it's very clear ! Deserve a good bottle of wine Dave!

@boknows8263 2 года назад

He is from US, obvious from how he speaks English.

@user-rc2vh6zu8d 4 месяца назад

If you would add a timeline (very easy in youtube) it would make navigation within this great video even easier.

@DaveOnData 4 месяца назад

My new videos have video chapters, but haven't had time to go back and update my older videos.

@williamstan1780 2 года назад

David Thanks for the clip, very useful and informative. May I suggest to have one for Mixed Data (Category and numeric data? I have been searching it for ages but in vain. Thanks for your help

@SMILE__6208 28 дней назад

Hi Dave! This video is very helpful, but i would like to know how you were able to add your existing tables into the power query editor. i am unable to select any other table from the worksheet, only the current one.

@ajaykulkarni576 3 года назад

Great Content David!

@DaveOnData 3 года назад

Thank you, Ajay, for the feedback, much appreciated! 🙏🙇‍♂️

@mikastamaria3062 9 месяцев назад

Hello! May I ask how the end results can be put into a graph for visualization to show the actual result of the clustering? Trying to search online but I can't seem to find one that aligns to this method. Very informative tutorial on the groundwork though to understand the foundations. :)

@dataanalyst1012 2 года назад

In k means clustering, is there an assumption in numbers of observations and variables? Would having variables greater than observation affect the results of clustering and make it less accurate?

@krispsimpson79 Год назад

You mentioned that for categorical data there are better alternatives for cluster analysis - what would you recommend please?

@rainashrivastava6182 Год назад

Hey, I have a media data set in which, Rows are episode names and Columns are the different slot timings, So Let's say episode A has data for only 3 slots, and Episode B has data for 4 slots and so on. How do I apply K means to this Data set?

@nagendravishwamitra3652 2 года назад

One of the best lecture on k-means ..if you were in front of me i would have kissed you ..Greetings from India

@mbaintheusa1165 Месяц назад

Thanks for the video! But what if the measures in different columns are way different? Lets say, we have spend in thousand dollars and some other parameter in Sq meters? Is it correct to say that we would need first to normalize date, to bring to some comparable values?

@DaveOnData 21 день назад

Great question! The results of k-means can be skewed when the data is not on equal footing. A standard solution (pun intended) is to apply the Z-score to each column before clustering to help with this issue.

@mamaraci 2 года назад

Hello! Thanks for the detailed explanation. But, do you have the same, but fully based on PowerQuery? Initially, I want to try the same, but with ~2 million (lines) of customers, and >50 columns (dimensions). I think VLOOKUping them is not an optimal way to do that ( Or, my best alternative is to switch to R / Python with these volumes?

@boknows8263 2 года назад

Yes

@sebastianbriceno5293 2 года назад

Great video! Thank you so much. Can we to access to the xls file to understand the formulas that you showed in the notepad? Regards

@willykitheka7618 2 года назад

at 39:27 David mentioned that you can find the notepad and the excel file in his Github repo.

@DaveOnData 2 года назад

That's correct. The GitHub repo link is in the video description.

@sebastianbriceno5293 2 года назад

@@willykitheka7618 Thanks!

@sebastianbriceno5293 2 года назад

@@DaveOnData Thanks!

@spilledgraphics 3 года назад

7 emojis: 🤯🤯🤯🤯🤯🤯🤯 ...... one more.... 🤯❗ amazing David !

@spilledgraphics 3 года назад

@David, have you thought combining LAMBDA function for the iterative calculations ?

@DaveOnData 3 года назад

Thank you for the feedback, very much appreciated! 🙏🙇‍♂️ I considered a number of options, but in the end I decided to go with the simplest (albeit most manual) solution.

@enanta7 18 дней назад

Can we cluster text with this method too?

@DaveOnData 15 дней назад

You can cluster text documents using k-means, but the data must be preprocessed first. Check out my Python in Excel videos to learn more.

@venkatesh0075 3 года назад

Thanks for sharing. Can you also show how this works in Python pls

@DaveOnData 3 года назад

My apologies, but I don't do Python. I'm a Excel, SQL, and R guy. 😁

@abhinav18rana 3 года назад

@@DaveOnData I usually do stuff in excel first and then transition the logic to a tool or language. Excel makes me grasp the data and logic much better which in turn makes my life easy when the time comes for the project to upscale on a tool/language for heavy processing.

@DaveOnData 3 года назад

@Abhinav Rana - I would suspect that the way you approach analytics is very common. Prototype in Excel and move to another technology if needed.

@user-ck9nv5gz7w 10 месяцев назад

@@DaveOnDataDavid is it easy for any non programmer and no computer science student to use R ?

@kevinwflanagan9156 2 года назад

Can you add the formula to download?

@DaveOnData 2 года назад

Apologies, I'm missing the intent of the question. The formula to download what?

@kevinwflanagan9156 2 года назад

@@DaveOnData The Notepad formulas you showed

@willykitheka7618 2 года назад

@@kevinwflanagan9156 you can find the notepad in David github repo. (watch at 39.27)

@DaveOnData 2 года назад

This is correct. The link to the GitHub repo is in the video description.

@RohbertWhite 12 дней назад

You clearly know your stuff, but the excel worksheet is blurry and often unreadable. So I'm having to make sense of much that you are talking about from your verbal description of what you are doing. It might as well be an audiobook instead of a video. Constructive criticism only.

@DaveOnData 12 дней назад

Interesting. I just watched the video, and it was crystal clear. The video was recorded in 1080P.

@RohbertWhite 12 дней назад

@@DaveOnData Thank you for your input. My computer did auto adjust to 480p for some ungodly reason. So I was wrong about that part, however, the writing is still very small and I have vision problem after starring a screen to long. It would have been helpful if he would zoom in on the particular chart that his is referring rather than me having to look at all three chart while he's talking about one. I may just be old, blind, cranky, and stupid. good possibility.