Как в эксель удалить дубликаты и сложить значения

Как в эксель удалить дубликаты и сложить значения

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Поиск и удаление повторений

В некоторых случаях повторяющиеся данные могут быть полезны, но иногда они усложняют понимание данных. Используйте условное форматирование для поиска и выделения повторяющихся данных. Это позволит вам просматривать повторения и удалять их по мере необходимости.

Выберите ячейки, которые нужно проверить на наличие повторений.

Примечание: В Excel не поддерживается выделение повторяющихся значений в области «Значения» отчета сводной таблицы.

Выберите Главная >условное форматирование >выделение правил ячеек >повторяющихся значений.

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

Удаление повторяющихся значений

При использовании функции Удалить дубликаты повторяющиеся данные удаляются без возможности восстановления. Чтобы случайно не потерять необходимые сведения, перед удалением повторяющихся данных рекомендуется скопировать исходные данные на другой лист.

Выделите диапазон ячеек с повторяющимися значениями, который нужно удалить.

Совет: Перед попыткой удаления повторений удалите все структуры и промежуточные итоги из своих данных.

Выберите Данные >Удалить дубликаты, а затем в разделе Столбцы проверка или снимите флажок столбцов, в которых нужно удалить дубликаты. Например, на данном листе в столбце «Январь» содержатся сведения о ценах, которые нужно сохранить. Поэтому флажок Январь в поле Удаление дубликатов нужно снять.

Нажмите кнопку ОК.

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

Фильтр уникальных значений или удаление повторяющихся значений

В Excel существует несколько способов фильтрации уникальных значений или удаления повторяющихся значений:

• Чтобы отфильтровать уникальные значения, щелкните Данные >Сортировка & Фильтр >Дополнительно.
• Чтобы удалить повторяющиеся значения, щелкните Data > Data Tools>Remove Duplicates (Удалить дубликаты).
• Чтобы выделить уникальные или повторяющиеся значения, используйте команду Условное форматирование в группе Стиль на вкладке Главная .

Сведения о фильтрации уникальных значений или удалении повторяющихся значений

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

Повторяющееся значение — это значение, в котором все значения по крайней мере в одной строке идентичны всем значениям в другой строке. Сравнение повторяющихся значений зависит от того, что отображается в ячейке, а не от базового значения, хранящегося в ячейке. Например, если у вас одно и то же значение даты в разных ячейках, одна из которых имеет формат «08.03.2006», а вторая — «8 марта 2006 г.», значения будут уникальными.

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

Фильтрация уникальных значений

1. Выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
2. Щелкните Данные > Дополнительно (в группе Фильтр & сортировки ).

Важно: Вставьте текст оповещения здесь.

Во всплывающем окне Расширенный фильтр выполните одно из следующих действий.

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

• Щелкните Фильтровать список на месте.

Чтобы скопировать результаты фильтра в другое расположение, выполните следующие действия:

• Установите переключатель скопировать результат в другое место.
• В поле Копировать введите ссылку на ячейку.
• Кроме того, нажмите кнопку Свернуть диалоговое окно , чтобы временно скрыть всплывающее окно, выберите ячейку на листе и нажмите кнопку Развернуть.
• Установите флажок Только уникальные записи и нажмите кнопку ОК.

Уникальные значения из диапазона будут скопированы в новое расположение.

Удаление повторяющихся значений

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

Так как данные удаляются без возможности восстановления, рекомендуется скопировать исходный диапазон ячеек или таблицу на другой лист или книгу перед удалением повторяющихся значений.

1. Выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
2. На вкладке Данные щелкните Удалить дубликаты (в группе Инструменты для работы с данными ).
3. Выполните одно или несколько из указанных ниже действий.
4. В разделе Столбцы выберите один или несколько столбцов.
5. Чтобы быстро выбрать все столбцы, нажмите кнопку Выбрать все.
6. Чтобы быстро очистить все столбцы, щелкните Отменить выбор всех. Если диапазон ячеек или таблицы содержит много столбцов и требуется выбрать только несколько столбцов, может оказаться проще нажать кнопку Отменить выбор всех, а затем в разделе Столбцы выберите эти столбцы.

Примечание: Данные будут удалены из всех столбцов, даже если на этом шаге не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключ, используемый для поиска дубликатов, будет значением ОБА столбца & Column2. Если в этих столбцах найден дубликат, будет удалена вся строка, включая другие столбцы в таблице или диапазоне.

Проблемы при удалении дубликатов из контурных или промежуточных данных

Невозможно удалить повторяющиеся значения из контурных данных, которые выделены или имеют промежуточные итоги. Чтобы удалить дубликаты, необходимо удалить как контур, так и промежуточные итоги. Дополнительные сведения см. в разделах Структура списка данных на листе и Удаление промежуточных итогов.

Условное форматирование уникальных или повторяющихся значений

Примечание: Поля в области «Значения» отчета сводной таблицы нельзя условно форматировать по уникальным или повторяющимся значениям.

Быстрое форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в группе Стиль щелкните небольшую стрелку для параметра Условное форматирование, а затем выберите пункт Правила выделения ячеек и выберите Повторяющиеся значения.

Важно: Вставьте текст оповещения здесь.

Введите нужные значения, а затем выберите формат.

Расширенное форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в группе Стили щелкните стрелку условного форматирования, а затем щелкните Управление правилами , чтобы открыть всплывающее окно Диспетчер правил условного форматирования .

Важно: Вставьте текст оповещения здесь.

Выполните одно из следующих действий:

• Чтобы добавить условный формат, щелкните Создать правило , чтобы открыть всплывающее окно Новое правило форматирования .
• Чтобы изменить условный формат, сначала убедитесь, что в разделе Показать правила форматирования для списка выбран соответствующий лист или таблица. При необходимости выберите другой диапазон ячеек, нажав кнопку Свернутьво всплывающем окне Применяется к временно скрыть его. Выберите новый диапазон ячеек на листе, а затем снова разверните всплывающее окно . Выберите правило и щелкните Изменить правило , чтобы открыть всплывающее окно Изменение правила форматирования .

В группе Выберите тип правила выберите параметр Форматировать только уникальные или повторяющиеся значения.

В списке Форматировать всев разделе Изменение описания правила выберите уникальное или повторяющееся.

Щелкните Формат , чтобы открыть всплывающее окно Формат ячеек .

Выберите число, шрифт, границу или формат заливки, которые нужно применить, когда значение ячейки удовлетворяет условию, и нажмите кнопку ОК. Можно выбрать несколько форматов. Выбираемые форматы отображаются на панели предварительного просмотра .

В Excel в Интернете можно удалить повторяющиеся значения.

Удаление повторяющихся значений

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

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

1. Выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
2. На вкладке Данные щелкните Удалить дубликаты .
3. В диалоговом окне Удаление дубликатов снимите флажки столбцов, в которых не нужно удалять повторяющиеся значения.

Примечание: Данные будут удалены из всех столбцов, даже если на этом шаге не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключ, используемый для поиска дубликатов, будет значением ОБА столбца & Column2. При обнаружении дубликата в столбцах Column1 и Column2 будет удалена вся строка, включая данные из Столбца 3.

Примечание: Если вы хотите вернуть данные, просто нажмите кнопку Отменить (или нажмите клавиши CTRL+Z на клавиатуре).

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

покупка

Объедините повторяющиеся строки и просуммируйте значения в Excel (простые приемы)

В Excel часто встречается набор данных с повторяющимися записями. Часто вы можете столкнуться с диапазоном данных, где ключевой задачей является эффективное объединение этих повторяющихся строк с одновременным суммированием значений в соответствующем столбце, как показано на следующем снимке экрана. В этом контексте мы рассмотрим несколько практических методов, которые помогут вам консолидировать повторяющиеся данные и агрегировать связанные с ними значения, повышая наглядность и полезность ваших книг Excel.

Объедините повторяющиеся строки и просуммируйте значения

• С помощью функции консолидации
• С мощной функцией – Kutools
• Со сводной таблицей
• С кодом VBA

Объедините повторяющиеся строки и просуммируйте значения с помощью функции консолидации.

Консолидация — это полезный инструмент для консолидации нескольких листов или строк в Excel. С помощью этой функции мы можем объединять повторяющиеся строки и быстро и легко суммировать их соответствующие значения. Пожалуйста, выполните следующие действия:

Шаг 1. Выберите ячейку назначения

Выберите, где вы хотите, чтобы консолидированные данные отображались.

Шаг 2. Получите доступ к функции консолидации и настройте консолидацию.

1. Нажмите Данные >Консолидировать, см. снимок экрана:
   
2. В Консолидировать диалоговое окно:
   • (1.) Выберите Сумма от Функция раскрывающийся список;
   • (2.) Нажмите, чтобы выбрать диапазон, который вы хотите консолидировать в Справка коробка;
   • (3.) Проверить Верхний ряд и Левая колонка от Используйте ярлыки в вариант;
   • (4.) Наконец, нажмите OK кнопку.
   • 

Результат:

Excel объединит все дубликаты, найденные в первом столбце, и суммирует их соответствующие значения в соседних столбцах, как показано на следующем снимке экрана:

• Если диапазон не включает строку заголовка, убедитесь, что снимите флажок Верхняя строка из Используйте ярлыки в опцию.
• Благодаря этой функции расчеты можно консолидировать только на основе первого столбца (крайнего левого) данных.

Объедините повторяющиеся строки и суммируйте значения с помощью мощной функции – Kutools

Если вы установили Kutools for Excel, Его Расширенные ряды комбинирования Функция позволяет вам легко комбинировать повторяющиеся строки, предоставляя возможности суммировать, подсчитывать, усреднять или выполнять другие вычисления с вашими данными. Более того, эта функция не ограничивается одним ключевым столбцом, она может обрабатывать несколько ключевых столбцов, что значительно упрощает сложные задачи консолидации данных.

Внимание : Если вы хотите использовать это Расширенные ряды комбинирования особенность, пожалуйста скачайте и установите Kutools для Excel первый.

После установки Kutools for Excel, выберите диапазон данных и нажмите кнопку Кутулс > Слияние и разделение > Расширенные ряды комбинирования.

В Расширенные ряды комбинирования диалоговое окно, установите следующие операции:

1. Щелкните имя столбца, на основе которого вы хотите объединить дубликаты. Здесь я нажму «Продукт», а затем выберите Основной ключ из раскрывающегося списка в Эксплуатация столбец;
2. Затем выберите имя столбца, значения которого вы хотите суммировать, а затем выберите Сумма из раскрывающегося списка в Эксплуатация столбец;
3. Что касается остальных столбцов, вы можете выбрать нужную вам операцию, например объединение значений с определенным разделителем или выполнение определенного расчета; (этот шаг можно проигнорировать, если у вас только два столбца)
4. Наконец, вы можете просмотреть объединенный результат, а затем нажать OK кнопку.

Результат:

Теперь повторяющиеся значения в ключевом столбце объединяются, а другие соответствующие значения суммируются, как показано на следующем снимке экрана:

• С помощью этой полезной функции вы также можете объединять строки на основе повторяющихся значений ячеек, как показано в следующей демонстрации:
• Эта особенность поддерживает отмену, если вы хотите восстановить исходные данные, просто нажмите Ctrl + Z.
• Чтобы применить эту функцию, пожалуйста, скачайте и установите Kutools для Excel первый.

Объедините повторяющиеся строки и просуммируйте значения с помощью сводной таблицы.

Сводная таблица в Excel предоставляет динамический способ реорганизации, группировки и суммирования данных. Эта функция становится невероятно полезной, когда вы сталкиваетесь с набором данных, заполненным повторяющимися записями, и вам необходимо суммировать соответствующие значения.

Шаг 1. Создание сводной таблицы

1. Выберите диапазон данных. А затем перейдите в Вставить вкладку и щелкните Сводная таблица, см. снимок экрана:
   
2. В появившемся диалоговом окне выберите, где вы хотите разместить отчет сводной таблицы. Вы можете поместить его на новый или существующий лист по мере необходимости. Затем нажмите OK. Смотрите скриншот:
   
3. Теперь сводная таблица вставляется в выбранную целевую ячейку. Смотрите скриншот:
   

Шаг 2. Настройка сводной таблицы:

1. В Поля сводной таблицы перетащите поле, содержащее дубликаты, на панель Строка область. Это сгруппирует ваши дубликаты.
2. Затем перетащите поля со значениями, которые вы хотите суммировать, в Наши ценности область. По умолчанию Excel суммирует значения. Посмотрите демо ниже:

Результат:

В сводной таблице теперь отображаются данные с объединенными дубликатами и суммированными их значениями, что обеспечивает четкое и краткое представление для анализа. Смотрите скриншот:

Объедините повторяющиеся строки и суммируйте значения с кодом VBA

Если вас интересует код VBA, в этом разделе мы предоставим код VBA для консолидации повторяющихся строк и суммирования соответствующих значений в других столбцах. Пожалуйста, выполните следующие действия:

Шаг 1. Откройте редактор модулей листов VBA и скопируйте код.

1. Удерживая нажатой ALT + F11 ключи в Excel, чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить >Модулии вставьте следующий код в Модули Окно.
   Код VBA: объедините повторяющиеся строки и просуммируйте значения

Sub CombineDuplicateRowsAndSumForMultipleColumns() 'Update by Extendoffice Dim SourceRange As Range, OutputRange As Range Dim Dict As Object Dim DataArray As Variant Dim i As Long, j As Long Dim Key As Variant Dim ColCount As Long Dim SumArray() As Variant Dim xArr As Variant Set SourceRange = Application.InputBox("Select the original range:", "Kutools for Excel", Type:=8) If SourceRange Is Nothing Then Exit Sub ColCount = SourceRange.Columns.Count Set OutputRange = Application.InputBox("Select a cell for output:", "Kutools for Excel", Type:=8) If OutputRange Is Nothing Then Exit Sub Set Dict = CreateObject("Scripting.Dictionary") DataArray = SourceRange.Value For i = 1 To UBound(DataArray, 1) Key = DataArray(i, 1) If Not Dict.Exists(Key) Then ReDim SumArray(1 To ColCount - 1) For j = 2 To ColCount SumArray(j - 1) = DataArray(i, j) Next j Dict.Add Key, SumArray Else xArr = Dict(Key) For j = 2 To ColCount xArr(j - 1) = xArr(j - 1) + DataArray(i, j) Next j Dict(Key) = xArr End If Next i OutputRange.Resize(Dict.Count, ColCount).ClearContents i = 1 For Each Key In Dict.Keys OutputRange.Cells(i, 1).Value = Key For j = 1 To ColCount - 1 OutputRange.Cells(i, j + 1).Value = Dict(Key)(j) Next j i = i + 1 Next Key Set Dict = Nothing Set SourceRange = Nothing Set OutputRange = Nothing End Sub 

Шаг 2. Выполните код

1. После вставки этого кода нажмите F5 ключ для запуска этого кода. В поле подсказки выберите диапазон данных, который вы хотите объединить и суммировать. Затем нажмите OK.
   
2. И в следующем окне подсказки выберите ячейку, в которую вы будете выводить результат, и нажмите OK.
   

Результат:

Теперь повторяющиеся строки объединены, и их соответствующие значения суммированы. Смотрите скриншот:

Объединение и суммирование повторяющихся строк в Excel может быть простым и эффективным. Выбирайте простую функцию консолидации, расширенные инструменты Kutools, аналитические сводные таблицы или гибкое кодирование VBA, чтобы найти решение, соответствующее вашим навыкам и потребностям. Если вы хотите узнать больше советов и рекомендаций по Excel, на нашем веб-сайте представлены тысячи руководств. нажмите здесь, чтобы получить к ним доступ. Спасибо за внимание, и мы с нетерпением ждем возможности предоставить вам еще больше полезной информации в будущем!

Статьи по теме:

• Объедините несколько строк в одну на основе дубликатов
• Возможно, у вас есть диапазон данных, в столбце «Название продукта» A есть несколько повторяющихся элементов, и теперь вам нужно удалить повторяющиеся записи в столбце A, но объединить соответствующие значения в столбце B. Как можно выполнить эту задачу в Excel? ?

• Vlookup и возврат нескольких значений без дубликатов
• Иногда вам может понадобиться vlookup и сразу вернуть несколько совпадающих значений в одну ячейку. Но если в возвращенные ячейки внесены повторяющиеся значения, как можно игнорировать дубликаты и сохранять только уникальные значения при возврате всех совпадающих значений, как показано на следующем снимке экрана в Excel?

• Объединить строки с одинаковым идентификатором/именем
• Например, у вас есть таблица, как показано на скриншоте ниже, и вам нужно объединить строки с идентификаторами заказов, есть идеи? В этой статье вы найдете два решения.