函数、VBA、图表是Excel的三把利器,希望这篇关于图表的回答能给大家一些启迪。但我最擅长的其实还是前两者。在这我就不放函数和VBA的笔记了。因为,我相信,要想熟练掌握函数与VBA,最好的办法还是借一本入门书,夯实基础,循序渐进。今天这里学一招,明天那里记住一个公式,会只见树木不见森林。
比如,Vlookup函数似乎成了新手迈向Excel初学者的第一步,但你可知Vlookup有很大的局限性?如果你借了本书从基础抓起,就会同时掌握Hlookup和lookup,还有更加灵活的Index与Match。
就像学了英语之后会发现更大的世界,自从掌握了函数与初步VBA,我也发现了更大的世界,处理数据会有全新的思路,不曾想到过的功能变成现实,像一个程序员一样做出些人机交互的”Excel app”。你会发现,配合函数与VBA,任何重复性工作都可以一键化实现。
因为工作中离不开Excel,经常使用,掌握的技能就不会生疏,而是越发触类旁通,稳步提升。
所以,如果在可见的未来,你的工作中会用到Excel,我建议你集中一段时间花上100~150个小时玩一玩Excel,乐趣无穷。
刘老师出的书 Excel图表之道 (豆瓣)
刘老师的博客ExcelPro的图表博客
将利润率设为次坐标:选中橙色那根线,右键-设置数据系列格式-次坐标轴
删去图表标题、图例,调节横坐标、两个纵坐标的上下限,删去纵网格线,删去两个纵坐标的轴线,得到这样一张图
下一步称为“锚定”,鼠标光标移动到下图所示的图表左上角的顶点处,按住Alt,随后按住鼠标进行拖动,发现这样调节图表的尺寸,限定于Excel的网格点。
四个角都这样进行调节,分别“锚定”于N7, V7, N15, V15
选中图表区域,右键-设置图表区域格式,在属性中选择“大小固定,位置随单元格而变”,这样,在调整Excel行距和列宽时,图表就不会随之而动。
在第4~6行输入内容,设置填充色
调节7~15行行距,使得Excel网格线与我们做的图表的横向网格线一一重叠;
调节O列和U列列宽,使得O列左侧网格线恰好经过图表横向网格线的起点,U列右侧这根网格线也是一样的道理,如下图所示。
选中图表区,填充色改为无色,外轮廓也删去,这样图表就变成“透明”的了
随后对N7:V15这个区域的单元格进行填充色。
(选中这些单元格的方法:
先选中图表区域外的一个单元格,如M7,按键盘的→键,移动到N7,然后按住Shift,再按→键或↓键调节即可,选中后进行单元格填充。)
在Excel“视图”中取消勾选网格线
最后添加一些图例即可
一流的Excel函数和VBA水平,自然要以一流的图表设计呈现出来。
我相信看完这个回答后,你再也不会将图做成这样。
好奇商业杂志上的这些高端大气的图是用什么特殊软件做出来的吗?
答案就是Excel。
滑珠图、子弹图、瀑布图……一切都可以用Excel最基本的操作搞定。
我会先介绍一些设计的核心理念和方法,然后列举16个“商务范”图表制作实例,包含详细的制作步骤,最后分享一些配色方案。
==================================================================
目录
一、商务图表制作核心理念和方法
二、“商务范”图表制作实例
三、配色方案
==================================================================
正文
一、商务图表制作核心理念和方法
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元素主要有单元格、文本框、线条、箭头等。
上面四例都是
将图表区和绘图区设置无色透明,将单元格设置相应的填充色(在我开头举的案例中有详尽的说明)
将图表对齐在某个单元格(锚定,上面有说明),则可以通过调整单元格的列宽行高,方便地控制图表的大小。
选中图表区域,右键-设置图表区域格式,在属性中设置图表是否随单元格变而变。
当多个图表都锚定在某一列/行的单元格上时,就可精确实现图表的对齐,更可方便的批量快速的调整图表大小。
2. 突破Excel的图表类型
左上图,先用所有数据做曲线图或柱形图,然后选中相应的序列,更改图表类型,有时还需要用到次坐标轴。
右上图,先做好面积图,然后将该数据序列再次加入图表,修改新序列的图表类型为曲线图,调粗线型。
3. 布局与细节
下图从上到下可以分为5个部分:主标题区、副标题区、图例图、绘图区、脚注区。
特点有:完整的图表要素;突出的标题区;从上到下的阅读顺序。
标题区非常突出,占到整个图表面积1/3以上,其中主标题用大号字和强烈对比效果,副标题提供详细信息。
整个图表外围高宽比例在2:1到1:1之间,图例一般在绘图区上部或融入绘图区里面
商业图表多选用无衬线类字体
图表和表格的数字中使用Arial字体、8~10磅大小,中文使用黑体
—————————————————————————————————————
二、“商务范”图表制作实例
(这一章节的16个案例均出自刘万祥老师的Excel图表之道 (豆瓣),该书基于Excel2003)例如要展示利率(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. 漏斗图-利用辅助列占位
最终效果与源数据
绘制方法
添加辅助序列
确认前提是“指标列”从大到小排列
假设<342>这个值在D3单元格,在C3中输入公式 =($D$3-D3)/2 然后拉到底。
选中<公司1>这个单元格到<152>这个单元格区域,做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
-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-横线】
然后设置其他颜色等
—————————————————————————————————————
三、配色方案
1. Nordri设计公司分享的配色方案
Nordri 商业演示设计1-碧海蓝天
2-达芬奇的左手
3-老男孩也有春天
4-路人甲的秘密
5-旅人的脚步
6-那拉提草原的天空
7-香柠青草
8-热季风
9-软件人生
10-商务素雅
11-商务现代
12-数据时代
13-素食主义
14-岁月经典红
15-夏日嬷嬷茶
16-邮递员的假期
17-毡房里的夏天夏天
2. ExcelPro分享的方案
四、自学参考书目和资料
联系客服