数据分析15——office中的Excel基础技术汇总

0、前言：

• 这部分总结就是总结每个基础技术的定义，在了解基础技术名称和定义后，方便对相关技术进行检索学习。笔记不会详细到所有操作都说明，但会把基础操作的名称及作用说明，可自行检索。
• 本文对于大部分读者有以下作用
  • 1、可以检索一些excel中常用的操作名称
  • 2、获取office的excel

1、基础总结：

• 为什么要安装office的excel，而用wps不行？
  • 因为数据分析中excel是个重要的工具，wps中缺乏一些专业功能，或者说大多数人更喜欢白P，而WPS看似白P，实则付费，excel看似付费，但破解之后可以完美白P所有功能。
  • 安装资料：
    链接：https://pan.baidu.com/s/1vE7-EGl-J1dmP2yiqzj4Nw
    提取码：1234
• 保护设置：
  • 1、保护工作簿：将整个excel文件进行加密
  • 2、保护工作簿的结构：不能操作excel，只能查看
  • 3、保护工作表：对工作表进行加密
  • 4、保护单元格：对部分单元格进行加密
  • 5、excel中的数据类型：文本、数字（日期是特殊的数字）、逻辑值、错误值
  • 6、有时候在单元格里写了公式，回车后不运行，原因可能是单元格格式设置为了文本格式

---

2、快速填充：

• 了解填充柄的概念：单元格右下角的小十字（对于要填充的内容，按住ctrl和不按ctrl效果不同）
• 选择填充：掌握这种填充方式就等于掌握了所有填充方式
  • 选中一个单元格，鼠标放置在右下角，等变为填充柄，再右击，等填充柄变为鼠标再下拉，就能选择填充了。

---

3、常见错误值：

• #VALUE：文本（非文本数字）与数字运算
• #DIV/0！：除法中分母为0
• #NAME！：公式名错误
• #N/A：查找值不存在

---

4、常见操作：

• 当单元格数字超过12位时，会用科学计数法表示，可以在输入时在数字前面加一个单引号，显示原数字。
• 文本数字×1：将文本数字转化为数字，也可以选择+0的方式
• alt+=：快速求和
• ctrl+shift+方向：快速选中连续的一列或一行，ctrl+方向（快速移动到边界），shift+方向（选中一格）

5、定位条件：F5快捷键调用，目的是同一类型单元格进行批处理

• 通过定位条件可以对需要单独选中的某一类单元格进行批处理，需要注意，如果是编辑一批单元格，不能简单回车，需要用ctrl+回车。

---

6、日期类数据格式的统一

• 首先要把日期类格式处理为excel中有的形式，然后用value函数或者用×1的方式就能把文本数据变成可以统一的日期格式了。
• 对于文本数据的处理可以用text的函数，这样就能把不规则的文本日期变成excel中有的形式，最后再×1变成日期数据。

---

7、冻结窗格：把首行或者首列或者多行多列冻结，在滑动滑块时，冻结部分会一直显示，方便做数据对照。

---

8、选择性粘贴>跳过空单元：粘贴时只会粘贴有内容的部分，空值部分不会粘贴，这样粘贴到相同结果的数据中，空值部分的数据就不会被修改。

---

9、数据验证：

• 第一种：把一堆数据拿来做下拉列表
• 第二种：圈出来无效数据（前提要在数据验证中设置对应区域的数值范围，超出范围的就会把圈起来）

---

10、★★快速填充：Ctrl+E

• 你可以在某一列第一个单元格输入内容，作为例子，然后选中这个单元格，按住Ctrl+E，这一列就会按照你给的例子进行填充。
• 这里把快速填充理解为一种excel的自带的智能应用，无需公式就能填充对应的内容。

---

11、分列：把同一列的数据按照某种规则分开，该操作在数据操作栏中。

---

12、转置：把行变列或者列变行，这个操作可以用选择性粘贴实现。

---

13、合并计算：

• 把相同格式但位于不同工作表当中的数据进行合并，例如通过合并运算可以把所有分公司的数据汇总，前提是所有分公司数据格式相同，该操作在数据操作栏中。
• 注意合并计算不仅有求和还有很多其他运算

---

14、条件格式：

• 根据条件设定单元格格式，该操作在开始操作栏中。
• 其中新建规则中有很多创建规则的方式，其中通过公式创建规则的方式可以动态改变对应单元格的格式。

---

15、单元格引用

• 在公式中需要引用某个单元格，这里引用时就要区分绝对引用和相对引用
• 绝对引用：不论按行填充还是按列填充，单元格内容被锁定不变
• 相对引用：按行填充行自增，按列填充列自增

---

16、excel公式中的运算符

17、excel公式相关

• 首先要掌握运算符，注意括号在运算中是支持的
• 在公式操作栏中可以设置追踪单元格引用，从而知道对应单元格的公式用了其他哪些单元格参与运算。
• 在公式操作栏中的错误检查，可以帮你一个一个快速定位出现公式错误的单元格。
• 在公式操作栏中的公式求值，可以让你清晰看到复杂公式的每一步计算结果。

---

18、常用函数

• 统计和逻辑类：求最大（MAX）、求最小（MIN）、求和（SUM）、数值计数（COUNT）、计数（COUNTA）、求平均（AVERAGE）、条件计数（COUNTIFS）、条件求和（SUMIFS）、条件平均（AVERAGES）、频数统计（FREQUENCY）、排名（RANK）、判断（IF）、与（AND）、或（OR）、非（NOT）。
• 数学类：取整（INT）、求余数（MOD）、四舍五入（ROUND）、取绝对值（ABS）、算术平方根（SQRT）、生成随机数（RANDBETWEEN）
• 文本类：取子串（MID）、文本长度（LEN）、数字转文本（TEXT）、文本重复（REPT）、替换指定位置文本（REPLACE）
• 日期类：求年（YEAR）、求月（MONTH）、求日（DAY）、当前日期（TODAY）、当前日期和时间（NOW）、指定日期前后月份的日期（EDATE）、某个月最后一天的序列号（EOMONTH）、计算日期差（DATEDIF）
• 查找引用类：垂直查找（VLOOKUP）、偏移（OFFSET）、通过查找内容返回序号（MATCH）、通过行列号查找内容（INDEX）、文本指定的单元格引用（INDIRECT）、引用行数据（ROW）、引用列数据（COLUMN）、水平方向查找（HLOOKUP）

---

19、数组公式

• 数组：单元格或者数值的集合（理解为多个数值构成一个计算的最小单位）
• 数组公式：以数组作为参数的公式，一个公式执行多个输入操作，可能返回多个结果值
• 调用数组公式：写好公式后，用ctrl+shift+enter
• 注意数组计算结果为一个统一区域，如果要删除就要全部删除
• 举例：
  

---

20、数据透视表

• 这部分内容就是对已有的数据进行选择性查看的方法