让 Google 电子表格公式无限重复 [英] Make Google Spreadsheet Formula Repeat Infinitely
问题描述
好的，我有一个可以将信息转储到电子表格中的 Google 表单.在每一行我需要做一个简单的计算.问题是我不知道如何让它在添加新行时在每个新行上重复一个公式.
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.
例如，这用于跟踪时间，因此有一个用于 In Time 的单元格和用于 Out Time 的单元格 在每一行.我想要一个名为 Time Spent 的列，它将从外出时间中减去他们的进入时间，以确定他们花费了多少时间.但是由于行数是无限的，所以我进入并复制公式是不切实际的.
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.
推荐答案
假设 In Time 单元格在 A 列中，Out时间单元格在 B 列中，您希望 花费的时间 在 C 列中.将此公式放在单元格 C2 中(假设 A1，B1 和 C1 包含标题，而不是数据):
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)
ARRAYFORMULA 函数指示电子表格在给定的范围内迭代包含的公式，而像 B2:B 这样没有最终数字的引用指的是包含电子表格中的所有剩余行.
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屋！
