Вопрос или проблема
Если в строке из другого столбца есть слово (например, Сумма), как сделать так, чтобы другой столбец суммировал все числа ниже последней Суммы и до предыдущей строки?
Мне нужна формула, которую нужно вставить в O5, O8 и O13. Если возможно, O2 и ниже должны просто выполнять это через формулы таблицы, так как N1:O – это таблица, называемая Table1.
В конечном итоге формула должна выглядеть как =if(exact(N1, "Sum"), ???, RANDBETWEEN(1, 1000)) – RANDBETWEEN просто пример, это будет другая формула для определения фактических чисел.
| N | O | |
|---|---|---|
| 1 | Сумма | Числа |
| 2 | 20 | |
| 3 | 35 | |
| 4 | 10 | |
| 5 | Сумма | =? должно быть 65 |
| 6 | 230 | |
| 7 | 70 | |
| 8 | Сумма | =? должно быть 300 |
| 9 | 1 | |
| 10 | 2 | |
| 11 | 3 | |
| 12 | 4 | |
| 13 | Сумма | =? должно быть 10 |
Попробуйте эту формулу (в AI2):
=IF(EXACT(AH2,"Сумма"),SUM(AI$1:AI1)-2*SUMIF(AH$1:AH1,"Сумма",AI$1:AI1),RANDBETWEEN(1,100))
Попробуйте следующее:
=SUM(O$2:O4)-SUMIF(N$2:N4,"Сумма",O$2:O4)*2
Это, по сути, суммирует весь столбец, а затем вычитает ранее рассчитанные суммы
Пример:
Если вы хотите, чтобы суммы были в столбце P, как на скриншоте вашего комментария, используйте следующее:
=IF(N2="Сумма",SUM(O1:O$2)-SUMIF(N1:N$2,"Сумма",P1:P$2),"")
пример:
Ответ или решение
Чтобы посчитать суммы значений в столбце на основе определённого слова в другом столбце в Excel, существует несколько подходов. Рассмотрим детально, как это можно реализовать, используя вашу таблицу в качестве примера.
Задача
Нам необходимо создать формулы, которые будут суммировать все числа в одном столбце (колонка O) до последнего вхождения слова "Sum" в другом столбце (колонка N). Формулы нужно разместить в ячейках O5, O8 и O13. Кроме того, требуется подключить формулы к таблице, чтобы формулы применялись автоматически при добавлении новых данных.
Шаг 1: Понимание структуры данных
Мы имеем таблицу, где:
- Столбец N содержит метки, которые включают слово "Sum".
- Столбец O содержит числа, которые нужно суммировать.
Шаг 2: Формула для суммирования
В ячейках O5, O8 и O13 необходимо использовать формулу для вычисления суммы. Формула будет выглядеть следующим образом:
=SUM(OFFSET(O5, -COUNTIF(N$2:N5, "Sum"), 0, COUNTIF(N$2:N5, "Sum"), 1))Описание формулы:
OFFSET(O5, -COUNTIF(N$2:N5, "Sum"), 0, COUNTIF(N$2:N5, "Sum"), 1):- Эта часть формулы создаёт диапазон для суммирования. Она перемещает начало отсчёта от текущей ячейки о количество строк, которое равно количеству слов "Sum" до текущей строки.
SUM(...): Считает сумму значений в созданном диапазоне.
Эта формула будет динамически изменяться в зависимости от того, где она расположена, и будет правильно находить диапазон для суммирования.
Шаг 3: Упрощение с использованием таблицы
Для использования таблицы (например, если ваша таблица называется Table1), можно использовать следующий подход:
В ячейках O2 и ниже, можно использовать формулу:
=IF([@N]="Sum", SUMIFS(Table1[Numbers], Table1[N], "<>Sum", Table1[N], "<=" &[@N]), RANDBETWEEN(1, 1000))Описание формулы:
[@N]: Ссылается на текущее значение в столбце N для соответствующей строки.SUMIFS(...): Суммирует значения из столбца "Numbers" (O), которые не равны "Sum" в столбце N, до текущей строки.
Применение
- Убедитесь, что данные находятся в формате таблицы, для удобства управления и применения формул.
- Введите соответствующие формулы в O5, O8 и O13.
- В O2 и ниже введите предложенные формулы.
Пример конечных формул:
- Ячейка O5:
=SUM(OFFSET(O5, -COUNTIF(N$2:N5, "Sum"), 0, COUNTIF(N$2:N5, "Sum"), 1)) - Ячейка O8:
=SUM(OFFSET(O8, -COUNTIF(N$2:N8, "Sum"), 0, COUNTIF(N$2:N8, "Sum"), 1)) - Ячейка O13:
=SUM(OFFSET(O13, -COUNTIF(N$2:N13, "Sum"), 0, COUNTIF(N$2:N13, "Sum"), 1))
Заключение
Эти формулы позволят вам динамически вычислять суммы в зависимости от позиций слов "Sum" в вашем столбце N. При этом использование таблиц в Excel облегчает процесс управления формулами и их автоматическую адаптацию к изменениям в данных.