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

Суммировать повторяющиеся строки в 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 при работе с логическими функциями!

Читайте также:  Лучший ноутбук для программиста 2018

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

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

Чтобы создать условие Примените правило Пример
Значение равно заданному числу или ячейке с данным адресом. Не используйте знак равенства и двойных кавычек. =СУММЕСЛИ(B1:B10;3)
Значение равно текстовой строке. Не используйте знак равенства, но используйте двойные кавычки по краям. =СУММЕСЛИ(B1:B10;"Клиент5")
Значение отличается от заданного числа. Поместите оператор и число в двойные кавычки. =СУММЕСЛИ(B1:B10;">=50")
Значение отличается от текстовой строки. Поместите оператор и число в двойные кавычки. =СУММЕСЛИ(B1:B10;"<>выплата")
Значение отличается от ячейки по указанному адресу или от результата вычисления формулы. Поместите оператор сравнения в двойные кавычки и соедините его символом амперсант (&) вместе со ссылкой на ячейку или с формулой. =СУММЕСЛИ(A1:A10;" "&СЕГОДНЯ())
Значение содержит фрагмент строки Используйте операторы многозначных символов и поместите их в двойные кавычки =СУММЕСЛИ(A1:A10;"*кг*";B1:B10)

Во втором аргументе критериев условий можно использовать разные функции и формулы. Ниже на рисунке изображен список дат и присвоенных им значений. Важно отметить что сегодня на момент написания статьи дата – «03.11.2018». Чтобы суммировать числовые значения только по сегодняшней дате используйте формулу:

Чтобы суммировать только значения от сегодняшнего дня включительно и до конца периода времени воспользуйтесь оператором «больше или равно» (>=) вместе с соответственной функцией =СЕГОДНЯ(). Формула c операторам (>=):

="&СЕГОДНЯ();B2:B10)/B11′ >

Суммирование по неточному совпадению в условии критерия отбора

Во втором логическом аргументе критериев условий функции СУММЕСЛИ можно применять многозначные символы – (?)и(*) для составления относительных неточных запросов. Знак вопроса (?) – следует читать как любой символ, а звездочка (*) – это строка из любого количества любых символов или пустая строка. Например, нам необходимо просуммировать только защитные краски-лаки с кодом 3 английские буквы в начале наименования:

Суммируются все значения ячеек в диапазоне B2:B16 в соответствии со значениями в ячейках диапазона A2:A16, в которых после третьего символа фрагмент строки «-защита».

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

Читайте также:  Радиус описанной окружности в прямоугольный треугольник формула

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

Пусть исходная таблица содержит 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

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

I need a macro to look at the list of data below, provide a count of the number of instances it appears and sum the value of each of them. I know a pivot table or series of formulas could work but I’m doing this for a coworker and it has to be a ‘one click here’ kinda deal. The data is as follows:

Читайте также:  Как ускорить вращение кулера на процессоре

The end-result would look like this

Создан 15 фев. 10 2010-02-15 17:22:45 john

Have your co-worker learn how to use Excel. – AMissico 11 мар. 10 2010-03-11 08:27:28

I’d move it to a database, and use a query something like: ‘select name, count(*), sum(price) from your_table, group by name’ – Jerry Coffin 13 июл. 12 2012-07-13 19:40:40

4 ответа

Are you allowed to mess with the data? You could sort it by column A and then use the subtotal capability, at changes in column A sum column B and show total in column B (which I think are the default settings anyway).

If you may not sort it then I’d think literally just copying it to a new sheet and sorting the copy of it would be the easiest way forward.

[Sorry, I forgot the "count" bit. You can add a new column filled with 1s and subtotal that in the same way.]

Создан 15 фев. 10 2010-02-15 17:42:58 Vicky

Excel provides these things

sumif()
countif()
Data, Filter, Advanced Filter, Unique records only

Gives you all you need, you can record as a macro.

Создан 15 фев. 10 2010-02-15 18:09:50 JD_55

JD, would you elaborate on how to set that up? Thanks, John – john 15 фев. 10 2010-02-15 20:10:52

Put your test data into columns A and B rows 1 to 8 In C2 enter =COUNTIF($A$2:$A$8,A2) and copy it down to cells below In D2 enter =SUMIF($A$2:$A$8,A2,$B$2:$B$8) and copy it down What you have then is every time Smith appears you get 3 in column C and 350 in column D, but you see that 3 times because Smith appears 3 times. You can use the Advanced Filter to hide the duplicates. – JD_55 16 фев. 10 2010-02-16 09:44:36

Pivot Table is much easier to set up, easier for the user to change, and less complicated. Moreover, your suggestion would be better served using Array Formulas. – AMissico 11 мар. 10 2010-03-11 08:40:56

Just create the pivot table in a new sheet, that references that data. To update it tell your co-worker to right click the pivot table and select refresh. No need for a macro or code for this.

Создан 15 фев. 10 2010-02-15 18:38:10 JRL

Ссылка на основную публикацию
Сталкер зов припяти лучшее оружие в игре
S.T.A.L.K.E.R.: Call of Pripyat 4,260 уникальных посетителей 105 добавили в избранное "Уникальная модель пистолета СИП-т М200. Была выпущена малой партией...
Соевый соус стебель бамбука классический отзывы
Всем доброго дня!Много мнений по этому поводу, как вы считаете, соевый соус или морская соль, что менее вредно для организма....
Соевый соус ямаса отзывы
Полное наименование: Соевый Соус классический (натурально сваренный) Изготовитель: Yamasa Corporation Все характеристики Соевый соус Yamasa: Результаты теста Достоинства Безопасный Не...
Сталкер зов припяти много оружия
Для Всех любителей отличного отечественного шутера S.T.A.L.K.E.R.Зов Припяти представлен новый Оружейный мод Автоматы Штурмовые винтовки:1. АК-472. АКS-47 тактический3. АК-113 "Монгол"4....
Adblock detector