怎么用excel制作九宫格图表

你知道excel可以制作九宫格图表吗?分本将为你一一介绍详细步骤,相信你看完之后一定会学会,并能制作出一份超秀的九宫格图表的,让我们一起看下去吧!

Step 01.  准备数据

A.下图是随机模拟的一份销售数据明细,小伙伴们可以根据自己实际工作数据灵活调整。

 

B.九宫格图表的制作需要依赖函数和辅助数据区域搭配完成,所以下图的辅助数据区域就出场了:

 

单元格公式:

=MOD(COLUMN(A1)-1,5)*200

MOD函数:MOD(被除数,除数),用于返回两数相除的余数。

COLUMN函数:COLUMN(单元格或者单元格区域),用于返回列号。

如果小伙伴们对函数不理解,可以打开Excel表格按F1查看一下帮助,这里我就不过多解释了,如果有需要的话可以留言,后续可以出单独的函数文章。

C.九宫格分层的辅助数据如下图所示:

 

细心的小伙伴一眼望去,就应该发现了上图数据中存在#N/A错误,第一反应肯定以为是数据出问题了。莫慌,数据没问题哈,此处的#N/A是故意用NA函数返回的,不是你想的#N/A错误。

解释一下上图的数据组成:

①  分成3层3组是因为我们要做的是九宫格的结构3*3=9,小伙伴在制作的时候可以灵活更改哦~

②  分隔线1、2、3用来区分层数,即上图4中N8单元格的数值是用公式:=IF($M8="",COLUMN(A:A)*1000,NA())生成的。

公式的意思是判断M8单元格是否等于空,等于空就返回引用列号乘1000,否则返回#N/A。在图表中,#N/A将不会被显示。

③  图4中的公式如下:

Q9单元格公式:=1000*(COLUMN(A1)-1)+B3

T16单元格公式:=1000*(COLUMN(A1)-1)+E3

W23单元格公式:=1000*(COLUMN(A1)-1)+H3

原理同分层时乘1000一样,既然每层按照1000间隔去区分,那么数据也需要在原有基础上去增加。

啰啰嗦嗦的解释了一大堆,估计小伙伴们已经等不及了,下面我们开始图表的制作吧。

Step 02.  插入折线图

选择N8:Y29单元格区域,单击【插入】选项卡,在插图分组中选择折线图中【带数据标记的折线图】,删除图例和网格线,如下图所示。

 

Step 03.  添加趋势线

鼠标双击分隔线标记点,右键选择【添加趋势线】,在【油漆桶】图标下方,设置线条为实线,颜色为灰白色,宽度1磅,短划线类型选择实线。分隔线1设置步骤如下图所示:

 

同理,对图表上的分隔线2和分隔线3进行操作,步骤如下图所示:

 

Step 04.  设置标记选项

可能有的小伙伴手感不是很好,操作的时候选不好图表中的标记点,贴心的花花已经想到了,可以先用鼠标选中图表,单击【格式】选项卡,在当前所选内容下拉列表里面选择【系列1】,设置所选系列格式,在【油漆桶】图标下方,将标记选项设置为【无】,【系列2】和【系列3】设置同理。操作步骤如下8所示:

 

Step 05.  添加误差线

鼠标选中图表,单击【格式】选项卡,在当前所选内容下拉列表里面选择【系列3】,接着在【设计】选项卡里面添加误差线,将误差线格式设置为【负偏差】、【无线端】、【误差百分比为100%】,操作步骤如下图9所示:

 

Step 06.  设置横坐标格式

单击鼠标右键,选中图表横坐标轴,设置其坐标轴格式,坐标轴位置设置为【在刻度线上】,在【油漆桶】图标下方,设置线条为实线,颜色为灰白色,宽度为1磅,短划线类型选择实线。

 

Step 07.  添加坐标轴标签

用鼠标选中图表后,右键点击【选择数据】,编辑(水平)分类轴标签,将区域设置为M8:M29,操作如下图所示:

 

Step 08.  添加辅助数据

鼠标选中图表后,右键点击【选择数据】,在图例项(系列)中点击【添加】,系列名称选择L1单元格,系列值选择M3:AB3单元格区域,操作步骤如下图所示:

 

Step 09.  更改图表类型

鼠标选中图表,单击【格式】选项卡,在当前所选内容下拉列表里面选择系列“辅助数据”,然后在【设计】选项卡里面将其图表类型更改为簇状条形图,操作步骤如下图所示。

 

Step 10.  设置次要坐标轴

鼠标选中图表,点击【设计】选项卡 è 添加图表元素 è 坐标轴 è 次要纵坐标轴。接着用鼠标选中条形图,右键点击【选择数据】,编辑【辅助数据】的轴标签区域为M2:AB2,完成操作后,图表右边的次坐标轴会出现刚刚添加的数据标签,操作步骤如下图所示。

 

Step 11.  设置条形图格式

我猜已经有小伙伴忍这个条形图很久了,实在是太碍眼了,下面我们就开始盘它!!!

选中条形图,单击鼠标右键,选择【设置数据系列格式】,在【油漆桶】图标下方设置无填充,边框设置为无线条,并在【系列选项】中,把间隙宽度调到最大。操作步骤如下图所示。

 

Step 12.  设置纵坐标轴格式

鼠标选中左边的纵坐标轴,单击鼠标右键,选择【设置坐标轴格式】,将最小值设置为0,最大值为3000,单位最大设置为200,接着在【设计】选项卡里面隐藏坐标轴标签(也可以直接删除坐标轴)。操作步骤如下图所示。

 

Step 13.  调整坐标轴标签位置

通过上述操作,九宫格图表算是初见雏形,但是和文章开头的效果图还是有些差别,我们还需要将坐标轴标签从右边调整到左边。先选中右边的坐标轴,单击鼠标右键,选择【设置坐标轴格式】,在标签选项里面设置标签位置为【低】,这样坐标轴标签就从右边调整到左边了。最后将图表上方不需要的坐标轴标签删除掉,操作步骤如下图17所示。

 

Step 14.  图表区域命名

图表中每格区域归属的信息需要插入一个文本框进行标注。插入文本框,在选中文本框的状态下,在编辑栏中输入等号,然后鼠标选中需要关联数据的单元格,按回车键确认。以湖北地区为例,操作步骤如下图所示。

 

Step 15.  设置平滑线

为了让图表看起来更加“圆润”,我们需要去除折线图的“棱角”。选中折线图,单击鼠标右键,选择【设置数据系列格式】,在【油漆桶】图标的最下方勾选平滑线。操作步骤如下图所示。

 

Step 16.  美化图表

对于图表标题的设置,我们也可以参考前面的文本框关联单元格数据的方法,这样在我们后期改动数据的时候,就可以实现图表数据动态更新了。剩下的就是给图表设置一个你喜欢的配色,并将其设置为圆角,然后用文本框在图表左下角添加数据来源和作者的信息,让图表呈现的更加专业。操作步骤如下图所示。

山姐姐提供专业word/office/excel教程及技巧,以及免费PPT模板下载。
山姐姐办公素材网 » 怎么用excel制作九宫格图表

提供最优质的资源集合

立即查看 了解详情