Мне часто приходиться сталкиваться с тормозными файлами.
Ситуация такая, есть почти 40 тысяч основных средств. По ним есть некие параметры, например амортизация.
Ведутся параллельно два разных оракла независимо друг от друга, в оба оракла вносят параллельно данные по основным средствам и периодически делают сверку данных под данным ораклам.
К примеру на конец месяца проверяют, что в обоих ораклах один и тот-же список основных средств и амортизация по ним одинаковая. Т.к. амортизация вычисляется самим ораклом на основе некоторых параметров и ещё по каким-то причинам, эта амортизация в двух ораклах может немного отличаться и каждый месяц нужно делать сверку, находить разницу и эту разницу заливать в один из ораклов, чтобы амортизация выравнялась.

Это была предыстория.

А вот уже реальная задача.
Есть две таблицы, в каждой порядка 40 тысяч строк.
Каждая строка соответствует некоему объекту с определенным инвентарным номером, но при этом по каждому объекту может быть как одна строка, так и несколько.
Так вот в каждой из таблиц есть инвентарный номер, есть амортизация.
Нужно сделать сверку амортизаций , для этого мы вытаскиваем данные с одного листа с помощью СУММЕСЛИ и ВПР на другой лист, связь делается по столбцу с инвентарным номером. И потом вычисляем разницу.
Задача дополнительно усложняется тем, что по некоторым объектам может быть по несколько строчек, поэтому такие строки надо объединять в одну, а суммы амортизации соответственно суммировать.

Так вот, когда у вас в файле используется около 40 тысяч ячеек с функцией ВПР (а ещё хуже если использовать СУММЕСЛИ), ссылающиеся на диапазон из порядка 40 тысяч строк, то это тоже долго считается. После любых манипуляций с фильтрами все 40 тысяч формул будут каждый раз пересчитываться и это в итоге занимает много времени.

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

Замечу ещё, что с помощь сводной таблицы такую таблицу сгруппировать не получится даже в Excel 2007, а в более ранних версиях тем более, т.к. там есть ограничение на количество разных элементов (вроде их должно быть не больше 10 тысяч, точно не знаю), здесь-же разных инвентарных номеров более 30 тысяч.

P.S. В данном сообщении я просто привёл пример тормозного файла, решение я не ищу.

Даже сделал небольшой такой тормозной файлик , ссылка будет доступна 30 дней с момента последнего обращения к ней. Т.к. его размер около 2 Мб, пришлось выложить его на файловый сервер.
Кому не лень будет скачать, поэксперементируйте с фильтром на листе 1 по столбцу C.
Даже у меня на Intel Core 2 DUO E8400 3000MHZ дикие тормоза. Если в третьем столбце вместо ВПР использовать СУММЕСЛИ (т.к. в реальной задаче предполагается, что один и то-же инвентарный номер может встречаться несколько раз), то тормоза будут ещё жёстче. А если ещё и несколько таких столбцов, то вобще пипец. Лет 5 назад на реальной задаче были ситуации, когда по 40 минут приходилось ждать после каждой операции с фильтром.

ZVIZVIDjon Player,
Ваши данные макросом обрабатываются за 1.2 секунды.
Выложил здесь (1.152 KB) : ZVI_FastComparing.zip
Забыл добавить, что данные с повторяющимися ID допускаются в обоих списках.
Погоняйте на разных данных, если что не так - отпишите, подправлю.Что касается непосредственно файла с тормознутыми данными, то он был лишь как пример файла который будет тормозить при работе с фильтрами. Реально там две таблицы практически равны, только несколько ячеек я руками изменил, их и выявила программа. Так-же в ней не было повторяющихся ID.

Поэтому я попробовал вашу программу на реальных данных. На реальных данных она так-же сделала расчёт за 1 секунду. Из замечаний я бы сделал парочку.
1. Видимо из-за проблем со структурой хранения самих дробных чисел, по некоторым данным которые на самом деле равны (скорее всего по таким объектам более одной строки) выдало расхождения вида -3,63797880709171E-12, что по сути тот-же ноль, но на самом деле не ноль. Не знаю, можно ли как-то обойти эту ошибку.
2. В текущем исполнении сверку можно делать лишь если данные состоят из 2 столбцов, идентификатора и суммы. Чаще всего этого достаточно, но иногда в моём случае бывает, что данные должны содержать более двух столбцов, к примеру идентификатор и пару столбцов с суммами, к примеру стоимостью и амортизацией. При этом сверка может вестись как одновременно по нескольким столбцам, так и по одному столбцу, но при этом в результатах сверки должны присутствовать все столбцы (в том числе которые и не сверялись, по ним соответственно должна выводиться сгруппированные суммы).

Второй пункт объяснил слишком сложно, попробую более конкретно на примере.
Допустим есть две таблицы Таблица 1 и Таблица 2. К примеру в таблице 1 три столбца, ID, стоимость, амортизация. В таблице 2 два столбца, ID и амортизация.
Причем по некоторым объектам ID может встречаться по несколько раз, соответственно по ним данные надо просто группировать в одну строчку по ID, а суммы просто складывать в одну строчку и лишь потом делать сверку. В данном конкретном задании в качестве решения должен быть такой результат. Должна быть некая табличка, где первые три столбца соответственно 3 столбца из Таблицы 1 (бучем считать её как основной, т.к. после выявления расхождений, бухгалтера будут вносить корректировки в Оракл сответствующий 1 таблице, данные во втором Оракле не корректируются), при этом строки уже сгруппированы. В 4-ом столбце беруться данные по амортизации из таблицы 2 соответствующие ID в данной строке. В 5-ом столбце разница между амортизациями.
При этом может быть два варианта вывода. Первый как в вашем файле, т.е. выводятся только строки по которым столбец разницы выявил расхождение.
Второй вариант, выводятся все строки, какие есть в таблице 1, независимо есть там разница или нет.

P.S. Написал так много, что мог и в чём-то ошибиться, заметите неточность или непонятки, спрашивайте.

PPS. Так как реально эти сверки нужны не мне, то сложно говорить о том, что нужно бухгалтерам и прочим.

А конкретно бы меня ваш вариант устроил, единственное, что бы не было проблем с округлениями на миллионные доли и чтобы было как-то видно случаи, когда в одной таблице есть объект с номером ID, а в другой его нет. Это может отображаться в виде комментария в соседнем столбце. Т.е. рядом со столбцом с разницей писать, что данный объект в первой (или во второй) таблице отсутствует.

ZVIZVIУчёл все пожелания, для удобства добавил еще и сортировку результата по 3-м полям.
Время обработки 2-х таблиц с 65535 уникальными строками в каждой (65536 уникальных всего) составляет 2.5 секунды.
Вторая версия здесь (1.324 KB): http://www.4shared.com/file/200785826/35fe95d2/ZVI_FastComparing_02.html] ZVI_FastComparing_02.zip
Проверяйте на реальных данных.
Пожелание "при этом в результатах сверки должны присутствовать все столбцы (в том числе которые и не сверялись, по ним соответственно должна выводиться сгруппированные суммы)" сначала сделал, а потом посчитал неправильным, так как тогда должны быть одинаковые структуры обеих таблиц.Смотря что понимается под структурой, если то, что количество столбцов и их название должно быть одинаковое, то в этом проблемы нет. Просто берется весь набор столбцов с первого и второго листа. Причем первым идёт поле с ID в одном экземпляре. А потом последовательно оставшиеся столбцы первой таблицы и столбцы второй таблицы (кроме ID), а после всех этих столбцов можно добавить столбец сверочный, показывающий разницу по тем столбцам которые надо сверить (ваш последний вариант ещё не посмотрел, поэтому пока не знаю, как он у вас задан), а затем ещё один столбец с комментарием, который пуст, когда заданный ID есть в обоих таблицах, либо там написано что-то типа того, что в таблице 1 (или 2, по ситуации) отсутствует данный объект.

P.S. Сейчас посмотрю ваш код, смущает время вашего сообщения 5 утра, надеюсь что это лишь из-за разницы поясов, а не потому что всю ночь сидели и писали программу.

ZVIУчёл все пожелания, для удобства добавил еще и сортировку результата по 3-м полям.
Время обработки 2-х таблиц с 65535 уникальными строками в каждой (65536 уникальных всего) составляет 2.5 секунды.
Вторая версия здесь (1.324 KB): http://www.4shared.com/file/200785826/35fe95d2/ZVI_FastComparing_02.html] ZVI_FastComparing_02.zip
Проверяйте на реальных данных.Провёл сверку тех данных, которые сегодня сам сверял с утра.

Скопировал результат вашей и своей сверки на один лист, одинаково их отсортировал, всё совпало.
Есть небольшие замечания.
В столбце New появляется New как я понял для тех записей, которые есть в таблице 2, но нет в таблице 1.
Зато записи, которые есть в таблице 1, но их нет в таблице 2, вообще не отображаются.
Возможно это потому, что я упоминал про то, что первая таблица главная.
Лучше всё таки отображать весь набор записей с обоих таблиц, а в столбце NEW (только его как-то по другому назвать, хотя можно и так) писать комментарий, что данная запись отсутствует в 1-ой таблице (или во второй). Только придумать какую-то более короткую формулировку, например "нет в таблице 1" ("нет в таблице 2").
Ну и небольшое замечание, что на листе Compare выведены лишь столбцы B, а столбец А не выведен, а без вывода столбца A вроде как и нет смысла вообще его вводить на лист.

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

P.S. Я пытался понять, каким образом работает у вас элемент VBA Code, так и не понял.

ZVIDjon Player,
Собственно, я хотел показать в этой теме методы и примеры VBA- кода, позволяющие значительно (в десятки-сотни и даже в тысячи раз) ускорить обработку достаточно больших массивов данных в Excel, для которых решения формулами неэффективны.

Что касается конкретно Вашей задачи, то решено вот это:
у меня на Intel Core 2 DUO E8400 3000MHZ дикие тормоза тормозов, надо понимать, не стало, так как
На реальных данных она сделала расчёт за 1 секунду

и это решено:
А конкретно бы меня ваш вариант устроил, единственное , чтобы не было проблем с округлениями на миллионные доли и чтобы было как-то видно случаи, когда в одной таблице есть объект с номером ID, а в другой его нет. Это может отображаться в виде комментария в соседнем столбце. Т.е. рядом со столбцом с разницей писать, что данный объект в первой (или во второй) таблице отсутствует
Да, спасибо, методы ускоренной сверки вы показали, правда до просмотра самого кода у меня всё руки не доходят, единственное что я пытался найти, так это каким образом у вас работает элемент (кнопка) Vba Code, так и не понял. С VBA Run там всё понятно, там просто назначен определённый макрос, а вот с Vba Code я не понял, для меня это загадка.

ZVI
По остальным пожеланиям меня остановило следующее:

1. Если в исходных данных обеих таблиц могут быть повторяющиеся ID, то
- добавлять результаты сверки в один из исходных листов некорректно, так как не понятно, в какую из строк с повторяющимся ID дописывать разность и прочие данные. Собственно, поэтому в моих вариантах результат помещается на 3-й отдельный лист.
- почему бы на этапе экспорта из Оракла сразу не объединить строки с повторяющимися ID с помощью запроса вместо того, чтобы делать это в Excel? Хотя это дело навыков и вкуса, т.е. Ваше.
Что касается добавления данных на один из исходных листов, то тут речь шла о том, как делал я, когда использовал ВПР и СУММЕСЛИ, т.к. там всё делалось руками и файл и так сам по себе был тяжёлый и порождать ещё лишний лист смысла не было. В вашей программе то, что результат выводиться на отдельном листе меня полностью устраивает, тем более, что это позволяет сохранять исходные данные в первоначальном виде.
Что касается этапа экспорта, то никакого экспорта нет. Есть готовые отчёты в Оракле (которые формирует сама бухгалтерия, у меня туда доступа нет), в этих отчётах есть группировка по ID (иначе бы там было не 35 тысяч строк, а возможно в десяток раз больше), но видимо по некоторым столбцам есть отличия, поэтому суммы для некоторых ID разбиваются на несколько строк. Просто бухгалтерия для сверки присылает мне лишь по несколько столбиков, которые их интересуют. Объясню ещё суть, почему два Оракла. На самом деле их даже ещё больше, как минимум 3 рабочих (помимо них у каждого есть ещё тестовые клоны, на которых проводят какие-то эксперементы, но тестовые нас не интересуют), но в каждый конкретный момент времени делают сверку по каким-то двум.
Сначала у нас была своя компания, внедряли свой Оракл. Потом нас скупила более крупная компания, у которая стала внедрять свой Оракл и наши данные надо было переносить из своего Оракла в их. Сначала был долгий этап полуавтоматического переноса данных по определенную дату. В дальнейшем стал вестись параллельный учёт, т.е. новые данные с той даты стали вводиться в оба Оракла. Т.к. возможны ошибки, человеческий фактор и не только, то в конце месяца сверяют два Оракла. Так вот та головная компания зачем-то решила вводить ещё второй релиз Оракла (для нас это уже третий Оракл), нам пришлось ещё так-же заниматься переносом данных уже из 2-ого Оракла в третий. В результате Бухгалтерия походу вносит данные во все 3 Оракла и проводит сверку 2-ого и 3-ого Оракла с нашим собственным 1-ым Ораклом (так как он у нас был внедрён на несколько лет раньше и уже весь был отшлифован по максимуму от ошибок), а те два Оракла относительно недавно появились и всё ещё в процессе внедрения.
[/quot]

ZVI
2. Понятен смысл сверки, т.е. вычисления разности уникальных строк одноименного поля (столбца) 2-х таблиц, с отображением в результате столбцов: ID, Data1, Data2, Data1-Data2, Comment
Но непонятно, как можно вести сверку одновременно по нескольким столбцам, отображая разность только одного из них (т.е. сверяя только один столбец). Непонятен смысл обработки, когда количество столбцов не совпадает - что делать с суммами столбцов, у которых нет пары, но есть совпадающие ID? Вам там, возможно, понятнее извращенные желания Ваших бухгалтеров ;)) а мне - не очень.Не, там где я писал об универсальности и возможности сверять несколько столбцов, разумеется подразумевалось, что количество столбцов с разностью такое-же, сколько и сверяемых столбцов.
Т.е. например Лист 1, поля ID,A,B,C,D. Лист 2, поля ID,B,D,E.
Из этого мы видим, что в шапке есть одинаковые имена столбцов B и D, следовательно сверять надо их.
На листе Compare должны быть такие поля: ID,A(1),B(1),C(1),D(1),B(2),D(2),E(2),B(1)-B(2),D(1)-D(2), Comment. Где (1)-говорит о принадлежности к листу 1, а (2) - к листу 2. В вашей программе собственно именно такие и были обозначения и я их взял за основу.
Причем результат должен содержать набор всех различных ID, из тех что имеется на обоих листах, при этом суммы должны быть сгруппированы и по каждому ID соответственно должна быть ровно одна строчка.

ZVI
Про крмментарии "нет в таблице 1" и "нет в таблице 2" – это не сложно, дорисую вечером.
Про "столбец А не выведен" – а что с ним делать для случая нескольких строк с одинаковым ID, кумулировать сумму (см. п2 выше)? Если сформулируете и пойму, то сделаю.
Заниматься универсальным вариантом не готов морально :-)Как написал в предыдущей цитате, группировку сумм надо делать по всем столбцам, а не только по тем, которые сравниваются.

А я обычно когда мне дают какое-то задание по написанию программы (данной сверки это не касается, т.к. тут меня просят сверять, а программу написать никто не просит), т.е. стараюсь максимально универсилизировать программу, путем создания листа с некоторыми настройками, чтобы в случае если вдруг пользователь захочет какие-то изменения в программе, обойтись лишь изменением настроек, не меняя код. Прием чаще всего так и происходит. Сначала пользователем нужно одно. А как только это реализуется, у них начинаются куча хотелок. К сожалению иногда хотелки у них появляются очень нехорошие и сделать их нужно в сию минуту, тогда приходиться жертвовать универсальностью программы и вносить "костыли" непосредственно в код.

Хотя бывает со временем, если эта задача представляет для меня интерес и появляются какие-то идеи по реализации интуитивно понятного интерфейса самих настроек, стараюсь все костыли из кода убрать, поместить их непосредственно в настройки в виде параметров и модернизировать код с учётом этих настроек.