Как объединить повторяющиеся строки и суммировать значения в Excel
Обновлено: 07.07.2024
В Excel часто возникает необходимость объединить повторяющиеся строки в диапазоне и суммировать их в отдельном столбце. Например, если у вас есть продажи по продуктам на листе (как показано ниже), вы можете захотеть суммировать продажи по каждому продукту. В следующем примере у вас есть продукты в столбце B (включая дубликаты), а суммы их продаж — в столбце C.
Вы можете объединить данные и получить общую сумму продаж для каждого продукта в отдельном диапазоне рядом с исходным.
<р>1. Выберите ячейку, в которую вы хотите получить новый диапазон данных (например, E1), и на ленте выберите Данные > Консолидировать.
Если в наборе данных нет строки заголовка, вам не нужно выбирать верхнюю строку.
Несколько лет назад в рамках моей основной работы одной из задач, с которыми мне приходилось сталкиваться, было объединение данных из разных рабочих книг, совместно используемых другими людьми.
И одной из частых задач было объединить данные таким образом, чтобы не было повторяющихся записей.
Например, ниже представлен набор данных, содержащий несколько записей для одного и того же региона.
Конечным результатом должен быть сводный набор данных, в котором каждая страна представлена только один раз.
В этом руководстве я покажу вам, как объединить повторяющиеся строки и суммировать значения для создания единого сводного набора данных.
Это руководство охватывает:
Объединение и суммирование данных с помощью параметра консолидации
Если все, что вам нужно сделать, это консолидировать данные и добавить все значения для повторяющихся записей, лучше всего использовать функцию консолидации в Excel.
Другой метод заключается в использовании сводной таблицы и обобщении данных (рассматривается далее в этом руководстве).
Предположим, у вас есть набор данных, как показано ниже, где название страны повторяется несколько раз.
Несмотря на то, что это уникальные записи, поскольку стоимость продаж различается, для целей отчетности вы можете удалить несколько экземпляров одной и той же страны и показать стоимость продаж в виде одной консолидированной суммы.
Ниже приведены шаги для этого:
Вышеуказанные шаги позволили бы объединить данные, удалив повторяющиеся записи и добавив значения для каждой страны.
В конечном результате вы получите уникальный список стран вместе со стоимостью продаж из исходного набора данных.
Я решил получить СУММУ значений из каждой записи.Вы также можете выбрать другие параметры, такие как «Количество», «Среднее» или «Макс./мин.».
В этом примере я показал вам, как объединить данные в один набор данных на листе. вы также можете использовать эту функцию для объединения данных из нескольких листов в одной книге и даже из нескольких разных книг.
Объединение и суммирование данных с помощью сводных таблиц
Сводная таблица — это швейцарский армейский нож для нарезки данных в Excel.
Он может легко дать вам сводку, представляющую собой комбинированный набор данных без дубликатов и значений, являющихся суммой всех похожих записей, и многое другое.
Недостаток этого метода по сравнению с предыдущим заключается в том, что он требует больше кликов и занимает на несколько секунд больше, чем предыдущий.
Предположим, у вас есть набор данных, как показано ниже, где название страны повторяется несколько раз, и вы хотите объединить эти данные.
Ниже приведены шаги по созданию сводной таблицы:
Вышеуказанные шаги вставят сводную таблицу в выбранную ячейку назначения.
Теперь со сводной таблицей мы можем делать все, что угодно, включая консолидацию нашего набора данных и удаление дубликатов.
Ниже приведены шаги для этого:
- Щелкните в любом месте области сводной таблицы, и справа откроется панель сводной таблицы.
- Перетащите поле «Страна» в область строк.
- Перетащите поле «Продажи» в область «Значения».
Вышеуказанные шаги обобщают данные и дают вам сумму продаж для всех стран.
Если это все, что вам нужно, и вам не нужна сводная таблица, вы можете скопировать данные и вставить их как значения в другое место, а сводную таблицу удалить.
Это также поможет вам уменьшить размер книги Excel.
Итак, это два быстрых и простых метода, которые вы можете использовать для консолидации данных, где они будут объединять повторяющиеся строки и суммировать все значения в этих записях.
Microsoft Excel – это программа для повышения производительности, используемая для обработки различных типов данных в различных областях. От домашних хозяйств до корпоративных офисов везде, где он используется. Это может помочь вам в ведении бухгалтерского учета и анализе данных, которые, если вы хотите рассчитать вручную, потребуют огромных времени и усилий. При вводе данных иногда может возникнуть необходимость ввести повторяющиеся данные (например, стоимость покупок одного и того же покупателя). Но при агрегировании данных вам потребуются сводные данные, которые будут представлять общую стоимость конкретной записи (то есть общую стоимость покупок клиента). Итак, здесь мы узнаем, как объединять повторяющиеся строки и суммировать их значения в Excel.
Практическая рабочая тетрадь
О рабочей тетради
В этой книге у нас есть список, содержащий платежи клиентов с 1 декабря 2021 года по 13 декабря 2021 года. Есть строки, содержащие одного и того же клиента в разные даты. Так что, если вы хотите получить общее представление о том, какая сумма взносов для каждого клиента. В этой статье мы увидим, как это можно сделать.
Объединение повторяющихся строк и суммирование значений в Excel (3 самых простых способа)
1. Использование функции удаления дубликатов и функции СУММЕСЛИ
- Скопируйте столбец имени клиента (убедитесь, что вы начинаете копирование с ЗаголовкаКлиент) с помощью CTRL+C или с ленты.
- Теперь при выборе скопированные ячейки переходят на вкладку "Данные".Затем на лентеИнструменты данных > Удалить дубликаты.
- Появится диалоговое окно для удаления дубликатов. Обязательно установите флажок «Мои данные имеют заголовки». Выберите перечисленные столбцы (в нашем случае «Клиент») и нажмите «ОК».
Теперь создайте новый заголовок рядом с клиентом, назвав его "Общая сумма к оплате".
- Выберите ячейку C5 под новым заголовком и напишите следующую функцию, используя СУММЕСЛИ
который относится к вычислению значения суммы F5 в соответствии с данными в D$5:D$17, соответствующими именам в диапазоне C$5:C$17. Формулу можно изменить соответствующим образом.
- Теперь скопируйте эту формулу в следующие несколько ячеек, перетащив ее вверх к ячейке, где заканчивается столбец "Клиент". Готово.
2. Использование консолидации
- Скопируйте заголовки исходных данных и вставьте их в нужное место для консолидированных данных.
- Выберите ячейку под первым скопированнымзаголовком. Перейдите на вкладку "Данные". Затем на лентеИнструменты данных > Консолидировать.
- Появится диалоговое окно для консолидации. В раскрывающемся списке «Функции» выберите «Сумма» (она уже должна быть там). Не забудьте отметить флажок "Левый столбец".
- Теперь самое главное. Нажмите на поле «Ссылка» и с помощью мыши выберите ячейки без заголовков(очень важно, чтобы вы это сделали) или вы можете вручную ввести диапазон ячеек (не забудьте чтобы использовать $, чтобы сделать ячейки абсолютными – например, в нашем примере это $C$5:$D$17.Знаете что?Используйте мышь, таким образом, Excel введет это автоматически). Затем нажмите "ОК".
Вы также можете использовать эту функцию для консолидации данных из нескольких рабочих листов в одной книге и даже несколько разных книг.
3. Использование сводной таблицы
Сводная таблица – это многофункциональная функция в Excel. с помощью сводной таблицы мы можем делать все, что угодно, включая объединение нашего набора данных и удаление дубликатов с их суммой. Это мощный инструмент. Использование сводной таблицы
- Выберите пустую ячейку, в которой мы создадим сводную таблицу. Перейдите на вкладку «Вставка». Затем выберите "Сводная таблица".
- Появится диалоговое окно «Создать сводную таблицу». Чтобы данные анализировались, выберите Выбрать таблицу или диапазон и выберите диапазон с помощью мыши так же, как Консолидация, но с заголовками. На этот раз в поле также появится новый термин для имени листа, поскольку сводную таблицу также можно использовать для получения данных из разных рабочих листов. Как и в нашем примере, это «3. Сводная таблица!$C$4:$D$17 для выбора ячеек с C4 по D17 на листе 3. Сводная таблица.
- Чтобы ввести данные в ячейку на текущем рабочем листе, выберите «Существующий рабочий лист», а затем выберите ячейку с помощью мыши или напишите «Имя рабочего листа»! Идентификатор ячейки . Убедитесь, что вы сделали ячейку абсолютной. Как в нашей камере это «3». Сводная таблица’!$F$4 для ввода значения в ячейку F4 на листе 3. Сводная таблица. Затем нажмите ОК.
- Щелкните в любом месте области сводной таблицы, и справа откроется панель сводной таблицы. Перетащите поле «Клиент» в область «Строки», а поле «Сумма к оплате» — в область «Значения».
- Теперь мы получили сумму взносов всех клиентов с их именами в сводной таблице.
Заключение
В этой статье мы узнали 3 способа удаления повторяющихся данных и суммирования их значений в Excel. Мы надеемся, что вы найдете эти методы интуитивно понятными и простыми в использовании. Эти типы проблем очень распространены во многих операциях Excel, поэтому мы постарались помочь вам решить эти проблемы с меньшими усилиями. Если у вас есть какие-либо предложения о том, как мы могли бы улучшить себя, это было бы здорово. Пожалуйста, оставьте отзыв о том, что вам понравилось в этой статье или что, по вашему мнению, мы могли бы улучшить в разделе комментариев. Обязательно оцените эту статью, спасибо.
В реальной статистике человек или элемент могут появляться в списке несколько раз с разными значениями. Мы не можем просто удалить любой из них. Вместо этого нам нужно объединить эти ячейки и просуммировать их значения. Так как же мы можем этого добиться?
Посмотрите на таблицу ниже. Очевидно, что в списке дважды появляются 2 человека. Я могу удалить один из них и вычислить сумму напрямую, но что, если список намного длиннее, а дубликатов намного больше?
В качестве примера возьму мой случай. Сначала скопируйте содержимое столбца A в столбец C. Это можно сделать, щелкнув заголовок столбца A, нажав Ctrl+C, затем щелкнув заголовок столбца C или ячейку C1, а затем нажав Ctrl+V.
Продолжайте выбирать содержимое в столбце C и нажмите Удалить дубликаты на вкладке Данные.
Выберите Продолжить текущий выбор и нажмите Удалить дубликаты…
Отметьте Мои данные имеют заголовки, так как столбец C содержит заголовок «Имя». Нажмите ОК, чтобы продолжить.
Теперь дубликаты в столбце C удалены.
Чтобы просуммировать значения, дважды нажмите D2 и введите формулу «=СУММЕСЛИ(A$2:A$14,C2,B$2:B$14)», которая относится к вычислению суммируемого значения C2 в соответствии с данными в B$2:B$14, соответствующие именам в диапазоне A$2:A$14. Формулу можно изменить в зависимости от ситуации.
Затем поместите курсор в нижний правый угол D2, пока он не станет маленьким черным крестиком. Перетащите его, чтобы выбрать все ячейки в столбце D (в моем случае это от D2 до D12). Таким образом, формула в D2 будет применена ко всем выделенным ячейкам.
Вы получили результат суммирования всех людей в списке без дубликатов.
Подходящее чтение
Вам также может понравиться
Заявление об авторских правах. Любое копирование или использование любых публикаций на этом веб-сайте должно осуществляться с письменного разрешения или авторизации Myofficetricks.
1 мысль о «Как объединить повторяющиеся ячейки и вычислить сумму в Excel»
К сведению, эта формула не работает, если ячейки находятся на разных страницах. Не знаю почему, но я точно скопировал это и даже учёл, что ссылки на страницы точны. По какой-то причине это просто не работает, поэтому обычно это бесполезно.
Читайте также: