EXCEL-2016图表达人(增强版).pdf

1、 图表达人 Excel 2016 EXCEL 2016 图表 达人 悟君居士 著 北京 BEIJING 前言 本书中的所有内容，均在 Excel 2016 版本中完成，大部分功能在 Excel 97Excel 2013所有版本中也是可以实现的，只是在不同版本中操作可能有所不同。建议使用 Excel 2013 或Excel 2016 进行操作。 事实上，本书所介绍的重点并不是高深的图表处理技巧，而是各种图表的设计方法和制作思路，因此，对于版本的依赖性并不大。只要掌握了书中的制作思路和方法，可以在不同Excel 版本之间完成同样的图表。 从难度而言， Excel 本身是一款操作型办公软件，虽然入门

2、比较容易，但是精通甚难。本书的最大特点在于用最常见的操作方式来完成一些看似复杂的图表，总体难度为中等水平。因此，只要具备一定的 Excel 基础知识的人，均可以学会本书内容。 由于各章节之间相对独立，因此，可以不必按照书中所讲的顺序学习，可以根据读者自己的兴趣随机学习 。本书仅作为作者抛砖引玉之用，为读者打开思路，希望读者能够在此基础上创造出更多 Excel 的神思妙用。 同时 ，由于本人审美水平有限，因此书中的 图表 仅追求实现方法，至于美化工作留待各位读者自行发挥。 尽管本人已尽力确保本书的准确性，但因知识和能力有限，书中难免存在纰漏之处，届时恳请各位朋友不吝批评指正。如果您需要指正书中的

3、错误或者需要额外支持，请发邮件到该邮箱： 如果你需要更多关于 Excel 的学习资源与资料，请到 作者 博客自行下载，博客地址为：http:/ 致谢 本书 中所有的图表均 为 作者日常工作中 学习 笔记，图表来源于各大网站、论坛、贴吧网络渠道， 也 咨询了 很多 Excel 爱好者，感谢散落于云海 、 姜辉、海涛、一图等， 在 与他们的交流过程中， 我学 到了很多 做 图 表 的知识和技术， 也非常感谢 李浩、高晓东 、 张厦等朋友，在 他们的 支持 和鼓励下，我 才 坚持完成 本书 。最后 ， 感谢 Excel 让我 认识了那么多朋友，让我找到自己的 兴趣 爱好，也让我 在 工作中得到了一

4、定的价值体现。 悟君居士 2016 年 4月 目录 前言 2 致谢 3 第 1 章 漂亮的动态图表组合 7 1.1 图表组合是未来的趋势 7 1.2 欲善其事，先利其器 10 第 2 章 常见图表的绘制技巧 11 2.1 棋盘图 11 2.2 方块图 13 2.3 半圆图 16 2.4 粗边面积图 18 2.5 帕累托图 20 2.6 漏斗图 23 2.7 柱形趋势图 25 2.8 T 线图 28 2.9 3D 魔方图 . 30 2.10 滑珠图 34 2.11 子弹图 36 2.12 温度计图 38 2.13 标签图 40 2.14 标签对齐图 42 2.15 双侧图 . 44 2.16 垂

5、直参考线图 46 2.17 水平参考线图 48 2.18 甘特图 50 2.19 瀑布图 52 2.20 圆环图 54 2.21 玫瑰图 57 2.22 注水图 60 2.23 双柱图 63 2.24 双色线图 66 2.25 风车图 69 2.26 波士顿矩阵图 . 72 2.27 总分柱状图 75 2.28 阶梯图 77 2.29 树状图 79 2.30 不等宽柱形图 . 81 2.31 旋风图 85 2.32 断层图 88 第 3 章 信息图表绘制技巧 93 3.1 信息图表的构成 . 93 3.2 温度计图 94 3.3 满意度图 98 3.4 圆形仪表盘图 100 3.5 半圆仪表盘

6、图 103 3.6 评级图 106 3.7 交通信号灯图 108 3.8 环形文本图 113 3.9 饼型文本图 115 3.10 人物填充图 118 第 4 章 动态图表绘制技巧 121 4.1 动态图表的实现方法 . 121 4.2 随机生成数法 122 4.21 双色球生成器 . 122 4.22 模拟股价图 . 124 4.3 控件法 126 4.31 动态柱形图 组合框 126 4.32 动态折线图 滚动条 129 4.33 动态圆环图 选项按钮 133 4.34 动态簇状柱 形图 复选框 135 4.35 双控柱形图 选项按钮 +组合框 . 137 4.3.6 动态环柱图 选项按钮

7、 . 140 4.4 切片器法 142 第 5 章 如何制作迷你图 144 5.1 迷你图简介 144 5.2 常见的迷你图制作方法 . 145 5.2.1 迷你折线图 145 5.2.2 迷你柱形图 147 5.2.3 迷你盈亏图 149 5.2.4 双折线迷你图 151 5.2.5 迷你子弹图 153 5.2.6 迷你帕累托图 155 5.2.7 迷你瀑布图 157 5.2.8 迷你散点图 159 5.2.9 迷你堆积图 161 5.2.10 迷你方差图 163 5.2.11 迷你面积图 165 5.2.12 迷你饼形图 167 5.2.13 迷你环形图 169 第 6 章 简单仪表盘的制

8、作 170 6.1 仪表盘的初步认识 . 170 6.2 仪表盘的设计思路 . 171 6.3 多图仪表盘设计 案例 1 174 6.4 商业气息的仪表盘 案例 2 176 6.5 动态仪表盘设 计 案例 3 . 179 附录 A 181 A.1 练习文件下载 . 181 A.2 Excel 相关插件推荐 182 A.3 Excel 相关博客和网站 183 A.4 Excel 图 表模仿网站 184 附录 B . 185 B.1 Excel 单机游戏推荐 185 B.2 Excel 其他运用 . 188 参考文献 189 关于作者 190 第 1章 漂亮 的动态图表组合 1.1图表 组 合是

9、未来的趋势 随着大数据 与信息可视化的不断发展，我认为未来图表的发展趋势分为两种，一是以Dashboard 报告形式组合出现，即商业仪表盘的形式；二是信息图表的方式出现 ， 即类似于各大媒体、咨询公司发布的信息图表报告。然而 前者 主要通过 Excel、 Tableau或其 他 商业智能工具将 复杂 的数据可视化，后者则通过 PS、 AI 和 PPT 等工具进行可视化设计。 下面介绍几张图表组合正 是 由 Excel 制作出来的 。 第一张 图表是一个股票诊断报告的商业仪表盘， 这份 Dashboard 上展示了多种图表的组合 ， 有折线图、漏斗图、滑珠图、温度计图、仪表盘等等。 第二张 图

10、是 一个个股数据分析的商业仪表盘， 这个 Dashboard 整体所用的图表类型较少，但是整体的布局以及颜色搭配十分优雅，但此图有一点缺陷就是左上角的两个带有大数字的单元格 没有 加盖白色遮罩效果。 第三张图 是关于某银行各个机构的业务 情况 一览仪表盘， 这张 仪表盘虽然用的图表类型不多，但是这张图表利用了控件，因此整个仪表盘均可以呈现动态变化，即动态的商业仪表盘。 1.2欲善其事 ，先利其器 正所谓 “工欲善其事 ， 必先利其器 “。要制 作 上述类型的图表组合，首先 ， 我们需要学会的 是 制作其中的各种单个图表 、 信息图表以及动态图表。当 掌握 的图表类型足够多的时候，再学习一下商业

11、仪表盘的布局设计，同时结合相关业务，就可以制作出 让人眼前 一 亮 的商业仪表盘了。 因此 ，在下面的章节，本书会分别为大家介绍各种图表的制作技巧、信息图表的制作技巧以及如何制作动态图表，让 读者 把制作商业仪表 所需要的 “术 ”图表制作技巧 充分 掌握好，以后再介绍制作商业仪表板的 “道 ”。 第 2章 常见 图表的绘制技巧 2.1 棋盘图 【特点 】 64个 格子黑背相间。 【原理 】 1.构造 函数进行判断 ； 2.利用条件格式 ； 3.隐藏 数据。 【制作 技巧】 Step1： 输入 公式 。 首先，打开一个工作簿，在任意一个 Sheet 里选中 A1： H8 单元格，然后输入如下公

12、式 =ISEVEN（ ROW()+COLUMN()），然后按 Ctrl+Enter，即可得到如下图所示结果。 Step2：设置 条件格式。 再次选中 A1： H8 单元格，开始 条件格式 新建规则 使用公式确定要设置格式的单元格，然后再次输入公式 =ISEVEN（ ROW()+COLUMN()） ，然后将格式 填充 黑色，设置情况如下图： Step3： 隐藏数据 。 点击确定后，结果是 False和黑色填充单元格的交替形式，此时，选中 A1： H8 单元格，再按 Ctrl+1进行单元格设置，选择自定义格式，将 “G/通用格式 ”改为 “;”,然后 Enter，即可得到黑白相间的棋盘了，最后选中

13、 1:8 行，将行高设置为 40，最终结果如下图。 2.2 方块 图 【特点 】 带色 方块随着数字 变化 【原理 】 1.设置 框架和数值； 2.利用 条件格式； 3.隐藏数据 ； 4.按 F9动态 变化 【制作技巧 】 Step1： 制作框架。 选中 B6： K15 单元格，共 10 行 10 列，将列宽改为 4，行高改为 20，同时设置颜色背景为金色，个性色 4，淡色 60%（主题颜色第三排右边第三列），填充白色的外边框和内框，最终结果如下图。 Step2：填充数值。 在 B15 单元格输入 1%，然后在 C15 单元格输入公式 =B15+$B$15，然后向右拖至 K15 单元格，继续在

14、 B13 单元格输入公式 =K15+$B$15，然后依次拖至 B6 单元格，最后选中 C15： K15 单元格向上拖至 C6： K6 单元格，从而最终生成 1%至 100%的百分数。 Step：设置条件格式。 选中 B16： K16 单元格，合并后居中，并在合并后的单元格中输入如下公式 =RANDBETWEEN（ 1,100） /100，最后设置为百分比格式。再次选中 B6： K15 单元格，在开始菜单中找到条件格式，并新建规则，选择使用公式确定要 设置格式的单元格，输入如下公式 =B6=$B$16，然后在格式里设置填充色第四行右边第三列颜色，点击确定后，结果如下图。 Step4： 隐藏数值。

15、 再次选中 B6： K15 单元格，然后按 Ctrl+1调出设置单元格格式的工具栏，进行自定义设置，将 0%改为 ;（三个英文状态下的分号），确定后，则所有数值均不显示，结果如下图。 91% 92% 93% 94% 95% 96% 97% 98% 99% 100%81% 82% 83% 84% 85% 86% 87% 88% 89% 90%71% 72% 73% 74% 75% 76% 77% 78% 79% 80%61% 62% 63% 64% 65% 66% 67% 68% 69% 70%51% 52% 53% 54% 55% 56% 57% 58% 59% 60%41% 42% 43%

16、44% 45% 46% 47% 48% 49% 50%31% 32% 33% 34% 35% 36% 37% 38% 39% 40%21% 22% 23% 24% 25% 26% 27% 28% 29% 30%11% 12% 13% 14% 15% 16% 17% 18% 19% 20%1% 2% 3% 4% 5% 6% 7% 8% 9% 10%91% 92% 93% 94% 95% 96% 97% 98% 99% 100%81% 82% 83% 84% 85% 86% 87% 88% 89% 90%71% 72% 73% 74% 75% 76% 77% 78% 79% 80%61% 62%

17、63% 64% 65% 66% 67% 68% 69% 70%51% 52% 53% 54% 55% 56% 57% 58% 59% 60%41% 42% 43% 44% 45% 46% 47% 48% 49% 50%31% 32% 33% 34% 35% 36% 37% 38% 39% 40%21% 22% 23% 24% 25% 26% 27% 28% 29% 30%11% 12% 13% 14% 15% 16% 17% 18% 19% 20%1% 2% 3% 4% 5% 6% 7% 8% 9% 10%83%Step5： 图表美化。 将 B6 单元格进行相应着色，同时将 B5:K5 单元格

18、合并，填充相应主题和背景色，此时整个图表就完成了，按 F9键可以发现整个图表的动态变化。 83%93%我究竟有多帅2.3 半圆图 【特点 】 整圆 变成半圆图 【原理 】 1.构造辅助数据 ； 2.无色填充 ； 3.利用照相机 【制作技巧 】 Step1： 准备数据。 在 A1： B6 单元格 里输入存款类型和金额两列数据，具体数据如下图所示： Step2： 插入 饼图。 选中 A1： B6 单元格 插入 饼图 ，同时将 图表 标题和 图例 删掉， 然后 选中图表 右键 设置 数据 系列格式 ， 在 系列 选项里，将 第一 扇区起始角度（ A） 改为 270 度 ，结果如图。 Step3： 美

19、化图表。 选中图表 格式 形状轮廓 无轮廓 ， 然后再选中下边的半圆 （ 单击 两次 ），继续选择格式 形状填充 无填充 颜色 ，此时半圆初步形成，但是下边空了很大一部分，如图所示。 Step4：照相机 工具。 按住 Shift 键调整图表大小，使其 同比例 变化，宽度等于三个单元格的宽度，然后选中合适数量的单元格，如图所示。 然后 点击工具栏的照相机工具，此时照片已被保存，然后点击任意单元格，则会出现一张大小合适的半圆图片了，此时的图片可以随 数据 自动变化，结果如图。 2.4 粗边面积图 【特点 】 面积图上 有一条粗 变 折线图 【原理 】 1.折线图 和面积图组合； 2 坐标轴 在刻度

20、线上 【制作技巧 】 Step1： 构造数据 。 选中 A1:B9 单元格输入 年份和存款数两列数据，具体数据结构如下所示： Step2： 插入 图表。 选中 A1:B9 单元格 插入 折线图， 再次 选中 B1： B9， Ctrl+C复制数据，选中图表后按 Ctrl+V， 此时 ，两根线重叠在一起， 点击 折线图 右键 更改 图表类型，如下图所示。 然后 将 其中 一个图表类型 改为 面积图，确定后，结果如图 所示 。 Step3： 交 于 Y 轴 。 选中图表， 点击 图表右上角的 ”+”坐标轴 更多选项 ， 将 坐标轴位置改为在刻度线上，结果如图。 Step4： 美化 图表。 最后 可以

21、 将 不需要的网格线、图表标题、背景颜色和轮廓等统统去掉或者改为自己 需要的格式 。 01002003004005006007002008 2009 2010 2011 2012 2013 2014 201501002003004005006007002008 2009 2010 2011 2012 2013 2014 20152.5 帕累托图 【特点 】 柱形图 递减，折线图递增 【原理 】 1.柱形图 +折线图； 2.隐藏次要坐标轴 【制作技巧 】 Step1： 构造数据 。 在 A1： D7单元格 里构造单身 原因 、人数、累计百分比、累计人数 4列 数据，其中 C3 数据公式 =C3/

22、$D$8， 然后双击得出一列数据， D3 数据公式 =SUM($D$3：D3)，然后双击 得出全部数据，结果如图： Step2： 插入图表 。 选择 A3： C8 单元格 插入 簇状柱形图，然后选 中 累计百分比序列的图表 右键 更改图 表 类型 带数据标签 的 折线图，同 时 改为 选用 次坐标轴 ， 结果如图。 Step3： 交于顶点 。 选中 上图中的系列 2右键 选择数据 系列 值选择 C2：C8，然后 将系列 2 水平标签 中 的空单元格取消勾选，确定后如图 0%20%40%60%80%100%120%0246810相貌差 体重大 家里穷 喜欢宅 脾气差 太幼稚系列 1 系列 2St

23、ep4： 图表 美化。 将次坐标轴最大值改为 100%，然后将图例删掉，网格线去掉 ， 再选中柱形图 右键 设置数据系列 格式 分类间距改为 5%， 如下图 点击 图表，选择右上角 “+”坐标轴 选中 次要横坐标轴 ， 单击次要横坐标轴 右键 设置坐标轴格式 在刻度线上 ， 结果如下图所示。 Step5： 隐藏次坐标轴。 分别 选择次要横纵坐标轴 右键 设置 坐标轴 格式 标签 改为无，则次坐标轴 均被隐藏起来 ，最终结果如下所示。 0%20%40%60%80%100%0246810相貌差 体重大 家里穷 喜欢宅 脾气差 太幼稚系列 1 系列 20%10%20%30%40%50%60%70%8

24、0%90%100%0123456789相貌差 体重大 家里穷 喜欢宅 脾气差 太幼稚相貌差 体重大 家里穷 喜欢宅 脾气差 太幼稚0%10%20%30%40%50%60%70%80%90%100%0123456789相貌差 体重大 家里穷 喜欢宅 脾气差 太幼稚0123456789相貌差 体重大 家里穷 喜欢宅 脾气差 太幼稚2.6 漏斗图 【特点 】 图表呈 漏斗形 【原理 】 1.构造辅助列 ； 2.无色 填充占位数据 【制作技巧 】 Step1： 构造数据 。 在 A1： C6 单元格 构造项目、占位数据、人数三列数据。其中 B2 单元格 公式 =（ $C$2-C2） /2， 然后双击填

25、充数据，最终结果 如图 。 Step2： 插入图表 。 选中 A1： C6 单元格 插入 堆积条形图 ，然后 选择 纵坐标轴右键 设置坐标轴格式 改为逆序 类别 ， 结果如图 。 Step3： 图表 美化。 选中 占位数据条形图 格式 形状填充 无填充颜色 ， 然后将 横坐标轴最大 值改为 500， 将网格线 、 图 表 标题和图例去掉，结果如下。 0 200 400 600浏览产品放购物车生成订单支付订单完成交易图表标题占位数据 人数Step4： 其他 方法。 由于最新 Excel 图表中 新增了 漏斗图，也可以不用辅助列，直接用A列 和 C列 数据自动生成漏斗图。 0 100 200 30

26、0 400 500浏览产品放购物车生成订单支付订单完成交易2.7 柱形趋势图 【特点 】 柱形图 上带有折线图 【原理 】 1.面积图 +折线图； 2.隐藏空值 。 【制作技巧 】 Step1： 构造 数据。 在 A： C 列构造如下数据，其中 C 列数据为随机生成结果，公式=RANDBETWEEN(40,80),结果如下图 所示。 Step2： 插入 图表。 选中 A1： C16 单元格 插入 面积图，然后选中 C2:C16 单元格 复制，选中图表按 Ctrl+V，得到新的一个图表将原图 表 覆盖。此时 ， 选中新图 表 右键 更改图表类型 将 系列 2 改为 折线图，同时 设置 次坐标轴。

27、结果如图 。 Step3： 去除 空值。 此时的图表最大的问题在于连接在一起，而我们的目的是让每个地区能够分开。此时 先选中 图表 右键 选择数据 隐藏 的单元格和空单元格 (H)，空单元格 显示设置为 ： 空距，点击确定，结果如图。 Step4： 图表 美化。 将 主次纵坐标最大值均设为 80， 然后删掉次纵坐标轴，删除网格线，结果如图。 01020304050607080900102030405060708090A支行B支行 C支行D支行E支行 F支行 G支行H支行I支行 J支行 K支行 L支行北京 天津 上海 济南0102030405060708090010203040506070809

28、0A支行B支行 C支行D支行E支行 F支行 G支行H支行I支行 J支行 K支行 L支行北京 天津 上海 济南01020304050607080A支行 B支行 C支行 D支行E支行 F支行 G支行H支行I支行 J支行 K支行 L支行北京 天津 上海 济南2.8 T线 图 【特点 】 条形图 上有个 T 字 形状线 【原理 】 1.利用误差线 【制作技巧 】 Step1： 构造数据 。 在 A1： D7 单元格 构造指标项 、 A 支行 、 B 支行 、误差线 四列 数据。其中 B2 单元格 =RAND()*100， B3 单元格 = B2*(1-RAND()/5)， 然后下拉得到 B列 完整数据

29、，C2 单元格 =B2*(1+RAND()/5)，双击 得到 C 列 数据， D2 单元格 =C2-B2， 双击得到 D 列 数据，结果如下图 。 Step2： 插入图表 。 选中 A1： B7单元格 插入 簇状 条形图 ，点击 纵轴 右键设置 坐标轴格式 逆序类别，然后点击图表，选中图表右上角的 “+”， 选中 误差线，结果如图。 Step3： 修饰 误差线。 点击 误差线 右键 设置错误栏格式 ， 将 正负偏差改为正偏差， 同时 将误差量改为自定义 指定值 ， 将正错误值选为 D2:D7 单元格，确定后结果如图。 0 500 1000 1500存款总额贷款总额信用卡储蓄卡网银员工人数A支行Step4： 美化图表 。 将图标表标题、网格线去掉，为条形图加上数据标签，结果如图 所示 。 0 200 400 600 800 1000存款总额贷款总额信用卡储蓄卡网银员工人数A支行916 805 800 710 627 587 0 200 400 600 800 1000存款总额贷款总额信用卡储蓄卡网银员工人数