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