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

Обновлено: 14.05.2024

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источником данных может быть:

Связанные выпадающие списки

Создадим выпадающие списки несколькими способами – для разных таблиц с исходными данными.

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

Способ 2. Названия групп – в первом столбце, элементы групп – во втором столбце.

Способ 1. Связанные выпадающие списки из таблицы с группами в заголовках столбцов

Исходные данные: таблица с названиями групп в заголовках столбцов.

Справка:

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

Создать форматированную таблицу просто: выделите диапазон ячеек и перейдите в меню Главная -> Форматировать как таблицу -> выберите понравившийся вид таблицы. Готово – форматированная таблица создана.

Формула ДВССЫЛ

Формула ДВССЫЛ передает значения из ячейки, адрес которой записан в самой формуле в виде текстовой строки.

Пошаговая инструкция по созданию связанных выпадающих списков

Шаг 1. Создайте справочник исходных данных в виде форматированной smart-таблицы.

Выделите таблицу со статьями и преобразуйте ее в smart-таблицу: выберите в меню Главная -> Форматировать как таблицу.

Столбец таблицы: = Источник[Материалы]

Чтобы появилась такая формула, нажмите равно = и выделите столбец, его имя появится в строке формул.

Шаг 2. Создайте выпадающий список с группами.

Шаг 3. Создайте выпадающий список со статьями.

Способ 2. Связанные выпадающие списки из таблицы с группами в первом столбце и элементами - во втором

Исходные данные: таблица с названиями групп в первом столбце, элементами групп – во втором столбце.

Для создания списков используем форматированные (умные) таблицы, сводные таблицы, формулы СМЕЩ + ПОИСКПОЗ + СЧЁТЗ, СЧЁТЕСЛИ и диспетчер имен.

Справка:

Формула СМЕЩ

Синтаксис формулы СМЕЩ такой:

СМЕЩ(ссылка ; смещ_по_строкам ; смещ_по_столбцам ; [высота] ; [ширина] ), где

ссылка – ссылка, от которой вычисляется смещение, может быть адресом ячейки или группы ячеек;
смещ_по_строкам – количество строк, которые требуется отсчитать вверх или вниз от начальной ссылки;
смещ_по_столбцам – количество столбцов, которые требуется отсчитать влево или вправо от начальной ссылки;
[высота] – число строк возвращаемой ссылки (необязательный);
[ширина] – число столбцов возвращаемой ссылки (необязательный).

Формула ПОИСКПОЗ

Ищет нужный нам элемент в диапазоне ячеек и выдает его порядковый номер в диапазоне.

Синтаксис ПОИСКПОЗ такой:

ПОИСКПОЗ( искомое_значение ; просматриваемый_массив ; [тип_сопоставления] )

искомое_значение – значение, которое ищем. Может быть числом, текстом, логическим значением или ссылкой на ячейку;
просматриваемый_массив – диапазон ячеек, где будем искать нужное значение;
[тип_сопоставления] — число -1, 0 или 1, которое показывает, как сравнивать искомое значение с ячейками просматриваемого массива. Не переживайте, если не поняли, когда и что ставить, потому что 90% случаев нужно выбирать ноль.

Подробнее про эту формулу можно посмотреть в видеоинструкции: Какая формула лучше ВПР и работает с несколькими критериями

Формула СЧЁТЗ

СЧЁТЗ просто считает количество непустых ячеек в диапазоне.

Формула СЧЁТЕСЛИ

Почти тот же СУММЕСЛИ, только проще – подсчитывает количество значений, соответствующих определенному условию.

Пошаговая инструкция по созданию списков

Шаг 1. Преобразуйте исходные данные в форматированную smart-таблицу.

Выделите таблицу со статьями и преобразуйте ее в smart-таблицу: перейдите в меню Главная -> Форматировать как таблицу.

Шаг 2. Создайте две сводные таблицы – одну с названиями групп, вторую — со статьями.

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

Создайте вторую сводную таблицу со статьями: меню Вставка -> Сводная таблица. В область строк поместите группы и статьи.

Форматируем сводную таблицу со статьями и придаем ей вид справочника.
Выделите любую ячейку таблицы, перейдите на вкладку Конструктор -> Макет отчета -> Показать в табличной форме. У нас получится почти та таблица, которая нам нужна, но в ней автоматом появятся промежуточные суммы. Чтобы их отключить, идем: Промежуточные итоги -> Не показывать промежуточные суммы.

В итоге получатся два справочника, как на рисунке ниже. Для удобства разместите таблицы рядом на одном листе – с первой строки и в столбцах A, C и D, как на рисунке (это поможет разобраться с формулой СМЕЩ).

Шаг 3. Создайте именованные диапазоны с помощью диспетчера имен.

Откройте диспетчер имен: в меню Формулы -> Диспетчер имен.

Пояснения к формуле:

СМЕЩ ( $A$1 ; 1 ; 0 ; СЧЁТЗ( $A:$A ) – 1 ; 1 ) – определяет адрес ячеек с названиями групп.

- $A$1 – это первая ячейка в справочнике групп.
- Следующие цифры 1 ; 0 – это отступ от первой ячейки на 1 строку и 0 столбцов (отступ нужен, потому что в первой ячейке название столбца).
- СЧЁТЗ( $A:$A ) – 1 Считаем число непустых ячеек в столбце А. Вычитаем -1, потому что название столбца не должно быть в списке.
- Последнее число 1 в формуле – это количество столбцов.
Нажмите ОК. Названия листов в формуле появятся сами.
- Точно так же создайте в диспетчере имен список статей.
  Введите имя ГруппыСтатей, а для диапазона – формулу:
  =СМЕЩ($C$1;ПОИСКПОЗ($G2;$C:$C;0)-1;1;СЧЁТЕСЛИ($C:$C;$G2);1)
Пояснения к формуле:

СМЕЩ ( $C$1 ; ПОИСКПОЗ ( $G2 ; $C:$C ; 0 ) – 1 ; 1 ; СЧЁТЕСЛИ( $C:$C ; $G2 ) ; 1 ) – определяет адрес ячеек с названиями статей из группы с помощью ПОИСКПОЗ, которая ищет группы статей.

Шаг 4. Создайте выпадающие списки.

То же самое – для статей. Тип данных – список, источник =ГруппыСтатьи

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

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

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

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

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

Для записывания в ячейку таблицы можно разрешить только один тип данных. К ним относятся:
- Целочисленные значения – в ячейку можно будет ввести только числа без дробной части. При этом можно также ограничить их значение определенным диапазоном либо запретить ввод отрицательных. Это хорошо подходит для номеров столбцев и строк.
- Действительные числа. В ячейку можно вводить только числовые значения, которые могут включать дробную часть (до десятых). Однако при этом невозможно ввести любой текст. Можно дополнительно вводить дату.
- При выборе параметра "Дата" появится возможность вводить числовые данные только в определенном формате. Для этого осуществляются проверка и поиск по введенному числу, и при неправильном выборе формата появится ошибка. Диапазон значений: от первого января 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 проверка данных в ячейке осуществляется более точно.

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

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

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

Использование ссылок на другие листы

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

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

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

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

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

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

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

Функция ЕСЛИ в MS Excel с одним условием.

Функцией ЕСЛИ в Excel, первый пример.

Рассмотрим простой пример применения функции ЕСЛИ в MS Excel. У нас есть столбец №1, в котором содержаться числа от 1 до 10.

Необходимо проверить, соответствует ли числа находящиеся в столбце №1 нашему критерию. Критерий: является ли числа находящиеся в столбце №1 числами, значения которых больше числа 4.

Взываем функцию ЕСЛИ в ячейку С3.

В диалоговом окне Аргументы функции видим три поля для заполнения:

логическое_выражение — B3>4, здесь указан наш критерий проверки. В данном случае проверяем ячейку В3. Функция определяет, является ли число, которое содержится в этой ячейки, числом больше числа 4.

Нажимаем ОК в диалоговом окне Аргументы функции.

Как видим, число содержащиеся в ячейки В3 не является числом, которое больше числа 4. Функция ЕСЛИ вернула значение_если_ложь (неправда). Протянем функцию ЕСЛИ вниз по столбцу, чтобы проверить значения находящиеся в остальных ячейках столбца №1, на соответствие нашему критерию (логическое_выражение).

Как видно из результата, ячейки в диапазоне В7:В12 содержат в себе числа, которые больше числа 4. Функция ЕСЛИ вернула значение_если_истина (правда).

Функция ЕСЛИ в Excel, второй пример.

Рассмотрим еще один пример. Предположим, что у нас есть таблица. В ней фамилии студентов, которые сдавали зачет. Часть студентов сдали зачет, а часть не сдали.

Теперь добавим в нашу таблицу столбец: Допуск к экзамену. Используем функцию ЕСЛИ для того, чтобы определить допущен студент к экзамену или нет. Диалоговое окно Аргументы функции будет выглядеть вот так:

Важно: текстовый критерий в поле логическое_выражение должен быть заключен в кавычки. Иначе функция выдаст ошибку.

Если студен сдал зачет, он допущен к экзамену, если нет, то не допущен. Нажимаем ОК в диалоговом окне Аргументы функции и протягиваем функцию ЕСЛИ вниз, по всему столбцу Допуск к экзамену. Получаем:

Видим, что в зависимости от результатов сдачи зачета, студент допущен к экзамену, либо не допущен.

Обращаю внимание, что при работе с функцией ЕСЛИ, можно использовать разные варианты равенств: больше (>), меньше( =), меньше или равно ( ).

Функция ЕСЛИ в MS Excel с несколькими условиями.

Предположим, что у нас есть таблица с результатами сдачи контрольной работы студентами ВУЗа. Результаты контрольной работы оцениваются в баллах. От 0 до 100. Где все, что выше 90 баллов, отлично. Выше 80 баллов, это хорошо. Выше 70 баллов, это удовлетворительно. Ниже 70 баллов, это плохо.

Формула функции ЕСЛИ будет выглядеть вот так:

Где ячейка С3, первая из проверяемых ячеек в столбце Оценка. Далее функция протянута вниз по столбцу Результат, до конца таблицы.

Внимание: в данном случае формула функции ЕСЛИ прописана в ручную, прямо в строе формулы.

Таблица выглядит вот так:

В столбце Результат, в зависимости от количества баллов, функция ЕСЛИ вернула значение, согласно заданному критерию в формуле функции.

Формула функции ЕСЛИ выглядит вот так:

Внимание: в данном случае формула функции ЕСЛИ прописана в ручную, прямо в строе формулы.

Формула функции ЕСЛИ выглядит вот так:

Внимание: в данном случае формула функции ЕСЛИ прописана в ручную, прямо в строе формулы.

Читайте также: