Excel Data Analysis 简明教程

Excel Data Analysis - Data Validation

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

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

  1. 在输入消息中显示需要输入的内容。

  2. 限制输入的值。

  3. 提供一个值列表供选择。

  4. 显示错误信息并拒绝无效的数据输入。

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

risk tracker

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

  1. Probability

  2. Impact

  3. Risk Category

  4. Risk Source

  5. Status

“风险敞口”列将具有计算值，您不能输入任何数据。甚至连 S. No. 列都设置为具有计算值，即使您删除了一行，这些计算值也会进行调整。

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

Prepare the Structure for the Worksheet

为工作表的结构做准备 −

  1. 从一个空白工作表开始。

  2. 在第 2 行放置标题。

  3. 在第 3 行放置列标题。

  4. 对于列标题“概率”、“影响”和“风险敞口”− 右键单击该单元格。从下拉菜单中单击“设置单元格格式”。在“设置单元格格式”对话框中，单击“对齐”选项卡。在“方向”下键入 90。

  5. 在第 3、4 和 5 行中每个列标题的单元格中进行合并和居中。

  6. 对第 2 - 5 行的单元格设置边框格式。

  7. 调整行宽和列宽。

您的工作表将如下所示 −

worksheet result

Set Valid Values for Risk Category

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

Category Values

End-Users

Customer

Management

Schedule

Schedule

Environment

Product

Project

  1. 单击“风险类别”列（H6）下的第一个单元格。

  2. 单击功能区上的数据选项卡。

  3. 单击数据工具组中的数据验证。

  4. 从下拉列表中选择数据验证…​。

select data validation

数据验证对话框出现。

  1. Click the Settings tab.

  2. 在验证条件下的 Allow: 下拉列表中，选择选项 List 。

select list

  1. 在出现的源: 方框中选择 M6：M13 区域。

  2. 选中出现的忽略空白和单元格下拉列表复选框。

check boxes

Set Input Message for Risk Category

  1. 单击数据验证对话框中的输入消息选项卡。

  2. 选中单元格被选中时的 Show input message 复选框。

  3. 在标题下方的框中输入风险类别：

  4. 在输入消息下方的框中输入从列表中选择风险类别。

show input message

Set Error Alert for Risk Category

要设置错误警报，请执行以下操作：

  1. 单击数据验证对话框中的错误警报选项卡。

  2. 选中输入无效数据后显示错误警报复选框。

  3. 在样式下拉菜单中选择停止

  4. 在标题下方的框中输入无效条目：

  5. 在错误消息下方的框中输入从下拉列表中选择一个值。

  6. Click OK.

set error alert

Verify Data Validation for Risk Category

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

  1. 已设置数据验证条件

  2. Input message is set

  3. Error alert is set

现在，您可以验证设置。

单击您已针对其设置数据验证条件的单元格。此时，将显示“输入”消息。“下拉”按钮将出现在单元格右侧。

verify data validation

“输入”消息显示正确。

  1. 单击单元格右侧的“下拉”按钮。此时，将显示一个下拉列表，其中列出了可以选择的值。

  2. 交叉检查下拉列表中的值与创建下拉列表时使用的值。

message correctly displayed

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

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

dropdown list

您可以看到合法值的选择工作正常。

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

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

error message displayed

  1. Verify the Error message.

  2. 您可以选择重试或取消。验证这两个选项。

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

Note − 查看您消息的拼写和语法非常重要。

Set Valid Criteria for the Risk Category Column

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

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

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

  2. 您不应为不需要的单元格范围或整个列设置条件。这会不必要地增加文件大小。这称为多余格式。如果您从外部来源获取了工作表，则必须删除多余格式，您将在本教程的 Inquire 章节中了解这一点。

按照以下步骤操作 −

  1. 为风险类别下的 10 个单元格设置验证条件。

  2. 您可以通过单击第一个单元格的右下角轻松实现此操作。

  3. 按住出现的 + 符号然后向下拉。

set valid criteria

数据验证被设置为所有选中的单元格。

单击被选中的最后一列并验证。

column selected and verify

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

Set Validation Values for Risk Source

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

  1. 单击风险来源列 (I6) 下面的第一个单元格

  2. 单击功能区上的数据选项卡

  3. 单击数据工具组中的数据验证

  4. 从下拉列表中选择数据验证…​。

将出现数据验证对话框。

  1. Click the Settings tab.

  2. 在验证条件中，在允许：下拉列表中，选择列表选项。

  3. 在出现的源：方框中键入内部，外部。

  4. 选中出现的忽略空白和单元格下拉列表复选框。

set validation values

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

set input

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

set error alert

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

  1. 已设置数据验证条件

  2. Input message is set

  3. Error alert is set

现在，您可以验证设置。

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

verify settings

输入消息正确显示。

  1. 单击单元格右侧的下拉箭头按钮。将出现一个下拉列表，其中包含可选择的值。

  2. 检查这些值是否与您键入的值相同 - 内部和外部。

input message displayed correctly

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

cell

你可以看到，有效值的选定正常工作。最后，尝试输入一个无效的条目并核实错误警告。

在单元格中键入财务并按 Enter。你会看到设置到该单元格的错误消息。

type financial

  1. 确认错误消息。你已经成功设置了单元格的数据验证。

  2. 设置风险来源列的有效标准

  3. 将数据验证标准应用到风险来源列的单元格 I6 - I15（即与风险类别列的相同范围）。

数据验证已设置到所有所选单元格。数据验证已针对风险来源列完成。

Set Validation Values for Status

  1. 重复你过去用于设置风险来源验证值时的相同步骤。

  2. 设置列表值为打开、关闭。

  3. 将数据验证标准应用到状态列的单元格 K6 - K15（即与风险类别列的相同范围）。

数据验证已设置到所有所选单元格。数据验证已针对状态列完成。

Set Validation Values for Probability

风险概率分数值在范围 1-5 内，1 为低，5 为高。此值可以是 1 和 5 之间的任意整数（包括 1 和 5）。

  1. 点击风险来源列（I6）下的第一个单元格。

  2. 单击功能区上的“数据”选项卡。

  3. 单击数据工具组中的数据验证。

  4. 从下拉列表中选择数据验证…​。

数据验证对话框出现。

  1. Click the Settings tab.

  2. 在验证标准中，在允许下拉列表中，选择整数。

data validation

  1. Select between under Data:

  2. 在最小值下方的方框中键入 1：

  3. 在最大值下方的方框中键入 5：

select between

设置概率的输入消息

set input message

设置概率的错误警告并点击确定。

set error alert for probability

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

  1. 已设置数据验证标准。

  2. Input message is set.

  3. Error alert is set.

现在，您可以验证设置。

点击已设置数据验证准则的单元格。出现输入信息。在这种情况下，不会有下拉按钮，因为输入值被设置为在一个范围内，而非一个列表中。

click cell

“输入”消息显示正确。

在单元格中输入介于 1 和 5 之间的整数。它会出现在单元格中。

enter integer

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

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

type 6

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

  1. 设置“可能性”列的有效准则。

  2. 将数据验证准则应用于“可能性”列中的单元格 E6 - E15（即与“风险类别”列相同的范围）。

已为所有选定的单元格设置了数据验证。列“可能性”的数据验证完成。

Set Validation Values for Impact

若要设置影响的验证值，请重复为设置可能性的验证值所用的相同步骤。

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

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

Set the Column Risk Exposure with Calculated Values

风险暴露被计算为风险可能性和风险影响的乘积。

风险暴露 = 可能性 * 影响

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

set column risk exposure

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

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

copy formula

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

  1. Select cells G6-G15

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

  3. Click the Protection tab.

  4. Check the option Locked.

locked

这是为了确保不允许在那些单元格中输入数据。不过，只有在工作表受保护后，这才会生效。工作表准备好后，你将作为最后一步执行此操作。

  1. Click OK.

  2. 为单元格 G6-G15 设置底纹以表明它们是计算值。

shade cells

Format Serial Number Values

你可以让用户自己填写序号列。但是，如果你对序号值进行格式化，那么工作表看起来会更美观。此外，它还可以显示工作表格式化了多少行。

在单元格 B6 中输入 =row()-5，然后按 Enter 键。

format serial number values

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

values appear

对单元格 B6-B15 进行阴影填充。

Wrap-up

你的项目几乎就完成了。

  1. 隐藏包含数据类别的列 M。

  2. 设置单元格 B6-K16 的边框格式。

wrapup

  1. 右键单击工作表选项卡。

  2. 从菜单中选择“保护工作表”。

select protect sheet

“保护工作表”对话框随即显示。

  1. 选中“保护工作表和锁定单元格的内容”选项。

  2. 在“解锁工作表的密码”下输入一个密码 - 密码区分大小写如果忘记了密码，将无法恢复受保护的工作表建议在某处保留工作表名称和密码的列表

  3. 选中“允许此工作表的所有用户：”下的“选择解锁的单元格”复选框。

select unlocked cells

你已将列“风险敞口”中的锁定单元格受保护，不准录入数据，并将其余的解锁单元格保持为可编辑状态。单击“确定”。

“ Confirm Password ”对话框显示。

confirm password

  1. Re-enter the password.

  2. Click OK.

已设置好数据验证的单元格的工作表可以使用了。

worksheet with data validation