私信回复关键词【礼包】,获取超大办公福利礼包~
PPT、Word、Excel模板一网打尽!
最近小A在分析公司 2008-2019 年年度销售情况。
我下班的时候经过她的工位,发现她的数据图一点都不直观,怪不得大家都下班回家了,她还在苦苦「挣扎」。
![a002c9d98d12ce25f49be9e37bd61084.png](https://i-blog.csdnimg.cn/blog_migrate/42ce537f89bf03debf4f5ff94065d7b4.jpeg)
「怎么样才能让数据更直观呢?」
一个很简单的小技巧,就是在图表上添加最大值、最小值和平均值。
最大值、最小值最好能在图表上标明,一眼就看到。
添加平均值,则可以很清楚地看到每年的销售额,哪些在平均值线之上,哪些在下;哪些年份的销售情况比较好,哪些年份又拖了后腿。
突出重点,方便对数据进行分析。
说了这么多,我们就以「某公司 2008-2019 年年度销售情况」为例,教大家如何快速在折线图上添加最大值、最小值和平均值线。
01
原始数据:
![ae9734fe9dec5ca469b1d1ca48d706f5.png](https://i-blog.csdnimg.cn/blog_migrate/f0773228f64a759414dce150fd31e6be.jpeg)
▋添加辅助列:最大值、最小值、平均值
❶ 选中 C2 单元格,输入公式:
=IF(B2=MAX($B$2:$B$13),MAX($B$2:$B$13),NA())
▲ 左右滑动查看
回车!
![0406eb19cc9eb4f10f86cd6b13377839.png](https://i-blog.csdnimg.cn/blog_migrate/c1564a9aea58d47262ca78def05b4532.jpeg)
公式含义:如果 B2 单元格等于 B2:B13 中的最大值,则返回该值,否则为#N/A。
❷ 选中 D2 单元格,输入公式:
=IF(B2=MIN($B$2:$B$13),MIN($B$2:$B$13),NA())
▲ 左右滑动查看
回车!
![a89b664fb3e4797a35841f47d96f74ce.png](https://i-blog.csdnimg.cn/blog_migrate/89af51a73b3a6914e05843bf82dedd1c.jpeg)
公式含义:如果 B2 单元格等于 B2:B13 中的最小值,则返回该值,否则为#N/A。
❸ 选中 E2 单元格,输入公式:
=ROUND(AVERAGE(B$3:B$8),0)
回车!
![445f76bcd96e0a106ad2680fc9088a6c.png](https://i-blog.csdnimg.cn/blog_migrate/ead03b7b3c7e9bfcb9c904af212b6ee9.jpeg)
公式含义:先用 AVERAGE 函数计算 B2:B13 区域的平均值,再用函数 ROUND 对它的平均值进行四舍五入取整。
▋插入折线图
选中数据区域,点击【插入】-「图表」-「折线图」。
![6e5cebed96bf18a527904d0c12a5e568.png](https://i-blog.csdnimg.cn/blog_migrate/237e99c11f1f4fbff23985d508d7e067.jpeg)
到这里,平均值就已经出现了,接下来,我们来看下最大值和最小值的数值,怎么显示在图上。
▋显示最大值和最小值
❶ 选中绘图区,右键,打开【设置绘图区格式】,单击【绘图区选项】下的小三角,选择【系列「最大值」】。
![c833e6c8032bdb115ccfea7f3290fe6f.png](https://i-blog.csdnimg.cn/blog_migrate/0b011f8b42d12fcbd0597fe81fbb2acc.jpeg)
❷ 勾选「无线条」。
![d28bc8cb18eeac48412f38d632d466c3.png](https://i-blog.csdnimg.cn/blog_migrate/02460473eb9823df98726e63d076a808.jpeg)
❸ 在【图表工具】-【设计】选项卡下,单击最右侧的「添加图表元素」,选择「数据标签」-「其他数据标签选项」,打开【设置数据标签格式】。
![5e1e258e05e466e4e69e6257363b83aa.png](https://i-blog.csdnimg.cn/blog_migrate/72e223705832866d42a94e46ffb39568.jpeg)
❹ 单击「标签选项」,即
![ee9671ebe35ecc25f11ef138f0181fda.png](https://i-blog.csdnimg.cn/blog_migrate/c377bf4c375bd81dce95c0fbd0f839ae.jpeg)
然后勾选「系列名称」、「值」,标签位置调整为「靠上」。
![ad25002f962b9dd1915a312a162224a8.png](https://i-blog.csdnimg.cn/blog_migrate/30810c25d1b4c895cfa87884d6cddc99.jpeg)
❺ 用同样的方法设置好最小值。
![3d9f9522c2eaf8a4916689a29c0c65f7.png](https://i-blog.csdnimg.cn/blog_migrate/f86b1b67e2fc99ec622519aa880ac3b7.jpeg)
给折线图添加最大值、最小值和平均值,这样就完成了,既清晰又直观!!
说到这里,顺便补充一个关于图表的小知识点。
如果你在插入折线图时,本该是水平轴标题的数据也变成了折线。
就像这样:
![e2054bc304d0bbb69cfa4cac62814daf.png](https://i-blog.csdnimg.cn/blog_migrate/7b75ba0edac5afb04a7039bf30784756.jpeg)
很可能是因为第 1 列的数据是数值型数据。
解决办法 ❶:
① 将数值型数据转换成文本型数据。
选中 A2 单元格,在数字前增加英文的单引号,回车,下拉填充。
![174089d8f6c8b8ec02079b17892fe108.png](https://i-blog.csdnimg.cn/blog_migrate/75ee37e084749add182057b903712075.jpeg)
然后再插入折线图就行啦!
解决办法 ❷:
① 选择除第 1 列的数据,点击【插入】-【折线图】。
![1c1d4e19753f2f0ad2677345e9e43d1c.png](https://i-blog.csdnimg.cn/blog_migrate/eacd6428fbc96b8f28210a5f966bf407.jpeg)
② 选中水平轴,右键-【选择数据】,编辑「水平轴标签」。
![a3fdc371c56f0fd6264ee2e59cfb88ed.png](https://i-blog.csdnimg.cn/blog_migrate/143e03b59cd84aeaffb04f1a10aae8bc.jpeg)
③ 选择第 1 列除标题外的数据,单击两次【确定】即可!
![2dedbc9ddab79492cec6750e116c8fb6.png](https://i-blog.csdnimg.cn/blog_migrate/ff6212c0d4f61a66259e90324f0fec99.jpeg)
02
除了折线图,柱状图、条形图等图表也可以插入平均值线。
来举个例子,源数据还是公司 2008-2019 年年度的销售额。
![8a3fe66b1d80f3c16c1dddeb9b3fe172.png](https://i-blog.csdnimg.cn/blog_migrate/5ba6716a5a6618ae38e29d6db3f0911d.jpeg)
操作步骤:
❶ 添加辅助列「平均值」;
❷ 全选,插入图表「簇状柱形图」;
![c6828dfffe74909f2928a43dc2199fbc.png](https://i-blog.csdnimg.cn/blog_migrate/dd76624cee0c03f081914ec9d8618465.jpeg)
❸ 选中图表区,单击【图表工具】-【设计】选项卡,找到【更改图表类型】,将图表类型改为「组合图」,平均值改为折线图,确定!
![e3a2be2bba496b85ce9993340f7f12ff.png](https://i-blog.csdnimg.cn/blog_migrate/fc8f86d99831e4e0b3e30bba76142c25.jpeg)
![7cee67db5d12545d66e439db03e910a2.png](https://i-blog.csdnimg.cn/blog_migrate/c93b52099c258cdaab802045012dff63.jpeg)
这样一个带有平均值线的柱状图就做好了,然后还可以根据自己的需求进行美化。
03
说到美化,我们来以之前的折线图为例,教你们怎样以简单的步骤制作美观的图表。
先看下效果图:
![41921fa664f81a4796f785e9a3b2eede.png](https://i-blog.csdnimg.cn/blog_migrate/87fd9d0c5bc62a72debed4577e076c0b.jpeg)
在这里,我们需要用到以下功能:
❶ 【设置图表区格式】;
❷ 【设置数据系列格式】;
❸ 【设置主要网格线格式】;
❹ 【设置坐标轴格式】;
❺ 【字体颜色】。
由于这些都比较基础,就不详细说明啦,大概地讲一下需要调整的地方。
❶ 【设置图表区格式】。
主要是设置填充颜色,这里我选择了自定义颜色,RGB 值:64,64,64。
![952eb696ba6ebff8913ad94ee942d94e.png](https://i-blog.csdnimg.cn/blog_migrate/93ca04784562b084d91f06381266aa3b.jpeg)
❷ 【设置数据系列格式】。
在「填充与线条」中勾选「平滑线」,在「效果」中设置「发光」效果。
![f46fb56314e13a17c251e8128ff9d637.png](https://i-blog.csdnimg.cn/blog_migrate/ce5032becd934bbdf27ed1759788ee4c.jpeg)
❸ 【设置主要网格线格式】。
调高透明度。
![14e0aad8c91dd29399881b9b1a6f0eed.png](https://i-blog.csdnimg.cn/blog_migrate/d7f5bf70db908ccab4a4da1c7a482f9c.jpeg)
❹ 【设置坐标轴格式】。
勾选「无线条」。
![0f652beea225857c38aa5ad0ef165b4f.png](https://i-blog.csdnimg.cn/blog_migrate/6cea0f6b8c875a2fa9b3cc1481a87ee8.jpeg)
❺ 调整【字体颜色】。
这里选择「白色,背景 1,深色 15%」。
同时,修改一下「图表标题」。
![41921fa664f81a4796f785e9a3b2eede.png](https://i-blog.csdnimg.cn/blog_migrate/87fd9d0c5bc62a72debed4577e076c0b.jpeg)
一张美化好的图表就完成了!
总结一下:
在图表上添加平均值,无论是折线图,还是柱形图、条形图等,学会了技巧,都能做到!
多多练习,熟能生巧,相信你们一定能够制作出既美观,又直观的数据图表。
私信回复关键词【礼包】,获取超大办公福利礼包~
PPT、Word、Excel模板一网打尽!
![ed52faf11adc3a858f97c74d6ade27c1.png](https://i-blog.csdnimg.cn/blog_migrate/3a2da89600a3615eb911657f21d68bbf.jpeg)