数据分析学习 excel的骚操作合集（则秀excel课程笔记）

---

前言

目前在自学数据分析的工具，excel的一些功能是之前没接触过的，这里做一下整理。

一、自动填充

选中文本之后，ctrl+delete可以删除光标后的所有内容。
ctrl+回车可以实现输入完成后仍然选中当前的框。
每一个框的右下角都有一个自动填充，选中之后拉开可以实现自动填充。
在自动填充选项中可以选择复制选项，填充序列等。

二、选取区域

ctrl+→，ctrl+←，ctrl+↑，ctrl+↓都可以实现直接选取最右/左/上/下的单元格。
选取整个区域，若为整张表，直接ctrl+a即可；若是自由选择，先选择左上角，再按住shift选择右下角，即可选取。

三、复制与粘贴

复制与粘贴部分需要注意一下粘贴选项，在开始的左上角可以看到，这里面提供了大量的粘贴选项，包括保留格式，粘贴为图片等等。

四、公式与函数

公式可以清晰地看到运算过程，而函数只是一个模块.
在列索引上方可以看到fx，点击fx可以打开查看所有函数。

五、输入公式

输入公式之后，点击框的右下角，可以实现公式的复制，但是excel会自动更改引用的框，这研究可以实现智能的填充，十分实用。
如果不放心，可以点击公式——公式审核——显示公式进行查看

六、快速求和

选中一大片区域，开始——查找与选择——定位条件——定位为空值
这样就可以实现所有空值的选定，这时候在运行自动求和指令，可以实现大量数据的求和操作

七、绝对引用

对一个小数进行操作的时候，可以选择数字列中的各种选项，这样就可以实现百分比，增加小数点后位数，减少小数点后位数等操作。
若要实现分子相对引用，分母绝对引用，举例H7/H13，那这时我们就要修改为H7/$H$13，这样就可以实现拖动框的右下角方便操作了。

八、if函数

if(A1>=1,$B$1,$C$1)
若A1这个值大于等于1，返回B1，否则返回C1，利用智能填充，可以实现高效操作。同样的，我可以把返回值替换。
if(A1>=1,“不错”,“还行”)
这样也是行得通的。

九、sumif，averageif函数

sumif（）
第一个参数是条件计算的区域，一般是需要绝对引用的。
第二个参数是你要计算的类别，因为我们还需要向下拖动公式，所以我们行相对引，列绝对引用。
第三个参数是实际求和的区域，因为要横向拖动公式，所以我们行绝对引用，列相对引用。

如果我们想要将sumif直接替换为averageif，可以先选中需要替换的区域，直接按ctrl+f，替换功能即可。

十、定义名称

选中表格中的一个区域后，点击左上角的名称，可以自行命名为“阿巴阿巴”，这时候如果我再使用sum函数，就可以直接写成sum（阿巴阿巴），就可以实现框选区域的求和了。

十一、数字与数值

excel只能处理15位以下的数字，超过的部分会写成00000，我们如果要输入一个数值，但是不把他当数字处理，这样是可以输入很长的数字的，一般的方法是，开始菜单的数字栏里面可以进行勾选为文本格式，或者直接在很长的数字前加 ’ 号，也可以实现。

同样的，如果excel表中全都是文本类型的数字，我们无法进行求和，这时候就可以，复制一个空白单元格，然后选中所有的文本型数字，再点击选择性粘贴，在运算里面点击运算中的任意一个（加，减都可以），再点确认，这时候数字就都变成可以运算的数字了。

十二、数组计算

很强大的功能，先框选一个区域，然后输入函数，例如
=(
选中同样高度的一列数据，乘以另一列数据，这时候ctrl+shift+回车可以实现在框选的区域进行求和

除此之外，勾选一个格，输入
=sum（
再选择一列数据乘以另一列数据，这样就可以实现两列数分别相乘再相加的运算，省掉了之前需要先计算乘积在计算求和的过程。

十三、日期

日期可以使用两个单元格相减实现之前的日期计算，但是注意要更改格式，如果目标格为日期格式现实就会出错，需要改为常规。

十四、日期函数

当天日期快捷输入：ctrl+；
当天日期输入函数：=today（）
当天时间快捷输入：ctrl+shift+；

十五、格式数字

开始菜单下面，数字栏，里面有很多可以自定义的格式，例如货币，年月日的特定表示方法。

举例来说，我想把表格中的0都显示为-方便我的浏览，这时候我就可以全选所有表格，然后在数字栏中选择自定义，里面有默认的将0转化为-，这样就可以实现方便我们的浏览。

---

十六、边框

边框可以在边框-其他边框中进行详细编辑，需要先设置颜色，再进行边框的添加。

在调整网格的宽度高度的时候，在光标变成调整的时候双击，就可以实现系统推荐的宽度更改。

---

十七、插入图片

插入图片后可以选择图片的格式中的颜色，选择透明，并且把白色的背景刷成透明色，这样可以实现不对网格进行遮挡。
插入形状的时候按shift是一种方式，按住ctrl是吸附。

十八、格式刷

格式刷双击可以多次刷，不要再傻乎乎的一次一次框选一次一次刷了！

十九、主题

当我们使用多个来自不同文件的图表时，会出现主题不统一，视觉上很乱的情况，这时候我们点击 页面布局-主题，选择一个主题应用，就可以实现统一主题了。

二十、样式

为某一个单元格设置样式的时候使用样式工具栏进行操作

若下次需要将某个样式的单元格进行修改的话，直接更改样式的内容，所有应用这个样式的单元格都会被更改，十分方便。
除此之外，我们还可以设置好我们需要的样式，然后在下次做类似的表的时候直接使用原来的蓝图样式就可以了。
但是新建一个excel表之后，往往以前的样式就不见了，这时候我们打开旧表，选择合并单元格样式，即可。

同理，word也应该使用这种方法，fuck，我要是早知道这个的话能节省很多时间。

二十一、冻结拆分窗格

使用视图-冻结窗格命令，可以实现在翻动excel表的时候仍然看到行索引或者列索引。
注意，如果想同时冻结行和列的话，需要将想要冻结的行和想要冻结的列交叉格的右下角单元格选中，再点击冻结窗格。

拆分可以实现距离很远的两行数据的对比，只要按这个小横线，就可以实现拆分操作。双击拆分出现的分割线，分割线就会不见了。

二十二、快速实现取消隐藏

隐藏实际上就是将你选择的行，列宽度设置为0，取消隐藏的时候我们只要选择一个区域，双击分割行或列，就可以取消隐藏了。

二十三、分组功能

如果想实现一些行或列的隐藏，只需要选中行或列，选择数据-分组，这时候就可以自由打开或隐藏了。
需要注意的是，分组效果会影响打印效果。

二十四、自定义视图

选择视图-缩放到选定区域，可以实现视图只显示你选择的区域，十分方便操作。

使用视图-自定义视图，可以实现各个画面的快速切换操作，这样可以实现高效地调用某部分内容的表，十分方便。

二十五、多视图浏览

我们现在有两个excel，一个里面有一个sheet，里一个有两个sheet，我们如何实现把它们放在一起进行比较呢？
首先，为了区分两个sheet的表格，在其中一个sheet上选择 视图-新建窗口

这时候点击右上角的

这时候我们就实现了平铺所有的表格，但是调整起来很麻烦，我们再选择
视图-全部重排，选择默认的平铺，

就完成了，excel会自动将较大的部分占用较大的空间。

二十六、管理工作表

可以按住ctrl选择两个sheet，这时候就会变成一个工作组的概念，这时候修改的单元格会在两个sheet中同时修改，在修改同类表格时大大提升工作效率。

二十七、分列与连接

当我们想把一个单元格的内容分为很多列时，需要点击数据-数据工具-分列

举例来说，我现在想把下面一列按照中英文名称分列

我首先使用ctrl+f中的替换功能将所有的"(“替换成”-("，结果如下

再点击分列，选择里面的

完成分割。
进行连接的时候，只需要选择要链接的单元格，在中间以&进行连接就可以了。

二十八、排序和筛选

位置在 数据-排序和筛选 里面，注意排序可以以多个条件进行排序，十分方便，筛选功能主要是用打开筛选之后的索引上面的小三角进行操作的，十分简单，打开就会用了。

二十九、分类汇总

位置在数据-分级显示中，
这个分类汇总的意思就是以地区为分类，计算评分的平均值。效果如下

三十、快速实现合并单元格填充

https://www.bilibili.com/video/BV1qs41157Lr?p=42

三十一、自定义图形的图片批注

首先点击一个单元格，选择新建批注

然后在批注的边框上点击右键，选择设置批注格式

选择颜色与线条-颜色，然后选择填充效果-图片，导入一张喜欢的图片即可。

然后任意添加一个形状

点击形状时候，会出现这个工具栏

在里面的编辑形状选项上，点击右键，选择添加到快速访问工具栏。
这时我们再编辑批注，就可以实现更改形状了

如下所示

三十二、表格

excel支持把已有数据做成一个表格，选中数据，在插入中选择表格即可，这给了我们做很多方便的操作的可能。

三十三、条件格式

在开始-样式中可以选择条件格式

选择一些数据，就可以设置条件格式了，例如下面我将评分大于4.5的重点显示

在这时候，我直接进行更改数值，都是可以实现即时更改格式的。
想要清除格式的时候，直接点击清楚规则就可以了。

三十四、筛选之后的复制粘贴

选择一片区域，这片区域可能有筛选的内容，或隐藏了行和列，如果直接复制，隐藏的内容也会被复制走，但是我们可以
一、选定区域后按alt+；
二、选定区域后按ctrl+G，选择定位条件，选择可见单元格

这两种操作过后，都会实现仅仅复制我们需要的内容。

三十五、模拟分析

主要是模拟分析表这个内容，具体在视频里有，日常用到的比较少。

https://www.bilibili.com/video/BV1qs41157Lr?p=49

三十六、数据透视表

格式

首先要注意如果想制作数据透视表，需要严格遵守格式的需求，例如：不能有合并单元格，数字列必须是数字格式，不能是文本格式…
除此之外，还要理解一些名词
字段：每一列
字段标题：就是每一列的标题
记录：表中除去标题的每一行都是一行记录

本质上

本质上就是多去尝试这些组合，这里的原理和tableau是一样的

合理使用创建组功能，实现更复杂的数据显示。

双击透视表标题，可以选择计算不同的值。

除此之外，还可以通过将数值信息插入到行标签和数值中，并且设置分组，这研究可以统计数值的个数，以及某一区段该数值的个数。

计算字段和计算项

计算字段用于增加新列
计算项是用于增加新行
单击行标签中的任意一项，在数据透视表工具-选项中，找到域、项目和集，点击就可以创建计算字段或者计算项了。一般会用于用公式处理特定的数据透视表中的内容。

三十七、打印

打印时候合理运用分页预览

并且适当的添加分页符，可以使打印效果更好。

使用打印标题来实现多个纸张的打印都有标题

使用数据-分级显示-分类汇总，勾选其中的每组数据分页，可以实现快速分组，提高打印效率。

三十七、页眉页脚

设置页眉页脚最好的方法是在

这个选项中进行设置，所见即所得，非常适合后期打印。

总结

骚操作还有很多，应该会持续更新，之后再做系统的整理。