Какая Функция в Microsoft Excel Суммирует Значения

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

ПРОБЛЕМА: Как суммировать данные по нескольким критериям??

Из указанной нами базы данных A1:G16 функция БДСУММ извлекает и суммирует данные столбца Количество (аргумент «Поле» = F1) по заданным в ячейках I1:K2 (Продавец = Иванов; Продукция = Карандаши; Месяц = Январь) критериям.

ПРИМЕЧАНИЯ: Количество критериев суммирования ограничено оперативной памятью.

ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая версия Excel

Функция СУММПРОИЗВ формирует массивы из значений ИСТИНА и ЛОЖЬ, согласно выбранным критериям, в памяти Excel.

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

Очевидно что если например, D2=Карандаши, то значение будет равно ИСТИНА, а если D3=Папки, то ЛОЖЬ (так как критерием отбора товара в нашем примере является значение Карандаши).

Теперь осталось только умножить полученный массив на массив содержащий данные, которые нам необходимо в итоге просуммировать ( диапазон F2:F16) и собственно, просуммировать то что на 0 не умножилось.

Теперь сравните полученные при помощи формулы и при пошаговом вычислении на листе массивы (выделены красным).

МИНУСЫ: СУММПРОИЗВ — «тяжёлая» формула массива. При вычислениях на больших диапазонах данных заметно увеличивается время пересчёта.

ПРИМЕЧАНИЯ: Количество обрабатываемых массивов ограничено 255.

КАК ЭТО РАБОТАЕТ: Точно так же как и Способ №2. Есть только два отличия — данная формула вводится нажатием Ctrl+Shift+Enter, а не просто нажатием Enter и массив 0-й и 1-ц не умножается на диапазон суммирования, а отбирается с помощью функции ЕСЛИ.

МИНУСЫ: Формулы массива при вычислениях на больших диапазонах данных заметно увеличивают время пересчёта.

ПРИМЕЧАНИЯ: Количество обрабатываемых массивов ограничено 255.

ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая версия Excel

КАК ЭТО РАБОТАЕТ:
Функция СУММЕСЛИМН требует обязательно указать диапазон суммирования (в нашем случае F2:F16) и хотя бы одну пару Диапазон/Условие.
По сути эта функция «один в один» повторяет действие функции СУММПРОИЗВ в нашем примере, но не является формулой массива.

МИНУСЫ: нет.

ОБЛАСТЬ ПРИМЕНЕНИЯ: Начиная с версии Excel 2007.

ПРИМЕЧАНИЯ: Количество пар диапазон/критерий ограничено 127

0 Спам

0 Спам

0 Спам

А в данном конкретном примере для функции СУММПРОИЗВ Примечание не относиться («Количество обрабатываемых массивов ограничено 255.») ?
Массив ведь всего один.

0 Спам

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

0 Спам

0 Спам

Натолкнулся на одну неприятную особенность при использовании данной формулы — если база данных находится в другом файле, то она обязательно должна быть открыта, иначе формула не будет считать. У меня Офис 2007. Никто не в курсе, в 2010 или 2013 офисе данная оплошность исправлена?

0 Спам

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

ЗЫ Это не оплошность. Так устроены функции листа. Независимо от версий Excel

0 Спам

Упс, простите, писал, о чем думал — а думал я о «суммеслимн». =) «суммпроизв» слишком тяжелая. Стоит целерон 2,1гГц и 512мб оперативки — страшно повесить систему.

0 Спам

Разница в быстродействии между СУММЕСЛИМН() и СУММПРОИЗВ() небольшая, обе формулы довольно «тяжелые», хотя СУММЕСЛИМН() немного быстрее. В любом случае, если исходных данных много — лучше пользоваться сводной таблицей. Это в разы быстрее и удобнее

0 Спам

А как сделать количество, если количество больше 50, за январь — если столбец с месяцем имеет формат даты ДД,ММ,ГГГГ?

0 Спам

0 Спам

Добрый день, подскажите, пожалуйста, если в одном из диапазонов условий (новый столбец E) будут значения дат в таком формате 04.05.2014, 03.01.2013 и т.п.
Одним из условий будет меньше или равно, допустим 03.01.2014, которое будет в ячейке N2. Как будет выглядеть формула?
В моем случае это не получается сделать: выдает ошибку.
Формула =СУММЕСЛИМН(G2:G16;B2:B16;J2;D2:D16;K2;A2:A16;L2;E2:E16;<=N2).

0 Спам

0 Спам

0

0 Спам

0 Спам

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

0 Спам

Хорошо всё расписано, но есть один вопрос. Если в столбце «А» указан не месяц, а конкретная дата, при этом суммировать надо диапазон, к примеру от «10 января до 27 января»

0 Спам

Добрый день, подскажите пож. как быть если в одном диапазоне несколько критериев, вариант сумеслимн(F:F;D:D;»карандаши»)+сумеслимн(F:F;D:D;»папки») не подходит.

0

0 Спам

подскажите, как построить формулу если одно условие — поиск значения по горизонтали, второе- по вертикали? выдает ошибку
заранее спасибо!

0

0 Спам

0

Никак
Нет такой опции в комментариях к статьям
Но это ведь Вы начали задавать вопросы там, где это делать не положено

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

Как сделать сумму в excel по горизонтали?

Функция List.Range выдает указанный диапазон в столбце продажи в качестве выходных данных, и этот диапазон изменяется в зависимости от значения индекса. Например, для первой записи диапазон будет просто первой ценой продажи. И по мере того, как вы спускаетесь по ячейкам, этот диапазон будет расширяться.

Расчет промежуточной суммы с табличными данными

Если у вас есть табличные данные (т. е. Таблица в Excel, которая не преобразована в таблицу Excel), вы можете использовать несколько простых формул для вычисления промежуточных итогов.

Использование оператора сложения

Предположим, у вас есть данные о продажах по датам, и вы хотите рассчитать промежуточную сумму в столбце C.

Ниже приведены шаги для этого.

Шаг 1. В ячейке C2, которая является первой ячейкой, в которой вы хотите получить промежуточную сумму, введите

Это просто получит те же значения продаж в ячейке B2.

Шаг 2 — В ячейке C3 введите следующую формулу:

Шаг 3 — Примените формулу ко всему столбцу. Вы можете использовать маркер Fill, чтобы выбрать и перетащить его, или просто скопировать и вставить ячейку C3 во все оставшиеся ячейки (что автоматически настроит ссылку и даст правильный результат).

Это действительно простой метод, который хорошо работает в большинстве случаев.

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

Есть только один недостаток — если вы удалите любую из существующих строк в этом наборе данных, все ячейки ниже, которые вернут ссылочную ошибку (#REF!)

Если это возможно с вашим набором данных, используйте следующий метод, который использует формулу SUM

Использование SUM с частично заблокированной ссылкой на ячейку

Предположим, у вас есть данные о продажах по датам, и вы хотите рассчитать промежуточную сумму в столбце C.

Ниже приведена формула СУММ, которая даст вам промежуточную сумму.

Позвольте мне объяснить, как работает эта формула.

В приведенной выше формуле SUM я использовал ссылку для добавления как $ B $ 2: B2

• $ B $ 2 — это абсолютная ссылка, что означает, что когда я копирую ту же формулу в ячейки ниже, эта ссылка не изменится. Поэтому при копировании формулы в ячейку ниже формула изменится на SUM ($ B $ 2: B3)
• B2 — это вторая часть ссылки, которая является относительной, что означает, что это будет корректироваться, когда я копирую формулу вниз или вправо. Таким образом, при копировании формулы в ячейку ниже это значение станет B3.

Самое замечательное в этом методе состоит в том, что если вы удалите какую-либо из строк в наборе данных, эта формула изменится и по-прежнему даст вам правильные промежуточные итоги.

Далее в ячейке E2 пишется формула «=D2*G2» и получаем результат. Только вот если растянуть формулу, как мы это делали до этого, в других строках результата мы не увидим, т.к. Excel в строку 3 поставит формулу «D3*G3», в 4-ю строку: «D4*G4» и т.д. Надо же, чтобы G2 везде оставалась G2…

СУММЕСЛИ и СУММЕСЛИМН в Excel — ExcelGuide: Про Excel и не только

• В ячейке C2 введите = SUM (
• Выберите ячейку B1, которая является заголовком столбца со стоимостью продажи. Вы можете использовать мышь или клавиши со стрелками. Вы заметите, что Excel автоматически вводит структурированную ссылку для этой ячейки.
• Добавьте: (символ двоеточия)
• Выберите ячейку B2. Excel снова автоматически вставит структурированную ссылку для ячейки
• Закройте скобку и нажмите Enter.

Кроме того, в аргументе range и sum_range я заблокировал вторую часть ссылки, чтобы при спуске по ячейкам диапазон продолжал расширяться. Это позволяет нам учитывать и добавлять значения только до этого диапазона (отсюда и промежуточные итоги).

СУММЕСЛИМН в Эксель

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

Диапазон суммирования – это область ячеек с числами, которые будут суммироваться по заданным условиям
Диапазон условия 1 – это область ячеек с первым условием
Условие 1 – это критерий для проверки в диапазоне условия 1
Диапазонов условий и соответственно условий может быть несколько.

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

C2:C13 – диапазон ячеек с выручкой, которую мы хотим просуммировать
A2:A13 – область ячеек с данными по фамилиям менеджеров, которые осуществляли продажи
E2 – ячейка с критерием по определенному менеджеру, эффективность работы которого мы хотим узнать
B2:B13 – диапазон ячеек с городами, где осуществлялись продажи
F2 – ячейка с условием, в каком именно городе нас интересуют продажи

По итогам работы этой функции, мы получим размер выручки, которую принес нам менеджер Петров, работая в городе Москва. Если вы все сделали правильно, то результат должен получиться 1 775 рублей.

P.S. Хотел бы обратить ваше внимание, что функция СУММЕСЛИМН входит в 10 наиболее популярных функций для анализа данных в Excel. Про остальные функции вы можете прочитать в нашей статье на сайте «10 наиболее полезных функций при анализе данных в Excel».

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

5 простых способов подсчета промежуточной суммы в Excel (совокупная сумма)

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

Одновременное суммирование столбцов

В Excel можно одновременно просуммировать несколько смежных и несмежных столбцов.

Под каждым столбцом автоматически добавилась функция СУММ(). Теперь в ячейках A4; B4 и D4 отображается результат суммирования каждого столбца. Это самый быстрый и удобный метод.

Примечание. Данная функция автоматически подставляет формат ячеек которых она суммирует.

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

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

Как сделать в excel автосуммирование?

1. «=СУММЕСЛИМН(F2:F7;A2:A7;»Саша»)» — (прим.: обратите внимание на кавычки для условия — они должны быть как на скрине ниже, а не как у меня сейчас написано на блоге). Так же обратите внимание, что Excel при вбивании начала формулы (к примеру «СУММ…»), сам подсказывает и подставляет возможные варианты — а формул в Excel’e сотни!;
2. F2:F7 — это диапазон, по которому будут складываться (суммироваться) числа из ячеек;
3. A2:A7 — это столбик, по которому будет проверяться наше условие;
4. «Саша» — это условие, те строки, в которых в столбце A будет «Саша» будут сложены (обратите внимание на показательный скриншот ниже).

Как видим, данный способ довольно простой и интуитивно понятный, но у него имеется один существенный недостаток. На его осуществление нужно потратить много времени, если сравнивать с теми вариантами, которые мы рассмотрим ниже. А если в таблице очень много столбцов, то временные затраты увеличатся ещё больше.

Выборочное суммирование

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

Как в экселе посчитать сумму выборочных ячеек, решает функция «СУММ» раздела «Формулы» панели управлений.

Все операции выполняются наведением курсора на разделы панели управления и ячейки с данными, щелканьем («клик») левой кнопкой мыши, в следующей последовательности:

• любая пустая ячейка;
• раздел «ФОРМУЛЫ» панели управлений;
• раздел «Вставить функцию»;
• раздел «Категория», выбрать «Математические»;
• раздел «Выберите функцию» выбрать «СУММ», перейти к разделу «Аргументы функции»;
• ячейка таблицы с первым слагаемым;
• раздел «Аргументы функции», строка «Число 2»;
• ячейка таблицы со вторым слагаемым.

После того как все слагаемые введены, нажать «ОК» окна «Аргументы функции», получив окончательный результат.

Программа позволяет находить более сложные суммы данных, объединенных между собой условием (СУММ ЕСЛИ).

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

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

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

Допустим, нам нужно сложить все эти ячейки. Конечно, можно воспользоваться стандартным способом – щелкнуть в какую-нибудь ячейку, напечатать знак =, нажать на ячейку A1, затем напечатать знак +, нажать на A2, снова напечатать +, нажать А3 и кнопку Enter. В итоге формула будет выглядеть следующим образом: =A1+A2+A3

Суммирование произведений двух столбцов

1. Выделяем любую пустую ячейку на листе, куда предполагаем выводить итог вычисления. При желании можно её выбрать даже на другом листе книги. Но подобное бывает все-таки редко, так как в большинстве случаев более удобно традиционно располагать ячейку для вывода итогов в той же строке, в которой находятся расчетные данные. После того, как выделение произведено, щелкаем по значку «Вставить функцию» слева от строки формул.
2. Запускается инструмент, который носит наименование Мастер функций. Переходим в нем в категорию «Математические» и из открывшегося списка операторов выбираем название «СУММ». Затем кликаем по кнопке «OK» внизу окошка Мастера функций.
3. Производится активация окна аргументов оператора СУММ. В этом окне может располагаться до 255 полей, но для решения нашей задачи понадобится всего одно поле – «Число1». В него нужно ввести координаты той строки, значения в которой следует сложить. Для этого ставим курсор в указанное поле, а затем, произведя зажим левой кнопкой мыши, выделяем курсором весь числовой диапазон нужной нам строчки. Как видим, адрес данного диапазона тут же будет отображен в поле окна аргументов. Затем щелкаем по кнопке «OK».
4. После того, как мы произвели указанное действие, сумма значений строки тут же отобразится в той ячейке, которую мы выделили ещё на самом первом этапе решения задачи данным способом.

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

Использование формулы

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

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

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

Способ 3: функция СУММ

Довольно часто требуется в формулах (когда вы их копируете), чтобы какой-нибудь значение не менялось. Скажем простая задача: перевести цены в долларах в рубли. Стоимость рубля задается в одной ячейке, в моем примере ниже — это G2.