表格占据整个页面_Excel从入门到表格分析（一）

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列填充，结果如下图：

如果我们想将一列中的指定字符替换成想要的字符，可以使用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已经提供了定位重复数据的功能。在开始菜单中找到条件格式，选择突出显示单元格规则中的重复值，重复的数据就会被高亮：

但是，条件格式在查找多列的重复数据时并不好用，因为它会将很多并非每列都重复的单元格也标红，比如以下例子：那么对于涉及多列的重复值筛选操作应该如何完成呢？比如我们现在有这样一张招聘数据表：

显然，后三行和第三行到第五行是重复的。我们首先选择想要清除重复数据的区域，然后在数据菜单栏中找到删除重复项，之后会弹出一个删除重复项的列表选择，我们可以针对每一列进行重复值筛选。

最后会显示删除了几个重复项：

(未完待续)