Сравнение 2-х списков в EXCEL

Сравним 2 списка содержащих ТЕКСТОВЫЕ повторяющиеся значения.

Пусть в столбцах A и В имеется два списка с повторяющимися значениями.

Задача

Сравнить содержимое обоих списков.

Прежде чем начать сравнение списков определимся с методикой сравнения:

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

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

3. Списки считаются не совпадающими , если списки их уникальных значений не совпадают (значения, которые есть в одном списке, в другом отсутствуют).

Решение

Создадим для удобства 2 Динамических диапазона Список1 и Список2 , которые будут ссылаться на диапазоны ячеек, содержащие значения в соответствующих списках.

Чтобы сравнить списки сделаем следующее (см. Файл примера ):

• Сформируем в столбце D единый список уникальных значений для обоих списков (см. статью Отбор уникальных значений из двух диапазонов ) с помощью формулы массива : = ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); "")
• Подсчитаем количество вхождений каждого уникального значения в оба списка с помощью формул =СЧЁТЕСЛИ(Список1;D5) и =СЧЁТЕСЛИ(Список2;D5)
• Если количество повторов каждого уникального значения в обоих списках совпадает, то формула в ячейке Е1 =СУММПРОИЗВ(ABS(E5:E34-F5:F34)) вернет 0, т.е. списки являются полностью совпадающими (их заголовки окрашиваются зеленым цветом);
• Если все значения из списка уникальных присутствуют в обоих списках, то формулы в ячейках Е2 и F2 =СЧЁТЕСЛИМН($D$5:$D$34;"*?";E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;"*?";F5:F34;0) вернут 0. Если в ячейке Е1 не 0, а в ячейках Е2 и F2 0, то списки являются частично совпадающими (их заголовки окрашиваются оранжевым цветом)
• Если хотябы одна из вышеуказанных формул (ячейки Е2 и F2 ) возвращают не 0, то списки считаются не совпадающими .

Тестируем

1. В файле примера (диапазоны ( А26:B33 ; А36:B41 ; А44:B49 ) имеется 3 пары списков каждого типа: полностью совпадающие; частично совпадающие; не совпадающие.

2. Вставляя по очереди указанные пары списков в диапазон A5:B19 , результат получим в виде цвета заголовка исходных списков (полностью совпадающие списки имеют заголовки зеленого цвета; частично совпадающие - желтого; не совпадающие - красного). Цвет заголовков списков определяется Условным форматированием .