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

      Можно настроить проверку данных в Excel, чтобы все заполняли таблицу, бланк, форму правильно. Настроить не только проверку вводимых данных в Excel, но и указать количество вводимых символов, цифр, длину текста, писать целые числа или с дробью, разрешить вводить данные в ячейку только, если будут заполнены другие ячейки, написать правильно код, данные в определенном формате (например, АО-123456), т.д. Как настроить ячейки Excel для ввода различных кодов, читайте в статье «Проверка вводимых данных в Excel».
      Внимание!
Проверка данных не работает, если в ячейку вставляют скопированные данные. Чтобы запретить вставлять скопированные данные сделаем так. Нажимаем кнопку «Office» в левом верхнем углу окна Excel, заходим в «Параметры Excel».  В разделе «Дополнительно» убираем галочку у слов «Разрешить маркеры заполнения и перетаскивание ячеек».
      Если в ячейке написана формула с ошибкой (#ССЫЛКА!  или #ДЕЛО/0!), то проверка данных работать не будет.
      Не работает «Проверка данных», если включен ручной пересчет данных таблицы.
обы отключить ручной пересчет данных, заходим на закладке «Формулы» в разделе Вычисление» нажимаем на кнопку «Параметры вычисления». Нажимаем в появившемся списке на слово «Автоматически».
      Если установлен общий доступ к таблице, то сначала его нужно убрать. Установить проверку данных в нужных ячейках, затем установить общий доступ к таблице, бланку. Как установить общий доступ к таблице, чтобы с ней могли работать на нескольких компьютерах, читайте в статье «Общий доступ к файлу Excel» тут.
      В Excel функция «Проверка данных» расположена на закладке «Данные» в разделе «Работа с данными». Нажимаем на кнопку «Проверка данных» и появляется такое окно.
Проверка данных в Excel.На закладке «Параметры» нажимаем на стрелку «Тип данных:». Рассмотрим появившейся список.
      «Любое значение» — это значит, что в ячейку можно написать всё: цифры, буквы, знаки, т.д. Эта функция пригодится, чтобы убрать из ячейки выпадающий список, т.д. – чтобы убрать любую настройку из этого списка.
елать ячейку обыкновенной.
      Не забудьте изменить формат ячейки. Например, в ячейке стояло ограничение по дате. Мы убрали ограничение по дате, поставив в этой ячейке тип данных «любое значение». Но формат «Дата» в ячейке остался. Поставим формат ячейки «Общий». О форматах ячейки смотрите в статье «Число Excel. Формат».
      «Целое число» —  устанавливает параметры вводимого в ячейку числа. Например, значение «Между» — указываем максимальное и минимальное число, числа между которыми можно написать в ячейке. Можно ограничить цифры года рождения в анкете (от 1930 до 2100, например).
      Значение «Вне» наоборот, можно написать числа, которые больше указанного максимума и меньше указанного минимума (указано от 10 до 100, значит можно написать любое число, кроме чисел в диапазоне 10-100).Проверка данных      «Действительное число» — можно ввести только число — целое или дробное. Нельзя ввести текст, т.д.
      «Список» — чтобы сделать раскрывающийся список. Подробнее об этом, смотрите в статье «Выпадающий список в Excel» здесь.
      «Дата» — можно установить, чтобы писали конкретную дату (значение — «равно»), дату в определенном диапазоне (значение — «между»), т.д.

Проверка данных      В условиях проверки данных Excel можно написать формулу, ссылаясь на другую ячейку. По значению указанной ячейке будет проверяться ячейка, в которой установили проверку. Например, в ячейку В1 установили текущую дату. А в ячейку В2 установили проверку данных по ячейке В1. Заполнили диалоговое окно проверки данных так.Проверка данныхПроверяем, введем в ячейку В2 другую дату. Вышло такое окно.Проверка данных«Время» — устанавливаем время, которое можно писать в ячейке. Например.Проверка данныхПри таком настрое проверки можно написать время такое: 12:05:00, 12:05:01, … 12:05:59, 12:06. Всё. 12:06:01 – не введется, выйдет сообщение об ошибке.
      Время в диалоговом окне проверки данных пишем через двоеточие – это формат времени.
      Можно установить время формулой.
пример, ставим «Время начала» =А1 (в ячейке А1 стоит время). А «Время конца» ставим =А1+»6:00». Это значит, что можно будет ввести значение данных в ячейку, в которой установили эту проверку данных, с времени из ячейки А1 плюс шесть часов (например, с 12:00 до 12:00+6:00=18:00).
      «Длина текста» — указываем, какое количество символов можно написать в ячейке. Например, номер счета, документа, т.д. Можно установить конкретное число символов (например,7) или диапазон символов, букв, цифр (от 7 до 9).
       «Другой» —  здесь пишем формулу для расчета условий проверки в строке «Формула» диалогового окна «Проверка вводимых значений». Например, установим в ячейке А1 такую формулу условия проверки. =ЕТЕКСТ(А1) Пояснение — в проверке данных функция «ТЕКСТ» не работает, п.э. пишем функцию ЕТЕКСТ.
Проверка данныхМы поставили в ячейке А1 условие, при котором в ячейку можно писать только текст. Цифры не напишутся.
      Еще один пример. У нас такая таблица.
Проверка данныхВ ячейку В1 установим правило заполнения ячейки, только когда будут заполнены ячейки А1 и А2. Выделяем ячейку В1. Заполним диалоговое окно «Проверка вводимых значений» так.

Проверка данныхФормулой мы написали, что, если в ячейке А1 будет стоять число больше 10, а в ячейке А2 будет стоять число меньше 20, то разрешается ввести данные в ячейку В1. Иначе, выйдет сообщение об ошибке. В ячейки А1 и А2 можно вставить формулы других расчетов данных таблицы. Например, в ячейках столбца А идет расчет прихода. А в столбце В1 введем проверкой данных контроль расхода.
      В условия проверки данных в Excel можно вводить логические формулы. 
Например, можно настроить формат ячеек в Excel. Нужно установить в ячейку А1 проверку ввода кода товара. Код всегда должен начинаться с буквы и имеет 6 цифр. Выделяем ячейку А1.  Диалоговое окно «Проверка вводимых данных» заполняем так.
Проверка данныхФормулой проверки мы говорим – данные в ячейке А1 должны начинаться и буквы АО и тире, затем количество цифр, не менее 5. Получилось так.
Проверка данных      Если нужно установить проверку данных не в одной ячейке, а в нескольких (в диапазоне, в столбце), то выделяем сразу весь нужный диапазон ячеек.
зываем диалоговое окно «Проверка данных» и указываем условия проверки, пишем формулы, т.д.
      Если нужно в проверке данных в формуле сделать ссылку на ячейку другого листа, то, сначала, нужно присвоить имя этой ячейке. Как это сделать, смотрите в статье «Присвоить имя в Excel ячейке, диапазону, формуле». Обыкновенную ссылку на ячейку другого листа проверка данных не распознает.
      Если в ячейки с установленной проверкой данных введены неверные значения, то после нажатия клавиши «Enter» выходит стандартное окно предупреждения об ошибке. Но можно настроить окно предупреждения с описанием ошибки.  Для этого в диалоговом оке «Проверка вводимых значений» переходим на закладку «Сообщение об ошибке».Проверка данных      Чтобы сразу ввели данные в ячейку верно, можно написать сообщение (подсказку) в окне «Проверка вводимых значений» в разделе «Сообщение для ввода». Например, так.
Проверка данных      Все функции можно совместить. Например, в ячейке анкеты мы установили, что нужно писать целое число. В сообщении для ввода (в подсказке) объясним, что нужно писать полное количество лет.
в сообщении об ошибке ещё раз объясняем, что они написали не полное количество лет одной цифрой.
      Чтобы найти ячейки, в которые установлена проверка данных, нажимаем на закладке «Главная» в разделе «Редактирование» на кнопку «Найти и выделить». Ячейки с проверкой данных выделятся.
      Чтобы очистить ячейку Excel от настроек проверки данных, нажимаем в диалоговом окне на кнопку «Очистить все» или устанавливаем «Тип данных» — «Любое значение».
      Как настроить проверку данных, чтобы в ячейках писать только рабочие даты, без выходных, смотрите в статье «Проверка даты в Excel».
      В Excel можно проводить анализ данных с помощью диаграмм. Как проанализировать эффективность своей деятельности, провести анализ продаж, выявить причины медленного развития бизнеса, т.д, читайте в статье «Диаграмма Парето в Excel».

www.excel-office.ru

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

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

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

Проверка на целое число

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

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

Список

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

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

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

Вставка из списка

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

Дата

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

Время

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

Длина текста

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

Другой

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

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


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

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

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

Найти ошибки

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

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

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

Поделиться, добавить в закладки или распечатать статью

officelegko.com

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

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

  2. В столбце Имя полявыберите поле, которое требуется изменить.

  3. В нижней части конструктора таблиц на вкладке Общиевыделите ячейку свойстваУсловие на значение (Validation Rule)и введите условие на значение.

-или-

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

Дополнительные сведения об использовании построителя выражений см. в статье Создание выражений.

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

  1. Выделите ячейку свойства Сообщение об ошибке (Validation Text)и введите сообщение об ошибке.

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

  1. Сохраните внесенные изменения.

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

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

  1. Повторите шаги 1-2 предыдущего раздела, чтобы открыть таблицу в режиме конструктора.

  2. На вкладке Общиевведите условие на значение на уровне записи в ячейку свойстваУсловие на значение (Validation Rule).

-или-

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

Дополнительные сведения об использовании построителя выражений см. в статье Создание выражений.

Условия на уровне записей ссылаются на несколько полей. Например, условие [ДатаНазначения]<=[ДатаЗаказа]+30ссылается на два поля в таблице, «ДатаНазначения» и «ДатаЗаказа», и гарантирует, что пользователи будут вводить даты срока исполнения заказа, отличающиеся от даты поступления заказа не более чем на 30 дней. Дополнительные примеры условий на значение на уровне записей приведены в разделеСправочные сведения об условии на значение.

  1. Сохраните изменения.

Проверка работы условий на значение

  1. В режиме конструктора откройте таблицу, содержащую нужное условие на значение.

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

  1. Нажмите кнопку Да, чтобы закрыть сообщение и начать проверку.

  2. Если будет предложено сохранить таблицу, нажмите кнопку Да.

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

studfiles.net

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

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

У нас имеется лист номенклатуры товаров магазина:

Номенклатура.

  1. Выделите столбец B и выберите инструмент «Данные»-«Проверка данных».
  2. Параметры.

  3. Заполняем значениями поля на вкладке «Параметры» как показано на рисунке:
  4. Сообщение.

  5. На вкладке «Сообщение для ввода» создаем особое примечание, в котором при помещения курсора на ячейку будет отображаться условия для ее заполнения. И жмем ОК.

Пример.

Теперь проверим. В ячейку B2 введите натуральное число, а в ячейку B3 отрицательное. Как видно в ячейке B3 действие оператора набора – заблокировано. Отображается сообщение об ошибке: «Введенное значение неверно».

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

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

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

Данным способом проверяются данные только в процессе ввода. Если данные уже введенные они будут не проверенные. Например, в столбце B нельзя ввести текст после установки условий заполнения в нем ячеек. Но заголовок в ячейке B1 «Цена» остался без предупреждения об ошибке.

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

Чтобы проверить соответствуют ли все введенные данные, определенным условиям в столбце и нет ли там ошибок, следует использовать другой инструмент: «Данные»-«Проверка данных»-«Обвести неверные данные».

Обводка.

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

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

exceltable.com

Очень часто при вводе данных в ячейки электронной таблицы мы совершаем ошибки.

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

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

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

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

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

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

Вот как это будет выглядеть в процессе работы.

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

Для этого надо предварительно ввести все допустимые значения из списка. Затем в поле «Источник» указать диапазон ячеек, где расположены допустимые списочные значения:

  • нажмите изображение таблицы, расположенной справа поля;
  • выделите диапазон ячеек;
  • повторно нажмите на изображение таблицы.

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

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

Проверка данныхР’ начало страницы

Проверка данныхР’ начало страницы

on-line-teaching.com

A. Проверка введенных значений

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

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

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

  • Целое число. В ячейку разрешен ввод только целых чисел, причем принадлежащих определенному диапазону;
  • Действительное. В ячейку разрешен ввод только чисел, в том числе с десятичной частью (нельзя ввести текст, дату ввести можно);
  • Дата. Предполагается, что в ячейку будут вводиться даты начиная от 01.01.1900 до 31.12.9999. Подробнее о формате Дата  — в статье Как Excel хранит дату и время
  • Время. Предполагается, что в ячейку с Проверкой данный этого типа будет вводиться время. Например, на рисунке ниже приведено условие, когда в ячейку разрешено вводить время принадлежащее только второй половине дня, т.е. от 12:00:00 до 23:59:59. Вместо утомительного ввода значения 12:00:00 можно использовать его числовой эквивалент 0,5. Возможность ввода чисел вместо времени следует из того, что любой дате в EXCEL сопоставлено положительное целое число, а следовательно времени (т.к. это часть суток), соответствует дробная часть числа (например, 0,5 – это полдень). Числовым эквивалентом для 23:59:59 будет 0,99999.

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

  • Длина текста. В ячейку разрешен ввод только определенного количества символов. При этом ограничении можно вводить и числа и даты, главное, чтобы количество введенных символов не противоречило ограничению по длине текста. Например, при ограничении количества символов менее 5, нельзя ввести дату позднее 13/10/2173, т.к. ей соответствует число 99999, а 14/10/2173 — это уже 100000, т.е. 6 символов. Интересно, что при ограничении, например, менее 5 символов, вы не сможете ввести в ячейку формулу =КОРЕНЬ(2), т.к. результат =1,4142135623731 (в зависимости от заданной в EXCEL точности), а вот =КОРЕНЬ(4) – сможете, ведь результат =2, а это только 1 символ.
  • Список. Наверное, самый интересный тип данных. В этом случае ввод значений в ячейку можно ограничить ранее определенным списком. Например, если в качестве источника указать через точку с запятой единицы измерения товара шт;кг;кв.м;куб.м, то ничего другого, кроме этих 4-х значений из списка вам выбрать не удастся. В источнике можно указать диапазон ячеек, содержащий заранее сформированный список или ссылку на Именованную формулу. Пример приведен в статье Выпадающий (раскрывающийся) список
  • Другой. В ячейку разрешен ввод значений удовлетворяющих более сложным критериям. Для задания критериев необходимо использовать формулу. Рассмотрим это условие подробнее.

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

Чтобы

Введите формулу

Пояснение

Ячейка B2 содержала только текст

=ЕТЕКСТ(B2)

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

Допустить ввод значения в ячейку B1 только в случае, если после ввода значение в ячейке D1 будет больше 100, в D2 меньше, чем 400

=И(D1>100;D2<400)

Проверку данных применяем к ячейке B1. При этом в ячейке D1 введена формула =B1*2, а в D2 – формула =B1*3. Хотя эта формула эквивалентна ограничению Действительное с диапазоном от 50 до 133,33, но при более сложных связях ячеек, этот прием может быть полезен

Значение в ячейке, содержащей возраст работника (С1), всегда должно быть больше числа полных лет работы (D1) плюс 18 (минимальный возраст приема на работу)

=ЕСЛИ(C1>D1+18;ИСТИНА;ЛОЖЬ)

При заполнении таблицы данными о возрасте и стаже работы можно поставить эту проверку для обеих ячеек (C1 и D1). Для этого нужно выделить сразу 2 ячейки, вызвать Проверку данных и немного модифицировать формулу =ЕСЛИ($C1>$D1+18;ИСТИНА;ЛОЖЬ)

Все данные в диапазоне ячеек A1:A20 содержали уникальные значения

=СЧЁТЕСЛИ($A$1:$A$20;A1)=1

Или

=ПОИСКПОЗ(A1;$A:$A;0)=СТРОКА(A1)

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

Значение в ячейке, содержащей имя кода продукта (B5), всегда начиналось со стандартного префикса «ID-» и имело длину не менее 10 знаков.

=И(ЛЕВСИМВ(B5;3)=»ID-«; ДЛСТР(B5)>9)

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

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

При использовании инструмента Проверка данных, предполагается, что в ячейку будут вводиться константы (123, товар1, 01.05.2010 и пр.), хотя никто не запрещает вводить и формулы. В этом случае проверяться все равно будет результат вычисления формулы. Вообще вводить формулы в ячейки с проверкой данных не советую – легко запутаться. В этом случае советую использовать Условное форматирование.

В. Отображение комментария, если ячейка является текущей.

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

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

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

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

С. Вывод подробного сообщения об ошибке.

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

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

D. Создание связанных диапазонов (списков)

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

Е. Использование в правилах ссылок на другие листы

В EXCEL 2007 в Проверке данных, как и в Условном форматировании нельзя впрямую указать ссылку на диапазоны другого листа, например, так =Лист2!$A$1. Позволяют обойти это ограничение использование Имен.

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

В Excel 2010, напротив, можно использовать правила проверки данных, ссылающиеся на значения на других листах. В Excel 2007 и Excel 97-2003 проверка данных этого типа не поддерживается и не отображается на листе. Однако все правила проверки данных остаются доступными в книге и применяются при повторном открытии книги в Excel 2010, если они не были изменены в Excel 2007 или Excel 97-2003.

F. Как срабатывает Проверка данных

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

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

Если значения вставляются через Буфер обмена (Вставить значения) или с использованием сочетания клавиш CTRL+D (копирование значения из ячейки сверху) или копируются Маркером заполнения сверху вниз, то проверка в явном виде не осуществляется. Кроме того, при копировании значений можно вообще случайно удалить правила Проверки данных, например если в ячейке источнике не определена Проверка данных, а данные из нее вставляются через Буфер обмена с использованием комбинации клавиш CTRL+V.

Поясним на примере. Предположим, к ячейке А1 применена Проверка данных с условием проверки Другой, где в поле формула введено =СТРОКА(A1)=1, т.е. для всех ячеек из первой строки условие Проверки данных будет принимать значение ИСТИНА, для других строк — ЛОЖЬ вне зависимости от содержания ячейки.

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

Теперь выделим ячейку А2 и нажмем CTRL+D. Значение из А1 скопируется в А2 вместе с условием Проверки данных. Несмотря на то, что теперь условие Проверки данных будет принимать значение ЛОЖЬ, никакого предупреждающего сообщения выведено не будет. Чтобы убедиться, что данные в ячейках соответствуют условиям определенным в Проверке данных, нужно вызвать команду меню Обвести неверные данные (Данные/ Работа с данными/ Проверка данных/ Обвести неверные данные). Ячейки с неверными данными будут обведены красными овалами. Теперь опять выделим ячеку А2 и нажмем клавишу F2 (войдем в режим Правки), затем нажмем ENTER — появится окно с сообщением, что введенное значение неверно.

Есть еще один способ обхода проверки данных. Предположим, ввод в ячейку ограничен значениями от 1 до 3. Теперь в любую другую ячейку без Проверки данных введем значение 4. Выделим эту ячейку, в Строке формул выделим значение 4 и скопируем его в Буфер обмена. Теперь выделим ячейку с Проверкой данных и нажмем CTRL+V. Значение вставилось в ячейку! Кроме того, Проверка данных осталась нетронутой в отличие от случая, когда через Буфер обмена, например, вставляется значение из WORD. Чтобы убедиться, что данные в ячейке не соответствуют условиям определенным в Проверке данных, нужно вызвать команду меню Обвести неверные данные (Данные/ Работа с данными/ Проверка данных/ Обвести неверные данные).

G. Поиск ячеек с Проверкой данных

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

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

excel2.ru

Недавно дочь обратилась с вопросом, нельзя ли в 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, или что означает зеленый треугольник

baguzin.ru

Хитрости »

Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (38)
Баги и глюки Excel (2)

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

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

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

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

В поле Тип данных(Allow) — выбирается непосредственно тип данных, который должен быть записан в ячейке. Всего доступно 8 типов: Любое значение, Целое число, Действительное, Список, Дата, Время, Длина текста, Другой(Any Value, Whole number, Decimal, List, Date, Time, Text lenght, Custom). Пункт Любое значение(Any Value) установлен по умолчанию, проверка не осуществляется. Подробно про пункт Списки(List) и как их создавать можно посмотреть и почитать в статье Выпадающие списки. Там все подробно и с нюансами расписано про списки в проверке данных, поэтому в данной статье рассмотрим оставшиеся 6 типов. Если кратко, то при выборе пункта Список в ячейке появляется выпадающий список допустимых значений. И ввести можно только то значение, которое присутствует в списке
Список в ячейке
Остальные типы данных:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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


    www.excel-vba.ru


You May Also Like

About the Author: admind

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

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.

Adblock
detector