Google Sheets - Как использовать SUMIF для разделенного текста?

Содержание

Вопрос или проблема
Результат разбиения
Попробуйте это
Пример результата:
Ссылка:
Ответ или решение
Шаги по созданию формулы для суммирования забитых голов:
Пример использования формулы:
Заключение:

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

Google Sheets – Как использовать SUMIF для разделенного текста?

Допустим, у меня есть две следующие таблицы:

Команда	Забитые голы
Команда A	X
Команда B	Y

Домашняя команда	Гостевая команда	Результат
Команда A	Команда B	3-1
Команда B	Команда A	1-0
Команда A	Команда B	1-1
Команда B	Команда A	0-4

Как мне вычислить X и Y?

Мне нужна формула, чтобы заполнить колонку Забитые голы, которая:

Разделяет колонку Результат на два числа, используя разделитель “-“
Суммирует первое значение разделенной строки, ЕСЛИ домашняя команда равна текущей строке колонки Команда
Суммирует второе значение разделенной строки, ЕСЛИ гостевая команда равна текущей строке колонки Команда
Суммирует две суммы, полученные на пунктах 2 и 3

Таким образом, в этом примере X равен 3+0+1+4 = 8, а Y равен 1+1+1+0 = 3

Спасибо

Я пробовал что-то вроде
=sumif(B110:B1000;$B103;index(split(D110:D1000;"-");;1)), но это выдает ошибку

Вот один из подходов, который вы можете адаптировать соответственно:

=reduce(;C:C;lambda(a;c;a+iferror(index(split(c;"-");xmatch(E2;offset(c;;-2;;2))))))

Результат разбиения

Разбиение приведет к массиву, который нельзя обрабатывать с помощью SumIf. Я предлагаю использовать функцию MAP и Sum вместо этого.

Попробуйте это

=BYROW(UNIQUE(TOCOL(A2:B5)), LAMBDA(z, SUM(CHOOSECOLS(LET(x,HSTACK(TOCOL(A2:B5),TOCOL(BYROW(C2:C5, LAMBDA(r,IFERROR(SPLIT(r,"-"),SPLIT("0-0","-")))))), FILTER(x, CHOOSECOLS(x,1) =z)),2))))

Пример результата:

Ссылка:

MAP

HSTACK

Предполагая, что ваша исходная таблица начинается с A1, вы можете сгенерировать всю результирующую таблицу в одной формуле рядом с ней или на другой вкладке следующим образом:

=arrayformula(query(
{tocol(A2:B;;1)\tocol(split(C2:C;"-");;1)};
"select Col1,sum(Col2) 
where Col1 is not null 
group by Col1 
label Col1 'Команда', sum(Col2) 'Забитые голы'";0))

Мы используем tocol и массивные литералы, чтобы ‘развернуть’ данные команды и гола в плоскую таблицу, затем query, чтобы суммировать их по мере необходимости.

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

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

Шаги по созданию формулы для суммирования забитых голов:

Структура таблицы: Убедитесь, что у вас есть две таблицы. Первая таблица должна содержать команды и соответствующие голы, вторая таблица — результаты матчей в формате "X-Y", где X — количество голов домашней команды, а Y — количество голов гостевой команды.
Формула для подсчета забитых голов:
Мы будем использовать функцию ARRAYFORMULA, SPLIT, FLATTEN, QUERY и SUM для реализации данной задачи.
Решение с использованием одной формулы:

Впишите следующую формулу в ячейку, соответствующую столбцу "Goal scored", соседнему с первой командой:
```
=ARRAYFORMULA(QUERY(
{FLATTEN(A2:A; SPLIT(C2:C; "-") * {1; 0})};
"SELECT Col1, SUM(Col2)
WHERE Col1 IS NOT NULL
GROUP BY Col1
LABEL Col1 'Team', SUM(Col2) 'Goals scored'"; 0))
```
- Разъяснение формулы:
  - FLATTEN(A2:A; SPLIT(C2:C; "-") * {1; 0}) извлекает команды и соответствующие им головы, превращая каждую строку в пару значений (домашняя команда и количество голов).
  - SPLIT(C2:C; "-") разбивает каждую строку результатов по символу "-" и формирует массив с двумя колонками.
  - QUERY затем группирует данные по командам и суммирует забитые голы.
Ручной подсчет:
Если вы хотите разложить процесс на шаги и заполнять столбцы вручную, вы можете использовать такие формулы:

Для команды X (где вы хотите посчитать забитые голы "Team A"):
```
=SUM(FILTER(ARRAYFORMULA(IFERROR(VALUE(SPLIT(C2:C, "-"))); 0), A2:A = "Team A"))
```
Для команды Y (где вы хотите посчитать забитые голы "Team B"):
```
=SUM(FILTER(ARRAYFORMULA(IFERROR(VALUE(SPLIT(C2:C, "-"))); 0), B2:B = "Team B"))
```

Пример использования формулы:

Перейдите к ячейке, которая соответствует столбцу "Goals scored" рядом с "Team A".
Вставьте предложенную формулу.
Повторите процесс для "Team B", используя соответствующее имя команды в формуле.

Заключение:

Эта методология позволяет эффективно динамически подсчитывать голы для каждой команды на основе результативности матчей, используя возможности Google Sheets. Вы можете настроить формулы в соответствии с вашими данными и требованиями. Если у вас возникли вопросы или потребуются дополнительные разъяснения, не стесняйтесь обращаться за помощью!