I propose one tool named Funfun which supports cluster analysis in Excel.
It allows people to code JavaScript and Python (rather than classic VBA macros) in Excel. It also supports external libraries. With this tool, you can use any JavaScript library you like to do data analysis (e.g. cluster analysis) or to plot graphs.
To answer the question, the function takes input in the format of JSON array (defined in the settings) and save them to a JavaScript variable. And then creates a link between them and spreadsheets.
Here I uses CanvasJS and jQuery to plot this chart. Clusterfck.js is used for the cluster process.
The core code:
var nb_clusters = 4;
// Calculate clusters.
var clusters = clusterfck.kmeans(data, nb_clusters); // data, nb_clusters
var chart = new CanvasJS.Chart("chartContainer", {
title: {
text: "Birth and death rate"
},
width:500,
axisX: {
title: "Birth rate",
minimum: 0,
maximum: 60
},
axisY: {
title: "Death rate",
titleFontSize: 16
},
data: [
{
type: "scatter",
markerType: "circle",
dataPoints: process(clusters[0])
},
{
type: "scatter",
markerType: "square",
dataPoints: process(clusters[1])
},
{
type: "scatter",
markerType: "square",
dataPoints: process(clusters[2])
},
{
type: "scatter",
markerType: "cross",
dataPoints: process(clusters[3])
}
]
});
chart.render();
Here’s the screenshot:
As you can see, it uses k-means cluster with k=4. You can change the hyper parameters in the code.
The link of this function of online playground. The screenshot is as follows:
The second pane is a preview of Excel. The third pane is the coding area. The last pane is the output of the code.
Hope it helps and have fun :)
P.S. I'm a developper of Funfun