让Google电子表格公式无限重复 [英] Make Google Spreadsheet Formula Repeat Infinitely

问题描述

好的，我有一个Google表单将信息转储到电子表格中。在每一行我都需要做一个简单的计算。问题是我无法弄清楚如何让它在每一个新行上重复一个公式，因为新行被添加。

是的，我知道如何使用填充柄来复制公式，但是我希望它自动添加公式，而不是手动复制公式。 p>

例如，它被用来追踪时间，所以有一个 In Time 的单元格和 Out Time 。我希望有一个名为 <花费时间 的栏，可以从时间中扣除时间，以确定花费的时间。但是由于有无数的行，所以我进入并复制公式是不现实的。

如果有人有任何想法，我会非常感激。我一直在寻找很多年，我能找到的所有人都在说使用填充手柄来手动复制公式，这不是我想要的。 方案

假设 时间 单元格位于A列， Out Time 单元格在列B中，并且您希望 <花费时间 在列C中。将此公式放入单元格C2中（假定A1，B1和C1包含标题，而不是数据）：

  = ARRAYFORMULA（B2：B  -  A2：A）
  pre> 
 
 
  ARRAYFORMULA 函数指示电子表格在给定的范围内迭代包含的公式，最终数字如 B2：B 指的是包含电子表格中所有其余行的范围。
 
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.
解决方案
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.

                        
这篇关于让Google电子表格公式无限重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！