Макрос Удаления Строк по Условию в Excel • Visual basic

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 с

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

эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
Рано или поздно у разработчиков возникает вопрос как удалить макросы, в том числе и из книги, в которой эти макросы расположены. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Следующий способ, как можно удалить макрос в Excel, заключается в использовании надстройки – специальной программы, которая устанавливается отдельно и содержит в себе несколько модулей для выполнения различных функций. Одной из таких надстроек является Kutools. С помощью данной программы можно одновременно удалить все макросы Excel.
Макрос Удаления Строк по Условию в Excel • Visual basic

Excel макрос удалить строки по условию

  • 50 000 строк со 100 уникальными строками, случайным образом распределены: 1m: 47s
  • 50 000 строк с 50 уникальными строками, случайным образом распределены: 57 секунд
  • 50 000 строк с 25 уникальными строками, случайное распространение: 28 секунд
  • 50 000 строк с 10 уникальными строками, случайное распространение: 12 с
  • 50 000 строк с 5 уникальными строками, случайное распространение: 6 с

Хотя поле «Описание» является необязательным, рекомендуется его заполнить. Кроме того, желательно ввести понятное описание, которое будет полезно вам и всем, кто запускает макрос. Если у вас много макросов, описания помогут быстро определить, для чего они нужны.

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Adblock
detector