Cоветы и управления по Гугл Таблицам

Как применять функцию СУММЕСЛИ в Гугл Таблицах? Примеры

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

В Гугл Таблицах есть комфортная функция СУММЕСЛИ, помогающая вычислять условные суммы.

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

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

Что делает функция СУММЕСЛИ?

Функция СУММЕСЛИ — это просто объединение функций СУММ и ЕСЛИ. Его применение обычное.

Функция просто просматривает спектр ячеек, чтоб отыскать те, которые соответствуют данному условию.

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

Как функция окончит сканирование всех ячеек в спектре, она берет группу избранных чисел и суммирует их!

Синтаксис функции СУММЕСЛИ в Гугл Таблицах

Синтаксис функции СУММЕСЛИ:

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

Как видно из синтаксиса выше, есть два метода использования функции СУММЕСЛИ.

• без отдельного спектра сумм
• с отдельным спектром сумм.

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

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

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

В конце концов, он возвращает сумму всех ячеек в спектре, соответственных аспектам.

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

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

К примеру, вы сможете применять его для:

• Найдите сумму лишь положительных либо отрицательных чисел в спектре
• Найдите общий размер продаж для отдела
• Найдите общие расходы до данной даты

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

Но в данной статье давайте просто разглядим эти три варианта использования. При разъяснении подстановочных символов мы включим очередной особенный вариант использования.

Внедрение СУММЕСЛИ с числовым условием

1-ый вариант использования — отыскать сумму лишь положительных либо отрицательных чисел в спектре.

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

Мы желаем сложить лишь положительные числа в спектре A2: A10. Ах так можно применить функцию СУММЕСЛИ в этом случае:

• Изберите ячейку, в которой вы желаете показать итог суммы (в нашем случае C2).
• Введите в ячейку последующую формулу: = СУММЕСЛИ (A2: A10, ”> = 0”) Направьте внимание, что в этом случае мы не включили 3-ий параметр.
• Нажмите кнопку возврата

Это обязано показать сумму положительных чисел в ячейке C2.

Разъяснение формулы

В этом примере функция СУММЕСЛИ проверила каждую ячейку от A2 до A10 и избрала лишь те ячейки, которые имеют значения больше либо равные 0.

Потом он сложил все избранные значения и показал итог в ячейке C2.

Примечание. Если заместо этого вы желаете показать сумму всех отрицательных чисел, все, что для вас необходимо создать, это поменять условие на «<0».

Внедрение СУММЕСЛИ с условием текста

Давайте поглядим на иной пример.

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

Ниже показано, как это можно создать с помощью формулы СУММЕСЛИ:

• Изберите ячейку, в которой вы желаете показать итог суммы (в нашем случае D2).
• Введите в ячейку последующую формулу: = СУММЕСЛИ (A2: A10, «Упаковка», B2: B10)
• Нажмите кнопку возврата

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

Разъяснение формулы

В этом примере функция СУММЕСЛИ инспектирует каждую ячейку от A2 до A10 и отыскивает лишь те ячейки, которые содержат значение «Packaging».

Для каждой ячейки, содержащей слово «Упаковка», функция СУММЕСЛИ выбирала соответственное значение продаж в столбце B. Потом она складывала все избранные значения и показывала итог в ячейке D2.

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

Внедрение СУММЕСЛИ с условием даты

Сейчас давайте поглядим на пример, в котором мы желаем отыскать общие расходы до данной даты, скажем, 1 сентября 2019 года.

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

• Изберите ячейку, в которой вы желаете показать итог общих расходов (в нашем случае D2).
• Введите в ячейку последующую формулу: = СУММЕСЛИ (A2: A10, ”<” & ДАТА (2019,9,1), B2: B10).
• Нажмите кнопку возврата

Это обязано показывать общие расходы до ДАТЫ (2019,9,2), что значит просто 1 сентября 2019 года. Мы употребляли лишь функцию ДАТА для представления даты.

Примечание. Мы должны присоединить оператор («<») к дате с помощью амперсанда (&).

Разъяснение формулы

В этом примере функция СУММЕСЛИ инспектирует каждую ячейку от A2 до A10 и отыскивает лишь те ячейки, которые содержат даты до 1 сентября 2019 года.

Для каждой совпадающей ячейки функция СУММЕСЛИ выбирала соответственное значение расходов из столбца B.

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

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

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

Внедрение СУММЕСЛИ с подстановочными знаками

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

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

Если вы желаете выяснить полное количество всех моделей Самсунг, вы сможете применять подстановочные знаки, такие как ‘*’ либо ‘?’ в вашей функции СУММЕСЛИ последующим образом:

• Изберите ячейку, в которой вы желаете показать итог общих продаж (в нашем случае D2).
• Введите в ячейку последующую формулу: = СУММЕСЛИ (A2: A10; «Самсунг *», B2: B10)
• Нажмите кнопку возврата

Это обязано показать количество телефонов Самсунг в ячейке D2.

Примечание. Подстановочный символ звездочки (*) обычно смешивается с основанием слова либо буковкы, чтоб отыскать разные варианты термина.

Разъяснение формулы

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

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

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

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

Вы сможете применять «?» подстановочный символ таковым же образом. ‘?’ подстановочный символ употребляется для представления 1-го знака в любом месте текстовой строчки.

Итак, если вы желаете отыскать, скажем, все модели Apple iPhone X, вы сможете применять «Apple iPhone X?» в состоянии.

Что следует держать в голове

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

• При использовании отдельного спектра сумм не забудьте убедиться, что и спектр, и спектр сумм имеют равное количество ячеек.
• Функция СУММЕСЛИ не чувствительна к регистру.
• Если условие содержит текстовое значение, дату либо подстановочные знаки, оно обязано быть заключено в двойные кавычки.
• Ссылки на ячейки в условии не заключаются в кавычки.
• Если условие состоит из композиции оператора и ссылки на ячейку либо иной функции (к примеру, функции ДАТА), то они должны быть объединены амперсандом (&). К примеру,
  • «<» & ДАТА (2019,9.1)
  • «>» И B2

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

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

  Часть 1

  

  Начинаю обзор основных инструментов возлюбленной нами программки от Microsoft. И естественно в начале желаю поведать о моей самой частоиспользуемой функции («формуле»). А поточнее функция СУММЕСЛИ. Если Вы не представляете, что же все-таки это такое и как это применять — я Для вас завидую! Для меня это было истинное открытие.

  Для вас приходилось суммировать данные по сотрудникам либо клиентам из большенный таблицы, выбирать сколько выручки было по той либо другой номенклатуре? Вы фильтровали по фамилиям/позициям, а потом вносили числа руками в отдельные ячейки? Быть может считали на калькуляторе? А если строк больше тыщи? Как посчитать стремительно?

  Вот здесь понадобится СУММЕСЛИ!

  Задача1. Существует статистика по товарам, городкам и какие характеристики были достигнуты по сиим позициям. Нужно высчитать: «на какую сумму продано номенклатуры Товар1?»

  

  До этого, чем преступить к решению 1ой задачки, разберем из что состоит функция СУММЕСЛИ:

  • Спектр. Спектр, в котором содержатся условия поиска. Заполнять непременно. Для 1ой задачки столбец Продукт.
  • Аспект. Можно заполнить числом (85), выражением («>85″), ссылкой на ячейку (B1), функцией (сейчас()). Описывает условие по которому суммируются (!). Все текстовые условия заключаются в кавычки («) «>85″. Заполнять непременно. Для 1ой задачки столбец =Товар1
  • Диапазон_суммирования. Ячейки, для суммирования, если они различаются от ячеек в Спектре. Для 1ой задачки столбец Выручка.

  Итак запишем формулу, за ранее занеся аргумент условия в ячейку F3

  

  Не забудьте проверить Посчиталось? Правильно? Непревзойденно!

  Задача2. Нужно высчитать: «на какую сумму продано номенклатуры Товар3 в городке Казань?»

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

  

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

  

  Также для подсчета количества повторений той либо другой позиции используйте функции СЧЁТЕСЛИ (СЧЁТЕСЛИМН)

  Задача3. Нужно высчитать: «сколько раз повторялось значение=»Самара» в столбце Город?»

  Решается она еще проще, чем прошлые две.

  

  Часть 2

  Что такое СУММЕСЛИ?

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

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

  Неувязка

  Но с наиболее сложным анализом функция СУММЕСЛИ не управится по полностью тривиальной причине — она выбирает данные лишь по одному условию. Без вариантов. Так, к примеру, если Вы желаете выяснить, сколько яхт было продано в Северной Америке, то функция СУММЕСЛИ Для вас ничем не поможет.

  Тем не наименее, есть как минимум три кандидатуры:

  1. Применять формулы массивов, чтоб суммировать данные по нескольким аспектам.
  2. Применять функцию СУММПРОИЗВ
  3. Применять функцию СУММЕСЛИМН

  Есть, правда, очередное решение, для которого даже Excel не пригодится — съездить в Северную Америку и посчитать яхты, но это, к счастью, не наш способ!

  Решение

  Итак, воспользуемся третьим из предложенных вариантов решения и запишем вот такую формулу:

  Направьте внимание, каким образом соотносятся спектры данных и аргументы данной функции:

  

  Как работает СУММЕСЛИМН?

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

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

  

  Приз

  И не один, а целых два!

  Во-1-х, в Excel 2007 и наиболее поздних версиях существует маленькое семейство схожих функций, включающее в себя СРЗНАЧЕСЛИМН (для вычисления средних значений) и СЧЁТЕСЛИМН (для подсчета количества значений).

  Во-2-х, функции этого семейства могут работать со операторами дела (>, <, = и т.п.) и знаками подстановки, таковыми как «*» — хоть какое количество всех знаков, и «?» — один хоть какой знак. Так, к примеру, чтоб подсчитать количество самолетов, проданных в Северной и Южной Америке, можно пользоваться таковой формулой:

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

  

  Функция СУММЕСЛИ популярна, так как практически в каждой таблице необходимо посчитать сумму чисел в ячейках, игнорируя значения, не попадающие под основное условие. Благодаря данной формуле подсчет не становится кое-чем сложным и долгим. Обычное оформление функции смотрится как =СУММЕСЛИ(Спектр;Аспект;Диапазон_суммирования), а «Спектр суммирования» указывается лишь при условии, когда есть фактические ячейки, сложение которых производится при всех обстоятельствах. Если данные в «Спектре суммирования» отсутствуют, проверяться будут все ячейки, входящие в «Спектр».

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

  Функция СУММЕСЛИ при условии неравенства

  1-ый пример — внедрение функции СУММЕСЛИ при условии, когда число для попадания обязано быть больше, меньше либо не приравниваться данному числу. При таком синтаксисе функция инспектирует все ячейки обозначенного спектра и считает лишь пригодные. Ручное ее написание через поле ввода состоит из нескольких частей:

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

  

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

  Функция СУММЕСЛИ при условии соответствия тексту

  Возьмем 2-ой пример, когда СУММЕСЛИ употребляется для подсчета суммы в ячейках, подпадающих под соответствие надписями в другом спектре блоков. Это понадобится, к примеру, когда производится подсчет общей цены всех продуктов, находящихся в одной группы, либо рассчитываются издержки компании на заработной платы сотрудникам на определенных должностях. Пример дизайна в данной ситуации еще увлекателен тем, что синтаксис незначительно изменяется, так как в условие подпадает 2-ой спектр ячеек.

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

  

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

  Функция СУММЕСЛИМН с несколькими условиями

  Завершим разбор примеров третьим вариантом, когда критерий для сложения сходу несколько. При таковых расчетах рассматриваемая функция заменяется на видоизмененную СУММЕСЛИМН, которая дозволяет задавать наиболее 1-го аргумента, что недозволено воплотить в первом варианте. Один из самых обычных примеров с полным синтаксическим соответствием создается так:

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

  

  

  

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

  Мы рады, что смогли посодействовать Для вас в решении трудности.

  Кроме данной статьи, на веб-сайте еще 12327 инструкций.
  Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

  Отблагодарите создателя, поделитесь статьей в соц сетях.

  Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.