EXCEL商务图表设计指南

1、EXCEL商务图表设计指南本分享资料来自知乎网站：作者：吴下锕蒙 陈希、吕展鹏、ivor Zou 等人赞同 回答有些长，为节约大家的时间，我选取了一个经典案例，辅以详细的说明（中文版Excel2013）放在开头，没时间看全部回答的品一下这个案例就好了。这个案例充分体现了“将Excel的元素融入图表”的技巧。以下商业杂志图表均利用了这一技巧。下图是我参照上图制作的图表。看完详细的绘制步骤后，你将深刻体会到这一技巧的奥妙所在。首先选中源数据，A到F列绘制散点图，得到经典的Excel风格图表将利润率设为次坐标：选中橙色那根线，右键-设置数据系列格式-次坐标轴删去图表标题、图例，调节横坐标、两个纵坐标

2、的上下限，删去纵网格线，删去两个纵坐标的轴线，得到这样一张图下一步称为“锚定”，鼠标光标移动到下图所示的图表左上角的顶点处，按住Alt，随后按住鼠标进行拖动，发现这样调节图表的尺寸，限定于Excel的网格点。四个角都这样进行调节，分别“锚定”于N7, V7, N15, V15选中图表区域，右键-设置图表区域格式，在属性中选择“大小固定，位置随单元格而变”，这样，在调整Excel行距和列宽时，图表就不会随之而动。在第46行输入内容，设置填充色调节715行行距，使得Excel网格线与我们做的图表的横向网格线一一重叠；调节O列和U列列宽，使得O列左侧网格线恰好经过图表横网格线的起点，U列右侧网格线也

3、是一样的道理，如下图所示。选中图表区，填充色改为无色，外轮廓也删去，这样图表就变成“透明”的了随后对N7:V15这个区域的单元格进行填充色。（选中这些单元格的方法：先选中图表区域外的一个单元格，如M7，按键盘的键，移动到N7，然后按住Shift，再按键或键调节即可，选中后进行单元格填充。）在Excel“视图”中取消勾选网格线最后添加一些图例即可怎么样？相信你已体会到了如何将Excel的元素融入图表设计中。=原回答=我曾在大三寒假闭关三周，自学Excel, PowerPoint和Word，一年后又花了一个月的时间研习VBA。楼上关于函数和操作技巧已经分享很多了，我在这分享一些图表设计的技巧。图表

4、的重要性不言而喻，再好的数据，如果不能有效地呈现出来也是白费功夫。我相信看完这个回答后，你再也不会将图做成这样。好奇商业杂志上的这些高端大气的图是用什么特殊软件做出来的吗？答案就是Excel。滑珠图、子弹图、瀑布图一切都可以用Excel最基本的操作搞定。我会先介绍一些设计的核心理念和方法，然后列举16个“商务范”图表制作实例，包含详细的制作步骤，最后分享一些配色方案。=目录一、商务图表制作核心理念和方法突破Excel的图表元素突破Excel的图表类型布局与细节二、“商务范”图表制作实例日期坐标轴妙用堆积柱形图妙用漏斗图-利用辅助列占位自定义Y轴刻度间距含加粗边缘的面积图图表覆盖妙用 - 横网格

5、线覆盖于图表之上为Pie图加背景图片仪表盘多数量级的几组数据同时比较手风琴式折叠bar图Water Fall 瀑布图不等宽柱形图滑珠图动态图表1动态图表2Bullet图-竖直三、配色方案Nordri设计公司分享的配色方案ExcelPro分享的方案四、自学参考书目和资料=正文一、商务图表制作核心理念和方法(这一章节的笔记整理自刘万祥老师的博客ExcelPro的图表博客)1. 突破Excel的图表元素不要仅用“图表”做图表，而是用“图表所有Excel元素（如单元格，填充色，文本框）”去做图表。（在我开头举的案例中有详尽的说明）左上图，只有B4单元格是图表区域，标题利用的是B2；B3B5填充浅色，&

6、quot;index"和"data"分别在B3、B5。右上图，B2为图表序号，C2为图表标题，填深绿色，B3为副标题，图例放在C4，图表在C5，B2到C5填充淡色，B6、C6合并填写注释。左上图，标题在C2H2居中，图表在C3H3，利用Excel单元格的数据表在C6H8。右上图，B2填红色装饰，标题和副标题分别在B2、B3，图表在D4F4，数据来源在D5，标号2为矩形框，整个区域有边框。2. 突破Excel的图表类型左上图，先用所有数据做曲线图或柱形图，然后选中相应的序列，更改图表类型，有时还需要用到次坐标轴。右上图，先做好面积图，然后将该数据序列再次加入图表，修

7、改新序列的图表类型为曲线图，调粗线型。3. 布局与细节布局下图从上到下可以分为5个部分：主标题区、副标题区、图例图、绘图区、脚注区。特点有：完整的图表要素；突出的标题区；从上到下的阅读顺序。标题区非常突出，占到整个图表面积1/3以上，其中主标题用大号字和强烈对比效果，副标题提供详细信息。竖向构图方式整个图表外围高宽比例在2:1到1:1之间，图例一般在绘图区上部或融入绘图区里面使用更为简洁醒目的字体商业图表多选用无衬线类字体图表和表格的数字中使用Arial字体、810磅大小，中文使用黑体注意图表的细节处理1. 脚注区写上数据来源2. 图标注释：对于图表中需要特别说明的地方，如指标解释、数据口径、

8、异常数据等，使用上标或*等进行标记，在脚注区说明3. 坐标轴截断标识4. 四舍五入：在脚注区写明：由于四舍五入，各数据之和可能不等于总额(或100%)5. 简洁的坐标轴标签：如2003、04、056. 让Line图从y轴开始：双击x轴，Axis Options-最下-Position Axis-on tick marks7. 作图数据的组织技巧: 原始数据不等于作图数据；作图前先数据排序；将数据分离为多个序列，每个序列单独格式化8. 其他: 去除绘图区的外框线，去除纵坐标轴的线条色，将网格线使用淡灰色予以弱化，bar间距小于bar宽度，饼图分块用的白色线二、“商务范”图表制作实例（这一章节的1

9、6个案例均出自刘万祥老师的Excel图表之道 (豆瓣)，该书基于Excel2003）最初回答中，这部分整理自我的笔记，基于英文版Excel2010。为了知友阅读方便，我以Excel 2013中文版操作了一遍，将操作步骤逐条改为了中文。如果你使用的是其他版本，具体操作方法会不同（我的回答中以【】注出），但“【”前面的步骤说明和思路是没有问题的。仪表盘、滑珠图、子弹图、瀑布图、动态图表我有自作的模板。有需要的请至 Excel templ_免费高速下载1. 日期坐标轴妙用利率（y轴）随时间（x轴）的变化，我们希望得到下图所示的柱状图，横坐标的间隔按月份（3月、6月、12月、24月）分布。原始数据与辅

10、助列(A列为月份，B列为利率，C列是辅助列）绘制方法1) 选中A2:B5，做柱状图，发现应是横坐标的A列值也成了柱子2) 删除系列1方法1【选中图表 -> Excel标题栏图表工具 -> 设计 -> 选择数据 -> 系列1 -> 删除】方法2【直接点击蓝色柱子 -> 按Delete键删除】3) 将横坐标转化为我们希望的A列的值【选中图表 -> Excel标题栏图表工具 -> 设计 -> 选择数据 -> 水平(分类)轴标签 编辑 -> 选择区域A2:A5】4) 将横坐标转化为日期坐标轴 【双击横坐标 ->如下左图所示选择“日

11、期坐标轴”】得到下右图5) 删去横坐标【选中横坐标 -> 按Delete键删除】6) 将辅助列添加进去【选中辅助列C2:C5 -> 复制 -> 选中图表 -> 粘贴】蓝色的“系列2”就是我们的辅助序列，因为值为0，所以看不到7) 将蓝色“系列2”转化为折线图 【选中图表 -> Excel标题栏图表工具 -> 格式 -> 最左侧下拉菜单选择最后一项“系列2” -> Excel标题栏图表工具 -> 设计 -> 更改图表类型 -> 如下图所示将蓝色系列1的类型改为折线图】得到8) 让蓝色折线图的数据标签显示出来 【选中蓝色折线 -&g

12、t; 右击鼠标 -> 下图所示勾选数据标签“下方”】9) 隐藏蓝色折线 【选中蓝色折线 -> 右击鼠标 -> 轮廓选择“无轮廓”】得到10) 逐个修改横坐标 【点击选中横坐标，发现四个都选中了(下左图所示) -> 再点击第一个0，将其选中(下右图所示) -> 鼠标点击公式输入栏，输入“=”，鼠标点击A2单元格 ->回车】依次修改即可注意：在选中第一个0后，不要直接输入“=”，而是要在公式输入栏里输入*点评：该案例妙在利用辅助列，做出了柱状图的坐标值。当然，也有万能的办法，即不用辅助列，在完成5)之后，添加文本框作为坐标值。用本例所示的方法好处在于，源数据3、

13、6、12、24修改之后，柱子、坐标值都会随之而动。2. 堆积柱形图妙用效果如图，看似是簇状和堆积柱形图合用，实际呢？一步即可，只需在源数据上下些功夫【选中下图所示B9:E20单元格 -> 绘制堆积柱形图】*点评：利用错行和空行，奇妙无穷。3. 漏斗图-利用辅助列占位效果如图，形似漏斗。 原始数据 （指标需排序好，从大大小）添加辅助列 【C3单元格公式=($D$3-D3)/2，然后拉至C8】绘制方法1) 选中B3:D8，绘制堆积条形图2) 把漏斗倒过来，即反转纵坐标 【双击纵坐标 -> 勾选“逆序类别”】3) 将绿色系列1隐去【选中绿色条 -> 右键 -> 填充 ->

14、; 无填充颜色】*点评：辅助列永远是好帮手。4. 自定义Y轴刻度间距以股价随时间变化为例，重要的是涨跌幅度，且幅度很大，这里我们采用自定义Y轴间距，并以常用的对数坐标为例。最终效果图原始数据通过观测原始数据最小值和最大值，我们希望以20、30、50、100、400、600为刻度作为纵坐标，将数据处理如下C列是B列值的对数值 C2单元格公式为 =Log(B2) ，拉至C12F列即我们希望的刻度G列同理，是F列的Log值绘制方法1)选中C2:C12 绘制折线图2) 将G2:G7加入到图表中 【选中辅助列G2:G7 -> 复制 -> 选中图表 -> 粘贴】3) 将新加入的蓝色折线改

15、为散点图【点击蓝色折线 -> Excel标题栏图表工具 -> 设计 -> 更改图表类型 -> 如下图所示将蓝色系列2的改为“带直线和数据标记的散点图”】得到4) 设置纵轴下限为1 【双击纵轴 -> 在坐标轴选项里将最小值调节为1】5) 删去纵坐标轴，删去水平网格线；6) 设置坐标轴在刻度线上【双击横坐标轴 -> 如下左图所示勾选“在刻度线上”】得到右下图7) 将蓝色折线的横坐标设置为E2:E7【点击蓝色折线 -> Excel标题栏图表工具 -> 设计 -> 选择数据 -> 如左下图所示选择“系列2” -> 点击“编辑” ->

16、; 如右下图所示，X轴系列值选为E2:E7 -> 确定】得到8) 让蓝色数据点的数据值显示出来 【点击蓝色直线 -> 右侧选择数据标签-左】缩小一下绘图区9) 添加误差线 【点击蓝色直线 -> 右侧选择误差线-更多选项】此时，横纵误差线都出来了10) 删除纵误差线 【点击下左图所示的位置选中纵误差线 -> 按Delete键删除】得到又下图11) 调节横误差线参数 【双击横误差线 -> 在右侧弹窗里勾选“正偏差”，“固定值”改为10】缩小一下绘图区，得到右下图12) 隐藏蓝色线 【右键蓝色直线 -> 选择无填充，无轮廓】13) 调节误差线的颜色、线形 【双击误

17、差线 -> 右侧弹窗中修改(下左图所示)】得到右下图13) 与本回答的案例1类似，逐个修改纵坐标数据值【以2.78这个数据为例：选中纵坐标(6个数据一下子都选中了) -> 再点击2.78这个数据(如下图所示，只有2.78选中了) -> 鼠标点击公式输入栏，输入“=”，然后鼠标点击600(F7单元格) -> 回车】依次逐个修改，大功告成。*点评：本例极其巧妙地借助误差线，实现横向网格线。误差线在后续案例中会多次提及。当然，有人会说完全可以不用误差线，插入几个直线拖动就好了。但是，本例方法的好处是，修改20、50、400等坐标值，网格线也会跟着移动。5. 含加粗边缘面积图最

18、终效果与源数据绘制方法1) 选中数据做折线图2) 将源数据再次添加进图表中【选中源数据 -> 复制 -> 选中图表 -> 粘贴】发现系列2覆盖住了系列13) 将系列2改为面积图【点击选中蓝色折线 -> Excel标题栏图表工具 -> 设计 -> 更改图表类型 -> 如下左图所示将改为面积图】得到下右图4) 调节坐标轴位置 【双击横坐标轴 -> 右侧弹窗中勾选“在刻度线上”】得到右下图调节颜色就好了下面这个图是我做的*点评：两种或多种图表类型合用的方法一定要掌握，活学活用。6. 图表覆盖妙用 - 横网格线覆盖于图表之上最终效果绘制方法以柱状图为例

19、（其他类型的图都一样）源数据1) 绘制柱形图2) 将其锚定 【鼠标光标移动到下图所示的图表左上角的顶点处，按住Alt，随后按住鼠标进行拖动，发现这样调节图表的尺寸，限定于Excel的网格点。】如下图所示，将四个角分别锚定于D2，G2，D9， G93) 复制图表【选中图表 -> 复制 -> 鼠标点击任意一个单元格 -> 粘贴】得到左右两个一模一样的图表4) 对右边的图表图表区背景色设为无色【右键图表区 -> 填充和轮廓都设为无】柱子设为无色【右键柱子 -> 填充和轮廓都设为无】对左边的图表删去左网格线【选中网格线 -> Delete键删除】横轴直线隐去【选中横

20、轴 -> 右键 -> 无轮廓】横坐标和纵坐标都的字体都设为白色 【分别选中横纵左边 -> 菜单栏中将字体颜色设为白色】5) 按住Alt移动第二张图覆盖于第一张图之上。一定要按住Alt进行拖动！6) 自行设计网格线颜色即可*点评：此案例巧妙地利用了图表覆盖。7. 为Pie图加背景图片最终效果原始数据绘制方法1) 先用A1:A5做饼图，为系列12) 选中源数据中任意一个值 (如A3) 添加到图表中 【选中A3 -> 复制 -> 选中图表 -> 粘贴】 为系列2此时无法看到也无法选择系列2，看到的仍然是上图的样子3) 将系列1改为次坐标轴【选中图表 -> E

21、xcel标题栏图表工具 -> 设计 -> 更改图表类型 -> 将系列1改为次坐标轴(下图所示)】看到的仍然是上图的样子4) 将系列设为无填充【右击大饼 -> 设置填充色为无填充】此时看到的正是系列2，如下图5) 为系列2加背景图片 【双击图表，右侧出现弹窗 ->Excel标题栏图表工具 -> 格式 -> 左侧下拉菜单选择“系列2” -> 右侧弹窗中选择插入图片 】*点评：如果不用本案例的方法，直接给饼图加背景图，得到的是.8. 仪表盘最终效果在某个单元格中输入数值（0-100），红色的指针会随之而动该案例不是很切题，应用也很局限，所以删去了操作步

22、骤。该例成品可至前面提到的网盘地址中下载。若有兴趣研究详细做法，请私信。9. 多数量级的几组数据同时比较最终效果原始数据处理数据F3单元格 =B3/MAX($B$3:$B$8)*0.8，拉至F8G3单元格 =1-F3，拉至G8H3单元格 =C3/MAX($C$3:$C$8)*0.8，拉至H8I3单元格 =1-H3，拉至I8J3单元格 =D3/MAX($D$3:$D$8)*0.8，拉至J8绘制方法1) F3:J8作堆积条形图，删去网格线、横坐标轴2) 纵坐标逆序【双击纵坐标 -> 左侧弹窗中勾选“逆序类别”】3) 把占位条设为白色 【在需要调成白色的条上右键 -> 填充色设为无色】添

23、加三个文本框，得到*点评：0.8是可调节的，根据需要而定，可以是0.7，也可以是0.9这个案例在2014年终汇报中用到了！特别适合不同数量级的数据对比。10. 手风琴式折叠bar图最终效果（突出前三个和后三个数据，中间的数据弱化显示）原始数据 （假设前后各有三个数据需要强调）作图数据注意：第一列 ：若前后各有n个数据需要强调，那么中间就空n个； 第二列：中间的数据若有m个，则前后各留m-1个；两列首行要对齐如下图所示绘制方法1) 以第一列做堆积条形图（上图第一列黑色框内的数据，E2:E10）2) 将第二列数据添加到图表中【选中上上图中第二列黑色框内的数据(F2:F17) -> 复制 -&

24、gt; 选中图表 -> 粘贴】3) 将蓝色条形图改为次坐标轴 【单击选中蓝色条 -> Excel标题栏图表工具 -> 设计 -> 更改图表类型 -> 将系列2的“次坐标轴”勾选】得到4) 将上下两个横坐标轴的上限值改为一致，这里改为100【双击横坐标轴 -> 在右侧弹窗中调节最大值为100】5) 让次纵坐标轴显示出来【点击图表区 -> 下图所示勾选次要纵轴】此时四根轴都出来了(上左图所示)6) 将左右两根纵轴反转【双击纵轴 -> 右侧弹窗中勾选“逆序系列” -> 另一根纵轴一样处理】得到右下图7) 删去下面和右边的两根轴，然后可设置填充色等

25、*点评：你可以尝试一下其他情况，如前后各突出5个，或前突出2两个，后突出4个。其实利用的都是空格占位。11. Water Fall 瀑布图最终效果原始数据作图数据D3 =B3D4 =SUM($B$3:B4) ，拉至D9E3 =B3E10 =B10F4 =IF(B4<0,D4,D3) ，拉至F9G4 =IF(B4>=0,B4,0) ， 拉至G9H4 =IF(B4>=0,0,ABS(B4) ， 拉至H9作图方法1) 选中蓝色框内的值 (E3: H10)，做堆积柱形图2) 蓝色柱形图设置为无色【右击蓝色柱 -> 无填充色】再稍作调节12. 不等宽柱形图最终效果1 - 方法1制

26、得（高度反映ARPU值，宽度反映用户规模，四个柱子依次是四种产品）原始数据最终效果2 - 方法2制得绘制方法1 - 分组细分法 - 柱形图将数据处理如下 每个ARPU数据重复次数为“用户规模”（柱子宽度）数1) 选中B7:E26，做柱形图，删去无关元素2) 选中任意一根柱子，在右侧“设置数据系列格式”中将“系列重叠”改为100%，将“分类间距”改为0%就得到了我们想要的图表绘制方法2- 时间刻度法 - 面积图原始数据依旧作图数据要花一些功夫首先看A列，A1的内容是0，A2到A4是“产品1”的“用户规模”，为8，A5到A7是“产品1”和“产品2”的“用户规模”之和8+4=12，同理A8到A10是

27、14，而最后一个单元格A11是8+4+2+6=20注意，如果是5个产品，8个产品呢？A1永远是0，A1下面每一组依旧是3个，而最后一个单元格仍是所有用户规模之和B列到E列就不用多说了，两两分别是ARPU值1) 选中A1:E11，做面积图，删去无用的信息，但注意要留着横坐标2) 将横轴改为A1:A11 【选中图表 -> Excel标题栏 图表工具 -> 设计 - > 选择数据 -> 单击下图所示的水平轴标签 编辑按钮 -> 在弹窗中选择为A1:A11 -> 确定】3) 删去多余图形，如下图所示，在红圈位置处单击，按Delete键删除得到4) 将横轴改为时间刻度

28、 【选中横坐标 - 右侧设置坐标轴格式中选为日期坐标轴】然后删去横坐标，得到5) 依次更改这4个柱子的轮廓为白色，并调节轮廓线宽得到最终的图表*点评：方法1简单易行，但方法2做出来的图更美观。两者都是巧妙地构造作图数据，值得一品。13. 滑珠图最终效果 （右图是我仿照原图画的）蓝色奥巴马支持率，红色麦凯恩支持率。纵坐标为不同人群两种滑珠为散点图，横梁为条形图绘制方法数据（左下） E列为散点图Y轴数据1)选中A2:A10和D2:D10，作簇状条形图，并将纵轴逆序排列，将横坐标最大值定为100，得到右下图2) 选中B2:B10，复制，粘贴入图表，然后将这个新系列改为改为散点图（左下）将红色散点图的

29、横坐标改为B2:B10，纵坐标改E2:E10，得到右下图3) 用同样的方法处理C2:C104) 调节柱形图、散点图的颜色、填充等，完工。*点评：乍一看摸不着头脑的图，其实就是条形图和散点图的巧妙叠加。我的工作中就用到了这一案例，纵坐标是10个人，而散点是每个人的两项指标（0100），真是形象而明了。本例用到的步骤在之前均多次使用，所以没有详细展开。14. 动态图表1 B3单元格 =INDEX(B8:B13,$B$5) 横向拉到N3 （这样当在右下角的List Box里选择时，B5单元格灰显示选择结果，B3:N3就会跟着显示选择结果对应各月的数值）以B3:N3作图即可辅助阅读：List box是怎么出来的？【也可不用List box，直接在B5里输入数值（15）就好】List box的调出方法：File-Options-Customize Ribbon-右边框内勾选Developer 这样面板就有Developer栏，单击Developer-Controls-Insert-第一排第五个 List Box 添加到工作表中右击该List Box， Format Contr