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 с
Как вы можете видеть, функция будет ухудшаться по мере увеличения числа уникальных строк. У меня здесь много дурацких идей, поэтому я решил поделиться своим кодом ради исследования:
5 вариантов, как удалить пустые строки в Excel | Понятный Excel
В этом методе используется конструктор Excel (расширенный режим с дополнительными возможностями). Конструктор активируется при выделении любой ячейки табличной области. Как и в первом случае, после применения производится удаление дублированных значений.
Другие методы
Существуют альтернативные способы избавления от повторов, которые также будут работать в excel 2003 года. Рассмотрим подробнее:
- Дубликаты можно удалить формулой. Создаете дополнительный столбец, в котором используете функцию СЧЁТЕСЛИ (на английском COUNTIF). В качестве первого аргумента выбираете анализируемый диапазон, который обязательно фиксируете клавишей F4 или значком доллара, а во втором блоке ставите ссылку на ячейку из столбца. Применяете формулу ко всем строкам при помощи маркера автозаполнения. Затем убираете строки, где стоит ИСТИНА, которая указывает на повтор.
Используя этот способ можно без смещения и удаления увидеть повторяющиеся значения.
- Пользовательский макрос, написанный на языке программирования vba (Visual Basic). Этот способ требует определенных знаний в кодировке программы, синтаксисе языка, а также необходимо иметь идею реализации поиска дублей в базе данных, что под силу только опытным пользователям MS Excel.
Как видите, существует несколько методов отыскания дубликатов, каждый из которых имеет свои преимущества и недостатки. Одни удаляют найденные значения, другие лишь указывают местоположение повторов, которые можно убрать при помощи других функций редактора.
Удалить дубликаты в excel — пошаговая инструкция с фото
- В появившемся диалогом окне выбираете столбцы, по которым будет выполняться проверка. Если поставить галочку напротив содержания заголовков, то название столбцов будет соответствовать названию таблицы, а не буквенной последовательности рабочего листа excel.
Если в каждом столбце таблицы имеется заголовок, установить маркер Мои данные содержат заголовки. Также проставляем маркеры напротив тех столбцов, в которых требуется произвести поиск дубликатов.
