Как в excel суммировать повторяющиеся значения

Отбор уникальных значений с суммированием по соседнему столбцу в MS EXCEL

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

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

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

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

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

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

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

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

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

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

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

Формула суммы по условию критерия выборки значений в Excel

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

Примеры формулы для суммы диапазонов с условием отбора в Excel

Ниже на рисунке представлен в таблице список счетов вместе с состоянием по каждому счету в виде положительных или отрицательных чисел. Допустим нам необходимо посчитать сумму всех отрицательных чисел для расчета суммарного расхода по движению финансовых средств. Этот результат будет позже сравниваться вместе с сумой положительных чисел с целью верификации и вывода балансового сальдо. Узнаем одинаковые ли суммы доходов и расходов – сойдется ли у нас дебит с кредитом. Для суммирования числовых значений по условию в Excel применяется логическая функция =СУММЕСЛИ():

Функция СУММЕСЛИ анализирует каждое значение ячейки в диапазоне B2:B12 и проверяет соответствует ли оно заданному условию (указанному во втором аргументе функции). Если значение меньше чем 0, тогда условие выполнено и данное число учитывается в общей итоговой сумме. Числовые значения больше или равно нулю игнорируются функцией. Проигнорированы также текстовые значения и пустые ячейки.

В приведенном примере сначала проверяется значения ячейки B2 и так как оно больше чем 0 – будет проигнорировано. Далее проверяется ячейка B3. В ней числовое значение меньше нуля, значит условие выполнено, поэтому оно добавляется к общей сумме. Данный процесс повторяется для каждой ячейки. В результате его выполнения суммированы значения ячеек B3, B6, B7, B8 и B10, а остальные ячейки не учитываются в итоговой сумме.

Обратите внимание что ниже результата суммирования отрицательных чисел находится формула суммирования положительных чисел. Единственное отличие между ними — это обратный оператор сравнения во втором аргументе где указывается условие для суммирования – вместо строки » 0″ (больше чем ноль). Теперь мы можем убедиться в том, что дебет с кредитом сходится балансовое сальдо будет равно нулю если сложить арифметически в ячейке B16 формулой =B15+B14.

Пример логического выражения в формуле для суммы с условием

Другой пример, когда нам нужно отдельно суммировать цены на группы товаров стоимости до 1000 и отдельно со стоимостью больше 1000. В таком случае одного оператора сравнения нам недостаточно ( =1000) иначе мы просуммируем сумму ровно в 1000 – 2 раза, что приведет к ошибочным итоговым результатам:

Это очень распространенная ошибка пользователей Excel при работе с логическими функциями!

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

Второй аргумент функции СУММЕСЛИ, то есть условие, которое должно быть выполнено, записывается между двойными кавычками. В данном примере используется символ сравнения – «меньше» ( ) меньше ( ), больше или равно (>=), меньше или равно ( =50″)

Как подсчитывать повторения в Excel?

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

Эту статью я хочу посвятить повторам тех значений, которые встречаются в вашей таблице, то есть мы научимся как подсчитывать повторения в 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». В созданное окно модуля вы вставляете код функции:

Подсчёт суммы значений, которые повторяются

Ситуация простая. 2 закладки: Sheet1 и Sheet2.

В закладке Sheet1 в столбике А имена, в стобике B (напротив) к-во денег.
Имена в столбике каждый день пополняются и могут повторяться. Каждый раз в столбике B (напротив имени) указывается к-во денег.

Пример:
Петя 50р.
Даша 40р.
Женя 68р.
Петя 32р.

В закладке Sheet2 в столбике A прописаны только имена.

Вопрос: Как в закладке Sheet2 напротив имён подсчитать сумму денег. Если имя было только один раз, то можно использовать vlookup. Но как посчитать, если тот же человек (тоже имя) в закладке Sheet1 было несколько раз и к-во денег было разное. Нужно в закладке Sheet2 напротив имён подсчитать сумму денег. Подскажите как?

Подсчёт количества значений в ячейках
Здравствуйте! Возникла такая проблема, — никак не могу составить формулу для подсчёта количества.

Подсчёт количества значений по условию
Подскажите, пожалуйста, где ошибка в формуле? Нужно посчитать количество значений в столбце D по.

Сложный подсчёт суммы последних чисел без нулей
Здравствуйте форумчане. Нужна помощь. не знаю как правильно описать тут. поэтому всё описание.

Подсчёт и поиск изменённых значений в ячейке
Подскажите пожалуйста 1) В 1 таблице название листа(Город Омск) заполняется в ручную участники.

Подсчёт суммы из ячеек разных листов при выборе диапазона
Здравствуйте! Есть макрос, который при выполнении считает сумму со всех ячеек всех листов книги.

Формула суммы по условию критерия выборки значений в Excel

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

Примеры формулы для суммы диапазонов с условием отбора в Excel

Ниже на рисунке представлен в таблице список счетов вместе с состоянием по каждому счету в виде положительных или отрицательных чисел. Допустим нам необходимо посчитать сумму всех отрицательных чисел для расчета суммарного расхода по движению финансовых средств. Этот результат будет позже сравниваться вместе с сумой положительных чисел с целью верификации и вывода балансового сальдо. Узнаем одинаковые ли суммы доходов и расходов – сойдется ли у нас дебит с кредитом. Для суммирования числовых значений по условию в Excel применяется логическая функция =СУММЕСЛИ():

Функция СУММЕСЛИ анализирует каждое значение ячейки в диапазоне B2:B12 и проверяет соответствует ли оно заданному условию (указанному во втором аргументе функции). Если значение меньше чем 0, тогда условие выполнено и данное число учитывается в общей итоговой сумме. Числовые значения больше или равно нулю игнорируются функцией. Проигнорированы также текстовые значения и пустые ячейки.

В приведенном примере сначала проверяется значения ячейки B2 и так как оно больше чем 0 – будет проигнорировано. Далее проверяется ячейка B3. В ней числовое значение меньше нуля, значит условие выполнено, поэтому оно добавляется к общей сумме. Данный процесс повторяется для каждой ячейки. В результате его выполнения суммированы значения ячеек B3, B6, B7, B8 и B10, а остальные ячейки не учитываются в итоговой сумме.

Обратите внимание что ниже результата суммирования отрицательных чисел находится формула суммирования положительных чисел. Единственное отличие между ними — это обратный оператор сравнения во втором аргументе где указывается условие для суммирования – вместо строки » 0″ (больше чем ноль). Теперь мы можем убедиться в том, что дебет с кредитом сходится балансовое сальдо будет равно нулю если сложить арифметически в ячейке B16 формулой =B15+B14.

Пример логического выражения в формуле для суммы с условием

Другой пример, когда нам нужно отдельно суммировать цены на группы товаров стоимости до 1000 и отдельно со стоимостью больше 1000. В таком случае одного оператора сравнения нам недостаточно ( =1000) иначе мы просуммируем сумму ровно в 1000 – 2 раза, что приведет к ошибочным итоговым результатам:

Это очень распространенная ошибка пользователей Excel при работе с логическими функциями!

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

Второй аргумент функции СУММЕСЛИ, то есть условие, которое должно быть выполнено, записывается между двойными кавычками. В данном примере используется символ сравнения – «меньше» ( ) меньше ( ), больше или равно (>=), меньше или равно ( =50″)