CHAP5:组织数据以开始分析

1.组织数据以开始分析

组织数据使数据更易于在分析中使用。在这部分课程中，您将了解通过排序和过滤来组织数据的重要性。您将探索在电子表格中以及使用 SQL 查询和临时表来组织数据。

分析的4个阶段

1. 组织数据
2. 格式化和调整数据
   格式化数据可以简化事情并节省您的时间。
3. 获得他人的意见
   征求意见是指从其他来源征求信息，为你的决策提供信息。
   在分析数据时，获得他人的意见是很重要的，因为它给了你一个你可能不理解或无法获得的观点。在获得他人意见的基础上，尽早寻求他人的观点也很重要。这样，如果他们预测到任何障碍或挑战，你会事先知道。你要找的人不一定是专家才会有帮助。有时，你所需要的只是熟悉你所考虑的主题或数据的人。
4. 通过观察关系转换数据在数据点之间进行计算
   转换数据意味着识别数据之间的关系和模式，并根据你所掌握的数据进行计算。

1.1排序与过滤

排序和过滤是您在格式化和调整数据以使用它时保持事物井井有条的两种方式。例如，过滤器可以帮助您查找错误或异常值，以便您可以在分析之前修复或标记它们。异常值是与类似收集的数据有很大不同的数据点，可能不是可靠的值。过滤数据的好处是，在您修复错误或识别异常值后，您可以删除过滤器并将数据返回到其原始组织。

排序是指将数据排列成有意义的顺序，以使其更易于理解、分析和可视化。它根据您选择的特定指标对您的数据进行排名。您可以对电子表格、SQL 数据库（当您的数据集对于电子表格而言太大）和文档中的表格中的数据进行排序。

当您只对查看满足特定条件的数据感兴趣并隐藏其余数据时使用过滤。当您有大量数据时，过滤非常有用。您可以通过关注真正重要的数据或存在错误或错误的数据来节省时间。大多数电子表格和 SQL 数据库允许您以多种方式过滤数据。过滤使您能够轻松找到所需内容。

回顾一下，记住排序和过滤之间区别的最简单方法是，您可以使用排序来快速排序数据，并过滤以仅显示符合您选择的条件的数据。当您需要减少显示的数据量时使用过滤。

需要指出的是，过滤数据后，也可以对过滤后的数据进行排序。如果您重新查看找出谁在 10 月份观看电影的示例，在您过滤了 10 月份看过的电影之后，您可以按字母顺序对观看这些电影的人的姓名进行排序。

1.1.1在数据透视表中排序

数据透视表的行和列区域中的项目首先按任何自定义列表按升序排序。例如，如果您的列表包含星期几，则数据透视表允许工作日和月份名称按如下方式排序：星期一、星期二、星期三等，而不是像这样按字母顺序排列：星期五、星期一、星期六等。

如果项目不在自定义列表中，则默认按升序排序。但是，如果您按降序排序，您将设置一个规则来控制字段的排序方式，即使在添加了新数据字段之后也是如此。

排序函数SORT()

eg:sort(A2:D6,2,TRUE) 对A2:D6表格排序，按第2列也就是B列排序，TRUE是ascending,FALSE是descending.

1.1.2在SQL中排序

ORDER BY XXX (ASC)DESC LIMIT 10

2.格式化和调整您的数据

随着您越来越接近分析数据，您将希望将数据格式化并准备好使用。在这部分课程中，您将学习有关转换和格式化数据的所有内容，包括如何使用 SQL 查询来组合数据。您还将发现来自同事的反馈和支持的价值，以及它如何导致您可以将新见解应用到您的工作中。

2.1转换和格式化数据

2.1.1转换电子表格中的数据

• 如何在 Excel 中将文本转换为日期：将一系列数字转换为日期是您将遇到的常见情况。该资源将帮助您了解如何使用 Excel 函数将文本和数字转换为日期，以及如何在没有公式的情况下将文本字符串转换为日期。
• 如何在 Excel 中将文本转换为数字：即使电子表格中的值类似于数字，它们实际上可能不是数字。这种转换很重要，因为它可以让您的数字相加并在 Excel 中的公式中使用而不会出现错误。
• 转换来自两个或多个单元格的文本：有时您可能需要合并来自两个或多个单元格的文本。此 Microsoft 支持页面将指导您通过两种不同的方式完成此任务，而不会丢失或更改您的数据。它还包括一个分步视频教程，以帮助指导您完成整个过程。
  CONCAT(A2,B2)
  CONCATENATE(C2," “,D2)
  CONCATENATE(C2,” “,D2,”, ",E2)
• 将数字格式化为百分比：将数字格式化为百分比对于任何项目都是一项有用的技能。此 Microsoft 支持页面将提供一些关于如何将数字显示为百分比的技术和提示。
• 华氏温度转换成摄氏温度：CONVERT(68,“F”,“C”)

2.1.2在SQL转换数据

在本阅读中，您将了解可以使用CAST函数完成的转换。还有更专业的函数，如COERCION处理大数字，UNIX_DATE处理日期。UNIX_DATE返回自 1970 年 1 月 1 日以来经过的天数，用于比较和处理多个时区的日期。您可能最常使用CAST 。

常见的转换

下表总结了一些用CAST函数进行的比较常见的转换。有关函数和相关规则的完整列表，请参考《标准SQL中的转换规则》。

CAST的语法

其中expression是要转换的数据，typename是要返回的数据类型。

• 将一个数字转换为一个字符串
  

• 将一个日期转换为一个字符串
  

• 将日期转换为日期时间
  

CONCAT

2.2 合并多个数据集

SQL的函数CONCAT

concat(a,"x",b)

3.用于分析的聚合数据。

在分析期间，您可能需要组合数据以获得洞察力并完成业务目标。在这部分课程中，您将探索用于组合或聚合数据的函数、过程和语法。您将学习如何在电子表格的多个单元格中以及使用 SQL 查询在多个数据库表中组合数据。

3.1用于数据聚合的 VLOOKUP

• VALUE(A2)：将A2（文本型）转化为数值型
• TRIM(A2)：TRIM 会自动删除单元格中任何多余的空格添加
• VLOOKUP(103,A2:B26,2,FALSE)：103是要查找的值，A2:B26是查找的范围，2是范围里的第二列，FALSE是完全匹配
  VLOOKUP只返回它找到的第一个匹配，即使有很多可能的匹配。还有一点要记住的是，VLOOKUP只能从右边的数据中返回一个值。它不能向左看。

排除故障的步骤

• 我应该如何优先考虑这些问题？
  试图一次解决很多问题会让人感到不知所措。我发现一次拿一件东西会很有帮助。
• 我所面临的问题是什么？
  这有助于澄清真正发生的事情，所以我不会被额外的细节所困住。毕竟，如果你在看数据之前没有一个明确的目标，你几乎可以找到任何东西。最好的办法是以你自己对情况的清晰理解为起点。然后让数据告诉你，你的方向是否正确。
• 什么资源可以帮助我解决问题？
  互联网是最好的资源之一。如果你有一个问题，有可能有成千上万的人遇到了完全相同的事情。因此，快速搜索可能真的很有帮助。记住人们也是资源，这很好。不要害怕问问题。这不仅是一个很好的学习方法，还可以帮助你与你的同事建立牢固的关系。
• 我怎样才能阻止这个问题在未来发生？
  如果一个新的程序或准则可以阻止同样的问题再次出现，那就可以大大节省时间。

什么时候需要使用 VLOOKUP？

使用 VLOOKUP 的两个常见原因是：

• 在电子表格中填充数据
• 将一个电子表格中的数据与另一个电子表格中的数据合并

VLOOKUP 语法

search_key

• 要搜索的值。
• 例如，42、“猫”或 I24。

range

• 搜索要考虑的范围。
• 搜索范围中的第一列以查找与 search_key 指定的值匹配的数据。

index

• 要返回的值的列索引，其中范围中的第一列编号为 1。
• 如果索引不在 1 和范围内的列数之间，#VALUE！被退回。

is_sorted

• 指示要搜索的列（指定范围的第一列）是否已排序。默认为真。
• 建议将 is_sorted 设置为 FALSE。如果设置为 FALSE，则返回完全匹配。如果有多个匹配值，则返回第一个找到的值对应的单元格内容，如果没有找到则返回#N/A。
• 如果 is_sorted 为 TRUE 或省略，则返回最接近的匹配项（小于或等于搜索键）。如果搜索列中的所有值都大于搜索键，则返回 #N/A。

如果你得到#N/A怎么办？

#N/A 表示无法作为 VLOOKUP 的结果返回匹配值。错误并不意味着数据实际上有任何问题，但如果人们在报告中看到错误，他们可能会产生疑问。您可以使用IFNA函数将 #N/A 错误替换为更具描述性的内容，例如“不存在”。

语法：

value

• 这是一个必需的值。
• 该函数检查单元格值是否与值匹配；比如#N/A。

value_if_na

• 这是一个必需的值。
• 如果单元格值与第一个参数中的值匹配，则函数返回此值；当单元格值为#N/A 时，它返回此值。

有用的 VLOOKUP 提醒

• TRUE 表示近似匹配，FALSE 表示与搜索键完全匹配。如果用于搜索关键字的数据已排序，则可以使用 TRUE。
• 您希望与 VLOOKUP 公式中的搜索键匹配的列位于数据的左侧。VLOOKUP 仅在找到匹配项后查看右侧的数据。换句话说，VLOOKUP 的索引仅指示右侧的列。这可能需要您在使用 VLOOKUP 之前移动列。
• 使用 VLOOKUP 公式填充数据后，您可以将数据复制并粘贴为值，仅删除公式，以便再次操作数据。

SQL:case when

case when a<0.2 then 'b1'
when a>=0.2 and a<0.6 then 'b2'
else 'b3'
end as b

3.2SQL 函数和子查询

SQL HAVING：这是 HAVING 子句的概述，包括它是什么以及关于它如何以及何时工作的教程。

SQL CASE：探索 CASE 语句的用法及其工作原理的示例。

SQL IF：这是 IF 函数的教程，并提供了可以练习的示例。

SQL COUNT：COUNT 函数与其他所有函数一样重要，本教程提供了多个示例供您查看。

4.执行数据计算

计算是数据分析师在分析期间执行的更常见的任务之一。在这部分课程中，您将探索电子表格和 SQL 查询中的公式、函数和数据透视表。所有这些都用于数据计算。您还将了解使用 SQL 管理临时数据库表的好处。

4.1 EXCEL

SUMIF&SUMIFS

SUMIF 函数的基本语法是：=SUMIF(range, criteria, sum_range)

您可以使用 SUMIF 计算此表中的燃料总价格，如下所示：

但是，您也可以使用 SUMIFS 函数构建多个条件。SUMIF 和 SUMIFS 非常相似，但 SUMIFS 可以包含多个条件。

基本语法是： =SUMIFS(sum_range, criteria_range1,criteria1, [criteria_range2,criteria2, …])

方括号让您知道这是可选的。语句末尾的省略号让您知道可以根据需要重复这些参数。例如，如果您想计算此表中某个日期的燃料成本总和，您可以创建一个具有多个条件的 SUMIFS 语句，如下所示：

此公式为您提供从条件中列出的日期起每笔燃料费用的总成本。在此示例中，C1:C9 是我们的第二个条件范围，日期 12/15/2020 是第二个条件。只要遵循基本语法，一条 SUMIFS 语句最多可以添加 127 个条件！

COUNTIF 到 COUNTIFS

COUNTIF 的基本语法是：=COUNTIF(range,criteria)

就像 SUMIF 一样，您设置范围，然后设置需要满足的条件。例如，如果您想计算 Food 在 Expenses 列中出现的次数，您可以使用这样的 COUNTIF 函数：

COUNTIFS 具有与 SUMIFS 相同的基本语法： =COUNTIFS(criteria_range1,criteria1, [criteria_range2,criteria2, …])

criteria_range 和criteria 顺序相同，可以在函数末尾添加更多条件。因此，如果您想查找 Coffee 在 2020 年 12 月 15 日出现在费用列中的次数，您可以使用 COUNTIFS 来应用这些条件，如下所示：

此公式遵循基本语法为“咖啡”和特定日期创建条件。现在我们可以找到这两个条件都为真的每个实例。

SUMPRODUCT

SUMPRODUCT是一个将数组相乘并返回这些乘积之和的函数。
语法：sumprodct（array1,array2……）

4.2数据透视表

4.3了解更多 SQL 计算

EXTRACT 命令让我们可以提取给定日期的一部分以供使用。

4.4数据验证过程

本阅读材料描述了六种数据验证的目的、示例和限制。前五种是与数据相关的验证类型（类型、范围、约束、一致性和结构），第六种侧重于验证用于接受来自用户输入的数据的应用程序代码。

1. 数据类型

• 目的：检查数据是否与为字段定义的数据类型匹配。
• 示例：学校 1-12 年级的数据值必须是数字数据类型。
• 限制：数据值 13 将通过数据类型验证，但将是不可接受的值。对于这种情况，还需要数据范围验证。

2. 数据范围

• 目的：检查数据是否在为字段定义的可接受值范围内。
• 示例：学校成绩的数据值应介于 1 和 12 之间。
• 限制：数据值 11.5 将在数据范围内，也将作为数字数据类型传递。但是，这是不可接受的，因为没有半分。对于这种情况，还需要数据约束验证。

3. 数据约束

• 目的：检查数据是否满足某个字段的某些条件或标准。这包括输入的数据类型以及字段的其他属性，例如字符数。
• 示例：内容限制：1-12 年级的数据值必须是整数。
• 限制：数据值 13 是一个整数，可以通过内容约束验证。但是，这是不可接受的，因为 13 不是公认的学校年级。对于这种情况，还需要数据范围验证。

4. 数据一致性

• 目的：检查数据在其他相关数据的上下文中是否有意义。
• 示例：产品发货日期的数据值不能早于产品生产日期。
• 限制：数据可能一致，但仍然不正确或不准确。发货日期可能晚于生产日期，但仍然是错误的。

5. 数据结构

• 目的：检查数据是否遵循或符合集合结构。
• 示例：网页必须遵循规定的结构才能正确显示。
• 限制：数据结构可能是正确的，但数据仍然不正确或不准确。网页上的内容可以正确显示，但仍然包含错误信息。

6. 代码验证

• 目的：检查应用程序代码在用户数据输入期间系统地执行任何前面提到的验证。
• 示例：在代码验证期间发现的常见问题包括：允许的数据类型不止一种、未完成数据范围检查或文本字符串的结尾未明确定义。
• 限制：代码验证可能无法验证数据输入的所有可能变化。

4.5使用临时表

有关临时表的知识

• 当您结束 SQL 会话时，它们会自动从数据库中删除。

• 如果您正在进行一系列计算，它们可以用作存储值的保存区域。这有时被称为数据的预处理。

• 他们可以收集多个单独查询的结果。这有时称为数据分段。如果您需要对收集的数据执行查询或合并收集的数据，登台很有用。

• 他们可以存储经过过滤的数据库子集。您无需在每次使用数据时都选择和过滤数据。此外，使用更少的 SQL 命令有助于保持数据清洁。

创建临时表

使用SELECT和INTO ，您可以根据WHERE子句定义的条件创建临时表，以找到临时表所需的信息。此方法的一般语法如下：

此SELECT语句使用FROM和WHERE等标准子句，但INTO子句告诉数据库将请求的数据存储在名为“AfricaSales”的新临时表中。

用户管理的临时表创建

作为分析师，您可能决定为您的分析创建一个您可以自己管理的临时表。您将使用CREATE TABLE语句来创建这种临时表。完成对表的处理后，您将在会话结束时将其从数据库中删除或删除。CREATE TABLE语句为任何人提供了使用该临时表的权限。

完成临时表的处理后，可以使用DROP TABLE子句从数据库中删除该表。一般语法如下：

使用临时表时的最佳实践

• 全局临时表与本地临时表：
  全局临时表可供所有数据库用户使用，并在所有使用它们的连接都关闭时被删除。本地临时表仅对其查询或连接建立临时表的用户可用。您很可能会使用本地临时表。如果您创建了一个本地临时表并且是唯一使用它的人，您可以在使用完临时表后删除它。

• 使用后删除临时表：
  删除临时表与删除临时表略有不同。删除临时表不仅会删除表行中包含的信息，还会删除表变量定义（列）本身。删除临时表会删除表中的行，但保留表定义和列以供再次使用。尽管结束 SQL 会话后会删除本地临时表，但它可能不会立即发生。如果数据库中正在进行大量处理，则在使用临时表后删除它们是保持数据库平稳运行的好习惯。