实用Excel技巧分享:一张图表搞定数据对比、走势和比例贡献

轻浩君_2631

轻浩君_2631

2023-04-13

10511人浏览

转载

在日常工作中,数据对比类图表我们经常会遇到,数据走势类图表也属于我们必会的图表技巧,相信很多同学都能完成。但是最近有位同学的奇葩领导提出:“既要有对比效果、又要有走势效果、还得是多系列全年按月份来展示图表,并且这些都要做一个图里!”

所以说,我们工作中,“难”不在没技术,而在于脑洞大开的上司。那么今天就给大家一个制作“多系列对比/走势图”的思路。

【数据源】

在我们看下面的文章之前,同学们可以自己考虑一下如果这样的数据让你来设计,你会做成什么样子呢?

【正文】

●如果只有1组数据

没啥说的,柱形图、条形图、环形图,随你用就好了。

即便是需要看到百分比和走势线的话,我们在数据中的C列插入一列百分比的辅助列即可,如下:

STEP1:选择单元格A1:B4区域,工具栏插入——图表——簇状柱形图;

那么就得到了一个柱形图的初稿。

STEP2:鼠标右键点击图表区域任意位置,在弹出的菜单中,左键点选“选择数据”。

在弹出的设置框中,点击左侧的“添加”系列,如下录入数据或者选择工作表区域,以输入内容;

STEP3:再在图表区域任意位置,点击鼠标右键,选择“更改图表类型”;

将占比系列的图表类型更改为“带平滑线和数据标记的散点图”。

藉此得到了第二步骤的图表雏形。

STEP4:选中图中占比系列的图形,点击鼠标右键,在菜单中选择“设置数据系列格式”选项;

在系列选项中,点选“次坐标轴”,

得到图表,如下:

再美化一下图表界面,添加数据标签,我们就不细说了,给个示例图吧。

●再来看今天的主要数据

不得不说,在实际工作中,图表的制作还是应该有重点、有突出的去表现某个数据,如果很多信息挤在一张图中,会显得混乱。

柱形图和折线图(作者在这里用的是散点图)的组合是很常用的,而且这样的组合,既有对比又有走势,很方便观看。那么我们今天的主要数据是不是也可以做成这样的效果呢?如下图示例:

【初期制图规划】

如果我们决定使用柱形图+折线图的方式来表现这个表的话,那么我们需要在纸上,或是脑中有一个大致的草图:每一组图形对应一个项目组12个月的数据。难点就在于,每一组图形的底端要对齐;折线要在本项目图形组中出现。

【图表类型选择】

很明显,如果多个柱形图叠加起来的话,我们应该选择“堆积柱形图”,可是如果我们使用原始数据操作的话,就会是下面的图表。

各项目组的底端没有对齐,这不是我们需要的,但如果我们在每个系列中间插入一个“占位图形”,是不是就可以把各个系列图形“抬高”呢?再把占位图形的颜色和边框设置为“无颜色”,就可以模拟出每行都是柱形图,并且底端对齐的效果了,如下:

同学们可以看一下,这里我们已经把A、B点占位图形的颜色都去掉了,C点绿色的占位图形还保留着,是不是可以看出来,底端已经对齐了。

而有了占位的空位之后,我们就可以在这些空位上添加散点,用来标记占比,最后再连接这些散点,模拟折线图的效果。所以我们对于走势线,采用“带平滑线和数据标记的散点图”。

【整理数据】

我们需要在两个系列图形中间,插入一个占位图形,如果在数据表中考虑的话,就是加入一个辅助数据。下面我们来看一下数据的整理阶段。

“好图表往往都不是源数据做出来的”,善于使用辅助数据,是做图表进阶的一个重要标志,所以也有说法“图表的使用,最后都归结于函数的使用”。

上图中“红色”字,是系列的数据标签显示值,而“蓝色”字,就是图表的占位图形和散点的标记点,逐一来看下每个数据的函数设计思路:

● A:项目A的数据标签值

  • B8单元格函数:=B2

● A点占位:项目A的占位图形

  • B9单元格函数:=MAX($B$2:$M$4)*2-B8

说明:B2:M4是源数据区域,我们以数据中最大值的两倍作为图形中最高的比例参考,那么各个项目做出来的图形就会比例均匀,其原理:数据点数值+对应占位点数值=数据源中最大值*2,所以对应占位点数值=数据源中最大值*2-数据点数值

Excel公式工具(专业版)
Excel公式工具(专业版)

从自然语言描述生成Excel公式,诊断表格错误,支持VLOOKUP、条件求和等常用函数。

下载

● B:项目B的数据标签值

  • B10单元格函数:=B3

● B点占位:项目B的占位图形

  • B11单元格函数:=MAX($B$2:$M$4)*2-B10

● C点占位:项目C的占位图形

  • B12单元格函数:=B4

● C点占位:项目C的占位图形

  • B13单元格函数:=MAX($B$2:$M$4)*2-B12

● A、B、C占比:项目A、B、C的数据点,分别在本月中占所有项目数据总和的比例

  • B14单元格函数:=B2/SUM(B$2:B$4)

  • B15单元格函数:=B3/SUM(B$2:B$4)

  • B16单元格函数:=B4/SUM(B$2:B$4)

● A占比点:A项目的走势图,对应散点的坐标位置。

  • B17单元格函数:=B8+B9*1/3

说明:散点图,本身是需要横纵两个坐标点的,如果我们不设置X轴,那么默认的就是序号,如下:

为了更贴合柱形图,我们的Y轴就设置成“对应项目占位点的三分之一处”。

● B、C占比点:B、C项目的走势图,对应散点的坐标位置。

  • B18单元格函数:=SUM(B8:B10)+B11*1/3

  • B19单元格函数:=SUM(B8:B12)+B13*1/3

说明:同学们要注意,因为各个项目的散点Y轴坐标是有“高度”要求的,所以它们的数据源,是累加其他项目数据,再加上对应占比点的三分之一位置,这样比较贴近对应的柱形图。

【图表制作】

最困难的数据整理部分已经做完了,还记得我们做单组数据时是如何插入图表的吗?赶紧动手试一试吧!

蓝色区域:柱形图

选中A7:M13区域,插入“堆积柱形图”,将A、B、C系列添加“数据标签”

黄色区域:散点图

按照文章开头中对单组数据设计图表的操作步骤,重复其中的步骤2、3。

  • 选中B17:M17区域,插入“带平滑线和数据标记的散点图”

  • 选中B18:M18区域,插入“带平滑线和数据标记的散点图”

  • 选中B19:M19区域,插入“带平滑线和数据标记的散点图”

添加“数据标签”

添加散点图的“数据标签”,并选中“数据标签”,在“设置数据标签格式”中,设置为按对应“单元格中的值”显示内容:

如此这般,同学们就会得到如下的图表“毛坯”。

【后期整理】

后期整理工作可以参考作者E图表述的“一删、二调、三排”的做法来处理。

1、删除图表中的“纵坐标轴”和“刻度线”,以及不需要显示的图例;

2、调整柱形图的颜色和平滑线的颜色,将不需显示的占位柱形以及平滑线上的标记点设置为无线条、无填充。

3、通过上述操作,图表已经基本成型了,选中柱形图的某一个系列,调整间隙宽度为5%,

将柱形图上的数据标签位置调整为轴内侧,平滑线上的数据标签位置调整为居中;

再调整下图表中各个元素的位置,并更改字体、字体颜色等,就完成了我们上图的效果。

【编后语】

对于图表的制作建议大家:所有Excel的默认效果都不要使用。图表更像一个平面设计,每个人的审美不同,出来的效果也不同,但是“可视化”既然是要给人看的,就要讲究美观。最后分享今天图文教程中,所使用的这一组配色方案,在“其他颜色——自定义”中输入RGB值即可。

相关学习推荐:excel教程

相关专题

更多
堆和栈的区别
堆和栈的区别

堆和栈的区别:1、内存分配方式不同;2、大小不同;3、数据访问方式不同;4、数据的生命周期。本专题为大家提供堆和栈的区别的相关的文章、下载、课程内容,供大家免费下载体验。

2023.07.18

7227

5

堆和栈区别
堆和栈区别

堆(Heap)和栈(Stack)是计算机中两种常见的内存分配机制。它们在内存管理的方式、分配方式以及使用场景上有很大的区别。本文将详细介绍堆和栈的特点、区别以及各自的使用场景。php中文网给大家带来了相关的教程以及文章欢迎大家前来学习阅读。

2023.08.10

2948

6

excel对比两列数据异同
excel对比两列数据异同

Excel作为数据的小型载体,在日常工作中经常会遇到需要核对两列数据的情况,本专题为大家提供excel对比两列数据异同相关的文章,大家可以免费体验。

2023.07.25

6281

7

excel重复项筛选标色
excel重复项筛选标色

excel的重复项筛选标色功能使我们能够快速找到和处理数据中的重复值。本专题为大家提供excel重复项筛选标色的相关的文章、下载、课程内容,供大家免费下载体验。

2023.07.31

3996

5

excel复制表格怎么复制出来和原来一样大
excel复制表格怎么复制出来和原来一样大

本专题为大家带来excel复制表格怎么复制出来和原来一样大相关文章,帮助大家解决问题。

2023.08.02

3780

3

excel表格斜线一分为二
excel表格斜线一分为二

在Excel表格中,我们可以使用斜线将单元格一分为二。本专题为大家带来excel表格斜线一分为二怎么弄的相关文章,希望可以帮到大家。

2023.08.02

1444

3

excel斜线表头一分为二
excel斜线表头一分为二

excel斜线表头一分为二的方法有使用合并单元格功能方法、使用文本框功能方法、使用自定义格式方法。本专题为大家提供excel斜线表头一分为二相关的各种文章、以及下载和课程。

2023.08.02

737

5

绝对引用的输入方法
绝对引用的输入方法

绝对引用允许在公式中引用一个固定的单元格,而不会随着公式的复制和粘贴而改变引用的单元格。本专题为大家提供绝对引用相关内容的文章,大家可以免费体验。

2023.08.09

5168

7

java导出excel
java导出excel

在Java中,我们可以使用Apache POI库来导出Excel文件。本专题提供java导出excel的相关文章,大家可以免费体验。

2023.08.18

8323

6

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
Excel 教程
Excel 教程

共162课时 | 45.9万人学习

成为PHP架构师-自制PHP框架
成为PHP架构师-自制PHP框架

共28课时 | 3.7万人学习