翻译 | 简单而有效的EXCEL数据分析小技巧

本文转载自：CDA数据分析师（ID：cadcdacda）

文 | Sunil Ray

翻译 | 沙拉丁

介绍

我一直很欣赏EXCEL蕴藏的巨大能量。这款软件不仅具备基本的数据运算，还能使用它对数据进行分析。EXCEL被广泛运用到很多领域，例如：金融建模和商业预测。对于刚进入数据分析行业新手来说，EXCEL可以被当做一款入门的软件。

甚至在学习R或Python前，对于新入门的小白来说，事先掌握一定的EXCEL知识是百利而无一害。EXCEL凭借其功能强大的函数、可视化图表、以及整齐排列的电子表格功能，使你能够快速而深入的洞察到数据不轻易为人所知的一面。

但与此同时，EXCEL也有它的一些不足之处，即它无法非常有效的处理大型数据。这是我曾经遇到的这个问题。当我尝试使用EXCEL处理含有20万行数据的数据集时，就会发现EXCEL运行的非常吃力。EXCEL并不适用于处理海量数据，虽然在某种程度上，可以通过一些其他的方法让EXCEL处理大型数据集，但我更推荐使用R或Python去处理，而不是EXCEL。

我感到非常荣幸，在我的职业生涯开始的时候就接触到了EXCEL。工作了这么多年后，我已经掌握了很多比以前更快处理数据的方法。EXCEL有着丰富的函数，使得我们通常困扰如何选择最有效的那个。在这篇文章中，我将会提到一些关于EXCEL使用方面的小技巧，从而可以节省你宝贵的时间。同时，这篇文章也适合热衷于提升自己的数据分析技能的人。

提示：如果你认为自己可以熟练使用代码去进行数据处理方面的运算，那么阅读本文将对你没有太多用处。而对于其他人，我建议你学习这些技巧，从而更深入的掌握并理解如何使用。

常用的函数

1.Vlooup()：它可以帮助你在表格中搜索并返回相应的值。让我们来看看下面Policy表和Customer表。在Policy表中，我们需要根据共同字段 “Customer id”将Customer表内City字段的信息匹配到Policy表中。这时，我们可以使用Vlookup()函数来执行这项任务。

对于上面的问题，我们可以在F4单元格中输入公式“=VLOOKUP(B4， $H$4：$L$15， 5， 0)”。按回车键后，在City字段下将会返回所有Customer id为1的城市名称，然后将公式复制到其他单元格中，从而匹配所有对应的值。

提示：在复制公式中请别忘记使用符号“$”，来锁定Customer表的查询范围。这被称之为绝对引用，也是经常容易出错的地方。

2. CONCATINATE()：这个函数可以将两个或更多单元格的内容进行联接并存入到一个单元格中。例如：我们希望通过联接Host Name和Request path字段来创建一个新的URL字段。

上面的问题可以通过使用公式“ =concatenate(B3，C3)” 并且下拉复制公式来解决。

提示：相对于“concatenate”函数，我更倾向于使用连接符“&”来解决上述问题，公式为“= B3&C3”。

3. LEN()-这个公式可以以数字的形式返回单元格内数据的长度，包括空格和特殊符号。

示例：=Len(B3) =23

4. LOWER()， UPPER() and PROPER()—这三个函数用以改变单元格内容的小写、大写以及首字母大写（即每个单词的第一个字母）。

在数据分析的项目中，这些函数对于将不同大小写形式的内容转换成统一的形式将会非常有用。否则，处理这些具有不同特征的内容将会非常麻烦。

下面的截图中，A列有五种形式的内容，而B列只有两种，这是因为我们已经将内容转换成了小写。

5. TRIM()：这是一个简单方便的函数，可以被用于清洗具有前缀或后缀的文本内容。通常，当你将数据库中的数据进行转储时，这些正在处理的文本数据将会保留字符串内部作为词与词之间分隔的空格。并且，如果你对这些内容不进行处理，后面的分析中将产生很多麻烦。

6. If()：我认为在EXCEL众多函数之中最有用的一个。当特定的事件在某个条件下为真，并且另一个条件为假时，可以使用这个公式来进行条件运算。例如：你想对每个销售订单进行评级，“高级”和“低级”。假设销售额大于或等于5000，则标记为“高级”，否则被标记为“低级”。

由数据得出结论

1. 数据透视表：每当你在处理公司的数据时，你需要从“北区分公司贡献的收入是多少？”或“客户购买产品A订单的平均价格是多少？”以及许多类似的其它问题中寻找答案。

EXCEL的数据透视表将会帮你轻松的找到这些问题的答案。数据透视表是一款用于汇总如：计数，求平均值，求和，以及其他依据相关选择进行特征计算的功能。它可以将数据表转换为反应数据结论的表格，从而帮助你做出决策。请看下面的截图：

从上图可以看出，左边的表格中有销售产品的细节内容，即以区域分布和产品的对应关系匹配到每一个客户。在右边的表格中，我们按不同区域进行了汇总，并且帮助我们得出了南区有着最高销售额的结论。

• 创建数据透视表的方法：

第一步：点击数据列表内的任何区域，选择：插入—数据透视表。EXCEL将会自动选择包含数据的区域，包括标题名称。如果系统自动选择的区域不正确，则可人为的进行修改。建议将数据透视表创建到新的工作表，点击New Worksheet（新工作表），然后点击OK。

第二步：现在，你可以看到数据透视表的选项板了，包含了所有已选的字段。你要做的就是把他们放在选项板的过滤器中，就可以看到在左边生成相应的数据透视表。

从上图可以看到，我们将“Region”放入行，“Productid”放入列中，“Premium”放入值中。现在，数据透视表中展示了“Premium”按照不同区域、不同产品费用的汇总情况。你也可以选择计数、平均值、最小值、最大值以及其他的统计指标。

2.创建图表：在EXCEL里面创建一个图表，你只要选择相应的数据，然后按F11，就会自动生成系统默认的图表。除此之外，你可以手工改变不同的图表类型。如果你倾向于在当前工作表中生成图表，可以按ALT F1，而不是F11。

当然，在任何一种情况下，只要你创建了图表，就可以通过定义特定数据源来展示期望的信息。

数据清洗

1.删除重复值：EXCEL有内置的功能，可以删除表中的重复值。它可以删除所选列中所含的重复值，也就是说，如果选择了两列，就会查找两列数据的相同组合，并删除。

如上图所示，可以看到A001 和 A002有重复的值，但是如果同时选定“ID”和“Name”列，将只会删除重复值（A002，2）。

按照下列步骤操作可以删除重复值：选择所需数据-转到数据面板-删除重复值

2.文本分列：假设你的数据存储在一列中，如下图所示：

如上如所示，我们可以看到A列中单元格内容被“；”所区分。我们需要将其进行分列，建议使用EXCEL的文本分列功能。按照下面的步骤可以实现分列：

• 1.选择A1：A6

• 2.点击：数据—分列

上图中，有两个选项，“分隔符号”和“固定宽度”。我选择“分隔符号”是因为有分隔符“；”。如果我们希望按照宽度分列，例如：前四个字符为第一列，第五到第十个字符为第二列，则可以选择按固定宽度分列。

• 3.点击下一步—点击“分号”，然后下一步，然后点击完成。

基本的快捷键

通过快捷键来浏览单元格或更快速地输入公式的是最佳的途径。下面列出了最常用的几种快捷键：

1.按Ctrl [向下|向上箭头]：移动到当前列的最底部或最顶部，按Ctrl [向左|向右箭头]，移动到当前行的最左端和最右端。

2.按Ctrl Shift 向下/向上箭头：选择包括从当前单元格直到最顶部或最底部范围内的数据。

3.Ctrl Home：定位到单元格A1

4.Ctrl End：导航到包含数据的最右下角的单元格

5.ALT F1：  创建基于所选数据集的图表。

6.按Ctrl Shift L：激活自动筛选数据功能。

7.Alt 向下箭头：打开下拉自动筛选的菜单。

8.ALT D S： 要排序的数据集

9.按Ctrl O：打开一个新的工作簿

10.按Ctrl N：创建一个新的工作簿

11.F4：选择范围，并且按F4键，可以将数据引用改为绝对引用，混合引用，相对引用。

注意：这不是一个详尽的清单，从字面上讲，我使用快捷键完成了平日工作的80%。

后记

EXCEL作为使用最广泛的数据统计分析软件，无论你是小白还是资深用户，总会有一些东西值得你去学习。

原文链接：https://www.analyticsvidhya.com/blog/2015/11/excel-tips-tricks-data-analysis/