作者 | SUNIL RAY
编译 | CDA数据分析师
Simple Yet Powerful Excel Tricks for Analyzing Data
- Microsoft Excel 是目前世界上被使用的最广泛的数据分析工具之一
- 使用 Excel 进行数据分析是使用 R 或 Python 进行数据科学的先驱
- 我们应该要学习用于分析数据的基本的 Excel 函数
介绍
我一直都很佩服 Excel 强大的数据分析能力。这款软件不仅能够进行基本的数据计算，还可以使用它来进行数据分析。它被广泛用于许多的领域内，包括财务建模和业务规划等。对于数据分析领域的新手来说，Excel 它可以成为一个很好的跳板。
甚至来说在学习 R 或 Python 之前，最好先了解一下 Excel。将 Excel 添加到你的技能库中没有什么坏处。Excel 具有非常广泛的功能：可视化功能、数组，使你能够迅速的通过数据产生洞察力，否则这些数据将很难看到价值。
当然 Excel 它也有一些缺点。比如它不能非常有效地处理大型的数据集。相信每个人都已经遇到了这个问题。当你尝试对大约 200,000 个条的数据进行数据计算的时候，你会注意到 excel 开始出现问题。当然有一些方法可以解决这个问题并在一定程度上处理这些数据，但是 Excel 并不是一个处理大数据的工具。在有大型数据集的情况下，R 或 Python 是最好的选择。
我很幸运，我学习的旅程始于 Excel。多年来，我学到了许多技巧，学会了许多可以更快处理数据的技巧。Excel 具有许多功能。选择最好的一个功能有时会会很令人困惑的。在本文中，将为大家提供一些在 Excel 的技巧，这样可以节省工作或处理数据的时间。这篇文章比较适合那些热衷于升级其数据分析技能的人们。
需要注意的是：如果你认为自己是数据科学领域的大师级人员，那么你可能觉得这篇文章对你来说不会有任何帮助。对于其他人，我建议你练习这些技巧以对它们有一个具体的了解。
常用函数
1. Vlookup（）：这个函数有助于在表中搜索值并返回相应的值。让我们看一下下表（Policy 和 Customer）。在“Policy”表中，我们希望基于公共的字段“Customer_id”从 Customer 表中找到“City”的名称。在这里，函数 vlookup（）就可以帮助你完成这项工作。
语法：= VLOOKUP（查找的值，要查找的区域，查找区域的第几列，是否相对匹配）
对于上述问题，我们可以在单元格“ F4”中将公式写为= VLOOKUP（B4，$ H $ 4：$ L $ 15，5，0），这将返回所有客户 ID 1 的城市名称并将该公式复制到所有的客户 ID 中。
提示：不要忘记使用“ $”符号锁定第二个表的范围，因为如果不锁定的话，可能会在向下复制公式的时候出现错误。而这就是所谓的相对引用。
2. CONCATINATE（）：将两个或多个单元格中的文本合并为一个单元格非常有用。例如：我们要基于主机名和请求路径的输入来创建 URL。
语法：= Concatenate（文本 1，文本 2，…..文本 n）
那么我们可以使用公式= concatenate（B3，C3）并向下复制公式来解决上述问题。
提示：当然我更喜欢使用“＆”符号，因为它比键入完整的“连接”公式要短，并且执行出来完全相同的结果。该公式也可以写为“ = B3＆C3”。
3. LEN（） 这个函数告诉你单元格的长度，也就是包括空格和特殊字符在内的字符数。
语法：= Len（文本）
例如：= Len（B3）= 23
4. LOWER（），UPPER（）和 PROPER（） –这三个函数分别帮助我们可以将文本更改为小写，大写和句子大小写（每个单词的首字母大写）。
语法：=upper（文本）/lower（文字）/proper（文字）
在数据分析项目中，这些有助于将不同案例的类别转换为单个情况下的类，否则将它们视为给定要素的不同类别。看下面的照片这种，A 列有五个类（标签），其中 B 列只有两个类（因为我们已将内容转换为小写）。
5. TRIM（）： 这是一个非常方便的函数，用于清除文本中开头和结尾的空白。通常，当你从数据库中获取数据进行单独存储的时候，你要处理的文本中的一些可能会被填充为空白。而且，如果你不处理它们，它们也将被视为列表中的一条数据，这对你来说肯定是没有帮助的。
语法：= Trim（文本）
6. If（）：我发现这个函数是excel中最有用价值的函数之一。它使你可以使用条件公式，当某件事为真时，它使用一种计算方式，而当某件事为假时，则进行另一种计算方式。例如，你要将每个销售标记为“高”和“低”。如果销售额大于或等于$ 5000，则标记为“高”，否则标记为“低”。
语法：= IF（条件，条件为真的计算，条件为假的计算）
从数据产生推理
1.数据透视表： 当你处理公司的数据时，你都在寻找问题的答案，例如“北部地区的分公司贡献了多少收入？”或“产品 A 的平均客户数量是多少？”等等。
Excel 的数据透视表可帮助你轻松地回答这些问题。数据透视表是一个汇总表，可以让你根据所选的特征进行计数，求平均值，求和并执行其他计算，即，它将数据表转换为推理表，这有助于我们做出决策。查看下面的图片：
在上面，你可以看到左侧的表具有针对每个客户的销售明细，并提供了区域和产品。在右表中，我们总结了区域级别的信息，现在可以帮助我们生成一个推论，也就是南部地区的销售额最高。
创建数据透视表的方法： 步骤 1：单击数据列表中的某处。选择插入选项卡，然后单击数据透视表。Excel 将自动选择包含数据（包括标题）的区域。如果没有正确选择区域，请在区域上拖动以手动选择合适的区域。最好将数据透视表放在新的工作表上，因此单击“ 新建工作表 ”作为位置，然后单击“ 确定”。
步骤 2：现在，你可以看到“数据透视表字段列表”的面板，其中包含列表中的字段；你需要做的就是将它们放在面板底部的框框中。完成此操作后，左侧的图表将成为数据透视表。
在上面，你可以看到我们在行中安排了“Region”，在列中安排了“Product id”，并以“Premium”的总和作为值。现在你准备好使用数据透视表了，它显示地区和产品保费总和。您还可以使用计数、平均值、最小值、最大值和其他汇总度量。。
2.创建图表：在 excel 中创建图表只需要选择要绘制的数据范围并按 F11 即可。这会创建一个默认图表样式的 excel 图表，但您可以通过选择不同的图表样式来更改它。如果您希望图表与数据位于相同的工作表上，请按 ALT + F1 而不是按 F11。
当然，无论哪种情况，一旦创建了图表，就可以根据自己的特定需求进行自定义图表，以传达所需的消息。
数据清理
1.删除重复值： Excel 具有内置的功能，可以从表中删除重复值。它根据选定的列从给定表中删除重复的值，即，如果你选择了两列，则它将搜索具有两列数据相同组合的重复值。
在上面，您可以看到 A001 和 A002 有重复的值，但是如果我们同时选择“ID”和“Name”列，那么我们只有一个重复的值(A002, 2)。请按照以下步骤删除重复值：选择数据–>转到数据功能区–>删除重复项
2.文本分列： 假设你将数据存储在列中，如下图片所示。
在上面，你可以看到值之间用分号“;”分隔。现在要将这些值拆分到不同的列中，我建议在 excel 中使用“ 分列 ”功能。请按照以下步骤将其转换为不同的列：
- 选择范围 A1：A6
- 转到“数据”功能区–>“文本到列”
- 在上方，我们有两个选项“分隔符号”和“固定宽度”。我选择分隔符号的原因是因为值由分隔符（;）分隔。如果我们想根据宽度拆分数据（例如，前四个字符为第一列，第五个字符到第十个字符为第二列），则可以选择固定宽度。
- 单击下一步–>标记“半冒号”复选框，然后单击下一步并完成。
基本键盘快捷键
键盘快捷键是快速浏览单元格或更快输入公式的最佳方法。在下面列出了我们的经常会使用的一些快捷键。
- Ctrl + [向下|向上箭头]： 移动到当前列的顶部或底部单元格，并结合ctrl+[左|右]箭头键，移动到当前行中最左侧或最右侧的单元格
- Ctrl + Shift +向下/向上箭头： 选择当前单元格上方或下方的所有单元格
- Ctrl +主页： 移动到单元格 A1
- Ctrl + End： 移动到包含数据的最后一个单元格
- Alt + F1： 基于所选数据集创建一个图表。
- Ctrl + Shift + L：激活对数据表的自动筛选
- Alt +向下箭头： 打开自动筛选的下拉菜单。使用此快捷方式：
- Alt + D + S： 对数据集进行排序
- Ctrl + O：打开一个新工作簿
- Ctrl + N：创建一个新工作簿
- F4：选择范围并按 F4 键，它将参考值更改为绝对值，混合值和相对值。
注意：这不是一个完全的快捷键列表。你可以在下面的评论部分中随意分享你在 Excel 中最喜欢的键盘快捷键。实际上，我使用这些快捷方式基本上完成了 80％的 excel 任务。
结语
Excel 可以说是有史以来最好的程序之一，它一直是全球几乎所有企业的黄金选择标准。但是，无论你是新手还是高级用户，总有一些东西需要学习。
扫码进入 CDA 官方小程序，解锁更多新鲜资讯和优质内容，还有免费试听课程，不要错过哟！