Суммировать повторяющиеся строки в excel

У меня длинный список имен продуктов и номеров этих продуктов, я хочу объединить все повторяющиеся записи и добавить их номера. Поэтому, если у меня есть 2 записи для Widget X, и каждая запись имеет 3 продукта, я хочу объединить это с одной записью для Widget X с 6 продуктами.

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

То, что я пытаюсь получить, является IF (любое поле в столбце A = 3792, затем суммирует все связанные с ним количественные поля в столбце C)

Есть ли у кого-нибудь идеи?

Excel SUMIF() -Formula может помочь вам выполнить это.

как описано, синтаксис

Ищет поля внутри range , которые соответствуют criteria и суммируют значения в sum_range (в том же индексе, где критерии были найдены в диапазоне, соответственно).

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

Для определенного столбца (пусть возьмите C) создайте временный столбец и добавьте значение 1 в каждую строку.

Например, в столбце "Сумма А:" введите формулу:

В то время как D1 – это ячейка, содержащая номер 3792 (сверху), а 2 в C2 – текущая строка, в которой находится формула.

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

Это будет результат:

Просто просто подведите итог в конце, и вы получили свои результаты. Если нужно добавить еще один номер, чем 1 , просто добавьте еще один столбец, содержащий соответствующее значение, затем замените его в IF -formula.

вам может понадобиться автоматизировать это еще больше, используя HLOOKUP() для номера виджета вверху.

Используйте функцию sumif. См. этот блог для объяснения.

Предполагая, что значения начинаются в A1, вставьте эту функцию в D1 и скопируйте в D1-D16, чтобы получить суммы для каждого поля (конечно, были бы дубликаты)

Поскольку у вас есть отсортированный список, я бы использовал встроенную функцию SUBTOTAL вместо формулы. Выделите все свои данные, затем перейдите в Data > Subtotal Затем настройте его на основе изменения продукта.

Читайте также:  Что делать если steamcmd сразу закрывается

Другой вариант – создать сводную таблицу, которая будет суммировать элементы. Я большой поклонник хранения резюме и данных отдельно.

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

Я взял все "Столбец А" и скопировал в новый лист (или вправо или где-то в сторону). Выделил скопированный набор данных и удаленные дубликаты, оставив новый список только одного экземпляра значений из "Столбец А" . Позволяет называть этот новый набор данных "Столбец D". Затем позвольте вывести итоговый столбец "Column E"

Затем я использовал SUMPRODUCT в сочетании с SUMIF в "Столбец E", чтобы получить такую ​​формулу:

Значение столбца E = SUMPRODUCT (SUMIF ( Исходная колонка A Диапазон , Подходящее значение в столбце D , Оригинал Диапазоны столбцов C ))

Это суммирует все экземпляры и выводит общую сумму.

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

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

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

Задача

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

Решение

Создадим Динамические диапазоны: Регионы (названия регионов из столбца А) и Продажи (объемы продаж из столбца B).

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

Для создания списка уникальных значений введем в ячейку D2 формулу массива:
=ЕСЛИОШИБКА(ИНДЕКС(Регионы; ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$1:D1;Регионы);0));"")

Не забудьте при вводе вышеуказанной формулы нажать CTRL+SHIFT+ENTER. Затем необходимо скопировать формулу вниз (размерность списка уникальных значений должна совпадать с размерностью исходного списка).

Для подсчета суммарных продаж в столбце E используем мощную функцию СУММПРОИЗВ() :
=СУММПРОИЗВ((Регионы=D2)*Продажи)

Читайте также:  Фонарь эра fa55m схема

Отображение нулей в строках, в которых нет регионов, уберем пользовательским форматом # ##0;-# ##0; (см. статью Скрытие значений равных 0).

Тестируем

1. Введите в ячейку А11 новый регион – Китай
2. Введите объем продаж – 100
3. Введите в А12Китай
4. Введите объем продаж – 500
5. В соседней таблице справа в ячейке D7 будет выведено название региона Китай с суммарным объемом продаж 600

СОВЕТ:
Другим подходом к решению этой задачи является использование Сводных таблиц (см. файл примера ).

Добрый день, уважаемый читатель!

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

Для начала рассмотрим, как выглядят столбики с данными, повторения в которых, мы собственно и будем считать. К примеру, возьмём список сотрудников, которые совершают продажи. Теперь можно посчитать, сколько раз сотрудник производил продажи, то есть попросту считаем сколько повторений его фамилии в столбике. Это возможно произвести несколькими способами:

Используя функцию СЧЁТЕСЛИ

В Excel произвести такой подсчёт чрезвычайно просто, достаточно задействовать функцию СЧЁТЕСЛИ и она за несколько секундок всё за вас сделает. В нашем случае формула будет следующего вида:

=СЧЁТЕСЛИ($B$2:$B$11;B15) В первом аргументе «диапазон» $B$2:$B$11, указываем тот диапазон ячеек, в котором и будет производиться подсчёт повторяющихся данных. Важно! Указывать случайный диапазон данных недопустимо. Его особенностью является то, что он может быть только диапазоном ячеек или ссылкой на определённую ячейку.

Вторым аргументом «критерий» ставим указание на ячейку, по которому будет производиться подсчёт аналогичных данных. Если он один вы можете его прописать вручную как текстовое слово и вместо адреса ячейки «B15» указать «Нагаев А.В.», результат будет аналогичным, но только в одном конкретном случае, возможность автоматизации таблицы тогда значительно сократится.

Дополнительная информация! Кроме прямого указания поиска данных, функция СЧЁТЕСЛИ умеет работать с символами подстановки. Таких знаков используют двух видов «?» и «*», применять их возможно только при работе с текстовыми символами. Знак «*» позволяет заменить абсолютно любое количество значений, а Знак «?» производит замену только одного символа.

Читайте также:  Чем открыть миди файл

Для работы с числовыми значениями необходимо применять знаки операторов сравнения: «>», « » и «=». К примеру, для подсчёта числовых значений больше «нуля» прописывайте «>0», а для подсчёта непустых ячеек нужен указать «<>».

Используем функцию СЧЁТЕСЛИМН

Когда у вас возникает необходимость подсчитывать повторения в Excel, но уже по нескольким критериям, тогда нужно работать с функцией СЧЁТЕСЛИМН, которая легко и просто сможет это выполнить.

В моём примере я добавлю категорию продаж по городам и используя формулы соберу необходимые мне повторы:

=СЧЁТЕСЛИМН($B$2:$B$11;B14;$C$2:$C$11;C14) Замечу, что орфография функции абсолютно аналогична предыдущей функции СЧЁТЕСЛИ, разница состоит только в их количестве. В нашем примере их два, но функция умеет работать и со 127 диапазонами.

Работаем с функцией ДЛСТР

Теперь давайте рассмотрим ситуацию, когда не всё так просто и упорядоченно, когда информация сбита в одну ячейку, к примеру, «Нагаев Гаврош Каропачёв Козубенко Нагаев Гаврош Козубенко Нагаев Нагаев». В этом случае статистические функции нам не помогут, необходимо считать символы и сверять повторы значений с указанным эталоном. Для этих целей есть много иных полезных функций, используя которые это можно провернуть достаточно просто:

=(ДЛСТР($B$2) -ДЛСТР(ПОДСТАВИТЬ($B$2;B5;"")))/ДЛСТР(B5) Итак, используя функцию ДЛСТР, мы считаем, сколько же символов содержится в ячейке «$B$2» и «B5», результат будет «71». А потом с помощью функции ПОДСТАВИТЬ производим замену текущего значения на «пусто», получаем результат «47». Следующим действием отнимаем от общего количества символов наш остаток «71-47=24» и делим на количество символов в одном значении «24/6=4», как результат получаем сколько раз в строке, встречается необходимый результат… Ответ: 4. (Это результат рассматривая только первую строку поиска).

С помощью функций VBA

Последний рассматриваемый вариант, это подсчёт количества повторений с помощью, созданной в VBA функции. Функции я не писал, а просто предлагаю вам найденный ранее вариант, чтобы упростить вашу работу.

Для начала вам нужно запустить редактор макросов VBA и вставить новый модуль с помощью команд «Insert» — «Module». В созданное окно модуля вы вставляете код функции:

Оцените статью
Добавить комментарий

Adblock detector