Объяснение функции SUMIF в Excel

Excel позволяет легко складывать значения в ваших анализах и отчетах. Стандартная функция SUM вполне подходит для большинства случаев. Но часто требуется сложить только определенные числа, а не суммировать все значения. Для этого в Excel предусмотрена функция SUMIF.

Функция SUMIF в Excel объединяет SUM с логической операцией. Функция суммирует значения только в том случае, если они соответствуют заданным критериям. Указанные критерии могут относиться к самому значению или к ячейке, которая ссылается на это значение. Например, можно суммировать только числа, которые меньше 1000. Или вы можете суммировать только значения в категории «Офисные принадлежности».

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

Синтаксис функции SUMIF не очень сложен.

Эта функция имеет три аргумента. Последний является необязательным:

• Диапазон: Укажите диапазон ячеек, к которым вы хотите применить критерии. Ячейки содержат числа или значения. Можно указать денежные значения и даты. Ячейки могут содержать имена, массивы или ссылки. Вы также можете указать значения, которые в конечном итоге нужно суммировать.
• Критерии: Вы можете определить различные типы критериев поиска. Вы можете указать числа, выражения, ссылки, тексты или другие функции. Если вы хотите использовать текст или математические формулы, вы должны заключить критерии в двойные кавычки. Вы можете использовать символы ? (соответствует любому одиночному символу) и * (соответствует любой последовательности символов).
• Sum_range: Если значения, которые вы хотите суммировать, еще не включены в первый аргумент, вы можете указать их здесь.

Если вы используете дополнительный аргумент Sum_range, он должен содержать такое же количество и распределение ячеек, как и аргумент Range. Каждой ячейке в первом аргументе присваивается одна ячейка в третьем аргументе. Если вы создадите несоответствие, например, укажете десять ячеек в Range, но только пять в Sum_range, Excel автоматически добавит еще пять ячеек в третий аргумент.

В дополнение к SUMIF можно использовать расширенную функцию SUMIFS. Эта функция доступна в Excel 2007 и выше и позволяет задавать несколько критериев. Принцип работы аналогичен SUMIF: Вы определяете диапазон, в котором нужно произвести поиск, критерии и диапазон ячеек, которые нужно просуммировать. Основное отличие заключается в том, что вы можете указать несколько критериев и выбрать больше диапазонов. Кроме того, порядок аргументов немного отличается:

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

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

В простейшей форме функции SUMIF используются логические операторы. Вы можете использовать стандартные операторы сравнения в Excel:

• меньше чем: <
• больше чем: >
• меньше или равно: <=
• больше или равно: >=
• равно: =
• не равно: <>

Формула складывает все значения в ячейках с A2 по A10, которые больше или равны 1000.

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

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

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

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

Еще одна полезная функция SUMIF заключается в том, что она может работать с датами. Предположим, вы хотите просуммировать только те вклады, которым более одного года. И снова мы начнем с несортированного списка. Вам нужно сравнить дату вклада с сегодняшней датой. Для этого используйте функции EDATE и TODAY.

Функция SUMIF определяет, какие ячейки в диапазоне от B2 до B16 содержат дату, которая меньше сегодняшней даты минус двенадцать месяцев. Функция суммирует только те ячейки в диапазоне от C2 до C16, к которым применимо это условие.

Предположим, вам нужна большая гибкость при суммировании значений. Вы также можете указать логические значения в качестве критериев для функции SUMIF. Если содержимое ячейки равно TRUE, то сумма в этой ячейке включается в сумму. Это может быть полезно, например, при использовании флажков. Флажки возвращают либо TRUE (если они установлены), либо FALSE (если они не установлены) в выбранную вами ячейку. Таким образом, ваши критерии поиска просто ссылаются на эти ячейки и ищут в них утверждение TRUE.

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

Часто требуется суммировать ячейки, отвечающие нескольким критериям. Это невозможно сделать с помощью одного только SUMIF, поэтому необходимо использовать функцию SUMIFS. Она позволяет искать на рабочем листе два или более критериев, которые должны быть удовлетворены.

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

Строка C содержит значения, которые вы хотите сложить. Вы выполняете поиск в строках A и B по критериям, которые вы определили в F2 и H2. Только если оба критерия верны, сумма будет включена в сумму.