Excel-《表格之道》的笔记

15年的时候由于工作需要，在网易云课堂上学习了《表格之道》的课程，感谢谷老师的教导至今受益匪浅😀。由于公司网络不能访问OneNote，同时为了分享知识给大家。故将自己的笔记整理分享出来。当然只是一个框架和笔记而已，要是有时间的话，还是希望提升Excel的伙伴学习一下《表格之道(专业版)》，才99RMB也不贵，哈哈哈哈

• 第四章-规范数据
  • 20-格式的基本概念
  • 21-日期时间格式
  • 22-数字与文本的转化
  • 23-电子表格的通用格式
  • 24-27-通用格式细则上中下总结
  • 28-Ctrl+L
• 第五章：字段调整-公式
  • 29-函数简介
  • 30-函数的学习路径
  • 31-名称的命名
  • 32-名称的应用、数据验证
  • 33-相对引用
  • 34-零件函数之统计函数5-6
  • 35-零件函数之日期函数5-7
  • 36-零件函数之文本函数
  • 38-零件函数之随机函数
  • 39-if函数概述
  • 40-if函数嵌套
  • 41-if函数and和or
  • 42-vlookup函数介绍以及基本用法
  • 43-Vlookup的具体应用
  • 44-vlookup模糊查找
  • 45-index与match函数

第四章-规范数据

20-格式的基本概念

• 数字在右，文本在左
• 时间日期的显示方式
• 数字与文本的转化

21-日期时间格式

• 日期就是一种特殊的表示的数字。在输入中，以减号或者除号来把日期间隔开来。

22-数字与文本的转化

超长的数字会被自动的转化为科学记数法。解决方法就是将单元格格式变为文本。

注意：不要在数字前加逗号来将超长的数字显示出来。这样本身是非常危险的。

问题：从外部网页或者数据库中导出的表格，很多时候数字是以文本的形式保存的，如何将其转化为数字？ 答案：

• 1.将需要转换的数据选中，在左边的小叹号中选择转化为数字。
• 2.在任意空白处键入数字1，Ctrl+C，然后选择要转换的数据，Ctrl+Alt+V，选择运算中的“乘”。（格式需要调整一下，因为1的格式会被带进去）
• 3.大招：分列。选中要转换的列，然后在点击数据中的“分列”，之后点击“完成”。（但是我有个疑问，这样好像还是转换成了文本，只不过把小绿帽子摘掉了）

23-电子表格的通用格式

• 数据分为前台和后台。
• 后台：存储、加工、分析、计算
• 前台：展示、传递
• Excel本身就是为了后台

24-27-通用格式细则上中下总结

电子表格是简化了的数据库。

问题：制作表格常见的10种错误有哪些？ 答案：

• 1.合并单元格。会导致数据透视表，筛选等功能失效。处理方法：选中单元格，点击开始–>合并后居中，这样数据就会显示在第一行，此时需要将之前被合并的单元格填充上，选中需要填充的部分，按F5->Alt+s(定位条件)->Alt+k(空值)->确定->输入“=C2”->Ctrl+Enter(批量填充)。由于现在虽然显示的效果是对的，但是内部其实是公式，所以需要选中单元格，Ctrl+c，Ctrl+Alt+V，并选择数值，来转换一下
• 2.没有序号，为了回溯方便，相当于数据库中的ID
• 3.乱加表头，不要乱加没有用的东西，Excel表格是用来分析的，不是用来展示的
• 4.胡乱合计，合计数不应该出现在元数据中的。
• 5.不符合数据透视表的规范，尽可能把一类的内容放到一列里面。如右图所示
• 6.错误的理解0，”0和空值是不一样的，这在计算平均分等情况下会被体现出来”
• 7.分裂表格，就是将一个表格以人类方便的方式拆分了
• 8.数字的文本化，为了计算方便，需要输入数字的地方一定要只填数字，如果一定要单位的话，有两种办法，一种是追加一列来记录单位。另一种是Ctrl+1->自定义–0分。显示时会带有分，但是实际是数字。并且在明确知道数字的范围的情况下，通过数字有效性（整数／小数）来限定输入，可以在一定程度上来防止录入错误。
• 9.内容不统一，格式等各方面吧
• 10.单元格非原子化，一个单元格就放一个不可分割的最小的信息。

问题：一个好的电子表格是什么样的？ 答案：

• 1.完整的字段标题
• 2.首字段做序号
• 3.数字和日期的正确
• 4.有效性避免错误
• 5.符合透视表的习惯
• 6.没有不规范项

28-Ctrl+L

套用格式的意义（优势）：

• 1.更易于阅读(无论是在屏幕还是在打印机)
• 2.更易于添加数据
• 3.帮助完善字段标题
• 4.优秀的初级数据分析平台(排序，筛选，查重)

套用格式应注意的问题：

• 1.结构化数据。在新增一列的时候，对一行添加公式，该公式会应用到所有的行中。有时候我们并不需要对所有的行都添加该公式，可以通过：表格工具（设计）->转换为区域。来取消套用格式
• 2.还原为数据表的方法

第五章：字段调整-公式

29-函数简介

函数是一种编辑好的计算规则，输入等于好新增一个函数，按Tab键来完善。

函数的主要用途：根据现有数据，新增一列字段。

函数的目的：

• 1.承担计算工作
• 2.为分析做准备

30-函数的学习路径

函数的分类：

31-名称的命名

单元格默认是以字母加数字来命名的，选中一个单元格在左上角可以重新命名该单元格。下次使用F3来引用该命名后的单元格(可以重命名一个区域的单元格，或者分散开的单元格)。

如何批量的重命名单元格？

答案：在满足特定格式的情况下(比如：左边一列是名称，右边一列是数据)先选中这两列，然后：公式->定义的名称->根据所选内容创建->已选定区域创建名称(根据实际情况选择，例子中是最左列)。

可以使用名称管理器来删除单元格名称

名称命名的规则有哪些： 答案：

• Excel名称框不允许使用RC
• 空格会被下划线自动填充
• 与单元格相同的名称为避免冲突不得引用(32、A4)
• 符号只能包括.和_

32-名称的应用、数据验证

如何新建一个数据有效性的备选名称？

答案： 选中需要创建的数据，点击->公式_定义的名称_根据所选内容创建

33-相对引用

• 相对引用
• 混合引用
• 绝对引用，在公式中选中单元格，按F4直接添加绝对引用

34-零件函数之统计函数5-6

统计函数不需要掌握太多，这些就够用了，大部分的统计函数我们可以用数据透视表来完成。

35-零件函数之日期函数5-7

36-零件函数之文本函数

主要是由于表格不规范，“擦屁股”用的函数。

拆

• left
• right
• mid
• 分列(office2013新增,提取表格中一个身份证号中的出生日期，然后一拉，右下角选快速填充。之后使用-》数据（分列）来将其转换为日期)字符长度相同，或者明显标识

合

• CONCATENATE
• &

转（不讲了，通过自定义格式的方法来处理）

• text
• Trim 删除多与空格

量（字符串的长度）

• len

找

• find

38-零件函数之随机函数

39-if函数概述

40-if函数嵌套

多层嵌套的话，最好用回车来换行，看着清楚

1
2
3
4
5

=IF(F2=1,200,
IF(F2=2,400,
IF(F2=3,600,
IF(F2=4,800,
IF(F2=5,1000,“”)))))

41-if函数and和or

and和or的具体意思就不说了

随便举一个例子：=IF(OR(E4>7,F4>2,SUM(G4:I4)>3000),"重要员工","")

42-vlookup函数介绍以及基本用法

vlokup函数的使用条件：

使用后的效果：

练习

答案：

注意：VLOOKUP还有一个兄弟函数，叫做HLOOKUP，当被查找表格呈现横着的状态时候使用。

43-Vlookup的具体应用

在选择被查找区域的时候，一定要被选取区域的首列为共同字段，并且区域包含了要添加的字段。

为了防止vlookup出现不好看的现象（没有返回值的话，会出现#N/A），需要使用iferror函数，具体体现如下：=IFERROR(VLOOKUP(C12,津贴发放,3,0),"")

vlookup函数是可以做查询的

44-vlookup模糊查找

模糊查找非常的方便，可以判断一个区域的值。比如根据成绩判断评级，85-100的给S，直接用模糊查找，给出这样的一个小表格就够了

但是Vlookup函数并不能完全取代IF函数，只是两者在模糊查找方面各有所长。

45-index与match函数

match函数可以返回一个指定字符在一行或者一列数据中的排序位置。

index函数，可以根据横纵坐标返回一个区域内的具体值。

具体例子

附录，日常使用的积累

日期计算

Excel，两个日期相减得到的数字的单位是天。若想得到两个时间相差的分钟数，则=(W3-U2)*24*60。若需要格式化去展示则=TEXT(W3-U2,"[h]小时mm分钟")。

拼装字符串时对于“日期”、“时间”的格式化

• Previous
  Java-用MessageFormat来替换占位符
• Next
  Linux-浅谈Nginx的反向代理 即http请求经历了什么😀