将公式向下拖动，并按除一个以外的另一个间隔更改行 [英] 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)

我手动完成了这个.我在 Flankers 工作表中有我的数据，现在我正在第二张工作表上计算我的反应时间和准确度得分的平均值.每个参与者的数据是相同的(每个参与者 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 函数 但该函数被认为是不稳定的¹，并且会在工作簿中的任何内容发生更改时重新计算.每次在任何单元格中输入内容时，大量这些将导致计算延迟.

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)

使用非易失性 索引,

=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 公式语言中(使用分号列表分隔符)，

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 函数会在整个工作簿中的任何内容发生变化时重新计算，而不仅仅是当影响其结果的某些内容发生变化时.volatile 函数的示例是 间接，偏移, 今天，现在, 兰德 和 RANDBETWEEN.CELL<的一些子功能/a> 和 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屋！