用Excel实现层次聚类法进行聚类分析
用Excel实现层次聚类法进行聚类分析
在数据分析的学习旅程中，很多人第一次接触“无监督学习”时都会被一个简单却深刻的问题吸引：如果没有标签，我们还能发现数据中的规律吗？
答案是肯定的——这正是聚类分析的魅力所在。它不依赖先验知识，而是通过挖掘数据内部的结构和相似性，自动识别出潜在的分组模式。比如，在商场运营中，如何根据顾客评分将门店划分为不同档次？在用户画像构建中，怎样从行为数据中提炼出自然形成的群体？这些都可以借助聚类来完成。
而今天我们要聊的，是一种特别适合初学者理解的经典方法：层次聚类法（Hierarchical Clustering）。更特别的是——整个过程你不需要写一行代码，只需要打开 Excel，就能一步步看到“分类是如何自己长出来的”。
聚类 vs 分类：别再傻傻分不清
很多人刚接触聚类时，总会把它和“分类”搞混。其实两者的逻辑完全不同：
- 分类像是考试判卷：题目有标准答案，你要做的只是把新题归到已知类别里；
- 聚类更像是考古发掘：你面对一堆未知文物，得靠它们的材质、形状、年代等特征，推测哪些可能属于同一个文明。
换句话说，聚类的核心在于两个关键词：
1.没有先验知识
2.基于亲疏程度
那么，“亲疏”怎么量化？最直接的方式就是计算距离。
如何衡量“谁跟谁近”？
在多维空间中，两个样本之间的“远近”，可以用多种方式定义。以下是几种常见距离度量：
欧式距离（Euclidean Distance）
两点之间的直线距离，也是最直观的一种。
$$
d(A,B) = \sqrt{\sum_{i=1}^{k}(x_i - y_i)^2}
$$
在 Excel 中可以用=SQRT(SUMXMY2())实现，或者手动平方求和再开根。
平方欧式距离
去掉开方步骤，简化计算：
$$
d^2(A,B) = \sum_{i=1}^{k}(x_i - y_i)^2
$$
适用于仅需比较相对大小的场景。
块距离（Manhattan Distance）
各维度差值绝对值之和，像城市中沿着街道走的距离：
$$
d(A,B) = \sum_{i=1}^{k}|x_i - y_i|
$$
切比雪夫距离（Chebyshev Distance）
取各维度最大差异：
$$
d(A,B) = \max(|x_1 - y_1|, …, |x_k - y_k|)
$$
强调“最短板”的影响。
闵可夫斯基距离（Minkowski Distance）
通用公式，涵盖以上所有：
$$
d(A,B) = \left( \sum_{i=1}^{k}|x_i - y_i|^q \right)^{1/q}
$$
- $ q=1 $：块距离
- $ q=2 $：欧式距离
- $ q \to \infty $：切比雪夫距离
此外还有兰氏距离、马氏距离等更复杂的度量，适用于特定分布或协方差结构的数据，但对初学者来说，掌握欧式距离已足够应对大多数情况。
动手实战：五座商厦的客户评分聚类
来看一个具体案例。
某调研机构收集了客户对五座商厦（A、B、C、D、E）在“购物环境”与“服务质量”上的平均评分：
| 商厦 | 购物环境 | 服务质量 |
|---|---|---|
| A | 8 | 7 |
| B | 7 | 6 |
| C | 5 | 4 |
| D | 3 | 2 |
| E | 4 | 3 |
我们的目标是：根据这两项指标，使用层次聚类法将这五座商厦分组。
第一步：构建初始距离矩阵
在 Excel 中输入原始数据后，建立一个 $5\times5$ 的表格用于计算两两之间的欧式距离。
以 A 和 B 为例：
=SQRT((C2-C3)^2 + (D2-D3)^2)填充整个区域，并利用$锁定行列地址以便复制公式。最终得到如下距离矩阵 $ D_1 $：
| A | B | C | D | E | |
|---|---|---|---|---|---|
| A | 0 | 1.41 | 4.24 | 7.07 | 5.83 |
| B | 1.41 | 0 | 2.83 | 5.66 | 4.47 |
| C | 4.24 | 2.83 | 0 | 2.83 | 1.41 |
| D | 7.07 | 5.66 | 2.83 | 0 | 1.41 |
| E | 5.83 | 4.47 | 1.41 | 1.41 | 0 |
观察可知，最小距离为1.41，出现在三对组合中：A-B、C-E、D-E。我们可以任选其一作为首次合并对象，这里选择先合并 D 和 E。
创建新类 CL4 = {D, E}
类间距离怎么算？这才是关键
当多个样本合并成一类后，问题来了：这个“类”和其他类之间该怎么算距离？
这就是层次聚类中最核心的设计选择。常见的策略有以下几种：
最短距离法（Single Linkage）
取两类中任意两个样品间的最小距离。
优点是能捕捉链状结构，比如一条延伸的客户偏好链条；缺点是容易出现“拉伸效应”，导致本不该连在一起的簇被强行串联。
最长距离法（Complete Linkage）
取最大距离，倾向于生成紧凑、边界清晰的簇。
对异常值敏感，但结果通常更均衡。
类平均法（Average Linkage）
所有跨类样本对距离的均值，平衡性好，推荐用于一般场景。
Ward法
每次合并使类内离差平方和增量最小，偏好大小相近的簇，效果稳定。
重心法（Centroid Method）
基于类的中心点（均值）计算距离，几何意义明确，但可能出现逆序现象（即后续合并距离反而变小）。
本文为了便于演示流程，采用最短距离法。
层次聚类三步走：合并 → 更新 → 重复
层次聚类的本质是一个迭代过程：
- 初始状态：每个样品自成一类；
- 找出距离最近的两类，合并为新类；
- 更新距离矩阵，回到第2步，直到只剩一个大类。
我们继续操作。
步骤1：合并 D 和 E → CL4 = {D, E}
按照最短距离法原则：
$$
\text{dist}(CL4, X) = \min(\text{dist}(D,X), \text{dist}(E,X))
$$
在 Excel 中新增一行/列，分别计算 CL4 与其他类的距离：
- dist(CL4, A) = min(7.07, 5.83) = 5.83
- dist(CL4, B) = min(5.66, 4.47) = 4.47
- dist(CL4, C) = min(2.83, 1.41) = 1.41
删除 D、E 行列，得到新的距离矩阵 $ D_2 $：
| A | B | C | CL4 | |
|---|---|---|---|---|
| A | 0 | 1.41 | 4.24 | 5.83 |
| B | 1.41 | 0 | 2.83 | 4.47 |
| C | 4.24 | 2.83 | 0 | 1.41 |
| CL4 | 5.83 | 4.47 | 1.41 | 0 |
当前最小距离仍是1.41，出现在 C 与 CL4 之间。于是合并为新类 CL2 = {C, D, E}
步骤2：更新为 $ D_3 $
计算 CL2 与 A、B 的距离：
- dist(CL2, A) = min(dist(C,A), dist(CL4,A)) = min(4.24, 5.83) = 4.24
- dist(CL2, B) = min(2.83, 4.47) = 2.83
得到 $ D_3 $：
| A | B | CL2 | |
|---|---|---|---|
| A | 0 | 1.41 | 4.24 |
| B | 1.41 | 0 | 2.83 |
| CL2 | 4.24 | 2.83 | 0 |
最小距离出现在 A 与 B 之间（1.41），合并为 CL3 = {A, B}
步骤3：更新为 $ D_4 $
计算 CL3 与 CL2 的距离：
- dist(CL3, CL2) = min(dist(A,CL2), dist(B,CL2)) = min(4.24, 2.83) = 2.83
得到 $ D_4 $：
| CL3 | CL2 | |
|---|---|---|
| CL3 | 0 | 2.83 |
| CL2 | 2.83 | 0 |
最后一步：合并 CL3 与 CL2，形成最终大类 CL1 = {A, B, C, D, E}
绘制谱系图（Dendrogram）：让聚类“生长”可视化
整个聚类过程就像一棵倒挂的树，根在上，叶在下，每一步合并都对应一次分支的连接。这种图称为谱系图或树状图（Dendrogram）。
虽然 Excel 原生不支持动态绘制树状图（Excel 2016+ 的“树状图”图表类型其实是用于层级比例展示，非聚类用途），但我们可以通过插入形状或文本方式手动构造。
也可以用文本形式模拟：
A B C D E \ / \ / AB DE | CDE \ / ABCDE或者按层级展示：
Level 0: A B C D E Level 1: AB C DE Level 2: CDE Level 3: AB ---- CDE每一层代表一次合并，纵轴可以标注合并时的距离值，帮助判断合理的切割位置。
分几类最合适？不能只看算法
聚类完成后，真正的挑战才开始：我们应该在哪一层切断这棵树？
没有唯一正确答案，但有几个实用方法可以帮助决策：
1. 设定距离阈值
在谱系图中画一条水平线，落在同一连通区域内的归为一类。
例如，在距离 3.0 处横切：
- AB 自成一类
- CDE 自成一类
→ 得到两类：G1={A,B}, G2={C,D,E}
若在 1.5 处切，则可得三类：G1={A,B}, G2={C}, G3={D,E}
2. 肘部法则（Elbow Method）
观察每次合并带来的距离跳跃。当跳跃突然增大时，说明强行合并会导致类内差异剧增——这个“拐点”往往就是最佳划分点。
本例中，最后一次合并距离为 2.83，前一次为 1.41，增长明显，支持在 CL3 和 CL2 合并前停止，即保留两类。
3. 结合业务背景解释
回到实际场景：A 和 B 是高端商场，评分最高；C 居中；D 和 E 是社区型小商场。分成两类符合运营定位，也便于制定差异化营销策略。
Excel 实操技巧：让手工操作更高效
尽管这是手工操作，但合理组织工作表结构可以让过程清晰可控：
| Sheet名称 | 内容说明 |
|---|---|
原始数据 | 存放初始评分数据 |
距离矩阵_D1 | 初始两两距离 |
D2,D3, … | 每轮合并后的更新矩阵 |
合并记录 | 记录每次合并的类名、成员、距离 |
谱系图草图 | 使用形状工具绘制树状结构 |
📌 提示：
- 使用颜色标记每次合并的类，增强可读性；
- 利用“命名区域”管理类标签，避免混淆；
- 公式中善用$固定引用，提高复制效率；
- 可用“条件格式”高亮每轮最小值，快速定位合并对象。
Q型聚类 vs R型聚类：不只是对“人”分类
我们刚才做的是对样品（行）进行聚类，称为Q型聚类，比如对商场、客户、设备等实体分组。
但聚类还可以反过来，对变量（列）进行，称为R型聚类。
例如，在本例中，“购物环境”和“服务质量”如果高度相关，可能会被聚为一类，说明它们共同反映了一个潜在因子——比如“整体体验满意度”。这在降维、指标体系优化中有重要应用。
只需将原始数据转置，然后重复上述流程即可实现 R 型聚类。
小结：为什么还在用 Excel 做聚类？
你可能会问：现在有 Python、R、SPSS，为什么还要用 Excel？
答案很简单：对于教学和小型数据分析任务，Excel 的优势无可替代。
- ✅零编码门槛：无需安装环境，打开即用；
- ✅过程透明：每一步都在眼前，适合理解算法逻辑；
- ✅交互性强：修改一个数值，立刻看到距离变化；
- ✅适合演示：向非技术人员讲解时，Excel 比代码更有说服力。
当然，它的局限也很明显：样本超过 20 个后，手动维护矩阵会变得繁琐且易错。此时建议转向编程工具，如 Python 的scipy.cluster.hierarchy或 R 的hclust()函数。
但正因如此，Excel 成为了最好的“启蒙老师”——它让你亲手“种”出一棵聚类树，而不是仅仅看到结果。
当你第一次在 Excel 里看着那几个数字慢慢合并成簇，仿佛看见数据自己讲出了它的故事，那种感觉，真的很不一样。