SUMIFS, соответствующие нескольким диапазонам значений

На чтение 3 мин Опубликовано 19.09.2024

Вопрос или проблема

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

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

Вот пример – https://docs.google.com/spreadsheets/d/1T7pZoj1hvotzUFyvfjm7M-8rSJBlOI9MkzSYrQr1bH8/edit?gid=0#gid=0

Я пытаюсь использовать сумму различных SUMIFS(), но эта формула должна быть изменена в случае добавления другой комбинации: =SUMIFS(C:C,A:A,”критерий 1″,B:B,”AU”)+SUMIFS(C:C,A:A,”критерий 1″,B:B,” AU *”)+SUMIFS(C:C,A:A,”критерий 1″,B:B,”AU *”)

Как лучше всего использовать SUMIFS() с REGEXMATCH, чтобы включить только необходимые комбинации ключевых слов?

Ответ или решение

Чтобы решить вашу задачу с использованием функции SUMIFS в сочетании с условием, основанным на регулярных выражениях, мы можем воспользоваться функцией FILTER в Google Sheets, чтобы отсортировать данные в соответствии с заданными критериям, и затем использовать SUM для получения итоговой суммы. В Google Sheets нет прямой поддержки регулярных выражений в функции SUMIFS, но мы можем достичь желаемого результата через гибкий подход.

Вот как это можно сделать:

Создайте отдельный столбец для проверки наличия ключевых слов. Например, добавьте столбец D и в ячейку D2 вставьте формулу, которая будет проверять наличие ключевых слов с использованием REGEXMATCH:
```
=REGEXMATCH(A2; "(AU|другое_слово|ещё_слово)")  // Здесь укажите необходимые ключевые слова через оператор "или"
```
Протяните эту формулу вниз по всем строкам.
Используйте функцию SUM и FILTER. Теперь, когда у вас есть столбец, который проверяет наличие ключевых слов, вы можете использовать его для фильтрации значений и суммирования нужных строк. Например, формула будет выглядеть следующим образом:
```
=SUM(FILTER(C:C; D:D=TRUE; B:B="условие_для_B"))
```
Здесь C:C — это диапазон суммируемых значений, D:D=TRUE — условие для проверки наличия ключевых слов, а B:B="условие_для_B" — другое условие для фильтрации.
Добавление дополнительных условий. Если вам необходимо добавить дополнительные условия, просто добавьте их в FILTER. Например:
```
=SUM(FILTER(C:C; D:D=TRUE; B:B="условие_для_B"; E:E="дополнительное_условие"))
```

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

Если у вас есть дополнительные условия или комбинации ключевых слов, просто обновите регулярное выражение в формуле REGEXMATCH. Это обеспечит гибкость при добавлении новых критериев.

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