校验excel表格数据类型_如何使用数据验证限制Excel中的数据输入

校验excel表格数据类型

If you use Excel spreadsheets to collect data from other people, but find that they often fill your carefully-planned cells with the wrong kind of information, data validation can help.

如果您使用Excel电子表格从其他人那里收集数据，但发现它们经常用错误的信息填充您精心计划的单元格，则数据验证会有所帮助。

This tool lets you restrict specific cells to only allow properly-formatted data. If someone enters anything that’s not supposed to be there—like “lunch at airport” instead of “$15.68” on an expense report—Excel rejects the input until they get it right. Think of it as a passive-aggressive way to make sure people don’t waste your time.

使用此工具，您可以限制特定的单元格以仅允许使用格式正确的数据。 如果有人输入了不应该在此处输入的任何内容(例如费用报告中的“在机场享用午餐”而不是“ 15.68美元”)，Excel将拒绝输入，直到他们正确输入为止。 可以将其视为一种消极进取的方式，以确保人们不会浪费您的时间。

As an example, here’s the basic expense report worksheet for How-To Geek. Let’s say we want to make sure that people only enter numerical values that are formatted as currency (i.e., some digits, followed by a decimal point, followed by two more digits) into certain cells.

例如，这是How-To Geek的基本费用报告工作表。 假设我们要确保人们仅在某些单元格中输入以货币格式设置的数字值(即，一些数字，后跟一个小数点，再跟两个数字)。

First, select all the cells you want to restrict.

首先，选择要限制的所有单元格。

Switch over to the “Data” tab on the Ribbon, and then click the “Data Validation” button. If your window isn’t full size and you can’t see the labels, it’s the icon with two horizontal boxes, a green check mark, and a red crossed circle.

切换到功能区上的“数据”选项卡，然后单击“数据验证”按钮。 如果您的窗口不是全尺寸，并且看不到标签，则为带有两个水平框，绿色复选标记和红色交叉圆圈的图标。

In the Data Validation window, on the “Settings” tab, click the “Allow” dropdown menu. Here, you can set a specific type of input to allow for your selected cells. For our expense report, we’re going to insist that users put in a number with two decimal values, so we would select the “Decimal” option. You can also select other criteria, like making sure a cell contains text, a time or date, text of a specific length, or even your own custom validation.

在“数据验证”窗口中的“设置”选项卡上，单击“允许”下拉菜单。 在这里，您可以设置特定的输入类型以允许您选择的单元格。 对于费用报告，我们将坚持要求用户输入带有两个十进制值的数字，因此我们将选择“十进制”选项。 您还可以选择其他条件，例如确保单元格包含文本，时间或日期，特定长度的文本，甚至是您自己的自定义验证。

Whatever type of data you select on the “Allow” dropdown changes the options available to you on the rest of the “Settings” tab. Since we want a numerical value corresponding to currency, we’re setting the “Data” dropdown to the “between” setting. Then, we’re configuring a minimum value of 0.00 and a maximum value of 10000.00, which is way more than enough to cover our needs.

在“允许”下拉列表中选择的任何数据类型都会更改“设置”标签其余部分上可用的选项。 由于我们想要一个与货币相对应的数值，因此我们将“数据”下拉列表设置为“介于”设置之间。 然后，我们配置最小值0.00和最大值10000.00，这足以满足我们的需求。

To test it our, click “OK” to apply the validation settings, and then try putting in an improper value. For example, if we type “pancakes” for the Breakfast value instead of the cost of the meal, we’ll get an error message.

要对其进行测试，请单击“确定”以应用验证设置，然后尝试输入不正确的值。 例如，如果我们在“早餐”值中键入“ pancakes”而不是餐费，则会收到错误消息。

While that does restrict people to entering only the correct type of data, it doesn’t give them any feedback on what type of data is required. So, let’s set that up, too.

尽管这确实限制了人们仅输入正确的数据类型，但是它并没有给他们任何关于所需数据类型的反馈。 因此，我们也进行设置。

Head back to the Data Validation window (Data > Data Validation on the Ribbon). You’ve got two options here (and you can use both of them if you want). You can use the “Input Message” tab to have a pop-up tool tip show people the type of data you want whenever they select a cell for which data validation is turned on. You can also use the “Error Alert” tab to customize the error they see when they enter the wrong type of data.

回到“数据验证”窗口(功能区上的“数据”>“数据验证”)。 您在这里有两个选择(如果需要，可以同时使用两个)。 您可以使用“输入消息”选项卡使弹出式工具提示向人们显示他们想要的数据类型，只要他们选择打开数据验证的单元格即可。 您还可以使用“错误警报”选项卡来自定义他们输入错误数据类型时看到的错误。

Let’s switch over to the “Input Message” tab first. Here, make sure the “Show input message when cell is selected” option is turned on. Then, give your input tooltip a title and some text. As you can see below, just clicking in one of the cells pops up the message letting people know what’s expected.

让我们首先切换到“输入消息”选项卡。 在此，请确保“选中单元格时显示输入消息”选项已打开。 然后，给您的输入工具提示一个标题和一些文本。 正如您在下面看到的那样，只需单击其中一个单元格，就会弹出消息，让人们知道预期的结果。

On the “Error Alert” tab, you can customize the error message people see when they enter the wrong type of data. Make sure the “Show error alert after invalid data is entered” option is turned on. Pick a style for your error message from the “Style” dropdown. You can go with a Stop (the red circle with the X), Warning (yellow triangle with an exclamation point), or Information (blue circle with a lowercase “i”), depending on how strong you want the message to come across.

在“错误警报”标签上，您可以自定义当用户输入错误的数据类型时看到的错误消息。 确保“输入无效数据后显示错误警报”选项已打开。 从“样式”下拉列表中为错误消息选择样式。 您可以选择Stop(停止)(带X的红色圆圈)，Warning(带感叹号的黄色三角形)或Information(带小写字母“ i”的蓝色圆圈)，具体取决于您希望消息传递的强度。

Type a title for your message, the text of the message itself, and then hit “OK” to finish up.

键入您的消息的标题，消息本身的文本，然后单击“确定”以完成操作。

Now, if someone tries to enter improper data, that error message is a little more helpful (or sarcastic, if you prefer).

现在，如果有人尝试输入不正确的数据，则该错误消息会有所帮助(或者，如果您愿意，则是讽刺)。

It’s a bit of extra legwork setting up data validation, but it can save you a lot of time later on if you use spreadsheets for collecting data from other people. It’s even useful for preventing your own mistakes. And this is doubly true if you’ve set up formulas or any kind of automation tasks that rely on that data.

设置数据验证需要一些额外的工作，但是如果您使用电子表格从其他人那里收集数据，则可以节省很多时间。 它对于防止自己的错误甚至很有用。 如果您已经建立了公式或任何依赖于该数据的自动化任务，这将成倍增加。

翻译自: https://www.howtogeek.com/349958/how-to-restrict-data-input-in-excel-with-data-validation/

校验excel表格数据类型