用Python和Excel搞定TOPSIS综合评价：从数据清洗到结果可视化（附完整代码）

TOPSIS实战指南：用Python和Excel轻松搞定多指标决策

当我们需要在多个选项中做出选择时，常常会面临一个难题：每个选项在不同指标上表现各异，有的指标表现优异，有的则不尽如人意。比如选择供应商时，价格、质量、交货时间等指标往往相互矛盾；评估员工绩效时，业绩、团队合作、创新能力等维度也难以简单比较。TOPSIS（Technique for Order Preference by Similarity to Ideal Solution）方法正是为解决这类多准则决策问题而生的利器。

1. TOPSIS方法的核心思想

TOPSIS，即"逼近理想解的排序方法"，它的核心思想非常直观：在所有备选方案中，最佳方案应该距离理想解最近，同时距离负理想解最远。这里的理想解是指所有指标都达到最优值的虚拟方案，负理想解则是所有指标都处于最差值的虚拟方案。

这种方法最早由Hwang和Yoon在1981年提出，如今已成为管理科学、运筹学等领域最常用的多属性决策方法之一。与AHP（层次分析法）等需要复杂两两比较的方法相比，TOPSIS具有计算简单、逻辑清晰、易于理解的优点，特别适合实际工作中的快速决策。

TOPSIS的典型应用场景包括：

• 产品选择：比较不同产品在价格、性能、质量等维度的表现
• 供应商评估：基于成本、交货时间、质量等指标筛选最优供应商
• 投资决策：评估不同投资项目在风险、收益、流动性等方面的平衡
• 人才评估：综合考量候选人的专业技能、工作经验、沟通能力等素质

2. TOPSIS的六步实现流程

2.1 数据准备与规范化

TOPSIS分析的第一步是构建决策矩阵。假设我们有5个供应商需要评估，考虑的指标包括价格（成本型，越低越好）、质量（效益型，越高越好）和交货时间（成本型，越短越好），原始数据如下表所示：

由于各指标的量纲和方向性不同，我们需要先进行规范化处理。常用的向量规范化公式为：

# Python实现向量规范化 import numpy as np def normalize_matrix(matrix): # 计算每列的平方和开根号 norms = np.sqrt(np.sum(matrix**2, axis=0)) # 避免除以零 norms[norms == 0] = 1 return matrix / norms # 示例数据（仅数值部分） raw_data = np.array([ [12, 8, 5], [15, 7, 3], [10, 6, 7], [18, 9, 4], [14, 7.5, 6] ]) normalized_data = normalize_matrix(raw_data) print("规范化后的矩阵:\n", normalized_data)

在Excel中，可以使用以下公式实现相同的规范化效果（假设数据位于B2:D6区域）：

=B2/SQRT(SUMSQ(B$2:B$6))

2.2 指标加权处理

不同指标的重要性往往不同，我们需要为每个指标分配权重。确定权重的方法有多种，如德尔菲法、AHP法等。假设我们通过专家评估确定价格、质量、交货时间的权重分别为0.4、0.3、0.3。

加权规范化矩阵的计算公式为： [ c_{ij} = w_j \times b_{ij} ]

Python实现：

weights = np.array([0.4, 0.3, 0.3]) weighted_data = normalized_data * weights print("加权后的矩阵:\n", weighted_data)

Excel中只需将规范化后的值乘以相应权重即可。

2.3 确定理想解与负理想解

根据指标类型（效益型或成本型）确定理想解和负理想解：

• 理想解(C)*：每个指标取加权矩阵中的最优值（效益型取最大，成本型取最小）
• 负理想解(C0)：每个指标取加权矩阵中的最差值（效益型取最小，成本型取最大）

Python代码实现：

# 指标类型：0表示成本型，1表示效益型 criteria_types = np.array([0, 1, 0]) ideal_solution = np.where(criteria_types == 1, np.max(weighted_data, axis=0), np.min(weighted_data, axis=0)) negative_ideal_solution = np.where(criteria_types == 1, np.min(weighted_data, axis=0), np.max(weighted_data, axis=0)) print("理想解:", ideal_solution) print("负理想解:", negative_ideal_solution)

2.4 计算距离与贴近度

计算每个方案到理想解和负理想解的欧氏距离：

[ s_i^* = \sqrt{\sum_{j=1}^n (c_{ij} - c_j^*)^2} ] [ s_i^0 = \sqrt{\sum_{j=1}^n (c_{ij} - c_j^0)^2} ]

然后计算相对贴近度：

[ f_i^* = \frac{s_i^0}{s_i^0 + s_i^*} ]

Python实现：

# 计算距离 dist_to_ideal = np.sqrt(np.sum((weighted_data - ideal_solution)**2, axis=1)) dist_to_negative = np.sqrt(np.sum((weighted_data - negative_ideal_solution)**2, axis=1)) # 计算贴近度 closeness = dist_to_negative / (dist_to_ideal + dist_to_negative) print("到理想解距离:", dist_to_ideal) print("到负理想解距离:", dist_to_negative) print("贴近度:", closeness)

2.5 结果排序与解读

根据贴近度对方案进行排序，贴近度越大表示方案越优。我们可以将结果可视化：

import matplotlib.pyplot as plt import pandas as pd results = pd.DataFrame({ 'Supplier': ['A', 'B', 'C', 'D', 'E'], 'Closeness': closeness }).sort_values('Closeness', ascending=False) plt.figure(figsize=(10, 6)) plt.barh(results['Supplier'], results['Closeness'], color='skyblue') plt.xlabel('TOPSIS Closeness Coefficient') plt.title('Supplier Ranking by TOPSIS Method') plt.gca().invert_yaxis() # 最高分在上方 plt.show()

在Excel中，可以使用排序功能对贴近度进行降序排列，并通过条形图直观展示结果。

3. 实际应用中的技巧与陷阱

3.1 权重确定的实用方法

权重对结果影响重大，以下是几种实用的权重确定方法：

1. 主观赋权法：

   • 直接评分法：由专家直接给出各指标权重
   • 德尔菲法：通过多轮专家调查达成共识

2. 客观赋权法：

   • 熵权法：根据数据本身的离散程度确定权重
   • CRITIC法：同时考虑对比强度和冲突性

3. 组合赋权法：

   • 将主客观权重按一定比例结合

# 熵权法示例实现 def entropy_weight(matrix): # 标准化 norm_matrix = matrix / np.sum(matrix, axis=0) # 计算熵值 k = 1 / np.log(matrix.shape[0]) entropy = -k * np.sum(norm_matrix * np.log(norm_matrix + 1e-10), axis=0) # 计算差异系数 d = 1 - entropy # 计算权重 weights = d / np.sum(d) return weights entropy_weights = entropy_weight(raw_data) print("熵权法计算的权重:", entropy_weights)

3.2 处理特殊指标类型

除了常见的效益型和成本型指标，实践中还可能遇到：

1. 区间型指标：希望指标值落在某个区间内
2. 固定型指标：希望指标值等于某个特定值
3. 非线性关系：指标与效用之间不是线性关系

对于区间型指标，可以使用以下转换公式：

[ b_{ij} = \begin{cases} 1 - \frac{a_j^0 - a_{ij}}{a_j^0 - a_j'} & a_j' \leq a_{ij} < a_j^0 \ 1 & a_j^0 \leq a_{ij} \leq a_j^* \ 1 - \frac{a_{ij} - a_j^}{a_j'' - a_j^} & a_j^* < a_{ij} \leq a_j'' \ 0 & \text{其他} \end{cases} ]

3.3 常见问题与解决方案

1. 权重敏感性分析：

   • 轻微调整权重，观察排序结果是否稳定
   • 如果结果对权重变化敏感，需要重新审视权重分配

2. 数据异常值处理：

   • 使用标准化替代规范化
   • 对极端值进行Winsorize处理

3. 结果验证：

   • 与其他多准则决策方法（如AHP、VIKOR）结果对比
   • 检查是否符合业务直觉和经验判断

4. 高级应用与扩展

4.1 结合模糊理论的Fuzzy TOPSIS

当评价信息不确定或模糊时，可以使用模糊TOPSIS。基本步骤：

1. 将语言评价转换为模糊数
2. 构建模糊决策矩阵
3. 确定模糊理想解和负理想解
4. 计算模糊距离
5. 计算贴近度并排序

# 模糊TOPSIS简化示例 from skfuzzy import trimf # 定义语言变量到三角模糊数的映射 linguistic_terms = { '很差': (0, 0, 2.5), '差': (0, 2.5, 5), '一般': (2.5, 5, 7.5), '好': (5, 7.5, 10), '很好': (7.5, 10, 10) } def linguistic_to_fuzzy(term): return linguistic_terms.get(term, (0, 0, 0)) # 示例：将语言评价转换为模糊数 fuzzy_data = np.array([[linguistic_to_fuzzy('好') for _ in range(3)] for _ in range(5)])

4.2 动态TOPSIS与时间序列分析

对于需要考虑时间变化的决策问题，可以引入时间权重，构建动态TOPSIS模型：

1. 确定不同时间点的重要性权重
2. 计算各时间点的TOPSIS结果
3. 加权聚合得到最终排序

4.3 与其他方法的结合应用

1. TOPSIS-AHP组合：

   • 用AHP确定指标权重
   • 用TOPSIS进行方案排序

2. TOPSIS-DEA组合：

   • 先用DEA筛选有效决策单元
   • 再用TOPSIS对有效单元排序

3. TOPSIS-灰色关联分析组合：

   • 考虑数据的不确定性和信息不完全性

5. 完整Python实现与Excel模板

5.1 Python完整实现代码

import numpy as np import pandas as pd import matplotlib.pyplot as plt class TOPSIS: def __init__(self, data, weights, criteria_types): """ :param data: 决策矩阵，每行一个方案，每列一个指标 :param weights: 指标权重数组 :param criteria_types: 指标类型数组，0表示成本型，1表示效益型 """ self.data = np.array(data) self.weights = np.array(weights) self.criteria_types = np.array(criteria_types) self.normalized_data = None self.weighted_data = None self.ideal_solution = None self.negative_ideal_solution = None self.dist_to_ideal = None self.dist_to_negative = None self.closeness = None def normalize(self): """向量规范化""" norms = np.sqrt(np.sum(self.data**2, axis=0)) norms[norms == 0] = 1 # 避免除以零 self.normalized_data = self.data / norms def apply_weights(self): """应用权重""" self.weighted_data = self.normalized_data * self.weights def determine_solutions(self): """确定理想解和负理想解""" self.ideal_solution = np.where(self.criteria_types == 1, np.max(self.weighted_data, axis=0), np.min(self.weighted_data, axis=0)) self.negative_ideal_solution = np.where(self.criteria_types == 1, np.min(self.weighted_data, axis=0), np.max(self.weighted_data, axis=0)) def calculate_distances(self): """计算距离""" self.dist_to_ideal = np.sqrt( np.sum((self.weighted_data - self.ideal_solution)**2, axis=1)) self.dist_to_negative = np.sqrt( np.sum((self.weighted_data - self.negative_ideal_solution)**2, axis=1)) def calculate_closeness(self): """计算贴近度""" self.closeness = self.dist_to_negative / (self.dist_to_ideal + self.dist_to_negative) def run(self): """执行完整TOPSIS流程""" self.normalize() self.apply_weights() self.determine_solutions() self.calculate_distances() self.calculate_closeness() return self.closeness def visualize(self, alternative_names=None): """可视化结果""" if alternative_names is None: alternative_names = [f'Option {i+1}' for i in range(len(self.data))] results = pd.DataFrame({ 'Alternative': alternative_names, 'Closeness': self.closeness }).sort_values('Closeness', ascending=False) plt.figure(figsize=(10, 6)) plt.barh(results['Alternative'], results['Closeness'], color='skyblue') plt.xlabel('TOPSIS Closeness Coefficient') plt.title('Alternatives Ranking by TOPSIS Method') plt.gca().invert_yaxis() plt.show() # 使用示例 if __name__ == "__main__": # 示例数据 data = [ [12, 8, 5], [15, 7, 3], [10, 6, 7], [18, 9, 4], [14, 7.5, 6] ] weights = [0.4, 0.3, 0.3] criteria_types = [0, 1, 0] # 0:成本型, 1:效益型 alternatives = ['Supplier A', 'Supplier B', 'Supplier C', 'Supplier D', 'Supplier E'] # 执行TOPSIS分析 topsis = TOPSIS(data, weights, criteria_types) closeness = topsis.run() topsis.visualize(alternatives)

5.2 Excel实现模板

在Excel中实现TOPSIS，可以按照以下步骤设置模板：

1. 数据输入区：输入原始决策矩阵
2. 权重输入区：设置各指标权重
3. 规范化计算区：使用公式=B2/SQRT(SUMSQ(B$2:B$6))计算规范化值
4. 加权计算区：规范化值乘以权重
5. 理想解计算：
   • 效益型：=MAX(加权列)
   • 成本型：=MIN(加权列)
6. 负理想解计算：
   • 效益型：=MIN(加权列)
   • 成本型：=MAX(加权列)
7. 距离计算：
   • 到理想解距离：=SQRT(SUMSQ(加权行-理想解行))
   • 到负理想解距离：=SQRT(SUMSQ(加权行-负理想解行))
8. 贴近度计算：=负理想距离/(负理想距离+理想距离)
9. 排序结果：使用RANK函数或排序功能

提示：在Excel中使用条件格式可以直观地高亮显示最优和最差值，帮助快速识别理想解和负理想解。

5.3 实际案例：员工绩效评估

假设我们需要评估5名员工在四个维度上的表现：

1. 销售业绩（效益型）
2. 客户满意度（效益型）
3. 项目完成时间（成本型）
4. 团队协作（效益型）

原始数据和权重如下：

# 员工绩效评估案例 performance_data = [ [85, 90, 10, 8], [92, 88, 15, 7], [78, 85, 8, 9], [88, 92, 12, 8], [95, 85, 14, 7] ] performance_weights = [0.4, 0.3, 0.1, 0.2] performance_types = [1, 1, 0, 1] employee_names = ['张三', '李四', '王五', '赵六', '钱七'] # 执行TOPSIS分析 topsis_perf = TOPSIS(performance_data, performance_weights, performance_types) closeness_perf = topsis_perf.run() topsis_perf.visualize(employee_names)

通过TOPSIS分析，我们可以得到综合考虑多个维度的员工绩效排名，避免了单一指标评估的片面性。