SUMIF Использование текстовых критериев & подстановочных в Microsoft Excel

Posted on: 2020年10月22日 2020年11月12日
Tags: tips

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

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

Общая формула

=SUMPRODUCTcriteria_range=criteria)*(sum_range

Criteria_range: это диапазон, в котором будут соответствовать критерии.

Критерии: это критерий или условие.

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

Пример: сумма денег, потраченных на манго.

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

В I2 формула

=SUMPRODUCT((B2:B9=H2)*C2:E9)

Это возвращает 525 как общую сумму, потраченную на манго. Вы можете увидеть это на изображении выше.

Как это работает? Что ж, это просто. Давайте разберем формулу и разберем ее по частям.

(B2: B9 = H2): эта часть сравнивает каждое значение в диапазоне B2: B9 с H2 и возвращает массив ИСТИНА и ЛОЖЬ.

\ {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}.

(B2: B9 = H2) * C2: E9: Здесь мы умножили каждое значение в приведенном выше массиве на значения в C2: E9. C2: C9 также рассматриваются как 2D-массив. Наконец, этот оператор возвращает 2D-массив из \ {0,0,0; 47,57,67; 0,0,0; 0,0,0; 0,0,0; 0,0,0; 0,0, 0; 108,118,128}.

Теперь СУММПРОИЗВ выглядит так: link: / summing-excel-sumproduct-function [SUMPRODUCT] (\ {0,0,0; 47,57,67; 0,0,0; 0,0,0; 0,0 , 0; 0,0,0; 0,0,0; 108,118,128}).

Он имеет только ценности манго. Он суммирует их и возвращает результат как 525.

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

Функция СУММЕСЛИ в Excel

Как следует из названия, формула СУММЕСЛИ в Excel суммирует значения в диапазоне при заданном условии.

Общая формула СУММЕСЛИ в Excel:

=SUMIF(condition_range,condition,sum range)

Давайте рассмотрим пример. Но теория… Ах! Мы расскажем об этом позже.

Используйте СУММЕСЛИ для суммирования значений при одном условии. Для этого примера я подготовил эти данные.

На основании этих данных нам необходимо ответить на следующие вопросы:

Начнем с первого вопроса.

СУММЕСЛИ с текстовым условием. Нам нужно указать сумму выигрыша, выигранного Рави.

Таким образом, нашим диапазоном условий будет диапазон имен, то есть A2: A10.

Наше состояние — Рави, а диапазон сумм — E2: E10.

Итак, в ячейке I2 мы напишем:

=SUMIF(A2:A10,»ravi»,E2:E10)

Обратите внимание, что рави заключен в двойные кавычки. Текстовые условия всегда заключаются в двойные кавычки. С числами дело обстоит иначе.

Обратите внимание, что ravi пишется мелким шрифтом. Поскольку СУММЕСЛИ не чувствительна к регистру, это не имеет значения.

Приведенная выше формула СУММЕСЛИ вернет 31907, как показано на изображении ниже.

СУММЕСЛИ с логическими операторами. Что касается второго вопроса, наш диапазон условий будет D2: D10.

Условие> 70 и диапазон сумм такой же, как и раньше.

=SUMIF(D2:D10,»>70″,E2:E10)

Приведенная выше формула СУММЕСЛИ вернет 103973, как показано на рисунке ниже.

СУММЕСЛИ с операторами подстановочных знаков В третьем вопросе наше условие — Дели. Но у нас нет городской колонки. Хммм… Так что у нас? Ага! Код города. Это сработает.

Мы знаем, что все коды городов Дели начинаются с 1100. Коды городов состоят из 6 цифр. Итак, мы знаем, что это 1100 фунтов стерлингов. «?» Оператор используется, когда мы знаем количество символов, но не знаем их. Поскольку здесь мы знаем, что после 1100 есть еще два числа. Они могут быть любыми, поэтому мы использовали «?». Если бы мы не знали количество символов, мы бы использовали «*».

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

Вы можете объединять числа с помощью «», чтобы сделать их текстовыми значениями.

(formula to convert number into text) _ = number & “”

_or =CONCATENATE(number,””)

Теперь в ячейке I2 напишите эту формулу

=SUMIF(B2:B10,»1100??»,E2:E10)

Это вернет сумму цены, код города которой начинается с 1100.

В нашем примере это 79836.

Примечания к профессионалам:

Если диапазон сумм и диапазон условий совпадают, вы можете опустить переменную sum_range в функции СУММЕСЛИ. = СУММЕСЛИ (E2: E10, «> 15000») и = СУММЕСЛИ (E2: E10, «> 15000», E2: E10) дадут тот же результат, 56163.
Текстовые значения заключены в двойные кавычки, а числа — нет.

СУММЕСЛИ (C2: C10,103, E2: E10) это будет работать нормально и вернет 28026.

Однако при работе с логическими операторами необходимо использовать двойные кавычки. Как в нашем примере = СУММЕСЛИ (D2: D10, «> 70», E2: E10)

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

Тааак… Это было полезно? Дайте мне знать в разделе комментариев. Попросите свой персональный запрос СУММЕСЛИ, если вы по-прежнему сталкиваетесь с трудностями.

Так что да, ребята, вот как вы можете суммировать несколько столбцов с условием без использования функции sumif. Дайте мне знать, если у вас есть какие-либо сомнения относительно этой статьи или любой другой статьи на этом сайте. Вы также можете задавать вопросы по Excel 2019, 2016, 2013 и более ранним версиям.