Excel 数据分析 - 数据验证

---

---

数据验证是 Excel 中一个非常有用且易于使用的工具，您可以使用它来设置对输入工作表中的数据的验证。

对于工作表上的任何单元格，您可以

• 显示输入消息，说明需要输入的内容。
• 限制输入的值。
• 提供一个值列表供选择。
• 显示错误消息并拒绝无效数据输入。

考虑以下风险跟踪器，可用于输入和跟踪已识别的风险信息。

在此跟踪器中，输入以下列中的数据会根据预设的数据约束进行验证，只有当输入数据满足验证条件时才会被接受。否则，您将收到错误消息。

• 概率
• 影响
• 风险类别
• 风险来源
• 状态

“风险暴露”列将包含计算值，您无法输入任何数据。即使删除一行，“序号”列也会设置计算值进行调整。

现在，您将学习如何设置这样的工作表。

准备工作表结构

准备工作表结构：

• 从空白工作表开始。
• 将标题放在第 2 行。
• 将列标题放在第 3 行。
• 对于“概率”、“影响”和“风险暴露”列标题：
  • 右键单击单元格。
  • 从下拉菜单中单击“设置单元格格式”。
  • 在“设置单元格格式”对话框中，单击“对齐”选项卡。
  • 在“方向”下输入 90。
• 合并并居中第 3、4 和 5 行中每个列标题的单元格。
• 设置第 2-5 行单元格的边框。
• 调整行和列的宽度。

您的工作表将如下所示：

设置风险类别的有效值

在单元格 M5-M13 中输入以下值（M5 是标题，M6-M13 是值）

• 单击“风险类别”列下的第一个单元格 (H6)。
• 单击功能区上的“数据”选项卡。
• 单击“数据工具”组中的“数据验证”。
• 从下拉列表中选择“数据验证…”。

将出现“数据验证”对话框。

• 单击“设置”选项卡。
• 在“验证条件”下，“允许：”下拉列表中，选择“列表”选项。

• 在出现的“来源：”框中选择区域 M6:M13。
• 选中出现的“忽略空白”和“单元格下拉列表”复选框。

设置风险类别的输入消息

• 在“数据验证”对话框中单击“输入消息”选项卡。
• 选中“选中单元格时显示输入消息”复选框。
• 在“标题：”框中，键入“风险类别”。
• 在“输入消息：”框中，键入“从列表中选择风险类别”。

设置风险类别的错误警告

设置错误警告：

• 在“数据验证”对话框中单击“错误警告”选项卡。
• 选中“输入无效数据后显示错误警告”复选框。
• 在“样式：”下拉菜单中选择“停止”。
• 在“标题：”框中，键入“无效输入”。
• 在“错误消息：”框中，键入“从下拉列表中选择一个值”。
• 单击“确定”。

验证风险类别的数 据验证

对于“风险类别”下选定的第一个单元格，

• 已设置数据验证条件
• 已设置输入消息
• 已设置错误警告

现在，您可以验证您的设置。

单击您已设置数据验证条件的单元格。将出现输入消息。单元格右侧将出现下拉按钮。

输入消息正确显示。

• 单击单元格右侧的下拉按钮。将出现包含可选择值的下拉列表。

• 将下拉列表中的值与用于创建下拉列表的值进行交叉检查。

两组值匹配。请注意，如果值的数量较多，则下拉列表的右侧将出现滚动条。

从下拉列表中选择一个值。它将出现在单元格中。

您可以看到有效值的选中功能正常。

最后，尝试输入无效条目并验证错误警告。

在单元格中键入“人员”并按 Enter 键。将显示您为单元格设置的错误消息。

• 验证错误消息。
• 您可以选择“重试”或“取消”。验证这两个选项。

您已成功设置单元格的数据验证。

注意 - 检查消息的拼写和语法非常重要。

为“风险类别”列设置有效条件

现在，您可以将数据验证条件应用于“风险类别”列中的所有单元格。

此时，您需要记住两件事：

• 您需要为可能使用的最大单元格数量设置条件。在我们的示例中，根据工作表的使用位置，它可以从 10 到 100 不等。

• 您不应为不需要的单元格范围或整列设置条件。这会不必要地增加文件大小。这被称为过度格式化。如果您从外部来源获取工作表，则必须删除多余的格式，您将在本教程中关于查询的章节中学习。

按照以下步骤操作：

• 为“风险类别”下方的 10 个单元格设置验证条件。
• 您可以通过单击第一个单元格的右下角轻松完成此操作。
• 按住出现的“+”符号并将其向下拖动。

已为所有选定单元格设置数据验证。

单击选定的最后一列并进行验证。

“风险类别”列的数据验证已完成。

设置风险来源的验证值

在这种情况下，我们只有两个值：内部和外部。

• 单击“风险来源”列下的第一个单元格 (I6)。
• 单击功能区上的“数据”选项卡。
• 单击“数据工具”组中的“数据验证”。
• 从下拉列表中选择“数据验证…”。

将出现“数据验证”对话框。

• 单击“设置”选项卡。
• 在“验证条件”下，“允许：”下拉列表中，选择“列表”选项。
• 在出现的“来源：”框中键入“内部，外部”。
• 选中出现的“忽略空白”和“单元格下拉列表”复选框。

设置风险来源的输入消息。

设置风险来源的错误警告。

对于“风险来源”下选定的第一个单元格：

• 已设置数据验证条件
• 已设置输入消息
• 已设置错误警告

现在，您可以验证您的设置。

单击您已设置数据验证条件的单元格。将出现输入消息。单元格右侧将出现下拉按钮。

输入消息正确显示。

• 单击单元格右侧的下拉箭头按钮。将出现包含可选择值的下拉列表。

• 检查值是否与您键入的值相同 - 内部和外部。

两组值匹配。从下拉列表中选择一个值。它将出现在单元格中。

您可以看到有效值的选中功能正常。最后，尝试输入无效条目并验证错误警告。

在单元格中键入“财务”并按 Enter 键。将显示您为单元格设置的错误消息。

• 验证错误消息。您已成功设置单元格的数据验证。

• 为“风险来源”列设置有效条件

• 将数据验证条件应用于“风险来源”列中的单元格 I6-I15（即与“风险类别”列相同的范围）。

已为所有选定单元格设置数据验证。“风险来源”列的数据验证已完成。

设置状态的验证值

• 重复您用于设置风险来源验证值的步骤。

• 将列表值设置为“打开”、“关闭”。

• 将数据验证条件应用于“状态”列中的单元格 K6-K15（即与“风险类别”列相同的范围）。

已为所有选定单元格设置数据验证。“状态”列的数据验证已完成。

设置概率的验证值

风险概率分数的值范围为 1-5，1 表示低，5 表示高。该值可以是 1 到 5 之间的任何整数，包含 1 和 5。

• 单击“风险来源”(I6)列下的第一个单元格。
• 单击功能区上的“数据”选项卡。
• 单击“数据工具”组中的“数据验证”。
• 从下拉列表中选择“数据验证…”。

将出现“数据验证”对话框。

• 单击“设置”选项卡。
• 在“数据验证”下的“允许：”下拉列表中，选择“整数”。

• 在“数据”下选择“之间”。
• 在“最小值”下的框中键入 1。
• 在“最大值”下的框中键入 5。

设置概率的输入信息。

设置概率的错误警告，然后单击“确定”。

对于“概率”下选择的第一个单元格，

• 数据验证条件已设置。
• 输入信息已设置。
• 错误警告已设置。

现在，您可以验证您的设置。

单击您已设置数据验证条件的单元格。将显示输入信息。在本例中，不会出现下拉按钮，因为输入值设置为范围而不是列表。

输入消息正确显示。

在单元格中输入 1 到 5 之间的整数。它将显示在单元格中。

有效值的选取工作正常。最后，尝试输入无效条目并验证错误警告。

在单元格中键入 6 并按 Enter 键。将显示您为单元格设置的错误消息。

您已成功设置单元格的数据验证。

• 为“概率”列设置有效条件。

• 将数据验证条件应用于“概率”列中的单元格 E6-E15（即与“风险类别”列相同的范围）。

已为所有选定的单元格设置数据验证。“概率”列的数据验证已完成。

设置影响力的验证值

要设置影响力的验证值，请重复用于设置概率验证值的步骤。

将数据验证条件应用于“影响”列中的单元格 F6-F15（即与“风险类别”列相同的范围）。

已为所有选定的单元格设置数据验证。“影响”列的数据验证已完成。

设置包含计算值的“风险暴露”列

风险暴露计算为风险概率和风险影响的乘积。

风险暴露 = 概率 * 影响

在单元格 G6 中键入 =E6*F6 并按 Enter 键。

由于 E6 和 F6 为空，单元格 G6 中将显示 0。

将公式复制到单元格 G6-G15。单元格 G6-G15 中将显示 0。

由于“风险暴露”列用于计算值，因此您不应允许在该列中输入数据。

• 选择单元格 G6-G15。

• 右键单击，然后在出现的下拉列表中选择“设置单元格格式”。将出现“设置单元格格式”对话框。

• 单击“保护”选项卡。

• 选中“锁定”选项。

这是为了确保不允许在这些单元格中输入数据。但是，只有在工作表受保护后（您将在工作表准备就绪后执行最后一步）才会生效。

• 单击“确定”。
• 对单元格 G6-G15 进行着色以指示它们是计算值。

设置序列号值的格式

您可以让用户填写“序号”列。但是，如果您设置“序号”值的格式，工作表看起来更美观。此外，它还显示了工作表的格式化行数。

在单元格 B6 中键入 =ROW()-5 并按 Enter 键。

单元格 B6 中将显示 1。将公式复制到单元格 B6-B15。将显示值 1-10。

对单元格 B6-B15 进行着色。

总结

您的项目即将完成。

• 隐藏包含数据类别值的 M 列。
• 为单元格 B6-K16 设置边框格式。

• 右键单击工作表标签。
• 从菜单中选择“保护工作表”。

将出现“保护工作表”对话框。

• 选中“保护工作表和锁定单元格的内容”选项。
• 在“取消保护工作表密码”下输入密码 -
  • 密码区分大小写
  • 如果忘记密码，则无法恢复受保护的工作表
  • 最好将工作表名称和密码列表保存在某个地方
• 在“允许所有用户执行以下操作”下，选中“选择未锁定单元格”复选框。

您已保护“风险暴露”列中的锁定单元格免受数据输入，并保持其余未锁定单元格可编辑。单击“确定”。

将出现“确认密码”对话框。

• 重新输入密码。
• 单击“确定”。

您已准备好使用为选定单元格设置了数据验证的工作表。