СУММЕСЛИ и СУММЕСЛИМН с учетом регистра в Excel и Google Таблицах
В этом руководстве вы узнаете, как создать формулу СУММЕСЛИ или СУММЕСЛИМН с учетом регистра в Excel и Google Sheets.
Функции СУММЕСЛИ и СУММЕСЛИМН доступны как в Microsoft Excel, так и в Google Sheets. И в обоих приложениях они по своей природе нечувствительны к регистру. Чтобы условно суммировать ячейки, рассматривающие строчные и прописные буквы как разные символы, вам придется придумать что-то еще.
Чувствителен ли регистр СУММЕСЛИ / СУММЕСЛИМН?
Нет. Как в Excel, так и в Google Таблицах ни СУММЕСЛИ, ни СУММЕСЛИМН не могут распознать регистр букв. Чтобы убедиться в этом, рассмотрим простой пример:
Предположим, у вас есть список идентификаторов в столбце A, где прописные и строчные буквы обозначают разные элементы. Соответствующие цифры продаж находятся в столбце B. Цель состоит в том, чтобы получить сумму продаж для определенного товара, скажем А-01.
С идентификатором цели в E1 мы создаем эту классическую формулу СУММЕСЛИ:
=СУММЕСЛИ(A2:A6, E1, B2:B6)
И получить абсолютно неверный результат 🙁
Формула суммы с учетом регистра в Excel
Чтобы просуммировать ячейки с одним условием с учетом регистра букв, вы можете использовать функции СУММПРОИЗВ и ТОЧНОЕ вместе:
СУММПРОИЗВ(–(ТОЧНО(критерии, диапазон)) сумма_диапазон)
С идентификатором цели в E1(критерии), список элементов в формате A2:A10 (диапазон) и номера продаж в B2:B10 (сумма_диапазон), формулы принимают следующий вид:
=СУММПРОИЗВ(–(ТОЧНО(E1, A2:A10)), B2:B10)
При необходимости вы можете «жестко закодировать» критерии прямо в формуле:
=СУММПРОИЗВ(–(ТОЧНО(“A-01”, A2:A10)), B2:B10)
Как работает эта формула:
В основе формулы функция EXACT сравнивает целевой элемент (E1) с каждым элементом в списке и возвращает TRUE, если сравниваемые значения в точности совпадают, включая текстовый регистр, в противном случае FALSE:
{ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ}
Двойной унарный оператор (–) преобразует ИСТИНА и ЛОЖЬ в 1 и 0 соответственно:
{1;0;0;0;1;0;1;0;0}
Функция СУММПРОИЗВ умножает элементы вышеуказанного массива на соответствующие элементы в ячейках B2:B10:
СУММПРОИЗВ({1;0;0;0;1;0;1;0;0}, {250;155;130;255;160;280;170;285;110})
И поскольку умножение на 0 дает ноль, выживают только те элементы, для которых EXACT возвращает TRUE:
СУММПРОИЗВ({250;0;0;0;160;0;170;0;0})
Наконец, СУММПРОИЗВ складывает произведения и выводит сумму.
Сумма с учетом регистра в Excel (с несколькими критериями)
Если вы ищете формулу СУММЕСЛИМН с учетом регистра с двумя или более критериями, вы можете эмулировать это поведение, определив дополнительную пару диапазон/критерий в другой функции EXACT:
СУММПРОИЗВ(–(ТОЧНО(критерии1, диапазон1)), –(ТОЧНЫЙ(критерии2, диапазон2)) сумма_диапазон)
Например, чтобы суммировать продажи определенного товара (F1) в заданном регионе (F2), формула выглядит следующим образом:
=СУММПРОИЗВ(–(ТОЧНО(F1, A2:A10)), –(ТОЧНО(F2, B2:B10)), C2:C10)
Формула «Сумма, если ячейка содержит» с учетом регистра в Excel
В ситуации, когда вам нужно сложить значения в одном столбце, если ячейка в другом столбце содержит определенный текст как часть содержимого ячейки, используйте функцию СУММПРОИЗВ вместе с НАЙТИ:
СУММПРОИЗВ(–(ЧИСЛО(НАЙТИ(критерии, диапазон))), сумма_диапазон)
Например, для суммирования продаж товара в E1 (который может соответствовать целой ячейке в A2:A10 или быть только частью текстовой строки) используется следующая формула:
=СУММПРОИЗВ(–(ЧИСЛО(НАЙТИ(E1, A2:A10))), B2:B10)
Чтобы суммировать ячейки на основе нескольких условий, добавьте еще одну комбинацию ISNUMBER/FIND:
СУММПРОИЗВ(–(ЧИСЛО(НАЙТИ(критерии1, диапазон1))), –(ЧИСЛО(НАЙТИ(критерии2, диапазон2))), сумма_диапазон)
Например, чтобы получить сумму продаж с двумя условиями (идентификатор товара в F1 и регион в F2), используется следующая формула:
=СУММПРОИЗВ(–(ЧИСЛО(НАЙТИ(F1, A2:A10))), –(ЧИСЛО(НАЙТИ(F2, B2:B10))), C2:C10)
Обратите внимание, что формула суммирует продажи для указанного товара в любом «северном» регионе, например Север, К северо-востокуили же Северо-Запад.
Как работает эта формула:
Здесь мы используем функцию FIND с учетом регистра для поиска целевого элемента (E1). Когда элемент найден, функция возвращает его относительное положение в исходной строке, в противном случае возникает ошибка #ЗНАЧ.
{2;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;1;#ЗНАЧ!;1;#ЗНАЧ!;#ЗНАЧ!}
Функция ISNUMBER преобразует любое число в TRUE, а значения ошибок в FALSE:
{ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ}
Затем вы выполняете «двойное отрицание» (–), чтобы привести логические значения к 1 и 0:
{1;0;0;0;1;0;1;0;0}
Наконец, функция СУММПРОИЗВ умножает элементы двух массивов и выводит результат:
СУММПРОИЗВ({1;0;0;0;1;0;1;0;0}, {250;155;130;255;160;280;170;285;110})
Если ваша формула обрабатывает несколько условий, то СУММПРОИЗВ будет перемножать элементы трех или более массивов. В нашем случае это:
=СУММПРОИЗВ({1;0;0;0;1;0;1;0;0}, {1;0;1;1;1;1;0;1;0}, {250;155;130; 255;160;280;170;285;110})
СУММЕСЛИ с учетом регистра в Google Таблицах
Формулы Sum If с учетом регистра, которые мы создали для Excel, также будут работать в Google Таблицах. Кроме того, вы можете получить саму функцию СУММЕСЛИ в Google Sheet, чтобы различать прописные и строчные буквы. Вот как:
СУММЕСЛИ(Формуламассива(ТОЧНО(критерий, диапазон)), ПРАВДА, сумма_диапазон)
Или же
СУММЕСЛИ(Формуламассива(НАЙТИ(критерий, диапазон)) 1, сумма_диапазон)
Для нашего примера набора данных реальные формулы выглядят следующим образом:
=СУММЕСЛИ(Формуламассива(ТОЧНО(E1, A2:A10)), ИСТИНА, B2:B10)
=СУММЕСЛИ(Формуламассива(НАЙТИ(E1, A2:A10)), 1, B2:B10)
Как работает формула SUMIF/EXACT:
Чтобы сделать SUMIF в Google Таблицах (диапазон, критерий, [sum_range]) функция распознавания регистра букв, мы используем следующую формулу для диапазон аргумент:
Формула массива (ТОЧНО (E1, A2: A10))
ArrayFormula заставляет EXACT сравнивать значение в E1 с каждым значением в A2:A10. Если найдено точное совпадение, формула возвращает ИСТИНА, в противном случае – ЛОЖЬ.
В диапазоне значений ИСТИНА и ЛОЖЬ функция СУММЕСЛИ ищет ИСТИНА и суммирует соответствующие значения в ячейках B2:B10. Это все!
Как работает формула СУММЕСЛИ/НАЙТИ:
Здесь мы используем комбинацию ArrayFormula и FIND для поиска целевого значения (E1) в диапазоне A2:A10:
Формула массива (НАЙТИ (E1, A2: A10))
При отдельном использовании функция НАЙТИ прекратит поиск после нахождения первого совпадения.
Везде, где найдено целевое значение, формула возвращает 1 (что является его относительным положением в строке поиска). Для ячеек, в которых значение не найдено, #VALUE! возвращается ошибка.
Затем вы используете число 1 в качестве критерия СУММЕСЛИ и выполняете свою работу 🙂
СУММЕСЛИМН с учетом регистра в Google Sheets
Чтобы суммировать ячейки с несколькими условиями в Google Таблицах, вы можете использовать либо формулы СУММПРОИЗВ с учетом регистра, описанные в части нашего руководства для Excel, либо СУММЕСЛИМН в Google Таблицах в сочетании с ТОЧНО или НАЙТИ:
СУММЕСЛИМН(сумма_диапазонМассивФормула(ТОЧНО(критерий 1, диапазон1)), ИСТИНА, формула массива (ТОЧНО (критерий2, диапазон2)), ПРАВДА)
Или же
СУММЕСЛИМН(сумма_диапазон,ФормулаМассива(НАЙТИ(критерий 1, диапазон1)), 1, Формула Массива(НАЙТИ(критерий2, диапазон2)) 1)
Как видите, логика такая же, как и в функции СУММЕСЛИ. Разница в том, что вы используете две или более пар диапазона/критерия.
Например, для суммирования продаж товара в F1 и региона в F2 используются следующие формулы:
=СУММЕСЛИМН(C2:C10,Формуламассива(ТОЧНО(F1, A2:A10)), ИСТИНА,Формуламассива(ТОЧНО(F2, B2:B10)), ИСТИНА)
=СУММЕСЛИМН(C2:C10, Формула массива(НАЙТИ(F1, A2:A10)), 1, Формула массива(НАЙТИ(F2, B2:B10)), 1)
Вот как вы можете создать формулу СУММЕСЛИ или СУММЕСЛИМН с учетом регистра в Excel и Google Sheets. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практические рабочие тетради
СУММЕСЛИ/СУММЕСЛИМН с учетом регистра в Excel (файл .xlsx)
СУММЕСЛИ/СУММЕСЛИМН с учетом регистра в Google Sheets (онлайн лист)
