数据分析师技术和必备知识汇总-----Excel-----软件操作技巧

主文章：数据分析师技术和必备知识汇总-----Excle-----持续更新，https://blog.youkuaiyun.com/grd_java/article/details/140174090

1. 快捷键

1. Ctrl+方向键

对单元格光标快速移动，移动到数据边缘（空白单元格位置前，例如1000条数据，中间500位置有个空白单元格，那么会停留在499位置）

2. Ctrl+Shift+方向键

对单元格快速框选，选择到数据边缘（空格位置）

3. Ctrl+Shift+L

筛选

4. Ctrl+A

全选，选中整张表

5. Alt+Enter

手动换行

6. Alt+=

快速求和

7. Ctrl+E

快速拆分/合并数据

8. Ctrl+T

套用表格样式

9. Ctrl+Enter

快速填充

2. 常用功能

2.1 冻结窗格、数据验证和条件格式

冻结窗格

点击视图选项卡，冻结窗格按钮，

此时你会发现，被冻结的窗格不会随着滚动条滚动而滚动

数据验证

1. 选择要验证的单元格区域，选择数据选项卡，有效性菜单，有效性选项
   
2. 弹出的数据有效性菜单中，允许的范围我们规定为序列，数据来源，我们手动输入1,2,3,4,5。表示这个序列只能取这5个数字
   
3. 此时，这些单元格只能输入1,2,3,4,5这5个数字，其它值都会被校验为不符合限制条件
   

条件格式

1. 数据条：以数据条的形式反应数值大小关系.操作方式：选择数据区域，选择开始菜单下——条件格式——数据条

2. 色阶：和数据条同理，只不过是按颜色深浅来表达数据大小关系

3. 低于平均值：将所选区域中的数值，所有低于平均值的标识出来.选择开始菜单下——条件格式——项目选取规则——低于平均值
   
   

4. 找出所有大于70的，选择开始菜单下——条件格式——突出显示单元格规则——大于
   
   
   

5. 自定义规则，将报名金额>70的学员姓名设置为红色，这个就涉及到两列的数据。选择条件格式中——新建规则——使用公式指定
   
   
   

2.2 分列与数据去重

分列

用于把一列分开或者数据格式转换

分列用法一：将数据分成若干列

1. 选择要分列的数据，然后选择数据选项卡——分列按钮——分列——弹出的对话框中，选择分隔符号——
   

2. 指定分隔符，我们可以看到我们的数据是通过-分隔的，所以我们设定分隔符为-。
   
   

分列用法二：将格式统一

1. 可以看到下图中的日期数据格式是不统一的，有小数点分隔，有逗号分隔，有横杠分隔

2. 一样的操作，但是我们分隔符号那里可以什么都不选
   

3. 数据类型中，我们选择日期格式
   

4. 可见，数据类型统一了
   

分列用法三：文本类型转数值类型

1. 只需要在分裂最后一步的数据类型设置为常规即可
   
2. 就会发现我们的字符串变成了数值，可以参与运算了

数据去重

1. 菜单法：数据选项卡——删除重复值
   

2. 条件格式: 主要就是利用这个功能来讲重复值标记出来
   

3. 公式标记法：利用公式来标记我们的重复值，例如countifs函数，这个前面介绍过，这里不再赘述

前面讲《一、数据清洗的7.排名排序时的rank排序》，有讲过排序后如何顺位修正问题，里面涉及到这个操作。

2.3 高级筛选

筛选

快捷键前面介绍过

高级筛选

1. 现在有如下表格
   

2. 将表格中，分校包含"广州"二字的数据，或者二级项目时"幼儿园"的数据提取出来，筛选出来的数据只要分校、项目名称、二级项目和学院姓名，其它数据省略

3. 如何去做呢？首先高级筛选需要我们建立条件区域。也就是将我们要筛选的数据名称规定出来，我们需要分校、项目名称、二级项目和学院姓名
   

4. 然后我们要在条件区域写上我们的筛选条件，也就是分校包含广州，或者二级项目是幼儿园
   

1. 上图中，我们规定，分校要包含广州二字，所以写*广州*,其中星号表示通配符，匹配任意字符
2. 我们规定，两个条件是或者的关系，也就是分校包含广州，二级项目为幼儿园两个条件满足一个就可以
3. 很简单，只需要将条件错开行写就行，代表两个条件是或的关系。如果写在同一行表示与的关系，必须同时满足。所以我们这里将*广州*和幼儿园分两行写

5. 选择数据选项卡——筛选——高级筛选选项
   

6. 首先选择我们要筛选的数据源，也就是列表区域
   

7. 然后选择我们的条件区域
   

8. 如果要将筛选结果复制到其它位置，就选择一个结果位置，但是必须规定要筛选的数据是什么，也就是分校、项目名称、二级项目和学院姓名
   

9. 我们选择多少行，筛选结果就会显示多少行。除非在上一步将扩展结果区域勾选上
   

10. 将扩展勾选上后，就可以复制所有筛选数据
    
    

2.4 自定义名称

1. 公式选项卡下的定义的名称菜单中，选择更加所选内容创建名称，创建名称后，相当于给数据起变量名，方便我们公式中引用
   

2. 创建后，选择名称管理器可看到我们的所有名称
   

3. 这些名称可以让我们写公式时更加方便，例如我们直接编写公式=项目1，自动引用了这一行数据
   

2.5 分组分级和序列填充

组合和分级，下面我们以列为例，以行进行操作是相同的道理

1. 选择要组合的数据，然后选择数据选项卡——组合
   

2. 此时，数据上方就会出现一个折叠和展开按钮，单击即可展开和折叠
   

3. 组合中还可以继续组合，这种方式我们叫分级操作
   

清除分级显示: 选中要取消分级的区域，然后只需要在取消组合中单击清除分级显示即可

序列填充

以等比数列为例

1. 先输入起始值，例如1
2. 然后选择开始选项卡——编辑菜单——填充——序列。然后选择序列产生在列，类型选择等比。步长值和终止值大家视情况而定
   
   

2.6 定位操作

根据条件定位指定单元格

1. 假设我们要定位下表中所有空单元格
   

2. 选中单元格区域后，选择开始选项卡——查找与旋转——定位条件,或者快捷键CTRL+G
   

3. 我们这里肯定选择空值
   

4. 成功定位了
   

利用定位制作工资条

1. 构建辅助列，自动填充
   
   

2. 定位空单元格后，插入整行
   

3. 复制工资条
   

4. 选中第一列，定位所有空行，然后粘贴工资条，这样每个员工的工资条就都出来了