Скажем, у меня есть электронная таблица с 18 строками, и я могу жестко закодировать диапазон последних 7 значений в столбце D и усреднить их значения следующим образом:
=AVERAGE(D12:D18)
Как же я могу сделать то же самое без их жесткого кодирования, чтобы это работало, когда я добавляю больше строк?
Если вы хотите рассчитать среднее значение или сумму, скрипт не нужен. Вы также можете сделать это с помощью фильтра массива. Следующая формула вычисляет среднее значение по последним 7 строкам в столбце A:
=AVERAGE(FILTER(A:A;ARRAYFORMULA(ROW(A:A)>COUNT(A:A)-7+1)))
Это предполагает, что данные начинаются с строки 1. В противном случае вам придется изменить последнюю константу в формуле на номер строки, с которой начинаются данные.
Оказывается, вы можете использовать свой собственный код. Грязно, но работает:
function lastValues(column, num) {
var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
var values = SpreadsheetApp.getActiveSheet().getRange(column + "1:" + column + lastRow).getValues();
for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {};
arr = [];
for(i=0; i < num+1; i++){
arr.push(values[lastRow - i]);
};
return arr;
};
Вы также можете использовать OFFSET(), как этот ответ Webapps.SO для агрегирования последних X ячеек строки.
Для противоположного — агрегирования последних X ячеек столбца — параметры OFFSET просто нужно перемещать. Вот команда, которая должна работать для вашего примера (предполагается, что ваши данные начинаются с D2 и переходят к D18):
=iferror(average(offset($D$2, max(0, count($D$2:$D18)-7), 0, 7, 1)), 0)
