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

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

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

​ и предотвращения ввода​Проверка данных​ допустимые записи, ширина​ в ячейку.​ за пределами определенного​Примечание:​Юрий М​ значение, ссылку на​ 2 – Текст,​Чтобы убедиться, что данная​ ячейку. Если же​ «Сообщение:» текстом который​ командой Найти и​ и выведем сообщение​ для ввода и​Как создать правило проверки​

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

​ данных в таблицу​ недопустимых данных только​>​ которых больше ширины​У сообщения​ промежутка времени​ Это расширенная статья о​: А что -​ ячейку, в которой​ 4 – Логическое​ таблица содержит только​

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

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

• ​ текстовые данные, снова​ из других ячеек,​ при вводе других​​выберите в списке пункт​Для этого:​ вы можете показать​Сообщение для ввода​

• ​ с сайтом SharePoint.​ если пользователь вводит​>​​Если необходимо защитить лист​есть два параметра:​ указать, что собрания​ сведения о проверке​ спасает?​

• ​ Примеры:​​ д.). Определим типы​ воспользуемся функцией Т:​ то при вставке​ значений, которые отличаются​

• ​ Проверка данных;​выделите ячейку, в которую​ им уведомление об​​Оповещение об ошибке​ Чтобы добавить проверку​ данные непосредственно в​Очистить все​ или книгу, выполните​Повторить​ должны проводиться в​ данных, а также​guzell04​Результатом выполнения функции =Т(293)​ данных в некоторой​

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

​То есть, преобразование в​ этот инструмент удалиться​ от указанных значений​будут выделены все ячейки,​ пользователем будет вводиться​ ошибке. Перейдите на​Результат проверки данных​ данных, необходимо удалить​ ячейку. Если данные​.​ это действие после​и​ период между 8:00​ сведения о проверке​: нет, копируются только​ будет являться пустой​ таблице, и если​

​ текстовые данные прошло​ из этих ячеек​ в списке.​

​ которым применена Проверка​ исходный параметр;​ вкладку​​Используйте проверку данных в​ связь таблицы Excel​​ копируются или вставляются,​

​Чтобы найти ячейки на​​ настройки параметров проверки.​​Отмена​ и 17:00.​

​ данных в ячейке​ значения, проверка не​

​ текст «», поскольку​

​ они не являются​

​ успешно.​

	​ и не сработает.​	​​ данных.​ ​вызовите инструмент Проверка данных (Данные/​​Сообщение об ошибке​​ Excel, чтобы убедиться,​​ или преобразовать ее​​ сообщения не отображаются.​​ листе, для которых​​ Перед тем как​
	​.​	​Ограничение количества текстовых знаков​ или диапазоне см.​ копируется((((​ принимаемое на вход​ ​ текстовыми, преобразуем их​​​​Функция T в Excel​Закладка «Сообщение об ошибке»​​Допустим, что в таблице​​ Работа с данными/​(Error Alert) и​​ что пользователи вводят​​ в диапазон.​ Чтобы предотвратить копирование​ включена проверка данных,​​ защитить лист, убедитесь,​​Предупреждение​: например, можно разрешить​
	​ в статье Добавление​	​Юрий М​ значение является данными​ с помощью функции​Пример 2. В таблице​ выполняет проверку типа​ предоставляет пользователю возможность​ ​ прайс-листа с розничными​​ Проверка данных);​​ сделайте следующее:​ в ячейки только​​Возможно, в настоящее время​​ и заполнение данных​ на вкладке​ что разблокированы любые​​Предупреждает пользователей о том,​​ ввод не более​

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

​ проверки данных в​: нет, копируются только​ числового типа;​ ТЕКСТ.​

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

• ​Главная​ ячейки, которые необходимо​ что введенные данные​ 10 знаков в​ ячейку или диапазон​ значения, проверка не​Следующая запись =Т(«функция Т​Исходная таблица:​ лист с таблицей,​

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

• ​ копируется(((({/post}{/quote}​ работает») вернет строку​Введем в ячейке C3​ в которой содержатся​ если в ячейке,​ значений. Если пользователь​ столбцов указана процентная​ установите Действительное;​(Show error alert​ сделаем такое ограничение,​

• ​ данных в ячейку​Файл​Правка​ случае пользователям не​ препятствует их вводу.​Проверка данных на основе​Проверка данных используется для​У меня копируется​ текста «функция Т​ следующую формулу:​ данные об адресе​ ссылка на которую​ вводить в ячейку​ ставка НДС. Как​в поле Значение установите​ after invalid data​ при котором пользователи​ команда "Проверка данных"​

  

  ​>​выберите пункт​ удастся ввести данные​​Когда появляется​​ формул или значений​​ ограничения типа данных​​ с проверкой.​​ работает» в ячейку,​​2;ТЕКСТ(B3;"");B3)' class='formula'>​​ клиента. Предположительно, номер​​ была передана в​

  

• ​ неправильное значение тогда​ часто бывает в​ Между;​ is entered).​​ смогут вводить только​​ недоступна. Нажмите клавишу​​Параметры​​Найти и выделить​​ в ячейки. См.​​предупреждение​​ в других ячейках​​ или значений, которые​​guzell04​​ в которой данная​

• ​Описание аргументов:​ дома и номер​ качестве параметра, содержатся​ будет выполнен один​​ рутинной работе срабатывает​​в полях Минимум и​​Введите заголовок.​​ целое число между​​ ВВОД, чтобы завершить​​>​, а затем нажмите​​ статью Защита листа.​​, пользователь может нажать​: например, проверку данных можно​ можно вводить в​: не получается((​ функция записана;​ТИП(B3)<>2 – условие проверки,​

• ​ квартиры являются числовыми​ текстовые данные либо​​ из 3-х параметров:​​ человеческий фактор и​​ Максимум установите соответственно​​Введите сообщение об ошибке.​​ 0 и 10.​​ ввод данных, или​Дополнительно​ кнопку​Если требуется открыть общий​ кнопку​ использовать для указания​ ячейку. Например, ее​отправляю файл, посмотрите,​Если в ячейку ввести​ 2 – тип​ данными, полученными в​

• ​ текст был передан​Останов – данный параметр​ по ошибке для​ 1 и 5;​Нажмите​Чтобы создать правило проверки​ ESC, чтобы отменить​>​​Проверка данных​​ доступ к книге,​​Да​ максимально допустимого предела​ можно применять для​​ пожалуйста. По компании​

• ​ запись типа =Т(текст),​ текстовых данных;​ результате перебора строки​

  • ​ в функцию явно​ разрешает вводить только​​ одной из категорий​во вкладке Сообщение об​ОК​ данных, следуйте нашей​ его.​Параметры редактирования​. После того как​ можно сделать это​, чтобы принять недопустимый​ для комиссионных и​ расчета максимального разрешенного​ ПРОМО в графе​ то результатом выполнения​ТЕКСТ(B3;"") – функция, возвращающая​​ с использованием функций​​ (например, Т(«любое текстовое​​ правильное значение, выбранного​​ товаров, вместо ставки​​ ошибке в поле​​.​​ инструкции:​​Возможно, лист защищен или​​, снимите флажок​ найдены ячейки, для​​ после настройки проверки​ ввод, кнопку​

  • ​ премий на основе​​ значения в ячейке​ заказ установлена проверка.​ функции будет код​ текстовое значение из​ ПРАВСИМВ, ЛЕВСИМВ, НАЙТИ​ значение»)).​​ из выпадающего списка​​ НДС 20% была​​ Заголовок введите, например,​​Выделите ячейку​​Выделите ячейку​​ является общим.​​Разрешить маркеры заполнения и​​ которых включена проверка​

  • ​ данных. Если книга​​Нет​ общего планируемого значения​ на основе значения​и еще вопрос,​ ошибки #ИМЯ?. Поэтому​ ячейки B3, если​ и ЗНАЧЕН. Последняя​Пример 1. В Excel​ или отменить ввод​ введена старая ставка​

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

  • ​ при явной передаче​ результат проверки условия​​ преобразует данные в​ реализована возможность импорта​ оставив пустую ячейку.​ 19%. Эта небольшая​в поле Сообщение введите​.​.​ используется совместно, изменить​, а затем защитите​

  • ​ скопировать или удалить​ доступа, изменение параметров​​ введенные данные, или​ пользователь в этой​ книги. В приведенном​ что в ячейках​ параметра в функцию​ – ИСТИНА;​ формат Число (если​ данных из внешних​

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

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

​ кнопку​ ячейке введет значение,​ ниже примере пользователь​ установлена проверка без​ его значение необходимо​B3 – неизмененное значение​ это возможно). Определить​ источников. С использованием​ предупреждает об вводе​ – 1% может​ будет выводиться, если​ чем 10.​Данные​ невозможно. Необходимо сначала​Выключен пересчет вручную.​Для создания раскрывающегося списка​ пока не будет​Отмена​

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

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

​ создать большие проблемы​ пользователь ввел неверное​

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

​Результат:​(Data) нажмите кнопку​ отменить общий доступ​  Если включен пересчет​ можно воспользоваться командой​ отменен общий доступ.​, чтобы удалить недопустимую​

support.office.com

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

• ​ сообщение об ошибке.​
• ​ значение "абв", которое​В прикрепленном файле​
• ​ кавычки.​
• ​ условия проверки –​
• ​ функция Т, а​

​ импортированы некоторые данные,​ предоставляет попытку отредактировать​ для фирмы с​ значение, например, Введите​Примечание:​

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

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

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

​Вы можете сделать так,​ является для нее​ во время работы​

1. ​Результатом выполнения функции =Т(A1)​​ ЛОЖЬ.​​ преобразовать в текстовый​
2. ​ содержащиеся в xml-документе.​​ или выбрать из​​ всевозможными последствиями. Чтобы​​ число от 1​​Чтобы удалить проверку​

   

   ​(Data Validation).​​ книги.​​ могут повлиять на​(на вкладке​ к ячейкам, в​Информационное сообщение​

   • ​ чтобы при выборе​​ недопустимым.​​ некоторые ячейки выделились​​ является текст, содержащийся​​Аналогично заполним последующие ячейки​
   • ​ формат – ТЕКСТ.​​ Необходимо создать новую​​ выпадающего списка.​​ исключить ошибки созданных​​ до 5.​
   • ​ данных из ячейки,​На вкладке​

     

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

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

1. ​ столбца C. В​​Таблица на защищенном листе​ таблицу, в которой​​Сообщение – параметр сообщает​ по причине человеческого​Теперь при вводе значения​
2. ​ выделите её и​
3. ​Параметры​

   

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

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

1. ​ (формулы не видны,​​ будут отображены только​​ о неправильном значении​ фактора, воспользуемся встроенным​ вне интервала [1,​
2. ​ на вкладке​
3. ​(Settings) диалогового окна​

   

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

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

1. ​ приложении Excel отсутствует​​ недопустимы, но не​​ данные какого типа​

   

2. ​ необходимо предоставить общий​ знаю.​

   ​ «», если в​

   

​Чтобы убедиться, что каждая​​ так как стоит​ текстовые данные для​ и позволяет проигнорировать​ инструментом Excel для​​ 5] будет выведено​​Данные​​ Проверка вводимых значений​​ и лист не​Формулы​​), которая позволяет определить​​ возможность автоматического уведомления​ препятствует их вводу.​ нужно ввести в​ доступ к книге​​Guest​​ данной ячейке находятся​

​ ячейка содержит текст,​ защита):​
​ совместимости с другими​
​ ошибку.​

​ проверки данных, который​

office-guru.ru

Контроль ввода ОШИБочных значений в MS EXCEL. Часть 2 - с применением Проверки данных

​ окно, сообщающее об​(Data) нажмите кнопку​ (Data Validation) сделайте​ защищен паролем, вы​

​, в группе​ имя для диапазона,​ о том, что​ Это самый гибкий​ ячейку. Такое сообщение​ другим пользователям и​: видимо кто-то(ну явно​ данные другого типа.​ используем функцию Т:​Данные в виде текста​ программными продуктами, не​Закладка «Сообщение для ввода»​ позволяет контролировать все​ ошибке (см. Файл​

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

• ​ следующее:​ можете изменить или​Вычисление​
• ​ содержащего список. После​ в существующих ячейках​ тип сообщения.​
• ​ выводится рядом с​ обеспечить точность и​ не Вы) нажал​
• ​guzell04​Функция Т имеет следующий​
• ​ необходимо ввести в​ поддерживающими форматы Число​ содержит поля ввода​

• ​ что вводиться на​ примера).​(Data Validation). Затем​Из выпадающего списка​
• ​ удалить проверку. Если​выберите​ создания списка на​ имеются недопустимые данные.​При появлении​ ячейкой. Вы можете​ согласованность вводимых данных.​

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

​ рабочий лист.​Также контроль ввода неверных​ кликните по​Тип данных​ лист защищен и​Параметры вычислений​ другом листе можно​ В этом случае​информационного сообщения​ переместить его, но​ Кроме всего прочего,​guzell04​ в ячейке С2​=Т(значение)​В ячейке B2 введем​ Excel.​ текстовое поле для​Пример прайс-листа с введенными​ данных можно организовать​Очистить все​

​(Allow) выберите​​ вы не знаете​
​, а затем —​ скрыть лист, который​ можно выделить недопустимые​пользователь может нажать​

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

excel2.ru

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

​(Clear All). Чтобы​Целое число​ пароль, обратитесь к​Выполнять автоматически​ содержит список, а​ данные на листе​ кнопку​ экране до тех​ использовать в следующих​ выбрать?​ клиента, в ячейке​ является обязательным для​Описание функций формулы:​Для решения используем следующую​ которое будет появляться​ ставках НДС:​ формул (см. статью​ быстро выбрать все​(Whole number).​ его прежнему владельцу,​.​ затем защитить книгу,​ с помощью кругов.​ОК​ пор, пока вы​ целях:​guzell04​ С1 — кредитный​

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

​ заполнения. Он используется​Т(защищенный!B3)="" – условие, проверяемое​ формулу:​

​ перед вводом данных​Чтобы в Excel сделать​ Контроль ввода ошибочных​ ячейки с проверкой​Из выпадающего списка​

1. ​ так как в​Отсутствуют ошибки в формулах.​ чтобы пользователи не​ Когда недопустимые данные​, чтобы принять недопустимое​ не перейдете к​Ограничение данных предопределенными элементами​
2. ​: При вставке чего?{/post}{/quote}​ лимит, а общая​ для передачи проверяемых​ функцией ЕСЛИ. Результат​Данная запись имеет следующий​ в ячейку. А​
3. ​ проверку вводимых данных​ значений с применением​ данных, используйте инструмент​Значение​
4. ​ Excel невозможно восстановить​  Убедитесь, что формулы​ смогли получить доступ​ найдены, круги можно​ значение, либо нажать​ другой ячейке или​ списка​копирование ячейки с​ сумма нового заказа​

​ выполнения функции Т​ смысл:​ точнее как только​ в ячейки следует​ формул) или с​Выделение группы ячеек​(Data) выберите​ потерянные пароли. Вы​ в ячейках, для​

1. ​ к этому списку.​ скрыть. При исправлении​ кнопку​ не нажмете клавишу​: например, можно ограничить​ проверкой при вставке​
2. ​ вводится в ячейке​Примечание 1: Как правило,​ проверяется на пустое​Функция ЕСЛИ выполняет проверку​ ячейка будет активной​ выполнить ряд последовательных​
3. ​ помощью Условного форматирования​(Go To Special).​Между​ также можете скопировать​

​ которых включена проверка,​Если вы измените параметры​ недопустимых данных они​Отмена​ ESC.​ выбор отдела значениями​ в другие ячейки​ СЗ (отредактируйте формулу,​ необходимость в использовании​ значение «».​ значения, которое вернет​ сразу высветлиться соответствующее​ действий:​ (см. статью Контроль​Урок подготовлен для Вас​(Between).​ данные на другой​ не являются причиной​

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

​ "Бухгалтерия", "Финансовый отдел",​ (чтобы в этих​ чтобы она работала​ функции Т не​ТЕКСТ(защищенный!B3;"#") – функция, преобразующая​ функция Т;​

​ примечание. Данное примечание​Выделите диапазон ячеек где​ ввода ошибочных значений​ командой сайта office-guru.ru​Введите минимальное и максимальное​ лист и удалить​ возникновения ошибок, таких​ вы можете автоматически​Чтобы обвести данные, выделите​Обратите внимание на советы​

exceltable.com

Функция Т в Excel для проверки принимаемых на вход типа данных

​ на второй вкладке​ "Отдел кадров" и​ ячейках тоже проверка​ с другой структурой​ возникает, поскольку в​ данные из ячейки​Если ячейка G2 не​ повышает контроль над​ вводиться формула с​ с применением Условного​Источник: http://www.excel-easy.com/basics/data-validation.html​ значения.​ проверку данных.​

Примеры использования функции Т в Excel

​ как #ССЫЛКА! или​ применить изменения ко​ нужные ячейки и​ и рекомендации по​ "Проверка данных".​ т. д.​ была) что выбрать?​ ячеек).​ Excel все преобразования​ защищенный!B3 в текстовый​ содержит текстовые данные,​ ошибками связанных с​ процентной ставкой для​ форматирования). Однако, в​Перевела: Ольга Гелих​Сообщения для ввода появляются,​

​Если при попытке ввести​

​ #ДЕЛ/0!. Пока ошибки​ всем остальным ячейкам,​

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

1. ​Когда пользователи привыкнут к​Ограничение использования чисел за​ например, спец. вставка​
2. ​1. Щелкните на​ данных выполняются в​ тип данных.​ будет возвращено пустое​ вводом значений. В​ цен с НДС​ отличие от Проверки​Автор: Антон Андронов​ когда пользователь выделяет​ данные в ячейку​
3. ​ не будут устранены,​ имеющим такие же​Данные​ в Excel.​ вводу данных, вы​

​ пределами указанного диапазона​ или что-то другое.​ ячейке СЗ, в​ автоматическом режиме. Данная​

​защищенный!B3 – получение данных​ значение «» и​ нем можно указать​ и выберите инструмент:​

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

Как определить формат ячейки в Excel?

​ приложение Excel будет​ параметры. Для этого​>​Примечание:​ можете снять флажок​: например, можно указать​Юрий М​ которую будет вводиться​ функция была добавлена​ из ячейки, если​ сработает функция ТЕКСТ,​ правильное значение для​ «ДАННЫЕ»-«Работа с данными»-«Проверка​ форматирование и формулы​ для предотвращения ввода​ ему, что нужно​ выводится предупреждение и​ пропускать проверку данных.​ на вкладке​Работа с данными​

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

​ сумма нового заказа.​ с целью обеспечения​ условие Т(защищенный!B3)="" -​

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

​ данных».​

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

​ ЛОЖЬ.​ тип данных в​После заполнения всех параметров​В появившемся окне «Проверка​

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

​ ввод ошибочных значений,​ в MS EXCEL.​ вкладку​ точно, что вы​ формулах являются правильными.​установите флажок​Проверка данных​ книгах в службы​.​ (например, 3 %) или​ не попробовали ПРОСТО​ Данные • Проверка.​ программными продуктами, поддерживающими​Аналогично заполним данными ячейку​ текстовый и запишет​ в окне «Проверка​ вводимых значений» на​ а лишь информируют​Предположим, что на листе​

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

​ можете вводить, обратитесь​  Если ссылка на​

​>​

1. ​ Excel или Excel​Также можно выводить​ разрешить только целое​
2. ​ ВСТАВИТЬ?​3. Перейдите на​ работу с таблицами.​ B3. В результате​ полученное значение в​
3. ​ вводимых значений» нажмите​ вкладке «Параметры» из​ о допущенных ошибках​ EXCEL производятся некие​

​(Input Message) и​ к владельцу книги.​ ячейку будет изменена​

​ ячейки с тем​Обвести неверные данные​ Web App, сначала​

Функция Т в Excel и особенности ее синтаксической записи

​сообщение об ошибке​ число от 1​

​Z​

​ выпадающего списка «Тип​ ввода.​ вычисления. В качестве​ сделайте следующее:​Задать вопрос на форуме​ и формула в​ же условием​.​ потребуется создать ее​при вводе пользователем​ до 100.​: Как вариант.​

​4. Выберите в​

1. ​Если функция Т принимает​ только текстовыми значениями:​Если в ячейке G2​В результате возле каждой​ данных:» выберите опцию​СОВЕТ​
2. ​ исходных данных пользователь​Поставьте галочку напротив​ сообщества, посвященном Excel​ ячейке, для которой​.​Чтобы быстро отменить проверку​ в классическом приложении​ неверных данных.​

• ​Ограничение дат, находящихся за​Z​ раскрывающемся списке Тип​ на вход текстовые​Пример 3. В Excel​ содержатся текстовые данные,​
• ​ заполняемой ячейки справа​ «Список».​:​ должен ввести числовой​Отображать подсказку, если ячейка​У вас есть предложения​
• ​ включена проверка, будет​Если проверка данных не​ данных в ячейке,​ Excel.​Существует три типа сообщений​ пределами определенного промежутка​: Извините, первый с​ данных строку Другой.​ данные (строка текста,​ можно определить тип​
• ​ они будут перенесены​ появляется стрелка выпадающего​В полю ввода «Источник:»​Чтобы найти все​ параметр из определенного​ является текущей​ по улучшению следующей​

exceltable.com

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

​ вычислять недопустимый результат,​​ работает, проверьте указанные​ выделите ее и​Ширина раскрывающегося списка определяется​ об ошибке.​ времени​ ошибкой, поправлен -​5. Введите в​ ячейка, содержащая текст),​ данных, содержащихся в​ в ячейку B2​ списка, в которой​ введите значения разных​
​ ячейки на листе,​ интервала (например, от​(Show input message​ версии Excel? Если​
​ для этой ячейки​ ниже условия.​
​ выберите​ шириной ячейки, для​
​Значок​: например, можно предотвратить​ см. этот.​
​ поле Формула текст​ она вернет эти​ определенной ячейке, используя​
​ без изменений.​ указана правильная процентная​ процентных ставок: 0%;​

​ к которым применена​

​ 1 до 5).​​ when cell is​ да, ознакомьтесь с​ не появится сообщение​

​Пользователи не копируют и​​Данные​ которой применяется проверка​Тип​

​ выбор даты, которая​​-19887-​ =СУММ($С$2:$С$3) ОК.​ данные.​
​ функцию ТИП, которая​Аналогично заполним все остальные​

​ ставка НДС.​​ 20%; освобождается.​
​ Проверка данных необходимо:​ С помощью инструмента Проверка​ selected).​ темами на портале​
​ о проверке.​ не вставляют данные.​>​ данных. Возможно, вам​Описание​

​ предшествует текущей, при​Guest​Вопрос: как копировать​Функция Т возвращает пустую​ возвращает числовое значение,​ ячейки столбца B.​

​Внимание! Инструмент «Проверка данных»​​Перейдите на закладку «Сообщение​на вкладке Главная в​ данных запретим пользователю​

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

​  Проверка данных предназначена​​Работа с данными​

​ потребуется увеличить ее,​Остановка​ запросе отгула.​: Спасибо! Мне помогло!​ данную проверку в​ строку, если она​ соответствующее одному из​ В результате получим​

​ срабатывает только при​​ об ошибке» и​ группе Редактирование щелкните​ ввод числа, не​Введите само сообщение.​

​ Excel.​​ с сайтом SharePoint.​

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

​ Алесандр, г.Минск​​ другие ячейки?​ принимает на вход​

planetaexcel.ru

​ поддерживаемых типов (например,​

Вступление

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

• значение является числом от 1 до 6
• дата произойдет в следующие 30 дней
• текстовая запись содержит менее 25 символов

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

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

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

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

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

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

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

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

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

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

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

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

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

Пользователь видит сообщение, подобное этому:

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

Вид: Останов

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

Вид: Предупреждение

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

Вид: Сообщение

Сообщает пользователям, что данные являются недействительными. Это сообщение не делает ничего, чтобы остановить ввод неверных данных. Информационное окно имеет 3 кнопки: «ОК», чтобы принять недействительные данные, «Отмена», чтобы удалить их и «Справка».

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

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

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

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

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

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

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

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

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

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

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

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

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

Простое выпадающее меню

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

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

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

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

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

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

Именованные диапазоны автоматически являются абсолютными, поэтому они не изменятся.

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

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

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

= ЕЧИСЛО (А1)

Если пользователь вводит значение 10 в A1, ЕЧИСЛО (ISNUMBER) возвращает ИСТИНА, и проверка данных завершается успешно. Если вводится значение типа «яблоко» в A1, ЕЧИСЛО (ISNUMBER) возвращает ЛОЖЬ, и проверка данных завершается неудачно.

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

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

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

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

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

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

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

= И (ЛЕВСИМВ (А1) = «z»; ДЛСТР (A1) = 5)

Эта формула возвращает ИСТИНА только тогда, когда код длиной 5 цифр и начинается с «z». Два значения в примере выше возвращают ЛОЖЬ с этой формулой.

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

= И (А1> СЕГОДНЯ (), А1 <= (СЕГОДНЯ () + 30))

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

= СЧЁТЕСЛИ (диапазон, А1) <2

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

= ЕЧИСЛО (НАЙТИ ( «@», A1)

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

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

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

= И (ЛЕВСИМВ (А1) = «z», ДЛСТР (A1) = 5)

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

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

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

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

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

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

Чтобы очистить все данные проверки из диапазона ячеек:

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

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

Используйте проверку данных в 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 существует инструмент для проверки введенных данных сразу после нажатия клавиши ENTER – Проверка данных.

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

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-х значений из списка вам выбрать не удастся. В источнике можно указать диапазон ячеек, содержащий заранее сформированный список или ссылку на Именованную формулу . Пример приведен в статье Выпадающий (раскрывающийся) список
• Другой . В ячейку разрешен ввод значений удовлетворяющих более сложным критериям. Для задания критериев необходимо использовать формулу. Рассмотрим это условие подробнее.

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

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

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

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