一、等级聚类法是什么

等级聚类法（Hierarchical Clustering），中文也叫系统聚类法、层次聚类法，是聚类分析里最经典的一类方法。它的思路和 K-Means 完全不同：

• K-Means：先定好要分几类，然后反复迭代让样本在类间移动。
• 等级聚类：不预设类别数，从"每个样本自成一类"开始，每步把最相近的两类合并，一路合并到"全部归为一类"，最后形成一棵树状结构（谱系图 / Dendrogram）。想分几类，在树的某个高度切一刀就行。

这个特性让等级聚类有两个独特的优势：

1. 不需要事先确定 K，切几类由分析者根据业务和树形结构决定
2. 过程可追溯，能看清哪些样本先聚在一起、哪些后合并

代价是计算量大：n 个样本要算 n(n-1)/2 个距离对，还要做 n-1 轮合并。所以等级聚类适合小样本（几十到几百个），大样本通常还是用 K-Means。

二、等级聚类的五种合并策略

合并"最相近的两类"时，"相近"怎么定义，决定了不同的方法：

Excel 里做等级聚类，绝大多数的实操教学用最短距离法或组平均法，因为公式最容易写。下面以最短距离法为主线，同时给出组平均法的改造方式。

三、Excel 实操：最短距离法完整步骤

3.1 第 1 步：整理原始数据

把样本按行排列，指标按列排列：

关键前提：必须标准化。 如果指标量纲不同（一个用元、一个用百分比），距离计算会被大数值指标完全主导。用 Z-score 处理：

在空白列写公式：

=(B2-AVERAGE($B$2:$B$6))/STDEV.S($B$2:$B$6)

每列都做一遍，得到标准化后的数据区域，后续所有距离都基于标准化的数据算。

3.2 第 2 步：计算距离矩阵

等级聚类最常用欧氏距离：

在 Excel 里用 SUMPRODUCT 一步算完，先生成"距离矩阵"（对称矩阵，n×n）。

假设标准化后的数据在 B2:D6：

=SQRT(SUMPRODUCT((B2:D2-B3:D3)^2))

更通用、可直接下拉填充的写法（用 $ 锁定引用）：

=SQRT(SUMPRODUCT((INDEX($B$2:$D$6,ROW()-1,0)-INDEX($B$2:$D$6,COLUMN()-1,0))^2))

说明：

• INDEX($B$2:$D$6, 行号, 0) 返回整行（一个样本的所有指标）
• ROW() / COLUMN() 让公式横向纵向拖动时自动指向对应的样本
• 对角线会得到 0（自己到自己），这是正常的

小数据量也可以手写简单公式。5 个样本的距离矩阵长这样：

矩阵是对称的，算一半就行（上三角）。

3.3 第 3 步：找最小距离，确定第一组合并

在距离矩阵上（排除对角线 0 值），找最小的非零值：

=MIN(IF(range>0, range))

这个公式是数组公式，输入后要按 Ctrl+Shift+Enter（Office 365 直接回车即可）。

或者更简单的做法：用 SMALL 函数取第二小的值（因为第一小是 0）：

=SMALL(区域, 2)

拿到最小值后，用 MATCH 定位它在哪一行哪一列：

=MATCH(最小值, 某行, 0)

上表里最小距离是 1.41（A 与 E），第一轮把 A、E 合并成新类 （A,E）。

3.4 第 4 步：更新距离矩阵（核心步骤）

合并后，样本数从 5 个变成 4 个"类"：（A,E）、B、C、D。需要重新计算新类到其他各类的距离。

最短距离法的定义是：新类到某类的距离 = 新类中所有样本到该类所有样本距离的最小值。

在 Excel 里就是：

=MIN(A到B的距离, E到B的距离)

具体到公式，假设 (A,E) 这一新类占据原来 A 的位置：

=SQRT(SUMPRODUCT((原A行-原B行)^2))  → 与 E 到 B 的距离取最小

实际填写时，把新类所在行/列填成：

=MIN(原距离矩阵中A-B, 原距离矩阵中E-B)

这样新矩阵变成 4×4，然后重复第 3 步：再找最小、再合并、再更新。

如果用组平均法，只需要把 MIN 换成 AVERAGE：

=AVERAGE(原A-B, 原E-B)

如果用最长距离法，换成 MAX。这是三种方法在 Excel 里唯一的公式差异，非常方便对比。

3.5 第 5 步：循环直到全部合并

重复"找最小 → 合并 → 更新矩阵"：

每轮都要记录合并时的距离值，这个值就是谱系图里的"合并高度"。

3.6 第 6 步：画谱系图（Dendrogram）

Excel 没有内置的树状图，但可以用堆积条形图手工构造，或者用散点图 + 误差线做"锤子图"（更规整）。

最直观的简化做法是用簇状条形图的变通：

1. 把每个合并步骤的高度值（距离）作为数据
2. 用"散点图 + 直线连接"画出类似树的横线
3. 每个样本在底部标上名字

实务上更推荐的做法：Excel 算完距离矩阵和合并顺序，把结果贴到 Python / R / 在线绘图工具里画谱系图。Excel 里画树状图的手工成本远高于把数据导出去画。

如果用 Python：

from scipy.cluster.hierarchy import dendrogram, linkage
import matplotlib.pyplot as plt

Z = linkage(scaled_data, method='single')
dendrogram(Z, labels=['A','B','C','D','E'])
plt.show()

method 参数可以填 single（最短距离）、complete（最长距离）、average（组平均）、ward（离差平方和），和 Excel 里换 MIN/MAX/AVERAGE 是一一对应的。

四、组平均法的 Excel 改造

如果你要用组平均法（更稳健、更常用），流程完全一样，只改第 4 步的更新公式：

单链接（最短距离）：

=MIN(距离1, 距离2)

完全链接（最长距离）：

=MAX(距离1, 距离2)

平均链接（组平均）：

=AVERAGE(距离1, 距离2)

当新类包含多个样本时（比如 (A,E,B)），组平均要按样本对数加权：

Excel 里就是把所有跨类样本对的距离取平均：

=AVERAGE(原矩阵中所有 (U中样本, V中样本) 的距离)

样本量不大时可以直接框选区域做 AVERAGE；样本多了这个公式会很笨重，这时建议转用 Python。

Ward 法在 Excel 里很难手写（需要算类内离差平方和），实操上要么放弃，要么借助数据分析插件。

五、切几类：谱系图的解读方法

聚类完成后，怎么决定分几类？三种方法：

5.1 看合并高度跳变

谱系图上，如果某一步的合并高度突然变大（比如前几步都是 1.7，突然跳到 5.9），说明这一步合并的两类差异已经很大，在这一步之前切开就是合理分类。

上例里：1.41 → 1.73 → 1.73 → 5.92，最后一步跳变明显，说明应该切成两类：{A, E, B} 和 {C, D}。

5.2 看业务可解释性

统计上的"最佳切点"未必业务上可用。分 2 类是图上合理，但业务上可能需要 3 类（比如分出高中低三档客户），那就往上切。

5.3 结合类别规模

分出来的类太不均衡（一类 1 个样本、另一类 99 个），说明切点不合适，要调整。

六、一个真实场景：客户分群

假设有 30 个客户，指标是：年消费额、购买频次、最近购买间隔、客单价。标准化后用最短距离法聚类，得到树形结构：

• 前三步合并的都是"高频高额"客户 → 形成一个核心 VIP 簇
• 中间几步合并"低频低额"客户
• 最后一步把这两大簇合并（高度跳变大）

切在倒数第二层，得到两类：高价值活客 vs 低价值客户。如果想更细，往上切一层，高价值里还能分出"高频低额"和"低频高额"两种不同策略人群。

这就是等级聚类在业务上的典型用法：先看结构，再按运营需要切分。

七、Excel 做等级聚类的局限

必须说清楚，Excel 不是做等级聚类的理想工具：

7.1 样本量上限很低

距离矩阵是 n×n，30 个样本就是 900 个格子，还要做 29 轮更新。50 个样本以上，公式维护成本就难以承受。

7.2 公式容易出错

每轮更新矩阵都要手工框选区域写 MIN/AVERAGE，一旦引用错行，结果全歪，而且很难发现。

7.3 无法自动画谱系图

Excel 没有原生树状图，只能手工拼或导出到其他工具。

7.4 大数据量下计算缓慢

SUMPRODUCT 在矩阵上反复重算，几百个格子就会卡。

结论：Excel 适合教学演示、小样本（<20）、理解算法原理；真实业务分析建议直接在 Python（scipy.cluster.hierarchy）或 SPSS（"分析 → 分类 → 系统聚类"）里做。

八、常见误区

8.1 不标准化就聚类

量纲不同时，距离完全被大数值指标主导，聚类结果毫无意义。这是最高频的错误。

8.2 指标选择不加思考

把所有能拿到的指标都放进去，会引入噪声和冗余（高度相关的指标等于重复计权）。应先做相关性检查，剔除冗余变量。

8.3 用欧氏距离处理分类变量

等级聚类的距离计算默认针对连续变量。如果有性别、地区这类分类变量，要么转成虚拟变量并慎用欧氏距离，要么改用专门的距离度量（如 Gower 距离）。

8.4 认为聚类结果唯一

换一种链接方法（单链接 → 组平均），结果可能完全不同。聚类结果不是"客观真理"，是方法 + 数据共同作用的产物，报告里要写明用了哪种方法。

8.5 把聚类当成分类

聚类是无监督的探索工具，产出的是"数据里存在这样的分组"，不是"这些样本确定属于这一类"。真要用于业务决策，还需要后续验证。

九、总结

Excel 做等级聚类，流程可以浓缩成五步：

1. 标准化数据（Z-score，消除量纲影响）
2. 算距离矩阵（欧氏距离，SUMPRODUCT 一步算出）
3. 找最小距离并合并（MIN / SMALL + MATCH）
4. 更新矩阵（单链接 MIN、完全链接 MAX、组平均 AVERAGE）
5. 循环到全部合并，记录合并高度，画谱系图

这套流程的价值不在"用 Excel 算出结果"，而在于亲手走一遍距离矩阵的每轮更新，能真正理解"类间距离"是怎么定义、怎么变的。理解了这一点，再看 Python 里一行 linkage(data, method='average') 就不会是黑箱。

真要用于业务分析，把 Excel 当"理解工具"，把 Python / SPSS 当"生产工具"，是最合理的分工。

推荐学习书籍 《CDA一级教材》适合CDA一级考生备考，也适合业务及数据分析岗位的从业者提升自我。完整电子版已上线CDA网校，累计已有10万+在读~ !

免费加入阅读：https://edu.cda.cn/goods/show/3151?targetId=5147&preview=0