Подскажите формулу, чтобы убрать дубликаты в excel и суммировать значения
Пример
Лучший ответ
Копируем формулу
=СУММЕСЛИ (A:A;A2;B:B) и вставляем в ячейку С2
Выделяем С2 и за нижний правый уголок тянем вниз до конца таблицы
выделяем и копируем столбец » С»
клик правой мышкой на D2 — специальная вставка — значения — ОК
Удалить столбец «С»
Выделяем «А: С» ( всю таблицу)
Нажимаем Данные — удалить дубликаты (в списке оставляем только «А» )
Результат на последней картинке
Елена OstrovskayаПрофи (672) 2 года назад
Спасибо большое!
Елена OstrovskayаПрофи (672) 2 года назад
Abram Pupkin Гений (97934) Елена Ostrovskayа, 1) «. -64kb.png . » .png — это значит вы загрузили КАРТИНКУ ! ( это все равно что врачу выслать фотографию больного и спросить почему у него кашель) Надо загружать НАСТОЯЩИЙ ФАЙЛ ! 2) «. дублирующие наименования почему-то по 2шт . » большая вероятность что в каком-то из наименований присутствует лишний пробел Реже бывает что в написании используют буквы русского и английского алфавита ( машина различает русскую «А» и английскую «A» ) Больше по картинке я ничего подсказать не могу, Если не получится — тогда больного в операционную
Елена OstrovskayаПрофи (672) 2 года назад
Сообщение поняла, почему выходит, там убрала лишний пробел, а вот почему после удаления дубликатов, остаются двойные, не могу догадаться.
Abram Pupkin Гений (97934) Елена Ostrovskayа, копируем формулу =ДЛСТР (A2) и и вставляем в Ячейку D2 ( на вашей первой картинке) протягиваем вниз и смотрим результат на одинаковых названиях
Елена OstrovskayаПрофи (672) 2 года назад
Я сортирую по наименованию и вижу дубли.
Вот файл с дублирующими наименованиями.
https://cloud.mail.ru/public/qYwQ/a7k9RoAvA
Abram Pupkin Гений (97934) Елена Ostrovskayа, проверяем 2550 Ясколка Серебряный ковер 0,1г 2551 Ясколка Серебряный ковер 0,1г D2550 =ДЛСТР (A2550) =34 (это кол-во символов в ячейке A2550 ) D2551 =ДЛСТР (A2551) =35 ( здесь больше на 1 знак! потому что в конце стоит пробел )
Елена OstrovskayаПрофи (672) 2 года назад
А если артикулы (четыре цифры) вывести отдельным столбцом? Возможно тогда по артикулу можно будет убрать дубликаты.
Abram PupkinГений (97934) 2 года назад
надеюсь запомнила » Одна ячейка — одно значение»
ТОО РудСтройТехСервис-1Ученик (197) 2 года назад
Здравствуйте, выручите пожалуйста, мне дали задание: нужно убрать дубликаты, но при этом сложить все свойства товаров в одну ячейку, только формулами. Делаю, как у вас указано выходит 0. Подскажите пожалуйста милейший, что делать в данной ситуации.
Abram Pupkin Гений (97934) ТОО РудСтройТехСервис-1,
Abram PupkinГений (97934) 2 года назад
Если честно — то я не понял конечную цель вашего задания .
Но ошибки уже видно .
функции =СУММЕСЛИ (A:A;A2;B:B) имеет такой синтаксис:
В диапазоне A:A машина ищет значение как в ячейке А2
Если такие значения находятся тогда соответствующие ячейки суммирует по диапазону B:B
разбор по картинке
В диапазоне A:А ищем «Май»
«май» встречается 3 раза (А2; А4 и А9)
машина находит сумму В2+В4+В9
На вашей картинке в обоих столбцах находится текст
Текст — это не числа . Его невозможно умножить/ вычесть/ сложить
Нарисуйте от руки и покажите картинку ТАК ЕСТЬ и ТАК ХОЧУ
ТОО РудСтройТехСервис-1 Ученик (197)
Abram PupkinГений (97934) 2 года назад
ТОО РудСтройТехСервис-1 Ученик (197) Abram Pupkin, у меня написано в задании используя команды ВПР, СЦЕПИТЬ, ПСТР, ЛЕВСИМВ, ПОИСКПОЗ, ДЛСТРБ, ПРОПНАЧ, ПЕЧСИМВ, СЖПРОБЕЛЫ, НАЙТИ, ИНДЕКС, СОВПАД, ИЛИ, И, СМЕЩ.
Остальные ответы
Данные — Консолидация. Или очень хочется именно формулой?
Елена OstrovskayаПрофи (672) 2 года назад
Можно и через консолидацию, только как? Подробно можно?
Freeloader Оракул (83382) Елена Ostrovskayа, Тыкаете мышкой в ячейку, которая станет левой верхней ячейкой консолидированной таблицы. Данные — Консолидация. Функция: Сумма Ссылка: Список диапазонов — добавить — выделяете свою таблицу, оба столбца. Использовать в качестве имен: значения левого столбца. ОК.
А внятно сказать, что нужно получить по этому скрину? Никак?!
Формулу можно написать, если знать, что нужно получить!
Елена OstrovskayаПрофи (672) 2 года назад
Да вроде понятно спросила. Нужно подсчитать кол-во каждого наименования, а дубликаты убрать.
Полосатый жираф Алик Искусственный Интеллект (312985) Елена Ostrovskayа, Совсем непонятно! Что нужно подсчитать? Количество УНИКАТЕЛЬНЫХ названий? Или что?!
Елена OstrovskayаПрофи (672) 2 года назад
Перечислены сорта семян и их кол-во. Сорта дублируются. Нужно дубли удалить, но при этом вывести количество для каждого наименования. Пример по скрину — артикул 0552 дубли убрать и поставить кол-во 21шт
Как удалить дубликаты в Microsoft Excel
Работа с данными в Microsoft Excel занимает львиную долю всего времени пребывания в программе. Пользователи вручную выполняют те действия, которые можно оптимизировать при помощи встроенных функций. Относится это и к поиску дубликатов в столбцах. Их обнаружение и удаление доступно в автоматическом режиме, что значительно экономит время.
Как определить дубликаты в таблице
Сначала предлагаю быстро разобраться с тем, как определить наличие дубликатов в столбце. Для этого подойдет одно из правил условного форматирования. Оно подсветит строки со встречающимися повторами, и вы сможете понять, нужно ли что-то из этого удалить для оптимизации таблицы.
- Выделите диапазон с данными и на вкладке «Главная» вызовите меню «Условное форматирование».
- Наведите курсор на список правил «Правила выделения ячеек» и выберите вариант из списка «Повторяющиеся значения».
- Можно изменить цвет выделения, но подойдет и стандартный. Просто подтвердите действие.
- Обратите внимание на то, что ячейки с повторами выделены цветом, а уникальные значения остаются неподсвеченными. Теперь вы знаете точно, какие данные повторяются и можно ли их удалить.
Это лишь один из примеров условного форматирования в Microsoft Excel. Если вас заинтересовала эта тема, читайте другую мою статью, где рассказано все о данной функции.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Удаление дубликатов в таблице Excel
Приступим непосредственно к удалению дубликатов в Excel-таблице. Как вы уже знаете, осуществляется это в автоматическом режиме, вам нужно только запустить процесс. Займет процедура меньше минуты, а выглядит следующим образом:
- Выделите необходимые ячейки, перейдите на вкладку «Данные» и разверните меню «Работа с данными».
- Щелкните по кнопке «Удалить дубликаты».
- Вы выбрали только один столбец, поэтому в появившемся окне просто нажмите «ОК».
- Отобразится уведомление с информацией о найденных и удаленных повторяющихся значений. Далее покажется, сколько уникальных значений осталось.
- Выделение условного форматирования пропало – значит, повторы удалены и остались только уникальные строки.
Отдельно стоит упомянуть удаление повторов сразу в нескольких столбцах. При их одновременном выделении алгоритм сотрет только те повторы, которые совпадают в обеих ячейках. Давайте разберем это на более наглядном примере.
- Вы видите два столбца с днями недели и пометкой «Рабочий» или «Нерабочий» день. Некоторые значения полностью повторяются, например «Понедельник — Рабочий», но есть и «Понедельник — Нерабочий». Так вот удаление дубликатов сработает только в первом случае.
- Выделите два столбца и нажмите кнопку «Удалить дубликаты».
- Подтвердите выбор в новом окне.
- Ознакомьтесь с информацией об удаленных и оставшихся значениях.
- Теперь обратите внимание на то, какие строки были удалены.
Надеюсь, я помог вам разобраться с принципом удаления дубликатов в Microsoft Excel, и теперь вы не будете делать это вручную, а доверите задачу встроенной функции.
Дубликаты внутри ячейки
Про поиск и подсветку дубликатов в разных ячейках и диапазонах я уже не раз писал, но что делать если нужно найти и, возможно, удалить повторяющиеся слова внутри ячейки? Например, мы имеем вот такую таблицу с данными (разделителями могут быть не обязательно пробелы): Хорошо видно, что некоторые имена в списках внутри ячеек повторяются. Давайте посмотрим, что можно с этим сделать.
Способ 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 и скопируем туда вот такой код:
Sub Color_Duplicates() Dim col As New Collection Dim curpos As Integer, i As Integer On Error Resume Next For Each cell In Selection Set col = Nothing curpos = 1 'убираем лишние пробелы и разбиваем текст из ячейки по пробелам arWords = Split(WorksheetFunction.Trim(cell.Value), " ") For i = LBound(arWords) To UBound(arWords) 'перебираем слова в получившемся массиве Err.Clear 'сбрасываем ошибки curpos = InStr(curpos, cell, arWords(i)) 'позиция начала текущего слова col.Add arWords(i), arWords(i) 'пытаемся добавить текущее слово в коллекцию If Err.Number <> 0 Then 'если возникает ошибка - значит это повтор, выделяем красным cell.Characters(Start:=curpos, Length:=Len(arWords(i))).Font.ColorIndex = 3 cell.Characters(Start:=InStr(1, cell, arWords(i)), Length:=Len(arWords(i))).Font.ColorIndex = 3 End If curpos = curpos + Len(arWords(i)) 'переходим к следующему слову Next i Next cell End Sub
Теперь можно вернуться в главное окно Excel, выделить ячейки с текстом и запустить созданный макрос через кнопку Макросы на вкладке Разработчик (Developer — Macros) или сочетанием клавиш Alt + F8 . Этот макрос проходит по всем выделенным ячейкам и помечает повторения красным цветом шрифта прямо внутри ячейки:
Если нужно, чтобы цветом выделялись только клоны, но не первые вхождения (т.е. только вторая и третья, но не первая Алиса, например), то достаточно будет просто убрать из кода строку 20.
Способ 3. Выводим повторы в соседний столбец
Если повторы внутри ячеек нужно не просто подсветить, а явным образом вывести, например, в соседний столбец, то удобнее будет использовать для этого макрофункцию, созданную по образу предыдущего макроса. Добавим в редакторе Visual Basic новый модуль и вставим туда код нашей функции GetDuplicates:
Function GetDuplicates(cell As Range) As String Dim col As New Collection Dim i As Integer, sDupes As String On Error Resume Next Set col = Nothing 'делим текст в ячейке по пробелам arWords = Split(WorksheetFunction.Trim(cell.Value), " ") 'проходим в цикле по всем получившимся словам For i = LBound(arWords) To UBound(arWords) Err.Clear 'сбрасываем ошибки col.Add arWords(i), arWords(i) 'пробуем добавить слово в коллекцию 'если ошибки не возникает, то это не повтор - добавляем слово к результату If Err.Number <> 0 Then sDupes = sDupes & " " & arWords(i) Next i GetDuplicates = Trim(sDupes) 'выводим результаты End Function
Эта функция, как легко догадаться, принимает в качестве единственного аргумента ячейку с текстом и выводит в качестве результата все повторы, которые там найдет:
Способ 4. Удаление повторов внутри ячейки макросом
Если нужно просто удалить дубликаты внутри ячейки, чтобы все оставшиеся там слова не повторялись, то макрос будет похож на предыдущий, но попроще:
Sub Delete_Duplicates() Dim col As New Collection Dim i As Integer On Error Resume Next For Each cell In Selection Set col = Nothing sResult = "" 'делим текст в ячейке по пробелам arWords = Split(WorksheetFunction.Trim(cell.Value), " ") 'проходим в цикле по всем получившимся словам For i = LBound(arWords) To UBound(arWords) Err.Clear 'сбрасываем ошибки col.Add arWords(i), arWords(i) 'пробуем добавить слово в коллекцию 'если ошибки не возникает, то это не повтор - добавляем слово к результату If Err.Number = 0 Then sResult = sResult & " " & arWords(i) Next i cell.Value = Trim(sResult) 'выводим результаты без повторов Next cell End Sub
Способ 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) и введём в открывшееся окно имя нового столбца и формулу (с соблюдением регистра!):
Здесь выражение [Ячейки][Имена] извлекает содержимое столбца Имена из каждой таблицы в колонке Ячейки, а функция Text.Combine склеивает затем их все через заданный разделитель (пробел). После нажатия на ОК мы, наконец, увидим желаемое:
Осталось удалить ненужные более столбцы Индекс и Ячейки, щелкнув по их заголовкам правой кнопкой мыши и выбрав команду Удалить столбцы (Remove Columns) и выгрузить результаты на лист через Главная — Закрыть и загрузить — Закрыть и загрузить в (Home — Close & Load — Close & Load to..) :
Задача решена! Если в будущем данные в исходной «умной» таблице изменятся или к ней будут дописаны новые строки, то достаточно будет просто обновить запрос, щёлкнув по результирующей зелёной таблице правой кнопкой мыши и выбрав команду Обновить или нажав сочетание клавиш Ctrl + Alt + F5 .
Ссылки по теме
- Как найти и вывести отличия в двух списках в Excel
- Как подсветить совпадающие пары дубликатов одинаковым цветом
- Сравнение двух таблиц с помощью ВПР, сводной или Power Query
Как убрать дубликаты в excel и суммировать значения
Добрый день. Очень прошу, сильно не пинайте!)))
Дано : В примере в столбце D названия позиций . В столбце Q код присвоенный данной из столбца D. В столбце Т вес позиции.
Задача: Удалить дубли в D,Q .Веса в Т сложить.
Извините , объяснил, как смог.))) Заранее спасибо.
Прикрепленные файлы
- пример.xls (51 КБ)
Пользователь
Сообщений: 8277 Регистрация: 03.12.2015
17.12.2019 14:17:59
dimentr, если удалить пустые столбцы то можно обычной сводной сделать
Не бойтесь совершенства. Вам его не достичь.
Пользователь
Сообщений: 1028 Регистрация: 08.11.2016
17.12.2019 14:18:20
У Вас там у таблицы структура мерзкая (с объединенными ячейками), так что я не стал мучиться со сводной таблицей и PQ.
Сделал вот так:
1. Скопировал всё на новый лист.
2. Данные/Удалить дубликаты (не сработало, пока не убрал объединение ячеек).
3. =СУММЕСЛИМН(Было!$T$3:$T$24;Было!$D$3:$D$24;Лист1!D3;Было!$Q$3:$Q$24;стало!Q3)
Прикрепленные файлы
- пример (5).xls (57.5 КБ)
Я не волшебник, я только учусь.
Пользователь
Сообщений: 17 Регистрация: 27.10.2019
17.12.2019 14:23:57
Спасибо за ответы и помощь. Сейчас буду тестить. )))
Пользователь
Сообщений: 17 Регистрация: 27.10.2019
17.12.2019 16:27:38
А возможно ли написать формулу которая будет работать непосредственно в таблице «Было»? т.е дубли я удалю через : Данные -удаление дублей , а суммирование произойдет по формуле ,но в этой же таблице.
Спасибо.
забыл. Данные будут меняться постоянно и коды тоже
Изменено: dimentr — 17.12.2019 16:31:01
Пользователь
Сообщений: 1028 Регистрация: 08.11.2016
17.12.2019 16:30:51
А данные откуда брать?
Как вариант:
1. Сделать дополнительный столбец
2. Написать в нём суммеслиМН
3. Скопировать и вставить как значения (можно в старый столбец с данными).
4. Удалить дубликаты.
Без дополнительных столбцов — только макросом, ИМХО.
Изменено: Wiss — 17.12.2019 16:31:26
Я не волшебник, я только учусь.
Пользователь
Сообщений: 17 Регистрация: 27.10.2019
17.12.2019 16:34:32
Могли бы Вы показать в прошлом примере ,как это будет выглядеть. Я еще только начинаю свой путь освоения могучего EXEL.
Пользователь
Сообщений: 2054 Регистрация: 01.07.2018
17.12.2019 16:46:29
еще вариант
Прикрепленные файлы
- пример.xls (56 КБ)
Пользователь
Сообщений: 17 Регистрация: 27.10.2019
17.12.2019 16:52:27
Спасибо. Буду тестить! )))
Пользователь
Сообщений: 647 Регистрация: 30.01.2018
17.12.2019 17:04:03
Прикрепленные файлы
- пример.xls (60 КБ)
Пользователь
Сообщений: 17 Регистрация: 27.10.2019
17.12.2019 17:07:06
Спасибо. буду тестить.
Пользователь
Сообщений: 8058 Регистрация: 21.12.2012
17.12.2019 17:20:59
| Цитата |
|---|
| а суммирование произойдет по формуле ,но в этой же таблице. |
Макросом рядом с таблицей в столбцах Z ,AA
Sub UnicWes() Dim i As Long Dim iLastRow As Long Dim dic As Object iLastRow = Cells(Rows.Count, "D").End(xlUp).Row Set dic = CreateObject("scripting.dictionary"): dic.comparemode = 1 For i = 3 To iLastRow 'в словарь уникальные dic.Item(CStr(Cells(i, "D").Value & " | " & Cells(i, "Q").Value)) = _ dic.Item(CStr(Cells(i, "D").Value & " | " & Cells(i, "Q").Value)) + Cells(i, "T") Next Range("Z3").Resize(dic.Count, 2) = Application.Transpose(Array(dic.Keys, dic.Items)) End Sub
Пользователь
Сообщений: 17 Регистрация: 27.10.2019
17.12.2019 17:22:04
Господа! Спасибо вам всем огромное. Вы творите МАГИЮ. ОЧЕНЬ помогло. А макрос вообще произвел впечатление, как зажигалка на индейца.))) Еще раз всем спасибо!
Пользователь
Сообщений: 17 Регистрация: 27.10.2019
17.12.2019 17:34:04
| Цитата |
|---|
| Kuzmich написал: Макросом рядом с таблицей в столбцах Z ,AA |
Спасибо, но я даже не знаю , как этим заклинанием пользоваться и куда его вставлять. Спасибо еще раз.
Пользователь
Сообщений: 831 Регистрация: 11.02.2021
08.03.2021 07:29:13
А как добавить в макрос Kuzmich-а. подсчет еще одной колонки H?
Т.е. суммы по G и H.
Прикрепленные файлы
- Sample_dup_2_2.xlsb (22.4 КБ)