Подсветка и сравнение двух и более списков


Некоторые пользователи не особо жалуют использование макросов в Excel, поэтому предлагаю рассмотреть сравнение двух списков с помощью условного форматирования и формул. Допустим, что у нас имеется два списка с повторяющимися словами:Самый быстрый и лёгкий способ найти отличия в двух таблицах – это применить условное форматирование. Итак, выделяем оба диапазона удерживая клавишу «Ctrl» и на вкладке Главная – Условное форматирование – Правила выделения ячеек – Повторяющиеся значения выбираем опцию "Уникальные", в результате Excel подсветит все ячейки, где нет повторов. Выбрав вариант "Повторяющиеся", будут выделены совпадения:
Подсветка и сравнение двух и более списков

Таким способом можно применить оба правила одновременно.Положительное свойство заключается в простоте и наглядности. Отрицательным – совпадения/отличия просто подсвечиваются и всё, поэтому для полного эффекта сравнения необходимо использовать формулы. Рассмотрим следующие примеры. Чтобы получить отличия отдельным списком я пошагово покажу процесс создания такого списка. Для этого вводим в соседней ячейке D2 формулу =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$9;C2)=0;СТРОКА(C2)) которая будет проверять количество вхождений с помощью функции СЧЁТЕСЛИ и если оно равно 0, то выводить номер строки для текущего элемента функцией СТРОКА.Для того, чтобы номер ячейки стал абсолютным, т.е. со знаком $, нужно в строке формулы навести курсор на номер и нажать F4.

Дальше в ячейке F2 используем формулу СТРОКА(F1)

Затем в ячейку G2 вводим формулу =НАИМЕНЬШИЙ($D$2:$D$10;F2) которая выведет последовательно номера строк от меньшего к большему:

Так мы получили номера строк отличающихся элементов второго списка от первого. Чтобы извлечь их самих, используем формулу=ИНДЕКС($C$2:$C$10;НАИМЕНЬШИЙ($D$2:$D$10;F2)-1)которая показывает значение из массива-столбца по порядковому номеру:

Теперь, чтобы избавиться от вспомогательного столбца, вместо диапазона D2:D10 вставим в нашу формулу логическую проверку количества вхождений с помощью функций ЕСЛИ и СЧЁТЕСЛИ, которую мы применили в самом начале:

Вводим формулу =ИНДЕКС($C$2:$C$10;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$9;$C$2:$C$10)=0;СТРОКА($C$2:$C$10));F2)-1) Чтобы формула массива заработала нажимаем сочетания клавиш Ctrl+Shift+Enter и протягиваем формулу вниз. После этого столбец D можно удалить.
Добавим красоты спрятав ошибку #ЧИСЛО!, возникающие в избыточных ячейках. Добавляем к формуле функцию =ЕСЛИОШИБКА, получается:

=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$10;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$9;$C$2:$C$10)=0;СТРОКА($C$2:$C$10));F10)-1);)
Убираем нули в Файл – Параметры – Дополнительно – Показывать нули… и получаем результатЗаменив цифру 0 на 1, мы получим общие значения в списках

Для поиска совпадений в трёх и более списках проделаем следующее. Сначала озаглавим наши списки, чтобы использовать их в формулах. Для этого выделим оба диапазона вместе с названиями, удерживая клавишу «Ctrl» и на вкладке Формулы – Создать из выделенного в открывшемся окне включим галочку «в строке выше» и жмём ОК:

Excel даст нашим спискам имена, взяв их из первых строк выделенных диапазонов, т.е. Metal1 и Metal2. Проверить именованные диапазоны можно на вкладке Формулы - Диспетчер имён:

Здесь же можно впоследствии подкорректировать и размеры диапазонов, если количество элементов в списках будет меняться.Нужная нам формула для поиска и вывода общих элементов в этих двух списках будет выглядеть следующим образом:=ИНДЕКС(metal1;ПОИСКПОЗ(1;СЧЁТЕСЛИ(metal2;metal1)*НЕ(СЧЁТЕСЛИ($H$1:H1;metal1));0))

Плюсом является то, что при увеличении количества списков достаточно будет добавить ещё один именованный диапазон (Metal3) и множитель в нашу формулу-массив проверки совпадений с помощью ещё одной функции СЧЁТЕСЛИ:

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




Добавить комментарий

Автору будет очень приятно узнать обратную связь о своей новости.

Похожие новости

Получение списка файлов в папке Получение списка файлов в папке
В Excel, путём проведения нехитрых махинаций, имеется возможность получения списка файлов, находящихся в папке компьютера. Для этого копируем путь к файлам в папке и вставляем его в нужную ячейку, при Автор: Veseliy.4el

Суммирование в Excel через одну, две, три ячейки Суммирование в Excel через одну, две, три ячейки
Автор: Veseliy.4el Всем известен способ подсчёта данных в Excel путём элементарного сложения ячеек =C2+C4+C6, либо с помощью функции =СУММ(C2;C4;C6), но если таблица большая, то такой способ выведет из себя даже самого терпеливого. Поэтому предлагаю

Как создать прозрачную папку на рабочем столе Как создать прозрачную папку на рабочем столе
Для тех, кому есть, что спрятать от посторонних глаз, пост будет полезен. В эту папку можно скидывать секретный материал и никто не догадается, что и где. 1. Создаём на рабочем столе новую папку. 2. Выбираем пункт "Переименовать", зажимая клавишу "Alt" и

Как восстановить утраченные данные на Micro SD флешке? Как восстановить утраченные данные на Micro SD флешке?
Такие носители, как флешки и карты памяти, часто подвергаются различным повреждениям. Может случиться потеря информации либо случайное форматирование носителя данных. Поэтому есть способы восстановления данных с флешек. Вам понадобится: - компьютер; -

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

120 горячих клавиш 120 горячих клавиш
120 горячих клавиш Чтобы работать за компьютером было проще и быстрее, давно придумали горячие клавиши. И если вы знаете еще не все полезные комбинации, то вот отличная шпаргалка. Операционная система Windows Ctrl+C — копировать. Для того чтобы увидеть

Отличия Mac и компьютеров на Windows Отличия Mac и компьютеров на Windows
Отличия Mac и компьютеров на Windows

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

Как достать для редактирования текст из файла PDF? Как достать для редактирования текст из файла PDF?
Очень часто, в процессе офисной, да и не тоько, работы приходится сталкиваться с такой проблемой, как получение текста в формате PDF, который не позволяет его редактировать, а редактировать надо. Что делать? Перепечатывать текст руками? Ну вот и не