将公式向下拖动，行更改除了一个以外的其他间隔 [英] Drag a formula down with Row changes by another interval other than one
问题描述
我一直在努力想出几个小时。我有这个公式：
I have been trying to figure this out for hours now. I have this formula:
=PROMEDIO.SI.CONJUNTO(Flankers!C15:C54;Flankers!D15:D54;1)
我已经手动完成了这一个。我在我的数据中有我的数据，现在我正在计算我的反应时间和第二张表的准确度分数。每个参与者的数据是相同的（每个参与者93行），我想要做的是拖动这个公式来自动填充每个参与者（我的29个参与者在一列）。当然，每个参考只改变1个数字。例如，我有C2中的公式，所以当我把它拖到C3时，公式更改为
I have done this one manually. I have my data in the Flankers sheet and now I'm calculating the means for my reaction times and my accuracy scores on a second sheet. The data for each participant is the same (93 rows per participant) and what I am trying to do is to drag this formula down to fill in each participant automatically (I have my 29 participants in a column). Of course, each reference changes only by 1 number. For example, I have the formula in C2, so when I drag it down to C3, the formula changes to
=AVERAGEIFS(Flankers!C16:C55;Flankers!D16:D55;1)
我需要所有行上升93并保持范围相同（40个单元格），以便C3的单元格应为= AVERAGEIFS（Flankers！C108：C147; Flankers！D108：D147; 1），然后C4的单元格应为
I need all of the rows to go up by 93 and keep the range the same (40 cells), so that C3's cell should be =AVERAGEIFS(Flankers!C108:C147;Flankers!D108:D147;1) and then C4's cell should be
=AVERAGEIFS(Flankers!C201:C240;Flankers!D201:D240;1)
等等。手动这是非常乏味（总共2698行）。有人可以清楚这一点，并解释一下我可以修改它以供将来分析吗？
, etc. Manually this is very tedious (2698 rows in total). Can someone shed light on this and explain it to where I could modify it for future analyses?
推荐答案
几乎将此标记为重复的单元格跳过10行，但实际上它是相反的。但是，解决方案是一样的。使用 INDEX功能有一点数学来实现交错。
Almost marked this as a duplicate of Duplicated cells skip 10 rows but in fact it is the reverse of it. However, the solution is the same. Use the INDEX function with a little maths to achieve the stagger.
最简单的方法是使用 OFFSET函数，但是该函数被认为是volatile¹，并且随着工作簿中的任何内容发生变化，它将重新计算。大量的这些将导致计算滞后，每当某些东西输入任何单元格。
The easiest way to do this is with the OFFSET function but that function is considered volatile¹ and will recalulate whenever anything in the workbook changes. Large numbers of these will result in calculation lag everytime something is typed into any cell.
使用volatile¹ OFFSET ，
With the volatile¹ OFFSET,
=AVERAGEIFS(OFFSET(Flankers!$C$15, (ROW(1:1)-1)*93, 0, 40, 1),
OFFSET(Flankers!$C$15, (ROW(1:1)-1)*93, 1, 40, 1), 1)
使用非易失性 INDEX ，
=AVERAGEIFS(INDEX(Flankers!C:C, 15+(ROW(1:1)-1)*93):INDEX(Flankers!C:C, 54+(ROW(1:1)-1)*93),
INDEX(Flankers!D:D, 15+(ROW(1:1)-1)*93):INDEX(Flankers!D:D, 54+(ROW(1:1)-1)*93), 1)
第二个公式可能会更复杂，但它真正做的是为 AVERAGEIFS功能。 INDEX引用整个列，一些基本的数学做其余的。
The second formula may look more complicated but all it really does is provide a starting cell and and ending cell for each range in the AVERAGEIFS function. INDEX references the entire column and some basic maths do the rest.
在ES-ES公式语言（使用分号列表分隔符）as，
In ES-ES formula language (with semicolon list separators) as,
=PROMEDIO.SI.CONJUNTO(DESREF(Flankers!$C$15; (FILA(1:1)-1)*93; 0; 40; 1);
DESREF(Flankers!$C$15; (FILA(1:1)-1)*93; 1; 40; 1); 1)
=PROMEDIO.SI.CONJUNTO(INDICE(Flankers!C:C; 15+(FILA(1:1)-1)*93):INDICE(Flankers!C:C; 54+(FILA(1:1)-1)*93);
INDICE(Flankers!D:D; 15+(FILA(1:1)-1)*93):INDICE(Flankers!D:D; 54+(FILA(1:1)-1)*93); 1)
¹易失性函数每次重新计算整个工作簿中的任何内容都会发生变化，而不仅仅是影响其结果的事情发生变化。 volatile 函数的示例是 INDIRECT ， OFFSET ，今天，现在， RAND 和 RANDBETWEEN 。 CELL 和 INFO 工作表函数也会使它们变得不稳定。
¹ Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes. Examples of volatile functions are INDIRECT, OFFSET, TODAY, NOW, RAND and RANDBETWEEN. Some sub-functions of the CELL and INFO worksheet functions will make them volatile as well.
这篇关于将公式向下拖动，行更改除了一个以外的其他间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
