2020年最牛，Excel 技能，100+图解+详细教程+免费拿走

2020-03-23 14:13·华仔的新知识

因为工作中离不开 Excel，经常使用，掌握的技能就不会生疏，而是越发触类旁通，稳步提升。

所以，如果在可见的未来，你的工作中会用到 Excel，我建议你集中一段时间花上 100～150 个小时玩一玩 Excel，乐趣无穷。

我也知道这回答长了点，所以为节约大家的时间，我选取了一个经典案例，辅以详细的说明（中文版 Excel 2013）放在开头，供各位参考。

看完这个案例之后，我们也可以尝试绘制这种商务风格的表格。

下图是我做的，看完详细的绘制步骤，你将深刻体会到“用 Excel 的元素辅助作图”的含义。

（编者注：想要在电脑上边阅读此答案边练习，可以分享到电子邮件发给自己，或分享到微信或微博和稍后阅读类 App，方便在电脑上查阅）

首先选中源数据，A 到 F 列：

绘制散点图，得到经典的 Excel 风格图表：

将利润率设为次坐标：选中橙色那根线，右键 - 设置数据系列格式 - 次坐标轴

删去图表标题、图例，调节横坐标、两个纵坐标的上下限，删去纵网格线，删去两个纵坐标的轴线，得到这样一张图：

下一步称为“锚定”，鼠标光标移动到下图所示的图表左上角的顶点处，按住 Alt，随后按住鼠标进行拖动，发现这样调节图表的尺寸，可以仅限定于 Excel 的网格点。

四个角都这样进行调节，分别“锚定”于 N7, V7, N15, V15：

选中图表区域，右键 - 设置图表区域格式，在属性中选择“大小固定，位置随单元格而变”，这样，在调整 Excel 行距和列宽时，图表就不会随之而动。

在第 4~6 行输入内容，设置填充色：

调节 7~15 行行距，使得 Excel 网格线与我们做的图表的横向网格线一一重叠；

调节 O 列和 U 列列宽，使得 O 列左侧网格线恰好经过图表横向网格线的起点，U 列右侧这根网格线也是一样的道理，如下图所示。

选中图表区，填充色改为无色，外轮廓也删去，这样图表就变成“透明”的了

随后对 N7:V15 这个区域的单元格进行填充色。

（选中这些单元格的方法：先选中图表区域外的一个单元格，如 M7，按键盘的 → 键，移动到 N7，然后按住 Shift，再按 → 键或 ↓ 键调节即可，选中后进行单元格填充。）

在 Excel “视图”中取消勾选网格线：

最后添加一些图例即可：

怎么样？相信你已体会到了如何将 Excel 的元素融入图表设计中。

= 原回答 =

我曾在大三寒假闭关三周，自学 Excel, PowerPoint 和 Word，一年后又花了一个月的时间研习 VBA。楼上关于函数和操作技巧已经分享很多了，我在这分享一些图表设计的技巧。

一流的 Excel 函数和 VBA 水平，自然要以一流的图表设计呈现出来。

我相信看完这个回答后，你再也不会将图做成这样。

好奇商业杂志上的这些高端大气的图是用什么特殊软件做出来的吗？

答案就是 Excel。

滑珠图、子弹图、瀑布图……一切都可以用 Excel 最基本的操作搞定。

我会先介绍一些设计的核心理念和方法，然后列举 16 个“商务范”图表制作实例，包含详细的制作步骤，最后分享一些配色方案。

目录

一、商务图表制作核心理念和方法

  1. 突破 Excel 的图表元素
  2. 突破 Excel 的图表类型
  3. 布局与细节

二、“商务范”图表制作实例

  1. 柱状图横坐标以时间间隔比例分布
  2. 簇状和堆积柱状图合用（Clustered & Stacked Column）
  3. 漏斗图 - 利用辅助列占位
  4. 自定义 Y 轴刻度间距
  5. 含加粗边缘的面积图
  6. 横网格线覆盖于 Area 图之上
  7. 为 Pie 图加背景图片
  8. 仪表盘
  9. 多数量级的几组数据同时比较
  10. 手风琴式折叠 bar 图
  11. Water Fall 瀑布图
  12. 不等宽柱形图
  13. 滑珠图
  14. 动态图表 1
  15. 动态图表 2
  16. Bullet 图 - 竖直

三、配色方案

  1. Nordri 设计公司分享的配色方案
  2. ExcelPro 分享的方案

四、自学参考书目和资料

= 正文 =

一、商务图表制作核心理念和方法

（这一章节的笔记整理自刘万祥老师的博客 ExcelPro 的图表博客）

1. 突破 Excel 的图表元素

不要仅用“图表”去做图表，而是用“图表＋所有 Excel 元素（如单元格）”去做图表。

左上图，只有 B4 单元格是图表区域，标题利用的是 B2；B3－B5 填充浅色，"index"和"data"分别在 B3、B5。

右上图，B2 为图表序号，C2 为图表标题，填深绿色，B3 为副标题，图例放在 C4，图表在 C5，B2 到 C5 填充淡色，B6、C6 合并填写注释。

　　左上图，标题在 C2－H2 居中，图表在 C3－H3，表格在 C6－H8。

　　右上图，B2 填红色装饰，标题和副标题分别在 B2、B3，图表在 D4－F4，数据来源在 D5，标号 2 为矩形框，整个区域有边框。

　　经常用来配合图表制作的其他 excel 元素主要有单元格、文本框、线条、箭头等。

  • 单元格 DIY 图表标

上面四例都是

  • 单元格填色替代图表区和绘图区填色

将图表区和绘图区设置无色透明，将单元格设置相应的填充色（在我开头举的案例中有详尽的说明）

  • 单元格控制图表的大小和排列

将图表对齐在某个单元格（锚定，上面有说明），则可以通过调整单元格的列宽行高，方便地控制图表的大小。

选中图表区域，右键 - 设置图表区域格式，在属性中设置图表是否随单元格变而变。

当多个图表都锚定在某一列 / 行的单元格上时，就可精确实现图表的对齐，更可方便的批量快速的调整图表大小。

  • 单元格表格 DIY 数据表

上面第三例

  • 矩形框或线条绘图对象来 DIY 图例

2. 突破 Excel 的图表类型

左上图，先用所有数据做曲线图或柱形图，然后选中相应的序列，更改图表类型，有时还需要用到次坐标轴。

右上图，先做好面积图，然后将该数据序列再次加入图表，修改新序列的图表类型为曲线图，调粗线型。

3. 布局与细节

  • 布局

下图从上到下可以分为 5 个部分：主标题区、副标题区、图例图、绘图区、脚注区。

特点有：完整的图表要素；突出的标题区；从上到下的阅读顺序。

标题区非常突出，占到整个图表面积 1/3 以上，其中主标题用大号字和强烈对比效果，副标题提供详细信息。

  • 竖向构图方式

整个图表外围高宽比例在 2:1 到 1:1 之间，图例一般在绘图区上部或融入绘图区里面

  • 使用更为简洁醒目的字体

商业图表多选用无衬线类字体

图表和表格的数字中使用 Arial 字体、8~10 磅大小，中文使用黑体

  • 注意图表的细节处理

    • 1. 脚注区写上数据来源
    • 2. 图标注释：对于图表中需要特别说明的地方，如指标解释、数据口径、异常数据等，使用上标或 * 等进行标记，在脚注区说明
    • 3. 坐标轴截断标识
    • 4. 四舍五入：在脚注区写明：由于四舍五入，各数据之和可能不等于总额(或 100%)
    • 5. 简洁的坐标轴标签：如 2003、’04、’05
    • 6. 让 Line 图从 y 轴开始：双击 x 轴，Axis Options- 最下 -Position Axis-on tick marks
    • 7. 作图数据的组织技巧: 原始数据不等于作图数据；作图前先数据排序；将数据分离为多个序列，每个序列单独格式化
    • 8. 其他：去除绘图区的外框线，去除纵坐标轴的线条色，将网格线使用淡灰色予以弱化，bar 间距小于 bar 宽度，饼图分块用的白色线

二、“商务范”图表制作实例

（这一章节的 16 个案例均出自刘万祥老师的Excel 图表之道 (豆瓣)，该书基于 Excel2003）

这一部分摘自我的笔记，当时用的是 2010 版本，虽说 2013 版本具体的操作方法变掉了，但制作的步骤是没变的。也就是说，如果你使用的是其他版本，“【】”里的操作方法会不同，但“【”前面的步骤和思路是没有问题的。

但对于新手来说可能参照起来有些困难，所以，如果有兴趣，我建议你阅读刘老师的这本深入浅出的书。集中时间和力量搞定公式、初步 VBA、图表制作，受益是终生的。因为工作离不开 Excel，所以大多技巧只要掌握了，就不会忘。

仪表盘、滑珠图、子弹图、瀑布图、动态图表我有自作的模板。需要的请留下邮箱。

1. 柱状图横坐标以时间间隔比例分布

例如要展示利率（Y 轴）随时间（X 轴）的变化，通常情况下做出的是左下所示的样子，横坐标均匀分布，但如何做出右下图这样的以时间间隔比例分布的图呢？

原始数据与辅助列

绘制方法

A2:B5 做 Column 图（左下）发现横坐标不是希望的 3、6、12、24

将横坐标转化成右下的样子 【选中图表-Chart Tools-Design-Select Data-Horizontal Axis Label-Edit-A2:A5】

将横坐标转化为 Date Axis（左下）【双击横坐标- Axis Options-Axis Type-Date Axis】 然后删去横坐标；

选中 C2:C5 添加进图表【Ctrl+C Ctrl+V】

（这里提示一下，很多地方用到了“将数据添加进已有图表”这一操作。操作方法是：选中需要添加的数据，Ctrl+C，然后选中图表，Ctrl+V）

将其转换为 Line 图【选中图表 - Chart Tools - Format - 最左侧 - Series2】【Chart Tools - Design - Change Chart Type - Line】

显示 Line 图标签【选中红线 - Chart Tools - Layout - Data Labels - Below 】单独修改这些 0，看起来正是 Column 图标签，选中单个标签进行修改即可。

2. 簇状和堆积柱状图合用（Clustered & Stacked Column）

源数据

最终效果

绘制方法

利用错行和空行

选中这些数据，直接做成 Stacked Column

3. 漏斗图 - 利用辅助列占位

最终效果与源数据

绘制方法

添加辅助序列

确认前提是“指标列”从大到小排列

假设这个值在 D3 单元格，在 C3 中输入公式 =($D$3-D3)/2 然后拉到底。

选中<公司 1="">这个单元格到这个单元格区域，做 Stacked Bar 图

反转纵坐标【双击纵轴 - Axis Options -Category in reverse order】

将红色 Bar 隐藏【右击红色bar-Format Data Series-Fill-No Fill】

4. 自定义 Y轴刻度间距

以股价随时间变化为例，重要的是涨跌幅度，且幅度很大，我们希望 Y 轴间距可以自己设定，这里我们实现对数坐标。

最终效果与源数据

绘制方法

上图，C 列为 B 列对数 =log10(Number) ， F 列为我们希望的 Y 轴刻度，G 列为 F 列取对数。即 F 列是 B 列的刻度，而 G 列是 F 列的刻度

先用 C2:C12 列 Line 图（下左）

加入 G2:G7（下中）【Ctrl+C Ctrl+V】

将红色 Line 改为 Scatter 图（下右）【选中红色 Line - Chart Tools - Design - Change Chart Type - Scatter】

更改红线横纵轴分别为 E2:E7、G2:G7【选中图表 - Chart Tools - Design - Select data - Series2 - Edit】

让 Y 轴从 1 开始【双击纵轴 - Axis Options - Minimum设置】并删去网线，删去纵轴

让曲线与 Y 轴相交（左下）【双击横轴 - Axis Options - 最下 Position Axis - On tick Marks】

采用误差线的方法添加横网线（右下）

【选中红线 - Chart Tools - Layout-Error Bars - 最下 More Error Bars Options - Close】此时图表中出现横纵误差线

在图表选中竖向误差线，删除

设置横向误差线【右击横向误差线 - Format Error Bars - Horizontal Error Bars - Direction - Plus - Error Amount - Fixed Value - 10】

将红点变小【右击红线 - Format Data Series - Marker Options 】

为红点标上数据【选中红线 - Chart Tools - Layout - Data Labels - Left】

依次选中各个标签，更改为 Y 列数值

5. 含加粗边缘面积图

最终效果与源数据

绘制方法

选中数据做 Line 图（左下）

再将数据添入图表中【Ctrl+C Ctrl+V】（右下）

更改红线为 Area 图【选中红线 - Chart Tools - Design - Change Chart Type - Area】

去除 Area 图 Border（否则无法自由修改 Line 颜色）【右击红色 Area - Format Data Series - Border Color - No Line】

设置两者颜色

让 Area 图从 Y 轴开始【双击横轴 - Axis Options - 最下 - Position Axis - On Tick Marks】

注意：最开始要做两个 line 图，而不能两个 area 图

下面这个图是我做的~

6. 横网格线覆盖于 Area 图之上

最终效果


第二张图 Chart area、Plot Area 及柱子设为 No fill （即只保留 Gridline）

将第一张图的 Gridline 删去

按住 Alt 移动第二张图覆盖于第一张图之上

可选中 Gridline 右击，在 Format Gridline 里改其颜色、粗细

7. 为 Pie 图加背景图片

最终效果与原始数据

绘制方法

先用 A1:A5 做 Pie 图，得到下图，此系列数据为 Series1

选中任意一个单元格（如 A3）添入【Ctrl+C - Paste - Paste Special - New Series】

此时无法看到也无法选择该 Series （为 Series2），看到的仍然是上图的样子

将 Series1 改为次坐标轴【选中大饼右键 - Format Data Series - Series Options - Plot Series on - Secondary Axis】

（此时在 Chart Tools - Format 里就可选择 Series1 或 Series2）

将 Series1 设为无填充【右击大饼 - Chart Tools - Format data Series - Fill - No Fill】

（此时看到的正是 Series2，如下图）

将 Series2 填充自定义图片【选中图表 - Chart Tools - Format - 最左边 - 下拉框 Series2 - Format Selection - Fill - Picture】

将 Series1 切割处改为白色【选中图表 - Chart Tools - Format - 最左边 -下拉框 Series1 - Format Selection - Border Color】

// 另一个办法 //

在 PPT 里画一个圆形，Shape Fill 一个图片，去掉 Outline，得到一个圆形图片，另存。直接画 Pie 图，在 Plot Area （下图所示，正方形和 Pie 图直接的白色区域）右击 Format Plot Area - Fill - Picture 即可

8. 仪表盘

最终效果

（在某个单元格中输入数值（0-100），红色的指针会随之而动）

绘制方法

E2 = D2/D4*270 E4 = 360-E2 用 E2:E4 作图，得指针图（Series1，左下）

将 A2:A24 添入【Ctrl+C -选中图 - Paste Special - New Series】得到仪表图（Series2，右下，目前看不到）

将 Series1 转为次坐标轴并爆炸、旋转【选中右击 - Foramt data series - Series Options - Plot Series on - Secondary Axis -Pie explosion - 20 - Angle of first Slice - 225】

先鼠标拖动红色的尖到圆心，然后将蓝色和绿色的拖到中心

蓝色、绿色 slice 设为白色，红色 slice 换为好看的红色【单独选中右击 - Format Data Point - Fill - Solid Fill】

E3 单元格调节为 2

将 Series2 旋转、变颜色【Format data series - Series Options - Angle of first Slice - 225 - Fill - Solid fill - White - Border Color - Border line - Black】

加刻度【选中 Series2 - Layout - Labels - Data labels - outside end】

将 27 删除，0 依次改为 10、20……

仪表盘上加显示器【插入单元格，选中单元格，在公式编辑栏输入 =$D$2回车】

随后可调节颜色

选中仪表图（Series2），在公式编辑栏末尾单击一下，按 F9，回车。图表便不再依赖于 A 列数据。

9. 多数量级的几组数据同时比较

最终效果

原始数据与处理数据

绘制方法

F3 单元格 =B3/MAX($B$3:$B$8)*0.8 即将用户数最大值基准转化为 0.8

G3 单元格=1-F3 为占位列

后几列公式类似

F3:J8 作 Stacked Bar 图（左下）

红色、紫色为占位列，设为白色（右下）【选中右击 - Format Data Series - Fill】

（如果基准为 1 则最大值会和下一条接壤）

10. 手风琴式折叠 bar 图

最终效果

（突出前三个和后三个）

原始数据（左下）和作图数据（右下）

第一列 ：前三、空格、后三单元格数做成 1:1:1

第二列：中间的数据若有 6 个，则前后各留 5 个

两列首行要对齐

绘制方法

以作图数据第一列做 Stacked Bar 图，第二列复制粘贴进去（红色，左下图）

将红条改为次坐标轴（右下图）【选中红色右击 - Format Data Series - Plot Data on - Secondary Axis】

注意上下两个轴，一个是 Primary axis 一个是 Secondary axis，将 Maximum 调为一样

将次坐标轴在右边显示出来【选中图表 - Layout - Axes - Secondary Vertical Axis - Show right to left Axis】

将左边主坐标轴反转【双击 - Axis Options - Category in reverse order】

删去横竖两个 Secondary Axis

11. Water Fall 瀑布图

最终效果

绘制方法

D4 =SUM($B$3:B4) F4 =IF(B4<0,d4,d3) g4="IF(B4">=0,B4,0)

H4 =IF(B4>=0,0,ABS(B4))

用蓝色框线区域作图。如果要添加横线。可将“累计”列贴入到图表，改为 Scatter 图，添加 Error Bars

12. 不等宽柱形图

最终效果与原始数据

（高度反映 ARPU 值，宽度反映用户规模）

绘制方法

// 分组细分法 -Column 图 //

处理数据如下。每个 ARPU 数据重复次数为“用户规模”（柱子宽度）数

第二~五列作图 （左下） 修改宽度和间距【选中右击 - Format data series - Series Options - Series Overlap - 100 - Gap Width - 0】

// 时间刻度法 -Area 图 //

原始数据依旧（左下图）

作图数据（右下图）

第一列最大值 20（8+4+2+6）。第一列的数据对应于“用户规模”（每个柱子宽度）。除 0 和 20 外 ，其他每个数据固定为 3 个（无论如何都是 3 个）

注意其他几列数字位置

以第一~五列作 Area 图

横轴改为需要的【选中图表 - Chart Tools - Design - Select Data - Horizontal 改为 A9:A19】

删去图中无关的深蓝色图形

横轴改为时间刻度（左下 ）【选中横坐标右击 - Format Axis - Axis Type - Date Axis】

柱子外框设为白色，粗一点（右下）

13. 滑珠图

最终效果 （右图是我仿照原图画的）

蓝色奥巴马支持率，红色麦凯恩支持率。纵坐标为不同人群

两种滑珠为 Scatter 图，横梁为 Bar 图

绘制方法

数据（左下） E 列为 Scatter 图 Y 轴数据。以 A、D 列作 Clustered Bar 图，纵轴 Category in reverse order

贴入 B 列，改为 Scatter 图（左下），选中红点，Select data-X 轴改为 B 列，Y 轴改为 E 列

同样方法添加 C 列并处理

14. 动态图表 1

File - Options - Customize Ribbon - 右边框内勾选 Developer 这样面板就有 Developer 栏，单击 Developer - Controls - Insert - 第一排第五个List Box 添加到工作表中

右击该 List Box， Format Control-Input range $B$8:$B$13

Cell link $B$5

B5 就会显示在 List Box 里选择了第几个数值

B3 单元格 =INDEX(B8:B13,$B$5) 横向拉到 N3

这样当在 List Box 里选择时，B5 单元格显示选择结果，B3:N3 就会跟着显示选择结果对应各月的数值

以 B3:N3 作图

15. 动态图表 2

以下图为例。B5 设置 Data Validation 只可选择 07 年、08 年或 09 年。

B7 单元格 =CHOOSE(IF(B5="08 年",2,IF(B5="07 年",1,3)),1,2,3)

B8 单元格 =INDEX(B1:B3,$B$7) 拉到 F8

先以 B1:F3 作 Line 图，选择 B8:F8 Ctrl+C Ctrl+V 到图表中即可

16. Bullet 图 -竖直

最终效果 与 原始数据

绘制方法

以 A2:F6 做 Stacked Column 图（左下），Swith Row/Column（右下）

更改最下蓝色柱子（实际）为次坐标轴并变窄（左下）【右击蓝柱 - Format Data Series - Series Options - Secondary Axis - Gap With - 300】

更改最下红色柱子（目标）为次坐标轴（右下）【右击红柱 - Format Data Series - Series Options - Secondary Axis】

更改红柱为 Line 图【选中红柱 - Chart Tools - Design - Change Chart Type】

去掉红色连线并将方块改为红短线【选中红线右击 - Format Data Series - Line Color - No Line - Marker Option - 横线】

然后设置其他颜色等

三、配色方案

配色主题设置方法 （以 Excel 2013 做示范，其他版本大同小异）

Step1. <页面布局 -="">

Step2. 总共 12 个颜色可自定义，单击任意一个颜色下拉菜单，选择“其他颜色”，输入 RGB 值，全部完后命名，保存即可。这样，在<页面布局 -="">下拉菜单中就可以选择自定义的主题。

以下每个配色方案都提供了这 12 种颜色的 RGB 值

1. Nordri 设计公司分享的配色方案

Nordri 商业演示设计

1 - 碧海蓝天

2 - 达芬奇的左手

3 - 老男孩也有春天

4 - 路人甲的秘密

5 - 旅人的脚步

6 - 那拉提草原的天空

7 - 香柠青草

8 - 热季风

9 - 软件人生

10 - 商务素雅

11 - 商务现代

12 - 数据时代

13 - 素食主义

14 - 岁月经典红

15 - 夏日嬷嬷茶

16 - 邮递员的假期

17 - 毡房里的夏天夏天

2. ExcelPro 分享的方案

函数、VBA、图表是 Excel 的三把利器，希望这篇关于图表的回答能给大家一些启迪。但我最擅长的其实还是前两者。在这我就不放函数和 VBA 的笔记了。因为，我相信，要想熟练掌握函数与 VBA，最好的办法还是借一本入门书，夯实基础，循序渐进。今天这里学一招，明天那里记住一个公式，会只见树木不见森林。

获取更多的教程请；



举报
评论 0
    热门：