精通Excel的体验.docx

精通Excel的体验.docx

《精通Excel的体验.docx》由会员分享，可在线阅读，更多相关《精通Excel的体验.docx（21页珍藏版）》请在冰豆网上搜索。

精通Excel的体验

精通Excel的体验

总纲:

Excel操作上,第一步是对数据进行清洗,去除不合规格的脏数据,将数据调整成整齐合理的格式.然后添适宜的数据辅助列,补充数据维度.最后是将处理好的数据以美观的图/表形式向他人展示.最高级的功能叫做VBA

VBA的使用非常灵活强大,不是几句话能说清楚的。

Excel招式繁多,先给各位施主一个直观印象.一般止于二级菜单.重点功能用星号标记:

章一.基础操作/Shortcuts

基础操作中的入门法门是数据整理.这个是最基本的柱础,腰马合一,力从地起.但很不幸的,大多号称精通Excel的少侠们尚未具备这个意识。

原始数据一般都长成这样。

这是个糟糕的数据样本,但是还不是最糟的.从不同的人手里收集原始数据的时候,这种情况特别常见.

好的数据格式是:

世间任何功夫都是由浅入深,循序渐进,数据整理就是其中最基本最重要的入门招式。

不过入门招式，往往也意味着很辛苦，别无捷径，唯手熟心细尔。

数据整理之起式:

清洗

吾宗神秀大师有云:

身是菩提树，心如明镜台，时时勤拂拭，莫使有尘埃.

心需拂拭,同理,数需清洗.使其平熨齐整,利于后续使用。

清洗的对象,简称脏数据.一般有如下几种情况:

1.同名异物:

例如公司里面有两个李明,如果不加区别地导入数据并进行合并统计,可能就会出问题。

2.同物异名:

例如性别,有的人写成男女,有的人写成M/F,有的人干脆写成0/1。

3.单位错乱:

例如金额,人民币和美元一旦混同,那绝对是一场灾难

4.规格不合:

例如身份证号为9527。

5.格式混乱:

最典型的就是日期!

例如10/6/11,根本说不清楚是11年10月6日,还是11年6月10日,抑或是10年6月11日,因为美式日期,英式日期,中式日期各自都不相同!

另外一种是分位符.美利坚的分位符是","而欧罗巴诸国的分位符是"."如果是一位叫Chateaubriand的美国同事发过来一个数字"123.456",根本说不清楚这是一百挂零,还是十万有余.

假使少侠天资过人,心如明镜,眼疾手快,刷刷刷刷检出来"123.456"个脏数据,怎么处理呢?

数据整理之承式:

规制

做数据之前,先要和其他人协商好,各个数据都是什么格式,不同数据表之间的格式是否要统一,之间是否有依赖关系.如果数据不满足依赖关系如何处理。

例如先约定好,性别一律写成"男/女".如果写成M/F的,那么M就当成男性,F就当成女性来处理（使用替换,或者使用中间映射表）.写成Nan/Ny的,直接当脏数据抛弃掉。

数据整理之转式:

分组

在数据预处理中,分组是一个很重要的手段,例如各位少侠要面对的是本公司的工资表,想看看整体是否失衡,可以将资历分为中低高三组（日企）,对应人员的工资进行汇总;但具体资历分层的节点的把握,则需要小心,必要时还需要反复尝试.例如可分成

a.工作1年以下，

b.工作1年-3年

c.工作3年-5年

d.工作5年以上

跑出来一看,wow,公司是大学生创业基金支援的,全部员工都是工作1年以下......

这个时候就得按更细粒度的月来进行划分了。

数据整理之合式:

聚类

聚类则更灵活,例如最早登记报册的只有员工的姓名工号,乱糟糟一大把,业余活动组织不起来怎么办?

这个时候找IT要一下各人上班的时候的浏览网页,从网页记录推算一下各人爱好,然后按照爱好进行聚类,变成篮球俱乐部,羽毛球娱乐部,DOTA俱乐部.....这以后的工作就好开展了。

上述四种,强调意识,不限于方法。

-------------------------------

对于Excel表格格式,不单单是因为美观整齐,表格格式还集成了筛选,排序,甚至冻结窗格的功能。

在表格格式内别有乾坤:

然后说说神鬼莫测七招式:

数据工具

先说说分列.这种密集大魔王造出的数据,大家想必都很常见吧.贴到Excel里面还依然是密密麻麻令人头晕目眩.

只需将之选中,使用"分列"式:

Duang（此处应该有掌声）

接下来是"速填"式,说实话,这是个相对比较无聊的招式.如果功力达到第二层,使用公式函数,取而代之乃是易如反掌.

不过既然至此,继续用上面的例子:

从"代码缩略"下面一直选到底,点击"快速填充".Duang:

删除重复项实而不华,特别推荐.非常简单,不赘述.

数据验证意正身正,实用,略繁琐,多人协作时很推荐.不赘述.

合并计算和"快速填充"有点相似,如果功力递进到第四层,使用数据透视表来取代之简直是易如反掌.且便捷不止十倍.先不详述.

模拟分析是堪称第一层的屠龙绝技,看似厉害,其实用处很小.而且这个模拟分析和公式/函数关联极大,留待第二章详述.

关系则是一种高不成低不就的招式.不能说没用,但它的功能呢,其一可以使用辅助列取代,其二可以使用vlookup取代,其三可以使用数据库取代.等说到数据透视表的时候再详述不迟.

中级法门是数据导入,早期数据清洗什么的,早在入库前就已经有人替你完成了,便当之至:

连个SQLserver看看:

能练到这一级,少侠,你功力已然不浅了.想必SQL这种中等功夫你也有过粗练.

（没练过的看这里:

SQL基础教程语句汇总）

至于MDX这种小无相功,功力也不在Excel五轮心经之下,但修习全凭机缘,先不多讲了.

（好奇想练练的看这里:

MDX的基本语法及概念）

注意事项:

Excel初级法门中有一道奇毒,名为"合并单元格",想数度往生极乐,多缘于"合并单元格"之手.作为原始数据,尽量不要使用"合并单元格",这个功能在后续处理数据的时候会带来大量的麻烦.足以令人呕血三升."合并单元格"一般是在最后一步,确定数据不再修改的时候才可以使用.

另外和别的人协作处理数据的时候,最好将处理好的原始数据和呈现数据一起提交给他人,方便他人未来进一步修改.

至于哪些Shortcuts比较重要,个人觉得是单元格位置的操作,能避免在万千数据里频繁地拖动滚动条.

------色想受行识分割线------

章二.函数/公式

此二者非常容易混淆,画张图比较容易说清楚:

只要在上方公式区输入的,等号之后的内容,都属于公式（绿色）.

而函数（红色）,则是后面带一对括号的那些内容.

这一节的初级要点是熟悉Excel现有的函数库

其中比较普世的是以下四类:

数量不多,经常去[该网站因为政策法规不予展示]上搜一下,就知道用法了。

这个常用函数里面有一个人气堪比AKB48的,那就是VLOOKUP（以及他的妹妹HLOOKUP）

VLOOKUP其实就是建立两个表的关联,将B表的内容,自动导入到A表:

-----------断见取见-----------

中级要点是如何将这么多函数做成一个复杂的公式。

复杂的公式,核心就是函数的嵌套,函数里面套函数,招中有招,直至八八六十四招.如长江大河,滔滔不绝.函数的嵌套最多可以套64层（Excel2013,从前Excel2003-2007为最多套7层）.函数的总字数长度可达恒河沙数（记不清具体数量的时候就暂时说恒河沙数）。

下图这个例子就是一个简单的多层嵌套,主要是if逻辑上的嵌套。

不过心有五蕴,人有三昧,简言之就是地球人还没有进化成三体星人的形态,嵌套的数量一多,就会令人心毒盛起,头晕目眩,前列腺紧张,根本看不清楚自己在写什么.

臣子恨,何时雪?

怎么办?

中间列!

这回举一个实用的例子,个税计算:

正统的个税计算算法是这样的:

写成公式是这样的:

把公式摘出来给各位欣赏一下:

=IF（[月工资]-3500<=0,0,IF（[月工资]-3500<=1500,（[月工资]-3500）*0.03,IF（[月工资]-3500<=4500,（[月工资]-3500）*0.1-105,IF（[月工资]-3500<=9000,（[月工资]-3500）*0.2-555,IF（[月工资]-3500<=35000,（[月工资]-3500）*0.25-1005,IF（[月工资]-3500<=55000,（[月工资]-3500）*0.3-2755,IF（[月工资]-3500<=80000,（[月工资]-3500）*0.35-5505,IF（[月工资]-3500>80000,（[月工资]-3500）*0.45-13505,0））））））））

看到这个公式是不是感到口干舌燥,头晕目眩,前列腺紧张?

但如果使用中间列,将公式拆解,每个子部分做成一列,就会立刻神清气爽:

最后将不需要的列隐藏起来,Missioncomplete~

然后该说说模拟运算了.

公式可以拖动,其中参考的单元格在拖动的时候位置也会变动.下图就是正在拖数据的瞬间:

一松手:

这种拖数据,虽然很简便,但也有一个问题,就是只能向着一个方向拖,或上或下,或左或右.假设现在有一个数据要求,有两个变量,相当于让你同时向下向右拖动怎么办?

例如不同利率,不同年限下房贷的问题（这真是一个令人悲伤的例子,施主请看破红尘吧）:

普通公式也可以做到,但是就是需要向右拖N次,或者向下拖M次.等你拖好,妹子已经下班,和别人一起吃麻辣烫去了.

模拟运算则可以一下子把这个6*6的结果全算出来.

操作很繁琐,接下来的内容请点赞,给施主增加信心:

先在左上角放一个本息合计公式:

然后选中所有的可变利率及可变年限:

然后选择"模拟运算表"

点击确定之后就可以Duang了:

唉,这果然是一个令人伤心的例子.

--------阿耨多罗三藐三菩提--------

高级要点是如何自定义一个函数.

刚才的例子,为了计算日期对应的季度,使用了一个漫长的公式.现在看看这个,一个函数就直接命中靶心,赏心悦目~

这个Quarter函数,少侠的Excel里面是找不到的,因为这是自创.它的真实面目是这样的（感谢@黄老邪的提醒）:

没错,这就是第五层心法乾坤大挪移第一级,也就是VBA.

--------六道轮回分割线--------

章三:

图/表

各位善男子善女人久等.今天为各位解说五轮真经的第三层,又称无上正等正觉图形图表经.如是我闻:

第一级:

表格

主要入口在这里:

也可以使用这个:

表格创建完成后,点击表格中的内容,会出现一个新的密法空间:

这些东西都是干嘛的呢?

首先是表名称.子曰:

“名不正,则言不顺;言不顺,则事不成;事不成,则饮西北风".达尔文在加拉帕戈斯群岛发现的奇行种生物程序猿,对名称就非常关注,程序猿对名称的关注主要是认为能方便后续使用.更直观,也不容易出错。

以上图出现过的公式距离

=VLOOKUP（[对应级数],个税速算表,4,FALSE）

"个税速算表"就是一个表格的名字,这样的话选择范围就不是一个类似于=D30:

F37这样很难记忆的字符串,而变成一个非常容易理解的对象.而[对应级数]这种列名也一目了然,如果不加命名,就得换成=F22:

F26,还要考虑绝对地址和相对地址,非常麻烦。

起个好名字就成功了一半哦~

切片器:

切片器诞生于2010年.其实就是一种更美观的筛选。

那筛选又是什么:

筛选一共只有两路18式.属于最简易的功夫,但是日常防身非常实用,希望各位有时间能多多操演,不过没时间的话,老板也会逼着你天天操演,所以这个不必多解释.

此外再说一下表格的另外一个好处:

生成透视表特别方便.

如果是普通数据,如果想要生成数据表,必须全部选中:

但如果是表格的话,随便选中表中任意一个单元格,即可开始操作:

既然事已至此,顺道说一下条件格式这个惠而不费的功能,自己使用还是给他人展示都非常美观:

下面做个集大成的演示

--------阿赖耶识分割线--------

图表经第二级图表

图表主练手少阳三焦经,内力不深,招式繁多.以愚见,这级偏向华而不实.不过既然至此,依旧为诸位善男子善女人逐一解说.

图表共有十式,常用者六,不常用者四.看起来招式并不多对吧?

但其中每一式下可能有若干变招,故而常见的总数是:

52式（自定义式未计入）

常用图式,可通过Excel上面的tips来理解（将鼠标hover在某图式上就可以看到）:

后面不太常用的四式可以稍微详细点说说.

--------诸行无常分割线--------

散点图

散点图是展示两变量关系强弱的图形.

举个蒸栗（正例）.一个国家的人均寿命和该国家的人均GDP有没有关系呢?

找了一份2014年的公开数据（不一定完全正确哦）

就用它作图（千万注意,否则图是画不出来的）:

Duang:

能看出来点规律吗?

似乎不是很容易对吧.喝!

目下才是真正显示手腕的时刻!

再看一遍:

看来钱可通神这四个字果然是有些道理.

刚才的例子也表明,真正控制图表的,不在图上,而是在属性格式里.

那再举一个例子,民主指数和人均GDP（购买力平价法）之间的关系:

咦,这个相关性就要差一些了.有很多有钱的一点也不民主,但民主的基本还算有钱.

散点图只是一个半成品,它不能显示数据所属的"系列",结果就是上面这一大片圆点,你是无法直观识别每个点是属于谁的.

这个时候就必须配合第三方标签工具来完成:

例如XYChartLabeler

顺道再说一句,如果施主打算自己也写这么个XYChartLabeler,功力需要达到第五层心法的第二级,也就是VSTO.掌握了VSTO,Excel世界的大门就算正式打开了,理论上就没有什么能够阻止施主了.

泡泡图

这是散点图的一种变招,散点图只能选取两列,而泡泡图必须选取三列,第三列就是用来计算泡泡面积的,继续用上面的数据做例子,我们不但要看看民主指数和人均GDP的关系,还得看看这个国家的总体量,省得被一群小国忽悠:

这三列全都选中,然后选择泡泡图:

结果如何,各位不妨亲自试试^_^

--------四无量心分割线--------

雷达图

雷达图主要是对两个（多个）对象的多个属性进行综合比较的时候使用.一般来说越圆,各项指标就越均衡;圈的面积越大,综合实力就越高.

注意事项:

属性值作为行（hang）,对象作为列.这样默认就能输出正确的雷达图.（样例数据来自汽车之家）

好,出图了:

如果数据写成了这样:

那默认出图就会是这样:

但其实也没啥问题.这时候需要右键点击图片:

选择数据

然后切换一下行列就OK了

选择数据是非常基本而关键的知识点.万望各位施主对此能提高重视.

-------------------------------

曲面图

曲面图主要是表现什么呢.......个人理解是..........这个主要表现z=f''（f（x）,f'（y））这样的计算式（这完全不是中文好吗?

就是不知道中文怎么讲啊!

!

）

曲面图和"模拟运算表"可谓是天生的一对,地造的一双.什么?

你已经忘了什么叫"模拟运算表"?

那其实也没啥关系,只要你不是科研/教学人员,那曲面图和模拟运算表在实际工作中,遭遇到的概率小于万分之一。

不过行文至此,图还是要上的（背后的数据就是使用模拟运算表得来的）。

是不是很酷炫?

还能3D旋转哦.但这个图究竟能干啥呢?

-------------------------------

股价图

顾名思义,就是做出股价分析的图表.

画一张还是可以的:

这张图对应的数据是这样的:

最多5列,分别是交易量,开盘价,高点,低点,收盘价.顺序不能随便改哦.

预祝炒股的各位同学今年好收成.

-------以下是干货分割线-------

关于图表的干货终于要来了!

那就是怎么把图表变漂亮~追求美乃是人之天性,但做浅说.

请看标记的部分,再加上空白的大背景,一般而言能控制的便是这些.想要变漂亮就要从每一个标记点上做道场:

一旦开始对图表进行操作,千万不要触碰快速布局和图表样式模板哦,否则:

然后就要确定一个所谓美观的范例.如果不知道猫长什么样,笔下又怎么可能画出一只猫呢?

不知道漂亮的Excel图表是什么样子,那即便对所有的操作无比精熟,又怎么可能做出一张漂亮的Excel图表呢?

（A同学默默地交给一张图:

我觉得这张图很帅,就要这样的吧）

............

花了半天功夫,终于找到了一个合适的示例:

电影票房火箭飞升,虽然立意不高,但是配色看着还挺喜庆~

这里没有原始数据,所以绘图不能100%复刻.先把已有的数据列出来.

然后标记上对应的颜色（需要使用第三方取色器,使用的是QQ截图......）

来画个八维太极圆环图吧（饼图的变招）

好像哪里不对,这和原图没有任何相似之处啊.

切换一下行列（忘了如何切换行列的请往前翻）:

好像有点意思了,不过这也太丑了.丑得让又开始思考苦海无边,回头是岸的道理了.....

但这就是Excel自带模板的真面目......

好,图表美化经中真正的大招来了:

天罡地火辅助列!

!

!

为了弥补Excel自带模板的先天不足,制图时,很可能需要添加一些数据,来实现美观的效果.

原有数据（提前切换了行列）添加辅助列之后变成这样:

至于辅助列里面的数据怎么来的,很遗憾地告诉大家,是随意编造的.这个其实需要一些尝试和反复修改.

请看大图!

似乎更丑了.......

吃包辣条冷静了一下,觉得问题主要是图中每个环都瘦比飞燕,如果变成杨玉环想必会漂亮得多.那就来试试吧:

于是乎:

眼尖的同学是不是可以看出一点眉目了?

接下来就简单了:

1.去掉每个环区的边框,使其彼此紧凑.2.扇区起始角度顺时针移动30度.3.将辅助列的部分改成"无填充"

填上一个底色让大家看清晰一些:

是不是有点意思了^_^

然后就是给每个环区赋上颜色.看结果吧:

然后添加文本说明（只加一个示意吧,迪塞尔的光头照请各位意会）

和原图比较一下:

是不是感觉有几分神似?

-------四种清净分隔线-------

第三层表格/图表的主要内容都说得差不多了.迷你图很简单,一试便知.今天剩下的时间,就讲讲第三层另一个屠龙神技吧:

般若白象功PowerView.

使用这个功能,能生成所谓的动态图表,怎么个动态法呢?

就是选中某个数据系列或者筛选项时,对应的数据系列会变色/高亮.

还有就是使用一个切片器（还记得这是什么吗?

）可以同时控制多个图表（即所谓的联动）

当然如果真的有这种需求存在,那PowerView应该还是有点价值的.属锦上添花的作用,最后都是用VBA解决的......

另外一个略有用的功能就是,PowerView能根据地名自动绑定Bing地图.

伪造了一份美国各州人口表（使用了Randbetween函数）:

将这两列选中后,点击"PowerView",这个时候就可以去泡咖啡了.

大概两分钟吧,终于生成了一个全新的工作表（Sheet）:

这个还是挺方便的.

但是从前见过的地图,一般要求画成这样（这个当年也是用VBA解决的）:

所以称之为屠龙神技,还是有自己的道理的.顺便一句,PowerView的功能,是用silverlight实现的（可以理解成微软家山寨flash）,而silverlight已经被微软判了死刑........春草碧色,春水渌波,送君黄泉,伤如之何,伤如之何,阿弥陀佛........

（至于怎么画上面的图,可以参见的另一个答案:

excel上怎么做数据地图？

-靳伟的回答）

第三层真经讲解完毕.不日更新第四层.

--------正理因明分割线--------

章四:

数据透视表

数据透视表（pivottable）这个]翻译比较古怪.不过名称不是大问题,只要理解数据透视表能做什么即可.

数据透视表是一种简易报表,可以对不同的数据行列进行数据汇总.

数据透视表的入口在此:

生成了Pivottable之后的主要控制区:

开始举例,这回还得请出王二和李明来:

此时前世孽缘来了,一个自称是老板的人,让你算一下李明和王二现在卖出的东西的平均价格是多少.

用公式sumif是可以实现的,但现在有更好的办法来了.

选中这个表格,插入数据透视表:

然后输入一个计算字段（计算字段CalculatedField是Pivottable中的重点功能,要着重注意）:

然后在右边拖一拖:

我们用Sumif核算一下

看来没什么问题^_^

这时候老板又发话了,按照颜色和销售人员各统计一下总销售金额.（高达八成的老板都是这样）怎么办呢?

很简单,再拖一下:

就是这么方便,就是这么任性!

老板继续发话,"你这么搞完全没有理解我身为老板的一片苦心!

我是让你制作两个表,一个统计人员,一个统计颜色,然后给我一个过滤表单,这样我可以按日期看人员和颜色的变化趋势."

这个说来很简单,只需将Pivottable整个圈中,复制黏贴,然后改一下字段即可.

过滤项呢,也是将字段拖入到筛选器即可:

但现在有两个pivottable,是否有方法同时操作两个pivottable呢?

有,那就是切片器（从前也提起过哦）

选中一个Pivottable,添加一个切片器。

添加切片器之后,右键选择"报表连接",继续添加连接的pivottable,两个都选中:

这样,用这个切片器,就可以达成一个切片器来控制多个pivottable的目标:

顺道说一下,"日程表"也是切片器的一种,只不过外观是特别优化过的罢了:

眼尖心细的少侠会发现,在PivotTable操作中,有几个选项一直是灰色的,例如:

还有:

这是因为它们都需要特殊的奇门兵器和外道功夫:

由于近来深研佛法三宝（合称PPT）,一时没有准备OLAP真经,所以这节暂时跳过,待机缘成熟再来补完。

至于PowerPivot,各位可以直接视之为"不服跑个分儿"版的数据透视表.严格点说,它预期起到的作用是简易的数据库（例如Access）,而工作方式比较像数据透视表。

另外想要在Excel中突破一张表最多100万行的限制,也得仰仗这位的大肚能容.在PowerPivot中,一张表的最大行数为20亿行.详情请见:

PowerPivotCapacitySpecification

但还是认为,如果必须应对上亿行的数据,学习一下数据库--例如SQLserver,Oracle,MySQL--是很有必要的,好过使用这个PowerPivot.所以这个PowerPivot暂不深表.

数据透视图和普通的图表几乎没什么不同,只不过能和一个数据透视表彼此联通,控制表的同时,可以影响到图的展示内容.不作为重点.

这一章通常来说,最常用的还是计算字段和计算项.望勤为操演.

余者待重新准备一下,来日方长.

--------众因缘生法分割线--------

章五:

VBA/VSTO

痛感逝者如斯,不舍昼夜,因而决定提前讲说章五.也就是乾坤大挪移心法.

请各位注视自己的Excel,是否能找到我神功入口?

找不到也正常,毕竟是奇门秘籍,一般都藏之名山大川,幽谷白猿之中.请从这里找寻:

"开发工具"一定要选中才行.

这个里面常用的又是"代码"和"控件"