- Excel数据分析教程
- Excel 数据分析 - 主页
- 数据分析 - 概述
- 数据分析-流程
- Excel 数据分析 - 概述
- 使用范围名称
- 表格
- 使用文本函数清理数据
- 清理数据包含日期值
- 使用时间值
- 条件格式
- 排序
- 过滤
- 带范围的小计
- 快速分析
- 查找功能
- 数据透视表
- 数据可视化
- 数据验证
- 财务分析
- 使用多张纸
- 公式审核
- 查询
- 高级数据分析
- 高级数据分析 - 概述
- 数据整合
- 假设分析
- 使用数据表进行假设分析
- 假设分析场景管理器
- 通过目标寻求进行假设分析
- 使用 Excel 求解器进行优化
- 将数据导入 Excel
- 数据模型
- 使用数据透视表探索数据
- 使用 Powerpivot 探索数据
- 使用 Power View 探索数据
- 探索数据力量查看图表
- 探索数据电源视图地图
- 探索数据 PowerView 倍数
- 探索数据 Power View 磁贴
- 使用层次结构探索数据
- 审美力量查看报告
- 关键绩效指标
- Excel 数据分析资源
- Excel 数据分析 - 快速指南
- Excel 数据分析 - 资源
- Excel 数据分析 - 讨论
Excel 数据分析 - 数据验证
数据验证是 Excel 中一个非常有用且易于使用的工具,您可以使用它对输入到工作表中的数据设置数据验证。
对于工作表上的任何单元格,您可以
- 显示有关需要输入内容的输入消息。
- 限制输入的值。
- 提供可供选择的值列表。
- 显示错误消息并拒绝无效的数据输入。
考虑以下风险跟踪器,它可用于输入和跟踪已识别的风险信息。
在此跟踪器中,输入以下列的数据将使用预设的数据约束进行验证,并且仅当输入的数据满足验证标准时才会接受。否则,您将收到错误消息。
- 可能性
- 影响
- 风险类别
- 风险来源
- 地位
风险暴露列将具有计算值,您无法输入任何数据。即使列S. No.也设置为具有计算值,即使您删除一行,这些值也会进行调整。
现在,您将学习如何设置这样的工作表。
准备工作表的结构
准备工作表的结构 -
- 从空白工作表开始。
- 将标题放在第 2 行。
- 将列标题放在第 3 行中。
- 对于列标题概率、影响和风险暴露 -
- 右键单击单元格。
- 单击下拉菜单中的“设置单元格格式”。
- 在“设置单元格格式”对话框中,单击“对齐”选项卡。
- 在“方向”下键入 90。
- 将每个列标题的第 3、4 和 5 行中的单元格合并并居中。
- 设置第 2 – 5 行中单元格边框的格式。
- 调整行和列的宽度。
您的工作表将如下所示 -
为风险类别设置有效值
在单元格 M5 – M13 中输入以下值(M5 是标题,M6 – M13 是值)
类别值 |
终端用户 |
顾客 |
管理 |
日程 |
日程 |
环境 |
产品 |
项目 |
- 单击风险类别 (H6) 列下的第一个单元格。
- 单击功能区上的“数据”选项卡。
- 单击数据工具组中的数据验证。
- 从下拉列表中选择数据验证...。
将出现“数据验证”对话框。
- 单击“设置”选项卡。
- 在验证条件下的允许:下拉列表中,选择选项列表。
- 在出现的 Source: 框中选择范围 M6:M13。
- 选中出现的“忽略空白”和“单元格内下拉列表”框。
设置风险类别的输入消息
- 单击“数据验证”对话框中的“输入消息”选项卡。
- 选中选择单元格时显示输入消息框。
- 在标题:下的框中,输入风险类别:
- 在输入消息下的框中:从列表中选择风险类别。
为风险类别设置错误警报
设置错误警报 -
- 单击“数据验证”对话框中的“错误警报”选项卡。
- 选中输入无效数据后显示错误警报框。
- 在“样式:”下拉列表下选择“停止”
- 在标题:下的框中,输入无效条目:
- 在错误消息下的框中:键入从下拉列表中选择一个值。
- 单击“确定”。
验证风险类别的数据有效性
对于风险类别下选定的第一个单元格,
- 设定数据验证标准
- 输入消息已设置
- 已设置错误警报
现在,您可以验证您的设置。
单击已为其设置数据验证条件的单元格。出现输入消息。下拉按钮出现在单元格的右侧。
输入的信息正确显示。
单击单元格右侧的下拉按钮。将出现下拉列表,其中包含可选择的值。
将下拉列表中的值与用于创建下拉列表的值进行交叉检查。
两组值都匹配。请注意,如果值的数量较多,您将在下拉列表的右侧看到一个向下滚动条。
从下拉列表中选择一个值。它出现在单元格中。
您可以看到有效值的选择工作正常。
最后,尝试输入无效条目并验证错误警报。
在单元格中键入 People,然后按 Enter。将显示您为单元格设置的错误消息。
- 验证错误消息。
- 您可以选择重试或取消。验证这两个选项。
您已成功设置单元格的数据验证。
注意- 检查消息的拼写和语法非常重要。
为风险类别列设置有效标准
现在,您已准备好将数据验证条件应用到风险类别列中的所有单元格。
此时,您需要记住两件事 -
您需要设置可以使用的最大单元数的标准。在我们的示例中,根据工作表的使用位置,它可以在 10 到 100 之间变化。
您不应为不需要的单元格范围或整个列设置条件。这会不必要地增加文件大小。这称为过度格式化。如果您从外部源获取工作表,则必须删除多余的格式,您将在本教程的“查询”一章中了解这一点。
请按照以下步骤操作 -
- 在风险类别下设置 10 个单元格的验证标准。
- 您可以通过单击第一个单元格的右下角轻松完成此操作。
- 按住出现的 + 符号并将其向下拉。
为所有选定的单元格设置数据验证。
单击选定的最后一列并进行验证。
风险类别列的数据验证已完成。
设置风险源的验证值
在这种情况下,我们只有两个值——内部和外部。
- 单击风险源 (I6) 列下的第一个单元格
- 单击功能区上的数据选项卡
- 单击数据工具组中的数据验证
- 从下拉列表中选择数据验证...。
出现数据验证对话框。
- 单击“设置”选项卡。
- 在“验证条件”下的“允许:”下拉列表中,选择“列表”选项。
- 在出现的“源:”框中键入“Internal”、“External”。
- 选中出现的“忽略空白”和“单元格内下拉列表”框。
设置风险源的输入消息。
为风险源设置错误警报。
对于风险源下选定的第一个单元格 -
- 设定数据验证标准
- 输入消息已设置
- 已设置错误警报
现在,您可以验证您的设置。
单击已为其设置数据验证条件的单元格。出现输入消息。下拉按钮出现在单元格的右侧。
输入的消息正确显示。
单击单元格右侧的下拉箭头按钮。将出现一个下拉列表,其中包含可选择的值。
检查这些值是否与您输入的值相同 - 内部和外部。
两组值都匹配。从下拉列表中选择一个值。它出现在单元格中。
您可以看到有效值的选择工作正常。最后,尝试输入无效条目并验证错误警报。
在单元格中键入 Financial,然后按 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 中。0 将显示在单元格 G6 - G15 中。
由于风险暴露列用于计算值,因此您不应允许在该列中输入数据。
选择单元格 G6-G15
右键单击并在出现的下拉列表中选择“设置单元格格式”。将出现“设置单元格格式”对话框。
单击“保护”选项卡。
检查选项已锁定。
这是为了确保这些单元格中不允许数据输入。但是,只有当工作表受到保护时,这才会生效,您将在工作表准备好后执行最后一步。
- 单击“确定”。
- 将单元格 G6-G15 涂上阴影以表明它们是计算值。
设置序列号值的格式
您可以让用户填写 S. No. 栏。但是,如果设置 S. No. 值的格式,工作表看起来会更美观。此外,它还显示工作表的格式化行数。
在单元格 B6 中键入 =row()-5,然后按 Enter。
1 将出现在单元格 B6 中。将公式复制到单元格 B6-B15 中。出现值 1-10。
遮蔽细胞 B6-B15。
包起来
您的项目即将完成。
- 隐藏包含数据类别值的列 M。
- 设置单元格 B6-K16 的边框格式。
- 右键单击工作表选项卡。
- 从菜单中选择保护工作表。
将出现“保护工作表”对话框。
- 选中“保护工作表和锁定单元格的内容”选项。
- 在密码下输入密码以取消保护工作表 -
- 密码区分大小写
- 如果忘记密码,受保护的工作表将无法恢复
- 最好将工作表名称和密码列表保存在某处
- 在“允许此工作表的所有用户:”下,选中“选择未锁定的单元格”框。
您已保护“风险暴露”列中的锁定单元格不被输入数据,并使其余未锁定单元格保持可编辑状态。单击“确定”。
出现确认密码对话框。
- 重新输入密码。
- 单击“确定”。
为所选单元格设置了数据验证的工作表已可供使用。