Excel 数据分析 - 数据验证
数据验证是 Excel 中一个非常有用且易于使用的工具，您可以使用它对输入到工作表中的数据设置数据验证。
对于工作表上的任何单元格，您可以
- 显示有关需要输入的内容的输入消息。
- 限制输入的值。
- 提供可供选择的值列表。
- 显示错误消息并拒绝无效数据输入。
考虑以下可用于输入和跟踪已识别风险信息的风险跟踪器。
在此跟踪器中，输入到以下列的数据会使用预设数据约束进行验证，并且只有在满足验证条件时才会接受输入的数据。 否则，您将收到一条错误消息。
- Probability(概率)
- Impact(影响)
- Risk Category(风险类别)
- Risk Source(风险来源)
- Status(状态)
Risk Exposure 列将具有计算值，您不能输入任何数据。 即使列 S. No. 也设置为具有计算值，即使您删除了一行，这些值也会进行调整。
现在，您将学习如何设置这样的工作表。
准备工作表的结构
准备工作表的结构 −
- 从空白工作表开始。
- 将标题放在第 2 行。
- 将列标题放在第 3 行。
- 对于列标题 Probability, Impact 和 Risk Exposure −
- 右键单击单元格。
- 点击下拉菜单中的单元格格式。
- 在"单元格格式"对话框中，单击"对齐"选项卡。
- 在方向下键入 90。
- 合并每个列标题的第 3、4 和 5 行中的单元格并将其居中。
- 第 2 至 5 行单元格的边框格式。
- 调整行宽和列宽。
您的工作表将如下所示 −
为 Risk Category(风险类别)设置有效值
在单元格 M5 - M13 中输入以下值（M5 是标题，M6 - M13 是值）
| Category Values |
| End-Users |
| Customer |
| Management |
| Schedule |
| Schedule |
| Environment |
| Product |
| Project |
- 单击"Risk Category (H6)"列下的第一个单元格。
- 单击功能区上的"数据"选项卡。
- 点击"数据工具"组中的"数据验证"。
- 从下拉列表中选择数据验证...。
出现数据验证对话框。
- 单击"设置"选项卡。
- 在验证条件下的允许:下拉列表中，选择选项列表。
- 在出现的来源:框中选择范围 M6:M13。
- 选中出现的"忽略空白"和"单元格内"下拉框。
为 Risk Category 设置输入消息
- 单击"数据验证"对话框中的"输入消息"选项卡。
- 选中单元格时显示输入消息框。
- 在 Title: 下的框中，键入 Risk Category:
- 在输入消息下方的框中:从列表中选择 Risk Category。
为 Risk Category(风险类别)设置错误警报
设置错误提示 −
- 单击"数据验证"对话框中的"错误警报"选项卡。
- 选中输入无效数据后显示错误警报复选框。
- 在"样式"下选择"停止":下拉列表
- 在"标题:"下的框中，输入无效条目:
- 在错误消息下方的框中:键入从下拉列表中选择一个值。
- 单击"确定"。
验证 Risk Category 的数据有效性
对于 Risk Category 下选定的第一个单元格，
- 设置数据验证标准
- 设置输入消息
- 设置了错误警报
现在，您可以验证您的设置。
单击已设置数据验证条件的单元格。 输入消息出现。 下拉按钮出现在单元格的右侧。
输入信息正确显示。
单击单元格右侧的下拉按钮。 出现下拉列表，其中包含可选择的值。
将下拉列表中的值与用于创建下拉列表的值进行交叉检查。
两组值都匹配。 请注意，如果值的数量更多，您将在下拉列表的右侧看到一个向下滚动条。
从下拉列表中选择一个值。 它出现在单元格中。
您可以看到有效值的选择工作正常。
最后，尝试输入无效条目并验证错误警报。
在单元格中键入人员，然后按 Enter 键。 将显示您为单元格设置的错误消息。
- 验证错误消息。
- 您可以选择重试或取消。 验证这两个选项。
您已成功为单元格设置数据验证。
注意 − 检查邮件的拼写和语法非常重要。
为 "Risk Category" 列设置有效标准
现在，您已准备好将数据验证标准应用于 "Risk Category" 列中的所有单元格。
此时，你需要记住两件事 −
您需要为可能使用的最大单元格数设置条件。 在我们的示例中，它可以根据工作表的使用位置在 10 到 100 之间变化。
您不应为不需要的单元格区域或整列设置条件。 这将不必要地增加文件大小。 它被称为过度格式化。 如果您从外部来源获得工作表，则必须删除多余的格式，您将在本教程的查询一章中学习。。 p>
按照下面给出的步骤 −
- 为 Risk Category 下的 10 个单元格设置验证标准。
- 您可以通过单击第一个单元格的右下角轻松地完成此操作。
- 按住出现的 + 符号并将其向下拉。
为所有选定的单元格设置数据验证。
单击选中的最后一列并进行验证。
Risk Category 列的数据验证已完成。
为 Risk Source（风险来源）设置验证值
在这种情况下，我们只有两个值 Internal 和 External。
- 单击"Risk Source (I6)"列下的第一个单元格
- 单击功能区上的"数据"选项卡
- 点击"数据工具"组中的"数据验证"
- 从下拉列表中选择数据验证...。
出现数据验证对话框。
- 单击"设置"选项卡。
- 在"验证条件"下的"允许:"下拉列表中，选择"列表"选项。
- 在出现的 Source: 框中键入 Internal、External。
- 选中出现的"忽略空白"和"单元格内"下拉框。
为 Risk Source 设置输入消息。
为 Risk Source 设置错误警报。
对于 Risk Source 下选定的第一个单元格 −
- 设置数据验证标准
- 设置输入消息
- 设置了错误警报
现在，您可以验证您的设置。
单击已设置数据验证条件的单元格。 出现输入信息。 下拉按钮出现在单元格的右侧。
输入的消息正确显示。
单击单元格右侧的下拉箭头按钮。 出现一个下拉列表，其中包含可以选择的值。
检查值是否与您键入的值相同 – Internal 和 External。
两组值都匹配。 从下拉列表中选择一个值。 它出现在单元格中。
您可以看到有效值的选择工作正常。 最后，尝试输入无效条目并验证错误警报。
在单元格中键入 Financial，然后按 Enter。 将显示您为单元格设置的错误消息。
验证错误信息。 您已成功为单元格设置数据验证。
为 Risk Source 列设置有效标准
将数据验证标准应用于 Risk Source 列中的单元格 I6 - I15（即与风险类别列的范围相同）。
为所有选定的单元格设置了数据验证。 Risk Source 列的数据验证已完成。
为 Status(状态)设置验证值
重复您用于设置 Risk Source 验证值的相同步骤。
将 List 值设置为 Open、Closed。
将数据验证标准应用于"Status"列中的单元格 K6 - K15（即与"Risk Category"列的范围相同）。
为所有选定的单元格设置了数据验证。 Status 列的数据验证已完成。
为 Probability(概率)设置验证值
Probability 值在 1-5 范围内，1 表示低，5 表示高。 该值可以是 1 到 5 之间的任何整数，包括两者。
- 单击Risk Source(I6) 列下的第一个单元格。
- 单击功能区上的"数据"选项卡。
- 点击"数据工具"组中的"数据验证"。
- 从下拉列表中选择数据验证...。
出现数据验证对话框。
- 单击"设置"选项卡。
- 在"验证条件"下的"允许:"下拉列表中，选择"整数"。
- 在数据下选择:
- 在最小值下的框中键入 1:
- 在最大值下的框中键入 5:
为 Probability 设置输入信息
为 Probability 设置错误警报并单击确定。
对于 Probability 下选定的第一个单元格，
- 设置了数据验证标准。
- 已设置输入消息。
- 设置了错误警报。
现在，您可以验证您的设置。
单击您已为其设置数据验证标准的单元格。 出现输入信息。 在这种情况下，不会有下拉按钮，因为输入值设置在一个范围内而不是来自列表。
输入信息正确显示。
在单元格中输入 1 到 5 之间的整数。 它出现在单元格中。
有效值的选择工作正常。 最后，尝试输入无效条目并验证错误警报。
在单元格中键入 6，然后按 Enter。 将显示您为单元格设置的错误消息。
您已成功为单元格设置数据验证。
为 Probability 列设置有效条件。
将数据验证标准应用于 Probability 列中的单元格 E6 - E15（即与 Risk Category 列的范围相同）。
为所有选定的单元格设置了数据验证。 Probability 列的数据验证已完成。
为 Impact(影响)设置验证值
要设置 Impact 的验证值，请重复设置 Probability 验证值所用的相同步骤。
将数据验证标准应用于 Impact 列中的单元格 F6 - F15（即与 Risk Category 列的范围相同）。
为所有选定的单元格设置了数据验证。 Impact 列的数据验证已完成。
使用计算值设置 Risk Exposure(风险敞口)列
Risk Exposure(风险敞口)计算为Risk Probability(风险概率)和Risk Impact(风险影响)的乘积。
Risk Exposure = Probability * Impact
在单元格 G6 中键入 =E6*F6，然后按 Enter。
0 将显示在单元格 G6 中，因为 E6 和 F6 为空。
复制单元格 G6 – G15 中的公式。 0 将显示在单元格 G6 - G15 中。
由于Risk Exposure(风险敞口)列用于计算值，因此您不应允许在该列中输入数据。
选择单元格 G6-G15
右键单击并在出现的下拉列表中选择"设置单元格格式"。 出现格式单元格对话框。
单击"保护"选项卡。
选中锁定选项。
这是为了确保不允许在这些单元格中输入数据。 但是，这只有在工作表受到保护时才会生效，这将作为工作表准备好后的最后一步。
- 单击"确定"。
- 为单元格 G6-G15 加阴影以表明它们是计算值。
格式化序列号值
可以留给用户填写 S. No 列。 但是，如果您设置 S. No 值的格式，工作表看起来更美观。 此外，它还显示工作表格式化的行数。
在单元格 B6 中键入 =row()-5 并按 Enter。
1 将出现在单元格 B6 中。 复制单元格 B6-B15 中的公式。 出现值 1-10。
隐藏单元格 B6-B15。
总结
您的项目即将完成。
- 隐藏包含数据类别值的 M 列。
- 为单元格 B6-K16 设置边框格式。
- 右键单击工作表选项卡。
- 从菜单中选择"保护工作表"。
出现"保护工作表"对话框。
- 选中"保护工作表和锁定单元格的内容"选项。
- 在密码下输入密码以取消保护工作表 −
- 密码区分大小写
- 如果忘记密码，则无法恢复受保护的工作表
- 最好在某处保存工作表名称和密码列表
- 在"允许此工作表的所有用户:"下选中"选择未锁定的单元格"复选框。
您已保护列 Risk Exposure 中的锁定单元格免受数据输入，并保持其余未锁定单元格可编辑。 单击"确定"。
出现确认密码对话框。
- 重新输入密码。
- 单击"确定"。
为所选单元格设置了数据验证的工作表可以使用了。
