EXCEL高逼格商务图表制作

合集下载
  1. 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。

EXCEL高逼格商务图表制作
======================= 目录
一、商务图表制作核心理念和方法
1.突破Excel的图表元素
2.突破Excel的图表类型
3.布局与细节
二、“商务范”图表制作实例
1.日期坐标轴妙用
2.堆积柱形图妙用
3.漏斗图-利用辅助列占位
4.自定义Y轴刻度间距
5.含加粗边缘的面积图
6.图表覆盖妙用- 横网格线覆盖于图表之上
7.为Pie图加背景图片
8.仪表盘
9.多数量级的几组数据同时比较
10.手风琴式折叠bar图
11.Water Fall 瀑布图
12.不等宽柱形图
13.滑珠图
14.动态图表1
15.动态图表2
16.Bullet图-竖直
三、配色方案
1.Nordri设计公司分享的配色方案
2.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,利用Excel单元格的数据表在C6-H8。

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

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宽度,饼图分块用的白色线——————————————————————————————————
二、“商务范”图表制作实例
1. 日期坐标轴妙用
利率(y轴)随时间(x轴)的变化,我们希望得到下图所示的柱状图,横坐标的间隔按月份(3月、6月、12月、24月)分布。

原始数据与辅助列(A列为月份,B列为利率,C列是辅助列)
绘制方法
1) 选中A2:B5,做柱状图,发现应是横坐标的A列值也成了柱子
删除系列1
2) 删除系列1
方法1【选中图表--> Excel标题栏图表工具--> 设计--> 选择数据--> 系列1 --> 删除】
方法2【直接点击蓝色柱子--> 按Delete键删除】
将横坐标转化为我们希望的A列的值
3) 将横坐标转化为我们希望的A列的值
【选中图表--> Excel标题栏图表工具--> 设计--> 选择数据--> 水平(分类)轴标签编辑--> 选择区域A2:A5】
4) 将横坐标转化为日期坐标轴【双击横坐标-->如下左图所示选择“日期坐标轴”】
得到下右图
5) 删去横坐标【选中横坐标--> 按Delete键删除】
6) 将辅助列添加进去【选中辅助列C2:C5 --> 复制--> 选中图表--> 粘贴】蓝色的“系列2”就是我们的辅助序列,因为值为0,所以看不到
7) 将蓝色“系列2”转化为折线图
【选中图表--> Excel标题栏图表工具--> 格式--> 最左侧下拉菜单选择最后一项“系列2” --> Excel标题栏图表工具--> 设计--> 更改图表类型--> 如下图所示将蓝色系列1的类型改为折线图】
得到
8) 让蓝色折线图的数据标签显示出来【选中蓝色折线--> 右击鼠标--> 下图所示勾选数据标签“下方”】
9) 隐藏蓝色折线【选中蓝色折线-->右击鼠标-->轮廓选择“无轮廓”】
得到
10) 逐个修改横坐标【点击选中横坐标,发现四个都选中了(下左图所示) -->再点击第一个0,将其选中(下右图所示) -->鼠标点击公式输入栏,输入“=”,鼠标点击A2单元格-->回车】依次修改即可
注意:在选中第一个0后,不要直接输入“=”,而是要在公式输入栏里输入
**点评:该案例妙在利用辅助列,做出了柱状图的坐标值。

当然,也有万能的办法,即不用辅助列,在完成5)之后,添加文本框作为坐标值。

用本例所示的方法好处在于,源数据3、6、12、24修改之后,柱子、坐标值都会随之而动。

2. 堆积柱形图妙用
效果如图,看似是簇状和堆积柱形图合用,实际呢?
一步即可,只需在源数据上下些功夫
【选中下图所示B9:E20单元格--> 绘制堆积柱形图】
**点评:利用错行和空行,奇妙无穷。

3. 漏斗图-利用辅助列占位
效果如图,形似漏斗。

原始数据(指标需排序好,从大大小)
添加辅助列【C3单元格公式=($D$3-D3)/2,然后拉至C8】
绘制方法
1) 选中B3:D8,绘制堆积条形图
2) 把漏斗倒过来,即反转纵坐标【双击纵坐标--> 勾选“逆序类别”】
3) 将绿色系列1隐去【选中绿色条--> 右键--> 填充--> 无填充颜色】
**点评:辅助列永远是好帮手。

4. 自定义Y轴刻度间距
以股价随时间变化为例,重要的是涨跌幅度,且幅度很大,这里我们采用自定义Y轴间距,并以常用的对数坐标为例。

最终效果图
原始数据
通过观测原始数据最小值和最大值,我们希望以20、30、50、100、400、600为刻度作为纵坐标,
将数据处理如下
∙C列是B列值的对数值C2单元格公式为=Log(B2) ,拉至C12
∙F列即我们希望的刻度
∙G列同理,是F列的Log值
绘制方法
1)选中C2:C12 绘制折线图
2) 将G2:G7加入到图表中【选中辅助列G2:G7 --> 复制--> 选中图表--> 粘贴】
3) 将新加入的蓝色折线改为散点图
【点击蓝色折线--> Excel标题栏图表工具--> 设计--> 更改图表类型--> 如下图所示将蓝色系列2的改为“带直线和数据标记的散点图”】
得到
得到
4) 设置纵轴下限为1 【双击纵轴--> 在坐标轴选项里将最小值调节为1】
5) 删去纵坐标轴,删去水平网格线;
6) 设置坐标轴在刻度线上【双击横坐标轴--> 如下左图所示勾选“在刻度线上”】得到右下图
7) 将蓝色折线的横坐标设置为E2:E7【点击蓝色折线--> Excel标题栏图表工具--> 设计--> 选择数据--> 如左下图所示选择“系列2” --> 点
击“编辑” --> 如右下图所示,X轴系列值选为E2:E7 --> 确定】
得到
得到
让蓝色数据点的数据值显示出来【点击蓝色直线--> 右侧选择数据标签-左】
缩小一下绘图区
9) 添加误差线【点击蓝色直线--> 右侧选择误差线-更多选项】
此时,横纵误差线都出来了
10) 删除纵误差线【点击下左图所示的位置选中纵误差线--> 按Delete键删除】
得到又下图
11)调节横误差线参数【双击横误差线--> 在右侧弹窗里勾选“正偏差”,“固定值”改为10】
缩小一下绘图区,得到右下图
12) 隐藏蓝色线【右键蓝色直线--> 选择无填充,无轮廓】
13) 调节误差线的颜色、线形【双击误差线--> 右侧弹窗中修改(下左图所示)】得到右下图
13) 与本回答的案例1类似,逐个修改纵坐标数据值【以2.78这个数据为例:选中纵坐标(6个数据一下子都选中了) --> 再点击2.78这个数据(如下图所示,只有2.78选中了) --> 鼠标点击公式输入栏,输入“=”,然后鼠标点击600(F7
单元格) --> 回车】
依次逐个修改,大功告成。

**点评:本例极其巧妙地借助误差线,实现横向网格线。

误差线在后续案例中会多次提及。

当然,有人会说完全可以不用误差线,插入几个直线拖动就好了。

但是,本例方法的好处是,修改20、50、400等坐标值,网格线也会跟着移动。

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

6. 图表覆盖妙用- 横网格线覆盖于图表之上
最终效果
绘制方法
以柱状图为例(其他类型的图都一样)
源数据
1) 绘制柱形图
2) 将其锚定【鼠标光标移动到下图所示的图表左上角的顶点处,按住Alt,随后按住鼠标进行拖动,发现这样调节图表的尺寸,限定于Excel的网格点。


如下图所示,将四个角分别锚定于D2,G2,D9,G9
3) 复制图表【选中图表--> 复制--> 鼠标点击任意一个单元格--> 粘贴】
得到左右两个一模一样的图表
4) 对右边的图表
∙图表区背景色设为无色【右键图表区-->填充和轮廓都设为无】
∙柱子设为无色【右键柱子-->填充和轮廓都设为无】
对左边的图表
∙删去左网格线【选中网格线--> Delete键删除】
∙横轴直线隐去【选中横轴-->右键-->无轮廓】
∙横坐标和纵坐标都的字体都设为白色【分别选中横纵左边-->菜单栏中将字体颜色设为白色】
5) 按住Alt移动第二张图覆盖于第一张图之上。

一定要按住Alt进行拖动!!!!!!
6) 自行设计网格线颜色即可
**点评:此案例巧妙地利用了图表覆盖。

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

9. 多数量级的几组数据同时比较
最终效果
原始数据
处理数据
F3单元格=B3/MAX($B$3:$B$8)*0.8,拉至F8 G3单元格=1-F3,拉至G8
H3单元格=C3/MAX($C$3:$C$8)*0.8,拉至H8 I3单元格=1-H3,拉至I8
J3单元格=D3/MAX($D$3:$D$8)*0.8,拉至J8
绘制方法
1) F3:J8作堆积条形图,删去网格线、横坐标轴
2) 纵坐标逆序【双击纵坐标--> 左侧弹窗中勾选“逆序类别”】
3) 把占位条设为白色【在需要调成白色的条上右键--> 填充色设为无色】
添加三个文本框,得到
**点评:0.8是可调节的,根据需要而定,可以是0.7,也可以是0.9
这个案例在2014年终汇报中用到了!特别适合不同数量级的数据对比。

10. 手风琴式折叠bar图
最终效果(突出前三个和后三个数据,中间的数据弱化显示)
原始数据(假设前后各有三个数据需要强调)
作图数据注意:
∙第一列:若前后各有n个数据需要强调,那么中间就空n个;
∙第二列:中间的数据若有m个,则前后各留m-1个;
∙两列首行要对齐
如下图所示
绘制方法
1) 以第一列做堆积条形图(上图第一列黑色框内的数据,E2:E10)
2) 将第二列数据添加到图表中【选中上上图中第二列黑色框内的数据(F2:F17) --> 复制--> 选中图表--> 粘贴】
3) 将蓝色条形图改为次坐标轴【单击选中蓝色条--> Excel标题栏图表工具--> 设计--> 更改图表类型--> 将系列2的“次坐标轴”勾选】
得到
4) 将上下两个横坐标轴的上限值改为一致,这里改为100【双击横坐标轴-->
在右侧弹窗中调节最大值为100】
5) 让次纵坐标轴显示出来【点击图表区--> 下图所示勾选次要纵轴】
此时四根轴都出来了(上左图所示)
6) 将左右两根纵轴反转【双击纵轴--> 右侧弹窗中勾选“逆序系列” --> 另一根纵轴一样处理】
得到右下图
7) 删去下面和右边的两根轴,然后可设置填充色等
**点评:你可以尝试一下其他情况,如前后各突出5个,或前突出2两个,后突出4个。

其实利用的都是空格占位。

11. Water Fall 瀑布图
最终效果
原始数据
作图数据D3 =B3
D4 =SUM($B$3:B4) ,拉至D9
E3 =B3
E10 =B10
F4 =IF(B4<0,D4,D3) ,拉至F9
G4 =IF(B4>=0,B4,0) ,拉至G9
H4 =IF(B4>=0,0,ABS(B4)) ,拉至H9
作图方法
1) 选中蓝色框内的值(E3: H10),做堆积柱形图
2) 蓝色柱形图设置为无色【右击蓝色柱--&gt; 无填充色】
再稍作调节
12. 不等宽柱形图
最终效果1 - 方法1制得
(高度反映ARPU值,宽度反映用户规模,四个柱子依次是四种产品)原始数据
最终效果2 - 方法2制得
绘制方法1 - 分组细分法- 柱形图
将数据处理如下[每个ARPU数据重复次数为“用户规模”(柱子宽度)数]
1) 选中B7:E26,做柱形图,删去无关元素
2) 选中任意一根柱子,在右侧“设置数据系列格式”中将“系列重叠”改为
就得到了我们想要的图表
绘制方法2- 时间刻度法- 面积图
原始数据依旧
作图数据要花一些功夫
首先看A列,A1的内容是0,A2到A4是“产品1”的“用户规模”,为8,A5到A7是“产品1”和“产品2”的“用户规模”之和8+4=12,同理A8到A10是14,而最后一个单元格A11是8+4+2+6=20
注意,如果是5个产品,8个产品呢?A1永远是0,A1下面每一组依旧是3个,而最后一个单元格仍是所有用户规模之和
B列到E列就不用多说了,两两分别是ARPU值
1) 选中A1:E11,做面积图,删去无用的信息,但注意要留着横坐标。

相关文档
最新文档