Как можно в Google Таблицах считать сумму строки при универсальном диапазоне — то есть суммировать построчно?

Question

[Дмитрий Дев]

Есть google Таблицы. В ней две таблицы: "отчет по клиентам" и "Чистая прибыль на сотрудника".
Суть в том, что в таблицу "отчет по клиентам" записываются приходы и оплаты принципалу, подсчитываются агентские, налог 6% и выводится "чистый профит". В таблице "Чистая прибыль на сотрудника" в каждой строке идет распределение денег между менеджерами. Первые два идут как руководители и делят прибыль пополам, если другие менеджеры не участвовали в продаже. В колонку P:P заносится имя менеджера, участвующего в продаже, и тогда автоматически во второй таблице заносится его процент вознаграждения в столбец под его именем.
По сути надо сделать, чтобы из значения ячейки АE5 вычиталась сумма значений AG5, AH5, AI5, AJ5, AK5/
Но чтобы один раз создать формулы и больше не касаться. Чтобы если в первую таблицу добавляешь ячеек в самый верх например, а вторую оставляешь нетронутой, то не получилось, чтобы первая срока второй таблицы ссылалась на вторую строку первой после добавления ячеек.
То есть не подходят варианты
Вариант AG:AG - СУММ(AG5:AK5)
Также не подходит вариант AG:AG - СУММ(AG:AG;AH:AH;AI:AI;AJ:AJ;AK:AK), потому что если появляется новая колонка, то в каждой ячейке придется добавлять в формулу новую колонку.
А вариант AG:AG - СУММ(AG:AK) вовсе начинает считать все значения в диапазоне, а не построчно.

Answer 1

[Григорий Боев]

Вообще при добавлении строки таблица сама подстраивает как надо. Но если надо зафиксировать, можно использовать функцию ДВССЫЛ. Например так:
=A1-СУММ(ДВССЫЛ("c1:1";ИСТИНА))
"c1:1" значит "строка 1 от столбца C вправо и до конца"

Comments

[Дмитрий Дев]

Григорий Боев Не подходит. Опять же как раз из-за того что автоматически подстраивается. Вот ссылка на таблицу https://docs.google.com/spreadsheets/d/1ANpfL36WQU...
Суть в том, что в таблицу "отчет по клиентам" записываются приходы и оплаты принципалу, подсчитываются агентские, налог 6% и выводится "чистый профит". В таблице "Чистая прибыль на сотрудника" в каждой строке идет распределение денег между менеджерами. Первые два идут как руководители и делят прибыль пополам, если другие менеджеры не участвовали в продаже. В колонку P:P заносится имя менеджера, участвующего в продаже, и тогда автоматически во второй таблице заносится его процент вознаграждения в столбец под его именем.
Если я Вашим форматом занесу, то когда в первую таблицу я добавлю строчку в самый верх, а вторую оставлю нетронутой, то получится что во второй таблице в первой строке формула будет ссылаться на вторую строку первой таблице и логика таблицы уже работает неправильно.
Сейчас там сделана попытка ссылок на масcив, но в таком случае получается, что он сумму всех имеющихся значений считает, а надо только построчно.

[Григорий Боев]

Несколько раз пытался понять логику работы и суть проблемы, но так и не сообразил. Может разнести "таблицы" на разные листы?

СУММ(ДВССЫЛ("c1:1";ИСТИНА)) - так диапазон железно прибит и не меняется при вставке строк и распространении
СУММ(c1:1) - так будет меняться при распространении и вставке строк
СУММ($c$1:$1) - так не меняется при распространении

[Дмитрий Дев]

Григорий Боев, не думаю, что получится, а с этой таблице я уже давно работаю, поэтому не хотелось бы и в ней получилось все очень наглядно. Вот взять колонку J:J. Там простая формула считает значения, только находящиеся на той же строке. Также и надо чтобы получилось во второй таблице, чтобы в колонке АЕ:АЕ вычитались значения только находящиеся на той же строке от колонки AG до AK.

А логика такая:

В колонки 1 и 2 заносятся значения, чтобы в итоге посчиталось значение в колонке 3. В колонку 4 заносится имя менеджера участвующего в продаже. Если туда ничего не занести, то в колонке AE посчитается значение из 3 колонки на той же строке и поделится на два, а в колонке AG на той же строке останется 0. Если занести в 4 колонку "Менеджер3", то колонка AG считает 17% от колонки 3, а колонка AЕ считается как (Колонка3-AG[на той же строке])/2.
Это все что нужно знать по логике, чтобы понять суть проблемы. Остальное это если там будут возвраты или если в продаже участвовали больше одного менеджера.

[Григорий Боев]

dikey58, AG[на той же строке] можно реализовать так:
=ДВССЫЛ(СЦЕПИТЬ("AG";СТРОКА());ИСТИНА)

[Дмитрий Дев]

Григорий Боев, нужно реализовать не AG[на той же строке], а диапазон от AG[на той же строке] по AK[на той же строке], но смысл понял, и, в принципе, должно работать, но в виде =СУММ(СЦЕПИТЬ("AG";СТРОКА();":AK";СТРОКА())), но почему-то в этом виде сумму считает не верно.

[Григорий Боев]

Диапазон от AG[на той же строке] по AK[на той же строке]

=СУММ(
	ДВССЫЛ(
		"AG"&СТРОКА()&":AK"&СТРОКА();ИСТИНА
	)
)

[Дмитрий Дев]

Григорий Боев, Спасибо. Такое решение помогло. Наконец-то с этим больше не придется мучаться.

Answer 2

[Alexander Ivanov]

Невероятно сложное описание.

В общей сложности для колонок C2:F18 верно

=ARRAYFORMULA(SUMIF(IF(COLUMN(C1:F1);ROW(A2:A18));ROW(A2:A18);C2:F18))

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

Предыдущее решение в новой формулировке

=MMULT(C2:F18;TRANSPOSE(ARRAYFORMULA(COLUMN(C1:F1)^0)))

Или далее для каждой третьей колонки из диапазона D3:U верно

=ARRAYFORMULA(IF(ROW(D3:D11);MMULT(N(D3:U);TRANSPOSE(N(MOD(COLUMN(D3:U);3)={1;0})));))

Comments

[Дмитрий Дев]

Пока смотрю, но из вкладки "Суммы и средние по строкам" получается, что формула работает при указании конкретных ячеек, то есть A2:A18 или C1:F1. А нужны, чтобы "Сумма, v1" считала сумму значений в колонках "число 1 - число 4" находящееся в той же строке, что и формула, не используя нумерацию строк - то есть например C:F