Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

Проверка данных

 

Проверка данных является неплохим инструментом Excel для контроля за вносимыми на листах изменениями, не прибегая к помощи VBA. С её помощью можно ограничить ввод в ячейку, разрешив вводить только даты либо время, либо только числа. Да к тому же еще и задать диапазон дат либо предел чисел(к примеру от 1 до 10).Применений, я думаю, можно придумать массу: для корректной работы многих формул требуются корректные исходные данные. Следовательно, мы можем с помощью Проверки данных разрешить пользователю вводить только тот тип и диапазон данных, который может обработать формула, не возвращая значение ошибки. Разберем поподробней.

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

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

Первая вкладка - Параметры

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

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

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

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

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

Я уже упоминал про остальные условия и если посмотреть на картинку, то можно увидеть еще два поля: Значение, Минимум и Максимум. Так вот. Поле Значение имеет так же несколько пунктов: Между, Вне, Равно, Больше, Меньше, Больше или равно, Меньше или равно.

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

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

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

Вкладка Сообщение для ввода

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

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

Останов, Сообщение - можно ввести только разрешенные проверкой значения. Различается только вид сообщения(пример сообщения Останов приведен на рисунке выше).

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

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


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

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

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 13 коммент.
  1. Роман:

    здравствуйте! я так и не понял, какие выбрать условия, чтобы ограничить длину текста шестью знаками? на пример - 02А02 и 25К31 можно написать, а 2А2 и 2А02 нельзя?

  2. Роман, а Вы хоть попробовали? Вы пишете 6 знаков, а для примера приводите значения из 5 :-)
    Выбираете Тип данных: Длина текста.
    Поле Значение: Равно.
    в поле Длина вводите цифру 6.
    Все.

  3. Роман:

    спасибо!
    точно, про шесть знаков я описАлся, конечно пять!
    я уже сам допетрил (экспериментальным путем), но Вы ответили быстрее! :)
    мне больше подходит вариант такой - значение БОЛЬШЕ ИЛИ РАВНО МИНИМУМ 5.
    т.е. меньше пяти знаков чтоб не могли записать в ячейку.

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

  4. Михаил:

    Подскажите пожалуйста возможно ли в Excel проверять данные таким образом как я хочу.
    Пример: Я хочу для всего листа задать такое условие, что если в одну ячейку была вписана например 1Б34 то в другую ячейку я не могу задать 1Б34 и выходила бы соответственно ошибка! То есть мне нужно создать проверку по водимых символов(не по длине текста), чтобы они не повторялись !!!
    Буду очень благодарен если Вы мне подскажите, жду ответа на E-mail [удалено администратором]
    С уважением Михаил.

  5. Чтобы статья стала еще более информативной, можно дополнить статью информацией о том, что несмотря на установку на ограничение ввода в ячейку через "Данные"-Проверка данных, в нее некоторые нерадивые пользователи (изобретательные, скажем так) все же умудряются внести неправильные данные ...
    причем особо не напрягаясь... они просто копируют, а потом вставляют данные в ту ячейку, где до этого существовало условие на проверку данных :-)

  6. Анастасия:

    как сделать проверку введенных чисел в таблицу для всей таблицы, а не для каждого числа?

    • Анастасия, что значит "для каждого числа"? В статье написано: "Для начала надо выделить ячейку(или диапазон ячеек)".
      Т.е. если Вы планируете использовать одно правило проверки данных для всех ячеек - выделяете сначала все нужные ячейки, а затем вызываете окно проверки данных. Проверка данных будет назначена всем выделенным ячейкам.
      Если имелось ввиду нечто другое - поясните подробнее.
      Спасибо.

  7. Анастасия:

    В строке таблицы введены данные 1 5 3 7, под каждым числом необходимо поставить условие что "можно вводить только это число". Значений в таблице много и листов с аналогичным условием тоже. Вручную "для каждого числа" вводить условие очень долго. Думала с помощью макросов сделать проверку вводимых значений?

    • Макросом можно. Может и стандартной проверкой можно с использованием формул. Но не видя в файле что и как там у Вас записано - сказать ничего конкретного нельзя.
      Обратитесь в форум.

  8. Добрый день!
    Подскажите, а как организовать проверку значения в ячейке. Если в ячейке значение "ВЕРНО", то сообщение об ошибки не всплывает, а если "НЕВЕРНО", то не всплывает!
    Заранее спасибо

  9. Артур:

    При выборе типа данных «Другой» есть возможность сделать так, чтобы в случае не выполнения условия ячейка стала пустой?

Поделитесь своим мнением

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


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти