Office三剑客之Excel

Excel的作为Office的重要三剑客之一的主要作用是：

• 记录整理
• 数据计算
• 数据分析
• 数据展现

1）界面设置：

在工具栏点击鼠标右键 -> 选择自定义功能区，就能添加工具栏的功能。

2）保存文件：

Ctrl + s：一般修改文件保存内容。

F12：另存为

3）快速填充：

Excel会根据我们输入的第一个关键词帮助我们填充其它列的数据。

案例:

根据邮箱填写名称，如lucy.123@qq.com的名称是lucy。

我们可以先输入lucy，再ctrl + e自动填充快捷键，excel就会帮助我们以第二行的匹配规则自动填充其他行的数据。

快速填充：

• 快捷键：ctrl + e
• 下拉选择

• 工具栏选择：

注意：

1.自动填充需要列和列之间不出现断层否则就会报错：

2.自动填充需要前列和后列能有一个准确的匹配规则否则无法自动填充：

3.自动填充不仅可以按照拆分规则提取数据还可以按照合并规则帮助我们合并数据：

4.如果发现自动填充不准确，可以手动多输入几列，这样Excel识别就会准确些。

4）快速分析：

通过分析数据格式化表格，生成图表等。

快速分析的主要作用就是帮助我们以图表的形式更直观的展示数据。

更具体的操作可以选择工具栏中的条件格式：

5）数据录入：

我们在写Excel数据时，最好是横向录入；

输入技巧：

• 上下左右键可以位移对焦列：

• 回车可以跳到下一列：

• ctrl + 回车原位填充，就是对焦原位不动，不向下移动。

• 输入空格可以删除列的内容：

• tab键可以从左到右，从上到下的切换对焦：

• shift + tab则是从右到左，从下到上的切换对焦。

5.1）特殊数据的录入：

• 时间：

  在Excel中只有yyyy-MM-dd或者yyyy/MM/dd的输入格式才会被识别为日期：

  

  通过日期筛选功能就能看到：

  

  通过工具栏常规设置日期展示格式，未能被Excel识别为日期的不能改变格式：

  

  输入日期时间快捷键：

  ctrl + ;:输入日期；ctrl + shift + ;:输入时间；ctrl + ; 空格 ctrl + shift + ;：日期 + 时间；使用now()函数输入当前的日期和时间，会动态变动。

  

• 百分比：

  直接输入30%或者输入0.6 + 工具栏的%：

  

  还可以通过工具栏的按钮添加或者删除精度。

• 分数：

  不能直接通过1/5录入会被识别为时间；要通过输入0 1/5或者输入0.2通过工具栏转成分数：

  

• 身份证：

  在Excel中输入数字最长是15位，身份证一般都是18位，超过15位会展示为科学计数法，因此我们需要将单元格的格式切换成文本：

  

  输入' + 数字也可以把数字切换成文本。

• 金钱：

  

5.2）录入数据技巧：

• 录入提示：

  工具栏数据 -> 数据验证 -> 输入信息

  

• 枚举：

  填写给出确定的几条数据，如性别只能写男或者女

  

• 数据长度校验：

  限制输入的文本长度，如身份证18位，不等于18位报错。

  

• 合并多个Excel文件数据：

  合并多个Excel文件需要格式头是一样的,并且放在同一个文件夹下。

  1.xlsx:

  

  2.xlsx:

  

  新建文件3.xlsx：

  

  合并后的文件还需要我们自己手动做一下小修改（略）。

5.3）数据爬取：

Excel自带了爬功能，我们可以爬取网页的数据。（注意爬取的数据只能是表格类型的数据）

数据 -> 来自网站

我们还可以通过数据->查询和连接设置定时的刷新数据：

5.4）数据清洗：

• 修改爬取数据的列名：

  我们不能直接修改列的名称，因为刷新数据会被覆盖掉：

  

  我们需要在查询->编辑进行数据的修改：

  

  此时我们无论怎么刷新数据，列名都不会被覆盖。

• 普通数据清洗：

  注意：清洗数据时候不要修改源数据。

  将数据格式转成表格（ctrl + t） -> 清洗操作（数据 -> 来自表格）

  

6）单元格操作：

6.1）单元格内容：

6.2）数据居中：

在excel中不建议使用合并居中：

建议使用跨列居中：

6.3）设置边框：

6.4）自定义格式：

想要自定义格式，首先得了解自定义格式代码：

7）数据验证：

为了保证输入的数据的正确性和规范性，我们可以加入数据输入验证功能：

数据校验：

数据枚举：

还有很多其它格式的数据验证，自行探究。

8）分析数据：

8.1）条件格式：

根据指定条件显示数据的格式：

8.2）筛选重复值：

通过数据->删除重复值：

弊端是会直接删除原数据。

通过表格筛选重复值：

9）定位：

我们想快速查找某些内容可以使用ctrl + f；当我们想查找对象（图片）、空值、公式列等可以在使用定位操作：

使用案例：

10）单元格技巧：

ctrl + 鼠标左键：同时选中多行:

shift + 鼠标左键：选择头部和尾部的所有区域：

ctrl + 方向键：快速跳转到表格边界：

ctrl + shift + 方向键：选择某一块区域：

冻结窗格：首行或者首列不会随着页面滚动而滚动：

11）函数：

11.1）什么是函数：

函数就是预先定义好的计算表达式。

函数模块所在位置：

使用方式：

在需要使用函数的单元格中输入=函数，例如年龄求和：

11.2）绝对引用和相对引用：

快捷键f4

1、了解写法

绝对引用：$A$1

相对引用：A1

混合引用：A$1/$A1

2、了解excel中行列的表达式

A：代表表格中的列的意思；1：代表表格中的行的意思。

如下图所示：选中F5则代表选中F列第五行

3、美元符号的含义（美元加在哪个符号前面则表示锁住所代表的行或列）

绝对引用：$A$1（表示把A列的第一行都固定住，也就是公式无论怎么拉取，引用A1这个单元格都是被锁定的）

相对引用：A1（都没有加美元，则说明公式一拉取，所引用的单元格都会跟随着位置变动而变动）

混合引用：A$1/$A1（在1前面加美元，则表示把第一行锁住，列会变动；在A前面加美元，则表示在A列锁住，行会变动）

99乘法表案例：

11.3）常用函数：

统计函数：

**COUNT()**：计算数字单元格的个数（只能统计数字单元格）；

**COUNTA()**：计算非空单元格的个数；

**COUNTBLANK()**：计算空单元格的个数；

**COUNTIF()**：通过条件判断统计个数；

**COUNTIFS()**：通过多个条件统计个数；

累加函数：

**sum()**：将多个单元格的数值进行累加。

sum()和直接相加的区别：

• sum()会过滤调空值和非数值的单元格；而直接相加不会，并且会报错。
• sum()插入新数据或者删除单元格也可以更新；直接相加不会。

需要进行相加操作最好使用sum()。

**sumif()**：通过条件筛选进行累加结果；

技巧：由于E和F列是不变的，我们可以设置为绝对引用，让条件变化即可。

**SUMPRODUCT(arr1,arr2)**：

将数组1的每一项跟数组2的每一项一对一相乘，然后进行累加；

平均值函数：

**average()**：计算平均值；

**averageif()**：通过条件计算平均值；

查找数据函数：

精确匹配案例：根据成绩单查找小明的成绩；

模糊匹配案例：通过给定的成绩等级表，给学生成绩划分等级；

12）快捷方式：

统一伸缩列宽：

选择需要一起伸缩的列，并且按下ctrl+鼠标拖拽，如没加ctrl则是普通的移动位置：

清除内容：

del:仅仅删除内容，会保留格式：

清除内容和格式，选择工具栏中的清除:

复制数据：

• 通过复制粘贴的方式：

  

• 通过原位填充的方式：

  先选择需要重复数据的区域 -> 再输入数据 -> 最后ctrl + 回车。

  

  原位填充复制的好处是可以选择多个区域同时复制数据：

  

选择所有有内容的单元格：

在有内容的单元格按下ctrl + a

自动换行：

单元格内容过长可以设置自动换行。

自动调整列宽：

影子工作簿：

在Excel中工作簿就是我们操作的页面，影子工作簿就是我们可以创建一个跟当前一模一样的工作簿进行操作，并且当前工作簿和影子工作簿的操作是相互同步的：

有了影子工作簿我们可以同时操作一个工作簿上的两个不同的sheet页了。

一键求和：

alt +：一键求和；

文本连接符：

在Excel中我们要连接两个文本可以复制粘贴，但是如果我们要连接两个单元格的内容最好是使用文本连接符&:

直接连接会提示错误：

添加文本连接后正常显示：

通配符：

Excel中一共有三种类型的通配符，分别为“*”，“？”，“~”。

*：代表任何的字符。

?：代表任何的单个字符。

~：代表解除字符的通配性，如~*输出为*。

通配符通常用于查找和函数中：

查找案例：

函数案例：

统计第二个字是明字的个数

查找/替换：

ctrl + f：查找

ctrl + h：替换

技巧：

1. 选择列再查找，即可只在单列进行查找；
2. 通过格式进行查找，如颜色；
3. 还可以通过通配符进行查找。

分列：

将一个单元格的数据根据符号或者固定宽度分为多列；

通过分列将非标准日期转为标准日期，并提取月和日：

排序：

对焦到需要排序的列，点击排序：

多条件排序：

对学生成绩进行排序，并输出序号：

方式1：先排序再输入序号

缺点就是不能根据成绩的改变而改变排序序号。

方式2：通过rank()函数进行排序；

优点就是可以根据分数变动而变动；实际操作更推荐这种方式。

筛选：

将数据进行过滤处理：

筛选前三名：

筛选小花或小明：

转置：

将标题行跟标题列进行位置转变；