Главная
Теле2
Ограничение на ввод информации в ячейку excel. Как в Excel исключить ввод некорректных значений с помощью проверки данных

Ограничение на ввод информации в ячейку excel. Как в Excel исключить ввод некорректных значений с помощью проверки данных

21.12.2023

Выделите ячейки с данными, которые должны попасть в выпадающий список (например, наименованиями товаров).

Выберите в меню Вставка - Имя – Присвоить и введите имя (можно любое, но обязательно без пробелов!) для выделенного диапазона (например Товары ). Нажмите ОК .

Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню Данные – Проверка . На первой вкладке Параметры из выпадающего списка Тип данных выберите вариант Список и введите в строчку Источник знак равно и имя диапазона (т.е. =Товары ).

Нажмите ОК .

Ограничение и запрет ввода данных в ячейку

Самый интересный тип проверки – Другой . Здесь нам предоставляется возможность более широко контролировать ввод данных. Для заполнения есть только одно поле – Формула . В него необходимо записать формулу, и при каждом вводе значений в ячейку Excel проверит введенное выражение на ИСТИНУ (если быть точнее, он сопоставит введенные данные с формулой) . Например, в ячейку А1 введем число 1. Выделим ячейку В1 и назначим проверку данных – Другой . В поле Формула впишем: =B1=A1 . Теперь в ячейку В1 можно будет внести только значение, которое полностью идентично значению в ячейке А1 .

Если значение в ячейке при сопоставлении с формулой проверки является ИСТИНОЙ, то оно введется, а если ЛОЖЬ, то будет выдано окно с сообщением об ошибке:

Вкладка Сообщение для ввода .Здесь можно написать текст, который будет отображаться при активации ячейки с проверкой данных (подсказка для тех ячеек, в которых нужно контролировать ввод данных):

Вкладка Сообщение об ошибке . Здесь можно указать действия при ошибке:

Вид Останов – при попытке ввода неверного значения программа не даст его ввести и покажет сообщение об ошибке.

Вид Сообщение – программа позволит вводить неверные данные, но сообщит об этом.

Вид Предупреждение – в ячейку можно ввести любое значение, но при вводе значения, противоречащего проверке, появится предупреждающее сообщение с подтверждением ввода данных.

Приложение 2

Просмотр и ввод данных через форму

Пользователь всегда может внести новую информацию в список, просто перейдя к первой пустой строке внизу списка и введя данные с клавиатуры. Но еще проще это сделать с помощью формы.

Выделите любую ячейку в списке и выберите в меню Данные - Форма . На экране появится форма:

В строке заголовка формы Excel выводит имя листа, на котором расположен список. Чуть ниже находятся заголовки всех столбцов списка. В правом верхнем углу окна выводится информация об общем количестве строк в списке и номере строки, отображаемой в данный момент. По умолчанию отображается содержимое первой строки списка. Перемещение по списку производится полосой прокрутки в центральной части окна или кнопками Назад и Далее .

Для добавления новой записи пользователь должен щелкнуть по кнопке Добавить . Excel выведет пустую форму, в строки которой нужно ввести новые значения и после окончания ввода нажать кнопку Закрыть . Excel автоматически расширит список вниз, дописав введенные данные после последней строки.

Кнопка Критерии выведет пустую форму, с помощью которой можно искать записи по введенным критериям.

Кнопка Удалить удалит текущую запись (строку).

Excel for Office 365 Excel for Office 365 for Mac Excel for the web Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 Excel Starter 2010 More... Less

You can 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 .

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: 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 .

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

In the Input message box, type the message that you want to display.

Select the cells where you want to display your error message.

On the Data tab, click Data Validation > Data Validation .

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	Important

Restrict data entry

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.

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.

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 .

Adding other types of data validation

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.
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 .