Excel 简明教程
Data Validation in Excel 2010
Data Validation
MS Excel 数据验证功能允许你设置某些规则来指定可以输入到单元格的内容。例如，你可能希望将特定单元格中的数据输入限制为介于 0 到 10 之间的整数。如果用户输入无效内容，则你可以显示自定义消息，如下所示。
MS Excel data validation feature allows you to set up certain rules that dictate what can be entered into a cell. For example, you may want to limit data entry in a particular cell to whole numbers between 0 and 10. If the user makes an invalid entry, you can display a custom message as shown below.
Validation Criteria
要指定单元格或区域中允许的数据类型，请按照以下步骤操作，其中显示了数据验证对话框的所有三个选项卡。
To specify the type of data allowable in a cell or range, follow the steps below, which shows all the three tabs of the Data Validation dialog box.
-
Select the cell or range.
-
Choose Data » Data Tools » Data Validation. Excel displays its Data Validation dialog box having 3 tabs settings, Input Message and Error alert.
Settings Tab
在这里，你可以设置所需验证类型。从允许下拉列表中选择一个选项。数据验证对话框的内容会发生变化，并根据你的选择显示控件。
Here you can set the type of validation you need. Choose an option from the Allow drop-down list. The contents of the Data Validation dialog box will change, displaying controls based on your choice.
-
Any Value − Selecting this option removes any existing data validation.
-
Whole Number − The user must enter a whole number.For example, you can specify that the entry must be a whole number greater than or equal to 50.
-
Decimal − The user must enter a number. For example, you can specify that the entry must be greater than or equal to 10 and less than or equal to 20.
-
List − The user must choose from a list of entries you provide. You will create drop-down list with this validation. You have to give input ranges then those values will appear in the drop-down.
-
Date − The user must enter a date. You specify a valid date range from choices in the Data drop-down list. For example, you can specify that the entered data must be greater than or equal to January 1, 2013, and less than or equal to December 31, 2013.
-
Time − The user must enter a time. You specify a valid time range from choices in the Data drop-down list. For example, you can specify that the entered data must be later than 12:00 p.m.
-
Text Length − The length of the data (number of characters) is limited. You specify a valid length by using the Data drop-down list. For example, you can specify that the length of the entered data be 1 (a single alphanumeric character).
-
Custom − To use this option, you must supply a logical formula that determines the validity of the user’s entry (a logical formula returns either TRUE or FALSE).
