Excel проверка данных: Проверка данных в EXCEL. Примеры и описание

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

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

Ситуации, в которых полезно использовать проверку данных

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

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

• Ограничение использования чисел за пределами указанного диапазона

  : например, можно указать максимальный процент годового роста зарплаты сотрудника (например, 3 %) или разрешить только целое число от 1 до 100.

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

• Ограничение времени, которое находится за пределами определенного промежутка времени: например, вы можете указать, что собрания должны проводиться в период между 8:00 и 17:00.

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

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

Подсказки и сообщения об ошибках

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

Вы можете настроить сообщение на второй вкладке «Проверка данных».

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

Также можно выводить сообщение об ошибке при вводе пользователем неверных данных.

Существует три типа сообщений об ошибке.

Значок	Тип	Описание
	Остановка	Предотвращает ввод недопустимых данных в ячейку. У сообщения Остановка есть два параметра: Повторить и Отмена.
	Предупреждение	Предупреждает пользователей о том, что введенные данные недопустимы, но не препятствует их вводу. Когда появляется предупреждение, пользователь может нажать кнопку Да, чтобы принять недопустимый ввод, кнопку Нет , чтобы изменить введенные данные, или кнопку Отмена, чтобы удалить недопустимую запись.
	Информационное сообщение	Информирует пользователей о том, что введенные данные недопустимы, но не препятствует их вводу. Это самый гибкий тип сообщения. При появлении информационного сообщения пользователь может нажать кнопку ОК, чтобы принять недопустимое значение, либо нажать кнопку Отмена, чтобы отменить ввод.

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

Обратите внимание на советы и рекомендации по использованию проверки данных в Excel.

Примечание: Если вы хотите использовать проверку данных в книгах в Службы Excel или Excel Web App, сначала потребуется создать ее в классическом приложении Excel.

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

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

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

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

  Чтобы обвести данные, выделите нужные ячейки и выберите Данные > Работа с данными > Проверка данных

  > Обвести неверные данные.
  

• Чтобы быстро отменить проверку данных в ячейке, выделите ее и выберите Данные > Работа с данными > Проверка данных > Параметры > Очистить все.

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

• Для создания раскрывающегося списка можно воспользоваться командой Присвоить имя (на вкладке Формулы в группе Определенные имена), которая позволяет определить имя для диапазона, содержащего список. После создания списка на другом листе можно скрыть лист, который содержит список, а затем защитить книгу, чтобы пользователи не смогли получить доступ к этому списку.

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

• Если проверка данных не работает, проверьте указанные ниже условия.

  • Пользователи не копируют и не вставляют данные. Проверка данных предназначена для отображения сообщений и предотвращения ввода недопустимых данных только в том случае, если пользователь вводит данные непосредственно в ячейку. Если данные копируются или вставляются, сообщения не отображаются. Чтобы предотвратить копирование и вставку данных путем перетаскивания ячеек, выберите Файл > Параметры > Дополнительно > Параметры правки и снимите флажок Разрешить маркеры заполнения и перетаскивание ячеек, а затем защитите лист.

  • Выключен пересчет вручную.  Если включен пересчет вручную, невычисляемые ячейки могут повлиять на правильность проверки данных. Чтобы отключить пересчет вручную, откройте вкладку Формулы, в группе Вычисление выберите Параметры вычислений и щелкните параметр Автоматически.

  • Отсутствуют ошибки в формулах. Убедитесь, что формулы в ячейках, для которых включена проверка, не являются причиной возникновения ошибок, таких как #ССЫЛКА! или #ДЕЛ/0!. Пока ошибки не будут устранены, приложение Excel будет пропускать проверку данных.

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

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

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

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

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

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

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

У вас есть вопрос об определенной функции?

Задать вопрос на форуме сообщества, посвященном Excel

Помогите нам улучшить Excel

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

Проверка данных в Excel — Microsoft Excel для начинающих

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

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

В этом примере мы сделаем такое ограничение, при котором пользователи смогут вводить только целое число между 0 и 10.

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

Чтобы создать правило проверки данных, следуйте нашей инструкции:

1. Выделите ячейку С2.
2. На вкладке Данные (Data) нажмите кнопку Проверка данных (Data Validation).

   

   На вкладке Параметры (Settings) диалогового окна Проверка вводимых значений (Data Validation) сделайте следующее:

   • Из выпадающего списка Тип данных (Allow) выберите Целое число (Whole number).
   • Из выпадающего списка Значение (Data) выберите Между (Between).
   • Введите минимальное и максимальное значения.

     

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

Сообщения для ввода появляются, когда пользователь выделяет ячейку. Они указывают ему, что нужно вводить. Перейдите на вкладку Сообщение для ввода (Input Message) и сделайте следующее:

1. Поставьте галочку напротив Отображать подсказку, если ячейка является текущей (Show input message when cell is selected).
2. Введите заголовок.
3. Введите само сообщение.

   

Сообщение об ошибке

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

1. Поставьте галочку напротив параметра Выводить сообщение об ошибке (Show error alert after invalid data is entered).
2. Введите заголовок.
3. Введите сообщение об ошибке.

   

4. Нажмите ОК.

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

1. Выделите ячейку С2.

   

2. Попробуйте ввести число больше, чем 10.

   Результат:

   

Примечание: Чтобы удалить проверку данных из ячейки, выделите её и на вкладке Данные (Data) нажмите кнопку Проверка данных (Data Validation). Затем кликните по Очистить все (Clear All). Чтобы быстро выбрать все ячейки с проверкой данных, используйте инструмент Выделение группы ячеек (Go To Special).

Оцените качество статьи. Нам важно ваше мнение:

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

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

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

Excel позволяет задать определенные правила, по которым будет определяться, какие данные могут содержаться в ячейке. [1] Например, необходимо, чтобы число, содержащееся в ячейке, принадлежало диапазону от 1 до 12. В случае если пользователь введет неправильное значение, программа выведет соответствующее сообщение (рис. 1).

Рис. 1. Вывод сообщения о неправильном вводе данных

Скачать заметку в формате Word или pdf, примеры в формате Excel2007

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

Средство проверки вводимых данных имеет принципиальный недостаток — если пользователь скопирует ячейку, а потом вставит ее в ячейку, в которой производится проверка вводимых данных, то все определенные для проверки формулы исчезнут.

Определение критерия проверки

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

1. Выделите ячейку или диапазон ячеек.

2. Выберите вкладку Данные, область Работа с данными → Проверка данных. Excel отобразит диалоговое окно Проверка вводимых значений.

3. Щелкните на вкладке Параметры (рис. 2).

Рис. 2. Вкладка Параметры диалогового окна Проверка вводимых значений

4. Выберите одну из опций из раскрывающегося списка Тип данных. В зависимости от выбранной опции может измениться внешний вид вкладки Параметры путем добавления или удаления дополнительных элементов управления (для определения формулы нужно выбрать опцию Другой).

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

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

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

8. Щелкните ОК.

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

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

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

• Любое значение. Выбор этой опции удаляет условие проверки данных. Однако сообщение для ввода все равно будет выводиться, если не снять флажок Выводить сообщение об ошибке во вкладке Сообщение для ввода.
• Целое число. Пользователь должен ввести целое число. С помощью раскрывающегося списка Значение можно определить допустимый диапазон значений. Например, можно определить, что вводимое значение должно быть целым числом и большим или равным 100.
• Действительное. Пользователь должен ввести действительное число. Диапазон допустимых значений можно определить с помощью раскрывающегося списка Значение. Например, можно определить, что вводимое число должно быть больше или равно 0 и меньше или равно 1.
• Список. Пользователь должен выбрать значение из предложенного списка значений. Подробнее см. ниже раздел Создание раскрывающегося списка.
• Дата. Пользователь должен ввести дату. С помощью раскрывающегося списка Значение можно определить допустимый диапазон дат. Например, можно определить, что вводимая дата должна быть больше или равна 1 января 2012 года и меньше или равна 31 декабря 2012 года.
• Время. Пользователь должен ввести значение времени. С помощью раскрывающегося списка Значение можно определить допустимый диапазон значений. Например, вводимое значение времени должно быть больше чем 12:00.
• Длина текста. Ограничивается длина вводимой строки (количество символов). С помощью раскрывающегося списка Значение можно определить допустимую длину строки. Например, можно определить, что длина вводимой строки должна равняться 1 (один символ).
• Другой. Логическая формула, которая определяет правильность вводимых пользователем данных. Формулу можно занести непосредственно в поле Формула (которое появляется при выборе этого типа) или определить ссылку на ячейку с формулой. Ниже приводятся примере нескольких полезных формул.

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

• Игнорировать пустые ячейки. Если установлен флажок этой опции, то допускаются пустые записи в ячейках, для которых определена проверка данных. Этот флажок действует немного странно. Если флажок снять и определить в ячейке какое-нибудь правило проверки данных, то флажок позволит оставить ячейку пустой. Но как только вы попытаетесь в ячейке ввести что-то недопустимое, и после этого попробуете оставить ячейку пустой, то это не получится.
• Распространить изменения на другие ячейки с тем же условием. Если флажок этой опции установлен, то все внесенные изменения будут применяться также и к другим ячейкам, которые содержат исходные условия проверки данных. Если вы определили некое правило в одной или нескольких ячейках, а затем выбрали диапазон, включающий эти ячейки и некоторые другие, и кликнули в меню Проверка данных, то появится сообщение: «Выделенная область содержит ячейки без условий на значения. Распространить условия на эти ячейки?». Если нажмете «Да», то появится вкладка Параметры диалогового окна Проверка вводимых значений. Так вот сейчас флажок Распространить изменения на другие ячейки с тем же условием ставить не нужно.

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

В Excel имеется команда Данные → Работа с данными → Проверка данных → Обвести неверные данные, после выбора которой все неверные значения будут обведены красным кружком (рис. 3).

Рис. 3. Ячейки с неверными значениями (значения которых больше 100) обведены кружками

Создание раскрывающегося списка

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

Рис. 4. Список, созданный с помощью средства проверки данных

Чтобы создать такой список:

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

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

3. Во вкладке Параметры диалогового окна Проверка вводимых данных выберите тип данных Список и в поле Источник укажите диапазон, который содержит список значений (в нашем примере – $А$1:$А$12).

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

5. Сделайте другие установки в диалоговом окне Проверка вводимых данных, как описано в предыдущем разделе.

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

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

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

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

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

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

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

Тип ссылок на ячейки в формулах для проверки данных

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

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

1. Выделите диапазон В2:В10 таким образом, чтобы ячейка В2 стала активизированной.

2. Выберите команду Данные → Работа с данными → Проверка данных, чтобы открыть диалоговое окно Проверка вводимых значений.

3. Перейдите на вкладку Параметры и в списке Тип данных выберите Другой.

4. Введите следующую формулу в поле Формула (рис. 5) =ЕНЕЧЁТ(В2). В этой формуле применена функция ЕНЕЧЁТ, которая возвращает значение ИСТИНА, если ее аргумент является нечетным числом.

5. Перейдите на вкладку Сообщение об ошибке и выберите вид сообщения Останов. Также введите текст сообщения «Разрешается ввод только нечетных чисел».

6. Щелкните на кнопке ОК, чтобы закрыть диалоговое окно Проверка вводимых значений.

Рис. 5. Ввод формулы в диалоговое окно Проверка вводимых значений

Заметьте, что введенная формула содержит ссылку на верхнюю левую ячейку выделенного диапазона. Эта формула должна применяться ко всему диапазону ячеек, поэтому следует ожидать, что каждая ячейка этого диапазона содержит такую же формулу. Поскольку в формуле ссыпка на ячейку относительная, то эта формула изменяется для каждой отдельной ячейки диапазона В2:В10. Чтобы в этом удостовериться, поставьте курсор, например, в ячейку В5, и откройте диалоговое окно Проверка вводимых значений. В этом окне вы должны увидеть формулу =ЕНЕЧЁТ(В5)

В общем случае, когда вводится формула для проверки данных в диапазон ячеек, следует использовать относительную ссылку на активизированную ячейку, которой, как правило, является верхняя левая ячейка выделенного диапазона. Исключение составляют ситуации, когда надо сделать ссылку на некоторую конкретную ячейку. Например, вы хотите, чтобы в диапазон А1:В10 вводились только такие значения, которые превышают значение в ячейке С1. Для этого используется формула =А1>$С$1

В таком случае ссылка на ячейку С1 делается абсолютной и поэтому данная ссылка не меняется во всех ячейках выделенного диапазона.

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

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

Ввод только текста. Для того чтобы разрешить ввод только текста (и запретить ввод числовых значений) в ячейку или диапазон, используется следующая формула: =ЕТЕКСТ(А1). Здесь предполагается, что А1 является активизированной ячейкой выделенного диапазона.

Ввод значений, больших, чем в предыдущей ячейке. Следующая формула проверки данных позволяет ввести число в ячейку только в том случае, если оно больше, чем значение в предыдущей ячейке: =А2>А1. В формуле предполагается, что активизированной ячейкой выделенного диапазона является ячейка А2. Заметьте, что эту формулу нельзя использовать в первой строке рабочего листа.

Ввод только уникальных значений. Следующая формула проверки вводимых данных не позволит пользователю ввести в диапазоне А1:С20 повторяющиеся значения: =СЧЁТЕСЛИ($А$1:$С$20;А1)=1. Здесь предполагается, что А1 является активизированной ячейкой выделенного диапазона. Обратите внимание на то, что в качестве первого аргумента функции СЧЁТЕСЛИ ($А$1:$С$20) используется абсолютная ссылка. Вторым аргументом (А1) является относительная ссылка, которая меняется для каждой ячейки выделенного диапазона. На рис. 6 показано, как работает эта формула. Здесь сделана попытка ввести в ячейку А5 значение 2, которое уже есть в диапазоне А1:С20.

Рис. 6. Использование средства проверки данных для предотвращения ввода дублирующихся значений

Ввод текста, начинающегося с буквы А. В следующей формуле используется прием, который позволяет проводить проверку по заданному символу. В данном случае формула вернет значение ИСТИНА, если ввести в ячейку строку, которая будет начинаться с буквы А (независимо от регистра): =ЛЕВСИМВ(А1)="а". В этой формуле предполагается, что активизированной ячейкой выделенного диапазона является ячейка А1.

Ниже приведена немного модифицированная формула проверки данных. С помощью этой формулы можно организовать ввод строки, которая состоит из пяти букв и начинается с буквы А:
=СЧЁТЕСЛИ (А1; "А????") =1

Возможно, вас также заинтересует Проверка формул в Excel, или что означает зеленый треугольник

---

[1] Цитируется по книге Джон Уокенбах. Microsoft Excel 2007. Библия пользователя. – М: ООО «И.Д. Вильямс», 2008. – С. 482–489.

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

Допустим, что в таблице прайс-листа с розничными ценами на товары, в одном из столбцов указана процентная ставка НДС. Как часто бывает в рутинной работе срабатывает человеческий фактор и по ошибке для одной из категорий товаров, вместо ставки НДС 20% была введена старая ставка 19%. Эта небольшая разница в данных – 1% может создать большие проблемы для фирмы с всевозможными последствиями. Чтобы исключить ошибки созданных по причине человеческого фактора, воспользуемся встроенным инструментом Excel для проверки данных, который позволяет контролировать все что вводиться на рабочий лист.

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

Пример прайс-листа с введенными ошибками в процентных ставках НДС:

Чтобы в Excel сделать проверку вводимых данных в ячейки следует выполнить ряд последовательных действий:

1. Выделите диапазон ячеек где вводиться формула с процентной ставкой для цен с НДС и выберите инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».
2. В появившемся окне «Проверка вводимых значений» на вкладке «Параметры» из выпадающего списка «Тип данных:» выберите опцию «Список».
3. В полю ввода «Источник:» введите значения разных процентных ставок: 0%; 20%; освобождается.
4. Перейдите на закладку «Сообщение об ошибке» и заполните текстовое поле «Сообщение:» текстом который будет содержать сообщение при вводе других значений, которые отличаются от указанных значений в списке.



Закладка «Сообщение об ошибке» предоставляет пользователю возможность оформить стиль сообщения об ошибочных вводах значений. Если пользователь вводить в ячейку неправильное значение тогда будет выполнен один из 3-х параметров:

1. Останов – данный параметр разрешает вводить только правильное значение, выбранного из выпадающего списка или отменить ввод оставив пустую ячейку.
2. Предупреждение – этот параметр предупреждает об вводе ошибочного значения и предоставляет попытку отредактировать или выбрать из выпадающего списка.
3. Сообщение – параметр сообщает о неправильном значении и позволяет проигнорировать ошибку.

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

После заполнения всех параметров в окне «Проверка вводимых значений» нажмите на кнопку ОК.

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

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

Проверка данных в Excel, выпадающих список, ограничение символов и значений

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

• 01.01.2001;
• 01/01/2001;
• 1 января 2001 года и т.д.

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

Где находится?

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

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

Настройка условия проверки

Изначально требуется выбрать тип проверяемых данных, что будет являться первым условием. Всего предоставлено 8 вариантов:

• Целое число;
• Действительное число;
• Список;
• Дата;
• Время;
• Длина текста;
• Другой.

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

Самым необычным видом является выпадающий список.

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

Всплывающая подсказка ячейки Excel

Функционал проверки данных в Excel позволяет настраивать всплывающие подсказки для ячеек листа. Для этого следует перейти на вторую вкладку окна проверки вводимых значений – «Сообщение для ввода».

На изображении показан пример возможного сообщения для ячейки, в которую вносятся целые числа от 1 до 31 (настройки вкладки «Параметры»). Заголовок и сообщения указываются по Вашему усмотрению, каких-либо правил к их оформлению нет. Не забудьте установить галочку на поле «Отображать подсказку, если ячейка является текущей», иначе сообщение будет деактивировано.

Пример всплывающей подсказки в Excel:

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

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

Существует три варианта сообщений, отличающихся по поведению:

• Останов;
• Предупреждение;
• Сообщение.

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

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

Сообщение выводить ошибку в виде простой информации и дает возможность отменить последнее действие.

Заголовок и сообщение заполняются по Вашему желанию.

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

• < Назад
• Вперёд >

Похожие статьи:Новые статьи:

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

Добавить комментарий

Анализ данных Excel — проверка данных

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

Для любой ячейки на листе вы можете

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

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

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

• Вероятность
• Влияние
• Категория риска
• Источник риска
• Статус

Столбец Risk Exposure будет иметь рассчитанные значения, и вы не сможете ввести какие-либо данные. Даже в столбце S. No. установлены расчетные значения, которые корректируются, даже если вы удаляете строку.

Теперь вы узнаете, как настроить такой лист.

Подготовьте структуру для рабочего листа

Чтобы подготовить структуру для рабочего листа —

• Начните с чистого листа.
• Поместите заголовок в строке 2.
• Поместите заголовки столбцов в строке 3.
• Для заголовков столбцов Вероятность, Воздействие и Риск —
  • Щелкните правой кнопкой мыши на ячейке.
  • Нажмите на Формат ячеек из выпадающего списка.
  • В диалоговом окне «Формат ячеек» перейдите на вкладку «Выравнивание».
  • Введите 90 под ориентацией.
• Объедините и отцентрируйте ячейки в строках 3, 4 и 5 для каждого заголовка столбца.
• Формат границ для ячеек в строках 2 — 5.
• Отрегулируйте ширину строки и столбца.

Ваш рабочий лист будет выглядеть следующим образом —

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

В ячейках M5 — M13 введите следующие значения (M5 — заголовок, а M6 — M13 — значения)

Значения категории

Конечные пользователи

Покупатель

управление

График

График

Среда

Товар

проект

• Нажмите на первую ячейку под столбцом Категория риска (H6).
• Нажмите вкладку ДАННЫЕ на ленте.
• Нажмите Проверка данных в группе Инструменты данных.
• Выберите Проверка данных … из раскрывающегося списка.

Откроется диалоговое окно «Проверка данных».

• Нажмите вкладку Настройки.
• В разделе «Критерии проверки» в раскрывающемся списке « Разрешить:» выберите « Список» .

• Выберите диапазон M6: M13 в появившемся поле Source :.
• Установите флажки Пропустить пустое поле и раскрывающийся список внутри ячейки.

Установить входное сообщение для категории риска

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

Установить оповещение об ошибке для категории риска

Чтобы установить предупреждение об ошибке —

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

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

Для выбранной первой ячейки в категории риска

• Критерии проверки данных установлены
• Входное сообщение установлено
• Предупреждение об ошибке установлено

Теперь вы можете проверить свои настройки.

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

Входное сообщение отображается правильно.

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

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

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

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

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

Выберите значение из выпадающего списка. Появляется в клетке.

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

Наконец, попробуйте ввести неверную запись и проверьте предупреждение об ошибке.

Введите People в ячейку и нажмите Enter. Появится сообщение об ошибке, установленное для ячейки.

• Проверьте сообщение об ошибке.
• У вас есть возможность либо повторить попытку, либо отменить. Проверьте оба варианта.

Вы успешно установили проверку данных для ячейки.

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

Установите действительные критерии для столбца категории риска

Теперь вы готовы применить критерии проверки данных ко всем ячейкам в столбце «Категория риска».

На данный момент вам нужно запомнить две вещи —

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

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

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

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

Следуйте инструкциям ниже

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

Проверка данных установлена ​​для всех выбранных ячеек.

Нажмите на последний выбранный столбец и подтвердите.

Проверка данных для столбца Категория риска завершена.

Установить значения проверки для источника риска

В этом случае у нас есть только два значения — Внутреннее и Внешнее.

• Нажмите в первой ячейке под столбцом Источник риска (I6)
• Нажмите вкладку ДАННЫЕ на ленте
• Нажмите Проверка данных в группе Инструменты данных.
• Выберите Проверка данных … из раскрывающегося списка.

Откроется диалоговое окно «Проверка данных».

• Нажмите вкладку Настройки.
• В разделе «Критерии проверки» в раскрывающемся списке «Разрешить:» выберите параметр «Список».
• Введите Внутренний, Внешний в поле Source: появится.
• Установите флажки Пропустить пустое поле и раскрывающийся список внутри ячейки.

Установите входное сообщение для источника риска.

Установите оповещение об ошибке для источника риска.

Для выбранной первой ячейки в разделе Источник риска —

• Критерии проверки данных установлены
• Входное сообщение установлено
• Предупреждение об ошибке установлено

Теперь вы можете проверить свои настройки.

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

Входное сообщение отображается правильно.

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

• Проверьте, совпадают ли значения с введенным вами — Внутренний и Внешний.

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

Проверьте, совпадают ли значения с введенным вами — Внутренний и Внешний.

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

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

Введите Financial в ячейку и нажмите Enter. Появится сообщение об ошибке, установленное для ячейки.

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

• Установите действительные критерии для столбца источника риска

• Примените критерии проверки данных к ячейкам I6 — I15 в столбце Источник риска (т. Е. Тот же диапазон, что и в столбце Категория риска).

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

Установите действительные критерии для столбца источника риска

Примените критерии проверки данных к ячейкам I6 — I15 в столбце Источник риска (т. Е. Тот же диапазон, что и в столбце Категория риска).

Проверка данных установлена ​​для всех выбранных ячеек. Проверка данных для столбца Источник риска завершена.

Установить значения проверки для статуса

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

• Установите значения списка как Open, Closed.

• Примените критерии проверки данных к ячейкам K6 — K15 в столбце «Статус» (т. Е. Тот же диапазон, что и в столбце «Категория риска»).

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

Установите значения списка как Open, Closed.

Примените критерии проверки данных к ячейкам K6 — K15 в столбце «Статус» (т. Е. Тот же диапазон, что и в столбце «Категория риска»).

Проверка данных установлена ​​для всех выбранных ячеек. Проверка данных для статуса столбца завершена.

Установите значения проверки для вероятности

Значения показателя вероятности риска находятся в диапазоне 1-5, 1 — низкий, а 5 — высокий. Значение может быть любым целым числом от 1 до 5, включая оба.

• Нажмите в первой ячейке под столбцом Источник риска (I6).
• Нажмите вкладку ДАННЫЕ на ленте.
• Нажмите Проверка данных в группе Инструменты данных.
• Выберите Проверка данных … из раскрывающегося списка.

Откроется диалоговое окно «Проверка данных».

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

• Выберите между под данными:
• Введите 1 в поле под минимумом:
• Введите 5 в поле под Максимум:

Установить входное сообщение для вероятности

Установите предупреждение об ошибке для вероятности и нажмите ОК.

Для выбранной первой ячейки в разделе Вероятность

• Критерии проверки данных установлены.
• Входное сообщение установлено.
• Предупреждение об ошибке установлено.

Теперь вы можете проверить свои настройки.

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

Входное сообщение отображается правильно.

Введите целое число от 1 до 5 в ячейку. Появляется в клетке.

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

Введите 6 в ячейку и нажмите Enter. Появится сообщение об ошибке, установленное для ячейки.

Вы успешно установили проверку данных для ячейки.

• Установите действительные критерии для столбца вероятности.

• Примените критерии проверки данных к ячейкам E6 — E15 в столбце «Вероятность» (т. Е. Тот же диапазон, что и в столбце «Категория риска»).

Установите действительные критерии для столбца вероятности.

Примените критерии проверки данных к ячейкам E6 — E15 в столбце «Вероятность» (т. Е. Тот же диапазон, что и в столбце «Категория риска»).

Проверка данных установлена ​​для всех выбранных ячеек. Проверка данных для столбца Вероятность завершена.

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

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

Примените критерии проверки данных к ячейкам F6 — F15 в столбце «Воздействие» (т. Е. Тот же диапазон, что и в столбце «Категория риска»).

Проверка данных установлена ​​для всех выбранных ячеек. Проверка данных для столбца Воздействие завершена.

Установите риск столбца с расчетными значениями

Риск подверженности рассчитывается как произведение вероятности риска и воздействия риска.

Подверженность риску = вероятность * влияние

Введите = E6 * F6 в ячейке G6 и нажмите Enter.

0 будет отображаться в ячейке G6, поскольку E6 и F6 пусты.

Скопируйте формулу в ячейки G6 — G15. 0 будет отображаться в ячейках G6 — G15.

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

• Выберите ячейки G6-G15

• Щелкните правой кнопкой мыши и в раскрывающемся списке выберите Формат ячеек. Откроется диалоговое окно «Формат ячеек».

• Нажмите вкладку Защита.

• Проверьте опцию Заблокировано .

Выберите ячейки G6-G15

Щелкните правой кнопкой мыши и в раскрывающемся списке выберите Формат ячеек. Откроется диалоговое окно «Формат ячеек».

Нажмите вкладку Защита.

Проверьте опцию Заблокировано .

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

• Нажмите ОК.
• Затените ячейки G6-G15, чтобы указать, что они являются рассчитанными значениями.

Форматировать значения серийного номера

Вы можете оставить это пользователю, чтобы заполнить столбец S. No. Однако, если вы отформатируете значения S. No., лист будет выглядеть более презентабельно. Кроме того, он показывает, на сколько строк отформатирован лист.

Введите = row () — 5 в ячейке B6 и нажмите Enter.

1 появится в ячейке B6. Скопируйте формулу в ячейки B6-B15. Значения 1-10 появляются.

Затенить клетки B6-B15.

Заворачивать

Вы почти закончили свой проект.

• Скрыть столбец M, который содержит значения категории данных.
• Формат Границы для ячеек B6-K16.

• Щелкните правой кнопкой мыши на вкладке листа.
• Выберите Защитить лист из меню.

Откроется диалоговое окно «Защитить лист».

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

Вы защитили заблокированные ячейки в столбце Risk Exposure от ввода данных и оставили доступными для редактирования оставшиеся разблокированные ячейки. Нажмите ОК.

Откроется диалоговое окно подтверждения пароля .

• Введите пароль еще раз.
• Нажмите ОК.

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

Проверка данных в Excel – для тех, кто ценит свое время

Привет всем. Как часто Вы сталкиваетесь с таблицами общего пользования, которые заполняют несколько сотрудников? Например, Вы формируете базу данных в Эксель, и всю информацию вносит несколько человек. Ошибок не избежать, некорректные данные часто проскакивают в таблицы. Вот если бы Excel мог проверить их перед внесением и указать на ошибку…

А он может! В программу встроен мощный инструмент под названием «Проверка данных», который минимизирует ошибки внесения информации.

 

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

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

На вкладке «Параметры» нужно задать условие для проверки в поле «Тип данных». Есть 8 типов условий для проверки:

1. Любое значение – проверка данных отключена
2. Целое число – можно ввести лишь целое число с дополнительным критерием;
3. Действительное число – любое действительное число с дополнительным критерием
4. Список – выбрать значение из заранее заданного списка
5. Дата – ввести только дату, удовлетворяющую дополнительному критерию
6. Время – ввести время с дополнительным условием
7. Длина текста – ввести текст определенной длины
8. Другой – ввести значение, которое удовлетворяет записанному вручную логическому выражению

В этом окне можно установить еще две галки:

• Игнорировать пустые ячейки – не применять условие к пустым ячейкам
• Распространить изменения на другие ячейки с тем же условием – найти ячейки с аналогичными условиями и применить к ним такие же изменения

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

На вкладке «Сообщение об ошибке выберите событие, которое произойдет при введении неправильных данных. Сначала сделаем выбор в списке «Вид»:

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

После этого запишем заголовок и основной текст для окна-предупреждения.

А теперь поговорим детальнее о каждом из видов условий.

Условие на целое число в Excel

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

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

Условие на действительное значение

Действительные числа – все положительные, отрицательные числа, а так же, ноль. От предыдущего пункта это правило отличается тем, что можно вводить и дробные числа. Все остальные настройки – аналогичны описанным выше.

Список

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

Чтобы задать список, опишите его в поле источник. Это можно сделать несколькими способами:

• Записать перечень в ячейках на листе книги, а в поле «Источник» указать ссылку на этот массив
• Создать именованный массив, в поле «Источник» указать его имя
• Перечислить пункты прямо в поле «Источник» через точку с запятой

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

Дата

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

Время

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

Длина текста

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

Другой

Этот способ проверки самый гибкий. В строке «Формула» Можно задать свою логическую формулу для проверки, если ни один из предыдущих способов проверки Вас не устраивает. Например, чтобы в ячейку А1 вносили только коды, начинающиеся с символов “SUV”, и содержащие 12 символов, внесем в поле формулу: =И(ЛЕВСИМВ(A1;3)=»SUV»;ДЛСТР(A1)=12). Лишь при выполнении этого правила значение будет считаться верным.

Напомню, функция ЛЕВСИМВ возвращает заданное количество символов слева направо, а ДЛСТР – считает количество символов в ячейке.

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

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

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

Понравилась статья? Считаете ее полезной? Поделитесь с другом – кликните на кнопках социальных сетей, или просто перешлите ссылку!

Я же отправляюсь писать новый важный пост, на этот раз про создание структуры таблицы. Не пропустите, особенно если Ваши таблицы большие и сложные. До встречи!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В качестве примера давайте создадим правило, которое ограничивает пользователей вводом целого числа от 1000 до 9999:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Длина текста

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Выберите ячейку, к которой применяется правило проверки, и нажмите Ctrl + C, чтобы скопировать ее.
2. Выберите другие ячейки, которые нужно проверить. Чтобы выделить несмежные ячейки, нажмите и удерживайте клавишу Ctrl при выборе ячеек.
3. Щелкните выборку правой кнопкой мыши, выберите Специальная вставка и выберите Проверка из контекстного меню. Или нажмите сочетание клавиш Paste Special > Validation : Ctrl + Alt + V, затем N.
4. Щелкните ОК .

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

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

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

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

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

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

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

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

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

Советы:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

= МИН (1 австралийский доллар: 10 австралийских долларов)

= МАКС (1 австралийский доллар: 10 австралийских долларов)

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

Как найти неверные данные на листе

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

Чтобы найти недопустимые данные, которые попали в ваши рабочие листы до добавления проверки данных, перейдите на вкладку Data и щелкните Data Validation > Circle Invalid Data .

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

Как только вы исправите неверную запись, кружок исчезнет автоматически. Чтобы удалить все круги, перейдите на вкладку Data и щелкните Data Validation > Clear Validation Circles .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Для получения дополнительной информации см. Сравнение автоматического расчета и ручного расчета.

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

Вас также может заинтересовать:

.Руководство по проверке данных

Excel | Exceljet

Введение

Проверка данных — это функция Excel, используемая для управления тем, что пользователь может вводить в ячейку. Например, вы можете использовать проверку данных, чтобы убедиться, что значение представляет собой число от 1 до 6, убедитесь, что дата наступит в следующие 30 дней, или убедитесь, что текстовая запись содержит менее 25 символов.

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

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

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

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

Контроль достоверности данных

Проверка данных осуществляется с помощью правил, определенных в пользовательском интерфейсе Excel на вкладке «Данные» ленты.

Важное ограничение

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

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

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

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

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

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

Пользователь видит такое сообщение:

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

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

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

Когда создается правило проверки данных, доступно восемь опций для проверки ввода данных пользователем:

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

Whole Number — разрешены только целые числа. После выбора варианта целого числа становятся доступны другие параметры для дальнейшего ограничения ввода. Например, вы можете потребовать целое число от 1 до 10.

Decimal — работает как опция целых чисел, но допускает десятичные значения. Например, если параметр «Десятичный» настроен на разрешение значений от 0 до 3, допустимы такие значения, как 0,5, 2,5 и 3,1.

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

Дата — разрешены только даты. Например, вы можете указать дату между 1 января 2018 г. и 31 декабря 2021 г. или дату после 1 июня 2018 г.

Время — разрешены только времена. Например, вы можете указать время с 9:00 до 17:00 или разрешить время только после 12:00.

Длина текста — проверяет ввод по количеству символов или цифр. Например, вам может потребоваться код, содержащий 5 цифр.

Custom — проверяет ввод данных пользователем с использованием настраиваемой формулы. Другими словами, вы можете написать свою собственную формулу для проверки ввода. Пользовательские формулы значительно расширяют возможности проверки данных. Например, вы можете использовать формулу, чтобы убедиться, что значение написано в верхнем регистре, значение содержит «xyz» или дата является днем ​​недели в следующие 45 дней.

На вкладке настроек также есть два флажка:

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

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

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

Простое раскрывающееся меню

Вы можете предоставить раскрывающееся меню параметров, жестко закодировав значения в поле настроек или выбрав диапазон на листе. Например, чтобы ограничить ввод действиями «КУПИТЬ», «УДЕРЖАТЬ» или «ПРОДАТЬ», вы можете ввести эти значения, разделенные запятыми, как показано ниже:

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

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

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

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

Вы также можете использовать именованные диапазоны для указания значений.Например, с именованным диапазоном, называемым «размеры» для F3: F7, вы можете ввести имя прямо в окне, начиная со знака равенства:

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

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

Проверка данных с помощью специальной формулы

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

Если пользователь вводит значение, подобное 10, в A1, ISNUMBER возвращает TRUE, и проверка данных завершается успешно.Если они вводят значение типа «яблоко» в A1, ISNUMBER возвращает FALSE, и проверка данных не выполняется.

Чтобы включить проверку данных с помощью формулы, выберите «Пользовательский» на вкладке настроек, затем введите формулу в строке формул, как обычно, начиная со знака равенства (=).

Формулы поиска неисправностей

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

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

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

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

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

Чтобы разрешить только 5-символьные значения, начинающиеся с «z», вы можете использовать:

Эта формула возвращает ИСТИНА, только если код состоит из 5 цифр и начинается с буквы «z». Два значения в кружке возвращают FALSE с этой формулой.

Чтобы разрешить только дату в течение 30 дней после сегодняшнего дня:

Чтобы разрешить только уникальные значения:

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

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

После применения проверки данных вы можете попросить Excel обвести ранее введенные недопустимые значения.На вкладке «Данные» на ленте щелкните «Проверка данных» и выберите «Обвести недопустимые данные»:

.

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

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

Чтобы найти ячейки с примененной проверкой данных, воспользуйтесь диалоговым окном «Перейти к> Специальный». Введите сочетание клавиш Control + G, затем нажмите кнопку Special. Когда появится диалоговое окно, выберите «Проверка данных»:

.

Копировать данные проверки из одной ячейки в другую

Для копирования проверки из одной ячейки в другие ячейки.Скопируйте ячейки, которые обычно содержат нужные данные для проверки, затем используйте Специальная вставка + Проверка. Когда появится диалоговое окно, введите «n», чтобы выбрать проверку, или щелкните «Проверка» с помощью мыши.

Примечание: вы можете использовать сочетание клавиш Control + Alt + V, чтобы вызвать Специальную вставку без мыши.

Очистить проверку всех данных

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

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

.

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

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

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

Как создать настраиваемое правило проверки на основе формулы

Microsoft Excel имеет несколько встроенных правил проверки данных для чисел, дат и текста, но они охватывают только самые простые сценарии. Если вы хотите проверять ячейки по своим критериям, создайте настраиваемое правило проверки на основе формулы.Вот как:

1. Выберите одну или несколько ячеек для проверки.
2. Откройте диалоговое окно «Проверка данных». Для этого нажмите кнопку Data Validation на вкладке Data в группе Data Tools или нажмите комбинацию клавиш Alt> D> L (каждую клавишу нужно нажимать отдельно).
3. На вкладке Settings диалогового окна Data Validation выберите Custom в поле Allow и введите формулу проверки данных в поле Formula .
4. Щелкните ОК .

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

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

Примечание. Все правила проверки данных Excel, встроенные и настраиваемые, проверяют только новые данные, введенные в ячейку после создания правила. Скопированные данные не проверяются, равно как и ввод данных в ячейку перед созданием правила.Чтобы закрепить существующие записи, которые не соответствуют вашим критериям проверки данных, используйте функцию Circle Invalid Data , как показано в разделе Как найти недопустимые данные в Excel.

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

Удивительно, но ни одно из встроенных правил проверки данных Excel не обслуживает очень типичную ситуацию, когда вам нужно ограничить пользователей вводом только чисел в определенные ячейки. Но это легко сделать с помощью специальной формулы проверки данных на основе функции ЕЧИСЛО, например этой:

.

= НОМЕР (C2)

Где C2 — самая верхняя ячейка диапазона, который вы хотите проверить.

Примечание. Функция ЕЧИСЛО позволяет использовать любые числовые значения в проверенных ячейках, включая целые числа, десятичные дроби, дроби, а также даты и время, которые также являются числами в терминах Excel.

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

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

= ISTEXT (D2)

Где D2 — самая верхняя ячейка выбранного диапазона.

Разрешить текст, начинающийся с определенного символа (ов)

Если все значения в определенном диапазоне должны начинаться с определенного символа или подстроки, выполните проверку данных Excel на основе функции СЧЁТЕСЛИ с подстановочным знаком:

СЧЁТЕСЛИ ( ячейка , « текст *»)

Например, чтобы гарантировать, что все идентификаторы заказов в столбце A начинаются с префикса «AA-», «aa-», «Aa-» или «aA-» (без учета регистра), определите настраиваемое правило с этими данными. формула проверки:

= СЧЁТЕСЛИ (A2; «aa- *»)

Формула проверки с логикой ИЛИ (несколько критериев)

Если имеется 2 или более действительных префикса, добавьте несколько функций СЧЁТЕСЛИ, чтобы ваше правило проверки данных Excel работало с логикой ИЛИ:

= СЧЁТЕСЛИ (A2; «аа - *») + СЧЁТЕСЛИ (A2; «bb- *»)

Формула проверки с учетом регистра

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

ТОЧНЫЙ (ЛЕВЫЙ ( ячейка , число_символов ), текст )

Например, чтобы разрешить только те идентификаторы заказов, которые начинаются с «AA-» (ни «aa-», ни «Aa-» не допускаются), используйте следующую формулу:

= ТОЧНЫЙ (ЛЕВЫЙ (A2,3), «AA-»)

В приведенной выше формуле функция LEFT извлекает первые 3 символа из ячейки A2, а EXACT выполняет сравнение с учетом регистра с жестко запрограммированной подстрокой («AA-» в этом примере).Если две подстроки точно совпадают, формула возвращает ИСТИНА и проверка проходит; в противном случае возвращается FALSE и проверка не выполняется.

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

Чтобы разрешить записи, содержащие определенный текст в любом месте ячейки (в начале, середине или конце), используйте функцию ISNUMBER в сочетании с FIND или SEARCH в зависимости от того, хотите ли вы совпадение с учетом регистра или без учета регистра:

• Проверка без учета регистра:

  НОМЕР (ПОИСК ( текст , ячейка ))

• Проверка с учетом регистра:

  ISNUMBER (НАЙТИ ( текст , ячейка ))

В нашем примере набора данных, чтобы разрешить только записи, содержащие текст «AA» в ячейках A2: A6, используйте одну из следующих формул:

Без учета регистра: = ISNUMBER (SEARCH ("AA", A2))

С учетом регистра: = ISNUMBER (FIND ("AA", A2))

Формулы работают по следующей логике:

Вы ищите подстроку «AA» в ячейке A2, используя НАЙТИ или ПОИСК, и оба возвращают позицию первого символа в подстроке.Если текст не найден, возвращается ошибка. Для любого числового значения, возвращаемого в результате поиска, функция ЕЧИСЛО возвращает ИСТИНА, и проверка данных проходит успешно. В случае ошибки ISNUMBER возвращает FALSE, и запись в ячейку запрещена.

Проверка данных для разрешения только уникальных записей и запрета дублирования

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

= СЧЁТЕСЛИ ( диапазон , верхняя_ячейка ) <= 1

Например, чтобы убедиться, что в ячейки с A2 по A6 вводятся только уникальные идентификаторы заказов, создайте настраиваемое правило с этой формулой проверки данных:

= СЧЁТЕСЛИ ($ A $ 2: $ A $ 6; A2) <= 1

Когда вводится уникальное значение, формула возвращает ИСТИНА, и проверка завершается успешно. Если такое же значение уже существует в указанном диапазоне (счетчик больше 1), СЧЁТЕСЛИ возвращает ЛОЖЬ, и ввод не проходит проверку.

Обратите внимание, что мы блокируем диапазон с помощью абсолютных ссылок на ячейки (A $ 2: $ A $ 6) и используем относительную ссылку для верхней ячейки (A2), чтобы формула правильно настраивалась для каждой ячейки в проверенном диапазоне.

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

Формулы проверки даты и времени

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

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

Разрешить даты между двумя датами

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

И ( ячейка > = начальная_дата ), ячейка <= конечная_дата )

Где:

• ячейка - самая верхняя ячейка в проверенном диапазоне, а
• начало и конец - допустимые даты, предоставленные с помощью функции ДАТА или ссылки на ячейки, содержащие даты.

Например, чтобы разрешить только даты в июле 2017 года, используйте следующую формулу:

= И (C2> = ДАТА (2017,7,1), C2 <= ДАТА (2017,7,31))

Или введите дату начала и дату окончания в некоторые ячейки (F1 и F2 в этом примере) и укажите на эти ячейки в формуле:

= И (C2> = $ F $ 1, C2 <= $ F $ 2)

Обратите внимание, что даты границ заблокированы абсолютными ссылками на ячейки.

Разрешить только будние или выходные дни

Чтобы ограничить пользователя вводом только будних или выходных дней, настройте пользовательское правило проверки на основе функции ДЕНЬ НЕДЕЛИ.

Если для аргумента return_type установлено значение 2, WEEKDAY возвращает целое число в диапазоне от 1 (понедельник) до 7 (воскресенье). Таким образом, для будних дней (с понедельника по пятницу) результат формулы должен быть меньше 6, а для выходных (сб и вс) больше 5.

Разрешить только рабочих дней :

WEEKDAY ( ячейка , 2) <6

Разрешить только выходных :

WEEKDAY ( ячейка , 2)> 5

Например, чтобы разрешить ввод только рабочих дней в ячейки C2: C6, используйте эту формулу:

= ДЕНЬ НЕДЕЛИ (C2,2) <6

Подтвердить даты на основе сегодняшней даты

Во многих ситуациях вы можете использовать сегодняшнюю дату как дату начала допустимого диапазона дат.Чтобы получить текущую дату, используйте функцию СЕГОДНЯ, а затем добавьте к ней желаемое количество дней, чтобы вычислить дату окончания.

Например, чтобы ограничить ввод данных 6 днями с текущего момента (7 дней, включая сегодняшний), мы собираемся использовать встроенное правило даты с критериями на основе формулы:

1. Выберите Дата в разрешении
2. Выберите между в Data
3. В поле Дата начала введите = СЕГОДНЯ ()
4. В поле End date введите = TODAY () + 6
.

Аналогичным образом вы можете ограничить пользователей вводом дат до или после сегодняшней даты.Для этого выберите либо меньше, чем , либо больше, чем в поле Data , а затем введите = TODAY () в поле даты End или Start , соответственно.

Подтвердить время на основе текущего времени

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

1. В поле Разрешить выберите Время .
2. В поле Данные выберите либо меньше, чем , чтобы разрешить только времена до текущего времени, либо больше, чем , чтобы разрешить времена после текущего времени.
3. В поле End time or Start time (в зависимости от того, какие критерии вы выбрали на предыдущем шаге) введите одну из следующих формул:
   • Для проверки даты и времени на основе текущей даты и времени:
     = СЕЙЧАС ()
   • Для проверки раз на основе текущего времени:
     = ВРЕМЯ (ЧАС (СЕЙЧАС ()), МИНУТА (СЕЙЧАС ()), СЕКУНДА (СЕЙЧАС ()))

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

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

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

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

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

Для начала скопируйте формулу проверки в какую-нибудь ячейку, чтобы убедиться, что она не возвращает ошибку, например # N / A, #VALUE или # DIV / 0 !.

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

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

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

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

Вот пример в самом простом виде:

Абсолютные и относительные ссылки на ячейки в формулах проверки данных

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

Если вы создаете правило для нескольких ячеек и ваши критерии проверки зависят от конкретных ячеек , обязательно используйте абсолютные ссылки на ячейки (со знаком $, например, $ A $ 1), в противном случае ваше правило будет работать правильно только для первая ячейка.Чтобы лучше проиллюстрировать эту мысль, рассмотрим следующий пример.

Предположим, вы хотите ограничить ввод данных в ячейки от D2 до D5 целыми числами от 1 (минимальное значение) до результата деления A2 на B2. Итак, вы рассчитываете максимальное значение с помощью этой простой формулы = A2 / B2 , как показано на скриншоте ниже:

Проблема в том, что эта, казалось бы, правильная формула не будет работать для ячеек с D3 по D5, поскольку относительные ссылки меняются в зависимости от относительного положения строк и столбцов.Таким образом, для ячейки D3 формула изменится на = A3 / B3 , а для D4 она станет = A4 / B4 , что делает проверку данных неверной!

Чтобы исправить формулу, просто введите «$» перед ссылками на столбцы и строки, чтобы заблокировать их: = $ A $ 2 / $ B $ 2 . Или нажмите F4 для переключения между различными типами ссылок.

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

Как видите, «абсолютной истины» не существует, одна и та же формула может быть правильной или неправильной в зависимости от ситуации и вашей конкретной задачи.

Вот как использовать проверку данных в Excel с вашими собственными формулами. Чтобы получить больше информации, загрузите нашу книгу «Пример проверки данных Excel» и изучите настройки правил. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также может заинтересовать:

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

в Excel - Easy Excel Tutorial

Пример проверки данных | Создать правило проверки данных | Входное сообщение | Предупреждение об ошибке | Результат проверки данных

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

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

В этом примере мы ограничиваем пользователей вводом целого числа от 0 до 10.

Создание правила проверки данных

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

1. Выберите ячейку C2.

2. На вкладке «Данные» в группе «Работа с данными» щелкните «Проверка данных».

На вкладке «Настройки»:

3. В списке разрешенных щелкните Целый номер.

4. В списке данных щелкните между.

5. Введите минимальное и максимальное значения.

Входное сообщение

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

На вкладке «Входное сообщение»:

1. Установите флажок «Показывать входное сообщение, когда ячейка выбрана».

2. Введите заголовок.

3. Введите входное сообщение.

Предупреждение об ошибке

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

На вкладке «Предупреждение об ошибке»:

1. Установите флажок «Показывать предупреждение об ошибке после ввода неверных данных».

2. Введите заголовок.

3. Введите сообщение об ошибке.

4. Щелкните OK.

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

1. Выберите ячейку C2.

2. Попробуйте ввести число больше 10.

Результат:

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

.