12 простых способов для ускоренной работы в excel

12 простых способов для ускоренной работы в excel
На чтение
28 мин.
Просмотров
76
Дата обновления
31.10.2024

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

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

 

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

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

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

Синтаксис функции следующий:

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

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

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

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

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

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

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

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

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

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

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

 

сравнить числа - sumif в excel для чайников

 

I3 говорит:

=СУММЕСЛИ(D2:D21,I2)

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

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

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

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

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

=СУММЕСЛИ(D2:D21;"

То есть суммируем все заказы, где количество меньше 144.

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

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

Критерии для текста.

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

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

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

 

определить текстовое соответствие

 

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

=СУММЕСЛИ(F2:F21,I2,E2:E21)

F2:F21 — это область, где мы выбираем соответствующие значения.

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

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

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

=СУММЕСЛИ(F2:F21, "Да", E2: E21)

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

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

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

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

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

 

носить сменную маску

 

Мы можем искать и считать значения, указывая не все содержимое ячейки, а только часть. Поэтому мы можем расширить пределы поиска с помощью подстановочных знаков “?”, “*”.

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

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

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

=СУММЕСЛИ(C2:C21,I2,E2:E21) , где E2 содержит *[слово]*

или

=СУММЕСЛИ(C2:C21;"*"&I2&"*";E2:E21)

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

Давайте практиковаться:

  • «*чёрный*»: ищем фразу, в которой встречается это выражение, а до и после любую букву, знак и цифру. В нашем случае это соответствует «Темный шоколад» и «Супер темный шоколад”.
  • "ЧАС?" - нужно слово из 2-х букв, первая из которых "Д", а вторая любая. В нашем случае подойдет «Да”.
  • «???» - найти слово из любых 3 букв

=СУММЕСЛИ(F2:F21;"???";E2:E21)

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

  • «??????*» - текст из 7 букв и более.

=СУММЕСЛИ(B2:B21;"?????????*";E8:E28)

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

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

Примечание. Если вам нужно использовать текст, содержащий * и ? в качестве задачи поиска используйте знак тильды (~) перед этими символами. И что? будут рассматриваться как обычные символы, а не как шаблон:

=СУММЕСЛИ(B2:B21,"*~?*";E8:E28)

Важная заметка. Если ваш поисковый текст содержит несколько символов * и ?, вы должны поставить тильду (~) перед каждым символом. Например, если мы ищем текст, который состоит из трех звездочек, то мы можем написать его так:

=СУММЕСЛИ(B2:B21,"~*~*~*",E8:E28)

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

=СУММЕСЛИ(B2:B21,"*~*~*~**",E8:E28)

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

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

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

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

 

сумма за период времени

 

Рассчитаем общий объем продаж на сегодня - 04.02.2020.

=СУММЕСЛИ(A2:A21,I1,E2:E21)

или

=СУММЕСЛИ(A2:A21,СЕГОДНЯ(),E2:E21)

Считаем вчерашний день.

=СУММЕСЛИ(A2:A21,СЕГОДНЯ()-1,E2:E21)

СЕГОДНЯ()-1 будет просто «вчера".

Мы добавляем даты, предшествующие 1 февраля.

=СУММЕСЛИ(A2:A21;"

После 1 февраля включительно:

=СУММЕСЛИ(A2:A21;">="&"01.02.2020";E2:E21)

Что, если нас интересует временной интервал «от-до"?

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

=СУММЕСЛИ(A2:A21;">="&"01/02/2020";E2:E21) - СУММЕСЛИ(A2:A21;"

Сумма значений, соответствующих пустым либо непустым ячейкам

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

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

 

итоги для пустых или непустых значений

 

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

=СУММЕСЛИ(F2:F21,"*",E2:E21)

Использование пары знаков больше и меньше - вместо звездочки дает точно такой же результат» - .

=СУММЕСЛИ(F2:F21,"",E2:E21)

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

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

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

=СУММЕСЛИ(F2:F21,"",E2:E21)

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

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

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

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

 

суммировать по двум критериям

 

Это просто:

=СУММЕСЛИ($C$2:$C$21;"*"&H3&"*";$E$2:$E$21)+СУММЕСЛИ($C$2:$C$21;"*"&H4&"*"; $E$2: 21 доллар США)

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

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

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

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

 

аргумент записывается как массив

 

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

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

СУММЕСЛИ($C$2:$C$21;{"*черный*";"*молочный*"};$E$2:$E$21)

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

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

=СУММ(СУММЕСЛИ($C$2:$C$21,{"*черный*";"*молочный*"},$E$2:$E$21))

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

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

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

 

 

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

=СУММПРОИЗВ(СУММЕСЛИ(C2:C21,H3:H4,E2:E21))

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

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

=СУММПРОИЗВ(СУММ.ЕСЛИ(C2:C21,{"*черный*";"*молочный*"},E2:E21))

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

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

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

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

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

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

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

Неверно: =СУММЕСЛИ({1,2,3}, "цвет", C1:C3)

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

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

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

=СУММЕСЛИ([Book1.xlsx]Лист1!$A$2:$A$9,"яблоки",[Book1.xlsx]Лист1!$F$2:$F$9)

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

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

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

 

0 Комментариев
Комментариев на модерации: 0
Оставьте комментарий