Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Сравнить два ОЧЕНЬ больших диапазона
 
#1
23.03.2017 12:56:20
Есть 2 диапазона по 630 000 строк
Надо находить уникальные значения в Д2, которых нет в Д1, и выводить их в отдельный диапазон.
Какой вариант решения задачи будет максимально быстродейственным? Использовал СЧЕТЕСЛИ, стандартную фунцкию - показать повторяющиеся - такие способы вешают систему намертво.
 
 
 
#2
23.03.2017 12:58:44
Использовать макросы.
 
 
 
#3
23.03.2017 13:00:08
Можно попробовать с помощью Power Query. Сделайте файл-пример с небольшим количеством данных. В примере укажите исходные данные и результат.
 
 
 
#4
23.03.2017 13:00:45
Макрос на словаре и массивах.
 
 
 
#5
23.03.2017 13:44:08
Файл-пример приложил.
 
 
 
#6
23.03.2017 14:21:14
Нужно сравнивать в таблицах все 4 столбца, чтобы сделать вывод, есть совпадение или нет? То есть G с A, H с B и т.д.?
Изменено: Karataev - 23.03.2017 14:24:32
 
 
 
#7
23.03.2017 14:23:59
Сравнить только 2 столбца, D и J
 
 
 
#8
23.03.2017 14:24:20
По поводу уникальности. Можно ведь сначала найти все новые строки, скопировать их в столбец "M", а потом просто удалить кнопкой: вкладка "Данные" - "Удалить дубликаты"? Или нужно именно сначала проверить, является ли строка уникальная в диапазоне G:J, а только потом уже проводить сравнение?
 
 
 
#9
23.03.2017 14:26:28
Цитата
Karataev написал:
Можно ведь сначала найти все новые строки, скопировать их в столбец "M",
Пожалуй, можно и так, если я вас верно понял. Как это сделать?
 
 
 
#10
23.03.2017 14:33:34
Как вариант, можно сложить два больших массива А и В в один массив С. При этом каждому массиву добавить столбец с уникальным признаком, А и В. Затем создать сводную таблицу на основе массива С. Так как объем большой, делать все нужно в Power Query и Power Pivot или сократить объем массива через удаление дубликатов.
 
 
 
#11
23.03.2017 15:49:49
Этот способ должен работать, начиная с "Excel 2010". На Mac OS вроде не будет работать.

Способ с помощью Power Query.
Диапазоны A:D и G:J преобразованы в умные таблицы (вкладка "Главная" - Форматировать как таблицу).
PQ использует имена умных таблицы и заголовки столбцов D и J, поэтому не изменяйте их.
Добавляйте данные в столбцы A:D и G:J. Затем вкладка "Данные" - верхняя кнопка кнопки "Обновить все". После этого обновятся данные в столбце "M".
Т.к. данных у Вас много, то результат будет не сразу, надо подождать. Внизу, в строке состояния будет информация, что происходят какие-то действия.
В столбце "M" будут только уникальные данные, так что не надо потом удалять дубликаты.
 
 
 
#12
23.03.2017 17:22:22
На основе решения от KuklP (сравнение больших объемов данных).
 
 
 
#13
23.03.2017 18:32:04
Всем спасибо, решение от Alex Matashev помогло идеально.
 
 
 
#14
23.03.2017 18:34:49
512metrov, а чем решение из поста 11 не подошло?
 
 
 
#15
23.03.2017 21:03:10
Сравниваю решение с PQ из поста 11 и макрос со словарем (сам сделал). Макрос со словарем быстро срабатывает, а PQ дольше(около 10 минут). При этом я убрал из PQ приведение к нижнему регистру (может так быстрее). Предположу, что я плохой запрос сделал. Я заполнил столбец "D" до 1 млн строк уникальными данными, а в столбце "J" строк 10.
Изменено: Karataev - 23.03.2017 21:04:52
 
 
 
#16
24.03.2017 13:30:07
Цитата
Karataev написал:
а PQ дольше(около 10 минут).
У меня в Excel 2010 32bit - значение ячеек строка вида &Hx, где x число от 0 до 10000000, для 800000 строк в где проверялось отсутствие и 650000 строк в тестовой - отработало за 18 секунд. Что я делаю не так? Вы же, смотрю, аж в двух темах (ещё и тут) отписались о медленной работе Power Query.
Изменено: Андрей VG - 24.03.2017 13:30:45
 
 
 
#17
24.03.2017 13:31:35
Цитата
Андрей VG написал:
Что я делаю не так?
:D
 
 
 
#18
24.03.2017 13:37:18
Андрей VG, в этой теме же есть файл-пример, сделайте на нем тест. Создайте в столбце "где искать" 1 млн строк, в столбце "что искать" можно оставить столько, сколько есть.
А то, что Вы выложили файл пример, он вообще не имеет отношения к теме на первый взгляд, я не понимаю, что с ним делать.
Я в столбце "где искать" создал 1 млн уникальных данных у себя, когда тестировал.
Изменено: Karataev - 24.03.2017 13:39:06
 
 
 
#19
24.03.2017 15:22:55
Цитата
Karataev написал:
н вообще не имеет отношения к теме на первый взгляд,
Почему это не имеет отношения? На лист Report выводятся строки таблицы с листа baseTable, которые отсутствуют в таблице на листе testTable. (в трёх ячейках трёх строк H заменено на A).
 
 
 
#20
24.03.2017 18:32:19
Андрей VG, я неправильно написал. Я добавил 1 млн строк не только в столбец "где искать", но и в столбец "что искать". И все данные уникальные (чтобы нельзя было удалить дубликаты). Если сделать 1 млн строк в столбце "где искать", а в столбце "что искать" оставить столько, сколько есть, то и мой запрос быстро работает.
Сейчас буду тестировать Ваш запрос, когда 1 млн строк в анализируемых столбцах.
 
 
 
#21
24.03.2017 19:03:50
Андрей VG, да Ваш запрос быстро отработал: 1 млн строк "где искать" и 1 млн строк "что искать", в обоих столбцах уникальные данные. Отработало за 2 минуты (у меня слабый компьютер).
Только нужно еще добавить в запрос "сравнение без учета регистра". Возможно эта операция замедлит работу запроса намного.
 
 
 
#22
24.03.2017 23:23:20
Цитата
Karataev написал:
еще добавить в запрос "сравнение без учета регистра"
Да, без проблем Table.Join - последний параметр как раз задаёт список функций сравнения на равенство соответствующих столбцов, и не думаю, что это будет существенно медленнее.
 
 
 
#23
24.03.2017 23:59:37
Андрей VG, я читал про этот аргумент, но не смог сделать. Я так понял, что можно сделать, только привязываясь к какому-то языку. Но хотелось бы без привязки.
 
 
 
#24
25.03.2017 13:44:40
Цитата
Karataev написал:
но не смог сделать.
Тоже не вышло :(  Получаю ошибку "Expression.Error: Локальная оценка Table.Join или Table.NestedJoin с функциями сравнения равенства ключей не поддерживается.". В более ранних версиях её не было (по описанию функций на август 2015). В интернете описаний фактически нет. Остаётся, видимо, только приведение в нижний регистр через Table.TransformColumns.
Приношу свои извинения за поспешность необоснованных утверждений.
 
 
 
#25
28.03.2017 19:52:44
Коллеги, не перемудрили?

Код
// Запрос1
let
    Что = Excel.CurrentWorkbook(){[Name="Table2"]}[Content][Что искать],
    Где = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][Где искать],
    Diff = List.Difference(Что,Где,Comparer.OrdinalIgnoreCase),
    Reslut = #table({"Result"},{List.Distinct(Diff)})
in
    Result
F1 творит чудеса
 
 
 
#26
28.03.2017 20:49:56
Сейчас проверил, два списка с 1 миллионом уников каждый, расхождение (без учета регистра) 499772 элемента, вместе с выгрузкой на лист примерно 6,5 секунд.
С учетом регистра примерно 750000 найдено, в среднем 7-7,5 секунд.
Запрос не в фоновом режиме, оформление таблицы отключено.
Win8.1 64Bit, 4Gb, 2 ядра, Excel 2013 32bit.
F1 творит чудеса
 
 
 
Страницы: 1
Наверх