这是一本有趣的Excel书籍,基于卢子的职场经历改编而成!通过聊天的方式讲解,让你在轻松、愉快的环境中学到各种技能。
享受Excel,远离加班,享受更多生活,享受更多爱。跟着书中主角卢子前行,所有Excel疑难,所有阻挡你快点见到爱人、见到孩子的数据、表格问题都将得到解决。
全书共分为9章,主要内容包括Excel使用习惯、数据录入技巧、数据分析排列技巧、*有价值的函数与公式及使用技巧、数据透视表技巧、SQL语句技巧、Excel数据分析经验、Excel娱乐、Excel*高境界——无招胜有招。
本书能有效帮助职场新人提升职场竞争力,也能帮助财务、品质分析、人力资源管理等人员解决实际问题。
Excel效率手册——早做完,不加班 (It部落窝论坛、中国会计视野论坛联袂**,不再被表格虐个千百遍)&nbsp_清华大学出版社_清华大学出版社_

**章养成好习惯
1.1了解Excel的四类表格2
1.2以身说法7
1.3注重细节12
1.4本章小结14
第二章向有经验的人学习
2.1再当一回学生16
2.1.1初识Excel16
2.1.2这条线怎么画16
2.1.3边框怎么显示不全19
2.1.4输入的0跑哪去了20
2.1.5分数怎么变成了日期22
2.1.6生成编号序列22
2.1.7不相邻单元格输入同一数据24
2.1.8快速录入大量小数26
2.2偷师27
2.2.1输入多个0有技巧28
2.2.2录入特殊字符29
2.2.3只允许输入某范围的日期31
2.2.4防止录入重复姓名31
2.2.5数据有效性其实很好骗33
2.2.6眼见不一定为实34
2.2.7所见即所得35
2.2.8哪里不同刷哪里36
2.2.9将两列的内容合并成一列37
2.2.10将行变成列37
2.2.11给单元格加把锁38
2.2.12转换成PDF,放心将文档交给供应商40
2.3本章小结40
第三章常用小技巧
3.1排序42
3.1.1排序其实很简单42
3.1.2按城市的发展情况排序43
3.2筛选45
3.2.1筛选中的搜索功能45
3.2.2将带颜色项目挑选出来46
3.2.3借助**筛选,让筛选数据更贴心47
3.3分列49
3.3.1按分隔符分列49
3.3.2将姓名分成两列显示50
3.3.3将不标准日期转换成标准日期51
3.4零散小技巧52
3.4.1闪电式数据填充52
3.4.2删除错误值54
3.4.3让数据输入更简单57
3.4.4重复值问题58
3.4.5套用表格样式60
3.5本章小结62
第四章*受欢迎的函数与公式
4.1一起来学习函数与公式64
4.1.1基本概述64
4.1.2初识函数66
4.1.3快速、准确输入函数的两种方法67
4.1.4输入公式三招68
4.1.5三种不同引用方式69
4.1.6两种不同引用样式70
4.1.7借助名称让公式更容易理解73
4.1.8了解另外一片天地——数组公式74
4.2函数经典再现77
4.2.1闲聊SUM78
4.2.2求和**SUMPRODUCT86
4.2.3既生SUMIF(COUNTIF),
何生SUMPRODUCT90
4.2.4无处不在的IF95
4.2.5学VLOOKUP,认识Excel爱好者98
4.2.6LOOKUP潮汕105
4.2.7经典的INDEX+MATCH组合112
4.2.8OFFSET的运用117
4.2.9百变神君TEXT120
4.2.10字符提取MID、LEFT
和RIGHT三兄弟124
4.2.11SUBSTITUTE的运用127
4.3为自己量身定做的函数129
4.3.1连接所有字符LJ130
4.3.2将借阅的书籍合并HB133
4.3.3提取批注内容PZ135
4.3.4自动录入当前时间NowTime136
4.4本章小结138
第五章看透数据的数据透视表
5.1多变的要求140
5.2跟领导聊聊数据透视表142
5.2.1什么是数据透视表142
5.2.2多角度分析数据143
5.2.3更改值的汇总依据147
5.2.4排序让数据更加直观148
5.2.5原来手工也能排序149
5.2.6对销售额进行排名150
5.2.7筛选销售额前5名的客户151
5.2.8让更改的数据源随时刷新151
5.2.9插入表格,让数据源“动”起来154
5.2.10日期组合真奇妙155
5.2.11手工组合,实现客户分级158
5.2.12善借辅助列,实现客户实际销售额分析160
5.2.13利用数据透视图让分析更直观160
5.3惬意的生活165
5.4本章小结166
第六章神奇的SQL语句
6.1系统是浮云168
6.2SQL是神马168
6.3试探性查询170
6.3.1认识SQL的储存地170
6.3.2查询所有记录174
6.3.3查询不重复客户175
6.3.4查询符合条件的客户176
6.4有目的统计177
6.4.1统计猪肉金额177
6.4.2统计批发跟零售金额179
6.4.3统计产品用量180
6.5强强联合,见证神奇181
6.5.1进销存分析181
6.5.2资金流动分析184
6.6本章小结186
第七章学E千日,用在一时
7.1年度数据分析188
7.1.1数据核对189
7.1.2数据汇总191
7.1.3数据分析195
7.2本章小结200
第八章在娱乐中学习
8.1模仿手机功能202
8.1.1手机号码的显示格式202
8.1.2姓名简称203
8.1.3防骚扰204
8.1.4联系人去重复206
8.1.5智能拨号207
8.1.6QQ通讯录开启人员208
8.2其实我是算命的209
8.2.1戏说生辰八字210
8.2.2星座学211
8.2.3生日密码212
8.2.4面相214
8.2.5点菜无烦恼215
8.2.6别用你的无知挑战概率216
8.3不会数学计算,Excel来帮你217
8.3.1数学基本计算217
8.3.2解方程218
8.3.3预测数字221
8.3.4随机抽样223
8.3.5利润*大化224
8.4本章小结226
第九章用合适的方法做合适的事
9.1简单就好228
9.1.1粘贴成图片,排版无烦恼228
9.1.2计算文本表达式231
9.1.3利用排序生成工资条232
9.1.4多表数据核对234
9.1.5多行多列提取不重复235
9.1.6逗号分隔符238
9.1.7提取数字240
9.1.8多表关联243
9.2聊聊跟Excel有关的事儿247
9.2.1不知者无罪247
9.2.2假如生活就是Excel253
9.3本章小结254

**章养成好习惯
良好的开始等于成功了一半,在开始学习Excel之前就要养成好习惯。了解规范数据源的重要性,这样会给以后的学习带来极大的便利。
了解Excel的四类表格
在讲Excel之前先了解一下Access的三类表格。有些人或许会疑惑,不是说Excel吗,怎么扯到Access上面去了呢?其实,Excel跟Access本是一家,两者有很多地方都是可以相互借鉴的,只要对我们有好处就要去学。在开始学习Excel之前,了解一下Access是有好处的。
下面是MrExcel说过的话:
Access就是严格自律、修炼成仙的Excel,虽然少了Excel的随意和灵活,但在某些方面的功力却远非Excel能比,如多表联合分析、数据参照完整性、级联更新和删除、对数据的操作和查询、宏、界面设计,以及和Excel的**整合能力等,所有这些给我们的工作带来了极大的便利!
这里涉及两个关键词:严格自律和多表联合分析。严格自律一般指对明细表要求很严格,而多表联合分析是指明细表通过跟参数表关联而获得汇总表,从而可以更好地分析数据。
下面一起来认识各类表格吧。
参数表
产品清单又名参数表。前期设置好后,有新产品就添加进去。模式基本上不会变动,只要输入规范就行,只起到引用的作用,如通过番号查找品名或者型号,平常接触的概率很低。图1-1就是一张参数表,了解一下就可以了。
图1-1?产品清单
明细表
图1-2所示就是一张每天生产产品明细表。
图1-2?明细表
这是一张极为普通的明细表,但里面包含了很多信息。
每一列都有标题,但标题无重复,没有多行标题。
同一列为同一数据类型,各列数据格式的规范统一。
没有合并单元格。
各记录之间没有空行、小计与合计行。
表格纵向发展,行数可达几十万行,列数控制在10列以内。
通过上面5点,我们可以清楚地知道,Access有很多限制,就如一个人做事情,不管做什么都很随便,别人说他还反驳:差不多就可以啦!生活中的“差不多先生”很多,但这些人普遍混得不好,没啥成就。相反,一个做事认真、严格要求自己的人,往往会得到上级的重视和同事的尊重。
我们平常80%的时间都在与数据打交道,所以需要特别重视。说这么多,只是想让更多人从一开始学习就能意识到这个自律的问题,这样才会给以后的学习带来更多便利。Excel中有一个处理数据的利器——数据透视表,当数据源规范时,用它来处理数据将十分方便、简单,取Access之长补己之短,Excel将越来越强大。
数据类型是一个挺重要的东西,经常会接触,需要好好了解。
数据类型可以分成两大类:文本和数值。产品属于文本,金额属于数值。当然日期也属于数值,日期是一种特殊形式的数值,如图1-3所示。
图1-3?产品记录表
还可以细分为:数值、货币、日期、分数……说白了,就是自定义格式的分类,如图1-4所示。
图1-4?数据类型
汇总表
汇总表可通过明细表跟参数表关联“变”出来,至于怎么关联以后会讲到。如图1-5所示,这类汇总表是*基础的,只根据一个要求汇总。
图1-5?汇总表
在Excel中汇总表包含两种形式:一种是固定模板。如图1-6所示,事先设置好模板,不允许改动,这时就得想办法设置公式来将数据源引用到总表。
图1-6?固定模板汇总表
另一种汇总表是不做要求的,可以灵活变动,如图1-7所示,但要考虑以下两个问题。
体现目的,一定要将说明的主要内容体现出来。
要容易汇总数据,可适当引用过渡表的数据。
通过上面的初步了解,我们知道Access中有三类表格,分别是参数表、明细表和汇总表。汇总表可以通过其他表格关联获取,不需要我们自己录入数据。既然Access是修炼成仙的Excel,那我们就得向Access学习,从一开始就有三类表的概念,将Excel当系统用。
图1-7?灵活变动汇总表
过渡表
其实Excel中还应该存在一种表格——过渡表。很多时候,通过明细表并不一定能够直接得到汇总表,得经过一系列的过渡才能真正转换成汇总表。如图1-8所示,过渡表的作用就是起到统计汇总表需要的某项信息,如猪肉生产金额。
图1-8?过渡表
有人也许会说,被你说得天花乱坠,我却越听越迷糊了。没事,等下就会明朗了。
先来认识四类表格的示意图,如图1-9所示。
图1-9?Excel的四类表格
明细表(原始数据)通过引用参数表的数据,经过一系列加工就可以变成汇总表。
也许你还不是很明白,别急,继续看下去。让我们当一回家庭“煮男”。
原材料:四季豆、虾仁;
配料:油、盐、蒜头;
目标:鲜虾四季豆。
俗话说:巧妇难为无米之炊。要做虾鲜四季豆,首先就得有四季豆、虾仁,但光有原材料肯定是制作不出虾鲜四季豆来的,还需要从厨房找到一些油、盐、蒜头。原材料、配料齐全了,就得经过一系列的加工:
锅里加水加盐、煮开;
将四季豆摘洗好,下锅;
再将四季豆出锅,冲洗过冷水;
……
一道虾鲜四季豆完成。
原材料(明细表)加上从厨房(参数表)挑选合适的配料,经过多次加工变成鲜虾四季豆(汇总表),而每次加工都只是临时的(过渡表),目的就是制作鲜虾四季豆(汇总表)。
重新回到Excel中,认识一下这些表格的关系。
明细表就是日常登记数据详细信息的记录表,因为每个公司对产品都有一定的编号管理,所以存在一部分参数是固定对应的。为了节约登记时间及频率,我们会尽量减少登记内容。在明细表中手工录入产品名及数量,由于每种产品的单价及类型基本固定,这样就可以设为参数表,直接引用就好。*后根据明细表中所列详细内容按要求求出自己所要的结果,即为汇总表,如图1-10所示。
图1-10?产品表
说了这么多,只是为了让你知道,表格之间是可以互相关联的。理清四类表格,从一开始就养成好习惯,设置好参数表,规范明细表,善用过渡表,*终让领导看到满意的汇总表。
以身说法
成长是需要付出代价的,多从别人那里获取失败的经验,这样你就会少走很多弯路。闭门造车是不可取的,否则终将付出惨重的代价!
2010年初卢子接手中山隆成那边的工作,中山隆成有事先设置好的表格模板,如图1-11所示。像这种二维表格经常会见到,好处就是录入数据简单。那时没有考虑到以后要对数据源进行汇总分析,也就没去重新设置表格模板。虽然表格存在很多不合理,如合并单元格,因贪图一时之便,后来害苦了自己。
图1-11?中山隆成模板
领导要卢子对不良数据进行汇总,好查看每个月各类产品的出货检查情况。通过这张明细表进行汇总,得到这样一张表,如图1-12所示。
通过明细表获得这张汇总表,对当时的我来说难度不亚于上青天。为了应付领导,我利用了各种转换方法,费了九牛二虎之力才勉强做完,效率极低。
为了寻求更简便的方法,我苦思一周无果。
图1-12?汇总表
不得已在网络上各处求助,终于皇天不负有心人,在wangjguo44老师的帮助下完成了这项艰巨的任务,在这里对他老人家说一声:谢谢!
我晒出其中一条公式,有兴趣的朋友可以研究一下。
=INDEX(小天使!G:G,RIGHT(TEXT(LARGE(MMULT((LOOKUP(ROW($5:$136),IF(小天使!$B$5:$B$136<>"",ROW($5:$136)),小天使!$B$5:$B$136)=$B$4)*(MONTH(小天使!$I$4:$DJ$4)=$F$4)*(小天使!$G$5:$G$136<>"检查数")*(小天使!$G$5:$G$136<>"其他")*小天使!$I$5:$DJ$136,ROW($1:$106)^0)+(LOOKUP(ROW($5:$136),IF(小天使!$B$5:$B$136<>"",ROW($5:$136)),小天使!$B$5:$B$136)=$B$4)*(小天使!$G$5:$G$136<>"检查数")*(小天使!$G$5:$G$136<>"其他")*(小天使!$G$5:$G$136<>"")*0.1+ROW($5:$136)%%,ROW(A1)),"0.0000"),3)*1)
不知道你看到这里有什么想法?不过我可以肯定地告诉你,工作上不应存在任何炫耀技能的行为,公式越长,只能说明你的表格设置越不合理。这就是我前面花那么长的篇幅介绍Access的原因,其目的就是学会规范数据源。故事的结局是,我狠下心来对这张数据源“做手术”,将它变成一张标准的一维表格,然后用数据透视表轻松搞定,其结果如图1-13所示。
图1-13?转换后的效果
VBA转换代码(好友无言的人提供):
OptionExplicit
PublicSub二维转一维()
??DimArr,Brr(),iAsByte,iiAsInteger,TAsLong
??DimiRAsLong,iCAsByte,iRCAsLong,iTemAsLong
??Arr=Sheets("小天使").Range("A4").CurrentRegion
??iR=UBound(Arr):iC=UBound(Arr,2):iRC=iR*iC
??iTem=1??'计数器
??ReDimPreserveBrr(1ToiRC,1To9)
??
??Fori=9ToiC
????Forii=2ToiR
'??????Ifi=113Andii=80ThenStop
??????Brr(iTem,1)=iTem???????????'序号
??????IfArr(ii,1)<>""AndArr(ii,1)<>"合计"Then
???????Brr(iTem,2)=Arr(ii,1)????????'番号
???????Brr(iTem,3)=Arr(ii,2)?'俗称
'??????ElseIfArr(ii,1)="合计"Then
''????????MsgBoxArr(ii,1)&vbTab&ii
'????????ii=ii+1
'????????Brr(iTem,2)=Arr(ii,1)
'????????Brr(iTem,3)=Arr(ii,2)'俗称
??????Else
????????Brr(iTem,2)=Brr(iTem-1,2)
????????Brr(iTem,3)=Brr(iTem-1,3)'俗称
??????EndIf
??????Brr(iTem,4)=Arr(ii,7)?'不良内容
??????Brr(iTem,5)=Arr(ii,8)?'等级
??????Brr(iTem,6)=Arr(1,i)?'日期
??????IfArr(ii,7)<>"检查数"ThenBrr(iTem,7)=Arr(ii,i)'不良数
??????IfArr(ii,i)<>""AndArr(ii,7)<>"检查数"ThenBrr(iTem,8)=1'不良数次数
??????IfArr(ii,7)="检查数"ThenBrr(iTem,9)=Arr(ii,i)???'检查数
??????iTem=iTem+1
????Nextii
??Nexti
??WithSheet4.Range("A2")
????.CurrentRegion.Clear
????.Resize(iRC,9)=Brr
????.Offset(-1,0).Resize(1,9)=Array("序号","番号","俗称","不良内容","等级","日期","不良数","不良数次数","检查数")
????.CurrentRegion.Columns.AutoFit
????.CurrentRegion.Borders.LineStyle=1
????.CurrentRegion.Borders.ColorIndex=3
??EndWith
EndSub
数据透视表汇总结果如图1-14所示。
图1-14?数据透视表汇总
如果你不会**公式和VBA,*好还是规规矩矩地做表格。
说了那么多,什么是二维表跟一维表呢?
左边的二维表:金额491对应产品A跟4月1日;右边的一维表:491对应金额,4月1日对应日期,A对应产品,如图1-15所示。也就是说,一维表每个数据都只有一个对应值,而二维表每个数据有两个对应值。
以前是因为没有人告诉我数据源规范的重要性,以致我走了很多弯路。靠自己摸索是件很痛苦的事情。成功是有捷径的,那就是站在巨人的肩膀上看问题、学习。
图1-15?二维表与一维表
注重细节
在日本企业工作,要**服从管理。日本企业一般有一套管理模式,在某一阶段做什么事情是非常明确的,整个公司的工作模式基本上是按部就班。每个新人来到日企,公司会有专门的培训,会告诉你做什么事情用什么样的方法比较好,甚至填表这样的事情也会有专门的人教你。因此想在日本企业工作,就需要具备很强的责任心,做事绝不能够马虎。
同时日本企业文化也提倡挑战精神,在日本企业,主动思考的部下才会得到赏识。即使挑战失败也没关系,但是不能自作主张,如果没有得到上司的许可,失败了,上司会发怒。在这种情况下,虽然上司不知情,但出现问题,上司也会因为“对部下的监督不力”而承担部下失败的责任。因此,挑战前应该先与上司“商量”。
此外也要特别注重礼仪。早上到公司,“早上好”是一定要说的,要是对方是领导,那就一定再加上“敬语”;下班时要说“失礼了”或者“辛苦了”,如图1-16所示。在和日本老板在一起步行时,一定要让老板走在前面,还要注意自己的仪表打扮。日本公司把注重自己的形象看成是对别人的礼貌,日本男人要求穿西装,日本女人还要求化淡妆。对中国人没有强制性要求,但起码要穿戴整齐。
其实很多外企都一样,都有自己的一套管理模式。不管怎么说,都非常注重一些细节问题。下面通过几个小事例来说说Excel的细节问题。
图1-16?注意礼仪
计划与实行
每年都有一个总计划,再逐步细分成月、周、日计划。每天再将实行的结果跟计划比较,找到自己没做好的,及时改正。图1-17所示就是一个每周计划与实行表,其他表格类似。
图1-17?每周计划与实行表
反馈
每天将所做的主要事情跟领导报告,如果出差在外,可以通过发送电子邮件把事情做简要说明,必要时添加Excel文档。如图1-18所示,发送前一定要确认称呼妥当,无错别字,附件添加没有,确认无误后再发送。如果是重要邮件,一定要打电话确认领导有没有收到。
图1-18?发送电子邮件
为领导节约每一秒钟
如图1-19所示,在给领导发送表格前,先对需要说明的重要数据标示出来,并将鼠标指针停留在标示的单元格那里,保存表格后再发送给领导。这样一来,领导一打开表格就可以看见你要给他看的**数据,以免他再重新查找。虽然只是节约几秒钟,但是对领导来说每一秒都有宝贵的价值。
图1-19?**标示
站在领导的角度看数据
一般领导都是上了年纪的人,视力大都不好。我们在给领导表格时,要尽量将字号调整大一点,或者隔行填充颜色,如图1-20所示,让他能够看清楚每个数据。
其实还有很多需要注意的细节,这个得靠自己慢慢摸索,别人说的你不一定适用,只有当你碰到这种事情时才会记忆深刻。另外再说一句,同事之间的关系也很重要,一定要处理好。
图1-20?调大字号、隔行填充颜色
本章小结
Excel存在四类表格——参数表、明细表、过渡表和汇总表,*重要的是从一开始就学习规范明细表,这样会给以后工作带来极大的便利。其实做人又何尝不是这样,规规矩矩做人,远离抽烟、酗酒、赌博等不良恶习……偶尔放纵自己一两次也可以,不过不能养成坏习惯,只限于玩玩就行。好习惯一旦养成,日久必将给人留下好印象,以后有什么好事也会轮到自己。同时也要处理好和领导及同事间的关系,注意一些细节。能力再强,关系没处理好也没用。