Excel — это эффективная программа для обработки данных. И один из методов анализа информации — это сравнение двух списков. Если правильно сравнить два списка в Excel, организовать этот процесс будет очень просто. Просто следите за некоторыми пунктами, которые будут обсуждаться сегодня. Практическая реализация этого метода полностью зависит от потребностей человека или организации в конкретное время. Следовательно, есть несколько возможных случаев, которые следует рассмотреть.
Сравнение двух списков в Excel
Очевидно, что можно вручную сравнить два списка. Но на это уйдет много времени. В Excel есть собственные умные инструменты, которые позволят вам не только быстро сравнивать данные, но и получать информацию, которую не так просто получить глазами. Предположим, у нас есть два столбца с координатами A и B. В них повторяются некоторые значения.
Постановка задачи
Итак, нам нужно сравнить эти столбцы. Метод сравнения двух документов следующий:
- Если уникальные ячейки каждого из этих списков одинаковы, а общее количество уникальных ячеек одинаково, и ячейки одинаковы, то эти списки можно считать одинаковыми. Порядок, в котором расположены значения в этом списке, не имеет значения.
- О частичном совпадении списков можно говорить, если сами уникальные значения совпадают, но количество повторений разное. Следовательно, в этих списках может быть разное количество элементов.
- Тот факт, что два списка не совпадают, обозначается другим набором уникальных значений.
Все эти три условия одновременно являются условиями нашей проблемы.
Решение задачи
Мы генерируем два динамических диапазона, чтобы упростить сравнение списков. Каждый из них будет соответствовать каждому из списков. 
Чтобы сравнить два списка, выполните следующие действия:
- В отдельном столбце создайте список уникальных значений, общих для обоих списков. Для этого используйте формулу: ЕСЛИОШИБКА (ЕСЛИОШИБКА (ИНДЕКС (Список1; ПОИСК (0; СЧЁТЕСЛИ ($ D $ 4: D4; Список1); 0)); ИНДЕКС (Список2; ПОИСК (0; СЧЁТЕСЛИ ($ D $ 4 : D4; Список2); 0))); «»). Сама формула должна быть записана в виде формулы массива.
- Мы определяем, сколько раз каждое уникальное значение встречается в массиве данных. Вот формулы, которые можно использовать для этого: = СЧЁТЕСЛИ (Список1; D5) и = СЧЁТЕСЛИ (Список2; D5).
- Если и количество повторений, и количество уникальных значений одинаковы во всех списках, включенных в эти диапазоны, функция возвращает значение 0. Это означает, что совпадение составляет сто процентов. В этом случае заголовки этих списков будут иметь зеленый фон.
- Если все уникальное содержимое находится в обоих списках, оно возвращается формулами = COUNTIFS ($ D $ 5: $ D $ 34; «*?»; E5: E34; 0) и = COUNTIFS ($ D $ 5: $ D $ 34; «*?»; F5: F34; 0) значение будет нулевым. Если E1 не содержит нуля, но это значение содержится в ячейках E2 и F2, диапазоны будут распознаваться как совпадающие, но только частично. В этом случае названия соответствующих списков станут оранжевыми.
- А в случае возврата одной из описанных выше формул списки будут совершенно отличными от нуля.
Вот ответ на вопрос, как анализировать столбцы на совпадения с помощью формул. Как видите, с помощью функций можно реализовать практически любую задачу, на первый взгляд не имеющую отношения к математике.
Тестирование на примере
В нашей версии таблицы есть три типа списков каждого типа, описанного выше. Содержит частично и полностью совпадающие и несовпадающие.
Для сравнения данных мы используем диапазон A5: B19, в который мы альтернативно вставляем эти пары списков. Будем понимать, какой будет результат сравнения по цвету исходных списков. Если они совершенно разные, это будет красный фон. Если какие-то данные совпадают, то желтый. В случае полной идентичности соответствующие заголовки будут зелеными. Как сделать цвет, который зависит от результата? Это требует условного форматирования.
Поиск отличий в двух списках двумя способами
Мы описываем два других метода поиска различий в зависимости от того, являются ли списки синхронными или нет.
Вариант 1. Синхронные списки
Это простой вариант. Допустим, у нас есть такие списки.
Чтобы определить, сколько раз значения расходились, можно использовать формулу: = СУММПРОИЗВ (- (A2: A20 B2: B20)). Если в результате мы получим 0, это означает, что два списка равны.
Вариант 2. Перемешанные списки
Если списки не идентичны по порядку содержащихся в них объектов, вам необходимо применить такую функцию, как условное форматирование и раскрасить повторяющиеся значения. Или воспользуйтесь функцией СЧЁТЕСЛИ, с помощью которой мы определяем, сколько раз элемент списка встречается в секундах.
Как сравнить 2 столбца по строкам
При сравнении двух столбцов нам часто приходится сравнивать информацию, найденную в разных строках. В этом нам поможет оператор IF. Давайте посмотрим, как это работает на практике. Для этого представим несколько наглядных ситуаций.
Пример. Как сравнить 2 столбца на совпадения и различия в одной строке
Чтобы проанализировать, совпадают ли значения в одной строке, но в разных столбцах, мы пишем функцию ЕСЛИ. Формула вставляется в каждую строку, помещенную во вспомогательный столбец, где будут отображаться результаты обработки данных. Но вам вообще не нужно записывать его в каждую строку, просто скопируйте его в оставшиеся ячейки этого столбца или используйте маркер автозаполнения.
Мы должны написать такую формулу, чтобы понять, совпадают ли значения в обоих столбцах или нет: = ЕСЛИ (A2 = B2; «Соответствие»; «»). Логика этой функции очень проста: она сравнивает значения в ячейках A2 и B2 и, если они равны, возвращает значение «Match». Если данные отличаются, он не возвращает значения. Вы также можете проверить ячейки, чтобы увидеть, есть ли между ними совпадение. В этом случае используется формула: = ЕСЛИ (A2 B2; «Не совпадает»; «»). Принцип тот же, первая проверка. Если ячейки соответствуют критериям, отображается значение «Не совпадает».
вы также можете использовать следующую формулу в поле формулы, чтобы отобразить как «Совпадение», если значения совпадают, так и «Не совпадает», если они различаются: = ЕСЛИ (A2 = B2; «Совпадение»; «Не соответствие»). Вы также можете использовать оператор неравенства вместо оператора равенства. Только порядок значений, которые будут отображаться в этом случае, будет немного отличаться: = ЕСЛИ (A2 B2; «Не совпадает»; «Совпадение»). После использования первой версии формулы результат будет следующим.
В этом варианте формулы регистр не учитывается. Следовательно, если значения в одном столбце отличаются от других только потому, что они написаны с заглавной буквы, программа не заметит этой разницы. Чтобы сравнение было чувствительным к регистру, вам необходимо использовать функцию EXECUTE в критерии. Остальные аргументы оставляем без изменений: = IF (EXACT (A2, B2); «Соответствует»; «Уникально”).
Как сравнить несколько столбцов на совпадения в одной строке
значения в списках можно анализировать по ряду критериев:
- Найдите те строки, которые имеют одинаковое значение повсюду.
- Найдите строки, в которых есть совпадения, только в двух списках.
Давайте рассмотрим несколько примеров того, как действовать в каждом из этих случаев.
Пример. Как найти совпадения в одной строке в нескольких столбцах таблицы
Предположим, у нас есть ряд столбцов, содержащих нужную нам информацию. Перед нами стоит задача определить те серии, в которых значения совпадают. Для этого необходимо использовать следующую формулу: = ЕСЛИ (И (A2 = B2; A2 = C2); «Соответствие”; ” “).
Если в таблице слишком много столбцов, просто используйте оператор COUNTIF вместе с функцией IF: = IF (COUNTIF ($ A2: $ C2; $ A2) = 3; ”Match”; ”“). Число, используемое в этой формуле, указывает количество столбцов для проверки. Если это не так, вам нужно записать, насколько это верно для вашей ситуации.
Пример. Как найти совпадения в одной строке в любых 2 столбцах таблицы
Допустим, нам нужно проверить, совпадают ли значения в одной строке в двух столбцах со значениями в таблице. Для этого нужно использовать функцию ИЛИ как условие, где поочередно записывать равенство каждого из столбцов другому. Вот пример.
Мы используем эту формулу: = ЕСЛИ (ИЛИ (A2 = B2; B2 = C2; A2 = C2); «Соответствие»; «»). Может возникнуть ситуация, когда в таблице много столбцов. В этом случае формула будет огромной и выбор всех необходимых комбинаций может занять много времени. Чтобы решить эту проблему, вам необходимо использовать функцию СЧЁТЕСЛИ: = ЕСЛИ (СЧЁТЕСЛИ (B2: D2; A2) + СЧЁТЕСЛИ (C2: D2; B2) + (C2 = D2) = 0; «Уникальная строка»; «Не уникальная нить”)
Мы видим, что всего у нас есть две функции СЧЁТЕСЛИ. Первым мы поочередно определяем, сколько столбцов похоже на A2, а вторым проверяем количество сходств с B2. Если в результате вычисления этой формулы мы получаем нулевое значение, это означает, что все строки в этом столбце уникальны, но если их больше, есть сходство. Следовательно, если в результате вычисления двух формул и сложения окончательных результатов мы получим нулевое значение, возвращается текстовое значение «Уникальная строка», но если это число больше, записывается, что эта строка не уникальна.
Как сравнить 2 столбца в Excel на совпадения
Теперь возьмем пример. Предположим, у нас есть таблица с двумя столбцами. Вам нужно проверить совпадения в них. Для этого вам нужно применить формулу, которая будет использовать как функцию ЕСЛИ, так и оператор СЧЁТЕСЛИ: = ЕСЛИ (СЧЁТЕСЛИ ($ B: $ B; $ A5) = 0; «Нет совпадений в столбце B»; «Есть соответствует в столбце B”)
Никаких дополнительных действий не требуется. После вычисления результата по этой формуле мы узнаем, совпадает ли значение третьего аргумента функции ЕСЛИ. Если нет, то содержание второго аргумента.
Как сравнить 2 столбца в Excel на совпадения и выделить цветом
Чтобы было легче визуально идентифицировать соответствующие столбцы, вы можете выделить их цветом. Для этого нужно использовать функцию «Условное форматирование». Давайте выясним на практике.
Поиск и выделение совпадений цветом в нескольких столбцах
Чтобы определить совпадения и выделить их, необходимо сначала выбрать диапазон данных, в котором будет выполняться проверка, затем открыть пункт «Условное форматирование» на вкладке «Главная». Там мы выбираем «Повторяющиеся значения» как правило выбора ячейки».
После этого появится новое диалоговое окно, в котором в левом выпадающем списке мы находим опцию «Дублировать», а в правом списке выбираем цвет, в котором будет сделан выбор. После нажатия кнопки «ОК» будет выделен фон всех ячеек с подобием. Затем сравните динамики на глаз.
Поиск и выделение цветом совпадающих строк
Методика проверки совпадения строк немного отличается. Во-первых, нам нужно создать дополнительный столбец, и в нем мы будем использовать конкатенированные значения с помощью оператора &. Для этого вам нужно написать формулу вида: = A2 & B2 & C2 & D2.
Выберите столбец, который был создан и содержит объединенные значения. Далее выполняем ту же последовательность действий, которая описана выше для столбцов. Дублированные строки будут выделены указанным цветом.
Мы видим, что в поисках повторения нет ничего сложного. Excel содержит все необходимые для этого инструменты. Прежде чем применять все эти знания на практике, важно потренироваться.
