Какие элементы у 2-х списков в EXCEL отличаются?

Пусть дано 2 списка значений. Определим, какие элементы первого списка НЕ содержатся во втором и какие элементы второго списка НЕ содержатся во первом .


Пусть Список1 находится в диапазоне A7:A22 , Список2 - в диапазоне B7 : B14 .

Определим какие элементы из первого списка отсутствуют во втором (см. Файл примера ).

Решение основано на формуле =НЕ(СУММПРОИЗВ((A7=$B$7:$B$22)+0)) , которая рассчитывается для каждого элемента первого списка. Если элемент первого списка отсутствует в Списке2, то формула возвращает ИСТИНА, в противном случае - ЛОЖЬ.

Эту же формулу можно использовать и в Условном форматировании .

СОВЕТ: Более подробное сравнение 2-х списков описано в статье Сравнение 2-х списков .


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

Аноним, 12 апреля 2016 г.
Спасибо! Отличное решение!
Аноним, 6 мая 2020 г.
Спасибо! Не хватает только подробный объяснений, как что работает.
Михаил, 6 мая 2020 г.
Добрый день, формула =НЕ(СУММПРОИЗВ((A7=$B$7:$B$22)+0)) работает так: 1) каждое значение из столбца А (в данном случае А7) сравнивается сразу со всеми значениями столбца В. Это выполняет формула A7=$B$7:$B$22 2) в результате получим массив {ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}, где первое значение ИСТИНА соответствует тому, что значение из А7 есть в столбце В (ячейка В7), второе значение - ЛОЖЬ значит, что значение в ячейке В8 не равно ячейке А7 и т.д. 3) Логические значения ЛОЖЬ и ИСТИНА соответствуют числовым 0 и 1. Любая арифметическая операция приводит к преобразованию логического значения в числовое, я использовал сложение с 0, но можно и умножение на 1. в итоге получим массив {1:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0} 4) сложение всех чисел массива даст либо 0, либо число больше 0. Ноль соответствует тому, что значения в списке нет, любое значение больше 0 - значение из А7 в списке присутствует и даже может не единожды 5) вместо СУММ использована СУММПРОИЗВ, т.к. в противном случае пришлось бы использовать формулу массива 6) НЕ() - логическая функция, она переводит ИСТИНА в ЛОЖЬ и наоборот. В нашем случае вместо логических ИСТИНА/ЛОЖЬ на вход функции подается число 0; 1; 2 и т.д. Функция НЕ() переводит 0 в 1 (ИСТИНА), а любое число больше 0 в 0 (ЛОЖЬ). 7)Таким образом, на утверждение "Значение из ячейки А7 (текст1) отсутствует в столбце В?" формула формула вернет ЛОЖЬ, т.к. оно есть. А для значения из А9 - ИСТИНА, т.е. его нет.
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved