Data validation excel как пользоваться
Excel обладает очень неплохим инструментом для проверки введенных данных. В их число входит создание выпадающего списка. В этом случае в одной ячейке может содержаться несколько значений, организованных в виде списка
Предназначен такой список не только для удобства ввода, но и для контроля вводимых в ячейку данных. Создав такой список можно настроить его так, чтобы пользователь имел возможность вводить в ячейку только значения из списка (такие настройки используются по умолчанию) или использовал список, но мог так же ввести любое значение не из списка.
Создание списков
Необходимо выбрать ячейку(или сразу несколько ячеек), в которую необходимо поместить этот список. В меню выбираем Данные (Data) -Проверка данных (Data Validation) . Переходим на вкладку Параметры (Settings) и в выпадающем списке Тип данных (Allow) выбираем Список (List) .
Поле Источник (Source) : предназначено как раз для ввода значений, из которых будет состоять выпадающий список. Варианты указания значений:
- Вариант 1
Ввести список значений вручную (значения в этом случае необходимо заносить через "точку-с-запятой" для русской локализации и через запятую для английской). - Вариант 2
Указание в качестве источника обычной ссылки на диапазон ячеек
При использовании обычного диапазона в поле Источник (Source) просто указываем диапазон ячеек со значениями (A1:A10). Для этого ставим курсор мыши в это поле и затем выделяем необходимый диапазон со значениями. Перед адресом диапазона Excel сам поставит знак равно: =$A$1:$A$10
Недостаток данного метода: нельзя применять ссылки на другие листы и книги. Список будет работать только в случае, если ячейки расположены на том же листе, что и сам список. Что порой не очень удобно, поэтому чаще используется Вариант 3. - Вариант 3
Указание в качестве источника именованного диапазона
При использовании именованного диапазона необходимо сначала его создать: Создание именованных диапазонов. Предположим, что мы создали именованный диапазон с именем Список1.
После создания именованного диапазона в поле Источник (Source) вписываем имя этого диапазона, не забыв поставить перед именем знак равно:
Параметры списков
Распространить изменения на другие ячейки с тем же условием (Apply this chages to all other cells with the same settings) - данный пункт понадобится уже после создания списка в ячейках: если на листе есть много разных проверок данных необходимо изменить тип или условия проверки. Выделяете одну ячейку на листе, меняете необходимые параметры проверки данных и ставите данную галку. Нажимаете ОК. Внесенные изменения будут применены для всех ячеек, в которых были такие же условия.
Подробнее с остальными возможностями проверки данных(помимо выпадающих списков) можно ознакомиться в этой статье: Проверка данных
Use data validation to restrict the type of data or the values that users enter into a cell. One of the most common data validation uses is to create a drop-down list.
Try it!
Select the cell(s) you want to create a rule for.
Select Data >Data Validation.
On the Settings tab, under Allow, select an option:
Whole Number - to restrict the cell to accept only whole numbers.
Decimal - to restrict the cell to accept only decimal numbers.
List - to pick data from the drop-down list.
Date - to restrict the cell to accept only date.
Time - to restrict the cell to accept only time.
Text Length - to restrict the length of the text.
Custom – for custom formula.
Under Data, select a condition.
Set the other required values based on what you chose for Allow and Data.
Select the Input Message tab and customize a message users will see when entering data.
Select the Show input message when cell is selected checkbox to display the message when the user selects or hovers over the selected cell(s).
Select the Error Alert tab to customize the error message and to choose a Style.
Now, if the user tries to enter a value that is not valid, an Error Alert appears with your customized message.
Download our examples
If you're creating a sheet that requires users to enter data, you might want to restrict entry to a certain range of dates or numbers, or make sure that only positive whole numbers are entered. Excel can restrict data entry to certain cells by using data validation, prompt users to enter valid data when a cell is selected, and display an error message when a user enters invalid data.
Restrict data entry
Select the cells where you want to restrict data entry.
On the Data tab, click Data Validation > Data Validation.
Note: If the validation command is unavailable, the sheet might be protected or the workbook might be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected. For more information about workbook protection, see Protect a workbook.
In the Allow box, select the type of data you want to allow, and fill in the limiting criteria and values.
Note: The boxes where you enter limiting values will be labeled based on the data and limiting criteria that you have chosen. For example, if you choose Date as your data type, you will be able to enter limiting values in minimum and maximum value boxes labeled Start Date and End Date.
Prompt users for valid entries
When users click in a cell that has data entry requirements, you can display a message that explains what data is valid.
Select the cells where you want to prompt users for valid data entries.
On the Data tab, click Data Validation > Data Validation.
Note: If the validation command is unavailable, the sheet might be protected or the workbook might be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected. For more information about workbook protection, see Protect a workbook.
On the Input Message tab, select the Show input message when cell is selected check box.
In the Title box, type a title for your message.
In the Input message box, type the message that you want to display.
Display an error message when invalid data is entered
If you have data restrictions in place and a user enters invalid data into a cell, you can display a message that explains the error.
Select the cells where you want to display your error message.
On the Data tab, click Data Validation > Data Validation.
Note: If the validation command is unavailable, the sheet might be protected or the workbook might be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected. For more information about workbook protection, see Protect a workbook.
On the Error Alert tab, in the Title box, type a title for your message.
In the Error message box, type the message that you want to display if invalid data is entered.
Do one of the following:
On the Style pop-up menu, select
Require users to fix the error before proceeding
Warn users that data is invalid, and require them to select Yes or No to indicate if they want to continue
Warn users that data is invalid, but allow them to proceed after dismissing the warning message
Add data validation to a cell or a range
Note: The first two steps in this section are for adding any type of data validation. Steps 3-7 are specifically for creating a drop-down list.
Select one or more cells to validate.
On the Data tab, in the Data Tools group, click Data Validation.
On the Settings tab, in the Allow box, select List.
In the Source box, type your list values, separated by commas. For example, type Low,Average,High.
Make sure that the In-cell dropdown check box is selected. Otherwise, you won't be able to see the drop-down arrow next to the cell.
To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
Test the data validation to make sure that it is working correctly. Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.
After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if the cell is wide enough to show all your entries.
Remove data validation - Select the cell or cells that contain the validation you want to delete, then go to Data > Data Validation and in the data validation dialog press the Clear All button, then click OK.
The following table lists other types of data validation and shows you ways to add it to your worksheets.
Follow these steps:
Restrict data entry to whole numbers within limits.
Follow steps 1-2 above.
From the Allow list, select Whole number.
In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.
Enter the minimum, maximum, or specific value to allow.
You can also enter a formula that returns a number value.
For example, say you're validating data in cell F1. To set a minimum limit of deductions to two times the number of children in that cell, select greater than or equal to in the Data box and enter the formula, =2*F1, in the Minimum box.
Restrict data entry to a decimal number within limits.
Follow steps 1-2 above.
In the Allow box, select Decimal.
In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.
Enter the minimum, maximum, or specific value to allow.
You can also enter a formula that returns a number value. For example, to set a maximum limit for commissions and bonuses of 6% of a salesperson's salary in cell E1, select less than or equal to in the Data box and enter the formula, =E1*6%, in the Maximum box.
Note: To let a user enter percentages, for example 20%, select Decimal in the Allow box, select the type of restriction that you want in the Data box, enter the minimum, maximum, or specific value as a decimal, for example .2, and then display the data validation cell as a percentage by selecting the cell and clicking Percent Style in the Number group on the Home tab.
Restrict data entry to a date within range of dates.
Follow steps 1-2 above.
In the Allow box, select Date.
In the Data box, select the type of restriction that you want. For example, to allow dates after a certain day, select greater than.
Enter the start, end, or specific date to allow.
You can also enter a formula that returns a date. For example, to set a time frame between today's date and 3 days from today's date, select between in the Data box, enter =TODAY() in the Start date box, and enter =TODAY()+3 in the End date box.
Restrict data entry to a time within a time frame.
Follow steps 1-2 above.
In the Allow box, select Time.
In the Data box, select the type of restriction that you want. For example, to allow times before a certain time of day, select less than.
Enter the start, end, or specific time to allow. If you want to enter specific times, use the hh:mm time format.
For example, say you have cell E2 set up with a start time (8:00 AM), and cell F2 with an end time (5:00 PM), and you want to limit meeting times between those times then select between in the Data box, enter =E2 in the Start time box, and then enter =F2 in the End time box.
Restrict data entry to text of a specified length.
Follow steps 1-2 above.
In the Allow box, select Text Length.
In the Data box, select the type of restriction that you want. For example, to allow up to a certain number of characters, select less than or equal to.
In this case we want to limit entry to 25 characters, so select less than or equal to in the Data box and enter 25 in the Maximum box.
Calculate what is allowed based on the content of another cell.
Follow steps 1-2 above.
In the Allow box, select the type of data that you want.
In the Data box, select the type of restriction that you want.
In the box or boxes below the Data box, click the cell that you want to use to specify what is allowed.
For example, to allow entries for an account only if the result won't go over the budget in cell E1, select Allow >Whole number, Data, less than or equal to, and Maximum >= =E1.
The following examples use the Custom option where you write formulas to set your conditions. You don't need to worry about whatever the Data box shows, as that's disabled with the Custom option.
The screen shots in this article were taken in Excel 2016; but the functionality is the same in Excel for the web.
To make sure that
Enter this formula
The cell that contains a product ID (C2) always begins with the standard prefix of "ID-" and is at least 10 (greater than 9) characters long.
The cell that contains a product name (D2) only contains text.
The cell that contains someone's birthday (B6) has to be greater than the number of years set in cell B4.
All the data in the cell range A2:A10 contains unique values.
Note: You must enter the data validation formula for cell A2 first, then copy A2 to A3:A10 so that the second argument to the COUNTIF will match the current cell. That is the A2)=1 portion will change to A3)=1, A4)=1 and so on.
Note: This is an advanced topic on data validation. For an introduction to data validation, and how to validate a cell or a range, see Add data validation to a cell or a range.
You can use data validation to restrict the type of data or values that users enter into cells. For example, you might use data validation to calculate the maximum allowed value in a cell based on a value elsewhere in the workbook. In the following example, the user has typed abc , which is not an acceptable value in that cell.
When is data validation useful?
Data validation is invaluable when you want to share a workbook with others, and you want the data entered to be accurate and consistent. Among other things, you can use data validation for the following:
Restrict entries to predefined items in a list— For example, you can limit a user’s department selections to Accounting, Payroll, HR, to name a few.
Restrict numbers outside a specified range— For example, you can specify a maximum percentage input for an employee’s annual merit increase, let’s say 3%, or only allow a whole number between 1 and 100.
Restrict dates outside a certain time frame— For example, in an employee time off request, you can prevent someone from selecting a date before today’s date.
Restrict times outside a certain time frame— For example, you can specify meeting scheduling between 8:00 AM and 5:00 PM.
Limit the number of text characters— For example, you can limit the allowed text in a cell to 10 or fewer characters.
Validate data based on formulas or values in other cells— For example, you can use data validation to set a maximum limit for commissions and bonuses based on the overall projected payroll value. If users enter more than the limit amount, they see an error message.
Data Validation Input and Error Messages
You can choose to show an Input Message when the user selects the cell. Input messages are generally used to offer users guidance about the type of data that you want entered in the cell. This type of message appears near the cell. You can move this message if you want to, and it remains visible until you move to another cell or press Esc.
You set up your Input Message in the second data validation tab.
Once your users get used to your Input Message, you can uncheck the Show input message when cell is selected option.
You can also show an Error Alert that appears only after users enter invalid data.
You can choose from three types of error alerts:
Prevent users from entering invalid data in a cell.
A Stop alert message has two options: Retry or Cancel.
Warn users that the data they entered is invalid, without preventing them from entering it.
When a Warning alert message appears, users can click Yes to accept the invalid entry, No to edit the invalid entry, or Cancel to remove the invalid entry.
Inform users that the data they entered is invalid, without preventing them from entering it. This type of error alert is the most flexible.
When an Information alert message appears, users can click OK to accept the invalid value or Cancel to reject it.
Tips for working with data validation
Use these tips and tricks for working with data validation in Excel.
Note: If you want to use data validation with workbooks in Excel Services or the Excel Web App you will need to create the data validation in the Excel desktop version first.
The width of the drop-down list is determined by the width of the cell that has the data validation. You might need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list.
If you plan to protect the worksheet or workbook, protect it after you have finished specifying any validation settings. Make sure that you unlock any validated cells before you protect the worksheet. Otherwise, users will not be able to type any data in the cells. See Protect a worksheet.
If you plan to share the workbook, share it only after you have finished specifying data validation and protection settings. After you share a workbook, you won't be able to change the validation settings unless you stop sharing.
You can apply data validation to cells that already have data entered in them. However, Excel does not automatically notify you that the existing cells contain invalid data. In this scenario, you can highlight invalid data by instructing Excel to circle it on the worksheet. Once you have identified the invalid data, you can hide the circles again. If you correct an invalid entry, the circle disappears automatically.
To apply the circles, select the cells you want to evaluate and go to Data > Data Tools > Data Validation > Circle Invalid Data.
To quickly remove data validation for a cell, select it, and then go to Data > Data Tools > Data Validation > Settings > Clear All.
To find the cells on the worksheet that have data validation, on the Home tab, in the Editing group, click Find & Select, and then click Data Validation. After you have found the cells that have data validation, you can change, copy, or remove validation settings.
When creating a drop-down list, you can use the Define Name command (Formulas tab, Defined Names group) to define a name for the range that contains the list. After you create the list on another worksheet, you can hide the worksheet that contains the list and then protect the workbook so that users won't have access to the list.
If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, on the Settings tab, select the Apply these changes to all other cells with the same settings check box.
If data validation isn't working, make sure that:
Users are not copying or filling data - Data validation is designed to show messages and prevent invalid entries only when users type data directly in a cell. When data is copied or filled, the messages do not appear. To prevent users from copying and filling data by dragging and dropping cells, go to File > Options > Advanced > Editing options > clear the Enable fill handle and cell drag-and-drop check box, and then protect the worksheet.
Manual recalculation is turned off - If manual recalculation is turned on, uncalculated cells can prevent data from being validated correctly. To turn off manual recalculation, go to the Formulas tab > Calculation group > Calculation Options > click Automatic.
Cells referenced in formulas are correct - If a referenced cell changes so that a formula in a validated cell calculates an invalid result, the validation message for the cell won't appear.
An Excel table might be linked to a SharePoint site - You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.
You might currently be entering data - The Data Validation command is not available while you are entering data in a cell. To finish entering data, press Enter or ESC to quit.
The worksheet might be protected or shared - You cannot change data validation settings if your workbook is shared or protected. You’ll need to unshare or unprotect your workbook first.
How to update or remove data validation in an inherited workbook
If you inherit a workbook with data validation, you can modify or remove it unless the worksheet is protected. If it’s protected with a password that you do not know you should try to contact the previous owner to help you unprotect the worksheet, as Excel has no way to recover unknown or lost passwords. You can also copy the data to another worksheet, and then remove the data validation.
If you see a data validation alert when you try to enter or change data in a cell, and you're not clear about what you can enter, contact the owner of the workbook.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
Библиотека JavaScript Excel предоставляет API, позволяющие вашей надстройке добавлять функцию автоматической проверки данных для таблиц, столбцов, строк и других диапазонов в книге. Чтобы понять понятия и терминологию проверки данных, см. в следующих статьях о том, как пользователи добавляют проверку данных Excel пользовательского интерфейса.
Программное управление проверкой данных
Свойство Range.dataValidation , которое получает объект DataValidation, является точкой входа для программного управления проверкой данных в Excel. Существует пять свойств объекта DataValidation :
- rule — определяет, какие данные для диапазона являются допустимыми. См. статью DataValidationRule.
- errorAlert — указывает, появляется ли ошибка, если пользователь вводит недопустимые данные, и определяет текст, название и стиль оповещения, например Informational (информирование), Warning (предупреждение) и Stop (остановка). См. статью DataValidationErrorAlert.
- prompt — указывает, появляется ли подсказка, когда пользователь наводит указатель мыши на диапазон, и определяет текст подсказки. См. статью DataValidationPrompt.
- ignoreBlanks — указывает, применяется ли правило проверки данных к пустым ячейкам в диапазоне. Значение по умолчанию: true .
- type — идентификация типа проверки "только для чтения", например WholeNumber, Date, TextLength и т. д. Это свойство устанавливается неявно при задании свойства rule .
Проверка данных, добавляемая программно, ведет себя так же, как проверка данных, добавляемая вручную. В частности, обратите внимание на то, что проверка данных запускается только в том случае, если пользователь вводит значение в ячейку или копирует и вставляет ячейки из другого источника в книге и выбирает параметр вставки Значения. Если пользователь копирует ячейку и выполняет простую вставку в диапазон проверки данных, проверка не выполняется.
Создание правил проверки
Чтобы добавить проверку данных в диапазон, ваш код должен установить свойство rule объекта DataValidation в Range.dataValidation . Это приводит к получению объекта DataValidationRule, который имеет семь дополнительных свойств. Максимум одно свойство может присутствовать в любом объекте DataValidationRule . Указываемое свойство определяет тип выполняемой проверки.
Типы правил проверки Basic и DateTime
Первые три свойства DataValidationRule (т. е. типы правил проверки) в качестве своего значения принимают объект BasicDataValidation.
- wholeNumber — требует целое число в дополнение к другим проверкам, указанным объектом BasicDataValidation .
- decimal — требует десятичное число в дополнение к другим проверкам, указанным объектом BasicDataValidation .
- textLength — применяет сведения проверки объекта BasicDataValidation к длине значения ячейки.
Ниже приведен пример создания правила проверки. Обратите внимание на указанные ниже аспекты этого кода.
- operator — это бинарный оператор "GreaterThan". При использовании бинарного оператора значение, которое пользователь пытается ввести в ячейку, — это левый операнд, а значение, указанное в formula1 , — это правый операнд. Поэтому согласно этому правилу только целые числа больше 0 являются допустимыми.
- formula1 — это жестко заданное число. Если во время кодирования вы не знаете, какое значение должно быть задано, можно также использовать формулу Excel (в виде строки) для значения. Например, "= A3" и "= SUM(A4,B5)" могут также быть значениями formula1 .
Перечень других бинарных операторов см. в статье BasicDataValidation.
Существует также два тернарных оператора: "Between" и "NotBetween". Для их использования необходимо указать необязательное свойство formula2 . Значения formula1 и formula2 — это ограничивающие операнды. Значение, которое пользователь пытается ввести в ячейку, — это третий (вычисленный) операнд. Ниже приводится пример использования оператора "Между".
Следующие два свойства правила в качестве своего значения принимают объект DateTimeDataValidation.
Объект DateTimeDataValidation структурирован так же, как и BasicDataValidation : он имеет свойства formula1 , formula2 и operator и используется аналогичным образом. Различие состоит в том, что в свойствах формулы нельзя использовать число, но можно ввести строку даты и времени ISO 8606 (или формулу Excel). Ниже приведен пример, в котором определяются допустимые значения для дат в первую неделю апреля 2018 года.
Тип правила проверки для списка
Используйте свойство list в объекте DataValidationRule , чтобы указать, что единственными допустимыми значениями являются значения из конечного списка. Ниже приведен пример. Обратите внимание на указанные ниже аспекты этого кода.
- Предполагается, что существует лист с именем "Имена", а значения в диапазоне "A1:A3" являются именами.
- Свойство source определяет список допустимых значений. Строковый аргумент ссылается на диапазон с именами. Можно также назначить разделенный запятыми список, например "Регина, Сергей, Анна".
- Свойство inCellDropDown указывает, будет ли раскрывающийся элемент управления отображаться в ячейке, когда пользователь выбирает ее. Если свойству присвоено значение true , то раскрывающийся список отображается со списком значений из source .
Настраиваемый тип правила проверки
Используйте свойство custom в объекте DataValidationRule , чтобы задать настраиваемую формулу проверки. Ниже приведен пример. Обратите внимание на указанные ниже аспекты этого кода.
- Предполагается, что на листе расположена таблица с двумя столбцами A и B: Имя спортсмена и Комментарии.
- Чтобы исключить многословие в столбце Комментарии, данные, содержащие имя спортсмена, определяются недопустимыми.
- SEARCH(A2,B2) возвращает стартовую позицию строки в ячейке A2 в строку в ячейке B2. Если A2 не находится в ячейке B2, не возвращается числовое значение. ISNUMBER() возвращает логическое значение. Поэтому свойство formula указывает, что допустимые данные для столбца Комментарии — это данные, которые не содержат строку в столбце Имя спортсмена.
Создание оповещений об ошибках проверки
Вы можете создать настраиваемое оповещение об ошибке, которое отображается, если пользователь пытается ввести недопустимые данные в ячейке. Ниже приведен простой пример. Обратите внимание на указанные ниже аспекты этого кода.
Дополнительные сведения см. в статье DataValidationErrorAlert.
Создание запросов проверки
Вы можете создать пояснительную подсказку, которая появляется, когда пользователь наводит указатель мыши на ячейку, к которой была применена проверка данных, или выбирает ее. Ниже приведен пример.
Дополнительные сведения см. в статье DataValidationPrompt.
Удаление проверки данных из диапазона
Чтобы удалить проверку данных из диапазона, вызовите метод Range.dataValidation.clear().
Необязательно, чтобы очищаемый диапазон был тем же диапазоном, к которому вы применили проверку данных. Если это не один и тот же диапазон, удаляются только перекрывающиеся ячейки двух диапазонов (при их наличии).
Удаление проверки данных из диапазона также распространяется на любую проверку данных, которую пользователь добавил вручную в диапазон.
Читайте также: