用Python和Excel搞定TOPSIS综合评价：从数据清洗到结果可视化的保姆级教程

用Python和Excel实现TOPSIS综合评价从数据清洗到可视化决策当产品经理需要评估多个功能迭代方案的优先级当HR部门要对年度优秀员工进行量化考核或是当采购团队需要从数十家供应商中筛选最优合作伙伴时TOPSISTechnique for Order Preference by Similarity to Ideal Solution这种不依赖主观判断的客观评价方法就显得尤为实用。本文将绕过复杂的数学推导用两种技术栈Python自动化处理与Excel手工操作带你完整实现这套方法论。1. 理解TOPSIS的核心逻辑想象你在为新房选购家电通常会考虑价格、能耗、功能等多个指标。TOPSIS的智慧在于同时衡量每个选项与理想中最好产品和想象中最差产品的距离最终选择那个离完美最近、同时离糟糕最远的方案。技术实现上主要分为五个关键步骤数据标准化消除量纲差异让价格万元和耗电量千瓦时可以公平比较加权处理根据指标重要性分配权重如能耗比价格重要1.5倍确定理想解正理想解各指标最优值的集合负理想解各指标最劣值的集合距离计算各方案与正理想解的欧氏距离S各方案与负理想解的欧氏距离S-相对接近度计算C S- / (S S-)值越大方案越优实际业务中常见误区直接对原始数据做加权平均这会导致量纲大的指标如销售额完全主导结果而量纲小的指标如客户评分失去作用。2. Python自动化实现方案我们以供应商评估场景为例假设需要从6家供应商中选出最佳合作伙伴评估指标包括报价成本型越小越好交货准时率效益型越大越好质量合格率效益型售后服务评分效益型2.1 数据准备与预处理首先安装必要库pip install pandas numpy matplotlib seaborn创建模拟数据集import pandas as pd import numpy as np data { 供应商: [A, B, C, D, E, F], 报价(万元): [120, 95, 110, 105, 115, 100], 准时率(%): [92, 88, 95, 90, 93, 97], 合格率(%): [98, 95, 99, 96, 97, 100], 服务评分: [4.2, 3.8, 4.5, 4.0, 4.3, 4.7] } df pd.DataFrame(data).set_index(供应商)2.2 标准化处理与权重分配采用向量归一化方法并设定权重def normalize(df, weights): # 分离效益型与成本型指标 benefit_cols [准时率(%), 合格率(%), 服务评分] cost_cols [报价(万元)] # 向量归一化 norm_df df.copy() for col in benefit_cols: norm_df[col] df[col] / np.sqrt(sum(df[col]**2)) for col in cost_cols: norm_df[col] df[col].apply(lambda x: 1/x) / np.sqrt(sum((1/df[col])**2)) # 加权处理 return norm_df * weights weights np.array([0.3, 0.25, 0.25, 0.2]) # 报价权重30%其他指标合计70% weighted_df normalize(df, weights)2.3 计算理想解与距离矩阵def topsis(weighted_df): # 确定理想解 ideal_best weighted_df.max() ideal_worst weighted_df.min() # 计算距离 dist_best np.sqrt(((weighted_df - ideal_best)**2).sum(axis1)) dist_worst np.sqrt(((weighted_df - ideal_worst)**2).sum(axis1)) # 计算接近度 closeness dist_worst / (dist_best dist_worst) return closeness.sort_values(ascendingFalse) result topsis(weighted_df) print(供应商排序结果\n, result)2.4 可视化呈现生成雷达图直观展示各供应商表现import matplotlib.pyplot as plt from math import pi categories list(df.columns) N len(categories) angles [n / float(N) * 2 * pi for n in range(N)] angles angles[:1] fig plt.figure(figsize(8, 8)) ax fig.add_subplot(111, polarTrue) ax.set_theta_offset(pi/2) ax.set_theta_direction(-1) plt.xticks(angles[:-1], categories) ax.set_rlabel_position(0) for supplier in df.index: values df.loc[supplier].values.flatten().tolist() values values[:1] ax.plot(angles, values, linewidth1, labelsupplier) ax.fill(angles, values, alpha0.1) plt.legend(locupper right) plt.show()3. Excel手工操作指南对于不熟悉Python的同事可以通过Excel公式实现相同效果3.1 数据标准化步骤创建原始数据表A1:E7在F2单元格输入向量归一化公式B2/SQRT(SUMSQ($B$2:$B$7)) # 成本型指标报价 C2/SQRT(SUMSQ($C$2:$C$7)) # 效益型指标准时率向右填充至所有指标列3.2 加权处理在J1:N1设置权重值如0.3, 0.25, 0.25, 0.2在J2单元格输入F2*J$1向右向下填充至整个加权区域3.3 理想解确定正理想解最大/最小值集合MAX(J2:J7) # 成本型取最小 MAX(K2:K7) # 效益型取最大负理想解相反逻辑3.4 距离计算正理想距离O2单元格SQRT(SUMXMY2(J2:N2,$J$9:$N$9))负理想距离P2单元格SQRT(SUMXMY2(J2:N2,$J$10:$N$10))3.5 最终排序接近度计算Q2单元格P2/(O2P2)排序结果R2单元格RANK.EQ(Q2,$Q$2:$Q$7)提示Excel操作时建议使用条件格式对最优值进行颜色标注并插入柱状图展示最终评分4. 业务场景中的实战技巧4.1 权重设定的艺术权重分配是TOPSIS中最具主观性的环节推荐三种科学方法AHP层次分析法构建判断矩阵计算特征向量一致性检验CR0.1熵权法客观赋权def entropy_weight(df): p df / df.sum() entropy (-p * np.log(p)).sum() return (1 - entropy) / (len(df.columns) - entropy.sum())德尔菲法集合专家意见迭代收敛4.2 敏感性分析通过调整权重观察结果稳定性weight_ranges { 报价: (0.2, 0.4), 准时率: (0.15, 0.3), # ...其他指标范围 } for _ in range(1000): random_weights np.random.uniform( [v[0] for v in weight_ranges.values()], [v[1] for v in weight_ranges.values()] ) random_weights / random_weights.sum() results.append(topsis(normalize(df, random_weights)))4.3 异常值处理策略当数据存在极端值时Winsorize处理from scipy.stats.mstats import winsorize df[报价] winsorize(df[报价], limits[0.05, 0.05])对数变换df[报价] np.log1p(df[报价])分箱离散化适用于非线性关系5. 进阶应用与系统集成5.1 与BI工具结合将Python脚本集成到Power BI在Power Query中使用Python脚本创建TOPSIS计算度量值设计动态权重调整参数5.2 自动化评估系统使用Flask构建简易Web应用from flask import Flask, request, jsonify app Flask(__name__) app.route(/evaluate, methods[POST]) def evaluate(): data request.json df pd.DataFrame(data[candidates]) weights np.array(data[weights]) result topsis(normalize(df, weights)) return jsonify(result.to_dict()) if __name__ __main__: app.run(port5000)5.3 混合型指标处理当同时存在定量和定性指标时对定性指标如服务等级A/B/C进行数值化映射采用模糊数学处理语言变量统一量纲后进入TOPSIS流程qualitative_map { 服务等级: {A: 5, B: 3, C: 1}, 认证资质: {ISO9001: 1, ISO14001: 0.8, #...} } df.replace(qualitative_map, inplaceTrue)