Office：4-Excel

发表于 更新于 分类于

微软 Office 系列软件使用方法系列第五篇：Excel 的使用

使用方法

大部分 Excel 的基本功能默认基本会用，这里只说一部分。

建议阅读的同时打开 Excel 试一试。

基本概念

专有名词

为了统一名称，对 Excel 中使用的术语/名词做一个简单的统一：

  1. 一个 .xls/.xlsx 文件称为「工作簿」（Book）
  2. 一个工作簿里，下面可以切换的 Tab 称作「工作表」（Sheet）
  3. 对图表「移动到新的」工作表产生的「Chart X」也称为工作表
  4. 一个工作表里的超级表/数据透视表称为「表」（Table / Pivot Table）
  5. 单元格（Cell）
  6. 公式也称为函数（Formula/Function）

    Excel 里确实两种称呼都有，不知道为什么

坐标系统

在一个工作表里，水平方向上使用大写字母编列，最多到 XFD 列，竖直方向上使用数字编行，最多 1048576 行，坐标原点为左上角（A1）。

如果想要使用坐标表示一个选区，即引用一个区域，使用半角冒号连接两个区域，区域可以都是单元格，或都是行/列，如 A1:C3, A:F, 1:4。

在 Excel 中，如果复制一个含有引用的区域到新的区域，其中的引用会自动变化（保持被引用单元格到引用单元格的相对距离），但是可以通过在坐标前加入修饰符 $ 使其变为绝对引用。如 $A$3:C4。

基本操作与快捷键

视图操作

冻结窗格

当表格的长/宽超出了显示的页面，固定标题可以方便滚动后留存标题信息。

我以前一直不会用「冻结窗格」，只会用「冻结首行/首列」，但是后来发现是我不识字，「冻结窗格」里面说的很清楚，“保持当前的选择，保持行和列可见”，不过这里还是说的不够清除，没说清楚保持什么行、什么列可见。

我之前认为是冻结「选中」的行/列，从来没成功过，后来发现，是冻结「选中的行/列之前的部分」(上方的行和左侧的列)，所以，冻结 A:C 列，那就选中 D 列然后冻结，冻结 1:8 行，选中 9 行冻结，同时冻结上面两个区域，选中 D9 单元格然后冻结。

打印视图与分页预览

在 Excel 界面的右下角，缩放条的左侧，有三个视图可选，平常都使用的是「普通视图」，其后的「打印视图」和「分页预览」也相当有用。

如果想要添加页眉页脚标题之类的，直接切换到打印视图添加。或者是想要调整列宽在某一页显示全，不用在普通视图调整后再打印预览，直接在打印视图调整。

分页预览和打印视图类似，不过它不显示具体的页边距和页眉页脚，而是直接选择每一页打印的范围。

新建窗口

当一个工作簿有多个工作表的时候，在工作表之间切换稍微有点麻烦（指鼠标或快捷键），这时可以使用「新建窗口」，为同一个工作簿打开多个窗口，修改在各窗口是同步的，这样使用熟悉的「Meta/Alt-Tab」快捷键就可以直接切换了。

具体使用请点击「视图-窗口-新建窗口」

导入数据

有了 Excel 的基本知识，但是数据从哪来？不是所有的数据都是 Excel 可以直接打开的表格形式。大部分数据是以文本（不是文本文件）形式存在的，而 Excel 本身就可以很好的处理它们。

Excel 处理数据的工具，大部分都在「数据」-「获取和转换数据中」

对于文本文件，直接点就好了。

我感觉有不少情况下有部分人不知道「文件后缀/扩展名」和「文件类型」之间的关系，就是「文件后缀」只是一个名字，用来告诉人或机器应该用什么程序去打开它，但是它的「文件类型」不是通过更改后缀就能转换的，比如一个 .dat 文件可以是文本文件也可以是二进制文件，它真正的文件类型是要看它本身在存储空间中的值的。

更改文件后缀名不过是换了一个打开它的程序，和打开这个程序再把这个文件拖进去是一样的。

对于网页上的表格，不需要复制到文本再导入，直接使用 Excel 的「自网站」，输入链接等待 Excel 自动识别 HTML 并转换再导入就好。

对于内容是文字的 .pdf 文件，使用 PDF 软件的「导出到电子表格」即可，根本不需要在此时使用 OCR 软件。只有对于内容是图片形式表格的 .pdf 文件，才要使用 OCR。

处理数据

填充

用单元格右下角的 Handle 拖动填充的方法不必多说，在「开始」-「编辑」-「填充」里还有更多的填充方式，比如生成等比/等差序列，不需要自己写公式或者先写好几个再拖动填充了。

分列

分列是很有用的工具，在「数据」-「数据工具」-「分列」。

当一个单元格内的文本具有相似的特征时，可以用分列将其分成两列。相似的特征包括「宽度」或者「内容」，也就是可以按照某个符号或者在某个宽度上分割一列称为多列。

比如网上不大不小的一个用逗号分割的数据，直接用「自网站」没必要，但复制下来又都都是逗号。可以用分列，选择「分割符号」，然后一直确定即可。

分列一次只能识别一个分割符号，如果有复杂的字符串处理需求，请学习「正则表达式」（此博客有一篇「正则表达式入门」），并考虑在支持正则表达式的文本编辑器中操作。

替换

替换的快捷键是 Ctrl-H，Excel 里替换的特别之处是可以处理单元格格式，但是具体的应用没有特别特殊的地方。

选择性粘贴

选择性粘贴很强大，但是有部分功能很少有人用，常用的应该包括粘贴为转置、图片、值，以及保留/不保留格式。

但是点开下面的「选择性粘贴」，还有其他内容可选，这里提示两个内容，一个是可以直接把粘贴数值和被粘贴区域做四则运算，另一个则是「跳过空单元」，这对于合并多列很有帮助，它即是只在对应单元格粘贴源内容不为空值的单元格。

组合与分类汇总

Excel 在默认的行的基础上，可以通过组合创建可折叠的索引，日常使用的用处不是很大。

同时，它也可以自动对数据进行分类汇总，同时建立组合。这两个选项都在「数据」-「分级显示」

合并计算

合并计算主要应用在有多个类似结构的数据表时，此时通过合并计算可以将所有数据表的信息进行汇总。比如 M20 单元格是由五个单元格的值加起来的。

快捷键

Excel 除了复制粘贴外，还有一些快捷键比较有用

  • 使用 F4 快速切换相对/绝对引用：在公式中输入一个区域的引用后，使用 F4 可在四种引用类型中切换
  • 按下 Shift 后，在单元格/行/列的边缘处，按住鼠标可快速移动选区，不必再复制-粘贴-复制-粘贴
  • 按下 Ctrl 后，拖动选中的区域可快速复制区域
  • 按下 Ctrl 后，使用方向键，会将选中的单元格位置移动到此方向上最后一个非空白的单元格
  • 在单元格边框双击，可以达到和上一条一样的效果
  • 按下 Shift 后，使用鼠标拖动或方向键移动所经过的区域都会被选中
  • 因此搭配 Ctrl 与 Shift 可以快速选中一个区域，不必再滑动鼠标选中
  • Ctrl-1 快速打开「单元格格式」窗口
  • Ctrl+G/F/H 对应「定位」「查找」「替换」
  • F2 可以编辑一个单元格的内容
  • Ctrl+Enter 可以对所有选中的单元格填充相同的值（可搭配定位-空值补零）

单元格格式

单元格格式非常基础，但也重要，但是大部分情况下可能会让人迷惑的是在数字格式处

自定义数字格式

在更改了数字格式后，可能显示并没有发生变化需要刷新，此时通过「分列」，不设置任何宽度或分隔符，就可以直接刷新格式。

使用「文本」格式时，如果其中存储的被识别为了数字，左上角会有小三角形的提示，不必在意。

如果「常规」格式下的时间转成「文本」后变成数字，可以用记事本或其他先复制，更改单元格格式后重新插入

格式刷

双击格式刷可以固定选中格式刷，即重复使用，至单击其图表取消选中后结束。

选中一个区域后复制的格式刷可以刷新一个区域的格式，可以搭配 Ctrl+Shift+方向键 的快捷键快速添加格式。

函数

输入公式

  1. 可以在单元格内直接输入公式，先输入等号后就可联想输入，使用 Tab 键快速补全
  2. 点击「公式」选项卡，找到公式再输入，但不够方便
  3. 使用「插入函数」按钮，比较方便

常用公式

字符串处理

Excel 中的字符串使用双引号，单引号的不是字符串

字符串处理的公式包括 LEFT，RIGHT，MID，FIND 等，其中 LEFT, RIGHT 均有两个参数，即选择的单元格、从左/右开始的字符个数，用于从最左/右提取固定长度的文字；MID 多一个开始的位置（从左）；FIND 用于找到字符串在候选字符串中的起始位置。

都是处理固定长度的，不定长度的请使用分列或正则表达式

字符串的连接可以使用 &，CONCAT，TEXTJOIN 等，其中 & 连接其左右的两个表达式，CONCAT 将所有参数按顺序连接，TEXTJOIN 可以设置连接符号，有更多可调整的地方。

逻辑运算与条件

Excel 中的布尔是 true/false，但判定时 0 也可能被认为是 false，大于 0 的值为 true

包括 IF IFS ...IFS AND OR NOT 等，其中 AND OR NOT 用于处理逻辑值的与或非关系。

IF 有三个参数，第一个参数的表达式应当返回一个值(布尔)或一个数，若为真，则返回第二个参数表达式的返回值，反之则返回第三个。

IFS 可以避免复杂的嵌套，一次处理多个逻辑表达式，共 2n-1 个参数，类似于 IF 若第一个参数为真，则返回第二个表达式的值，反之，则将第三个参数作为表达式继续判断，最后得到结果。

...IFS 代表一系列类似的函数，包括 AVERAGEIFS，COUNTIFS，SUMIFS 等，均可以使用多个条件对区域进行统计

数学与统计

product 返回所有参数的积

AVERAGE SUM COUNT 就比较常见

查找

LOOKUP VLOOKUP HLOOKUP 用于查找数据，其中「精确查找」会将查询数据在和待查数据一一比较，在有极大量数据时会有可察觉的延时；「模糊查询」则是默认数据已经是排序过的，使用二分法查找，返回匹配的值或最临近匹配值的值。

SWITCH 用于待查数据较少时，可以被 LOOKUP 数据替换。

名称与管理器

Excel 中可以给变量/区域添加名称，从而在定位/公式中直接引用，加快人书写/阅读的效率。

在「公式」-「名称管理器」中，可以管理已有名称或添加新的名称，名称可以存在与工作表或工作簿中。

在公式中使用，只要把填写引用的区域填写为名称即可。

公式审核与计算

一般情况下，当有数据发生改变时，Excel 会自动重算所有公式，但自动重算在有大量公式时会影响速度，可以通过「公式」-「计算」-「计算选项」设置「手动重算」或「保存时重算」，减轻计算压力，手动重算使用快捷键 F9。

可以使用「公式审核」-「显示公式」使得所有公式显示出来，而不必设置单元格格式。

为了追踪单元格的引用与被引用，可以通过「公式审核」-「追踪从属/引用单元格」查看

条件格式

条件格式是非常有用的工具，通过它可以根据数据调整单元格的显示的方式，比如添加颜色条或符号表等等。除了它本身提供的五种条件外，还可以通过公式自定义格式。

使用条件格式需要三个参数：条件、格式、应用区域。这里主要说一下使用自定义公式的条件格式：

使用自定义公式需要一个返回布尔值的公式，条件格式实际上会对应用区域的每一个单元格，分别应用一次输入的公式。而分别应用公式里的引用会随着单元格变化（如果不设置绝对引用而使用相对引用）。

比如这里有很多行数据，我想让所有 K 列值不为空且不是字符串 “C” 的行变成黄色，那需要输入的公式是

1
=AND( $K4 <> "C", $K4 <> "")

其中应用的区域是 $A$4:$K$20。公式里的 AND 很好理解，$K4 <> "C" 就是 K4 不等于 “C”，但是绝对引用是什么意思？因为输入的公式，默认是指应用到选取最左上角的单元格的，那么这个公式默认会被应用到 A4，此时在 A4 判断的是 K4，那接下来到了 B4 单元格，判定的就是 L4 单元格了，所以需要限制住列的变化。

这里非常建议自己尝试一下，就明白了

因此，应用条件格式的区域，和公式里引用的区域，不需要有包含关系。

值得一提的有两点，一是条件格式只有 Excel 可以用，Word 和 PowerPoint 直接插入的表格无法使用条件格式，除非「粘贴为图片」或插入 OLE 对象。二是对于应用了公式条件格式的区域，建议不要对其中的区域进行剪切-粘贴，很可能会导致公式的引用出错。

在「条件格式管理器」里，可以快速看到当前表/工作表/选区的条件格式并对其进行修改。

数组公式

这里还没搞清楚，先放在这。

超级表与数据透视

超级表

超级表是 Excel 很方便的设计，它比直接管理原生的区域要方便一些。

使用 Ctrl+T 可以快速对一个区域创建超级表（也可以简称为表），而后会自动应用一个表格样式同时产生列标题行。

表的方便之处第一点，是当选中表后，标题行会自动出现在索引中，同时可以筛选，此时不需要冻结首行。

第二点，它可以自动的对整个区域变换格式，不需要手动选择整个表，只需要点在这个区域里即可。

第三点，在使用公式时，可以不再使用引用来表示区域，而是直接用表来表示，比如下面图中，直接使用 表名[区域名] 选择区域

其中区域名这里的 @ 表示只使用对应行的值，去掉 @ 则表示引用的区域是整个一列：

具体表的名字、样式以及其它可能有用的工具（切片器之类的）都可以在「设计」里找到（需要先选中表格）。

如果想要把表格转换回区域，不需要复制-删除，直接在设计里的「转换为区域」就好了。

数据透视

数据透视是 Excel 比较强大的功能，它包括数据透视图与数据透视表，感觉和 Tableau 很像（勿喷，不知道哪一个先出的），使用数据透视可以仅通过鼠标，轻松的完成数据分析与可视化。

只说一点，数据透视表的描述/引用方法是引用其左上角的单元格。

数据透视的基本使用自学即可。

计算字段

计算字段可以通过公式运算，为数据透视表增加新的字段而不用改动原数据集。在「分析」-「计算」-「字段、项目和集」-「计算字段」中可以添加计算字段。

比如有两年的销售数据，想要展示增长量，不需要在数据源添加一列，而是增加一个计算字段，公式使用 列B - 列A 就好。

需要注意的是，修改和删除新添加的计算字段，需要在「插入计算字段」中选择，然后删除。

切片器

切片器，类似与可移动的筛选器，通过制定要切片的字段，其中就会有所有的值，和筛选功能基本一致。

按住 Ctrl 可选中多个值。

数据验证与工作表保护

数据有效性

为了确保数据能被正确的输入，可以设置数据有效性，以限制输入的内容。

具体位于「数据」-「数据工具」-「数据验证」

其中「序列」类型具有「提供下拉箭头」选项，允许单元格的值来自于某个区域，即一个序列。但是这里的问题在于如果源区域内容变化，可选项也会变化，因此需要对源区域单独加密。

设置密码

在「审阅」-「更改」中具有「保护工作表」「保护工作簿」「允许用户编辑区域」等多个选项，需要提示的是，它们的密码是不互通的，同时每个选项保护的范围是不相交的。其中「允许用户编辑区域」设置的“编辑区域”的密码后，还需要设置保护工作表其他区域的密码。

辅助工具

Excel 可用的插件只推荐这一个

excel2latex

可以快速把 Excel 表格转换为 TeX 语法形式，同时保留相关的格式，只能说非常好用。

下载好后应该是一个 .xla 文件，拖入 Excel 即可使用。

Reference

Tools for screen recording:

https://github.com/NickeManarin/ScreenToGif