Меню

Таблицы эксель сумма с условием



Суммирование значений с учетом нескольких условий

Предположим, что вам нужно свести значения с более чем одним условием, например суммой продаж продуктов в определенном регионе. Это хороший случай для использования функции СУММЕСС в формуле.

Взгляните на этот пример, в котором есть два условия: мы хотим получить сумму продаж «Мясо» (из столбца C) в регионе «Южный» (из столбца A).

Данные на листе Excel

Вот формула, с помощью которая можно сопровождать эту формулу:

Результат — значение 14 719.

Рассмотрим каждую часть формулы более подробно.

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

Другими словами, вы хотите, чтобы формула суммировала числа в этом столбце, если они соответствуют определенным условиям. Это диапазон ячеок является первым аргументом в этой формуле — первым элементом данных, который требуется функции в качестве входных данных.

Затем вам нужно найти данные, отвечающие двум условиям, поэтому введите первое условие, указав для функции расположение данных (A2:A11) и условие («Южный»). Обратите внимание на запятую между аргументами:

Кавычка вокруг текста «Южный» указывает на то, что это текстовые данные.

Наконец, вы вводите аргументы для второго условия — диапазон ячеек (C2:C11), которые содержат слово «Мясо», а также само слово (заключенное в кавычки), чтобы приложение Excel смогло их сопоставить. В конце формулы введите закрываю скобки) и нажмите ввод. Результат — 14 719.

Если вы ввели в Excel функцию СУММЕСС, если вы не помните аргументов, справка готова. После того как вы введете =СУММЕСС(, под формулой появится автозавершенная формула со списком аргументов в правильном порядке.

На изображении автозавершена формулы и списке аргументов в нашем примере sum_range — D2:D11, столбец чисел, которые нужно свести; criteria_range1 — A2. A11 — столбец данных, в котором находится «Южный» (критерий1).

Использование автозаполнения формул при вводе функции СУММЕСЛИМН

По мере того, как вы вводите формулу, в автозавершении формулы появятся остальные аргументы (здесь они не показаны); диапазон_условия2 — это диапазон C2:C11, представляющий собой столбец с данными, в котором находится условие2 — “Мясо”.

Если вы нажмете кнопку СУММЕСС в автозавершении формул, откроется статья с дополнительной справкой.

Попробуйте попрактиковаться

Если вы хотите поэкспериментировать с функцией СУММЕСС, вот примеры данных и формула, в которую она используется.

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

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

Источник

7 примеров использования формулы СУММЕСЛИ в Excel с несколькими условиями

В таблицах Excel можно не просто находить сумму чисел, но и делать это в зависимости от заранее определённых критериев отбора. Мы рассмотрим, как правильно применить функцию СУММЕСЛИ (Sumif) в таблицах Excel. Начнем с самых простых случаев, как можно использовать при этом знаки подстановки, назначить диапазон суммирования, работать с числами, текстом и датами. Особо остановимся на том, как использовать сразу несколько условий. И, конечно, мы применим новые знания на практике, рассмотрев несложные примеры.

  1. Как пользоваться СУММЕСЛИ в Excel – синтаксис
  2. Примеры использования функции СУММЕСЛИ в Excel
    • Сумма если больше чем, меньше, или равно
    • Критерии для текста.
    • Подстановочные знаки для частичного совпадения.
    • Точная дата либо диапазон дат.
    • Сумма значений, соответствующих пустым либо непустым ячейкам
    • Сумма по нескольким условиям.
    • Почему СУММЕСЛИ у меня не работает?

Хорошо, что функция СУММЕСЛИ одинакова во всех версиях MS Excel, с 2016 по 2003 год. Еще одна приятная новость: если вы потратите некоторое время на ее изучение, вам потребуется совсем немного усилий, чтобы понять другие «ЕСЛИ»-функции, такие как СУММЕСЛИМН, СЧЕТЕСЛИ, СЧЕТЕСЛИМН и т.д.

Как пользоваться СУММЕСЛИ в Excel – синтаксис

Её назначение – найти итог значений, которые удовлетворяют определённым требованиям.

Синтаксис функции выглядит следующим образом:

=СУММЕСЛИ(диапазон, критерий, [диапазон_суммирования])

Диапазон – это область, которую мы исследуем на соответствие определённому значению.

Критерий – это значение или шаблон, по которому мы производим отбор чисел для суммирования.

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

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

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

Итак, если он указан, то расчет идет именно по его данным. Если отсутствует, то складываются значения из той же области, где производился поиск.

Чтобы лучше понять это описание, рассмотрим несколько простых задач. Надеюсь, что они будут понятны не только «продвинутым» пользователям, но и подойдут для «чайников».

Примеры использования функции СУММЕСЛИ в Excel

Сумма если больше чем, меньше, или равно

Начнем с самого простого. Предположим, у нас есть данные о продажах шоколада. Рассчитаем различные варианты продаж.

D2:D21 – это координаты, в которых мы ищем значение.

I2 – ссылка на критерий отбора. Иначе говоря, мы ищем ячейки со значением 144 и складываем их.

Читайте также:  Как повернуть таблицу значений

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

Кроме того, в качестве задания для отбора нужных значений можно указать текстовое выражение, состоящее из знаков >, , = и числа.

Можно указать его прямо в формуле, как это сделано в I13

В I3 запишем выражение:

F2:F21 – это область, в которой мы отбираем подходящие значения.

I2 – здесь записано, что именно отбираем.

E2:E21 – складываем числа, соответствующие найденным совпадениям.

Конечно, можно указать параметр отбора прямо в выражении:

Но мы уже договорились, что так делать не совсем рационально.

Важное замечание. Не забываем, что все текстовые значения необходимо заключать в кавычки.

Подстановочные знаки для частичного совпадения.

При работе с текстовыми данными часто приходится производить поиск по какой-то части слова или фразы.

Вернемся к нашему случаю. Определим, сколько всего было заказов на черный шоколад. В результате, у нас есть 2 подходящих наименования товара. Как учесть их оба? Для этого есть понятие неточного соответствия.

Мы можем производить поиск и подсчет значений, указывая не всё содержимое ячейки, а только её часть. Таким образом мы можем расширить границы поиска, применив знаки подстановки “?”, “*”.

Символ “?” позволяет заменить собой один любой символ.

Символ ”*” позволяет заменить собой не один, а любое количество символов (в том числе ноль).

Эти знаки можно применить в нашем случае двумя способами. Либо прямо вписать их в таблицу –

=СУММЕСЛИ(C2:C21;I2;E2:Е21) , где в E2 записано *[слово]*

где * вставлены прямо в выражение и «склеены» с нужным текстом.

  • “*черный*” — мы ищем фразу, в которой встречается это выражение, а до него и после него – любые буквы, знаки и числа. В нашем случае этому соответствуют “Черный шоколад” и “Супер Черный шоколад”.
  • “Д?” — необходимо слово из 2 букв, первая из которых “Д”, а вторая – любая. В нашем случае подойдет “Да”.
  • “. ” — найдем слово из любых 3 букв

Этому требованию соответствует “Нет”.

  • “. *” — текст из любых 7 и более букв.

Подойдет “Зеленый”, “Оранжевый”, “Серебряный”, “Голубой”, “Коричневый”, “Золотой”, “Розовый”.

  • “З*” — мы выбираем фразу, первая буква которой “З”, а далее – любые буквы, знаки и числа. Это “Золотой” и “Зеленый”.
  • “Черный*” — подходит фраза, которая начинаются именно с этого слова, а далее – любые буквы, знаки и числа. Подходит “Черный шоколад”.

Примечание. Если вам необходимо в качестве задания для поиска применять текст, который содержит в себе * и ?, то используйте знак тильда (

), поставив его перед этими символами. Тогда * и ? будут считаться обычными символами, а не шаблоном:

Важное замечание. Если в вашем тексте для поиска встречается несколько знаков * и ?, то тильду (

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

А если текст просто содержит в себе 3 звездочки, то можно наше выражение переписать так:

Точная дата либо диапазон дат.

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

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

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

Рассчитываем итог продаж за сегодняшний день – 04.02.2020г.

Рассчитаем за вчерашний день.

СЕГОДНЯ()-1 как раз и будет «вчера».

Складываем за даты, которые предшествовали 1 февраля.

А если нас интересует временной интервал «от-до»?

Мы можем рассчитать итоги за определённый период времени. Для этого применим маленькую хитрость: разность функций СУММЕСЛИ. Предположим, нам нужна выручка с 1 по 4 февраля включительно. Из продаж после 1 февраля вычитаем все, что реализовано до 4 февраля.

Если критерий указать просто “*”, то мы учитываем для подсчета непустые ячейки, в которых имеется хотя бы одна буква или символ (кроме пустых).

Точно такой же результат даёт использование вместо звездочки пары знаков «больше» и «меньше» — <>.

Теперь рассмотрим, как можно находить сумму, соответствующую пустым ячейкам.

Для того, чтобы найти пустые, в которых нет ни букв, ни цифр, в качестве критерия поставьте парные одинарные кавычки ‘’, если значение критерия указано в ячейке, а формула ссылается на неё.

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

Сумма по нескольким условиям.

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

Вновь вернемся к нашему случаю с заказами. Рассмотрим два условия и найдем, сколько всего сделано заказов черного и молочного шоколада.

1. СУММЕСЛИ + СУММЕСЛИ

Находим сумму заказов по каждому виду товара, а затем просто их складываем. Думаю, с этим вы уже научились работать :).

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

2. СУММ и СУММЕСЛИ с аргументами массива.

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

Читайте также:  Отличие фирмы от предприятия таблица

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

Если вы хотите найти покупки этих двух товаров, то ваши критерии в виде массива будут выглядеть так:

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

А теперь воспользуемся функцией СУММ, которая умеет работать с массивами данных, складывая их содержимое.

Важно, что результаты вычислений в первом и втором случае совпадают.

3. СУММПРОИЗВ и СУММЕСЛИ.

А если вы предпочитаете перечислять критерии в какой-то специально отведенной для этого части таблицы? Можете использовать СУММЕСЛИ в сочетании с функцией СУММПРОИЗВ, которая умножает компоненты в заданных массивах и возвращает сумму этих произведений.

Вот как это будет выглядеть:

в H3 и H4 мы запишем критерии отбора.

Но, конечно, ничто не мешает вам перечислить значения в виде массива критериев:

Результат, возвращаемый в обоих случаях, будет идентичен тому, что вы наблюдаете на скриншоте.

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

Почему СУММЕСЛИ у меня не работает?

Этому может быть несколько причин. Иногда ваше выражение не возвращает того, что вы ожидаете, только потому, что тип данных в ячейке или в каком-либо аргументе не подходит для нее. Итак, вот что нужно проверить.

1. «Диапазон данных» и «диапазон суммирования» должны быть указаны ссылками, а не в виде массива.

Первый и третий атрибуты функции всегда должны быть ссылкой на область таблицы, например A1: A10. Если вы попытаетесь передать что-нибудь еще, например, массив <1,2,3>, Excel выдаст сообщение об ошибке.

Правильно: =СУММЕСЛИ(A1:A3, «цвет», C1:C3)

Неверно : =СУММЕСЛИ(<1,2,3>, «цвет», C1:C3)

2. Ошибка при суммировании значений из других листов или рабочих книг

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

Найдем сумму значений в F2: F9 на листе 1 книги 1, если соответствующие данные записаны в столбце A, и если среди них содержатся «яблоки»:

Однако это перестанет работать, как только Книга1 будет закрыта. Это происходит потому, что области, на которые ссылаются формулы в закрытых книгах, преобразуются в массивы и хранятся в таком виде в текущей книге. А поскольку в аргументах 1 и 3 массивы не допускаются, то формула выдает ошибку #ЗНАЧ!.

3. Чтобы избежать проблем, убедитесь, что диапазоны данных и поиска имеют одинаковый размер.

Как отмечалось в начале этого руководства, в современных версиях Microsoft Excel они не обязательно должны иметь одинаковый размер. Но вот в Excel 2000 и более ранних версиях это может вызвать проблемы. Однако, даже в самых последних версиях Excel 2010 и Excel 2016 сложные выражения, в которых диапазон сложения имеет меньше строк и/или столбцов, чем диапазон поиска, являются капризными. Вот почему рекомендуется всегда иметь их одинакового размера и формы.

Источник

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

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

  1. 1 Простое сложение в Excel
  2. 2 Суммирование ячеек по условию
  3. 3 Функция «СУММЕСЛИ»
  4. 4 Функция «СУММЕСЛИМН»
  5. 5 Итог

Простое сложение в Excel

Складывать значения ячеек можно с помощью простой функции «СУММ», которая суммирует выбранные значения без определенного условия. Она используется, когда есть ячейки, массив или несколько массивов для сложения. В этом случае достаточно выделить нужные элементы электронной таблицы и применить функцию. Она играет роль обычного калькулятора со знаком «+».

Функция СУММ в Excel

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

Excel также предусматривает суммирование с учетом условия. Функция применяется, когда необходимо просуммировать только те значения, которые соответствуют заданному условию. Для нее возможно задать три аргумента:

  • Диапазон – обязательный аргумент, представляющий собой массив, в котором происходит проверка заданного условия;
  • Критерий – еще один обязательный аргумент, которое является условием для отбора значений в ячейках. При равенстве определенному числу, необходимо ввести его без кавычек, в других случаях необходимы кавычки: например, если значение больше числа 5, то его нужно прописать, как «>5» . Также работают текстовые значения: если нужно суммировать выручку продавца Иванова в таблице, то прописывается условие «Иванов» ;
  • Диапазон суммирования – массив значений, которые нужно сложить.

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

Функция «СУММЕСЛИ»

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

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

Выбор ячейки для вывода результата СУММЕСЛИ

Выбор функции СУММЕСЛИ

Аргументы функции СУММЕСЛИ

Критерий функции СУММЕСЛИ

Диапазон суммирования СУММЕСЛИ

Результат функции СУММЕСЛИ

В выделенной ячейке отобразится результат сложения, а в верхней строке – функция. Вычислить значение можно без вызова окна «Аргументы функции», если прописать все вручную. При этом базовая запись должна выглядеть так: =СУММЕСЛИ(X; «Условие»; Y) , где X – это диапазон, а Y – диапазон суммирования. Значения не обязательно вписывать, их достаточно выделить с помощью мышки в таблице. Все элементы функции разделены знаком «;».

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

Функция «СУММЕСЛИМН»

«СУММЕСЛИМН» позволяет рассчитать результат суммирования с использованием нескольких условий. Функция предоставляет больше возможностей для задания параметров математического вычисления. Для расчета можно использовать сразу несколько критериев суммирования, причем условий может быть задано до 127. На примере данной таблицы рассмотрим, как найти, сколько килограмм яблок купил Евдокимов, ведь он приобретал также и бананы.

Читайте также:  Таблица длин отводов трубопроводов

Чтобы суммировать ячейки с несколькими условиями, действуйте согласно следующей инструкции:

  1. Выделите пустую ячейку, в которой будет отображаться конечный результат, затем нажмите на кнопку fx, которая находится рядом со строкой функций.
  2. В разделе «Математические» в окне «Вставка функций» нажмите «СУММЕСЛИМН», затем подтвердите выбор, нажав на кнопку «ОК».

Вставка функции СУММЕСЛИМН в ячейку

Диапазон суммирования СУММЕСЛИМН

Диапазон первого условия СУММЕСЛИМН

Первое условие СУММЕСЛИМН

Диапазон и второе условие СУММЕСЛИМН

Функцию «СУММЕСЛИМН» возможно прописать вручную в строке формул, но это сложно, поскольку используется слишком много условий. В данной таблице результат равен 8, а вверху отображается функция полностью.

СУММЕСЛИМН в строке формул

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

Источник

Функции Excel: СУММЕСЛИ (суммирование по одному условию)

Функция СУММЕСЛИ используется, когда вам необходимо просуммировать значения диапазона, соответствующие одному заданному условию. Например, необходимо просуммировать заказы за период по менеджеру (поставщику, покупателю, дате) или значения больше 10 000.

= СУММЕСЛИ(Диапазон ячеек ( ГДЕ ИЩЕМ); Условие ( ЧТО ИЩЕМ); [диапазон_суммирования] ( ЧТО СУММИРУЕМ))

Аргумент_1 Диапазон ячеек, оцениваемых на соответствие условиям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые и текстовые значения игнорируются. Выбранный диапазон может содержать даты в стандартном формате Excel.

Аргумент_2 Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Подстановочные знаки можно включать в вопросительный знак ( ?), чтобы они соответствовали любому символу, звездочку ( *) в соответствии с любой последовательностью знаков. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак «тильда» (

Условие может быть выражено в виде 42, «>66 000», B5, «7?», «Iphone *», «*

Аргумент_3 Диапазон_суммирования (не обязательный) ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона.
Если аргумент опущен, Excel суммирует ячейки, указанные в Аргументе_1 (те же ячейки, к которым применяется условие).
Диапазон Аргумента_3 должен быть одного размера , что и Аргумента_1.

Функцию ВПР можно вызвать, через:

Мастер формул, раздел Математические;
Вкладку Формулы ► Математические;
Введя в строку =СУММЕСЛИ(.

Источник

Как в Экселе посчитать сумму определенных ячеек

Иногда даже такие расчеты могут вызвать определенные затруднения у начинающих пользователей пакета MS Office с программами Word и Excel. Например, далеко не все знают даже то, как в Экселе посчитать сумму выделенных ячеек. Не говоря уже о действиях, необходимых для сложения чисел в одинаковых диапазонах на разных страницах. На самом деле складывать числа в табличном процессоре очень легко, и применяются для этого всего 3 функции и один математический знак.

Простое сложение

Самый простой способ, как в Экселе посчитать сумму определенных ячеек – использование знака «плюс». Он подходит при необходимости сложить небольшое количество чисел или суммировать диапазоны, расположенные в произвольном порядке на одном или нескольких листах.

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

Не обойтись без такого способа и в тех случаях, когда ячейки расположены в произвольном порядке друг относительно друга. Например, пользователь точно знает (определив это способом, который невозможно заменить формулой), что сложить нужно именно ячейки G8, H16, J7 и K18. Для получения суммы достаточно просто написать в ячейке сначала знак равенства, а затем номера ячеек, между которыми ставится плюс.

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

Применение функции СУММ

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

Функцию можно применять, складывая отдельные значения или целые диапазоны. Причем от пользователя не требуется ставить какие-то знаки – даже точка с запятой и двоеточие устанавливаются автоматически. В одной формуле можно указать до 255 аргументов. Каждый из них может быть положительным, отрицательным, дробным. Округление происходит в соответствии с настройками ячейки, в которой записывается формула.

Источник

Adblock
detector