每10行平均一次 [英] excel averaging every 10 rows
问题描述
1
2
3
4
2
3
4
5
现在我需要平均前十行,然后是下一个十行,依此类推,对于所有9000行。我可以做到这一点,但是后来我把所有这些行平均在中间,我不需要,我不能删除那些行。必须有一个简单的方法来做到这一点吗?
感谢任何帮助!
假设您的数据从 A1
开始。在 B1
中尝试一个:
= AVERAGE(INDEX(A:A,1 + 10 *(ROW() - ROW($ B $ 1))):INDEX(A:A,10 *(ROW() - ROW($ B $ 1)+1)))
并将其拖下来
- 在
B1
B2 $>
= AVERAGE(A1:A10)
- c $ c>
=
=
B3 $ c> = AVERAGE(A11:A20)
< $ c>它将是
= AVERAGE(A21:A30)
和
一般情况
如果您的数据从 An
(其中 n
是 2
, 3
, 4
,...),使用这一个:
= AVERAGE(INDEX(A:A组,n + 10 *(ROW() - ROW($ B $ 1))):INDEX(A:A,N-1 + 10 *(ROW() - ROW($ B $ 1)+1))
您应该将 n
更改为 2
, 3
, 4
...
I have a big data set which has about 9000 rows. I have a few variables for every year from 1960 onwards, and I need to average them in ten year bins. So I have something like:
1
2
3
4
2
3
4
5
Now I need to average the first ten rows, then the next ten, and so on, for all 9000-odd rows. I can do that, but then I get all these rows averaged in the middle which I don't need, and I can't go about deleting those many rows. There has to be an easy way to do this, surely?
Would appreciate any help!
Suppose your data starts from A1
. Try this one in B1
:
=AVERAGE(INDEX(A:A,1+10*(ROW()-ROW($B$1))):INDEX(A:A,10*(ROW()-ROW($B$1)+1)))
and drag it down.
- in
B1
it would be=AVERAGE(A1:A10)
- in
B2
it would be=AVERAGE(A11:A20)
- in
B3
it would be=AVERAGE(A21:A30)
and so on.
General case
If your data starts from An
(where n
is 2
,3
,4
,...), use this one:
=AVERAGE(INDEX(A:A,n+10*(ROW()-ROW($B$1))):INDEX(A:A,n-1+10*(ROW()-ROW($B$1)+1))
where you should change n
to 2
,3
,4
,...
这篇关于每10行平均一次的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!