Excel从入门到表格分析（一）
Excel电子表格
在分析数据之前，我们首先要对数据进行预处理，比如：清洗、提取、整合。清洗是指对数据的缺失值、异常值进行处理；提取是指将不需要的字段进行删除；整合是指存在另外一种或几种数据维度在当前表当中不存在，需要从其他数据源整合到当前数据表。Excel电子表格可以帮助我们实现这些功能。得益于技术的发展，Excel电子表格不但可以进行基础的增删改查，而且可以创建可视化图表、透视表、自动化报表，支持打印、数据自动填充等自动化功能。在Excel表格的顶端有一些功能栏目：File （文件）, Home（开始）, Insert（插入）, PageLayout（页面布局）, Formulas（公式）, Data（数据）, Review（审阅）, View（视图）。以下表格概括了他们的功能：
| 栏目 | 功能 |
|---|---|
| 文件 | 主要涉及到文件保存、打开、分享、导出为pdf等功能 |
| 开始 | 调整文本样式、比如字体的大小颜色，数据的格式，表格的颜色格式，以及基础的增删操作等 |
| 插入 | 引入现有数据之外的媒介，比如透视表、图片、各个类型的图表、批注等 |
| 页面布局 | 打印时的布局调整，比如整体的主题颜色，字体大小，页面边距，横向纵向等 |
| 公式 | 提供了所有的计算公式，并且按照处理的数据对象进行了分类，比如最常用的文本函数，查找引用函数，以及数据函数等。也可以定义自有的公式进行引用。 |
| 数据 | 主要用于引入数据源，格式可以是网页、文本、数据库等。还可以对数据进行一些预处理工作，比如筛选、删除重复项，对基础统计指标的一些计算等。 |
| 审阅 | 提供了用于多人协作的批注功能，以及权限设置功能，文件保护等。 |
| 视图 | 提供便于阅读习惯的一些功能，比如单页或者多页报表样式，缩放大小。如果数据量很多，可以使用冻结行列数据固定数据维度，方便操作。如果重复操作比较多，可以通过录制宏的方式将多个操作记录为一个快捷键，然后使用。 |
Excel常用功能
Excel的功能十分强大。对于数据预处理，我们可以使用Excel的文本函数，重复数据的处理，拆分列数据，和数据排序与筛选等功能。在进行数据查找和计算（统计和分析）时，我们可以使用：
- 数学函数： SUM, AVERAGE
- 逻辑函数：OR, NOT
- 条件聚合函数：COUNTIF， SUMIF
- LOOKUP引用函数
当然，Excel可以创建可视化图表，比如数据透视表（Pivot Table），各种统计图表来进行数据可视化。
文本函数
文本函数是对文本进行提取、查找、转换、更新的函数。比如，我想讲字符1的列表和字符2的列表进行合并：
| 字符1 | 字符2 |
|---|---|
| A1 | B1 |
| A2 | B2 |
| A3 | B3 |
| A4 | B4 |
| A5 | B5 |
| A6 | B6 |
| A7 | B7 |
| A8 | B8 |
这时，我们需要使用文本合并类函数，比如CONCATENATE
如果我们想提取一列中的部分文字，可以使用MID函数。MID函数的用法是MID(text, start_num, num_chars)。这里，text是需要提取的单元格，start_num指从哪一位开始提取（start_num>=1），num_chars表示一共提取多少位。
比如，有这样一列：
我们的需求是去掉工作经验一栏中的“经验”二字，并且保留其余的文字。不难发现，“经验”二字全部为前两个字。所以我们可以使用
MID函数对这一列除去“经验”的文字进行提取。首先编写第二行的文本提取：=MID(F2,3,6)，表示提取F2的内容，从第3位开始提取，提取的总长度为6。然后再用自动补全（将鼠标移动至G2单元格右下角，出现+号指针，并且双击右下角），将整个G列填充，结果如下图：[图片上传失败...(image-8848b4-1609657966384)]
如果我们想将一列中的指定字符替换成想要的字符，可以使用
SUBSTITUTE函数。SUBSTITUTE函数的用法是SUBSTITUTE(text, old_text, new_text, instance_num)，这里，text是要被替换的单元格，old_text是将要被替换的指定字符串，new_text是想要替换的目标字符串，instance_num是指被替换字符串如果出现多次，替换第几个出现的字符串，如果为空，则默认全部替换。
比如，有这样一列：
我们的需求是将此列中所有内容为1的单元格替换为“本科及以上”。所以，我们可以使用
SUBSTITUTE函数，对于文本替换列，我们输入=SUBSTITUTE(H2,1,"本科及以上")，并且使用自动填充，即可得到结果：
如果我们想验证某一列中是否存在指定字符串（数据验证），可以使用FIND函数。FIND函数的用法是FIND(find_text, within_text, start_num)，这里,find_text是指要查找的字符串，within_text是要在其中搜索的字符串单元格，start_num是指开始搜索的位置，如果忽略，start_num默认为1。
比如有这样一列：
我们的需求是判断此列中是否存在“数据分析”这一关键字。所以，我们首先使用
FIND函数，对于数据验证列，我们输入=FIND("数据分析",J2,1)，并且使用自动填充，可以得到结果：
我们可以看到，由于有一些类别中并非存在“数据分析”关键字，所以结果中会有错误#VALUE!。对于这样的情况，我们可以进一步使用ISNUMBER函数来同一格式。如果单元格内容为数字，ISNUMBER函数返回TRUE，否则返回FALSE。在FIND函数外再嵌套ISNUMBER函数，我们可以得到结果：
其实，掌握了函数的使用方法，我们可以根据具体需求使用不同种类的函数。微软官方也提供了全部函数的使用指南：https://support.microsoft.com/zh-cn/office/excel-%E5%87%BD%E6%95%B0%EF%BC%88%E6%8C%89%E5%AD%97%E6%AF%8D%E9%A1%BA%E5%BA%8F%EF%BC%89-b3944572-255d-4efb-bb96-c6d90033e188。
数学函数
前文中提到的三个函数都是对文本数据进行处理（查找、提取、替换），所以会使用文本函数进行处理。文本函数更多的是对文本进行清洗、格式化。对于数值型数据，我们可以使用数学函数，进行一定的统计计算。我们大致可以将数学函数分为几类：
| 类型 | 关键字 |
| :---: | :----: | :----: |
| 基本换算 | SQRT（平方根）, POWER（幂运算）|
| 基础统计 | SUM（求和），AVERAGE（平均值）|
| 类型转换 | INT （求整），EVEN（求最近的偶数）|
这些计算都是基本的数值运算，下面的例子展示了他们的用法：
运算的结果是：
需要留意的是，数学函数的输入参数既可以是手动输入的数值数据（非单元格数据），也可以是单元格数据。比如，统计求和函数
SUM()可以统计一列单元格的总和：
在之前的章节中，我们提到了集中趋势指标和离散趋势指标。我们可以利用数学函数来计算以下工资表的各种指标：
计算结果如下：
处理重复数据
在真实的资料分析工作中，数据的预处理是指数据的清洗、格式化、去重、排序和基础计算。这一步很可能会占据整个分析工作50%以上的时间。为什么数据预处理如此地重要呢？是因为如果数据预处理没有做好，比如存在一些缺失值、异常值，那么最后的数据分析结果的准确性会直接受到影响，从而造成错误的统计结果和结论。之前的章节已经介绍了如何处理缺失值和异常值。对于缺失值、异常值可以进行删除或者填充平均值的方法进行处理。那么对于重复数据，应该如何处理呢？
首先我们需要定位重复数据。EXCEL已经提供了定位重复数据的功能。在开始菜单中找到条件格式，选择突出显示单元格规则中的重复值，重复的数据就会被高亮：
但是，条件格式在查找多列的重复数据时并不好用，因为它会将很多并非每列都重复的单元格也标红，比如以下例子：
那么对于涉及多列的重复值筛选操作应该如何完成呢？比如我们现在有这样一张招聘数据表：
显然，后三行和第三行到第五行是重复的。我们首先选择想要清除重复数据的区域，然后在数据菜单栏中找到删除重复项，之后会弹出一个删除重复项的列表选择，我们可以针对每一列进行重复值筛选。
最后会显示删除了几个重复项：
（未完待续）
