Google Spreadsheet, SUM или MINUS указанной выше ячейки в столбце с использованием ARRAYFORMULA в первой строке

Я учусь использовать формулы массива и успешно делаю простые вещи, такие как добавление двух столбцов в третий столбец. Например, я могу поместить =arrayformula(B:B+C:C) в D1, и он добавит B и C для каждой строки.

Но теперь у меня есть ситуация, когда я хочу вычесть два числа в одном столбце. Я хочу взять значение этого столбца в текущей строке и вычесть из него значение предыдущей строки. Без формул массива это просто: в O7 я помещаю =N7-N6 и копирую это, так что O8 получает =N8-N7 и т. д. Но это требует копирования каждый раз - могу ли я сделать то же самое с формулой массива?

В принципе, могу ли я сделать что-то вроде =arrayformula(B:B+(B-1):(B-1)) ?

Контекст: столбец N — ежемесячный баланс счета. Я хотел бы рассчитать, насколько этот баланс менялся каждый месяц. Итак, для строки 7 =N7-N6 дает мне эту разницу. Но я меняю всю электронную таблицу на формулы массива, чтобы перестать вставлять все формулы, и я застрял на этом, так как он сравнивает один и тот же столбец.

Я пытаюсь поместить все в строку 1, чтобы мои значения и расчеты могли начинаться в строке 2. Например, вот одна из моих формул в строке 1:

arrayformula(if(row(A:A)=1,"Total gross income",if(LEN(B:B),B:B+C:C,"")))

К сожалению, в столбце O (тот, о котором я спрашивал изначально), если я сделаю это:

=arrayformula(if(row(A:A)=1,"Amount saved this month",if(row(A:A)>1,if(LEN(N:N),N2:N-N:N,""))))

Или это:

=arrayformula(if(row(A:A)=1,"Amount saved this month",if(row(A:A)>1,if(LEN(N:N),offset(N:N,1,0)-N:N,""))))

Каждая строка отключена на 1 - результат, который должен быть в строке 3, попадает в строку 2 и т. д. И если я сделаю это:

=arrayformula(if(row(A:A)=1,"Amount saved this month",if(row(A:A)>1,if(LEN(N:N),N:N-offset(N:N,-1,0),""))))

Затем это дает мне ошибку, потому что функция смещения пытается оценить что-то вне диапазона (возможно, она начинается с N1 и пытается получить значение на 1 строку выше N1?)

Любые советы о том, как справиться с этой ошибкой вне диапазона?


google-sheets excel spreadsheet google-spreadsheet-api
person Community    schedule 19.04.2017    source источник
comment
Если основная проблема связана со смещением полного столбца, то есть N:N, вы всегда можете поставить вместо него что-то вроде N2:N999999, этого достаточно?   -  person A.S.H    schedule 20.04.2017

Ответы (1)


arrow_upward
3
arrow_downward

Я думаю, что ошибка связана с диапазоном смещения N:N, который начинается с N1, и вы пытаетесь сдвинуть его на -1 или на одну ячейку вверх, что выводит формулу за пределы листа.

Вместо этого попробуйте эту формулу:

=arrayformula( {"Amount saved this month"; if(LEN(N2:N),N2:N-offset(N2:N,-1,0),"")})

Он использует {} для создания массива. Смотрите больше информации:

https://support.google.com/docs/answer/6208276?hl=ru

Бонус. Сейчас нет причин проверять номер строки.

person Max Makhrov    schedule 21.04.2017