Допустим, что в таблице прайс-листа с розничными ценами на товары, в одном из столбцов указана процентная ставка НДС. Как часто бывает в рутинной работе срабатывает человеческий фактор и по ошибке для одной из категорий товаров, вместо ставки НДС 20% была введена старая ставка 19%. Эта небольшая разница в данных – 1% может создать большие проблемы для фирмы с всевозможными последствиями. Чтобы исключить ошибки созданных по причине человеческого фактора, воспользуемся встроенным инструментом Excel для проверки данных, который позволяет контролировать все что вводиться на рабочий лист.
Видео на тему: Курс Excel. Урок 63. Формулы для проверки данных.
Как в Excel сделать проверку данных в ячейках
Пример прайс-листа с введенными ошибками в процентных ставках НДС:
Чтобы в Excel сделать проверку вводимых данных в ячейки следует выполнить ряд последовательных действий:
2. В появившемся окне «Проверка вводимых значений» на вкладке «Параметры» из выпадающего списка «Тип данных:» выберите опцию «Список».
3. В полю ввода «Источник:» введите значения разных процентных ставок: 0%; 20%; освобождается.
4. Перейдите на закладку «Сообщение об ошибке» и заполните текстовое поле «Сообщение:» текстом который будет содержать сообщение при вводе других значений, которые отличаются от указанных значений в списке.
Закладка «Сообщение об ошибке» предоставляет пользователю возможность оформить стиль сообщения об ошибочных вводах значений. Если пользователь вводить в ячейку неправильное значение тогда будет выполнен один из 3-х параметров:
Закладка «Сообщение для ввода» содержит поля ввода для заголовка и текстовое поле для сообщения в примечании, которое будет появляться перед вводом данных в ячейку. А точнее как только ячейка будет активной сразу высветлиться соответствующее примечание. Данное примечание повышает контроль над ошибками связанных с вводом значений. В нем можно указать правильное значение для данных ячеек.
После заполнения всех параметров в окне «Проверка вводимых значений» нажмите на кнопку ОК.
В результате возле каждой заполняемой ячейки справа появляется стрелка выпадающего списка, в которой указана правильная процентная ставка НДС.
Внимание! Инструмент «Проверка данных» срабатывает только при вводе значений в ячейку. Если же значения буду скопированы из других ячеек, то при вставке этот инструмент удалиться из этих ячеек и не сработает.
При вводе большого количества информации в ячейки таблицы легко допустить ошибку. В EXCEL существует инструмент для проверки введенных данных сразу после нажатия клавиши ENTER – Проверка данных.
Инструмент Проверка данных ( Данные/ Работа с данными/ Проверка данных ) не был бы столь популярным, если бы его функциональность ограничивалась бы только собственно проверкой. Ниже кратко перечислены основные приемы работы с этим инструментом.
A. Проверка введенных значений
Как видно на рисунке снизу, условия проверки вводимого значения можно настроить очень гибко.
Можно разрешить ввод значений в ячейку только определенного типа, выбрав необходимое условие из выпадающего (раскрывающегося) списка:
При выбранном типе Другой, в поле Формула нужно ввести формулу для расчета логического значения. Если оно принимает значение ИСТИНА, то такое значение разрешено вводить в ячейку, если ЛОЖЬ, то ваше значение ввести не получится. В качестве аргумента формулы нужно использовать ссылку на саму ячейку, к которой применяется Проверка данных или ссылку на зависящую от нее ячейку. Например,
Чтобы
Введите формулу
Пояснение
Ячейка B2 содержала только текст
Допустить ввод значения в ячейку B1 только в случае, если после ввода значение в ячейке D1 будет больше 100, в D2 меньше, чем 400
Значение в ячейке, содержащей возраст работника ( С1 ), всегда должно быть больше числа полных лет работы ( D1 ) плюс 18 (минимальный возраст приема на работу)
При заполнении таблицы данными о возрасте и стаже работы можно поставить эту проверку для обеих ячеек ( C1 и D1 ). Для этого нужно выделить сразу 2 ячейки, вызвать Проверку данных и немного модифицировать формулу =ЕСЛИ($C1>$D1+18;ИСТИНА;ЛОЖЬ)
Все данные в диапазоне ячеек A1:A20 содержали уникальные значения
Значение в ячейке, содержащей имя кода продукта ( B5 ), всегда начиналось со стандартного префикса «ID-» и имело длину не менее 10 знаков.
Проверку данных вводим для ячейки B5
При выделении нескольких ячеек, там где нужно, не забывайте указывать абсолютную ссылку на ячейки (например, $A$1:$A$20 ).
В.Отображение комментария, если ячейка является текущей.
В отличие от обычного примечания ( Рецензирование/ Примечание/ Создать примечание ), которое пропадает после того, как курсор мыши уходит с ячейки (если не активна опция Показать все примечания ), этот комментарий отображается всегда, когда ячейка выделена.
С.Вывод подробного сообщения об ошибке.
D.Создание связанных диапазонов (списков)
Е. Использование в правилах ссылок на другие листы
В Excel 2010, напротив, можно использовать правила проверки данных, ссылающиеся на значения на других листах. В Excel 2007 и Excel 97-2003 проверка данных этого типа не поддерживается и не отображается на листе. Однако все правила проверки данных остаются доступными в книге и применяются при повторном открытии книги в Excel 2010, если они не были изменены в Excel 2007 или Excel 97-2003.
F. Как срабатывает Проверка данных
G. Поиск ячеек с Проверкой данных
Опция Проверка данных этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных ). При выборе переключателя Всех будут выделены все такие ячейки. При выборе опции Этихже выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.
Для того чтобы обезопасить себя от ошибок (описок) при вводе каких-то показателей или данных, работая в Excel, мы с помощью самой программы имеем возможность контролировать правильность занесения этих самых показателей в ячейки.
Для приведения контроллера в действие нам необходимо задать ему определённые условия и установить необходимые параметры. Правда за всем-всем контроль установить мы не можем, но и предлагаемых условий вполне может быть достаточно.
Допустим, мы собираемся вводить показатели, содержащие некоторое количество нулей. При больших объёмах и продолжительной работе с цифрами возможность ошибки очень даже реальна. Мы живые люди и усталость глаз да и общая усталость появляется у любого человека.
Первым нашим шагом станет открытие вкладки «Данные» верхнего меню:
В этой вкладке мы направим своё внимание на раздел под названием «Работа с данными»:
Ну, а в самом разделе устремимся взором на функционал поименованный «Проверка данных»:
Давайте щелчком левой кнопки мыши по стрелочке справа от названия откроем этот функционал и посмотрим на содержащиеся в нём опции:
Нам сейчас интересна первая самая верхняя опция. Одним щелчком развернём её окно, чтобы посмотреть на содержимое:
В сущности это и есть тот самый контроллер, о котором мы повели речь в самом начале сегодняшнего разговора. Что от нас требуется, так это выбрать и зафиксировать те параметры из предлагаемых, которые и будут помогать при вводе каких-то данных (показателей). Даже можем завести текст сообщения-предупреждения.
Например, мы собираемся вводить в ячейки показатели, содержащие не менее и не более 5-ти нулей. Конечно, если количество самих показателей не более пяти, то вероятность недобрать нули или перебрать, невелика. А если количество показателей значительно больше, то после 125-го введённого показателя, в наших глазах появится мелькание нулей, резь и слезливость. Нам это нужно? Нет!
Давайте на простом примере с этими нулями проверим, как контроллер работает.
На практике работа контроллера будет выражаться появлением окошка сообщения о нарушении условий ввода, которые мы же сами и определим. Предположим, что ввод показателей будет производиться нами в ячейки столбца «В». Выделим его, щёлкнув единожды по букве «В»:
Затем откроем окошко проверки данных и начнём работу с параметрами:
В окошке «Тип данных» установим параметр «Целое число»:
В поле «Значение» выберем из списка параметр «Между»:
После того как мы выбрали этот параметр в нижней части окошка появились две строки ввода поименованные «Минимум» и «Максимум»:
Поскольку в нашем примере показатели не должны содержать менее или более пяти нулей, то в строку минимума мы собственноручно впишем параметр равный 100 000, а в строку максимума 900 000:
Нажимать кнопку «Ок» не спешим, а в верхней части окошка открываем следующую вкладку с названием «Сообщение для ввода»:
Строки этой вкладки мы заполняем произвольно, вводя любой текст, какой только захотим. Число символов заголовка ограничено, поэтому, решив написать заголовок, мы не должны делать его слишком длинным. Текст сообщения, напротив, предусматривает большое количество слов. На мой взгляд, без острой на то необходимости, строки этой вкладки можно и не заполнять. Мы можем, даже снять галочку с отображения подсказки ввода в текущую ячейку. Но если мы сочтём появление подсказки ещё до начала ввода показателей (данных) жизненно необходимой, то эта самая подсказка будет появляться одновременно с помещением курсора в ту ячейку, в которую мы собираемся вводить показатель:
В общем, с заполнением строк этой вкладки поступаем на своё усмотрение.
Давайте откроем следующую вкладку поименованную «Сообщение об ошибке»:
В этой вкладке мы также можем ввести заголовок и сам текст сообщения. На мой взгляд, заполнение одного лишь поля «Сообщение» вполне будет достаточно. При выборе одного из вариантов: Остановка, Предупреждение, Сообщение раздела «Вид» мы должны быть готовы к тому, что варианты Предупреждение и Сообщение выполнив каждый свою задачу (предупредив или же сообщив) всё же позволят осуществить ввод показателя не соответствующего условию:
А вот вариант остановки ввода не позволит продолжать ввод данных или каких-то показателей в те предварительно выделенные ячейки, пока в ячейке с данными, внесёнными с нарушением условий, не будут произведены изменения или же ввод и вовсе прекращён:
Основная наша задача при работе с контроллером заключается в правильности задаваемых условий, в основе которых лежит чёткое понимание того, что именно мы хотим проконтролировать.
Чтобы проверить орфографию в тексте на листе, на вкладке Рецензирование нажмите кнопку Орфография.
Совет: Кроме того, можно нажать клавишу F7.
Ниже описано, что происходит при вызове функции проверки орфографии.
Если для проверки орфографии выделена одна ячейка, Excel проверяет весь лист, включая комментарии, колонтитулы и графические элементы.
Если выделено несколько ячеек, Excel проверяет орфографию только в этих ячейках.
Чтобы проверить орфографию в строке формул, выделите соответствующие слова.
Примечание: Excel не проверяет орфографию в ячейках, содержащих формулы.
Проверка орфографии при вводе
Функции автозаполнения и автозамены помогают исправлять ошибки непосредственно при вводе текста.
Функция автозаполнения, которая включена по умолчанию, помогает соблюдать точность при вводе, подставляя значения из других ячеек, но не проверяет отдельные слова в ячейке. Автозаполнение может пригодиться при создании формул.
Автозамена исправляет ошибки в тексте формулы, элементе управления листа, текстовом поле и метках диаграммы. Вот как это использовать:
Выберите Файл > Параметры.
В категории Правописание нажмите кнопку Параметры автозамены и ознакомьтесь с ошибками, которые чаще всего возникают при вводе.
Примечание: Функция автозамены не работает с текстом в диалоговых окнах.
Дополнительные ресурсы
Вы также можете проверить Исследования, Тезаурус и Перевод для получения дополнительной помощи с правописанием и языком.
На вкладке Обзор нажмите Правописание или нажмите F7 на клавиатуре.
Примечание: Диалоговое окно Орфография не откроется, если ошибки правописания не обнаружены или вы пытаетесь добавить слово, которое уже есть в словаре.
Выполните любое из описанных ниже действий.
В разделе Варианты выберите нужное слово и нажмите кнопку Изменить.
Изменение каждого экземпляра этого слова в данном документе
В разделе Варианты выберите нужное слово и нажмите кнопку Изменить все.
Пропуск слова и переход к следующему слову с ошибкой
Пропуск каждого экземпляра этого слова в данном документе и переход к следующему слову с ошибкой
Нажмите Пропустить все.
Проверка орфографии при вводе
Вы можете использовать функцию автозамены для исправления опечаток и слов с ошибками. Для получения дополнительной информации см. Добавление, изменение или отключение автоматических исправлений.
Чтобы проверить орфографию любого текста на листе, нажмите Просмотр > Проверка > Правописание.
Ниже описано, что происходит при вызове функции проверки орфографии.
Если для проверки орфографии выделена одна ячейка, Excel проверяет весь лист, включая комментарии, колонтитулы и графические элементы.
Если выделено несколько ячеек, Excel проверяет орфографию только в этих ячейках.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Кроме проверки присутствуют и многие другие функции, которые позволяют осуществлять эту работу. Перечислим существующие типы проверок.
Проверка на введенные значения
Программа Microsoft Office Excel позволяет гибко настроить введенные значения. При этом правильность будет проверяться по одному либо нескольким параметрам. Благодаря этому можно настроить поиск и ячейку.
Числовые проверки
Для записывания в ячейку таблицы можно разрешить только один тип данных. К ним относятся:
Текстовая проверка
Можно ограничить способ введения текста различными методами. Рассмотрим подробнее.
При помощи формулы
Существует отдельный параметр, если ни один из стандартных не соответствует нуждам пользователя. Проверка данных в Excel в таком случае осуществляет по формуле. В текстовое поле будет предложено ввести формулу, соответствующую желаемому типу значений, которые будут вводиться в ячейку. Таким образом, в Excel проверка данных в ячейке осуществляется более точно.
Некоторые способы ввода.
При этом существуют ограничения на ввод формул. Поэтому лучше воспользоваться условным форматированием.
Вывод комментария в случае, если ячейка выбрана
Функция проверка данных в Excel имеет возможность добавлять различные комментарии при выборе текущей ячейки, в которой была произведена запись по проверке вводимых значений. Для того чтобы это сделать, нужно в окне проверки перейти в раздел сообщения для ввода и ввести в текстовые поля заголовка и сообщения произвольный текст.
Такой комментарий останется на экране, пока ячейка выделена, что может быть полезно при длительной работе.
Вывод сообщения об ошибке
Как сделать проверку данных в Excel? Если пользователь вводит некорректное значение, то можно вывести сообщение об ошибке и предложить ввести значения заново. По факту приложение полностью соответствует функции MessageBox из встроенного языка программирования в Microsoft Excel Visual Basic Application.
В окне выбора проверки нужно перейти в раздел «Сообщение об ошибке», затем поставить галочку на чекбоксе «Выводить сообщение об ошибке», выбрать тип сообщения, затем ввести заголовок и произвольное сообщение в текстовое поле. Проверка ввода данных в Excel в таком случае осуществляется по двум условиям.
Виды сообщений об ошибке:
Использование ссылок на другие листы
В новых версиях программы можно проводить связывание нескольких несвязанных листов, как и в условном форматировании. Для того чтобы было возможно при заполнении условий проверять наличие информации на другом листе, можно воспользоваться функцией получения имен.
Для того чтобы корректно выполнить проверку данных, которые находятся на другом листе, нужно для начала определить имя листа, а также наименование ячейки, затем при помощи ссылочного типа сослаться на полное имя листа и ячейки и корректно ввести данные. Проблема была устранена с обновлением распространяемого пакета Microsoft Office до 2010 года. Однако в более ранних версиях невозможно отобразить на листе проверку данных, которая проводилась этим способом.
Принцип работы проверки данных
Подпрограмма осуществляет свою работу при проверке введенных в ячейку таблицы данных. При этом срабатывает встроенный язык программирования, при корректном значении ошибки не возникнет. Однако в случае если значение не подходит по типу или величине, появится сообщение об ошибке, однако у этого есть свои ограничения.
При попытке ввести данные при помощи вставки из буфера обмена или при автоматическом заполнении проверка не сработает. Кроме того, в случае копирования можно «задеть» правила проверки и в некоторых случаях их удалить.
Как найти ячейку с проверкой данных
Чтобы найти на листе все ячейки, с которыми осуществляется работа и в которых заполнена проверка данных, можно использовать инструмент «Выделение группы ячеек». Он находится в разделе «Главная». Свойство проверки данных этого инструмента позволит выделить все нужные данные в ячейке.
