Подсветка и сравнение двух и более списков
Некоторые пользователи не особо жалуют использование макросов в Excel, поэтому предлагаю рассмотреть сравнение двух списков с помощью условного форматирования и формул. Допустим, что у нас имеется два списка с повторяющимися словами: Самый быстрый и лёгкий способ найти отличия в двух таблицах – это применить условное форматирование. Итак, выделяем оба диапазона удерживая клавишу «Ctrl» и на вкладке Главная – Условное форматирование – Правила выделения ячеек – Повторяющиеся значения выбираем опцию "Уникальные", в результате Excel подсветит все ячейки, где нет повторов. Выбрав вариант "Повторяющиеся", будут выделены совпадения:
Скрытый текст:
Таким способом можно применить оба правила одновременно. Положительное свойство заключается в простоте и наглядности. Отрицательным – совпадения/отличия просто подсвечиваются и всё, поэтому для полного эффекта сравнения необходимо использовать формулы. Рассмотрим следующие примеры. Чтобы получить отличия отдельным списком я пошагово покажу процесс создания такого списка. Для этого вводим в соседней ячейке D2 формулу =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$9;C2)=0;СТРОКА(C2))которая будет проверять количество вхождений с помощью функции СЧЁТЕСЛИ и если оно равно 0, то выводить номер строки для текущего элемента функцией СТРОКА. Для того, чтобы номер ячейки стал абсолютным, т.е. со знаком $, нужно в строке формулы навести курсор на номер и нажать F4. https://cs11.pikabu.ru/post_img/2019...3181747857.jpg Дальше в ячейке F2 используем формулу СТРОКА(F1) https://cs10.pikabu.ru/post_img/2019...3153165642.jpg Затем в ячейку G2 вводим формулу =НАИМЕНЬШИЙ($D$2:$D$10;F2) которая выведет последовательно номера строк от меньшего к большему: https://cs10.pikabu.ru/post_img/2019...6180879068.jpg Так мы получили номера строк отличающихся элементов второго списка от первого. Чтобы извлечь их самих, используем формулу =ИНДЕКС($C$2:$C$10;НАИМЕНЬШИЙ($D$2:$D$10;F2)-1) которая показывает значение из массива-столбца по порядковому номеру: https://cs13.pikabu.ru/post_img/2019...7711152680.jpg Теперь, чтобы избавиться от вспомогательного столбца, вместо диапазона D2:D10 вставим в нашу формулу логическую проверку количества вхождений с помощью функций ЕСЛИ и СЧЁТЕСЛИ, которую мы применили в самом начале: https://cs10.pikabu.ru/post_img/big/...2142483899.jpg Вводим формулу =ИНДЕКС($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));F 10)-1);) Убираем нули в Файл – Параметры – Дополнительно – Показывать нули…и получаем результат. Заменив цифру 0 на 1, мы получим общие значения в списках https://cs12.pikabu.ru/post_img/big/...2163357871.jpg Для поиска совпадений в трёх и более списках проделаем следующее. Сначала озаглавим наши списки, чтобы использовать их в формулах. Для этого выделим оба диапазона вместе с названиями, удерживая клавишу «Ctrl» и на вкладке Формулы – Создать из выделенного в открывшемся окне включим галочку «в строке выше» и жмём ОК: https://cs12.pikabu.ru/post_img/2019...0176787192.jpg Excel даст нашим спискам имена, взяв их из первых строк выделенных диапазонов, т.е. Metal1 и Metal2. Проверить именованные диапазоны можно на вкладке Формулы - Диспетчер имён: https://cs12.pikabu.ru/post_img/big/...5169667334.jpg Здесь же можно впоследствии подкорректировать и размеры диапазонов, если количество элементов в списках будет меняться. Нужная нам формула для поиска и вывода общих элементов в этих двух списках будет выглядеть следующим образом: =ИНДЕКС(metal1;ПОИСКПОЗ(1;СЧЁТЕСЛИ(metal2;metal1)* НЕ(СЧЁТЕСЛИ($H$1:H1;metal1));0)) https://cs9.pikabu.ru/post_img/big/2...1167066860.jpg Плюсом является то, что при увеличении количества списков достаточно будет добавить ещё один именованный диапазон (Metal3) и множитель в нашу формулу-массив проверки совпадений с помощью ещё одной функции СЧЁТЕСЛИ: https://cs12.pikabu.ru/post_img/2019...0191813007.jpg В общем списке будут указаны только те значения, которые являются общими во всех трёх списках, при удалении одного элемента из списка, в общем списке он также исчезнет. Данные примеры не конфликтуют с пустыми ячейками. Формулы на вид кажутся сложными, но всё выполнимо. Можно создать один шаблон с большим диапазоном ячеек и в последующем вставлять в него необходимые списки для проверки. |
Цитата:
|
Цитата:
|
Цитата:
|
kelt, В теме разве sql описывается ?
|
сохранить списки как текст, и в тотал коммандер сравнить файлы по содержимому (либо с помощью diff в линукс)
|
Цитата:
Я выиграл. И я до сих пор не умею пользоваться всеми этими экселевскими функциями. Мне время от времени их показывают, иногда сам гуглю, но затем они вылетают из памяти. |
Цитата:
|
Otkritka, лучше намного быстрее сделать макросом. К тому же список может расширятся:
(на курсах много бесполезной инфы, за которую просят денег капиталисты. Их задача не научит как лучше, а заполнить учебные часы ненужными методиками) Скрытый текст:
|
Текущее время: 14:36. Часовой пояс GMT +5. |
Powered by vBulletin® Version 3.8.5
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd. Перевод:
OOO «Единый интегратор UZINFOCOM»