Сравнение 2-х списков в MS 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, результат получим в виде цвета заголовка исходных списков (полностью совпадающие списки имеют заголовки зеленого цвета; частично совпадающие - желтого; не совпадающие - красного). Цвет заголовков списков определяется Условным форматированием.
Комментарии
Статья хорошая, но вот где файл примера (выделено желтым) ни как найти не могу. Есть ли этот файл и где
Размещено, см. внизу статьи, кнопка Скачать файл примера