Поиск недопустимых значений в Excel

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

  • Год рождения должен быть числом от 1900 до 2020.
  • Электронное письмо должно содержать символ @, за которым следует точка (.com, .info, .gov, .org и т. Д.).
  • Страна должна быть написана особым образом, обычно задается в раскрывающемся меню.

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

Для целей примера предположим, что у нас есть следующие

Случай

В компании, в которой мы работаем, клиенты разделены на 4 региона — ЕС, Великобритания, Россия, США. В нашем списке клиентов несколько тысяч строк, и с годами регионы были представлены по-разному, разными людьми и в разных системах. В итоге ситуация выглядит так:

Читать также:  Какие данные хранит Linkedin для каждого пользователя

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

Решение 1

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

Значения, не соответствующие указанным критериям, выделяются красным цветом. В данном случае — список в столбце D.

Интересная функциональность, но с очень небольшой практической применимостью из-за следующих проблем:

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

Как мы можем проверить, в каких ячейках размещена валидация? Сначала нажмите CTRL + G, чтобы открыть меню «Перейти», и выберите «Специальные».

Читать также:  Исправьте нечитаемый текст с помощью Power Query

Затем выберите Проверка данных Все

И когда мы даем ОК, Excel отмечает все ячейки, в которые вводится проверка.

Обратите внимание, что в строках 6, 7 и 9 есть проверки, но в них вводятся недопустимые значения. Это происходит, когда проверка применяется ПОСЛЕ ввода значений. Действует при вводе значений, но не для уже введенных..

  1. Проверка могла быть изменена (удалена) при вводе данных. Самый простой способ — скопировать и вставить значение из другого места на листе или даже в другом файле. Затем ячейка принимает правила проверки вместе со скопированным значением (или его отсутствием). Это могло произойти в строках 13 и 14 изображения выше.
  1. Самым большим ограничением этого метода является то, что он подходит для небольшого числа значений — 10, 20, 50. В общем, число, которое можно легко охватить одним взглядом. А если у нас 20 тысяч строк — какой толк, что 3 тысячи окружены кружками? Как мы их накроем?
Читать также:  Удалить все скрытые листы в Excel (Удалить все скрытые листы)

Решение 2

Короче говоря, полезно знать вышеуказанные функции, если вы ищете несколько недопустимых значений (или собираетесь пройти тест Excel). &# x1f609;). Но с практической точки зрения лучшим решением будет использование ВПР, как показано ниже.

Это покажет # N / A для всех недопустимых значений. Отныне их очень легко отфильтровать с помощью фильтра.

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

Мы видим, что в нашем случае они в основном работают с клиентами из Болгарии и Европы, но у нас также есть несколько клиентов из Азии и Африки..

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

Понравилась статья? Поделиться с друзьями:
Что нужно знать пользователю?