Excel ограничения на ввод информации в ячейку: Применение проверки данных к ячейкам

Сей чудесный инструмент находится: Данные(Data)—Проверка данных(Data Validation). Должно появиться окно:

Для начала надо выделить ячейку(или диапазон ячеек) для которой необходимо установить проверку. Затем идем в Данные(Data)—Проверка данных(Data Validation).

Первая вкладка — Параметры(Settings)

В поле Тип данных(Allow) — выбирается непосредственно тип данных, который должен быть записан в ячейке.

• Целое число(Whole number) — говорит само за себя. После установки такой проверки в ячейку можно будет внести только целое число. Т.е. число, не имеющее дробного остатка(9,1 например уже нельзя будет ввести). Так же нельзя будет ввести произвольный текст.
  Чаще всего подобная проверка применяется в полях для записи кол-ва штук товара и т.п. Т.е. там, где не может быть дробных значений.
• Действительное(Decimal) — тоже, что и в предыдущем пункте, но ввести можно любое число — хоть целое, хоть дробное, но невозможно будет ввести текст

Примечание: применив данные типы проверок, внести в ячейку можно только число. Занести текст Excel уже не разрешит. Однако стоит помнить, что даты и время Excel воспринимает и хранит именно как ЧИСЛОВЫЕ значения, поэтому ввод даты и времени тоже будет разрешен, если он не противоречит остальным условиям проверки(см.ниже). Но для проверки

Целое число разрешен будет ввод только даты, т.к. время Excel хранит как дробное значение(кроме 24:00:00 и 00:00:00, которые Excel воспринимает как 1 и 0 соответственно). Более подробно о восприятии Excel-ем данных читайте в статье Как Excel воспринимает данные?.

• Дата(Date) — В ячейке может содержаться только значение даты. Дата может быть записана в любом формате, допустимом в текущей локализации Windows. Здесь тот же нюанс, что и проверкой на числа, только в обратную сторону — любая дата это число, поэтому по сути можно будет ввести любое целое число, которое может быть переведено Excel-м в дату.
• Время(Time) — можно записывать значения в формате времени для текущей локализации и так же как с датой в ячейку можно будет вводить не только время, но и любые числа: целые или дробные
• Длина текста(Text lenght) — позволяет ограничить количество вводимых в ячейку символов. Данные могут являться и числом и текстом и временем. Чем угодно, только должны соответствовать остальным условиям проверки. Часто применяется для контроля ввода данных по ИНН, КПП, БИК, артикулам и т.п.

Если посмотреть на картинку в начале статьи, то можно увидеть еще три поля, помимо тех, которые я перечислил: Значение(Data), Минимум(Minimum) и Максимум(Maximum). Поле Значение содержит несколько вариантов проверки: Между, Вне, Равно, Больше, Меньше, Больше или равно, Меньше или равно(between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to) и доступно оно только при выборе Типа данных Целое число, Действительное, Дата, Время, Длина текста. Для других типов поле Значение недоступно. В зависимости от того, какой пункт выбран в поле Значение появляются дополнительные поля: Минимум и Максимум. При этом может быть только одно поле и название может быть иным. Например, для варианта Равно будет показано только одно поле и называться оно будет Значение(Value). Но здесь нет никаких подводных камней и разобраться с этими полями можно не напрягаясь и без поллитры 🙂

По сути все пункты довольно красноречивы и пояснять подробно каждый, думаю, смысла нет. Например, Между(between) — указывается интервал дат или чисел, в который должно входить условие(например целое число от 1 до 12: Минимум = 1, Максимум = 12). Если пользователь попытается ввести число за пределами указанного интервала(скажем число 0, -3 или 14), то Excel выдаст сообщение об ошибке(см.ниже). Вариант Больше или равно(greater than or equal to) позволит вносить только положительные значения больше нуля, если указать в поле Минимум значение 1.
Чуть большее внимание следует уделить варианту проверки Вне(not between). При установленном значении Вне, в ячейку можно будет внести только данные, которые не входят в диапазон, указанный в полях Минимум и Максимум. Т.е. при тех же условиях от 1 до 12 ввести можно будет и 0 и -3 и 14, но нельзя будет ввести 1 или 10.
При этом в полях для ввода значений допускается указать ссылку на ячейку. Например, в ячейке A1 записана начальная дата приема заявок, в B1 — конечная дата. И надо установить в ячейках A2:A50 проверку на интервал дат, указанных как раз в A1 и B1. Для этого выделяем ячейки A2:A50 и создаем в них проверку данных: Тип данных: Дата, Значение: Между, Начальная дата: =A1, Конечная дата: =B1. Теперь можно регулировать интервал без изменения самой проверки данных — просто изменяя значения ячеек A1 и B1.

• Другой(Custom) — на мой взгляд самый интересный и самый мощный тип проверки. Здесь нам предоставляется возможность более широко контролировать ввод данных. Для заполнения есть только одно поле — Формула(Formula). В него необходимо записать формулу и при каждом вводе значений в ячейку, Excel проверит введенное выражение на ИСТИНУ. Если быть точнее то Excel сначала вычислит формулу в этом поле ориентируясь на введенное в ячейку с проверкой данных значение, а потом проверит — возвращает ли формула значение ИСТИНА(TRUE). Если результатом будет ИСТИНА(TRUE), то введенное значение будет сохранено в ячейке, а если ЛОЖЬ(FALSE), то будет выдано окно с сообщением об ошибке:
  

  Приведу простой пример. В ячейку А1 введем число 1. Выделяем ячейку В1 — назначаем проверку данных-Другой. В поле Формула вписываем: =B1=A1. Теперь в ячейку В1 можно вписать только значение, которое полностью идентично значению в ячейке А1.

  Важно: если проверка данных в дальнейшем будет скопирована в другие ячейки, то ссылки будут смещены так, будто это простая формула. Поэтому при создании формул в проверке данных необходимо учитывать возможность смещения и при необходимости закреплять ссылки(знак доллара — $).

Вкладка Сообщение для ввода (Input Message)

Здесь указывается текст, который будет отображаться при выделении ячейки с проверкой данных:

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

Вкладка Сообщение об ошибке(Error Alert)
Здесь указывается отображать сообщение об ошибочном вводе или нет, и сам тип выводимого сообщения об ошибке.

• Выводить сообщение об ошибке(Show error alert after invalid data is entered) — в большинстве случаев необходимо установить галочку. Если галочка снята, то проверка вводимых в ячейку данных не будет осуществляться.
• Вид(Style)
  • Останов, Сообщение(Stop, Information) — можно ввести только разрешенные проверкой значения. Различается только вид сообщения(пример сообщения Останов приведен на рисунке выше).
  • Предупреждение(Warning) — в ячейку можно ввести любое значение, но при вводе значения, противоречащего проверке, появиться предупреждающее сообщение с подтверждением ввода данных.
• Заголовок(Title) — текст, который будет показан в заголовке сообщения об ошибке. Если не указан, то в заголовке будет написано Microsoft Excel.
• Сообщение(Error message) — непосредственно текст самого сообщения об ошибке. Если не указан, то будет показан текст примерно следующего содержания:
  Это значение не соответствует ограничениям по проверке данных, установленным для этой ячейки
  (This value doesn’t match the data validation restrictions defined for this cell)

Но так же необходимо помнить, что какое бы условие на проверку Вы не поставили — значение в ячейке можно удалить, нажав кнопку Del. Либо скопировав ячейку из другой книги или листа и вставив на место проверки данных — проверка исчезнет, т.к. вся ячейка будет заменена скопированной. И от этого никак не уйти — такой проверку сделали разработчики…

Как скопировать проверку данных на другие ячейки
Все очень просто — копируем ячейку с нужной проверкой данных -выделяем ячейки для создания в них такого же условия -Правая кнопка мыши —Специальная вставка(Paste Special) -в окне выбираем Условия на значения(Validation) —Ок:


Как удалить проверку данных из ячеек
Выделяем необходимые ячейки -вкладка Данные(Data)—Проверка данных(Data Validation). В поле Тип данных(Allow) устанавливаем Любое значение(Any Value) —Ок.

Маленькая хитрость использования проверки данных
Если проверка данных на ячейки была установлена уже после того, как данные были внесены, то очень неплохо понять, все ли данные соответствуют условиям проверки. Сделать это несложно. После того, как ячейкам была назначена проверка данных переходим на вкладку Данные(Data)—Проверка данных(Data Validation) -раскрываем меню и выбираем Обвести неверные данные(Circle Invalid Data). Все ячейки, данные в которых не соответствуют условиям проверки данных будут обведены красной линией:

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

Сразу после исправления неверных значений на те, которые есть в списке обводка исчезает. Если не все значения надо исправлять, а обводку тем не менее надо удалить, то после всех нужных правок просто переходим на вкладку Данные(Data) —Проверка данных(Data Validation) -раскрываем меню и выбираем Удалить обводку неверных данных(Clear Validation Circles)

---

Статья помогла? Поделись ссылкой с друзьями!     Видеоуроки

---

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика

Как установить ограничение на ввод данных в ячейку в excel

Как защитить от редактирования ячейки в Excel — запрет ввода ошибочных данных

Доброго времени!

В офисной работе часто возникает необходимость создать какой-нибудь общий документ Excel, в который бы вносили изменения сразу несколько человек (расшарив его в сети).

Разумеется, среди пользователей не все хорошо разбираются с ПК — и есть большая вероятность того, что своими не аккуратными действиями могут испортить документ. 😠

Хорошим решением подобной загвоздки может стать защита определенных ячеек от редактирования — т.е. позволить пользователям изменять документ только в разрешенном месте!

Собственно, об этом и будет сегодняшняя заметка. 👇

Примечание: все скрины в статье сделаны в MS Office 2019. Если у вас нет возможности использовать официальный продукт от Microsoft, можете воспользоваться его аналогами: https://ocomp.info/chem-zamenit-word-i-excel.html

Защищаем ячейки в Excel

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

Поэтому, сначала необходимо выделить те ячейки (строки/столбцы), которые мы хотим разрешить редактировать, после нажать по ним правой кнопкой мышки и кликнуть по меню «Формат ячеек» . См. пример ниже.

Далее в меню «Защита» нужно снять галочку рядом с пунктом «Защищаемая ячейка». Нажать OK.

После перейти в меню «Рецензирование» и активировать инструмент «Защитить лист» . Меню Excel приведено на скрине ниже.

Далее укажите пароль и отметьте галочками, что пользователи могут делать с листом (можно оставить все по умолчанию).

Собственно, на этом всё!

В заранее отмеченных нами ячейках (в шаге 1), в которых мы сняли защиту — можно вводить любые числа и значения, в других же — Excel блокирует ввод и просит снять защиту с листа. Удобно?!

Дополнительно Excel позволят сделать так, что ввести в ячейку можно будет только определенное значение. Например, число от 1 до 100! (дополнительная защита от «дурака»).

Покажу на простом примере как это делается.

Сначала нужно выделить строку/столбец (или просто ячейки), перейти в меню «Данные» и кликнуть по инструменту «Проверка данных» (как он выглядит — показано на скриншоте ниже).

Проверка данных на этапе ввода

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

Указание диапазона верных значений

После этого, когда поставите курсор в «нашу ячейку» Excel сразу же подскажет, что ввести можно от 1 до 100 (а если кто-то введет что-то отличное — выскочит ошибка 👇).

Как работает проверка

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

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

Как установить ограничение на количество символов в ячейке Excel

Компоновка строк и столбцов является удобным способом управления большими объемами информации, а особенности Excel позволяют использовать электронные таблицы в качестве инструмента для сбора данных. Редактор предоставляет некоторые функции, которые позволяют использовать его в качестве заполняемой формы. Но если нужно создать инструкции по ее заполнению, это не всегда удобно. Более простой способ убедиться в правильности вводимых данных — это установить ограничение на количество символов в ячейке Excel для всех пользователей.

Сегодня мы с вами разберемся, как это сделать. Для примера мы будем использовать настольную версию табличного процессора 2016.

Количество символов в ячейке Excel

Установленный лимит на ввод работает таким образом, что введенное значение является минимальным или не большим определенного количества символов. Это полезно, например, когда пользователь должен ввести номер телефона, почтовый индекс и т.п.

Типы данных, доступных для проверки:

• любое значение;
• целое число;
• действительное;
• список;
• дата;
• время;
• длина текста;
• другой.

Значение может быть:

• между;
• вне;
• равно;
• не равно;
• больше;
• меньше;
• больше или равно;
• меньше или равно.

Откройте новую или ранее созданную электронную таблицу и выберите нужную ячейку, столбец или строку. Перейдите на вкладку «Данные». Раскройте список по кнопке «Проверка данных» и выберите одноименную опцию.

Кнопка «Проверка данных»

На вкладке «Параметры» выберите необходимый вам тип данных из списка и его значение. В примере определим условие — допускается ввод 8 символов или меньше.

На следующей вкладке «Сообщение для ввода» устанавливаем отметку для опции отображения подсказки, если ячейка является текущей. Вводим заголовок сообщения и его текст.

Последняя вкладка «Сообщение об ошибке» будет выводить его при попытке ввода неверных данных. При этом можно выбрать вид значка в окне: остановка, предупреждение, сообщение. Сохраните внесенные изменения.

Установка опций для проверки

Теперь при вводе неверных данных в ячейку пользователь получит соответствующее сообщение. На этом все!

Вывод сообщения об ошибке

Заключение

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

Главная / Офис / Как установить ограничение на количество символов в ячейке Excel

2 комментария к “Как установить ограничение на количество символов в ячейке Excel”

Добрый день!
Помогите пожалуйста разобраться с возникшей проблемой при слиянии данных из экселя в ворд!
в ячейке находится большое количество цифр через запятую, от 200 до 400 шт, при вставке поля слияния в ворд переносится не вся информация из ячейки, а только часть! но в другой ячейке, гораздо большее количество цифр, так они отображаются в акте ворд полностью, формат ячеек одинаковый. что нужно поправить? давно мучаюсь, в ручную приходится вставлять. очень большой объём.

Как установить ограничение на ввод данных в ячейку в excel

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

Ограничить ввод ячеек целыми числами или числами в заданном диапазоне

Вы можете установить числовые значения с определенными ограничениями, выполнив следующие действия:

1. Выберите диапазон, в котором вы будете устанавливать записи числовых значений, и нажмите кнопку Данные> Проверка данных. Смотрите скриншот:

2. В открывшемся диалоговом окне «Проверка данных» перейдите на вкладку «Настройка» и:

(1) Выберите Целое число элемент из Разрешить выпадающий список. См. Первый снимок экрана ниже:

(2) Выберите один элемент из Данные раскрывающийся список в соответствии с вашими потребностями. Смотрите второй снимок экрана выше:
(3) Введите конкретные числа для ограничения в максимальная/минимальный/Длина коробка в соответствии с вашими потребностями.

3. Нажмите OK кнопку.

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

Ограничьте ввод в ячейку любым числовым значением (целыми / десятичными числами) или запретите использование определенных символов

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

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

Ограничьте ввод ячеек целыми или десятичными числами:

Запретить вводить буквы в указанном диапазоне:

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

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

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

1. Предустановите все тексты, которые другие пользователи могут вводить в ячейки, говоря, что предварительно установите список имен в A2: A10 как показано на следующем снимке экрана:

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

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

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

1. Предустановите все тексты, которые другие пользователи могут вводить в ячейки, говоря, что предварительно установите список имен в A2: A10 как показано на следующем снимке экрана:

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

3. В диалоговом окне Проверка данных перейдите к настройка вкладка и:
(1) Выберите Список элемент из Разрешить раскрывающийся список;
(2) Проверьте Раскрывающийся список внутри ячейки вариант;
(3) Укажите предустановленный источник. В данном случае это список имен в A2: A10.

4. Нажмите OK кнопку.

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

Вы также можете установить входное сообщение для ограничения ввода числовых или текстовых ячеек и установите Предупреждение об ошибке для ограничения.

Иногда вам может потребоваться ввести уникальные значения в столбец или список. Вы можете сделать это одним щелчком мыши с помощью Kutools for Excel’s Предотвратить дублирование утилита.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

Выберите столбец или список, в котором вы ограничите ввод дубликатов, а затем щелкните Kutools > Предотвратить дублирование. С этого момента, когда в указанном столбце или списке появляются повторяющиеся записи, он немедленно предупредит вас:

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Легко ограничить ввод ячеек целыми / десятичными числами только в Excel

Полнофункциональная бесплатная 30-дневная пробная версия!

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Проверка данных Excel — ограничение того, что пользователь может вводить в ячейку

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

Разделы:

Что такое проверка данных в Excel?

Как добавить проверку данных

Типы проверки данных

• Любое значение
• Целое число
• Десятичный
• Список — включает выпадающее меню
• Финики
• Время
• Длина текста
• Пользовательский — универсальная проверка

Ссылки на ячейки

Удалить проверку данных

Ввод справочных сообщений

Пользовательские сообщения об ошибках

Примечания

Что такое проверка данных в Excel?

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

Если вы попытаетесь ввести значение, не входящее в этот список, вы получите следующее сообщение об ошибке:

Как добавить проверку данных в ячейку

1. Выберите нужную ячейку.
2. Перейдите на вкладку «Данные» и нажмите кнопку «Проверка данных» (в Excel 2003 выберите «Данные» > «Проверка» в верхнем меню).
3. Выберите нужный тип проверки данных в разделе Разрешить.
4. Здесь я выбрал Список.
5. Заполните остальные отображаемые параметры, в данном случае только поле Источник.
6. Нажмите OK, и вы добавили проверку данных в ячейку в Excel!

Типы проверки данных и способы их использования

Самая сложная часть проверки данных — знать, что вводить в поле «Источник», особенно для пользовательского типа.

В следующих разделах я рассмотрю все различные типы проверки данных и покажу вам, как правильно ограничить ввод значений в ячейки. Различные типы находятся в раскрывающемся меню «Разрешить»:

Любое значение — разрешить ввод любого значения в ячейку

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

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

Целое число — разрешить ввод в ячейку только целых чисел

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

Вы можете ограничить ввод ячеек числом от 1 до 5 или от 500 до 1000 или любым другим значением, если это целое число.

Поместите наименьшее число, которое вы хотите разрешить, в поле Минимум и наибольшее число, которое вы хотите разрешить, в поле Максимум поле, подобное этому:

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

Десятичный — разрешить ввод в ячейку только чисел, включая числа с десятичными знаками

Это похоже на пример целого числа выше. за исключением того, что теперь мы можем поместить числа с десятичными знаками в ячейку.

Например, теперь мы можем разрешить пользователю вводить в ячейку такие числа, как 1,5, 3,33 или 100,23434, но по-прежнему ограничивать наименьшее и наибольшее числа, которые разрешено вводить в ячейку.

Теперь давайте введем 1,5 в поле Минимум и 5,5 в поле Максимум .

После того, как мы нажмем OK, мы сможем ввести в ячейку любое число от 1,5 до 5,5.

Это означает, что мы можем сделать что-то вроде этого:

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

Список — ограничить ввод в ячейку предопределенным списком значений (включая список раскрывающегося меню)

Это одно из самое крутое использование для проверки данных, потому что оно позволяет поместить раскрывающееся меню в ячейку в Excel. Это делает так, что пользователь может поместить значение только из списка в ячейку.

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

Вот экран, используемый для создания списка:

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

Есть много способов создать список, но каждый из них связан с тем, что вы введете в поле Source .

Вы можете ввести диапазон ячеек, содержащих список значений:

Или вы можете просто ввести список значений непосредственно в поле Источник :

Обратите внимание, что вам не нужно заключать текст с кавычками. Вам просто нужно отделить каждое значение запятой. То же самое работает и с числами:

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

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

Дата — ограничение ввода в ячейку датой

Эта функция позволяет ограничить ввод в ячейку датой, а затем ограничить даты, которые можно вводить в ячейку.

Заполните поля Дата начала и Дата окончания датой в формате, понятном Excel, или ссылкой на ячейку с датой и все:

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

Время — Ограничить ввод ячейки временем

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

Введите время в Время начала и Время окончания :

Обратите внимание, что мой Excel использует AM и PM, но 24-часовые часы также будут работать без проблем.

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

Длина текста — ограничить ввод в ячейку текстом и контролировать его длину

Это довольно просто; он позволяет вам ограничить количество символов, которые можно ввести в ячейку, и ограничивает эти символы текстовыми символами, включая большинство обычных символов, которые может использовать пользователь, включая цифры и знаки препинания.

Давайте поставим 5 для Минимум и 10 для Максимум :

Это означает, что мы должны поместить минимум 5 символов в ячейку, но не более 10 символов в ячейку.

Обратите внимание, что пробелы учитываются при подсчете символов.

Значение по умолчанию для длины текста — это ограничение количества символов, которые можно ввести, числом между двумя числами. Если вы хотите изменить это, просто измените параметр в раскрывающемся меню Данные :

Пользовательский — ограничить ввод ячеек любыми данными, которые вы хотите в Excel

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

Здесь у вас есть только одно поле для работы, поле Формула .

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

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

Это говорит о том, что Excel разрешает ввод в ячейку ТОЛЬКО в том случае, если ячейка D2 содержит число больше 1, а ячейка D3 содержит число больше 5.

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

Ссылки на ячейки при проверке данных

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

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

Удалить проверку данных

Чтобы удалить проверку данных, просто выберите нужную ячейку или ячейки и перейдите в окно проверки данных (вкладка «Данные» > «Проверка данных») и нажмите кнопку «Очистить все» в левом нижнем углу окна. окно:

Справка по вводу данных в ячейку

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

Перейдите в окно проверки данных, а затем на вкладку Входное сообщение :

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

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

Это сообщение появляется только при выборе ячейки, а не при наведении указателя мыши на ячейку:

Пользовательские сообщения об ошибках

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

Вот сообщение об ошибке по умолчанию:

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

Перейдите в окно проверки данных, а затем в окно Предупреждение об ошибке вкладка:

Здесь вы можете выбрать, какой значок вы хотите для вашей ошибки (подробнее об этом ниже):

Затем вы можете ввести заголовок и сообщение:

Теперь давайте посмотрим на наше сообщение, когда мы пытаемся ввести неправильное значение:

Обратите внимание, что на этот раз нам разрешено продолжать ввод значения, даже если это не должно быть разрешено в ячейке. Это потому, что мы выбрали Предупреждение. Сообщение об ошибке в стиле . Очень важно помнить, что при изменении параметра Style вы меняете не только значок, но и функциональность сообщения об ошибке.

Опция Информация также позволяет сохранить неверное значение после того, как оно появилось:

Единственный способ, который действительно остановит ввод неверных значений, — это стиль Остановить :

Примечания

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

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

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

Проверка данных — отличный инструмент для обеспечения целостности данных в ваших книгах, особенно когда вы отправляете их другим людям, которые должны правильно вводить данные!

Объедините это с защитой ячеек и рабочих листов, и вы сможете создавать надежные файлы Excel.

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

Как ограничить ввод данных в ячейку Excel (2 простых метода)

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

Скачать практическую рабочую тетрадь

2 простых метода ограничения ввода данных в ячейку Excel

1. Ограничьте типы ввода данных в ячейке Excel с проверкой данных

1.1 Все типы ограничений на ввод данных

1. 2 Разрешить только целое число

1.3 Разрешены только десятичные числа

1.4 Ограничить ввод данных списком

1.5 Ограничение ввода данных диапазоном дат

1.6 Ограничить ввод данных временем

1.7 Ограничьте ввод данных, чтобы разрешить только текст

1.8 Ограничьте ввод данных, чтобы разрешить только число

2. Защитите рабочий лист, чтобы ограничить ввод данных в ячейку Excel

Вывод

Статьи по Теме

Загрузить рабочую тетрадь

Вы можете скачать учебную тетрадь отсюда.

2 простых метода ограничения ввода данных в ячейку Excel

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

1. Ограничение типов ввода данных в ячейке Excel с проверкой данных

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

1.1 Все типы ограничений на ввод данных

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

ШАГИ:

• Для начала выделите ячейки ( C5:C9 ).

• Кроме того, перейдите к Данные > Проверка данных > Проверка данных

• Приведенная выше команда открывает новое диалоговое окно с именем « Проверка данных ’.
• Затем перейдите к Настройки Выберите параметр Длина текста из раскрывающегося списка Разрешить .

• Кроме того, выберите , равное , из раскрывающегося списка Данные , затем введите 0 в поле Длина .

• Затем перейдите на вкладку Оповещение об ошибке .
• Кроме того, введите имя в поле Заголовок Также введите сообщение в поле Сообщение об ошибке . Это сообщение с заголовком будет отображаться, когда пользователь попытается ввести данные в определенные ячейки.
• Теперь нажмите OK .

• После этого выберите ячейку C5 . Введите 1 в эту ячейку.
• Нажмите Введите .

• Наконец, мы получаем окно с предупреждением об ошибке, как показано ниже.

1.2 Разрешить только целое число

В этом методе мы допустим только целое число для ввода данных в ячейку Excel . Процесс этого метода аналогичен предыдущему. Для этого мы выполним следующие шаги.

ШАГИ:

• Сначала повторите первые два шага предыдущего метода.
• Далее перейдите на вкладку Настройки .
• Затем выберите опцию Целое число из раскрывающегося списка Разрешить , между из раскрывающегося списка Данные .
• Поместите значение 1 в поле Минимум и 100 в поле Максимум , которые мы хотим разрешить.

• После этого перейдите на вкладку Оповещение об ошибке .
• Введите следующие тексты в поля Заголовок и Сообщение об ошибке .

• После этого введите значение 5 в ячейку C5 .
• Нажмите Введите .

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

1.3 Допускаются только десятичные числа

Этот пример аналогичен предыдущему. Здесь мы ограничим ввод данных в ячейку Excel только десятичными числами. Давайте посмотрим, как это сделать:

ШАГОВ:

• Во-первых, откройте диалоговое окно Проверка данных , как и в предыдущем методе.
• Во-вторых, перейдите на вкладку Настройки .
• В-третьих, в раскрывающемся списке Разрешить выберите Десятичный и между из раскрывающегося списка Данные .
• Затем заполните поля Минимум и Максимум значениями 1 и 100 соответственно.
• После этого внесите изменения в окно предупреждения об ошибке, как и в предыдущем методе.
• Теперь нажмите OK .

• После этого выберите ячейку C5 . Введите 125 в эту ячейку.
• Нажмите Введите .

• Наконец, мы можем видеть окно сообщения об ошибке на следующем изображении.

1.4 Ограничить ввод данных списком

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

ШАГИ:

• Во-первых, повторите первые два шага первого метода. Откроется диалоговое окно Проверка данных .
• Далее перейдите на вкладку Настройки .
• Затем выберите опцию Список из раскрывающегося списка Разрешить .
• Кроме того, введите список значений в Source .
• Теперь нажмите OK .

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

• Затем введите 23R в ячейку C5 . Значение отсутствует в определенном списке.

• Нажмите Введите .
• Наконец, мы увидим сообщение об ошибке, подобное следующему изображению.

1.5 Ограничение ввода данных диапазоном дат

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

ШАГИ:

• Для начала выполните первые два шага, чтобы открыть Диалоговое окно проверки данных .
• Дополнительно перейдите на вкладку Настройки .
• Кроме того, выберите Дата из раскрывающегося списка Разрешить и между из раскрывающегося списка Данные .
• Кроме того, введите дату начала и дату окончания вашей задачи в поля « Дата начала » и « Конец дата » соответственно.
• Оставьте сообщения об ошибках такими же, как и в предыдущих методах.
• Теперь нажмите OK .

• После этого введите дату вне заданного диапазона.

• Нажмите Введите .
• Наконец, мы получаем окно сообщения об ошибке, подобное следующему изображению.

1.

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

ШАГИ:

• Сначала откройте Проверка данных Для этого мы выполним первые два шага первого метода.
• Далее перейдите на вкладку Настройки .
• Затем выберите Время из раскрывающегося списка Разрешить и между из раскрывающегося списка Данные .
• Дополнительно установите значение 9:00:00 в поле Время начала и 17:00:00 в поле  Время окончания .
• Установите сообщение в Предупреждение об ошибке , как и в предыдущем методе.
• Нажмите OK .

• После этого введите значение 6:00:00 PM в ячейку C5 .

• Нажмите Введите .
• В результате мы получаем следующее окно с сообщением об ошибке. Мы получаем это сообщение, так как значение 6:00:00 PM находится вне заданного нами диапазона.

1.7 Ограничить ввод данных, чтобы разрешить только текст

До сих пор все ограничения были встроены функцией проверки данных . Что, если мы хотим ограничить иначе. При этом мы должны применить Custom Data Validation . Предположим, в этом методе мы хотим ограничить ввод данных, чтобы разрешить только текстовые значения. Давайте посмотрим, как это сделать.

ШАГИ:

• Во-первых, повторите первые два шага первого метода. Он откроет Диалоговое окно проверки данных .
• Во-вторых, перейдите на вкладку Настройки .
• В-третьих, выберите параметр Пользовательский в раскрывающемся списке Разрешить .
• Кроме того, в поле Формула введите следующую формулу:

=ИСТЕКСТ(C5:C9)

• Установите сообщения на вкладке Предупреждение об ошибке , как и в предыдущих методах.
• Нажмите OK .

• Здесь функция ISTEXT определяет, является ли значение текстом, и возвращает TRUE , если это так. В противном случае возвращается False .
• Кроме того, введите числовое значение 222 в ячейку C5 .

• Нажмите Введите .
• Итак, мы получаем окно с сообщением об ошибке, подобное следующему.

1.8 Ограничить ввод данных, чтобы разрешить только число

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

ШАГИ:

• Вначале выполните первые два шага первого метода, чтобы открыть диалоговое окно Проверка данных .
• Далее перейдите на вкладку Настройки .
• Затем в раскрывающемся списке Разрешить выберите вариант Пользовательский .
• Дополнительно в поле Формула введите следующую формулу:

=ЧИСЛО(C5:C9)

• Настройте сообщения на вкладке Предупреждение об ошибке таким же образом, как и в предыдущих подходах.
• Нажмите OK .

• Здесь функция ISNUMBER используется для проверки того, является ли значение числом или нет
• Тип АСД в ячейке С5 .

• Нажмите Введите .
• Наконец, мы увидим окно с сообщением об ошибке, подобное следующему изображению.

Подробнее: Как создать форму ввода данных в Excel (шаг за шагом)

Аналогичные показания

• Заполнение электронной таблицы Excel из веб-формы
• Как создать журнал данных в Excel (2 подходящих способа)
• Создание формы ввода данных с раскрывающимся списком в Excel (2 метода)
• Как сделать заполняемую форму в Excel (5 подходящих примеров)

2. Защита рабочего листа для ограничения ввода данных в ячейку Excel

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

ШАГИ:

• Для начала щелкните значок треугольника в левом углу, чтобы выбрать весь рабочий лист.
• Кроме того, щелкните правой кнопкой мыши выбранную область. Выберите параметр Формат ячеек .

• Затем в диалоговом окне Формат ячеек перейдите к Защита
• Снимите флажок с опции Заблокировано .
• Нажмите OK .

• Кроме того, выберите только ячейки ( B5:B9 ).
• Щелкните правой кнопкой мыши на выбранной области и выберите параметр Формат ячеек .

• Затем перейдите на вкладку Защита диалогового окна Формат ячеек . Отметьте опцию Locked .
• Теперь нажмите OK .

• После этого перейдите к обзору Выберите параметр Защитить лист на ленте.

• Введите пароль в поле ввода. Мы используем 1234 .
• Нажмите OK .

• Снова появится еще одно диалоговое окно для подтверждения пароля.
• Итак, введите пароль еще раз в поле ввода.
• Нажмите OK .

• После этого выберите ячейку В5 . Попробуйте ввести любое значение в эту ячейку.

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

Подробнее: Как создать форму ввода данных в Excel VBA (с помощью простых шагов)

Заключение

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

Связанные статьи

• Автоматически вставлять записи данных метки времени в Excel (5 методов)
• Как создать форму автозаполнения в Excel (пошаговое руководство)
• Создание формы ввода данных Excel без пользовательской формы
• Как автоматизировать ввод данных в Excel (2 эффективных способа)

как добавлять, использовать и удалять

В учебнике объясняется, как выполнять проверку данных в Excel: создавать правила проверки для чисел, дат или текстовых значений, создавать списки проверки данных, копировать данные проверки в другие ячейки, находить неверные записи, исправлять и удалять проверки данных.

При настройке рабочей книги для пользователей часто может потребоваться контролировать ввод информации в определенные ячейки, чтобы убедиться, что все введенные данные точны и непротиворечивы. Среди прочего, вы можете захотеть разрешить в ячейке только определенный тип данных, например числа или даты, или ограничить числа определенным диапазоном, а текст — заданной длиной. Возможно, вы даже захотите предоставить заранее определенный список допустимых записей, чтобы исключить возможные ошибки. Проверка данных Excel позволяет делать все это во всех версиях Microsoft Excel 365, 2021, 2019., 2016, 20013, 2010 и ниже.

• Что такое проверка данных в Excel?
• Как добавить проверку данных в Excel
• Примеры проверки данных Excel
  • Целые и десятичные числа
  • Проверка даты и времени
  • Длина текста
  • Список проверки данных Excel (раскрывающийся)
  • Пользовательские правила проверки данных
• Как редактировать правила проверки Excel
• Как скопировать параметры проверки Excel в другие ячейки
• Как найти проверку данных в Excel
• Как удалить проверку данных в Excel
• Как использовать проверку данных в Excel — советы и рекомендации
• Проверка данных Excel не работает

Что такое проверка данных в Excel?

Проверка данных Excel — это функция, которая ограничивает (проверяет) пользовательский ввод на рабочем листе. Технически вы создаете правило проверки, которое контролирует, какие данные можно вводить в определенную ячейку.

Вот лишь несколько примеров того, что может сделать проверка данных Excel:

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

Например, вы можете настроить правило, которое ограничивает ввод данных 4-значными числами от 1000 до 9999. Если пользователь вводит что-то другое, Excel покажет предупреждение об ошибке, объясняющее, что он сделал неправильно:

Как выполнить проверку данных в Excel

Чтобы добавить проверку данных в Excel, выполните следующие действия.

1. Откройте диалоговое окно Проверка данных

Выберите одну или несколько ячеек для проверки, перейдите на вкладку Данные > группу Инструменты данных и нажмите кнопку

Вы также можете открыть диалоговое окно проверки данных, нажав Alt > D > L, при этом каждая клавиша нажимается отдельно.

2. Создайте правило проверки Excel

На вкладке Settings определите критерии проверки в соответствии с вашими потребностями. В критериях вы можете указать любое из следующего:

• Значения — введите числа в поля критериев, как показано на снимке экрана ниже.
• Ссылки на ячейки — создание правила на основе значения или формулы в другой ячейке.
• Формулы — позволяют выражать более сложные условия, как в этом примере.

В качестве примера создадим правило, запрещающее пользователям вводить целое число от 1000 до 9.999:

С настроенным правилом проверки либо нажмите OK , чтобы закрыть окно Проверка данных, либо переключитесь на другую вкладку, чтобы добавить входное сообщение и/или предупреждение об ошибке.

3. Добавьте сообщение ввода (необязательно)

Если вы хотите отобразить сообщение, объясняющее пользователю, какие данные разрешены в данной ячейке, откройте вкладку Сообщение ввода и выполните следующие действия:

• Сделать Убедитесь, что Показать входное сообщение, когда выбрана ячейка 9Флажок 1287 установлен.
• Введите заголовок и текст сообщения в соответствующие поля.
• Нажмите OK , чтобы закрыть диалоговое окно.

Как только пользователь выберет проверенную ячейку, появится следующее сообщение:

4. Отображение предупреждения об ошибке (необязательно)

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

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

Чтобы настроить пользовательское сообщение об ошибке, перейдите на вкладку Оповещение об ошибке и задайте следующие параметры:

• Установите флажок Показывать предупреждение об ошибке после ввода неверных данных (обычно установлен по умолчанию).
• В поле Стиль выберите нужный тип оповещения.
• Введите заголовок и текст сообщения об ошибке в соответствующие поля.
• Нажмите OK .

И теперь, если пользователь вводит неверные данные, Excel отобразит специальное предупреждение с объяснением ошибки (как показано в начале этого руководства).

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

Примеры проверки данных в Excel

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

Как вы уже знаете, критерии проверки определяются на вкладке Настройки диалогового окна Проверка данных ( вкладка Данные > Проверка данных ).

Целые и десятичные числа

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

• Равен или не равен указанному числу
• Больше или меньше указанного числа
• Между двумя номерами или не между , чтобы исключить этот диапазон номеров

Например, вот как вы создаете правило проверки Excel, которое разрешает любое целое число больше 0:

Проверка даты и времени в Excel

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

Аналогично, для проверки времени выберите Время в поле Разрешить , а затем определите необходимые критерии.

Например, чтобы разрешить только даты между Дата начала в B1 и Дата окончания в B2, примените это правило проверки даты Excel:

Чтобы проверить записи на основе сегодняшних данных и текущего времени, создайте свои собственные формулы проверки данных, как показано в этих примерах:

• Проверяйте даты на основе сегодняшней даты
• Проверка времени на основе текущего времени

Длина текста

Чтобы разрешить ввод данных определенной длины, выберите Длина текста в поле Разрешить и выберите критерии проверки в соответствии с вашей бизнес-логикой.

Например, чтобы ограничить ввод 10 символами, создайте это правило:

Примечание. Опция Длина текста ограничивает количество символов, но не тип данных, что означает, что приведенное выше правило разрешает как текст, так и числа до 10 символов или 10 цифр соответственно.

Список проверки данных Excel (раскрывающийся)

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

1. Откройте окно 9Диалоговое окно 1246 Data Validation ( вкладка Data > Data Validation ).
2. На вкладке Параметры выберите Список в поле Разрешить .
3. В поле Источник введите элементы списка проверки Excel, разделенные запятыми. Например, чтобы ограничить пользовательский ввод тремя вариантами, введите Да, Нет, Н/Д .
4. Убедитесь, что установлен флажок Раскрывающийся список в ячейке , чтобы стрелка раскрывающегося списка отображалась рядом с ячейкой.
5. Нажмите OK .

Результирующий список проверки данных Excel будет выглядеть следующим образом:

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

Другие способы создания списка проверки данных в Excel

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

• Выпадающий список проверки данных из диапазона ячеек
• Динамический список проверки данных из именованного диапазона
• Динамический список проверки данных из таблицы Excel
• Каскадный (зависимый) выпадающий список

Пользовательские правила проверки данных

В дополнение к встроенным правилам проверки данных Excel, описанным в этом руководстве, вы можете создать настраиваемые правила с собственными формулами проверки данных. Вот лишь несколько примеров:

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

Дополнительные примеры см. в разделе Пользовательские правила и формулы проверки данных.

Как изменить проверку данных в Excel

Чтобы изменить правило проверки Excel, выполните следующие действия:

1. Выберите любую из проверенных ячеек.
2. Откройте диалоговое окно Data Validation ( вкладка Data > Data Validation ).
3. Внесите необходимые изменения.
4. Выберите Примените эти изменения ко всем остальным ячейкам с теми же настройками , чтобы скопировать внесенные вами изменения во все остальные ячейки с исходными критериями проверки.
5. Нажмите OK , чтобы сохранить изменения.

Например, вы можете отредактировать список проверки данных Excel, добавив или удалив элементы из поля Источник , и применить эти изменения ко всем другим ячейкам, содержащим тот же раскрывающийся список:

Как скопировать данные Excel правило проверки для других ячеек

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

Чтобы скопировать правило проверки в Excel, выполните следующие 4 быстрых шага:

1. Выберите ячейку, к которой применяется правило проверки, и нажмите Ctrl + C, чтобы скопировать ее.
2. Выберите другие ячейки, которые вы хотите проверить. Чтобы выбрать несмежные ячейки, нажмите и удерживайте клавишу Ctrl при выборе ячеек.
3. Щелкните выделенное правой кнопкой мыши, выберите Специальная вставка и выберите параметр Проверка .

   В качестве альтернативы нажмите Специальная паста > Ярлык проверки : Ctrl + Alt + V, затем N.

4. Нажмите OK .

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

Как найти ячейки с проверкой данных в Excel

Чтобы быстро найти все проверенные ячейки на текущем листе, перейдите к Главная вкладка > Редактирование группы и нажмите Найти и выбрать > Проверка данных :

При этом будут выбраны все ячейки, к которым применены какие-либо правила проверки данных:

Как удалить проверку данных в Excel

В общем, есть два способа удалить проверку в Excel: стандартный подход, разработанный Microsoft, и метод без мыши, разработанный фанатами Excel, которые никогда не отрывают руки от клавиатуры без крайней необходимости (например, чтобы выпить чашку кофе). кофе 🙂

Метод 1: Обычный способ удаления проверки данных

Обычно, чтобы удалить проверку данных на листах Excel, выполните следующие действия:

1. Выберите ячейки с проверкой данных.
2. На вкладке Данные нажмите кнопку Проверка данных .
3. На вкладке Параметры нажмите кнопку Очистить все , а затем нажмите OK .

Советы:

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

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

Метод 2: Специальная вставка для удаления правил проверки данных

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

1. Выберите пустую ячейку без проверки данных и нажмите Ctrl + C, чтобы скопировать ее.
2. Выберите ячейки, из которых вы хотите удалить проверку данных.
3. Нажмите Ctrl + Alt + V, затем N, что является ярлыком для Специальная вставка > Проверка данных .
4. Нажмите Enter. Сделанный!

Советы по проверке данных в Excel

Теперь, когда вы знаете основы проверки данных в Excel, позвольте мне поделиться несколькими советами, которые могут значительно повысить эффективность ваших правил.

Проверка данных Excel на основе другой ячейки

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

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

Например, чтобы разрешить любое целое число, кроме числа в A1, выберите критерий не равно в поле Данные и введите =$A$1 в поле Значение :

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

Например, чтобы запретить пользователям вводить даты после сегодняшней даты, введите формулу =СЕГОДНЯ() в какую-нибудь ячейку, скажем, B1, а затем настройте правило проверки даты на основе этой ячейки:

Или вы можно ввести формулу =СЕГОДНЯ() непосредственно в поле Дата начала , что будет иметь тот же эффект.

Правила проверки на основе формул

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

Например, чтобы ограничить ввод минимальным и максимальным значениями в существующем списке чисел, скажем, A1:A10, используйте следующие формулы:

=MIN($A$1:$A$10)

=MAX($A$1:$A$10)

Обратите внимание, что мы блокируем диапазон, используя знак $ (абсолютные ссылки на ячейки), чтобы наше правило проверки Excel работало правильно для всех выбранных ячеек.

Как найти недопустимые данные на листе

Хотя Microsoft Excel позволяет применять проверку данных к ячейкам, в которых уже есть данные, он не уведомит вас, если некоторые из существующих значений не соответствуют критериям проверки.

Чтобы найти недействительные данные, которые попали в ваши рабочие листы до того, как вы добавили проверку данных, перейдите на вкладку Данные и щелкните Проверка данных > Обведите неверные данные .

Это выделит все ячейки, которые не соответствуют критериям проверки:

Как только вы исправите неверную запись, круг автоматически исчезнет. Чтобы удалить все круги, перейдите на вкладку Данные и щелкните Проверка данных > Очистить круги проверки .

Как защитить рабочий лист с помощью проверки данных

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

Как предоставить общий доступ к книге с проверкой данных

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

Проверка данных Excel не работает

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

Проверка данных не работает для скопированных данных

Проверка данных в Excel предназначена для запрета ввода неверных данных непосредственно в ячейку, но не может помешать пользователям копировать неверные данные. Хотя нет способа отключить ярлыки копирования/вставки (кроме использования VBA), вы можете, по крайней мере, предотвратить копирование данных путем перетаскивания ячеек. Для этого перейдите на Файл > Параметры > Дополнительно > Параметры редактирования и снимите флажок Включить маркер заполнения и перетаскивание ячеек .

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

Команда проверки данных недоступна (выделена серым цветом), если вы вводите или изменяете данные в ячейке. Закончив редактирование ячейки, нажмите Enter или Esc, чтобы выйти из режима редактирования, а затем выполните проверку данных.

Проверка данных не может быть применена к защищенной или общей книге

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

Неправильные формулы проверки данных

При проверке данных на основе формул в Excel необходимо проверить три важные вещи:

• Формула проверки не возвращает ошибок.
• Формула не ссылается на пустые ячейки.
• Используются соответствующие ссылки на ячейки.

Дополнительные сведения см. в разделе Пользовательское правило проверки данных не работает.

Включен ручной пересчет

Если в Excel включен режим ручного расчета, невычисленные формулы могут помешать правильной проверке данных. Чтобы снова изменить параметр вычисления Excel на автоматический, перейдите на вкладку Formulas > Calculation 9.