Меню
ГлавнаяЖелезо

Как изменить набор значений ячейки. Проверка данных Excel. Определение данных, допустимых для ввода в ячейки

Для сокращения числа неточностей при вводе данных я часто использую списки, основанные на диапазоне ячеек. Рассмотрим применение этого инструмента на примере ведения табеля.

Для начала на отдельном листе (это не обязательно) разместим список допустимых значений в одном столбце или одной строке (рис. 1а); см. также Excel-файл, лист «Список».

Рис. 1. Список фамилия: (а) в произвольном порядке; (б) в алфавитном порядке.

Скачать в формате , примеры в формате

Этот перечень фамилий будет появляться в раскрывающемся списке в том порядке, в котором они расположены в этом столбце. Понятно, что для удобства поиска их лучше отсортировать по алфавиту (рис. 1б).

Присвоим нашему списку имя диапазона. Для этого выделим диапазон; в нашем случае это область А2:А21 и введем имя диапазона, как показано на рис. 2; в нашем случае – это «фамилии»:

Рис. 2. Присвоение диапазону имени

Выберем область, в которой будем вводить фамилии (см. Excel-файл, лист «Ввод»). В нашем примере – А2:А32 (рис. 3). Перейдем на вкладку Данные, группу Работа с данными, выберем команду Проверка данных:

Рис. 3. Проверка данных

В диалоговом окне «Проверка вводимых значений» перейдем на вкладку Параметры (рис. 4). В поле «Тип данных» выберем «Список». В поле «Источник» укажем: (а) область ячеек, в которых хранится список; этот вариант подходит в том случае, если список расположен на том же листе Excel; (б) имя диапазона; этот вариант может использоваться как в том случае, когда список расположен на том же листе Excel, так и в том случае, если список расположен на другом листе Excel (как в нашем случае). В обоих случаях следует убедиться, что перед ссылкой или именем стоит знак равенства (=).

Рис. 4. Выбор источника данных для списка: (а) на том же листе; (б) на любом листе

И еще о двух опциях на вкладке «Параметры»:

  • Игнорировать пустые ячейки. Если галочка установлена, Excel позволит оставить ячейку пустой. Если галочка снята, то из ячейки можно выйти только после выбора одной из фамилий списка. Особенность опции заключается в том, что перемещаться между ячейками (например клавишей Ввод или стрелками вверх / вниз) Excel позволит, а вот начать набор, потом стереть все символы и перейти в другую ячейку нельзя.
  • Список допустимых ячеек. Если галочки нет, то, когда вы установите курсор в ячейку для ввода, значок списка не появится рядом с ячейкой, и соответственно выбрать из списка не получится. Хотя все остальные свойства работы со списком будут действовать, и Excel не позволит вам ввести произвольное значение в ячейку.

Перейдем в окне «Проверка вводимых значений» на вкладку «Сообщения для ввода». Поставим галочку в поле «Отображать подсказку, если ячейка является текущей». Введем в соответствующие поля заголовок и текст сообщения (рис. 5). В последующем, когда пользователь встанет на одну из ячеек области ввода (в примере на рис. 5 – в ячейку А6), отобразится созданное нами сообщение.

Рис. 5. Установка Сообщения для ввода

В окне «Проверка вводимых значений» перейдем на вкладку «Сообщение об ошибке» (рис 6). Поставим галочку в поле «Выводить сообщение об ошибке». В поле «Вид», выберем тип сообщения. Введем заголовок и текст сообщения. Созданное нами сообщение будет появляться всякий раз, когда пользователь попытается ввести недопустимое значение.

Рис. 6. Установка Сообщения об ошибке

Допустимые типы сообщений об ошибке (рис. 7):

  • Останов – предотвращает ввод недопустимых данных; кнопка Повторить позволяет вернуться к вводу, кнопка Отмена Повторить.
  • Предупреждение – предупреждает о вводе недопустимых данных, но не запрещает такой ввод; кнопка Да позволяет принять недопустимый ввод; кнопка Нет позволяет продолжить набор (ранее набранное в ячейке значение становится доступным для редактирования); кнопка Отмена очищает ячейку и позволяет начать ввод сначала или перейти к вводу в другие ячейки; по умолчанию выбрана кнопка Нет .
  • Сообщение – уведомляет о вводе недопустимых данных; хотя и разрешает их ввести. Этот тип сообщения является самым гибким. При появлении информационного сообщения пользователь может нажать кнопку ОК , чтобы принять ввод недопустимых данных, либо нажать кнопку Отмена , чтобы отменить ввод; по умолчанию выбрана кнопка ОК.

Рис. 7. Выбор типа сообщения об ошибке

Некоторые замечания. 1. Если вы ввели в окне Сообщение вкладки Сообщение об ошибке слишком длинный текст, то окно сообщения об ошибке будет слишком широким (как на рис. 7); используйте перенос строки Shift + Enter в том месте сообщения, где вы хотите разделить строки (рис. 8).

Рис. 8. Окно сообщения об ошибке уменьшенной ширины

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

3. Максимальное число записей в раскрывающемся списке ограничено, правда, не слишком сильно:), а именно числом 32 767.

4. Если вы не хотите чтобы пользователи редактировали список проверки, поместите его на отдельном листе, после чего скройте и защитите этот лист.

Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Раскрывающиеся списки позволяют пользователям выбирать элементы из созданного вами списка.

Видео

На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel . Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T .

Примечания:

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

    На ленте откройте вкладку Данные и нажмите кнопку Проверка данных .

    Примечание: Если кнопка Проверка данных недоступна, возможно, лист защищен или является общим. Разблокируйте определенные области защищенной книги или отмените общий доступ к листу, а затем повторите шаг 3.

    На вкладке Параметры в поле Тип данных выберите пункт Список .

    Щелкните поле Источник и выделите диапазон списка. В примере данные находятся на листе "Города" в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.

    Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки .

    Установите флажок Список допустимых значений

    Откройте вкладку Подсказка по вводу .


  • Откройте вкладку Сообщение об ошибке .


  • Не знаете, какой параметр выбрать в поле Вид ?

    Работа с раскрывающимся списком

    изменяется ли ширина столбцов и высота строк при отображении всех ваших записей.

    Скачивание примеров

    Предлагаем скачать образец книги с несколькими примерами проверки данных, аналогичными примеру в этой статье. Вы можете воспользоваться ими или создать собственные сценарии проверки данных. Скачать примеры проверки данных Excel .

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

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

    См. также

    После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить,

    Функция ИЛИ возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

    Синтаксис

    ИЛИ(логическое_значение1; логическое_значение2; ...)

    Логическое_значение1, логическое_значение2, ... - от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

    Внимание!

    Аргументы должны принимать логические значения (ИСТИНА или ЛОЖЬ) или быть массивами или ссылками, содержащими логические значения. Массив - объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.

    Если заданный интервал не содержит логических значений, то функция ИЛИ возвращает значение ошибки #ЗНАЧ!.

    Можно использовать функцию ИЛИ как формулу массива, чтобы проверить, имеются ли значения в массиве. Чтобы ввести формулу массива, нажмите кнопки CTRL+SHIFT+ENTER.

    Пример

    A B
    1 Формула Описание (результат)
    2 =ИЛИ(ИСТИНА) Один аргумент имеет значение ИСТИНА (ИСТИНА)
    3 =ИЛИ(1+1=1;2+2=5) Все аргументы принимают значение ЛОЖЬ (ЛОЖЬ)
    4 =ИЛИ(ИСТИНА;ЛОЖЬ;ИСТИНА) По крайней мере один аргумент имеет значение ИСТИНА (ИСТИНА)

    Еще про Excel.

    Определение данных, допустимых для ввода в ячейки

    1. Выберите ячейку, которую требуется проверить.

    2. Выберите команду Проверка в меню Данные, а затем откройте вкладку Параметры.

    3. Определите требуемый тип проверки.

    Разрешить ввод только значений из списка

    1. В списке Тип данных выберите вариант Список.

    2. Щелкните в поле Источник и выполните одно из следующих действий:

    чтобы определить список локально, введите значения списка, разделяя их запятыми;

    чтобы использовать диапазон ячеек, которому назначено имя, введите знак равенства (=), а затем - имя диапазона;

    3. Установите флажок Список допустимых значений.

    Разрешить ввод значений, находящихся в заданных пределах

    3. Введите минимальное, максимальное или определенное разрешенное значение.

    Разрешить числа без ограничений

    1. В списке Тип данных выберите вариант Целое число или Действительное.

    2. В списке Значение выберите требуемое ограничение. Например, чтобы установить нижнюю и верхнюю границы, выберите значение между.

    3. Введите минимальное и максимальное разрешенные значения, или определите значение.

    Разрешить даты и время в рамках определенного интервала времени

    1. В поле Разрешить выберите Дата или Время.

    2. В поле Данные выберите требуемое ограничение. Например, чтобы разрешить даты после определенного дня, выберите значение больше.

    3. Введите начальную, конечную или определенную дату или время.

    Разрешить текст определенной длины

    1. Выберите команду Длина текста в окне Тип данных.

    2. В поле Данные выберите требуемое ограничение. Например, чтобы установить определенное количество знаков, выберите значение меньше или равно.

    3. Укажите минимальную, максимальную или определенную длину для текста.

    Подсчет допустимых значений на основании содержимого другой ячейки.

    1. Выберите требуемый тип данных в списке Тип данных.

    2. В поле Данные выберите требуемое ограничение.

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

    Использование формулы для подсчета допущений

    1. Выберите тип Другой в окне Тип данных.

    2. В поле Формула введите формулу для расчета логического значения (ИСТИНА для корректных данных или ЛОЖЬ для некорректных данных). Например, чтобы допустить ввод значения в ячейку для счета пикника только в случае, если ничего не финансируется за дискреционный счет (ячейка D6), и общий бюджет (D20) также меньше, чем выделенные 40000 р., можно ввести =AND(D6=0;D20

    4. Определите, может ли ячейка оставаться пустой.

    Если допускаются пустые (нулевые) значения, установите флажок Игнорировать пустые ячейки.

    Если не допускается ввод пустых (нулевых) значений, снимите флажок Игнорировать пустые ячейки.

    Примечание . Если допустимые значения заданы диапазоном ячеек с назначенным именем, в котором имеется пустая ячейка, установка флажка Игнорировать пустые ячейки позволит вводить в проверяемую ячейку любые значения. Это также верно для любых ячеек, на которые ссылаются формулы проверки: если любая ячейка, на которую указывает ссылка, пуста, то при установленном флажке Игнорировать пустые ячейки в проверяемую ячейку можно вводить любые значения.

    5. Чтобы при выделении ячейки отображалось дополнительное сообщение для ввода, перейдите на вкладку Сообщение и установите флажок Отображать подсказку, если ячейка является текущей, после чего укажите заголовок и введите текст для сообщения.

    6. Определите способ, которым Microsoft Excel будет сообщать о вводе неправильных данных.

    Инструкции

    1. Перейдите на вкладку Сообщение об ошибке и установите флажок Выводить сообщение об ошибке.

    2. Выберите один из следующих параметров для поля Вид.

    Для отображения информационного сообщения, не запрещающего ввод неправильных данных, выберите значение Сведения.

    Для отображения предупреждения, не запрещающего ввод неправильных данных, выберите значение Предупреждение.

    Чтобы запретить ввод неправильных данных, выберите значение Стоп.

    3. Укажите заголовок и введите текст для сообщения (до 225 знаков).

    Примечание . Если заголовок и текст не введены, по умолчанию вводится заголовок «Microsoft Excel» и сообщение «Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен.»

    Примечание . Применение проверки вводимых в ячейку значений не приводит к форматированию ячейки.

    Рассмотрим, как защитить отдельные ячейки в Excel . Есть несколько способов. Можно вставить в ячейку выпадающий список. Пользователь будет выбирать нужное слово из появившегося списка. Какими способами сделать раскрывающийся список, смотрите в статье "Выпадающий список в Excel" .
    Второй способ, чтобы защитить ячейки Excel от ввода неверных данных - это функциия «Проверка данных». Это нужно для правильного заполнения анкеты, заявки, для работы с таблицей, где сотрудника постоянно отвлекают, др.
    Сначала отмечаем ячейку или диапазон ячеек, куда нужно установить ограничения по вводу данных. Как выделить диапазон ячеек по его имени, смотрите в статье « Диапазон в Excel ».
    Теперь заходим на закладку «Данные» в раздел "Работа с данными", нажимаем на кнопку «Проверка данных». Выйдет окно «Проверка вводимых значений». На вкладке " Параметры " в строке «Тип данных» будет стоять тип - «Любое значение».
    Нам нужно здесь установить свое значение. Мы установили функцию «Целое число». Указали самое маленькое и самое большое числа, которые можно вводить.
    Например, в анкете написано «возраст». Нужно указать полное количество лет, а пишут дату рождения.
    Заполнили диалоговое окно так.
    Нажимаем "ОК". Теперь проверяем, вводим разные цифры, а когда ввели цифру 1234 (это больше 100) и нажали «Enter», появилось такое окно предупреждения.

    Нажимаем кнопку "Повторить" или "Отмена" и пишем правильную цифру.
    На вкладке «Сообщение для ввода» диалогового окна «Проверка вводимых значений» можно написать объяснение, что конкретно нужно здесь написать. И, при наведении курсора на эту ячейку, будет появляться это сообщение. Например.В окне «Проверка вводимых значений» на третьей вкладе "Сообщение об ошибке" можно написать текст сообщения, которое будет показано, если цифра введена не верно.

    Чтобы убрать из ячейки эту настройку, заходим в функцию «Проверка данных» и нажимаем кнопку «Очистить все», затем - «ОК».
    Можно настроить ячейки таблицы, бланка для заполнения так., чтобы не могли написать только дату рабочего дня. подробнее о такой настройки ячеек, смотрите в статье "Проверка даты в Excel" .
    Можно настроить таблицу так, чтобы нельзя было ввести одно название. слово, код, значение дважды. Об этом читайте "Запретить вводить повторяющиеся значения в Excel".
    Для контроля за сроками реализации продуктов, за сроками оплаты счетов, за разницей в сумме, т.д., можно установить функцию "Условное форматирование". Например, если сумма в ячейке будет больше 6000 рублей, то ячейка окрасится цветом.
    Смотрите, как установить эту функцию, в статье "

    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 .

    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

    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 .

    Examples of formulas in data validation

    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.

    =AND(LEFT(C2,3)="ID-",LEN(C2)>9)

    The cell that contains a product name (D2) only contains text.

    =ISTEXT(D2)

    The cell that contains someone"s birthday (B6) has to be greater than the number of years set in cell B4.

    =IF(B6