Excel макрос VBA для суммирования повторяющихся значений, а затем удаления дубликатов записей
Я пытаюсь суммировать значения на основе дубликатов, найденных в столбцах “AO”. Я использую макрос ниже. Есть около 500k+ записей, а нижний макрос висит плохо.
Позвольте мне кратко объяснить макрос. У меня есть столбцы “AO”, и мне приходится их группировать… на основе группировки я должен суммировать столбцы “P, Q”. У меня есть функция, которая делает конкатенированную строку из 16 столбцов и хранится в столбце “AA”. Основываясь на этом столбце, я использую функцию sumif для суммирования всех повторяющихся значений
Затем я копирую пасту в качестве “значений” вышеуказанных значений, чтобы удалить формулу, в 2 новых столбцах (функция pasteSpecial в указанном выше макрокодеке).
Наконец, я вызываю дубликаты удаления, чтобы удалить повторяющиеся значения
Я использовал метод.removeduplicates, который, кажется, работает довольно быстро даже на таком огромном наборе данных. Есть ли какая-либо предопределенная функция в excel, которая бы даже суммировала значения дубликатов, а затем удаляла дубликаты записей?
Вышеупомянутая логика висит плохо, едя все ресурсы ЦП и сильно врезавшись…
Кто-то, пожалуйста, оптимизируйте макрос выше, чтобы он работал с записями 500k+. Допустимая производительность составляет 1-2 минуты.
EDIT: По записи 500k+ я имею в виду A1: O500000. Таким образом, предполагается, что для дублирования в этом случае используется комбинация A1, B1, C1, D1, E1, F1, G1, H1, I1, J1, K1, L1, M1, N1, O1 с A2, B2, C2, D2, E2, F2, G2, H2, I2, J2, K2, L2, M2, N2, O2 и A3, B3, C3, D3, E3, F3, G3, H3, I3, J3, K3, L3, M3, O3 и т.д…. до A500000, B500000 и т.п….
Короче, я должен проверить все совпадения A1-O1 со всеми A2-O2 или A3-O3 или….. A500k-O500k и так далее
Для каждого совпадения между всем набором записей AO мне нужно суммировать их соответствующие столбцы P, Q. Скажем, например, набор A1-O1, соответствующий набору A2-O2, затем добавьте P1, Q1 и P2, Q2 и сохраните в P1, Q1 или что-то в этом роде.
В любом случае мне нужно сохранить каждый оригинальный набор записей, скажем, A1-O1 с суммированными значениями его дубликатов и свойством в P1, Q1
Я не думаю, что мы можем приложить демоверсию листа Excel здесь, не так ли? 🙁
Функция для репликации формулы sumif во всех ячейках
Он висит довольно плохо. Какая проблема в репликации формулы в 30k-40k строк. Может ли кто-то оптимизировать код?
Что-то должно быть ужасно неправильно с тем, как вы делаете добавление дубликатов. Поскольку у вас мало информации о данных, с которыми вы работаете, я не знаю, является ли это одним и тем же, но я заполнил A1: O33334 (более 500 тыс. Ячеек) случайным числом от 1 до 10 000.
Используя объект словаря (я известен своей любовью и чрезмерным использованием), я прошел через все из них и суммировал только повторяющиеся значения, а затем ударил уникальный список элементов в столбец A в sheet2.
Проверка и добавление дубликатов и копирование уникальных ячеек занимает всего 2 секунды. Вот код для вашей справки.
Вы не должны select каждую ячейку при выполнении кода.
Кстати, если вы посмотрите на свой код, некоторые утверждения бесполезны:
Что касается производительности, см. Некоторые советы в этом потоке: Бенчмаркинг кода VBA
Суть вопроса, как я понимаю, состоит в том, чтобы найти дубликаты и добавить их, а затем удалить их. Вы также упомянули о группировке, но неясно, как это сделать. В любом случае, я бы выбрал макросы. Операции над отдельными рядами не будут работать в этом наборе данных.
Вот несколько шагов, которые я бы предпринял. Измените их в соответствии с вашими потребностями:
Используйте функцию concatenate для создания нового столбца справа от вашего набора данных. Например
Создайте столбец под названием Dups и используйте его для заполнения:
Я добавляю это как второй ответ, так как он будет длиться долго…
Безусловно, я упрямый мул, я пробовал много разных вещей, я думаю, вы достигли предела того, что может сделать Excel. Лучшая функция, которую я мог придумать, заключалась в этом, и обратите внимание, что я использую 50 000 строк, а не 500 000:
- 50 000 строк со 100 уникальными строками, случайным образом распределены: 1m: 47s
- 50 000 строк с 50 уникальными строками, случайным образом распределены: 57 секунд
- 50 000 строк с 25 уникальными строками, случайное распространение: 28 секунд
- 50 000 строк с 10 уникальными строками, случайное распространение: 12 с
- 50 000 строк с 5 уникальными строками, случайное распространение: 6 с
Как вы можете видеть, функция будет ухудшаться по мере увеличения числа уникальных строк. У меня здесь много дурацких идей, поэтому я решил поделиться своим кодом ради исследования:
[expert_bq id=»1570″]Рано или поздно у разработчиков возникает вопрос как удалить макросы, в том числе и из книги, в которой эти макросы расположены. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Следующий способ, как можно удалить макрос в Excel, заключается в использовании надстройки – специальной программы, которая устанавливается отдельно и содержит в себе несколько модулей для выполнения различных функций. Одной из таких надстроек является Kutools. С помощью данной программы можно одновременно удалить все макросы Excel.
Excel макрос удалить строки по условию
- 50 000 строк со 100 уникальными строками, случайным образом распределены: 1m: 47s
- 50 000 строк с 50 уникальными строками, случайным образом распределены: 57 секунд
- 50 000 строк с 25 уникальными строками, случайное распространение: 28 секунд
- 50 000 строк с 10 уникальными строками, случайное распространение: 12 с
- 50 000 строк с 5 уникальными строками, случайное распространение: 6 с
Хотя поле «Описание» является необязательным, рекомендуется его заполнить. Кроме того, желательно ввести понятное описание, которое будет полезно вам и всем, кто запускает макрос. Если у вас много макросов, описания помогут быстро определить, для чего они нужны.
