Excel综合分析多指标问题

1.综合分析

引用百度百科一句话:综合分析法是指运用各种统计综合指标来反映和研究社会经济现象总体的一般特征和数量关系的研究方法。

在本文展示的例子,主要浅谈某水果店的中各类水果和多个指标存在的关系,使用综合分析方法找出水果店中的各类水果的排名。

2.主要思路

  1. 对多指标通过矩阵进行权重划分

    确认各指标的权重值

  2. 对各指标进行归一化,也是0-1标准化

    主要消除量纲差异导致的影响

  3. 利用归一化的指标与指标进行矩阵相乘求和计算出各类水果的综合得分

    利用综合得分对各水果进行排名

3.实操

1.以水果店的各类水果为例

假设原始数据整理汇总如下:

	ROI(投资回报率)	下单转化率	试吃率		来客数
百香果		2.79 	2.50%		3.00%		492 
西瓜		4.80 	1.50%		5.00%		413 
李子		4.61 	1.20%		10.00%		529 
芒果		-0.83 	2.30%		1.00%		377 
桃子		2.89 	2.00%		8.00%		737 
荔枝		5.87 	1.10%		9.00%		781 
梨子		1.15 	3.40%		1.00%		532 
香蕉		3.40 	1.30%		2.00%		644 

以上的数据纯属手动构造,主要是为了巩固知识和学会如何使用综合分析法去解决它们之间的排名问题

2.确认指标权重

首先,我们要明白指标权重背后的原理,它把复杂的东西简单化,最后转化成计算机认识的0-1逻辑思维,从而产生结果。

接着,把纵轴上的多个指标依次构成新的一列指标,以便得到纵横两两指标之间对比。

最后,由专家进行投票决定,如果纵轴上的指标比横轴上的指标重要,那么在两个指标交叉的单元格中填“1”,否则填“0”

  • 对比指标重要性

现在,假设我对指标重要性做出判断,如下:

Excel综合分析多指标问题

如上图,我把相同的指标作比较时,单元格填“0”,横轴的指标与纵轴的指标作比较,如果横纵的指标比纵轴的指标大,单元格填“1”,否则填“0”。

  • 指标权重占比

Excel综合分析多指标问题

如上图,由于指标得分出现0分,为了保证每个指标可用,我们把得分+1,最后得到权重占比

3.数据归一化

公式=(x - min(数据范围))/(max(数据范围) - min(数据范围)),x为当前值,数据范围为某个指标下所有的水果种类,最后计算得到的结果如下:

Excel综合分析多指标问题

4.计算综合得分

综合得分,使用了Excel的MMULT函数,该函数返回两个数组的矩阵乘数,本例中的两个数组分别是权重占比四个指标下的所有水果值

排名,使用了Excel的RANK.AVG函数,该函数返回一列数字的数字排位。

Excel综合分析多指标问题

从上图中,我们看到排名第一的是荔枝,果然是夏季的当季水果,深受很多人喜欢。

Excel综合分析多指标问题

需要注意的是,如上例子中,综合得分生成的结果是数组,想要完成生成数组公式操作,最后输入完成后使用ctrl + shift + enter快捷键完成。(如果公式中有大括号,它变成了一个数组公式;如仍使用以前的方式,单元格填=MMULT(array1, array2)再向下拖拽或者ctrl+D,那么后续的值就会报错,所以,要把综合得分下的所有单元格选中,在第一个单元格填入该公式,如不显示光标可按F2显示光标,填写完公式,最后按ctrl + shift + enter快捷键即可生成一个数组)。

4.总结

本文以多个关键指标找出水果店里哪些水果在夏季更受人欢迎。本文开始先是给出经过整理汇总的数据,接着以假设方式,跳过应当以投票得来的权重得分,获取到各个重要指标占比后,进一步做数据归一化,最后使用Excel的MMULT和Rank函数分别完成综合得分和数字排位。

Excel综合分析多指标问题

上一篇:RM电控入门(二) GPIO


下一篇:HPA-VPA-KPA实现自动扩容