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