Заставить формулу таблицы электронных таблиц Google бесконечно

Итак, у меня есть форма Google, которая выгружает информацию в электронную таблицу. На каждой строке мне нужно сделать простой расчет. Проблема в том, что я не могу понять, как заставить ее повторять формулу на каждой новой строке по мере добавления новых строк.

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

Например, это используется для отслеживания времени, поэтому для каждой ячейки используется ячейка для In Time и ячейка для Out Time. Я хочу иметь столбец с именем Time Spent, который будет вычитать их вовремя из времени, чтобы определить, сколько времени они потратили. Но поскольку существует бесконечное количество строк, для меня нецелесообразно входить и копировать формулу.

Если у кого-нибудь есть идеи, я бы очень признателен. Я искал вокруг целую вечность, и все, что я когда-либо нахожу, это люди, которые говорят, чтобы использовать дескриптор заполнения для копирования формул вручную, что не то, что я хочу.

Ответ 1

Скажем, что ячейки In Time находятся в столбце A, а ячейки Out Time находятся в столбце B, и вы хотите потраченное время быть в столбце C. Поместите эту формулу в ячейку C2 (предполагая, что A1, B1 и C1 содержат заголовки, а не данные):

=ARRAYFORMULA(B2:B - A2:A)

Функция ARRAYFORMULA указывает электронной таблице итерировать содержащуюся формулу в указанных диапазонах, а ссылка без конечного числа, такого как B2:B, относится к диапазону, содержащему все остальные строки в электронной таблице.

Ответ 2

Если по какой-то причине вы обнаруживаете, что arrayformula сложно использовать в вашей ситуации, альтернативным вариантом является создание зеркала таблицы на другом листе с использованием запроса, в котором записаны формулы.

например. если у вас есть номера в столбцах A и B и формула для их добавления:

=query(Data!A:B, "select A, B, A + B")

Преимущество такого подхода состоит в том, что вы часто захотите сделать агрегацию и обобщение данных формы, и вы можете убить двух зайцев одним выстрелом, выполнив это в том же запросе, который вы используете для применения формул.

Я упомянул еще один трюк, который был полезен в прошлом, когда синтаксис запросов Google имеет пробел в функциональности. Фактически вы можете применить формулу массива к данным, прежде чем передавать ее в функцию запроса, как этот следующий пример, который имеет выход, эквивалентный предыдущему запросу.

=query({Data!A:B, arrayformula(Data!A:A+Data!B:B)},
  "select Col1, Col2, Col3")

Обратите внимание на то, что столбцы больше не упоминаются буквой, а Col1, Col2 и т.д.