如何平均Excel中的第n列? [英] How to average every nth column in Excel?

问题描述

我已经在Google上四处寻找该问题的答案，但是大多数网站仅显示如何对第n行进行平均或求和(尽管我想它们之间是相关的).我已经尝试过使用可以为列找到的各种公式，但是并没有成功.(不可否认，我的Excel技能不是很熟练.)

I've looked around on Google for answer to this question, but most websites only show how to average or sum every nth row (although I imagine they're related). I've tried using various formulas I could find for columns, but I haven't been successful. (Admittedly, my Excel skills aren't very sophisticated.)

我想获取数据集中每第6列的平均值.有很多列需​​要求平均值，因此，如果有一些公式可以帮助实现这一点，那就太好了！

I want to get the average of every 6th column in my dataset. There are a ton of columns that need averaging, so if there's some formula that could aid with this, that would be great!

作为我正在使用的数据类型的示例，请参见下文.基本上，我有很多列，每列都有很多行(119)，并且它们都包含空值.我想求平均值，例如Q1(= 10.68)和Q7(= 16)，依此类推.

As an example of the kind of data I'm working with, see below. Basically, I have many columns, each with many rows (119) and all of them contain empty values. I want to average, for example, Q1 (=10.68) and Q7 (=16), and so on.

谢谢！

推荐答案

类似于此数组公式的内容:

Something similar to this array formula:

=AVERAGE(IF((MOD(COLUMN($A$2:$R$12)-1,6)=0)*($A$2:$R$12<>""),$A$2:$R$12))

作为数组公式，退出编辑模式时，需要使用Ctrl-Shift-Enter而不是Enter进行确认.如果操作正确，则Excel会将 {} 放在公式周围.

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when leaving edit mode. If done correctly then Excel will put {} around the formula.

您可能需要调整数学以获取正确的列:

You may need to adjust the math to get the correct column:

这篇关于如何平均Excel中的第n列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！