首页 > Excel专区 > Excel技巧 >

excel 有趣的正方形 波士顿矩阵图[隔岸观火]

Excel技巧 2021-11-25 12:27:15

有时我们需要设定图表绘图区为长宽相等的正方形,但绘制在直角坐标系的图表并不予以支持,调整的方法往往是通过目视拖曳来设定,不够精准。图7.3-1所示即是一个典型案例。

有趣的正方形[隔岸观火]

图7.3-1有趣的正方形

案例介绍

图7.3-1所示的案例是一个波士顿矩阵图,该图表通过两组数据的交叉点在直角坐标系中进行定位描点,并4等分图表面积,将图表裂区分组为4个不同区域,每1个区域代表1类属性,然后根据落在不同区域的数据所属属性来进行分析。

注:此图多用于商业策划与分析,有关具体的应用详情请搜索互联网。

有趣的正方形[隔岸观火]

学习思考


关于波士顿矩阵图:

  1. 在视觉上具有强烈的分割布局,分割的每个区域在约定俗成上有“问题”、“明星”、“瘦狗”、“奶牛”四个约定俗成的隐喻指定。
  2. 通常在实际的应用中,由于不同指标间的关系不同,图表在制作时的发射方向并不相同。
  3. 需要说明:使用强制绘图区为正方形,可以使图表在视觉上更加容易被阅读和理解,但这并非是一个强制使用的要求。

案例分析

图7.3-1案例在直观制作上似乎并不具有太大难度:

  1. 两组数据的交叉点在直角坐标系中进行定位描点使用XY散点图即可实现;
  2. 4等分图表面积可以使用绘图区背景来实现;
  3. 如何使绘图区固定为正方形?在Excel图表中,极坐标系图表具有正方形绘图区特性,在图表中引入饼图、环形图、雷达图即可解决该问题。

不论版本,Excel均可使用上述方法来实现这个图表。但不幸的是:由于Excel自2007始重构的图表引擎和之前版本存在一定的兼容性问题,故使用上述方法来实现本案例时,使用Excel 2003绘制的图表,Excel 2007打开后会没有绘图区背景,反之亦然。所以当文档要在不同版本的Excel中打开时,就面临着如何使不同版本打开时均显示正常的问题。由此,4等分图表面积使用绘图区背景来实现不具有广泛意义,这带来了以下新的挑战:

1)4等分图表面积必须改用堆积柱形图来实现,而这会给两组数据的交叉点在直角坐标系中进行定位描点带来困难,因为二者具有不同的类型的横轴(X轴),堆积柱形图使用分类坐标,而XY散点使用数值坐标。

2)极坐标系图表必须独占图表的一个坐标系,这必须使堆积柱形图和XY散点共置一个绘图区。所以挑战1)只可以在同一坐标系的前提下解决。

案例实现

本案例中“指标A”为XY散点图的X值,“指标B”为XY散点图的Y值,变换XY散点图X值是图表实现的关键。当纵轴置于分类坐标之间时,分类坐标实际是自0.5起始、步长为1的序列数值。此处使用两个分类,故其最大数值为2.5,这就为XY散点图X值的转换提供了有价值的信息。具体实现说明如下:

1.添加辅助列“辅助背景A”和“辅助背景B”,各两个数据点,根据“指标A”的最大数值,此处数值设定为50,该辅助列的目的是将绘图区面积4等分。选中新添加的两列辅助列,制作堆积柱形图,数据产生在列,如图7.3-2所示。

有趣的正方形[隔岸观火]

图7.3-2 7.3-1案例实现第1步

2.调整主纵坐标轴最小刻度为0,最大刻度为100,主要刻度单位为10;然后依次将左侧图片复制粘贴到右侧图表对应的数据点中,将图例删除。注:此处请根据需要来设置填充,可以设置为相对应的填充颜色,如图7.3-3所示。

有趣的正方形[隔岸观火]

图7.3-3 7.3-1案例实现第2步

3.将“指标B”所在列数值复制并粘贴进图表,系列产生在列,如图7.3-4所示。

有趣的正方形[隔岸观火]

图7.3-4 7.3-1案例实现第3步

4.添加辅助列“辅助X”,目的是将“指标A”所在列数值进行转换,以保持和分类坐标最小值和最大值的一致性。此处使用的公式大致如下:

=当前数值/坐标最大值*2

然后修改第3步中新建的图表系列为XY散点图,并修改系列所引用的X值,将其指定为“辅助X”所在列的数据区域,如图7.3-5所示。

有趣的正方形[隔岸观火]

图7.3-5 7.3-1案例实现第4步

5.此时图表横轴标签和真实数据存在差异,需要进行调整。根据图表数值轴,添加辅助列“辅助X轴X”、“辅助X轴Y”和“辅助X轴标签”,此处使用10行,目的是依照纵轴样式来模拟图表横轴标签。

  1. “辅助X轴X”列的数值为:自0.7起始,步长间隔0.2,依次到2.5结束的数值;
  2. “坐标X轴Y”列的数值为空;
  3. “辅助X轴标签”列的数值为:自10起始,步长间隔10,依次到100结束的数值。

提示


1)和2)均可在第1和第2单元格依上述内容填入数值后,然后选中第1和第2单元格,下拉拖曳来完成;也可使用函数公式实现。


使用“辅助X轴X”和“辅助X轴Y”所在列数据,在图表添加一个XY散点图系列,并使用“辅助X轴标签”所在列数据,通过在编辑栏使用“=”将该系列的数据点标签进行一个个的动态引用。最后将坐标轴标签设为“无”,并选中图表,设置图表选项:空单元格以“零值代表”,如图7.3-6所示。

有趣的正方形[隔岸观火]

图7.3-6 7.3-1案例实现第5步

6.在图表中添加只有1个数据点的新图表系列,并修改该系列的图表类型为饼图,设置饼图系列无填充无边框。此时不管我们如何拖曳设置图表,绘图区将保持绝对的正方形,如图7.3-7所示。

有趣的正方形[隔岸观火]

图7.3-7 7.3-1案例实现第6步

7.修饰整理后的最终效果如图7.3-8所示。

有趣的正方形[隔岸观火]

图7.3-8 7.3-1案例最终实现

其他

图7.3-9所示的案例是一个SWOT分析矩阵,准确而言这个并非图表,看不到丝毫的数值数据,仅仅是个文本列表。该案例的大多数实现方法是使用单元格或自选图形,虽然文本的编辑非常方便,但糟糕的是如果进一步进行美化编辑时就显得非常烦琐,对齐、颜色等问题让人烦心。换个思路来试试,使用图7.3-1案例的实现方法,我们会发现原来Excel图表还可以这么使用,最经典的是,无论如何调整其都保持正方形外观。

有趣的正方形[隔岸观火]

图7.3-9 SWOT分析矩阵

图7.3-10所示的案例是一个风险矩阵图,这个案例和图7.3-1案例实现的方法不出左右,而且该案例配合绘图区正方形的格式,更加易于判断风险等级。

有趣的正方形[隔岸观火]

图7.3-10 风险矩阵Risk Matrix

名词解释:


■ SWOT分析矩阵:

SWOT分析中,S代表Strength(优势),W代表Weakness(弱势),O代表Opportunity(机会),T代表threat(威胁),其中,S、W是内部因素,O、T是外部因素。根据既定的内在条件,借助该工具进行分析,找出优势、劣势及核心竞争力之所在。

■ 风险矩阵Risk Matrix:

风险矩阵常配合FMEA(Failure Mode and Effect Analysis,失效模式和效果分析)来使用,其目的主要是评价当前项目的整体风险状态,通过对高风险等级具体项实施预防性措施,降低整体项目的风险系数。该图表一般使用3*3的9宫格和5*5的25宫格来界定项目风险等级。使用发生概率和影响等级两组数值在直角坐标系中交叉描点,发生概率和影响等级使用定性打分方式给出,评价指标为1~9的整数。


示例文档


本书所附案例文档7.3是上述案例的实现。

考虑到使用绘图区背景的方法有兼容性问题,案例包含了两个版本,有兴趣的读者可以在不同版本Excel中交叉打开观察,这样可以更好地理解兼容性问题。

除本节详述的案例实现方法外,还可使用绘图区背景及其他不同类型案例的实现方法,感兴趣的读者可参照案例文档演练学习。


标签: excel技巧隔岸观火波士顿矩阵图

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.