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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Динамические выпадающие списки Excel

• Критерий Манна-Уитни
• Подключение MySQL в Excel
• Подключение Excel к SQL Server

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

Добавление, изменение и удаление проверки данных

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

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

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

В этой статье

• Способами, уведомления пользователей о проверки ошибок
• Проблемы совместимости
• Добавление проверки данных
• Изменение состояния проверки данных
• Удаление проверки данных

Способами, уведомления пользователей о проверки ошибок

Для формы, которая предназначена для заполнения с помощью InfoPath можно настроить сообщение проверки данных, которое будет отображаться как всплывающая подсказка. После просмотра всплывающей подсказки пользователей по желанию можно открыть подробное оповещения, можно предоставить. Кроме того можно настроить подробные оповещение автоматически отображается. Например при указании ошибку в случае, если пользователь вводит его имя в поле, используемый для сбора телефонные номера можно включить всплывающая подсказка для отображения сообщения «Тип телефонный номер в этом поле». При включении оповещение автоматического добавления вы можете Показать подробные сообщения по умолчанию, такие как «в этом поле требует телефонный номер в формате (555) 555-0100». При выборе для отображения всплывающей подсказки по умолчанию пользователей можно вручную отображать оповещение, которые предоставляют, щелкнув правой кнопкой мыши поле в InfoPath и выбрав параметр, чтобы отображать оповещение.

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

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

3. Если щелкнуть полное описание ошибки, появляется соответствующее оповещение.

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

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

2. указатель примет форму руки при перемещении всплывающая подсказка.

3. Если щелкнуть ссылку, появляется соответствующее оповещение.

Совместимость

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

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

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

Список элементов управления, которые поддерживают проверки данных

В следующей таблице перечислены элементы управления Microsoft Office InfoPath 2007 с поддержкой проверки данных и ли они доступны для шаблонов форм с поддержкой браузера.

Элементы управления, которые поддерживают привязк данных Название

Доступно для шаблонов форм с поддержкой браузера?

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

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

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

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

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

• Ограничение данных предопределенными элементами списка: например, можно ограничить выбор отдела значениями «Бухгалтерия», «Финансовый отдел», «Отдел кадров» и т. д.
• Ограничение использования чисел за пределами указанного диапазона: например, можно указать максимальный процент годового роста зарплаты сотрудника (например, 3 %) или разрешить только целое число от 1 до 100.
• Ограничение дат, находящихся за пределами определенного промежутка времени: например, можно предотвратить выбор даты, которая предшествует текущей, при запросе отгула.
• Ограничение времени, которое находится за пределами определенного промежутка времени: например, вы можете указать, что собрания должны проводиться в период между 8:00 и 17:00.
• Ограничение количества текстовых знаков: например, можно разрешить ввод не более 10 знаков в ячейку.
• Проверка данных на основе формул или значений в других ячейках: например, проверку данных можно использовать для указания максимально допустимого предела для комиссионных и премий на основе общего планируемого значения заработной платы. Если пользователь в этой ячейке введет значение, превышающее ограничение, появится сообщение об ошибке.

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

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

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

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

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

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

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

У сообщения Остановка есть два параметра: Повторить и Отмена.

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

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

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

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

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

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

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

• Ширина раскрывающегося списка определяется шириной ячейки, для которой применяется проверка данных. Возможно, вам потребуется увеличить ее, чтобы не обрезать допустимые записи, ширина которых больше ширины раскрывающегося списка.
• Если необходимо защитить лист или книгу, выполните это действие после настройки параметров проверки. Перед тем как защитить лист, убедитесь, что разблокированы любые ячейки, которые необходимо проверить. В противном случае пользователям не удастся ввести данные в ячейки. См. статью Защита листа.
• Если требуется открыть общий доступ к книге, можно сделать это после настройки проверки данных. Если книга открыта для общего доступа, изменение параметров проверки данных невозможно, пока не будет отменен общий доступ.
• Можно применить поверку данных к ячейкам, в которые уже введены данные. Однако в приложении Excel отсутствует возможность автоматического уведомления о том, что в существующих ячейках имеются недопустимые данные. В этом случае можно выделить недопустимые данные на листе с помощью кругов. Когда недопустимые данные найдены, круги можно скрыть. При исправлении недопустимых данных они исчезают автоматически. Чтобы обвести данные, выделите нужные ячейки и выберите Данные >Работа с данными >Проверка данных >Обвести неверные данные.
• Чтобы быстро отменить проверку данных в ячейке, выделите ее и выберите Данные >Работа с данными >Проверка данных >Параметры >Очистить все.
• Чтобы найти ячейки на листе, для которых включена проверка данных, на вкладке Главная в группе элементов Правка выберите пункт Найти и выделить, а затем нажмите кнопку Проверка данных. После того как найдены ячейки, для которых включена проверка данных, можно изменить, скопировать или удалить параметры проверки данных.
• Для создания раскрывающегося списка можно воспользоваться командой Присвоить имя (на вкладке Формулы в группе Определенные имена), которая позволяет определить имя для диапазона, содержащего список. После создания списка на другом листе можно скрыть лист, который содержит список, а затем защитить книгу, чтобы пользователи не смогли получить доступ к этому списку.
• Если вы измените параметры проверки одной ячейки, вы можете автоматически применить изменения ко всем остальным ячейкам, имеющим такие же параметры. Для этого на вкладке Параметры установите флажок Распространить изменения на другие ячейки с тем же условием.
• Если проверка данных не работает, проверьте указанные ниже условия.
  • Пользователи не копируют и не вставляют данные. Проверка данных предназначена для отображения сообщений и предотвращения ввода недопустимых данных только в том случае, если пользователь вводит данные непосредственно в ячейку. Если данные копируются или вставляются, сообщения не отображаются. Чтобы предотвратить копирование и вставку данных путем перетаскивания ячеек, выберите Файл >Параметры >Дополнительно >Параметры правки и снимите флажок Разрешить маркеры заполнения и перетаскивание ячеек, а затем защитите лист.
  • Выключен пересчет вручную. Если включен пересчет вручную, невычисляемые ячейки могут повлиять на правильность проверки данных. Чтобы отключить пересчет вручную, откройте вкладку Формулы, в группе Вычисление выберите Параметры вычислений и щелкните параметр Автоматически.
  • Отсутствуют ошибки в формулах. Убедитесь, что формулы в ячейках, для которых включена проверка, не являются причиной возникновения ошибок, таких как #ССЫЛКА! или #ДЕЛ/0!. Пока ошибки не будут устранены, приложение Excel будет пропускать проверку данных.
  • Ссылки на ячейки в формулах являются правильными. Если ссылка на ячейку будет изменена и формула в ячейке, для которой включена проверка, будет вычислять недопустимый результат, для этой ячейки не появится сообщение о проверке.
  • Возможно, таблица Excel связана с сайтом SharePoint. В таблицу Excel, которая связана с сайтом SharePoint, добавить проверку данных невозможно. Чтобы добавить проверку данных, необходимо удалить связь таблицы Excel или преобразовать ее в диапазон.
  • Возможно, в настоящее время вводятся данные. Во время ввода данных в ячейку команда «Проверка данных» недоступна. Нажмите клавишу ВВОД, чтобы завершить ввод данных, или ESC, чтобы отменить его.
  • Возможно, лист защищен или является общим. Если книга защищена или используется совместно, изменить параметры проверки данных невозможно. Необходимо сначала отменить общий доступ или снять защиту книги.

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

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

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

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

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

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

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

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

Ошибки пользователей при вводе данных – одна из самых распространенных проблем в Excel . В результате ошибочных данных ячейки с формулами на разных листах друг за другом могут заполниться ошибками #ЗНАЧ , #H/Д и т.п. Поиск ошибок может занимать длительное время, потому что непонятен первоначальный источник ошибки. С чего начинать? Но появление ячеек с ошибками это даже хорошо.

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

НЕДОСТАТКИ СТАНДАРТНЫХ ИНСТРУМЕНТОВ EXCEL

Как вы наверняка знаете, в Excel существует стандартный функционал для контроля вводимых в ячейки данных. Он устанавливается через меню Данные -> Проверка данных . В результате появляется диалоговое окно Проверка вводимых значений c тремя закладками.

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

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

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

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

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

Например, если данные вводятся в ячейку с использованием команд Копировать->Вставить, Копировать->Специальная вставка-Значения или путем «растаскивания» от соседних ячеек, то проверка данных не осуществляется. Также значения в ячейках не контролируются, если они введены в ячейку с помощью макроса (если только контроль не осуществляется в самом макросе).

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

Часто это бывает, когда вы выгружаете данные в таблицы из других программ, например, 1С. Но о собенно неприятно когда ошибки незаметны или малозаметны. Например:

• Значение в ячейке выглядит как число и дата, а на самом деле оно является текстом. Например потому, что перед или после цифр есть пробел или другие непечатные (невидимые) символы.
• Ячейка выглядит пустой, а на самом деле там есть непечатные символы.
• В качестве разделителя целой и дробной части использована точка вместо запятой.
• Введено значение, которое отсутствует в «привязанном» к ячейке выпадающем списке.

Последняя ситуация достаточно распространена. Например, вы ведете реестр платежей от контрагентов, и вводите наименование контрагентов с помощью выпадающего списка, связанного со справочником контрагентов. Спустя некоторое время вы обнаруживаете в наименовании контрагента ошибку и исправляете ее в справочнике. При этом Excel никак не отреагирует на то, что в реестре есть записи со старым (ошибочным) наименованием контрагента. То есть Excel при изменении элементов списка не отлавливает уже введенные данные типа «Список».

2. Стандартные способы проверки могут «тормозить»

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

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

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

3. Бесполезный инструмент «Обвести неверные данные»

Для проверки правильности введенных значений в Excel есть встроенный инструмент «Обвести неверные данные» , который вызывается через пункты меню — Данные -> Проверка данных -> Обвести неверные данные.