Новости:

Новая редакция правил форума: 2.4. Если вопрос или ответ содержится во вложенном файле, все-равно кратко описывайте в сообщении вопрос или суть решения. Это необходимо, чтобы тему можно было найти через поиск.

Главное меню

Сравнение списков и поиск отсуствующих строк

Автор Exc_fun, 02.03.2017, 08:19

« назад - далее »

Exc_fun

Имеется 2 списка одних и тех же mp3 файлов. Первый создан примерно 10 лет назад, когда закидывал эти песни на хард диск. И второй, из тех же файлов, составил только что. Сам хард диск, на котором лежат эти песни, вызывает у меня недоверие. Он часто сбоил и зависал (за эти годы) и из 1000 файлов, к примеру, осталось 800. Поэтому, хочу сравнить списки и пометить (выделить цветом) только недостающие файлы во втором, который создал только что. Причем, сравнение должно проводиться по 10 колонкам (от А до К). Как можно это быстро выполнить?

IKor

Это можно выполнить при помощи Условного форматирования при использовании функций СЧЁТЕСЛИ(), ПОИСКПОЗ(), ЕОШИБКА() и других.
Когда (и если) Вы найдете возможность продемонстрировать документ Excel с фрагментом исходных данных (не содержащим конфиденциальной информации), а также примером желаемого результата, я уверен, Вам быстро подскажут недостающие детали ;)

Exc_fun

#2
Кнопка цитирования не для ответа [МОДЕРАТОР]

2 идентичных списка. Во втором отсуствуют 6 песен (что есть в первом). Oригинальные списки содержат: один 16000 строк, другой примерно 12000, колонки А-К заняты, как тут.  Нужно сравнить 2 списка, недостающие во 2-м списке строки сначaла выделить в 1-м списке и потом отобразить отдельно только их.

IKor

Посмотрите прилагаемые файлы - подходит?

Exc_fun

#4
Цитата: IKor от 13.03.2017, 10:01
Посмотрите прилагаемые файлы - подходит?
Извините, что зашел сюда так поздно. Посмотрю таблицы и выскажу свое мнение. На первый взгляд достойная работа. Благодарю.
Пока на просторах интернета мне попалось другое решение (сравнение по 7 столбцам).
=ЕСЛИ(СУММПРОИЗВ(($A2='C:\[Tab2.xls]Лист1'!$A$1:$A$33)*($B2='C:\[Tab2.xls]Лист1'!$B$1:$B$33)*($C2='C:\[Tab2.xls]Лист1'!$C$1:$C$33)*($D2='C:\[Tab2.xls]Лист1'!$D$1:$D$33)*($E2='C:\[Tab2.xls]Лист1'!$E$1:$E$33)*($F2='C:\[Tab2.xls]Лист1'!$F$1:$F$33)*($G2='C:\[Tab2.xls]Лист1'!$G$1:$G$33));"есть";"нет")
Хорошая формула, но мне кажется здесь не учтено то, что таблицы главного (1) и связанного (2) с ним документа могут находится на разных страницах (в моем Экселе 2003 г. всего 3 страницы по более 64000 строк). Скажем, основная таблица на третьей странице, а сравниваемая - на 1-й или 2-й. И по этой причине замечаю ошибки. Но возможно ошибка в другом. Даже в укороченном виде совпадения и отличия находит неверно. Гляньте (на вложения). После переименования файлов вроде результат показывает правильно. Но почему-то невозможна сортировка результата по столбцу L. Эту формулу можно как-то скорректировать?

IKor

2 Exc_fun
Я посмотрел предлагаемую Вами формулу и не нашел в ней принципиальных преимуществ перед предложенным мной решением (которое также несложно свернуть в один столбец) - предлагаю Вам протестировать на большом документе оба варианта и выбрать наиболее подходящий :) Не сочтите за труд - отпишите здесь, какая понравилась Вам больше.

К сожалению, для проверки наличия совпадений в несвязанных диапазонах формулы придется дублировать для каждого из них отдельно:
В вашем примере для основной таблицы (на третьем листе) придется подготовить пару столбцов для независимого поиска совпадений строк основной таблицы в каждой из дополнительных таблиц (расположенных на первом и втором листах).

Для возможности сортировки данных предлагаю вам заменить формулы в столбце L их значениями (результатами вычислений):
выделить весь столбец => скопировать => вставить как значение
А затем сортировать.
Если не хотите терять формулы, то вставляйте в соседний столбец :)

Exc_fun

IKor, да, я посмотрю. Формула, которую я выложил, мне нравится. Но ее большим минусом является то, что выполняет операцию очень медленно (порой кажется, что комп завис и пришло время его выключать). Уже не говоря о сравнении ячеек и сортировке, даже простое открытие документов отнимает много времени т.к. по ходу идет сравнение с другой.

vikttur

Ограничение на размер файлов - 1 Мб. Но это не значит, что нужно подтягивать под эту границу. Неужели для примера нужен файл в 917 кБ?!

Exc_fun

IKor, хотел выполнить сравнение этих двух таблиц со своей формулой (привел выше). Оба документа расположены в D:\
http://www.mediafire.com/file/oj2uxcs8k53u3j9/Matieu1.xls
http://www.mediafire.com/file/y3x7ski5muhz4r9/Matieu2.xls
Сравниваю по трем столбцам А, В, Е. Kоличество строк указал 1113 (по большой таблице). Не справляется с задачей и ваша формула. Что тут неправильно (могли-бы проверить)?

IKor

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

Тем не менее для "облегчения файла" и ускорения процесса сравнения предлагаю Вам заменить значениями формулы конкатенации (сцепления строк => в моем примере они находятся в столбце M) - в этом случае они перестанут пересчитываться при открытии документа.
А саму формулу сравнения (СЧЁТЕСЛИ() в столбце N) оставить как она есть.

Кроме того, возможно (если позволяют ограничения MS Excel'я) будет удобнее собрать все листы в один документ...

Надеюсь, что эти советы Вам помогут.

Exc_fun

#10
Не нужно бестолково вставлять целое сообщение в цитату. Кнопка цитирования не для ответа [МОДЕРАТОР]

Выше мы рассматривали сравнение двух таблиц. А что, если одинаковые строки есть в одной таблице? Как их быстро пометить и удалить?

cheshiki1

Цитата: Exc_fun от 28.08.2017, 08:06
Как их быстро пометить и удалить?
1. счетесли или счетеслимн (2007 и выше) в отдельном столбце, фильтр, выделить, удалить.
2. Данные - работа с данными - удалить дубликаты. (2007 и выше) в 2003 где то в другом месте.