Excel 有哪些可能需要熟练掌握而很多人不会的技能.docx

Excel 有哪些可能需要熟练掌握而很多人不会的技能？函数、VBA、图表是Excel的三把利器，希望这篇关于图表的回答能给大家一些启迪。但我最擅长的其实还是前两者。在这我就不放函数和VBA的笔记了。因为，我相信，要想熟练掌握函数与VBA，最好的办法还是借一本入门书，夯实基础，循序渐进。今天这里学一招，明天那里记住一个公式，会只见树木不见森林。比如，Vlookup函数似乎成了新手迈向Excel初学者的第一步，但你可知Vlookup有很大的局限性？如果你借了本书从基础抓起，就会同时掌握Hlookup和lookup，还有更加灵活的Index与Match。就像学了英语之后会发现更大的世界，自从掌握了函数与初步VBA，我也发现了更大的世界，处理数据会有全新的思路，不曾想到过的功能变成现实，像一个程序员一样做出些人机交互的”Excel app”。你会发现，配合函数与VBA，任何重复性工作都可以一键化实现。因为工作中离不开Excel，经常使用，掌握的技能就不会生疏，而是越发触类旁通，稳步提升。所以，如果在可见的未来，你的工作中会用到Excel，我建议你集中一段时间花上100150个小时玩一玩Excel，乐趣无穷。2014-12-9更新图表制作方面的技巧我大多自学于刘万祥的资料，这个回答中的某些图片也来自刘老师。在此一并感谢。刘老师出的书Excel图表之道 (豆瓣)刘老师的博客ExcelPro的图表博客2014-12-8更新感谢大家的赞，但我也知道这回答长了点，所以为节约大家的时间，我选取了一个经典案例，辅以详细的说明（中文版Excel2013）放在开头，供各位参考。看完这个案例之后，我们也可以尝试绘制这种商务风格的表格。下图是我做的，看完详细的绘制步骤，你将深刻体会到“用Excel的元素辅助作图”的含义。首先选中源数据，A到F列绘制散点图，得到经典的Excel风格图表将利润率设为次坐标：选中橙色那根线，右键-设置数据系列格式-次坐标轴删去图表标题、图例，调节横坐标、两个纵坐标的上下限，删去纵网格线，删去两个纵坐标的轴线，得到这样一张图下一步称为“锚定”，鼠标光标移动到下图所示的图表左上角的顶点处，按住Alt，随后按住鼠标进行拖动，发现这样调节图表的尺寸，限定于Excel的网格点。四个角都这样进行调节，分别“锚定”于N7, V7, N15, V15选中图表区域，右键-设置图表区域格式，在属性中选择“大小固定，位置随单元格而变”，这样，在调整Excel行距和列宽时，图表就不会随之而动。在第46行输入内容，设置填充色调节715行行距，使得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. 动态图表115. 动态图表216. Bullet图-竖直三、配色方案1. Nordri设计公司分享的配色方案2. ExcelPro分享的方案四、自学参考书目和资料=正文一、商务图表制作核心理念和方法(这一章节的笔记整理自刘万祥老师的博客ExcelPro的图表博客)1. 突破Excel的图表元素不要仅用“图表”去做图表，而是用“图表所有Excel元素（如单元格）”去做图表。左上图，只有B4单元格是图表区域，标题利用的是B2；B3B5填充浅色，index和data分别在B3、B5。右上图，B2为图表序号，C2为图表标题，填深绿色，B3为副标题，图例放在C4，图表在C5，B2到C5填充淡色，B6、C6合并填写注释。左上图，标题在C2H2居中，图表在C3H3，表格在C6H8。右上图，B2填红色装饰，标题和副标题分别在B2、B3，图表在D4F4，数据来源在D5，标号2为矩形框，整个区域有边框。经常用来配合图表制作的其他excel元素主要有单元格、文本框、线条、箭头等。 单元格DIY图表标题上面四例都是 单元格填色替代图表区和绘图区填色将图表区和绘图区设置无色透明，将单元格设置相应的填充色（在我开头举的案例中有详尽的说明） 单元格控制图表的大小和排列将图表对齐在某个单元格（锚定，上面有说明），则可以通过调整单元格的列宽行高，方便地控制图表的大小。选中图表区域，右键-设置图表区域格式，在属性中设置图表是否随单元格变而变。当多个图表都锚定在某一列/行的单元格上时，就可精确实现图表的对齐，更可方便的批量快速的调整图表大小。 单元格表格DIY数据表上面第三例 矩形框或线条绘图对象来DIY图例2. 突破Excel的图表类型左上图，先用所有数据做曲线图或柱形图，然后选中相应的序列，更改图表类型，有时还需要用到次坐标轴。右上图，先做好面积图，然后将该数据序列再次加入图表，修改新序列的图表类型为曲线图，调粗线型。3. 布局与细节 布局下图从上到下可以分为5个部分：主标题区、副标题区、图例图、绘图区、脚注区。特点有：完整的图表要素；突出的标题区；从上到下的阅读顺序。标题区非常突出，占到整个图表面积1/3以上，其中主标题用大号字和强烈对比效果，副标题提供详细信息。 竖向构图方式整个图表外围高宽比例在2:1到1:1之间，图例一般在绘图区上部或融入绘图区里面 使用更为简洁醒目的字体商业图表多选用无衬线类字体图表和表格的数字中使用Arial字体、810磅大小，中文使用黑体 注意图表的细节处理1. 脚注区写上数据来源2. 图标注释：对于图表中需要特别说明的地方，如指标解释、数据口径、异常数据等，使用上标或*等进行标记，在脚注区说明3. 坐标轴截断标识4. 四舍五入：在脚注区写明：由于四舍五入，各数据之和可能不等于总额(或100%)5. 简洁的坐标轴标签：如2003、04、056. 让Line图从y轴开始：双击x轴，Axis Options-最下-Position Axis-on tick marks7. 作图数据的组织技巧: 原始数据不等于作图数据；作图前先数据排序；将数据分离为多个序列，每个序列单独格式化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 Column3.漏斗图-利用辅助列占位最终效果与源数据绘制方法添加辅助序列确认前提是“指标列”从大到小排列假设这个值在D3单元格，在C3中输入公式 =($D$3-D3)/2 然后拉到底。选中这个单元格到这个单元格区域，做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.8G3单元格=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 Axis11. Water Fall瀑布图最终效果绘制方法D4 =SUM($B$3:B4) F4 =IF(B4=0,B4,0)H4 =IF(B4=0,0,ABS(B4)用蓝色框线区域作图。如果要添加横线。可将“累计”列贴入到图表，改为Scatter图，添加Error Bars12.不等宽柱形图最终效果与原始数据（高度反映ARPU值，宽度反映用户规模）绘制方法/分组细分法-Column图/处理数据如下。每个ARPU数据重复次数为“用户规模”（柱子宽度）数第二五列作图 （左下） 修改宽度和间距【选中右击-Format data series-Series Options-Series Overlap-100-GapWidth-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. 动态图表1File-Options-Customize Ribbon-右边框内勾选Developer这样面板就有Developer栏，单击Developer-Controls-Insert-第一排第五个List Box添加到工作表中右击该List Box，Format Control-Input range$B$8:$B$13Cell link$B$5B5就会显示在List Box里选择了第几个数值B3单元格 =INDEX(B8:B13,$B$5) 横向拉到N3这样当在List Box里选择时，B5单元格显示选择结果，B3:N3就会跟着显示选择结果对应各月的数值以B3:N3作图15.动态图表2以下图为例。B5