Make Google Spreadsheet Formula Repeat Infinitely

问题:

Okay so I have a Google Form that dumps info into a spreadsheet. On each line I need to have a simple calculation done. The problem is I can't figure out how to get it to repeat a formula on every new line as new lines are added.

Yes I know how to use the fill handle to copy formulas down and what not, but I want it to automatically add the formula instead of me manually copying it.

For example this is being used to track time so there is a cell for In Time and a cell for Out Time on each row. I want to have a column called Time Spent that will subtract their in time from the out time to determine how much time they spent. But since there are an infinite number of rows it is not practical for me to go in and copy the formula.

If anybody has any ideas I would really appreciate it. I have been looking around for ages and all I can ever find is people saying to use the fill handle to copy formulas down manually which is not what I want.

回答1:

Let's say the In Time cells are in Column A, and Out Time cells are in Column B, and you want Time Spent to be in Column C. Put this formula in cell C2 (assuming A1, B1, and C1 contain headers, not data):

=ARRAYFORMULA(B2:B - A2:A)

The ARRAYFORMULA function instructs the spreadsheet to iterate the contained formula over the ranges given, and a reference without a final number like B2:B refers to a range that contains all the remaining rows in the spreadsheet.

回答2:

If for some reason you find arrayformula tricky to use in your situation, an alternative option is to create a mirror of the table on another sheet using a query that has the formulas written into it.

E.g. if you have numbers in columns A and B and a formula to add them together:

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

The benefit to this approach is that you'll often want to do aggregation and summarization on your form data, and you can kill two birds with one stone by doing that in the same query that you use to apply the formulas.

I'll mention one other trick that has been useful in the past when Google's query syntax has a gap in functionality. You can actually apply an array formula to the data before passing it in to the query function, like this following example, which has output equivalent to the previous query.

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

Note how the columns are no longer referred to by letter, but by Col1, Col2, etc.