Моё меню Общее меню Пользователи Правила форума Все прочитано
Вернуться   uForum.uz > ИКТ и телеком > IT-индустрия > Софт
Знаете ли Вы, что ...
...нарушения правил форума наказываются. Старайтесь их не нарушать.
<< Предыдущий совет - Случайный совет - Следующий совет >>

Софт Программки, утилиты, операционки - все, что превращает мертвую груду металла в компьютер


Ответить

 
Опции темы Опции просмотра
Старый 30.10.2019 13:16   #1  
Аватар для Otkritka
Оффлайн
Дизайн студия открыток
Поздравление пользователей
AKA:Otkritka
Сообщений: 4,528
+ 115  1,979/1,004
– 602  199/178

UzbekistanОтправить сообщение для Otkritka с помощью ICQОтправить сообщение для Otkritka с помощью AIMОтправить сообщение для Otkritka с помощью MSNОтправить сообщение для Otkritka с помощью YahooОтправить сообщение для Otkritka с помощью Skype™LiveJournalМой КругАккаунт на TwitterМой мирFacebook
Information Подсветка и сравнение двух и более списков

Некоторые пользователи не особо жалуют использование макросов в 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));F 10)-1)

Убираем нули в Файл – Параметры – Дополнительно – Показывать нули…и получаем результат. Заменив цифру 0 на 1, мы получим общие значения в списках



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



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



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



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



В общем списке будут указаны только те значения, которые являются общими во всех трёх списках, при удалении одного элемента из списка, в общем списке он также исчезнет. Данные примеры не конфликтуют с пустыми ячейками. Формулы на вид кажутся сложными, но всё выполнимо. Можно создать один шаблон с большим диапазоном ячеек и в последующем вставлять в него необходимые списки для проверки.
__________________
С мужчиной должно быть хорошо, плохо жить я и сама смогу.
Ответить 
Старый 30.10.2019 16:43   #2  
Аватар для Немой
Оффлайн
Сообщений: 1,732
+ 301  541/376
– 0  31/23

Uzbekistan
Цитата:
Сообщение от Otkritka Посмотреть сообщение
Некоторые пользователи не особо жалуют использование макросов в Excel, поэтому предлагаю рассмотреть сравнение двух списков с помощью условного форматирования и формул.
Макросом намного проще и быстрее.
Ответить 
Старый 30.10.2019 16:58   #3  
Аватар для Otkritka
Оффлайн
Дизайн студия открыток
Поздравление пользователей
AKA:Otkritka
Сообщений: 4,528
+ 115  1,979/1,004
– 602  199/178

UzbekistanОтправить сообщение для Otkritka с помощью ICQОтправить сообщение для Otkritka с помощью AIMОтправить сообщение для Otkritka с помощью MSNОтправить сообщение для Otkritka с помощью YahooОтправить сообщение для Otkritka с помощью Skype™LiveJournalМой КругАккаунт на TwitterМой мирFacebook
Цитата:
Сообщение от Немой Посмотреть сообщение
Макросом намного проще и быстрее.
Создайте тему и опишите процесс, думаю многим пригодиться.
__________________
С мужчиной должно быть хорошо, плохо жить я и сама смогу.
Ответить 
"+" от:
Старый 30.10.2019 17:01   #4  
Аватар для kelt
Оффлайн
Павлин
Сообщений: 2,263
+ 594  458/337
– 1,250  976/401

Switzerland
Цитата:
Сообщение от Otkritka Посмотреть сообщение
Создайте тему и опишите процесс, думаю многим пригодиться.
а как это в sql реализовать. Выложите готовый пример запросов.
Ответить 
Старый 30.10.2019 17:07   #5  
Аватар для Otkritka
Оффлайн
Дизайн студия открыток
Поздравление пользователей
AKA:Otkritka
Сообщений: 4,528
+ 115  1,979/1,004
– 602  199/178

UzbekistanОтправить сообщение для Otkritka с помощью ICQОтправить сообщение для Otkritka с помощью AIMОтправить сообщение для Otkritka с помощью MSNОтправить сообщение для Otkritka с помощью YahooОтправить сообщение для Otkritka с помощью Skype™LiveJournalМой КругАккаунт на TwitterМой мирFacebook
kelt, В теме разве sql описывается ?
__________________
С мужчиной должно быть хорошо, плохо жить я и сама смогу.
Ответить 
Старый 30.10.2019 17:29   #6  
Real ID Group
Аватар для Азизбек Кадыров
Оффлайн
Uzinfocom
AKA:censor2005
Сообщений: 1,279
+ 3,546  939/425
– 40  26/25

UzbekistanМой мирFacebook
сохранить списки как текст, и в тотал коммандер сравнить файлы по содержимому (либо с помощью diff в линукс)
__________________
http://lugat.uz/ - переводчики и словари (онлайн, Telegram и Android версии)
Ответить 
Старый 30.10.2019 19:16   #7  
Аватар для Немой
Оффлайн
Сообщений: 1,732
+ 301  541/376
– 0  31/23

Uzbekistan
Цитата:
Сообщение от Otkritka Посмотреть сообщение
Создайте тему и опишите процесс, думаю многим пригодиться.
У меня как-то пари было на эту тему. Решали задачу в экселе на скорость.
Я выиграл.
И я до сих пор не умею пользоваться всеми этими экселевскими функциями. Мне время от времени их показывают, иногда сам гуглю, но затем они вылетают из памяти.
Ответить 
Реклама и уведомления
Старый 31.10.2019 11:52   #8  
Аватар для spykee
Оффлайн
дом
Сообщений: 5,817
+ 1,272  1,679/1,120
– 203  271/231

Uzbekistan
Цитата:
Сообщение от Азизбек Кадыров Посмотреть сообщение
сохранить списки как текст, и в тотал коммандер сравнить файлы по содержимому (либо с помощью diff в линукс)
Они точные соответствия находят. Если одинаковые слова в разных строчках находятся могут выдать как различие.
__________________
Либералоиды и агенты: Инесса-MONSTER666, kelt, Немой
Ответить 
Старый 31.10.2019 12:08   #9  
Аватар для YangierBola
Оффлайн
Сообщений: 1,720
+ 511  547/394
– 27  42/34

Uzbekistan
Otkritka, лучше намного быстрее сделать макросом. К тому же список может расширятся:
(на курсах много бесполезной инфы, за которую просят денег капиталисты. Их задача не научит как лучше, а заполнить учебные часы ненужными методиками)
Скрытый текст:
' где сравнивать
Dim gh As Variant, x As Variant, y As Variant
Set gh = Range(Sheets("gh").Cells(начало списка, столбец), Sheets("gh").Cells(Rows.Count, столбец).End(xlUp))
' что сравнивать
Dim ci As Variant
Set ci = Range(Sheets("другой лист").Cells(начало списка, столбец), Sheets("другой лист").Cells(Rows.Count, столбец).End(xlUp))
For Each x In ci
For Each y In gh
If x = y Then
' окрасить ячейку в нужный цвет
y.Interior.ColorIndex = 43
' скопировать значение рядом
y.Offset(0, 1) = x.Offset(0, 1).Value
' если шрифт найденной ячейки такой же, то окрасить в тот же
If Range(Sheets("другой лист").Cells(x.Row, столбец), Sheets("i").Cells(x.Row, столбец)).Interior.color = 16777215 Then
Range(Sheets("другой лист").Cells(x.Row, столбец), Sheets("i").Cells(x.Row, столбец)).Interior.color = 16777215
End If
End If
Next y
Next x
Ответить 
"+" от:
Ответить
Опции темы
Опции просмотра




Powered by vBulletin® Version 3.8.5
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd. Перевод: zCarot
Advertisement System V2.5 By Branden
OOO «Единый интегратор UZINFOCOM»


Новые 24 часа Кто на форуме Новички Поиск Кабинет Все прочитано Вверх