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

Содержание

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

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

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

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

Прибыль – это естественно доход минус расход. Допустим нам нужно проверить, в какие дни прибыль падала ниже 40$. Решение следующее:

Выделите данные в диапазоне D2:D6 и выберите инструмент: «Данные»-«Работа с данными»-«Проверка данных».
В появившемся окне: «Проверка вводимых значений» установите такие же настройки как показано на рисунке. И нажмите ОК.
Теперь выберите инструмент из выпадающего списка: «Данные»-«Проверка данных»-«Обвести неверные данные». И обратите внимание на результат:
При необходимости можете удалить красные обводки, выбрав инструмент «Удалить обводку неверных данных».

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

Окно контрольного значения в Excel

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

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

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

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

Создайте книгу с листами, так как указано выше на рисунке и на каждом листе добавьте по несколько позиций разным количеством.
На листе: «ИТОГО» поставьте формулы как указано выше на рисунке и перейдите в ячейку B4.
Перейдите на закладку «Формулы» и выберите инструмент «Окно контрольного значения»

В появившемся окне нажмите кнопку «Добавить контрольное значение» и в нем указываем адрес ячейки, за значением которого мы будем следить: =ИТОГО!$B$4. Жмите добавить.

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

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

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

все уроки

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

Допустим, что в таблице прайс-листа с розничными ценами на товары, в одном из столбцов указана процентная ставка НДС.

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

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

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

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

В полю ввода «Источник:» введите значения разных процентных ставок: 0%; 20%; освобождается.
Перейдите на закладку «Сообщение об ошибке» и заполните текстовое поле «Сообщение:» текстом который будет содержать сообщение при вводе других значений, которые отличаются от указанных значений в списке.

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

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

Сообщение – параметр сообщает о неправильном значении и позволяет проигнорировать ошибку.

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

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

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

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

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

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

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

Когда полезна проверка данных?

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

Ограничить записи предопределенными элементами в списке — Например, вы можете ограничить выбор отдела пользователя бухгалтерией, расчетом заработной платы, отделом кадров и т. д.
org/ListItem»>
Ограничить числа за пределами указанного диапазона — Например, вы можете указать максимальный процент ввода для ежегодного повышения заслуг сотрудника, скажем, 3%, или разрешить только целое число от 1 до 100.
Ограничить даты вне определенного периода времени — Например, в запросе на отгул сотрудника вы можете запретить кому-либо выбирать дату до сегодняшней даты.
Ограничить время за пределами определенного периода времени — Например, вы можете указать расписание собраний с 8:00 до 17:00.
Ограничить количество текстовых символов — Например, вы можете ограничить разрешенный текст в ячейке до 10 или менее символов.
Проверка данных на основе формул или значений в других ячейках — Например, вы можете использовать проверку данных, чтобы установить максимальный предел для комиссий и бонусов на основе общей прогнозируемой стоимости заработной платы. Если пользователи вводят сумму, превышающую лимит, они видят сообщение об ошибке.

Ввод проверки данных и сообщения об ошибках

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

Вы настроили входное сообщение на второй вкладке проверки данных.

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

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

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

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

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

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

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

Ширина раскрывающегося списка определяется шириной ячейки, в которой выполняется проверка данных. Возможно, вам придется настроить ширину этой ячейки, чтобы предотвратить усечение ширины допустимых записей, которые шире, чем ширина раскрывающегося списка.
Если вы планируете защитить лист или книгу, защитите их после того, как закончите указывать все параметры проверки. Убедитесь, что вы разблокировали все проверенные ячейки, прежде чем защищать лист. В противном случае пользователи не смогут вводить какие-либо данные в ячейки. См. Защита рабочего листа.
Если вы планируете предоставить общий доступ к книге, предоставьте к ней общий доступ только после того, как вы закончите указывать параметры проверки и защиты данных. После предоставления общего доступа к книге вы не сможете изменить параметры проверки, пока не прекратите общий доступ.
Вы можете применить проверку данных к ячейкам, в которые уже введены данные. Однако Excel не уведомляет вас автоматически о том, что существующие ячейки содержат недопустимые данные. В этом случае вы можете выделить недопустимые данные, указав Excel обвести их на листе. Как только вы определили неверные данные, вы можете снова скрыть круги. Если вы исправите неверную запись, кружок автоматически исчезнет.
Чтобы применить круги, выберите ячейки, которые вы хотите оценить, и перейдите к Данные > Инструменты для работы с данными > Проверка данных > Обведите неверные данные .
Чтобы быстро удалить проверку данных для ячейки, выберите ее, а затем перейдите к Данные > Инструменты данных > Проверка данных > Настройки > Очистить все .
org/ListItem»>
Чтобы найти на листе ячейки с проверкой данных, на вкладке Главная в группе Редактирование щелкните Найти и выбрать , а затем щелкните Проверка данных . После того, как вы нашли ячейки с проверкой данных, вы можете изменить, скопировать или удалить настройки проверки.
При создании выпадающего списка можно использовать Определить имя 9Команда 0004 (вкладка Formulas , группа Defined Names ), чтобы определить имя для диапазона, содержащего список. После создания списка на другом листе вы можете скрыть рабочий лист, содержащий этот список, а затем защитить книгу, чтобы пользователи не имели доступа к списку.
Если вы измените параметры проверки для ячейки, вы можете автоматически применить эти изменения ко всем другим ячейкам с такими же параметрами. Для этого на Настройки установите флажок Применить эти изменения ко всем другим ячейкам с теми же настройками .
Если проверка данных не работает, убедитесь, что:
- Пользователи не копируют и не заполняют данные — Проверка данных предназначена для отображения сообщений и предотвращения неверных записей только тогда, когда пользователи вводят данные непосредственно в ячейку. При копировании или заполнении данных сообщения не появляются. Чтобы пользователи не могли копировать и заполнять данные путем перетаскивания ячеек, перейдите к Файл > Параметры > Дополнительно > Параметры редактирования > снимите флажок Включить маркер заполнения и перетаскивание ячеек , а затем защитите лист.
- Ручной пересчет отключен — Если ручной пересчет включен, невычисленные ячейки могут помешать правильной проверке данных. Чтобы отключить ручной пересчет, перейдите на Вкладка Формулы > Группа Расчет > Параметры расчета > щелкните Автоматически .
- Формулы не содержат ошибок — Убедитесь, что формулы в проверенных ячейках не вызывают ошибок, таких как #ССЫЛКА! или #ДЕЛ/0!. Excel игнорирует проверку данных, пока вы не исправите ошибку.
- Ячейки, на которые есть ссылки в формулах, верны — Если ячейка, на которую ссылаются, изменяется так, что формула в проверенной ячейке вычисляет недопустимый результат, сообщение проверки для ячейки не появляется.
- Таблица Excel может быть связана с сайтом SharePoint — Вы не можете добавить проверку данных в таблицу Excel, связанную с сайтом SharePoint. Чтобы добавить проверку данных, необходимо отменить связь таблицы Excel или преобразовать таблицу Excel в диапазон.
- Возможно, в данный момент вы вводите данные. — Команда проверки данных недоступна, пока вы вводите данные в ячейку. Чтобы завершить ввод данных, нажмите Enter или ESC для выхода.
- Рабочий лист может быть защищен или доступен для общего доступа . Вы не можете изменить параметры проверки данных, если ваша книга является общей или защищенной. Сначала вам нужно отменить общий доступ к книге или снять защиту с нее.

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

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

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

Нужна дополнительная помощь?

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

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

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

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

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

Вкладка «Настройки»

На вкладке настроек вы вводите критерии проверки. Доступно восемь вариантов проверки пользовательского ввода:

Любое значение — удаляет любую существующую проверку данных.
Целое число — разрешены только целые числа. Например, вы можете указать, что пользователь должен ввести число от 0 до 30.
Decimal – пользователь должен ввести число с десятичными значениями.
Список. Пользователь должен будет создать раскрывающийся список для выбора.
Дата — пользователь должен будет ввести формат даты.
Время — пользователь должен ввести время.
Длина текста — проверяет ввод на основе длины данных.
Пользовательский — проверяет ввод данных пользователем с помощью пользовательской формулы.

Вкладка «Ввод сообщения»

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

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

Предупреждение об ошибке, вкладка

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

На вкладке сообщения об ошибке:

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

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

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

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

Шаг 1. Выберите ячейку для проверки

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

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

Шаг 2. Укажите критерии проверки

На вкладке настроек укажите критерии проверки.

Шаг 3. В разделе «Разрешить» выберите «Критерии»

В разделе «Разрешить» выберите параметр «Целое число», «Десятичный», «Список», «Дата», «Время», «Длина текста» и «Пользовательский».

Шаг 4. Выберите условие

.
В разделе «Данные» выберите условие и установите необходимые значения в зависимости от того, что вы выбрали для «Разрешить» и «Данные».
Шаг 5 – Ввод сообщения
Вы можете ввести входное сообщение, если хотите. Этот шаг является необязательным.
Шаг 6 — Пользовательское сообщение об ошибке
Вы также можете установить собственное сообщение об ошибке. Этот шаг является необязательным.
Шаг 7. Нажмите «ОК»
Нажмите OK. Теперь, если вы попытаетесь ввести значение вне указанного диапазона (10, 50), это приведет к ошибке.
Список проверки данных Excel (раскрывающийся список)
Чтобы добавить раскрывающийся список, выполните следующие действия:
Откройте диалоговое окно проверки данных.
На вкладке Параметры выберите список.
В поле источника введите список проверки, разделенный запятыми.
Вы получите следующий результат.
Проверка даты
Вы можете настроить проверку даты в Excel. Выберите дату в поле разрешения и подберите соответствующие критерии.
Выполните следующие действия, чтобы настроить проверку даты:
Выберите ячейки, в которых вы будете применять проверку данных.
В раскрывающемся списке разрешенных выберите дату.
В раскрывающемся списке Данные выберите одно из них.
Щелкните в поле «Дата начала» и выберите ячейку O10, где вводится дата начала.
Нажмите клавишу F4, чтобы изменить ссылку на ячейку на абсолютную — $O$10.
Щелкните в поле Дата окончания и выберите ячейку O11, где введена Дата окончания.
Нажмите клавишу F4, чтобы изменить ссылку на ячейку на абсолютную — $O$11.
Нажмите OK, чтобы закрыть окно проверки данных.
Приведенная выше проверка даты будет принимать только значения даты между 9с ноября 2020 г. по 18 ноября 2020 г.
Начните свой карьерный рост с PGP в области бизнес-анализа. Получите возможность освоить инструменты Excel, Tableau и Python. Начните учиться прямо сейчас!
Заключение
Проверка данных в Excel обеспечивает способ ограничения ошибок пользователя и сбора достоверных данных от пользователя. Это приводит к стандартному вводу данных и систематической отчетности.
Повысьте свою карьеру в области аналитики с помощью новых мощных навыков работы с Microsoft Excel, пройдя сертификационный курс Business Analytics with Excel, который включает обучение работе с Power BI. Вы даже можете записаться на программу последипломного образования по бизнес-анализу, которая поможет вам приобрести обширный бизнес-опыт в реальных проектах
Курс «Бизнес-аналитика» — это комплексная программа, которая дает очень глубокое понимание основных концепций анализа данных и статистики и того, как они помогают в принятии решений на основе данных. Тренинг по бизнес-аналитике знакомит вас с Power BI, одним из самых популярных на сегодняшний день инструментов бизнес-аналитики, и позволяет вам изучить ключевые статистические концепции, чтобы извлечь ценную информацию из доступных наборов данных и помочь вам представить свои результаты с помощью информационных панелей уровня руководителей.