Excel 数据分析 - 快速指南

---

数据分析 - 概述

数据分析是检查、清理、转换和建模数据的过程，目的是发现有用信息、提出结论和支持决策

。

数据分析的类型

存在多种数据分析技术，涵盖商业、科学、社会科学等各个领域，具有各种名称。主要的数据分析方法是 -

• 数据挖掘
• 商业智能
• 统计分析
• 预测分析
• 文本分析

数据挖掘

数据挖掘是对大量数据的分析，以提取以前未知的、有趣的数据模式、异常数据和依赖性。请注意，目标是从大量数据中提取模式和知识，而不是提取数据本身。

数据挖掘分析涉及人工智能、机器学习、统计学和数据库系统交叉的计算机科学方法。

从数据挖掘中获得的模式可以被视为输入数据的摘要，可用于进一步分析或通过决策支持系统获得更准确的预测结果。

商业智能

商业智能技术和工具用于获取和转换大量非结构化业务数据，以帮助识别、开发和创造新的战略商机。

商业智能的目标是轻松解释大量数据以发现新机会。它有助于实施基于洞察力的有效战略，为企业提供竞争性市场优势和长期稳定性。

统计分析

统计学是对数据的收集、分析、解释、呈现和组织的研究。

在数据分析中，使用两种主要的统计方法 -

• 描述性统计- 在描述性统计中，来自整个总体或样本的数据用数字描述符进行总结，例如 -

  • 连续数据的平均值、标准差

  • 分类数据的频率、百分比

• 推论统计- 它使用样本数据中的模式来推断所代表的总体或解释随机性。这些推论可以是 -

  • 回答有关数据的是/否问题（假设检验）

  • 估计数据的数值特征（估计）

  • 描述数据内的关联（相关性）

  • 数据内的建模关系（例如回归分析）

预测分析

预测分析使用统计模型来分析当前和历史数据，以预测未来或其他未知事件。在商业中，预测分析用于识别有助于决策的风险和机遇。

文本分析

文本分析，也称为文本挖掘或文本数据挖掘，是从文本中获取高质量信息的过程。文本挖掘通常涉及结构化输入文本、使用统计模式学习等手段在结构化数据中导出模式以及最终评估和解释输出的过程。

数据分析流程

统计学家 John Tukey 在 1961 年将数据分析定义为“分析数据的程序、解释此类程序结果的技术、规划数据收集以使其分析更容易、更精确或更准确的方法，以及所有的机制”以及适用于分析数据的（数学）统计结果。”

因此，数据分析是从各种来源获取大量非结构化数据并将其转换为有用信息的过程 -

• 回答问题
• 检验假设
• 决策
• 反驳理论

使用 Excel 进行数据分析

Microsoft Excel 提供了多种分析和解释数据的手段和方法。数据可以来自各种来源。数据可以通过多种方式进行转换和格式化。可以使用相关的 Excel 命令、函数和工具进行分析 - 包括条件格式、范围、表格、文本函数、日期函数、时间函数、财务函数、小计、快速分析、公式审核、查询工具、假设分析、求解器、数据模型、PowerPivot、PowerView、PowerMap 等

您将学习这些使用 Excel 进行数据分析的技术，分为两部分：

• 使用 Excel 进行数据分析
• 使用 Excel 进行高级数据分析

数据分析-流程

数据分析是收集、转换、清理和建模数据的过程，其目的是发现所需的信息。传达由此获得的结果，提出结论并支持决策。数据可视化有时用于描绘数据，以便于发现数据中的有用模式。术语“数据建模”和“数据分析”的含义相同。

数据分析过程由以下迭代阶段组成 -

• 数据需求规范
• 数据采集
• 数据处理
• 数据清理
• 数据分析
• 沟通

数据需求规范

分析所需的数据基于问题或实验。根据指导分析人员的要求，确定作为分析输入所需的数据（例如人口）。可以指定并获得关于人口的特定变量（例如，年龄和收入）。数据可以是数字的或分类的。

数据采集

数据收集是收集有关确定为数据要求的目标变量的信息的过程。重点是确保准确和诚实地收集数据。数据收集确保收集的数据准确，从而使相关决策有效。数据收集提供了衡量基准和改进目标。

数据是从各种来源收集的，从组织数据库到网页信息。由此获得的数据可能不是结构化的，并且可能包含不相关的信息。因此，需要对收集到的数据进行数据处理和数据清洗。

数据处理

必须对收集到的数据进行处理或组织以进行分析。这包括根据相关分析工具的要求构建数据。例如，数据可能必须放置在电子表格或统计应用程序内的表的行和列中。可能必须创建数据模型。

数据清理

处理和组织的数据可能不完整、包含重复项或包含错误。数据清理是预防和纠正这些错误的过程。数据清理有多种类型，具体取决于数据类型。例如，在清理财务数据时，可能会将某些总计与可靠的公布数字或定义的阈值进行比较。同样，定量数据方法可用于异常值检测，随后在分析中将其排除。

数据分析

经过处理、组织和清理的数据即可用于分析。可以使用各种数据分析技术来根据需求理解、解释并得出结论。数据可视化还可用于检查图形格式的数据，以获得有关数据内消息的额外见解。

统计数据模型（例如相关性、回归分析）可用于识别数据变量之间的关系。这些描述数据的模型有助于简化分析和传达结果。

该过程可能需要额外的数据清理或额外的数据收集，因此这些活动本质上是迭代的。

沟通

数据分析的结果将以用户要求的格式报告，以支持他们的决策和进一步的行动。用户的反馈可能会导致额外的分析。

数据分析师可以选择数据可视化技术，例如表格和图表，这有助于向用户清晰有效地传达信息。分析工具提供了通过颜色代码以及表格和图表中的格式来突出显示所需信息的工具。

Excel 数据分析 - 概述

Excel 提供的命令、函数和工具可让您轻松完成数据分析任务。您可以使用 Excel 避免许多耗时和/或复杂的计算。在本教程中，您将初步了解如何使用 Excel 执行数据分析。您将通过相关示例、Excel命令的逐步使用以及每一步的屏幕截图来了解。

范围和表格

您拥有的数据可以位于范围内或表中。无论数据是在范围内还是在表中，都可以对数据执行某些操作。

但是，当数据位于表中而不是范围中时，某些操作会更有效。还有一些专门针对表的操作。

您还将了解分析范围和表格中的数据的方法。您将了解如何命名范围、使用名称和管理名称。这同样适用于表中的名称。

数据清理——文本函数、日期和时间

在进行数据分析之前，您需要清理从各种来源获得的数据并对其进行结构化。您将了解如何清理数据。

• 具有文本功能
• 包含日期值
• 包含时间值

条件格式

Excel 提供了条件格式命令，允许您为单元格或字体着色，根据预定义的条件在单元格中的值旁边显示符号。这有助于人们形象化突出的价值观。您将了解用于条件格式化单元格的各种命令。

排序和过滤

在准备数据分析和/或显示某些重要数据期间，您可能必须对数据进行排序和/或过滤。您可以使用 Excel 中易于使用的排序和筛选选项执行相同的操作。

带范围的小计

如您所知，数据透视表通常用于汇总数据。然而，范围小计是 Excel 提供的另一个功能，它允许您通过简单的步骤对数据进行分组/取消分组并汇总范围中存在的数据。

快速分析

使用Excel中的快速分析工具，您可以快速执行各种数据分析任务并快速可视化结果。

了解查找函数

Excel 查找函数使您能够从大量数据中查找与定义条件匹配的数据值。

数据透视表

使用数据透视表，您可以汇总数据，通过更改数据透视表的内容动态准备报告。

数据可视化

您将学习多种使用 Excel 图表的数据可视化技术。您还将学习如何创建带状图、温度计图、甘特图、瀑布图、迷你图和数据透视图。

数据验证

可能需要仅在某些单元格中输入有效值。否则，它们可能会导致错误的计算。使用数据验证命令，您可以轻松地为单元格设置数据验证值、提示用户预期在单元格中输入的内容的输入消息、验证使用定义的条件输入的值并在出现以下情况时显示错误消息错误的条目。

财务分析

Excel 为您提供了多种财务功能。但是，对于需要财务分析的常见问题，您可以学习如何组合使用这些函数。

使用多个工作表

您可能必须在多个工作表中执行多项相同的计算。您可以在一个工作表中进行计算，并将其显示在其他选定的工作表中，而不是在每个工作表中重复这些计算。您还可以将各个工作表中的数据汇总到报告工作表中。

公式审核

使用公式时，您可能想要检查公式是否按预期工作。在 Excel 中，公式审核命令可帮助您跟踪先例值和相关值以及错误检查。

查询

Excel 还提供了查询加载项，使您能够比较两个工作簿以识别更改、创建交互式报告以及查看工作簿、工作表和单元格之间的关系。您还可以清除工作表中过多的格式，这些格式会导致 Excel 速度变慢或文件大小变大。

使用范围名称

在进行数据分析时，如果通过名称而不是单元格引用（单个单元格或单元格范围）来引用各种数据，则会更有意义且更容易。例如，如果您根据贴现率和一系列现金流计算净现值，则公式

净现值 = NPV（折扣率、现金流）

比更有意义

C10 = NPV (C2, C6:C8)

使用 Excel，您可以为数据的各个部分创建和使用有意义的名称。使用范围名称的优点包括 -

• 有意义的范围名称（例如 Cash_Flows）比范围地址（例如 C6:C8）更容易记住。

• 输入名称比输入单元格或范围地址更不容易出错。

• 如果在公式中输入错误的名称，Excel 将显示 #NAME ? 错误。

• 您可以使用定义的名称快速移动到工作表的区域。

• 有了名称，您的公式将更容易理解且更易于使用。例如，公式 Net_Income = Gross_Income – Deductions 比 C40 = C20 – B18 更直观。

• 使用范围名称创建公式比使用单元格或范围地址更容易。您可以使用公式自动完成将单元格或区域名称复制到公式中。

在本章中，您将学习 -

• 名称的语法规则。
• 为单元格引用创建名称。
• 为常量创建名称。
• 管理名称。
• 您定义的名称的范围。
• 编辑姓名。
• 过滤名称。
• 删除名字。
• 应用名称。
• 在公式中使用名称。
• 查看工作簿中的名称。
• 使用粘贴名称和粘贴列表。
• 使用范围交点的名称。
• 复制带有名称的公式。

使用公式自动完成复制名称

在公式中键入名称的第一个字母。将出现一个下拉框，其中包含函数名称和范围名称。选择所需的名称。它被复制到您的公式中。

范围名称语法规则

Excel 的名称语法规则如下 -

• 您可以使用字母、数字和符号（下划线、反斜杠和句点）的任意组合。不允许使用其他符号。

• 名称可以以字符、下划线或反斜杠开头。

• 名称不能以数字开头（例如 - 1stQuarter）或类似于单元格地址（例如 - QTR1）。

• 如果您喜欢使用此类名称，请在名称前添加下划线或反斜杠（例如 - \1stQuarter、_QTR1）。

• 名称不能包含空格。如果您想区分名称中的两个单词，可以使用下划线（示例 - Cash_Flows 而不是 Cash Flows）

• 您定义的名称不应与 Excel 内部定义的名称冲突，例如Print_Area、Print_Titles、Consolidate_Area 和 Sheet_Title。如果您定义相同的名称，它们将覆盖 Excel 的内部名称，并且您不会收到任何错误消息。但是，建议不要这样做。

• 尽管最多可以使用 255 个字符，但名称应简短但易于理解

创建范围名称

您可以通过两种方式创建范围名称 -

• 使用名称框。

• 使用“新名称”对话框。

• 使用选择对话框。

使用名称框创建范围名称

要创建范围名称，使用编辑栏左侧的名称框是最快的方法。请按照以下步骤操作 -

步骤 1 - 选择要定义名称的范围。

步骤 2 - 单击名称框。

步骤 3 - 输入名称并按 Enter 键创建名称。

使用“新名称”对话框创建范围名称

您还可以使用“公式”选项卡中的“新名称”对话框创建范围名称。

步骤 1 - 选择要为其定义名称的范围。

步骤 2 - 单击“公式”选项卡。

步骤 3 - 单击定义名称组中的定义名称。将出现“新名称”对话框。

步骤 4 - 在名称旁边的框中输入名称

步骤 5 - 检查“引用”框中选择并显示的范围是否正确。单击“确定”。

使用“从选择中创建名称”对话框创建范围名称

当您的文本值与范围相邻时，您还可以使用“公式”选项卡的“选择”对话框中的“创建名称”来创建范围名称。

步骤 1 - 选择要定义名称的范围以及包含该名称的行/列。

步骤 2 - 单击“公式”选项卡。

步骤 3 - 单击定义名称组中的选择创建。将出现“根据选择创建名称”对话框。

步骤 4 - 选择顶行，因为文本出现在所选内容的顶行中。

步骤 5 - 检查选择并显示在“引用”旁边的框中的范围是否正确。单击“确定”。

现在，您可以使用= Sum （学生姓名）找到范围内的最大值，如下所示 -

您也可以通过多项选择来创建名称。在下面给出的示例中，您可以使用学生的姓名来命名每个学生的分数行。

现在，您可以通过 = Sum （学生姓名）找到每个学生的总分，如下所示。

为常量创建名称

假设您有一个将在整个工作簿中使用的常量。您可以直接为其指定名称，而无需将其放置在单元格中。

在下面的示例中，储蓄银行利率设置为 5%。

• 单击定义名称。
• 在“新名称”对话框的“名称”框中键入 Savings_Bank_Interest_Rate。
• 在范围中，选择工作簿。
• 在引用框中，清除内容并键入 5%。
• 单击“确定”。

名称Savings_Bank_Interest_Rate设置为常量 5%。您可以在名称管理器中验证这一点。您可以看到该值设置为 0.05，并且在“引用=0.05”中放置。

管理名称

Excel 工作簿可以具有任意数量的命名单元格和区域。您可以使用名称管理器管理这些名称。

• 单击公式选项卡。

• 单击已定义名称组中的名称管理器。将出现名称管理器对话框。显示当前工作簿中定义的所有名称。

显示名称列表，其中包含定义的值、单元格引用（包括工作表名称）、范围和注释。

名称管理器可以选择 -

• 使用“新建”按钮定义新名称。

• 编辑定义的名称。

• 删除定义的名称。

• 按类别过滤定义的名称。

• 修改它引用的已定义名称的范围。

名称的范围

默认情况下名称的范围是工作簿。您可以从名称管理器中范围列下的名称列表中找到已定义名称的范围。

使用“新名称”对话框定义名称时，可以定义新名称的范围。例如，您正在定义名称 Interest_Rate。然后您可以看到新名称Interest_Rate的范围是Workbook。

假设您希望将此利率的范围仅限于此工作表。

步骤 1 - 单击范围框中的向下箭头。可用的范围选项显示在下拉列表中。

范围选项包括Workbook以及工作簿中的工作表名称。

步骤 2 - 单击当前工作表名称，在本例中为 NPV，然后单击“确定”。您可以在工作表选项卡中定义/查找工作表名称。

步骤 3 - 要验证范围是否为工作表，请单击名称管理器。在“范围”列中，您将找到 Interest_Rate 的 NPV。这意味着您只能在工作表 NPV 中使用名称 Interest_Rate，而不能在其他工作表中使用。

注意- 一旦定义了名称的范围，以后就无法修改。

删除带有错误值的名称

有时，由于各种原因，名称定义可能会出现错误。您可以按如下方式删除此类名称 -

步骤 1 - 单击“名称管理器”对话框中的“过滤器” 。

出现以下过滤选项 -

• 清除过滤器
• 工作表范围内的名称
• 工作簿范围内的名称
• 有错误的名字
• 没有错误的名称
• 定义名称
• 表名

您可以通过选择这些选项中的一个或多个来将过滤器应用到定义的名称。

步骤 2 - 选择有错误的名称。将显示包含错误值的名称。

步骤 3 - 从获得的名称列表中，选择要删除的名称，然后单击“删除”。

您将收到一条消息，确认删除。单击“确定”。

编辑姓名

您可以使用“名称管理器”对话框中的“编辑”选项来 -

• 更改名称。

• 修改引用范围

• 编辑名称中的注释。

更改名称

步骤 1 - 单击包含函数Large的单元格。

您可以看到，数组中添加了另外两个值，但未包含在函数中，因为它们不是 Array1 的一部分。

步骤 2 -在“名称管理器”对话框中单击要编辑的名称。在本例中，为 Array1。

步骤 3 - 单击编辑。将出现“编辑名称”对话框。

步骤 4 -通过在名称框中键入所需的新名称来更改名称。

步骤 5 - 单击引用框右侧的范围按钮并包含新的单元格引用。

第 6 步- 添加评论（可选）

请注意，范围处于非活动状态，因此无法更改。

单击“确定”。您将观察所做的更改。

应用名称

考虑以下示例 -

正如您所观察到的，PMT 函数中未定义和使用名称。如果将此函数放置在工作表中的其他位置，您还需要记住参数值的确切位置。您知道使用名称是更好的选择。

在这种情况下，该函数已使用没有名称的单元格引用定义。您仍然可以定义名称并应用它们。

步骤 1 - 使用从选择创建，定义名称。

步骤 2 - 选择包含公式的单元格。单击“公式”选项卡上“定义名称”组中“定义名称”旁边的。从下拉列表中，单击“应用名称”。

步骤 3 -将出现“应用名称”对话框。选择要应用的名称，然后单击“确定”。

选定的名称将应用于选定的单元格。

您还可以通过选择工作表并重复上述步骤，将名称应用到整个工作表。

在公式中使用名称

您可以通过以下方式在公式中使用名称-

• 如果您还记得名称，请输入名称，或者

• 键入前一个或两个字母并使用Excel 公式自动完成功能。

• 单击“公式”选项卡上“定义名称”组中的“在公式中使用”。

  • 从已定义名称的下拉列表中选择所需的名称。

  • 双击该名称。

• 使用“粘贴名称”对话框。

  • 从定义名称的下拉列表中选择粘贴名称选项。将出现“粘贴名称”对话框。

  • 在粘贴名称对话框中选择名称并双击它。

查看工作簿中的名称

您可以获取工作簿中的所有名称及其参考并保存或打印它们。

• 单击要复制工作簿中名称的空单元格。

• 单击“定义的名称”组中的“在公式中使用”。

• 单击下拉列表中的粘贴名称。

• 在出现的“粘贴名称”对话框中单击“粘贴列表” 。

名称列表及其相应的引用被复制到工作表上的指定位置，如下面的屏幕截图所示 -

使用范围交点的名称

范围交集是那些具有两个共同范围的单个单元格。

例如，在下面给出的数据中，范围 B6:F6 和范围 C3:C8 具有共同的单元格 C6，它实际上代表学生 Kodeda Adam 在考试 1 中的分数。

您可以使用范围名称使其更有意义。

• 使用“根据选择创建”为学生和考试创建名称。

• 您的名字将如下所示 -

• 类型 = B11 中的Kodeda_Adam Exam_1。

在这里，您正在使用范围交集操作，即两个范围之间的空间。

这将显示考试 1 中 Kodeda、Adam 的分数，这些分数在单元格 C6 中给出。

复制带有名称的公式

您可以通过在同一工作表中复制和粘贴来复制带有名称的公式。

您还可以通过复制和粘贴将带有名称的公式复制到不同的工作表，前提是公式中的所有名称都将工作簿作为范围。否则，您将收到#VALUE错误。

Excel 数据分析 - 表格

表是结构化数据的矩形范围。主要特点是 -

• 表中的每一行对应于一条数据记录。示例 - 员工信息。

• 每列包含一条特定的信息。示例 - 列可以包含姓名、员工编号、雇用日期、工资、部门等数据。

• 最上面一行描述了每列中包含的信息，称为标题行。

• 顶行中的每个条目称为列标题。

您可以创建并使用 Excel 表格来轻松管理和分析数据。此外，借助 Excel 表格，您可以获得内置的过滤、排序和行着色功能，从而简化您的报告活动。

此外，Excel 还会智能地响应在表格上执行的操作。例如，您在一列中有一个公式，或者您已经根据表中的数据创建了一个图表。当您向表中添加更多数据（即更多行）时，Excel 会将公式扩展到新数据，并且图表会自动展开。

表和范围之间的差异

以下是表格和范围之间的区别 -

• 表是一种比范围更结构化的数据处理方式。
• 您可以将范围转换为表格，Excel 会自动提供 -
  • 表名
  • 列标题名称
  • 设置数据格式（单元格颜色和字体颜色）以获得更好的可视化效果

表格提供了范围所不具备的附加功能。这些是 -

• Excel 在功能区中提供了从属性到样式的表格工具。

• Excel 自动在每个列标题中提供一个“筛选”按钮，用于对数据进行排序或筛选表，以便仅显示满足您定义的条件的行。

• 如果表中有多行，并且向下滚动工作表以使标题行消失，则工作表中的列字母将替换为表标题。

• 当您将公式放入表格列中的任何单元格时，它会传播到该列中的所有单元格。

• 您可以在公式中使用表名称和列标题名称，而无需使用单元格引用或创建范围名称。

• 您可以通过单击并拖动右下单元格右下角的小三角形控件来添加更多行或更多列来扩展表格大小。

• 您可以为表创建和使用切片器来过滤数据。

您将在本章中了解所有这些功能。

创建表

要根据工作表上的数据创建表格，请按照给定的步骤操作 -

步骤 1 - 选择要包含在表中的单元格范围。单元格可以包含数据，也可以为空。以下 Range 有 290 行员工数据。数据的顶行有标题。

步骤 2 - 在“插入”选项卡下的“表格”组中，单击“表格”。将出现“创建表”对话框。检查表的数据在哪里？中选择的数据范围是否正确。盒子是正确的。

步骤 3 -如果所选范围的顶行包含要用作表标题的数据，请选中我的表有标题框。

注意- 如果您不选中此框，您的表格将有标题 - Column1、Column2，...

步骤 4 - 单击“确定”。

范围将转换为具有默认样式的表格。

步骤 5 - 您还可以通过单击范围内的任意位置并按 Ctrl+T 将范围转换为表格。将出现“创建表”对话框，然后您可以重复上面给出的步骤。

表名

Excel 为创建的每个表分配一个名称。

步骤 1 - 要查看刚刚创建的表的名称，请单击表，然后单击功能区上的表工具 - 设计选项卡。

步骤 2 - 在“属性”组中的“表名称”框中，将显示您的表名称。

步骤 3 - 您可以编辑此表名称以使其对您的数据更有意义。

步骤 4 - 单击表名称框。清除名称并输入 Emp_Data。

注意- 范围名称的语法规则适用于表名称。

管理表中的名称

您可以像使用名称管理器管理范围名称一样管理表名称。

• 单击表格。

• 单击“公式”选项卡上“已定义名称”组中的“名称管理器”。

将出现“名称管理器”对话框，您可以在工作簿中找到表名称。

您可以编辑表名称或使用“名称管理器”对话框中的“新建”选项添加注释。但是，您无法更改“引用”中的范围。

您可以创建带有列标题的名称，以便在公式、图表等中使用它们。

• 单击表中的列标题EmployeeID 。

• 单击名称管理器。

• 在“名称管理器”对话框中单击“新建” 。

将出现“新名称”对话框。

在“名称”框中，您可以找到列标题，在“引用”框中，您将找到Emp_Data[[#Headers],[EmployeeID]]。

正如您所观察到的，这是在表中定义名称的快速方法。

表格标题替换列字母

当您在表中处理更多行数据时，您可能需要向下滚动才能查看这些行中的数据。

但是，在执行此操作时，您还需要表标题来标识哪个值属于哪个列。Excel 自动提供了一种平滑的方法来执行此操作。当您向下滚动数据时，工作表本身的列字母将转换为表标题。

在下面给出的工作表中，列字母按原样显示，表标题位于第 2 行。可见 290 行数据中的 21 行。

向下滚动可查看表格第 25 – 35 行。表格标题将替换表格列的列字母。其他列字母保持原样。

表中公式的传播

在下表中，假设您要包含每位员工的年龄。

步骤 1 - 在出生日期列的右侧插入一列。在列标题中键入年龄。

步骤 2 - 在该空列的任何单元格中，键入公式 = DAYS ([@BirthDate], TODAY ())并按 Enter。

该公式会自动传播到表格该列中的其他单元格。

调整表格大小

您可以调整表格大小以添加或删除行/列。

请考虑下表 Student_Marks，其中包含批次 1 - 15 的总分。

假设您要添加另外三个批次 16 – 18 以及包含通过百分比的列。

• 单击表格。

• 向下拖动右下角的蓝色控件以在表中包含另外三行。

• 再次向侧面拖动右下角的蓝色控件，以在表中再包含一列。

您的表格如下所示。您还可以在“名称管理器”对话框中检查表中包含的范围 -

删除重复项

当您从不同来源收集数据时，可能会出现重复的值。在进一步分析之前，您需要删除重复值。

查看以下数据，其中包含有关各个品牌的各种产品的信息。假设您想从此数据中删除重复项。

• 单击表格。

• 在“设计”选项卡上，单击功能区“工具”组中的“删除重复项” 。将出现“删除重复项”对话框。

列标题出现在“删除重复项”对话框中的列下方。

• 根据您要删除重复项的列检查列标题，然后单击“确定”。

您将收到一条消息，显示有多少具有重复值的行被删除以及保留了多少唯一值。清理后的数据将显示在表格中。

您还可以使用功能区“数据”选项卡下“数据工具”组中的“删除重复项”来删除重复项。

转换为范围

您可以将表转换为Range。

• 单击表格。

• 单击功能区“设计”选项卡下“工具”组中的“转换为范围” 。

您将收到一条消息，询问您是否要将表转换为范围。单击“是”确认后，表格将转换为“范围”。

表格样式选项

您有多种表格样式可供选择。如果您需要突出显示行/列，可以使用这些选项。

您可以选中/取消选中这些框来查看表格的外观。最后，您可以决定哪些选项适合您的数据。

建议仅使用表格样式选项来投影数据中的重要信息，而不是使其变得丰富多彩，这在数据分析中是不需要的。

表格样式

您有多种表格样式可供选择。可以根据您想要在表中显示数据的颜色和图案来使用这些样式。

将鼠标移到这些样式上即可预览带有样式的表格。最后，您可以决定哪种样式适合您的数据。

建议表格样式仅用于以可呈现的方式投影数据中的重要信息，而不是使其变得丰富多彩，这在数据分析中是不需要的。

桌子切片机

如果您使用的是 Excel 2013 或 Excel 2016，则可以使用切片器来过滤表中的数据。

有关如何使用表切片器的详细信息，请参阅本教程中的“过滤”章节。

使用文本函数清理数据

您从不同来源获得的数据很多都不是可供分析的形式。在本章中，您将了解如何准备文本形式的数据以进行分析。

最初，您需要清理数据。数据清理包括从文本中删除不需要的字符。接下来，您需要以进一步分析所需的形式构建数据。您可以通过以下方式执行相同操作 -

• 使用文本函数查找所需的文本模式。
• 从文本中提取数据值。
• 使用文本函数格式化数据。
• 使用文本函数执行数据操作。

从文本中删除不需要的字符

当您从其他应用程序导入数据时，它可能包含不可打印的字符和/或多余的空格。多余的空间可以是 -

• 前导空格，和/或
• 单词之间的额外空格。

如果您对此类数据进行排序或分析，您将得到错误的结果。

考虑以下示例 -

这是您获得的产品信息的原始数据，包含产品 ID、产品描述和价格。字符“|” 分隔每行中的字段。

当您将此数据导入 Excel 工作表时，它看起来如下 -

正如您所观察到的，整个数据都在一个列中。您需要构建此数据以执行数据分析。但是，最初您需要清理数据。

您需要删除数据中可能存在的所有不可打印字符和多余空格。为此，您可以使用 CLEAN 函数和 TRIM 函数。

• 选择单元格 C3 – C11。
• 键入 =TRIM (CLEAN (B3))，然后按 CTRL + Enter。

公式填写在单元格 C3 – C11 中。

结果如下所示 -

使用文本函数查找所需的文本模式

要构建数据，您可能必须进行某些文本模式匹配，基于此您可以提取数据值。为此目的有用的一些文本函数是 -

从文本中提取数据值

您需要从文本中提取所需的数据才能构建相同的数据。在上面的示例中，您需要将数据放入三列中 - ProductID、Product_Description 和 Price。

您可以通过以下方式之一提取数据 -

• 使用将文本转换为列向导提取数据值
• 使用文本函数提取数据值
• 使用快速填充提取数据值

使用将文本转换为列向导提取数据值

如果您的字段是：

• 由字符分隔，或
• 按列对齐，每个字段之间留有空格。

在上面的示例中，字段由字符“|”分隔。因此，您可以使用“将文本转换为列”向导。

• 选择数据。

• 将值复制并粘贴到同一位置。否则，“将文本转换为列”将函数而不是数据本身作为输入。

• 选择数据。

• 单击功能区“数据”选项卡下“数据工具”组中的“文本到列” 。

步骤 1 - 将文本转换为列向导 - 将出现第 1 步（共 3 步）。

• 选择分隔符。
• 点击下一步。

步骤 2 - 将文本转换为分栏向导 - 将出现第 2 步（共 3 步）。

• 在“分隔符”下，选择“其他”。

• 在“其他”旁边的框中，键入字符|

• 单击“下一步”。

步骤 3 - 将文本转换为分栏向导 - 将出现第 3 步（共 3 步）。

在此屏幕中，您可以在向导中选择数据的每一列并设置该列的格式。

• 对于“目标”，选择单元格 D3。

• 您可以单击“高级” ，在弹出的“高级文本导入设置”对话框中设置小数分隔符和千位分隔符。

• 单击“完成”。

您的数据会转换为列，显示在三列中 - D、E 和 F。

• 将列标题命名为 ProductID、Product_Description 和 Price。

使用文本函数提取数据值

假设数据中的字段既不由字符分隔，也不在每个字段之间有空格的列中对齐，则可以使用文本函数来提取数据值。即使在字段被分隔的情况下，您仍然可以使用文本函数来提取数据。

为此目的有用的一些文本函数是 -

您还可以根据手头的数据组合两个或多个这些文本函数，以提取所需的数据值。例如，使用LEFT、RIGHT和VALUE函数的组合或者使用FIND、LEFT、LEN和MID函数的组合。

在上面的例子中，

• 剩下第一个 | 的所有字符 指定名称 ProductID。

• 从第二个开始的所有字符 | 给出名称“价格”。

• 第一个 | 之间的所有字符 第二个| 命名为 Product_Description。

• 每个 | 前后各有一个空格。

观察此信息，您可以通过以下步骤提取数据值 -

• 找到第一个 | 的位置 -第一| 位置

  • 您可以使用FIND功能

• 找到第二个 | 的位置 -第二 | 位置

  • 您可以再次使用FIND功能

• 开始到（第一个 | 位置- 2）文本字符给出 ProductID

  • 您可以使用左功能

• ( First | Position + 2) 到 ( Second | Position - 2) 文本字符给出 Product_Description

  • 您可以使用 MID 功能

• (第二个 | 位置+ 2) 到文本的结束字符给出价格

  • 您可以使用 RIGHT 功能

结果如下所示 -

您可以观察到价格列中的值是文本值。要对这些值执行计算，您必须设置相应单元格的格式。您可以查看下面给出的部分来了解格式化文本。

使用快速填充提取数据值

使用 Excel Flash Fill是从文本中提取数据值的另一种方法。但是，只有当 Excel 能够在数据中找到模式时，此方法才有效。

步骤 1 - 在数据旁边为 ProductID、Product_Description 和 Price 创建三列。

步骤 2 - 从 B3 复制并粘贴 C3、D3 和 E3 的值。

步骤 3 - 选择单元格 C3，然后单击“数据”选项卡上“数据工具”组中的“快速填充”。ProductID 的所有值均已填充。

步骤 4 - 对 Product_Description 和 Price 重复上述步骤。数据已填满。

使用文本函数格式化数据

Excel 具有多个内置文本函数，可用于格式化包含文本的数据。这些包括 -

根据您的需要格式化文本的函数-

将数字转换和/或格式化为文本的函数-

将文本转换为数字的函数-

使用文本函数执行数据操作

您可能必须对数据执行某些文本操作。例如，如果组织中员工的登录 ID 更改为新格式，则可能需要根据格式更改进行文本替换。

以下文本函数可帮助您对包含文本的数据执行文本操作 -

清理包含日期值的数据

您从不同来源获取的数据可能包含日期值。在本章中，您将了解如何准备包含用于分析的数据值的数据。

您将了解 -

• 日期格式
  • 串行格式的日期
  • 不同月-日-年格式的日期
• 将串行格式的日期转换为月-日-年格式
• 将月-日-年格式的日期转换为序列格式
• 获取今天的日期
• 查找指定天数后的工作日
• 定制周末的定义
• 两个给定日期之间的工作日数
• 从日期中提取年、月、日
• 从日期中提取星期几
• 从年、月、日获取日期
• 计算两个日期之间的年数、月数和天数

日期格式

Excel以两种方式支持日期值 -

• 串行格式
• 不同的年月日格式

您可以转换 -

• 序列格式的日期到年月日格式的日期

• 年月日格式的日期到串行格式的日期

串行格式的日期

序列格式的日期是一个正整数，表示给定日期和 1900 年 1 月1日之间的天数。当前日期和1900 年 1 月 1 日都包含在计数中。例如，42354 是代表 12/16/2015 的日期。

月-日-年格式的日期

Excel根据您选择的区域设置（位置）支持不同的日期格式。因此，您需要首先确定日期格式和当前数据分析的兼容性。请注意，某些日期格式带有 *（星号）前缀 -

• 以 *（星号）开头的日期格式响应为操作系统指定的区域日期和时间设置的更改

• 不带 *（星号）的日期格式不受操作系统设置的影响

为了便于理解，您可以假设美国为区域设置。您可以找到以下日期格式来选择日期- 2016 年 6 月 8^日-

• *2016年6月8日（受操作系统设置影响）
• *2016 年 6 月 8 日星期三（受操作系统设置影响）
• 6/8
• 2016年6月8日
• 2016年6月8日
• 6月8日
• 2016 年 6 月 8 日
• 2016 年 6 月 8 日
• 16年6月
• 6 月 16 日
• J
• 歼16
• 2016年6月8日
• 2016 年 6 月 8 日

如果您仅输入两位数字来代表年份并且如果 -

• 数字为 30 或更高，Excel 假定这些数字代表二十世纪的年份。

• 数字低于 30，Excel 假定这些数字代表二十一世纪的年份。

例如，1/1/29 被视为 2029 年 1 月 1 日，1/1/30 被视为 1930 年 1 月 1 日。

将串行格式的日期转换为月-日-年格式

要将日期从串行格式转换为月-日-年格式，请按照以下步骤操作 -

• 单击“设置单元格格式”对话框中的“数字”选项卡。

• 单击“类别”下的“日期”。

• 选择区域设置。可用的日期格式将显示为类型下的列表。

• 单击“类型”下的“格式”以查看“示例”旁边的框中的预览。

选择格式后，单击“确定”。

将月-日-年格式的日期转换为序列格式

您可以通过两种方式将月-日-年格式的日期转换为序列格式 -

• 使用“设置单元格格式”对话框

• 使用 Excel DATEVALUE函数

使用“设置单元格格式”对话框

• 单击“设置单元格格式”对话框中的“数字”选项卡。

• 单击类别下的常规。

使用 Excel DATEVALUE 函数

您可以使用 Excel DATEVALUE函数将日期转换为序列号格式。您需要将日期参数括在“”中。例如，

=DATEVALUE ("6/8/2016") 结果为 42529

获取今天的日期

如果需要根据今天的日期进行计算，只需使用Excel函数TODAY()即可。结果反映了使用日期。

^{以下 TODAY () 函数使用截图于 2016 年 5月}16 日拍摄-

查找指定天数之后的工作日

您可能需要执行某些计算