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

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

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

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

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

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

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

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

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

Пояснение

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

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

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

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

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

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

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

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

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

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

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

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

Интересно почитать: Как в excel задать значение ячейки

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

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

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

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

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

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

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

Ошибки #ЗНАЧ и #Н/Д в функции ВПР() Excel и как сними биться.

В данной статье расскажу о 2-ух ошибках которые может выдать функция ВПР() :

Вышеперечисленные ошибки более нередко встречаться при использовании функции ВПР() и весьма нередко вызывают трудности с устранением у начинающих юзеров Excel .

Интересно почитать: Как в excel скопировать ячейки с сохранением размера

Когда возникает ошибка #Н/Д и как от нее избавиться при использовании ВПР().

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

Почему возникает ошибка Н/Д (НД)?

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

Как поправить ошибки Н/Д?

1-ый метод – применить обработку ошибок – функцию ЕСЛИОШИБКА(ВПР(*;*;*;0);”Тут была ошибка”). Эта функция подменяет сообщение о ошибке на хоть какое значение, которое Вы укажете.
Метод №2 – удалить все пробелы и, по способности, знаки препинания из ячеек. Для этого необходимо нажатием кнопок ctrl+H вызвать окно подмены значений, позже в поле «Отыскать» ввести пробел либо символ препинания, а в поле «Поменять на:» не вводить ничего и нажить клавишу «Поменять все».
Метод №3 – поставить в функции ВПР() допуск ошибки. Как нам извесчтно 4 –й аргумент функции это число ошибок которые может допускать в сравниваемой строке функция ВПР(). Другими словами, если поставить число «1», то допускается 1 ошибка при сопоставлении [ВПР(*;*;*;1)]. В таком случае строчка без пробела и с одним пробелом будут считаться схожими. Но в таком методе есть подкол — весьма высока возможность неправильных результатов, к примеру, слово «полка» и «палка» имеют отличие всего в один символ и будут восприняты функцией, как одно и то же.

Когда возникает ошибка #ЗНАЧ и как от нее избавиться при использовании ВПР().

Ошибка #ЗНАЧ может выводиться функцией ВПР(), если введенные значения аргументов функции неправильны и функция не может их обработать.

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

Возникает ошибка #ЗНАЧ в функции ВПР() тогда, когда длина строчки сравниваемой функцией очень большая и не может быть обработана. К примеру, в Excel 2010 наибольшая длина строчки обрабатываемой функцией всего 255 знаков, и если Вы будете ассоциировать строчки длиной 256 и наиболее знаков, то получите ошибку #ЗНАЧ.

Поправить ошибку #ЗНАЧ в таком случае можно уменьшив длины сравниваемых строк.

Еще ошибка #ЗНАЧ может появиться если Вы пропустили(не указали) один из аргументов в функции.

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

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

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

Проверка на введенные значения

Программка Microsoft Office Excel дозволяет гибко настроить введенные значения. При всем этом корректность будет проверяться по одному или нескольким характеристикам. Благодаря этому можно настроить поиск и ячейку.

Числовые проверки

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

Целочисленные значения – в ячейку можно будет ввести лишь числа без дробной части. При всем этом можно также ограничить их значение определенным спектром или запретить ввод отрицательных. Это отлично подступает для номеров столбцев и строк.
Действительные числа. В ячейку можно вводить лишь числовые значения, которые могут включать дробную часть (до 10-х). Но при всем этом нереально ввести хоть какой текст. Можно добавочно вводить дату.
При выбирании параметра «Дата» покажется возможность вводить числовые данные лишь в определенном формате. Для этого осуществляются проверка и поиск по введенному числу, и при неверном выборе формата покажется ошибка. Спектр значений: от первого января 1900 года до 31 декабря 9999.
Если избрать тип ограничений по времени – то, как и в случае с датой, можно будет ввести лишь временной просвет в определенном формате. Не считая того, можно ограничить введенные значения, к примеру, введя данные лишь опосля пополудни. Также существует возможность вводить данные в ячейку программки Microsoft Office Excel с помощью числового эквивалента. Так, 12:00 соответствует число 0.5. Это обуславливается методами хранения данных в Microsoft Office Excel. В приложении за дату и время отвечает дробное число. На примере пополудни: 12/24 = 0.5.

Текстовая проверка

Можно ограничить метод введения текста разными способами. Разглядим подробнее.

Проверка данных в Excel по длине введенного текста. Подступает для фамилий и наименований компаний. При всем этом разрешено вводить в определенную ячейку лишь заблаговременно данный размер знаков. Также можно вводить числа и даты. Но тут есть свои индивидуальности. Из-за хранения дат в памяти в виде десятичного числа не получится ввести дату позднее 13/10/2173, но лишь если ограничить продолжительность вводимого значения 5 знаками. То же самое относится и к формулам. Если итог формулы очень длиннющий, запись в ячейку не будет изготовлена.
Перечень ограничений. Проверка вводимых данных в Excel осуществляется с помощью заблаговременно данного перечня ограничений. При всем этом можно заблаговременно задать определенный перечень ограничений. Не считая того, можно задавать значения в перечне с помощью ссылки на ячейку или именованной формулы. Перечень можно заполнять разными методами.

Интересно почитать: Уместить текст в одной ячейке в excel

С помощью формулы

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

Некие методы ввода.

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

При всем этом есть ограничения на ввод формул. Потому лучше пользоваться условным форматированием.

Вывод комментария в случае, если ячейка выбрана

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

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

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

Как создать проверку данных в Excel? Если юзер вводит неточное значение, то можно вывести сообщение о ошибке и предложить ввести значения поновой. По факту приложение стопроцентно соответствует функции MessageBox из встроенного языка программирования в Microsoft Excel Visual Basic Application.

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

Виды сообщений о ошибке:

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

Внедрение ссылок на остальные листы

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

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

Механизм работы проверки данных

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

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

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

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