vba 在列中重复特定的 excel 公式模式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28065252/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
repeat a specific excel formula pattern down column
提问by Patrick Handcock
I'm not an excel whiz but really stuck on this one and hoping a guru out there will be able to help me out as it's really important for some research I am doing - please!! I think it will be quite a simple fix (see attached example excel sheet for context below). If you are even able to modify the formula in question and re-attach that would be awesome too!!
我不是一个优秀的天才，但真的坚持这个，希望那里的大师能够帮助我，因为这对我正在做的一些研究来说真的很重要——拜托！！我认为这将是一个非常简单的修复（参见下面的上下文示例 Excel 表）。如果您甚至能够修改有问题的公式并重新附加，那也太棒了！！
There are 2 datasets in sheet 1 side-by-side. The data on left (rows A to K) displays data in 10 sec time epochs, the data on right (rows N to X) in 1 min time epochs. I would like to be able to drag the formula in P2 all the way down the column P based on data in column C (as per colour coding in red and blue).
表 1 中有 2 个并排的数据集。左边的数据（行 A 到 K）显示 10 秒时间的数据，右边的数据（行 N 到 X）显示 1 分钟的时间。我希望能够根据 C 列中的数据（根据红色和蓝色的颜色编码）将 P2 中的公式一直拖到 P 列。
You'll note that the P2 formula is taking a SUM of C2-C7 and P3 is taking sum of C8-C13. I would like to be able to continue this pattern down the column, perhaps with a better drag-down formula (or more efficiently – as there is loads of data!) if possible. Essentially I want each single data row on the right to move onto the next block of 6 rows from data on the left.
您会注意到 P2 公式计算的是 C2-C7 的总和，而 P3 计算的是 C8-C13 的总和。如果可能的话，我希望能够在列中继续这种模式，也许可以使用更好的下拉公式（或更有效 - 因为有大量数据！）。本质上，我希望右侧的每个数据行都从左侧的数据移动到下一个 6 行块。
I hope I explained that well enough. Really hoping someone can help! Really important to me!
我希望我解释得足够好。真的希望有人能帮忙！对我来说真的很重要！
Patrick
帕特里克
See attached excel example - thanks so much!! I will be ever grateful!
请参阅随附的 excel 示例 - 非常感谢！！我将永远感激！
https://www.dropbox.com/s/72r7ty9v15vzyyv/drag%20formula%20quick%20way%20-%20help.xlsx?dl=0
https://www.dropbox.com/s/72r7ty9v15vzyyv/drag%20formula%20quick%20way%20-%20help.xlsx?dl=0
回答by Rory
Here's one way. In P2 enter:
这是一种方法。在 P2 中输入：
=SUM(OFFSET(INDEX(C:C,(ROWS($A$1:$A1)-1)*6+2),0,0,6))
=SUM(OFFSET(INDEX(C:C,(ROWS($A$1:$A1)-1)*6+2),0,0,6))
and then copy that across and down as required. This is volatile due to the use of OFFSET which shouldn't be a problem with a small data set like this, but if your real data is bigger and you have more formulas, you may prefer:
然后根据需要上下复制。由于使用了 OFFSET 这不应该是像这样的小数据集的问题，这是不稳定的，但是如果您的真实数据更大并且您有更多公式，您可能更喜欢：
=SUM(INDEX(C:C,(ROWS($A$1:$A1)-1)*6+2):INDEX(C:C,(ROWS($A$1:$A1)-1)*6+7))
=SUM(INDEX(C:C,(ROWS($A$1:$A1)-1)*6+2):INDEX(C:C,(ROWS($A$1:$A1)-1)*6+7))
which is only semi-volatile. (i.e. it will recalculate whenever the workbook is opened, even if its inputs haven't changed)
这只是半挥发性的。（即它会在打开工作簿时重新计算，即使它的输入没有改变）
