СУММЕСЛИ очень удобная функция в случае определения данных на основании какого-либо условия. Если есть массив данных, в котором повторяются значения, которые необходимо просуммировать, то это отличный случай воспользоваться этой формулой.
Конечно, у данной формулы есть ограничение - искать можно только по одному критерию. Однако можно при помощи небольшой хитрости победить и это ограничение, об этом поговорим в разделе полезных советов.
Что может делать функция CУММЕСЛИ в Excel
Функция CУММЕСЛИ находит в таблице значения, которые соответствуют заданному условию и складывает их. При этом столбцы с данными могут быть расположены в любом порядке на листе Excel. Условия, по которым формула проводит поиск, могут быть разнообразными: по датам, по текстовым названиям и т.д..
Как заполнять аргументы формулы СУММЕСЛИ
Для того, чтобы открыть формулу СУММЕСЛИ в книге Excel
переходим в ячейку, в которой необходимо отразить результат, и нажимаем равно =
рядом со значком Функции, в строке формул пишем =суммесли(, затем нажимаем на
значок Функция для вызова формулы.
Функция СУММЕСЛИ для заполнения выглядит следующим образом:
Аргументы функции СУММЕСЛИ заполним на примере таблицы в Excel.
У нас есть таблица с итоговыми показателями, а также табличка со значениями.
Скачать файл с примером можно здесь.
Задание: найти сумму показателей из Таблички в сводную таблицу. Для этого переходим в ячейку В4 – Картошка Итого. Далее вызываем функцию СУММЕСЛИ, как описывалось выше.
Описание к картинке по заполнению аргументов функции:
Диапазон - это столбец в котором заданы условия отбора
Критерий - показатель, по которому должна быть посчитана сумма
Диапазон_суммирования - столбец, в котором находятся значения, которые необходимо сложить.
В результате получим:
Ошибки при использовании функции СУММЕСЛИ в Excel
При использовании функции СУММЕСЛИ возникает ряд ситуаций, которые могут привести к ошибкам в расчетам. Рассмотрим, наиболее частые ситуации.
Ошибка при использовании разных диапазонов в поиске и суммировании
Ошибка разных диапазонов может возникать в случае, когда указан разный диапазон столбцов, в которых происходит поиск значений - Диапазон, и суммирование - Диапазон_суммирования. Данныя ошибка ведет к тому, что будет посчитан неверный результат.
Для исправления ошибки необходимо указать одинаковые диапазоны столбцов, то есть первые и последние номера строк таблицы, в которой происходит суммирование, должны быть одинаковые.
Поправить диапазоны можно прямо из ячейки с формулой: необходимо потянуть за уголок диапазона, который указан неверно.
Данные для поиска и суммирования находятся с другой книге/ файле Excel
Если книга, на которую ссылается формула СУММЕСЛИ, закрыта,
то появится ошибка #ЗНАЧ!. В этом случае, нужно просто открыть файл с данными,
связи между книгами обновятся, и ошибка заменится данными.
Грамматические ошибки в названиях критериев поиска
Также работа формулы зависит от того, что в названиях условия
поиска и показателях в таблице нет орфографических ошибок. В этом случае слова,
например, «солома» и «салома» – будут считаться разными словами и суммирование
по ним происходить не будет.
Полезные советы при использовании функции СУММЕСЛИ в Excel
Автозаполнение таблицы Excel с итоговыми данными
Для того, чтобы заполнить таблицу Excel искомыми значениями,
просто протянув формулу вниз, сначала необходимо закрепить значком $ диапазон
массива, в которой ищем данные. В противном случае, диапазон будет смещаться
вниз и формула будет искать данные в неверном диапазоне.
Копирование формулы с функцией СУММЕСЛИ
Иногда бывает необходимость сделать несколько разных таблиц, которые будут осуществлять поиск по похожим условиям. И чтобы не писать формулу СУММЕСЛИ заново, можно ее просто скопировать из одной ячейки и вставить в другую.
Для этого необходимо зайти в ячейку с формулой, выделить ее, нажать правую кнопку мыши и выбрать «Копировать». Затем необходимо встать в ячейку, куда нужно скопировать и вставить формулу. Этот способ позволит сохранить ссылки на колонки и исходный массив в том же виде, как и в исходной таблице.
Возможность задавать различные критерии для суммирования
Для того, чтобы задавать различные условия поиска, кроме самого простого, когда мы просто ссылаемся на ячейку, существуют определенные правила, позволяющие делать поиск по другим критериям:
"*" - замена любого количества символов в
аргументе «Критерий»
В итоге получается сумма значений по словам «КАРТИНА» и «КАРТОШКА»
"" - сумма пустых ячеек в аргументе «Критерий»
Использование конструкции СУММЕСЛИ + СУММЕСЛИ
Если есть необходимость сложить несколько условий из
разных таблиц, не связанных между собой, то можно записать поиск используя
конструкцию СУММЕСЛИ + / - СУММЕСЛИ
Для этого в одной ячейке мы пишем конструкцию из двух формул. Пример, когда формулы ссылаются на разные таблицы и условия:
Пример, когда формулы ссылаются на одну таблицу, но с разными условиями
Функция СУММЕСЛИ в Excel – очень удобный инструмент для суммирования данных на основе определенного условия. Структура написания формулы довольна проста, для ее использования подходят таблицы в любом виде, нет необходимости их преобразовывать в подходящий для расчета вид. Все эти факторы делают эту функцию очень популярной и часто используемой.
Оставить комментарий