Загрузите образец книги
В этом руководстве будет показано, как использовать функцию СУММЕСЛИМН и подстановочные знаки для суммирования данных, соответствующих ячейкам, содержащим определенный текст в 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? *») |
Эта функция особенно полезна при поиске по кодам продуктов, почтовым индексам или серийным номерам, где положение каждого символа имеет определенное значение.
Использование символа ~ (тильда)
Специальный символ ~ (известный как тильда) позволяет нам лечить * или? символы, как если бы они были простыми текстовыми значениями и не ведут себя как подстановочные знаки.
В приведенном ниже примере нам нужно просуммировать Уровень запасов когда наименование товара конкретно соответствует тексту «Продукт?»:
| 1 | = СУММЕСЛИМН (C3: C8; B3: B8; «Продукт ~?») |
Знак ~ непосредственно перед * или? символ превращает его обратно в текстовое значение, поэтому поисковый запрос «Продукт ~?» используется для поиска точного соответствия текста запросу «Продукт?».
Объединение подстановочных знаков СУММЕСЛИМН со ссылками на ячейки
Обычно жестко записывать значения в формулы не рекомендуется. Вместо этого более гибко использовать отдельные ячейки для указания значений для наших условий поиска.
Чтобы определить, содержат ли ячейки текст, содержащийся в ячейке E3, мы можем использовать функцию СУММЕСЛИМН со ссылкой на ячейку и подстановочными знаками *:
| 1 | = СУММЕСЛИМН (C3: C9; B3: B9; «*» & E3 & «*») |
Обратите внимание, что текст «Дакота» был заменен ссылкой на ячейку & E3 &, а символы * были заключены в кавычки (»«).
Множественные ссылки на ячейки и подстановочные знаки также можно комбинировать. Найти Имена штатов которые начинаются с текста в ячейке E3 и содержат текст в ячейке F3, за которым следует как минимум еще 1 символ, можно использовать следующую формулу:
| 1 | = СУММЕСЛИМН (C3: C9, B3: B9, E3 & "*" & F3 & "? *") |
Блокировка ссылок на ячейки
Чтобы наши формулы было легче читать, мы показали формулы без заблокированных ссылок на ячейки:
| 1 | = СУММЕСЛИМН (C3: C9; B3: B9; «*» & E3 & «*») |
Но эти формулы не будут работать должным образом при копировании и вставке в другое место в файле. Вместо этого вы должны использовать ссылки на заблокированные ячейки следующим образом:
| 1 | = СУММЕСЛИМН ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "*" & E3 & "*") |
Прочтите нашу статью о блокировке ссылок на ячейки, чтобы узнать больше.
Сумма, если ячейка содержит определенный текст с использованием подстановочных знаков в Google Таблицах
Эти формулы работают в Google Таблицах точно так же, как и в Excel.
