Поиск расхождений в Excel — объяснение 5 простых приемов

При работе с большими наборами данных в Microsoft Excel неизбежны расхождения в данных. Выявление и устранение этих несоответствий имеет решающее значение для обеспечения точных отчетов и анализа.

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

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

Оглавление

• Понимание расхождений в Excel
• 5 способов найти расхождения в Excel
• # 1 — Поиск расхождений с помощью ручного просмотра в Excel
• # 2 — Использование фильтра в Excel для обнаружения и исправления несогласованных данных
• 3# – Выделение несоответствий с условным форматированием
• #4 – Поиск расхождений с помощью расширенных функций Excel
  • Функции ЕСЛИ и ИС
  • ВПР, ГПР и XПР
  • СООТВЕТСТВОВАТЬ
• #5 – Использование надстроек Excel для обнаружения несоответствий
• Как можно создать правила проверки данных для предотвращения расхождений?
• Наше последнее слово

Понимание расхождений в Excel

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

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

Чтобы найти эти несоответствия и управлять ими, вы можете использовать следующие функции Excel :

• Фильтры: применяйте фильтры к определенным столбцам, чтобы просматривать уникальные значения и быстро выявлять несогласованные данные.
• Условное форматирование: используйте правила форматирования, чтобы выделить ячейки, соответствующие определенным критериям, или несоответствующие данные между столбцами.
• Расширенные функции Excel. Используйте такие функции, как ПОИСКПОЗ и ВПР, для сравнения наборов данных и выявления расхождений между ними.

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

5 способов найти расхождения в Excel

Есть 5 основных способов устранения несоответствий в Excel:

1. Проверка вручную
2. Фильтрация
3. Условное форматирование
4. Расширенные функции Excel
5. Надстройки Excel

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

# 1 — Поиск расхождений с помощью ручного просмотра в Excel

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

Вот несколько шагов, которые вы можете выполнить, чтобы выполнить проверку вручную:

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

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

# 2 — Использование фильтра в Excel для обнаружения и исправления несогласованных данных

Один из самых простых способов обнаружения несогласованных данных — использование фильтра Excel .

С помощью фильтра мы изолируем неверные данные в столбце «Пол» в следующем наборе данных:

1. Выберите «Сортировка и фильтр» на вкладке «Главная» .

2. Выберите «Фильтр» — убедитесь, что ваш курсор находится в диапазоне данных.

3. Щелкните стрелку раскрывающегося списка в столбце с несогласованными данными.

4. Теперь отмените выбор всех правильных совпадающих данных. В нашем случае это мужчина . Затем нажмите OK , чтобы сохранить изменения.

5. Теперь на вашем листе Excel будут отображаться только строки с неверными значениями. Чтобы исправить их, нажмите «Найти и выбрать» на вкладке «Главная» .

6. Нажмите «Заменить» , когда появится раскрывающийся список.

7. Введите неправильное значение в поле «Найти» и правильное значение в поле «Заменить на» . Затем нажмите «Заменить все» .

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

3# – Выделение несоответствий с условным форматированием

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

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

Мы будем использовать образец таблицы, чтобы показать вам силу условного форматирования:

1. Выберите таблицу или предпочитаемые строки и столбцы. Мы выбираем столбец A (компания) и столбец B (страна). Затем выберите «Условное форматирование» на вкладке «Главная» .

2. Выберите тип правила, а затем правило. Мы выбираем правила выделения ячеек и повторяющиеся значения…

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

4. Все повторяющиеся значения теперь выделены светло-красной заливкой с темно-красным текстом.

#4 – Поиск расхождений с помощью расширенных функций Excel

Функции ЕСЛИ и ИС

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

Например, мы использовали формулу =IF(A2=B2,"MATCH","NOT MATCHED")для сравнения ячеек A2 и B2. И мы перетащили формулу, чтобы применить ее к другим ячейкам. Таким образом, идентичные ячейки возвращают MATCH , иначе NOT MATCHED .

Вы можете комбинировать ЕСЛИ с функциями IS, такими как ISNUMBER или ISTEXT, для проверки определенных типов данных. Например, формула =IF(ISNUMBER(B2), "Number", "NaN")вернет число в столбце c, если B2 является числовым, и NaN в противном случае.

ВПР, ГПР и XПР

Функции ВПР (вертикальный) и ГПР (горизонтальный) полезны при сравнении значений в разных столбцах или строках двух таблиц. Для этого используйте следующую формулу: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) или =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

Замените lookup_value на значение, которое вы хотите найти в другой таблице, table_array на диапазон ячеек во второй таблице, col_index_num или row_index_num на порядковый номер столбца или строки, из которых вы хотите получить значение, а [range_lookup] на FALSE для точного совпадения или TRUE для приблизительного совпадения.

В следующем руководстве мы используем функцию ВПР в столбце E, чтобы сравнить имена сотрудников в столбце A и столбце D, чтобы получить их зарплаты из столбца B, если совпадение найдено.

XLOOKUP — это обновленная версия VLOOKUP и HLOOKUP, доступная в Excel 2021 и Excel 365. Формула XLOOKUP: =XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]).

Замените lookup_value на значение, которое вы хотите найти в другой таблице, lookup_array на диапазон ячеек для поиска во второй таблице, return_array на массив, из которого вы хотите вернуть значение, и [if_nout_found] на текстовое значение, если нет соответствия значения найдены.

Вот быстрый пример XLOOKUP с теми же таблицами:

СООТВЕТСТВОВАТЬ

Функцию ПОИСКПОЗ также можно использовать для сравнения двух списков на наличие несоответствий.

Функция ПОИСКПОЗ =MATCH(lookup_value, lookup_array, [match_type])ищет указанный элемент в диапазоне и возвращает относительное положение элемента в этом диапазоне.

Здесь мы находим положение арбузов в массиве A2: A8 , используя ПОИСКПОЗ. Мы используем match_type как 0 , чтобы найти первое значение, точно равное lookup_value .

#5 – Использование надстроек Excel для обнаружения несоответствий

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

Во-первых, рассмотрите возможность использования надстройки Spreadsheet Inquire . Это может помочь вам сравнить две книги и выделить различия между ячейками. Чтобы включить надстройку Spreadsheet Inquire:

1. Перейдите на вкладку «Файл» .

2. Выберите «Параметры» и щелкните категорию «Надстройки» .

3. В поле «Управление» выберите « Надстройки COM» и нажмите «Перейти» .

4. Установите флажок Запрос электронной таблицы и нажмите OK .

5. После включения перейдите на вкладку «Запрос» , чтобы использовать команду «Сравнить файлы» .

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

Чтобы включить Analysis ToolPak, выполните те же действия, что и при включении надстройки Spreadsheet Inquire. Но выберите «Надстройки Excel» в поле «Управление » и установите флажок «Пакет анализа» .

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

Как можно создать правила проверки данных для предотвращения расхождений?

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

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

2. Перейдите на вкладку «Данные» на панели инструментов и нажмите кнопку «Проверка данных» (отмечена двумя горизонтальными прямоугольниками, зеленой галочкой и красным перечеркнутым кружком).

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

В Excel доступны различные типы критериев проверки, некоторые из которых включают:

• Целое число — разрешены только целые числа в указанном диапазоне.
• Decimal — разрешает только десятичные числа в указанном диапазоне.
• Список — ограничивает ввод предварительно определенным списком допустимых значений.
• Дата — требуются даты в определенном диапазоне дат.
• Время — принимает только время в пределах определенного временного диапазона.
• Длина текста — устанавливает ограничения на длину вводимого текста.
• Пользовательский — позволяет создавать настраиваемые правила проверки с использованием формул Excel.

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

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

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

Наше последнее слово

В этой статье вы узнали о различных методах поиска и устранения расхождений в данных Excel. Давайте вспомним эти ключевые стратегии:

• Использование раскрывающихся списков «Фильтр» в столбцах для выявления уникальных значений и противоречивых данных.
• Использование условного форматирования в выбранных ячейках для выделения несоответствий с использованием множества шаблонов правил.
• Применение расширенных функций Excel, таких как ПОИСКПОЗ , ВПР , ССПР и т. д., для перекрестной проверки данных в двух списках или таблицах.
• Использование надстроек Excel для обнаружения расхождений.
• Настройка правил проверки данных для предотвращения ошибок в данных

С этими методами в вашем наборе навыков Excel ваши данные будут более точными и заслуживающими доверия.

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

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