Excel数据分析-数据验证

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

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

• 显示有关需要输入的内容的输入消息。
• 限制输入的值。
• 提供可供选择的值列表。
• 显示错误消息并拒绝无效的数据输入。

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

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

• 概率
• 冲击
• 风险类别
• 风险来源
• 地位

“风险敞口”列将具有计算值，您无法输入任何数据。甚至列 S. No. 也设置为具有即使删除行也会调整的计算值。

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

准备工作表的结构

准备工作表的结构 −

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

您的工作表将如下所示 -

为风险类别设置有效值

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

类别值

最终用户

客户

管理

附表

附表

环境

产品

项目

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

将出现“数据有效性”对话框。

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

• 在 来源： 出现的框。
• 选中显示的忽略空白和单元格内下拉菜单复选框。

设置风险类别的输入消息

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

为风险类别设置错误警报

设置错误警报 -

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

验证风险类别的数据验证

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

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

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

单击已为其设置数据有效性条件的单元格。此时将显示输入消息。下拉按钮显示在单元格的右侧。

输入消息显示正确。

• 单击单元格右侧的下拉按钮。此时将显示下拉列表，其中包含可以选择的值。

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

两组值都匹配。请注意，如果值的数量更多，您将在下拉列表的右侧看到一个向下滚动条。

从下拉列表中选择一个值。它显示在单元格中。

您可以看到有效值的选择工作正常。

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

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

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

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

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

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

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

在这一点上，你需要记住两件事——

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

• 不应为不需要的单元格区域或整个列设置条件。这将不必要地增加文件大小。它被称为过度格式化。如果您从外部来源获得工作表，则必须删除多余的格式，您将在本教程的“查询”一章中了解这些格式。

请按照以下步骤操作 -

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

为所有选定的单元格设置数据有效性。

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

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

设置风险源的验证值

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

• 单击风险源 （I6） 列下的第一个单元格
• 单击功能区上的数据选项卡
• 单击“数据工具”组中的“数据有效性”
• 选择数据验证...从下拉列表中。

将出现“数据有效性”对话框。

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

为风险源设置输入消息。

为风险源设置错误警报。

对于风险源下选定的第一个单元格 -

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

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

单击已为其设置数据有效性条件的单元格。此时将显示输入消息。下拉按钮显示在单元格的右侧。

输入消息显示正确。

• 单击单元格右侧的下拉箭头按钮。此时将显示一个下拉列表，其中包含可以选择的值。

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

两组值都匹配。从下拉列表中选择一个值。它显示在单元格中。

您可以看到有效值的选择工作正常。最后，尝试输入无效条目并验证错误警报。

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

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

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

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

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

设置状态的验证值

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

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

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

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

设置概率的验证值

风险概率评分值在 1-5 范围内，1 表示低，5 表示高。该值可以是介于 1 和 5 之间的任何整数，包括两者。

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

将出现“数据有效性”对话框。

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

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

设置概率的输入消息

为概率设置错误警报，然后单击确定。

对于概率下选定的第一个单元格，

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

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

单击已为其设置数据验证条件的单元格。此时将显示输入消息。在这种情况下，不会有下拉按钮，因为输入值设置为在范围内而不是从列表中。

输入消息显示正确。

在单元格中输入一个介于 1 和 5 之间的整数。它显示在单元格中。

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

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

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

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

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

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

设置影响的验证值

若要设置“影响”的验证值，请重复用于设置概率验证值的相同步骤。

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

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

使用计算值设置列风险敞口

风险敞口计算为风险概率和风险影响的乘积。

风险敞口=概率*影响

键入 = E6 * F6 在单元格G6中，然后按输入。

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

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

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

• 选择单元格 G6-G15

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

• 单击保护选项卡。

• 检查选项 锁定.

这是为了确保不允许在这些单元格中输入数据。但是，这仅在工作表受到保护时生效，您将在工作表准备就绪后的最后一步执行此操作。

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

设置序列号值的格式

您可以将其留给用户填写 S. No。列。但是，如果您格式化 S.值，则工作表看起来更像样。此外，它还显示工作表的格式有多少行。

键入 =row（）-5 在单元格 B6 中，然后按 Enter 键。

1 将出现在单元格 B6 中。复制单元格 B6-B15 中的公式。将显示值 1-10。

给细胞 B6-B15 着色。

总结

您几乎完成了您的项目。

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

• 右键单击工作表选项卡。
• 从菜单中选择保护工作表。

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

• 检查选项 保护工作表和锁定单元格的内容.
• 在“取消保护工作表的密码”下键入密码 -
  • 密码区分大小写
  • 如果忘记密码，则无法恢复受保护的工作表
  • 最好在某处保留工作表名称和密码列表
• 在“允许此工作表的所有用户：”下，选中“选择未锁定的单元格”框。

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

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

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

为所选单元格设置了“数据有效性”的工作表已准备就绪，可供使用。