Excel: как суммировать, если ячейки содержат текст
Вы можете использовать следующие методы для суммирования ячеек в Excel, содержащих определенный текст:
Метод 1: суммирование ячеек, содержащих один конкретный текст
Эта формула суммирует ячейки в диапазоне B2:B11 , где соответствующие ячейки в A2:A11 содержат «avs» в ячейке.
Метод 2: суммирование ячеек, содержащих один из нескольких текстов
Эта формула суммирует ячейки в диапазоне B2:B11 , где соответствующие ячейки в A2:A11 содержат «avs» или «eat» в ячейке.
В следующих примерах показано, как использовать каждый метод на практике со следующим набором данных в Excel:
Пример 1: сумма ячеек, содержащих один конкретный текст
Мы можем использовать следующую формулу для суммирования значений в столбце « Очки », где название команды содержит «avs» в названии:
На следующем снимке экрана показано, как использовать эту формулу на практике:
Мы видим, что ячейки, в которых столбец « Команда » содержит «avs», набрали в общей сложности 52 балла.
Мы можем проверить это вручную, просуммировав значения, где столбец Team содержит «avs»:
Сумма Очков Команд, в названии которых есть «avs»: 12 + 5 + 8 + 10 + 17 = 52 .
Пример 2. Суммарные ячейки, содержащие один из нескольких текстов
Мы можем использовать следующую формулу для суммирования значений в столбце « Очки », где название команды содержит «avs» или «eat» в названии:
На следующем снимке экрана показано, как использовать эту формулу на практике:
Мы видим, что ячейки, в которых столбец Team содержит «avs» или «eat», набрали в общей сложности 65 баллов.
Мы можем проверить это вручную, просуммировав значения, где столбец Team содержит «avs» или «eat»:
Сумма Очков для Команд, в названии которых есть «avs» или «eat»: 12 + 5 + 8 + 10 + 13 + 17 = 65 .
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Пример функции СУММЕСЛИ для суммирования в Excel по условию
Функция СУММЕСЛИ в Excel используется для расчета суммы числовых значений, содержащихся в диапазоне ячеек, с учетом критерия, указанного в качестве одного из аргументов, и возвращает соответствующее числовое значение. Данная функция является альтернативой совместного использования функций СУММ и ЕСЛИ. Ее использование позволяет упростить формулы, поскольку критерий, по которому производится суммирование значений, предусмотрен непосредственно в ее синтаксисе.
Примеры использования функции СУММЕСЛИ в Excel
Пример 1. В таблице Excel записаны члены геометрической прогрессии. Какую часть (в процентах) составляет сумма первых 15 членов прогрессии от общей суммы ее членов.
Вид исходной таблицы данных:
Выполним расчет с помощью следующей формулы:
- A3:A22 – диапазон ячеек, содержащих порядковые номера членов прогрессии, относительно которых задается критерий суммирования;
- «<=15» – логическое выражение, являющееся критерием суммирования;
- B3:B22 – диапазон ячеек, содержащих значения членов прогрессии.
Доля в процентах первых 15-ти значений (75% — от количества всех 20-ти значений) данной геометрической прогрессии составляет всего лишь 0,41%.
Сумма ячеек с определенным значением в Excel
Пример 2. В таблице Excel указаны данные о работе двух продавцов небольшого магазина. Определить, какой из сотрудника принес больший доход за 19 рабочих дней).
Исходная таблица имеет следующий вид:
Для расчета используем функцию в формуле:
Функция ЕСЛИ выполняет проверку возвращаемых значений функциями СУММЕСЛИ с условиями проверки «Иванов» и «Петров» соответственно и возвращает текстовую строку с фамилией продавца, суммарная прибыль которого оказалась больше.
В итоге получим следующее значение:
Как в Excel суммировать ячейки только с определенным значением
Пример 3. В таблице указаны данные о зарплате сотрудника на протяжении 12 месяцев прошлого года. Рассчитать доходы работника за весенние месяцы.
Вид таблицы данных:
Весенними месяцами являются месяца с номерами 3, 4 и 5. Для расчета используем формулу:
Сумма зарплат с 6-го по 12-й месяц является подмножеством множества суммы зарплат с 3-го по 12-й месяц. Разница этих сумм является искомой величиной – суммой зарплат за весенние месяцы:
Функцию СУММЕСЛИ можно использовать если требуется определить сразу несколько значений для различных критериев. Например, для расчета суммы зарплат за три первых и три последних месяца в году соответственно составим следующую таблицу:
Суммировать, если ячейка содержит определенный текст с использованием подстановочных знаков — Excel и Google Таблицы
В этом руководстве будет показано, как использовать функцию СУММЕСЛИМН и подстановочные знаки для суммирования данных, соответствующих ячейкам, содержащим определенный текст в Excel и Google Таблицах.
Сумма, если текст содержит
Сначала мы продемонстрируем, как суммировать данные, относящиеся к ячейкам, содержащим определенный текст, с помощью функции СУММЕСЛИМН.
Функция СУММЕСЛИМН суммирует строки данных, которые соответствуют определенным критериям. Его синтаксис:
Этот пример суммирует все Очки с Название штата который содержит «Дакота» с использованием функции СУММЕСЛИМН и подстановочного знака *.
| 1 | = СУММЕСЛИМН (C3: C9; B3: B9; «* Дакота *») |
Символ * позволяет использовать любое количество (включая ноль) других символов.
В этом примере он используется для поиска всех ячеек, содержащих текст «Дакота». Этот поиск не чувствителен к регистру, поэтому «дакота» считается тем же, что и «Дакота» или «ДАКОТА». Северная Дакота а также Северная Дакота содержат «Дакота» и поэтому включены в сумму.
Сумма, если текст начинается с
Символ * также можно использовать для поиска ячеек, которые начинаются с указанного текста:
| 1 | = СУММЕСЛИМН (C3: C9; B3: B9; «Новый *») |
Нью-Йорк, Нью-Джерси, а также Нью-Мексико начинаются с «Новый» и поэтому включаются в сумму. Обратите внимание, что при поисковом запросе «Новый *» текстовая ячейка должна начинаться с «Новый»; просто содержать эти символы недостаточно.
Сумма, если текст заканчивается
Точно так же мы можем просуммировать все Очки для состояния оканчивается на «o», используя:
| 1 | = СУММЕСЛИМН (C3: C9; B3: B9; "* o") |
Нью-Мексико а также Огайо оканчиваются на «о» и поэтому включаются в сумму.
С помощью ? Подстановочный знак
? Символ может использоваться для представления любого отдельного символа в текстовой строке.
Этот пример находит все Имена штатов начиная с «New», за которым следуют ровно 7 символов (включая пробелы).
| 1 | = СУММЕСЛИМН (C3: C9; B3: B9; "Новый . ") |
Нью-Джерси а также Нью-Мексико соответствуют этим критериям, но Нью-Йорк нет, так как после слова «Новый» в Нью-Йорк.
Обратите внимание, что * и? Подстановочные знаки можно комбинировать, если требуется для создания очень конкретных команд поиска. Следующий пример находит Имена штатов которые начинаются с «N» и содержат «о» перед последним символом строки. Это исключает Нью-Мексико; он начинается с «N», но не имеет «о» перед последним символом.
| 1 | = СУММЕСЛИМН (C3: C9; B3: B9; «N * o? *») |
Эта функция особенно полезна при поиске по кодам продуктов, почтовым индексам или серийным номерам, где положение каждого символа имеет определенное значение.
Использование символа
(известный как тильда) позволяет нам лечить * или? символы, как если бы они были простыми текстовыми значениями и не ведут себя как подстановочные знаки.
В приведенном ниже примере нам нужно просуммировать Уровень запасов когда наименование товара конкретно соответствует тексту «Продукт?»:
непосредственно перед * или? символ превращает его обратно в текстовое значение, поэтому поисковый запрос «Продукт
?» используется для поиска точного соответствия текста запросу «Продукт?».
Объединение подстановочных знаков СУММЕСЛИМН со ссылками на ячейки
Обычно жестко записывать значения в формулы не рекомендуется. Вместо этого более гибко использовать отдельные ячейки для указания значений для наших условий поиска.
Чтобы определить, содержат ли ячейки текст, содержащийся в ячейке E3, мы можем использовать функцию СУММЕСЛИМН со ссылкой на ячейку и подстановочными знаками *:
| 1 | = СУММЕСЛИМН (C3: C9; B3: B9; «*» & E3 & «*») |
Обратите внимание, что текст «Дакота» был заменен ссылкой на ячейку & E3 &, а символы * были заключены в кавычки (»«).
Множественные ссылки на ячейки и подстановочные знаки также можно комбинировать. Найти Имена штатов которые начинаются с текста в ячейке E3 и содержат текст в ячейке F3, за которым следует как минимум еще 1 символ, можно использовать следующую формулу:
| 1 | = СУММЕСЛИМН (C3: C9, B3: B9, E3 & "*" & F3 & "? *") |
Блокировка ссылок на ячейки
Чтобы наши формулы было легче читать, мы показали формулы без заблокированных ссылок на ячейки:
| 1 | = СУММЕСЛИМН (C3: C9; B3: B9; «*» & E3 & «*») |
Но эти формулы не будут работать должным образом при копировании и вставке в другое место в файле. Вместо этого вы должны использовать ссылки на заблокированные ячейки следующим образом:
Формула суммы ячеек в Excel
Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит «*».
Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».
Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символов» и так далее.
Потому как звездочки и вопросительные знаки, сами по себе являются подстановочными знаками, если вы хотите искать эти символы в частности, вы должны защитить их с тильдой (
). Тильда причины Excel следует рассматривать следующий символ буквально.
В этом случае мы используем «
*», чтобы соответствовать символу звездочки, но он окружен звездочками по бокам, для того чтобы соответствовать звездочкам в любом месте ячейки. Если вы просто хотите, чтобы соответствовало звездочкам на конце ячейки, используйте: «*
Альтернатива с СУММЕСЛИМН
Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. С СУММЕСЛИМН, диапазон сумма всегда стоит на первом месте в списке аргументов, затем пара/диапазон критериев:
Сумма, если ячейки содержат X и Y
Чтобы посчитать сумму, если ячейки содержат X и Y (т. е. содержат «кошка» и «крыса», в одной ячейке) можно использовать функцию СУММЕСЛИМН.
В показанном примере, формула в F5:
Функция СУММЕСЛИМН основана на логике и такое поведение является автоматическим. Нам просто нужно поставить два диапазона/критерии пары, работающие на одном диапазоне (В5:В9).
Для обоих критериев (содержит «крыса», содержится «кошка») мы используем звездочку, которая является подстановочным, что означает «один или несколько символов». Мы ставим звездочку в начале и в конце, чтобы позволить формуле проверить, чтобы соответствовало «кошка» и «крыса» везде, где они появляются в ячейке.
Когда оба критерия рассчитывает Истина в том же ряду, СУММЕСЛИМН суммирует значения в столбец C.
Обратите внимание, что СУММЕСЛИМН не чувствителен к регистру.
Сумма, если ячейки содержат либо x, либо Y
Чтобы посчитать сумму, если ячейки содержат либо одну текстовую строку или другую (т. е. содержащие «кошка» или «крыса») вы можете использовать функция СУММПРОИЗВ.
Когда вы суммируете ячейки с критерием «или», вы должны быть осторожны, чтобы не произошел двойной счет, когда существует вероятность того, что оба критерия будет рассчитывать Истина. В показанном примере, мы хотим просуммировать значения в столбце C, когда ячейки в столбце B содержат слово «кошка» или «крыса». Мы не можем использовать СУММЕСЛИМН по двум критериям, т. к. СУММЕСЛИМН основан на логике. И если мы попытаемся использовать две функции СУММЕСЛИМН (т.е. СУММЕСЛИМН + СУММЕСЛИМН) у нас будет двойной счет, потому что есть клетки, которые содержат как «кошка», так и «крыса»
Одно из решений-использовать функцию СУММПРОИЗВ с ЕЧИСЛО + ПОИСК или НАЙТИ.
Эта формула основана на формуле, что находит текст внутри ячейки:
При заданном диапазоне ячеек, этот фрагмент будет рассчитывать массив значений Истина или Ложь, одно значение для каждой ячейки диапазона. Поскольку мы делаем это дважды (один раз для «кошки» и один раз для «крысы»), мы получим два массива.
Далее, мы добавляем эти массивы вместе (с +), которая создает новый единый массив чисел. Каждое число в этом массиве является результатом добавления истинных и ложных значений в исходных двух массивах вместе. В показанном примере, массив выглядит так:
Нам нужно добавить эти цифры, но мы не хотим, чтобы удвоился счет. Поэтому мы должны убедиться, что любое значение больше нуля, всего лишь раз пересчитать. Чтобы сделать это, мы превращаем все значения Истина или Ложь, проверяя массив с «>0». Это рассчитывает Истина или Ложь:
Которые мы затем преобразовываем в 1/0 с помощью двойного отрицания (—):
Параметр чувствителен к регистру
Функция поиска не учитывает регистр. Если вам нужен чувствительный вариант, замените поиск на найти.
Сумма, если ячейки содержат определенный текст
Чтобы посчитать сумму, если ячейки содержат определенный текст, вы можете использовать функцию СУММЕСЛИ с подстановкой.
В примере показано, ячейка G4 содержит эту формулу:
Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит «футболка». Обратите внимание, что СУММЕСЛИ-это не регистр.
Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».
Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символа» и так далее.
Нужно, чтобы соответствовали все элементы, которые содержат слово «футболка», критериям»*футболка*». Обратите внимание, что вы должны заключить текст и подстановочные знаки в двойные кавычки («»).
Альтернатива с СУММЕСЛИМН
Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:
Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.
Сумма, если ячейка заканчивается определенным текстом
В сумме, если ячейки заканчиваются определенным текстом, можно использовать функцию СУММЕСЛИ.
В примере показано, ячейка G5 содержит эту формулу:
Эта формула суммирует ячейки именованного диапазона сумма (D5:D11), только если ячейки именованного диапазона (С5:С11) оканчиваются на «шапка».
Обратите внимание, что СУММЕСЛИ не поддерживает регистр. Критерию «*шапка» соответствует любой текст, который заканчивается «Шапка» или «шапка».
Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».
Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символа» и так далее.
Чтобы соответствовали все элементы, которые оканчиваются на «шапка» перед текстом нужно поставить звездочку (*):
Обратите внимание, что вы должны заключить текст и подстановочные знаки в двойные кавычки («»).
Альтернатива с СУММЕСЛИМН
Вы также можете использовать функцию СУММЕСЛИМН в сумме, если клетки начинаются с. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:
Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.
Сумма, если равен х или у
Если вам необходимо суммировать числа, основанные на других ячейках равных либо одному значению или другому (х или у), вы можете использовать функцию СУММЕСЛИ.
В примере, мы подсчитываем все продажи либо от Западного или Северного региона. Формула в ячейке G6 является:
Каждый экземпляр СУММЕСЛИ обеспечивает промежуточный итог, один для продаж на Западе, один для продаж на Севере. Формула просто добавляет эти два результата вместе.
СУММЕСЛИ с аргументом массива
Более элегантное решение, чтобы дать функции СУММЕСЛИ более одного значения для критериев, использовать константу массива. Чтобы сделать это, постройте нормальный СУММЕСЛИ, но пакет критериев в синтаксисе массива — фигурные скобки, с отдельными элементами, разделенные запятыми. И, наконец, обернуть всю функцию СУММЕСЛИ в функцию СУММ. Это необходимо, потому что СУММЕСЛИ будет рассчитывать один результат для каждого элемента массива критериев. Они должны быть добавлены вместе, чтобы получить один результат.
Вы можете также использовать СУММПРОИЗВ для подсчета ячеек с логикой ИЛИ. Формула:
=СУММПРОИЗВ( количество * (( регион = «Западный» ) + ( регион = «Северный» )))
Это также может быть записано в виде:
СУММПРОИЗВ не так быстра, как СУММЕСЛИ, но разница в скорости не заметна с меньшими наборами данных.
Сумма, если ячейки не пустые
Для подсчита ячеек, когда определенные значения не являются пустыми, вы можете использовать функцию СУММЕСЛИ.
В показанном примере, ячейка G4 содержит следующую формулу:
Эта формула суммирует суммы в колонке D только тогда, когда значение в столбце С не пусто
Функция СУММЕСЛИ поддерживает все стандартные операторы Excel, в том числе не равно к, который вводится в <>.
При использовании оператора в критериях функции как СУММЕСЛИ, необходимо заключить его в двойные кавычки ( «»). При использовании только «<>» в качестве критерия, вы можете думать о значении как «не равно пустой», или «не пусто».
Альтернатива с СУММЕСЛИМН
Вы можете также использовать функцию суммы СУММЕСЛИМН, если ячейки не являются пустыми. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эта формула СУММЕСЛИМН эквивалентна формуле СУММЕСЛИ выше: