Многие думают, что в excel сумма если и считается, то только сразу из всего указанного интервала, но это не так. Функция суммесли в excel позволяет проще и быстрее выполнять нужные вычисления. Она помогает выбирать только те числа, которые подходят под указанные требования.
На данном сайте вы найдете и другие статьи по работе с программой Excel.
Содержание
Суммесли и ее синтаксис
Суммесли – опция, с помощью которой можно суммировать только ячейки из промежутка, которые соответствуют заданному требованию или сразу нескольким. В последнем случае используется суммеслимн.
Первое, что стоит сделать, чтобы понять, как работает суммесли, — это разобраться с синтаксисом формулы суммесли в экселе. В стандартном виде она выглядит следующим образом:
=СУММЕСЛИ(А1:А10;«>1»).
Из формулы видно, что суммируются только слоты, значение которых больше 1 (или любой другой цифры, указанной в формуле). С помощью нее удобно подсчитать общее количество значений, которые равны, больше или меньше указанного.
Если разобрать формулу подробнее, то она будет выглядеть так:
=СУММЕСЛИ(Диапазон ячеек (ГДЕ ИЩЕМ); Условие (ЧТО ИЩЕМ); [диапазон_суммирования] (ЧТО СУММИРУЕМ)).
Для более простого и понятного пользования в эксель суммесли, можно вызвать окно «Аргументы функции». В нем сумма если формула представлена через указание диапазона и критерия выбора ячеек. Поэтому будет проще понять, как использовать суммесли.
Значение диапазона необходимо указать, если требуется найти значения больше или меньше указанно, при этом добавив в сумму сведения из другого столбца. Подробнее в этом разберемся далее.
Аргументы включают в себя диапазон, условие и диапазон суммирования. Первые два считаются обязательными, третий нет.
Диапазон содержит ту информацию, которая будет оцениваться с точки зрения соответствия ограничениям. Они должны содержать числа, имена, массивы или ссылки на числа. Пустые и текстовые значения не учитываются. Для использования дат необходимо представить их в допустимом формате Excel.
Условие определяет, какие значения будут суммироваться. Оно представляется в виде числа, выражения, ссылки, текста или функции. Может содержать следующие символы:
? – для сопоставления с любым единичным символом;
* — для соответствия с последовательностью из символов.
Совет: если необходимо найти именно символы ? и *, то перед ними ставится символ «тильда» (~).
Важно! Формулировки с логическим и математическими знаками и в виде текста необходимо выделять с двух сторон кавычками («»). Числа не нуждаются в выделении.
Диапазон суммирования – непосредственно те ячейки, которые будут суммироваться после проверки на соответствие критериям.
Как работает функция суммесли в excel
Важная особенность формулы состоит в том, что она заменяет собой несколько сложных и однотипных вычислений.
Ее функционал заключается в следующем:
- Уточнение значения ячеек. По ним будут выполняться арифметические действия.
- Условие. Оно обозначается в кавычках и содержит критерий, по которому отбираются сведения для дальнейших операций.
- Суммирование.
Для того, чтобы использовать формулу, достаточно поставить курсор в свободный слот и перейти в раздел «Формулы» в верхней части окна.
Далее необходимо найти раздел с математическими формулами и среди них выбрать суммесли.
В открывшемся окне потребуется указать диапазон и критерий. После заполнения полей останется только нажать на кнопку «Готово» или «ОК».
Формулу сумма если в эксель предлагает использовать в двух вариантах: полном и сокращенном.
Первый вариант применяется, когда есть необходимость суммировать только те данные, которые подходят под ограничения.
Разберем сумма если в excel примеры, сокращенный вариант формулы.
Необходимо найти общую сумму заказов, которые превышают 30000 рублей.
Разберем аргументы, которые будут использованы для решения.
- Диапазон ячеек. Он ограничивается столбцом «Стоимость заказа».
- Условие суммирования. Суммируются только те значения, которые равны или больше 30000 рублей.
В данном случае будет достаточно 2 аргументов. Нет необходимости использовать 3.
Важно! Знак «больше или равно» является текстом. Поэтому вся формулировка заключается в кавычки.
Формула возвращает результат суммирования одним числом в указанное место на листе. Общая сумма заказов, превышающих 30000, равна 112600 рублей.
Перейдем к рассмотрению полного варианта суммесли excel. Для этого найдем общую сумму заказов от одного покупателя (Пятерочка).
В качестве аргументов в этом случае будут задействованы столбцы «Покупатель» и «Стоимость заказа».
Первый аргумент – диапазон ячеек, которые проходят проверку на соответствие критерию. В данном случае выберем столбец «Покупатель». Потребуется найти все ячейки, содержащие название «Пятерочка».
Важно! Так как это текстовое значение, то его необходимо заключить в кавычки.
Следующий аргумент – столбец «Стоимость заказа». Его значения будут суммироваться.
Разберем подробнее, как в данном случае будет осуществляться работа функции. Первым этапом из первого столбца будут отобраны те слоты, которые содержат слово «Пятерочка». При его обнаружении анализ перемещается в столбец «Стоимость заказа» и выберет в нем соответствующее значение.
Затем перемещается обратно в столбец «Покупатель» и ищет там нужное значение. Таким образом ее работа продолжается до конца. Результат – сумма только тех значений, которые соответствуют критерию, обозначенному во втором аргументе.
Из таблицы видно, что общая сумма заказов магазина «Пятерочка» равна 112600 рублей.
Совет! Суммесли – функция, аргумент которой можно задать не только символами, но и ссылкой, содержащую нужное значение. В данном примере, можно вставить в формулу значение слота, который содержит текст «Пятерочка».
Функция суммесли в excel с несколькими условиями
Для отбора по нескольким критериям используется функция суммеслимн в exсel. Критерии при этом записываются в качестве аргументов. В итоге возвращается их сумма. Главное отличие, которое содержит формула суммеслимн, — возможность указания до 127 критериев отбора данных для суммирования. Она удобна при работе с большими массивами данных.
Синтаксис с использованием функции по нескольким критериям
Для обозначения функции с несколькими положениями к формуле СУММЕСЛИ добавляются 2 буквы – МН (СУММЕСЛИМН). Суммеслимн на английском excel SUMIFS. Выбор суммесли или суммеслимн зависит от количества условий. Возникает вопрос, сколько условий можно прописать в формулу суммесли? Ответ, только одно. Если много критериев для отбора ячеек, то используется вторая функция.
Минимальное количество аргументов, которое содержит функция суммеслимн, — 5.
- Диапазон суммирования. Он обозначается в начале формулы и содержит ячейки, подлежащие суммированию.
- Диапазон условия 1 – ячейки, которые проверяются на соответствие первому критерию.
- Условие 1 – определяет те ячейки, которые будут выделены функцией из 1 диапазона.
- Диапазон условия 2 – ячейки, проверяемые с точки зрения соответствия 2 критерию.
- Условие 2 – определяет ячейки, выделяемые функцией из 2 диапазона.
Далее пункты повторяются до того числа, которое будет соответствовать количеству критериев.
Пример использования
Суммеслимн – частный пример суммесли, который используется для анализа многих критериев. Поэтому алгоритм работы будет схож. В пустую ячейку поставить курсор. Далее в верхнем меню выбрать раздел «Формулы». Найти в «Библиотеке функций» отдельно «Математические» и в выпавшем списке выбрать суммеслимн формула. Далее потребуется заполнить поля с диапазонами и условиями. В конце нажать на кнопку «Готово».
Функция суммеслимн в excel примеры с несколькими условиями.
Разберем, суммеслимн как работает, есть следующая задача. Необходимо найти общую сумму заказов «Пятерочки», которые больше 30000 рублей.
1 аргумент – диапазон суммирования. В данном случае это будет столбец «Стоимость заказа».
2 аргумент – диапазон условия 1. Он совпадает со столбцом «Покупатель».
3 аргумент – условие 1. Значение ячейки должно совпадать с названием «Пятерочка».
4 аргумент – диапазон условия 2. Он соответствует столбцу «Сумма заказа».
5 аргумент – условие 2. В ячейках должны быть значения, которые равны или превышают 30000 рублей.
Функция автоматически подставляет в указанную область суммирование по условию в excel заказов от магазина «Пятерочка», которые равны или превышают 30000 рублей. Она равна 112600 рублей.
При использовании функции суммесли и суммеслимн важно правильно выбрать диапазон. Если он выбран неправильно, то результат, суммирование ячеек в excel по условию будут неверными. Важно, чтобы промежуток суммирование по нескольким условиям в excel были равными.
Если по ошибке в формуле указать не ячейку, а нажать на табулятор столбца, то вычисления не сработают. В формулу будут подставлены абсолютно все значения выбранного столбца. Даже в том случае, если ниже данных нет, вычисления будут некорректными. Это объясняется тем, что заголовок столбца стал частью данных и включен в вычисления. Из-за этого весь результирующий диапазон смещается вверх на одну ступеньку.
Как итог, программа выдаст ошибку без дополнительных комментариев и примечаний. Найти ее причину будет крайне сложно.
Суммесли в excel с динамическим условием
Особенность функций суммесли и суммеслимн – их автоматическое подстраивание под изменение условий. Можно изменить данные в ячейках. Сумма с условием в excel после этого изменится автоматически.
Вернемся к примеру с магазинами и суммой их покупок. Допустим, что в таблице не хватает еще одного города, в котором происходили закупки, его необходимо добавить в таблицу, чтобы данные были достоверными. Для этого достаточно нажать правой кнопкой мыши и в выпавшем списке выбрать «Вставить». После этого появляется новая строка, в которую заносятся нужные данные.
Важно! После добавления новой строки промежуток суммирования автоматически расширяется.
Excel функция суммесли сразу же реагирует на появление новых данных. Общая excel сумма по условию изменится автоматически.
После добавления еще одной строки, в которой сумма заказа от магазина «Пятерочка» составила 19200 рублей, изменились некоторые результаты. Так, общая сумма заказов от магазина «Пятерочка» стала 125800 рублей. Ячейки, в которых было задано требование, касающееся суммы заказа (>=30000 рублей) остались без изменений.
При необходимости можно не только добавить, но и убрать данные из таблицы. Достаточно просто удалить лишнюю строку. Если нужно только изменить данные, а не удалить их полностью, то достаточно просто поменять значение, например, изменить название магазина или сумму закупки.
