Выбрать повторяющиеся значения и суммировать vba excel

Обновлено: 08.07.2024

Добрый день. Есть два и более массивов состоящих всегда из двух столбцов и разного количества строк.
Необходимо создать итоговый массив в котором в первом столбце будут уникальные значения из первого столбца массивов, во втором столбце сумма из вторых столбцов массивов при совпадении значения.
То есть первая формула выводит уникальные имена из первых столбцов массивов в итоговый массив. Вторая формула при должна перебрать имена в первых столбцах массивов, при нахождении совпадений суммировать вторые столбцы напротив этих имен и сумму вывести напротив имени в итоговом массиве. Если имя уникально (не повторяется ни в одном массиве) то вывести значение напротив него. Файл "сумма массивов".

Если это невозможно то:
исходные массивы можно ручками объединить в один массив состоящий из двух столбцов. В первом столбце могут быть как уникальные так и повторяющиеся имена.
Вывод уникальных значений из первого столбца я сделал.
Нужна формула суммирования значений второго столбца при совпадении имен в первом столбце исходного и итогового массивов. Файл "сумма массива".

Про поиск и подсветку дубликатов в разных ячейках и диапазонах я уже не раз писал, но что делать если нужно найти и, возможно, удалить повторяющиеся слова внутри ячейки? Например, мы имеем вот такую таблицу с данными (разделителями могут быть не обязательно пробелы):

Хорошо видно, что некоторые имена в списках внутри ячеек повторяются. Давайте посмотрим, что можно с этим сделать.

Способ 1. Ищем повторения: текст по столбцам и формула массива

Это не самый удобный и быстрый, но зато самый простой вариант решения задачи "на коленке". Выделим исходный список и разобъем его на столбцы по пробелам с помощью команды Данные - Текст по столбцам (Data - Text to columns) . В открывшемся окне трёхшагового Мастера выберем формат По разделителю (By delimiter) на первом шаге и поставим флажок Пробел (Space) на втором:

Если в исходных данных могут быть лишние пробелы, то лучше включить и опцию Считать последовательные разделители одним (Treat consecutive delimiters as one) - это избавит нас от лишних столбцов.

На третьем шаге в поле Поместить в зададим пустую ячейку рядом с таблицей, чтобы результаты не затёрли нам исходные данные и нажмём на Готово (Finish) :

Наши данные разделятся по ячейкам. Останется подсчитать количество повторов в каждой строке с помощью небольшой, но хитрой формулы массива:

В английской версии это будет =SUMPRODUCT(N(COUNTIF(B2:G2,B2:G2)>1))

Давайте разберём логику её работы на примере первой строки.

Сначала мы с помощью формулы СЧЁТЕСЛИ( B2:G2 ; B2:G2 ) вычисляем по очереди количество вхождений каждого имени в диапазон B2:G2 и получаем на выходе массив 2,1,2,1>, т.к. Иван встречается в первой строке 1 раз, Елена - 2 раза, Сергей - 1 и т.д.
Проверяем с помощью СЧЁТЕСЛИ(B2:G2;B2:G2) >1 какие из полученных чисел больше единицы, т.е. где у нас повторы. На выходе эта формула выдаст нам массив результатов проверки в виде .
Переводим логические значения ЛОЖЬ и ИСТИНА в более удобные для подсчета 0 и 1, соответственно, с помощью функции Ч. На выходе получаем массив >.
Суммируем все элементы получившегося массива функцией СУММПРОИЗВ. Можно было бы использовать и обычную функцию СУММ, но тогда пришлось бы жать вместо привычного Enter сочетание клавиш Ctrl + Shift + Enter , чтобы ввести формулу как формулу массива.

По получившемуся столбцу можно легко отфильтровать строки с повторами и работать потом с ними дальше уже вручную.

Минусы такого способа, впрочем, весьма очевидны: при изменении в исходных данных придётся повторять всю процедуру заново, дубликаты не очень заметны и удалять их тоже надо врукопашную. Поэтому идём дальше.

Способ 2. Выделение цветом повторов внутри ячейки макросом

Если дубликаты нужно именно наглядно показать, то удобнее будет использовать для этого специальный макрос. Откроем редактор Visual Basic одноимённой кнопкой на вкладке Разработчик (Developer - Visual Basic) или сочетанием клавиш Alt + F11 . Вставим в книгу новый пустой модуль через меню Insert - Module и скопируем туда вот такой код:

Теперь можно вернуться в главное окно Excel, выделить ячейки с текстом и запустить созданный макрос через кнопку Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt + F8 . Этот макрос проходит по всем выделенным ячейкам и помечает повторения красным цветом шрифта прямо внутри ячейки:

Если нужно, чтобы цветом выделялись только клоны, но не первые вхождения (т.е. только вторая и третья, но не первая Алиса, например), то достаточно будет просто убрать из кода строку 20.

Способ 3. Выводим повторы в соседний столбец

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

Эта функция, как легко догадаться, принимает в качестве единственного аргумента ячейку с текстом и выводит в качестве результата все повторы, которые там найдет:

Способ 4. Удаление повторов внутри ячейки макросом

Если нужно просто удалить дубликаты внутри ячейки, чтобы все оставшиеся там слова не повторялись, то макрос будет похож на предыдущий, но попроще:

Способ 5. Удаление повторов внутри ячейки через Power Query

Этот способ использует бесплатную надстройку Excel для обработки данных под названием Power Query. Для Excel 2010-2013 скачать её можно с сайта Microsoft, а в Excel 2016-2019 она уже встроена по умолчанию. Огромным плюсом этого варианта является возможность автоматического обновления - если в будущем исходные данные изменятся, то нам не придется заново проделывать всю обработку (как в Способе 1) или запускать макрос (как в Способе 4) - достаточно будет просто обновить созданный запрос.

Сначала наши данные нужно загрузить в Power Query. Проще всего для этого превратить нашу таблицу в "умную" сочетанием клавиш Ctrl + T или кнопкой Форматировать как таблицу на вкладке Главная (Home - Format as Table) , а затем нажать кнопку Из таблицы/диапазона (From table/range) на вкладке Power Query (если у вас Excel 2010-2013) или на вкладке Данные (если у вас Excel 2016 или новее):

Поверх окна Excel откроется окно редактора запросов Power Query с загруженными туда нашими данными:

Дальше делаем следующую цепочку действий:

Удаляем ненужный пока шаг Измененный тип (Changed Type) справа в панели применённых шагов с помощью крестика слева от шага.

Чтобы можно было потом идентифицировать принадлежность каждого имени к исходной строке - добавляем столбец с нумерацией строк на вкладке Добавление столбца - Столбец индекса - От 1 (Add Column - Index Column - From 1) :

Выделяем столбец с именами и жмём на вкладке Преобразование - Разделить столбец - По разделителю (Transform - Split Column - By delimiter) , а в открывшемся окне выбираем деление по каждому пробелу и - главное - деление на строки, а не на столбцы в расширенных параметрах:

После нажатия на ОК увидим следующее:

Теперь выделяем оба столбца (удерживая клавишу Ctrl или Shift ) и удаляем дубликаты через Главная - Удалить строки - Удалить дубликаты (Home - Remove Rows - Remove Duplicates) .

Осталось собрать всё обратно в ячейки :) Для этого выделим столбец Индекс и используем команду Группировать по на вкладке Преобразование (Transform - Group By) со следующими параметрами:

После нажатия на ОК наши имена сгруппируются во вложенные таблицы, имитирующие начальные ячейки - только уже без повторов. Увидеть содержимое свёрнутых таблиц можно, если щёлкнуть мышью в фон ячейки рядом со словом Table (но не в слово Table!):

Осталось вытащить все имена из первой колонки каждой таблицы и склеить их через пробел. Это можно сделать с помощью небольшой формулы на встроенном в Power Query языке М. Выберем на вкладке Добавление столбца команду Настраиваемый столбец (Add Column - Custom Column) и введём в открывшееся окно имя нового столбца и формулу (с соблюдением регистра!):

Осталось удалить ненужные более столбцы Индекс и Ячейки, щелкнув по их заголовкам правой кнопкой мыши и выбрав команду Удалить столбцы (Remove Columns) и выгрузить результаты на лист через Главная - Закрыть и загрузить - Закрыть и загрузить в (Home - Close & Load - Close & Load to..) :

Задача решена! Если в будущем данные в исходной "умной" таблице изменятся или к ней будут дописаны новые строки, то достаточно будет просто обновить запрос, щёлкнув по результирующей зелёной таблице правой кнопкой мыши и выбрав команду Обновить или нажав сочетание клавиш Ctrl + Alt + F5 .

Имеем таблицу по продажам, например, следующего вида:

Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".

Способ 1. Функция СУММЕСЛИ, когда одно условие

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:

Жмем ОК и вводим ее аргументы:

Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.
Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.
Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. - при необходимости.

Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться - см. следующие способы.

Способ 3. Столбец-индикатор

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1, иначе - 0. Формула, которую надо ввести в этот столбец очень простая:

=(A2="Копейка")*(B2="Григорьев")

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:

Способ 4. Волшебная формула массива

Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:

=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)

После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

Способ 4. Функция баз данных БДСУММ

В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:

Доброго времени суток.

Есть таблица размерностью 15000 строк и 6 колонок.
Суть макроса в том что бы по 3 столбцу искать повторяющиеся значение, при найденном просуммировать значение 4 столбца для найденных.
После удалить повторяющиеся строки и поставить общую сумму.
Данный код не оптимизирован.
Прошу Вас мне подсказать на мои ошибки по данному коду.
[vba]

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayStatusBar = False

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True

Доброго времени суток.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayStatusBar = False

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayStatusBar = False

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True

[/vba] Автор - MisterYu
Дата добавления - 15.02.2018 в 12:56 Добрый день.
Думаю, что нужно переименовать тему (в суть макроса)
и приложите файл-пример с данными.
Чтобы ускорить код, нужно написать новый =)
Судя по описанию, я бы применил штатное средство "удалить дубликаты" и потом подсчет суммы для каждого из источника Добрый день.
Думаю, что нужно переименовать тему (в суть макроса)
и приложите файл-пример с данными.
Чтобы ускорить код, нужно написать новый =)
Судя по описанию, я бы применил штатное средство "удалить дубликаты" и потом подсчет суммы для каждого из источника sboy
Или воспользоваться сводной таблицей Автор - and_evg
Дата добавления - 15.02.2018 в 13:12

Прилагаю.
Упс файл 1,1 мб. а тут ограничение
Максимальный размер файла 100 Kb
Обрежу таблицу.

П.С. Добавил Автор - MisterYu
Дата добавления - 15.02.2018 в 14:33

Покажите рабочий код.
Может он был в файле, не знаю - у меня админы код режут. Но тот, что в теме - не работает.
И вообще зачем в файле 1000 строк? Достаточно и десяти.
А так да, сводная, или удалить на копии дубликаты и просуммировать формулой.
Или как вариант:
[vba] Покажите рабочий код.
Может он был в файле, не знаю - у меня админы код режут. Но тот, что в теме - не работает.
И вообще зачем в файле 1000 строк? Достаточно и десяти.
А так да, сводная, или удалить на копии дубликаты и просуммировать формулой.
Или как вариант:
[vba] [/vba] Автор - Hugo
Дата добавления - 15.02.2018 в 14:43
Как бы вот. Автор - MisterYu
Дата добавления - 15.02.2018 в 14:58 Ну я ведь писал - такое "вот" мне без толку. Ну да ладно, выше добавил макрос. Если нужно сохранить формат - можно добавить апостроф в строке
[vba] Ну я ведь писал - такое "вот" мне без толку. Ну да ладно, выше добавил макрос. Если нужно сохранить формат - можно добавить апостроф в строке
[vba] [/vba] Автор - Hugo
Дата добавления - 15.02.2018 в 15:06

Спасибо Вам за помощь.
Я не настолько силен в программированию. Ваш код частично понял.

Я решил свою задач своим путем, которым мне понятнее и скорость работы меня устраивает.

Если кому интересно, то напишу как решил.
1 этап
Загоняем данную таблицу в массив:
[vba]

2 этап
Ищу одинаковые значение в массиве по Arr_I(2, i).
После нахождению суммирую значение соответственно Arr_I(3, i) и записываю признак повторения ( у меня "1") в массив Arr_I(0, j) = "1"

[/vba]
3 этап
Прохожу обратным циклом и удаляю строки соответствующие признаку в массиве Arr_I(0, j) = "1"
[vba]

Спасибо Вам за помощь.
Я не настолько силен в программированию. Ваш код частично понял.

Я решил свою задач своим путем, которым мне понятнее и скорость работы меня устраивает.

Если кому интересно, то напишу как решил.
1 этап
Загоняем данную таблицу в массив:
[vba]

[/vba]
3 этап
Прохожу обратным циклом и удаляю строки соответствующие признаку в массиве Arr_I(0, j) = "1"
[vba]

Спасибо Вам за помощь.
Я не настолько силен в программированию. Ваш код частично понял.

Я решил свою задач своим путем, которым мне понятнее и скорость работы меня устраивает.

Если кому интересно, то напишу как решил.
1 этап
Загоняем данную таблицу в массив:
[vba]

[/vba]
3 этап
Прохожу обратным циклом и удаляю строки соответствующие признаку в массиве Arr_I(0, j) = "1"
[vba] [/vba] Автор - MisterYu
Дата добавления - 16.02.2018 в 15:25

Option Explicit
Dim Col As Integer
Dim s As String
Dim wb As Workbook
Dim Name_Wb As String
Public Stop_Pr As Boolean

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayStatusBar = False

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True

Call Wb_Books
UserForm1.Show
Call Base
End Sub

Option Explicit
Dim Col As Integer
Dim s As String
Dim wb As Workbook
Dim Name_Wb As String
Public Stop_Pr As Boolean

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayStatusBar = False

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True

Call Wb_Books
UserForm1.Show
Call Base
End Sub

Option Explicit
Dim Col As Integer
Dim s As String
Dim wb As Workbook
Dim Name_Wb As String
Public Stop_Pr As Boolean

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayStatusBar = False

Читайте также: