Can you apply a builtin function that takes a range (e.g. AVERAGE()), and have it apply to a multiple ranges?
I'm currently finishing up a pretty basic data-analysis unit with a small Excel segment and I had a past assignment that involved analysing some housing data.
我目前正在完成一个非常基本的数据分析单元,其中包含一个小的Excel段,我有一个过去的任务,涉及分析一些住房数据。
One task involved averaging and rounding the residential housing prices of a few cities for each time period.
其中一项任务涉及对每个时期的几个城市的住宅价格进行平均和舍入。
The target data is in the worksheet Residential_Houses, formatted such that cities are columns and time periods are rows spanning: C7:J30. With the goal to have the averages of each row in the worksheet Analysis spanning C7:C30.
目标数据位于工作表Residential_Houses中,格式化为城市为列,时间段为跨越行:C7:J30。目标是使工作表分析中每行的平均值跨越C7:C30。
My simple solution was to write the formula:
我的简单解决方案是编写公式:
=ROUND(AVERAGE(Residential_Houses!C7:J7), 2)
Into Analysis!C7 and then used Excel's "AutoFill" feature to generate the formulae for Analysis!C8:C30. This worked fine, and the results were correct but I lost marks for not explicitly using cell array formula.
进入Analysis!C7,然后使用Excel的“自动填充”功能生成Analysis的公式!C8:C30。这工作正常,结果是正确的,但我没有明确使用单元格数组公式丢失标记。
Instead the sample answer I was given was this monolithic line:
相反,我给出的样本答案是这个单片线:
{=ROUND((Residential_Property!C7:C30+Residential_Property!D7:D30+Residential_Property!E7:E30+Residential_Property!F7:F30+Residential_Property!G7:G30+Residential_Property!H7:H30+Residential_Property!I7:I30+Residential_Property!J7:J30)/8,2)}
It seems clear that the intent of this code is to take the average of columns C-J for each row 7-30, but it avoids using the AVERAGE() builtin. This works, but it's cumbersome, and wouldn't scale if there were more than a few columns in this test data.
很明显,这段代码的意图是取每行7-30的C-J列的平均值,但它避免使用内置的AVERAGE()。这可行,但它很麻烦,如果此测试数据中有多列,则无法扩展。
The crux of my question here is how can you combine functions that already operate on ranges, with cell array formula to process multiple ranges with a single formula?
这里我的问题的关键是如何将已经在范围上运行的函数与单元格数组公式结合起来,用单个公式处理多个范围?
My intuition tells me it might be something like this:
我的直觉告诉我它可能是这样的:
{=AVERAGE((C7:J7):(C30:J30))}
Or something involving indirect addressing, but I can't seem to find any detailed information on the topic. All of the array formula resources I've found so far have been limited to basic arithmetic and single dimension ranges. Could someone point me in the right direction here, or is this just not possible with array formula?
或涉及间接寻址的事情,但我似乎无法找到有关该主题的任何详细信息。到目前为止,我发现的所有数组公式资源都限于基本算术和单维范围。有人能指出我在正确的方向,或者这是不可能的数组公式?
EDIT: For anyone who stumbles upon this in the future. I chose Barry's solution because it helped me understand how to generalise this concept (replacing the range with a function that can slice a range). But as Scott notes, OFFSET() is volatile; so INDEX() is a better way to do this if you're working with large datasets.
编辑:对于任何在将来偶然发现这一点的人。我选择了Barry的解决方案,因为它帮助我理解了如何概括这个概念(用可以切割范围的函数替换范围)。但正如Scott所说,OFFSET()是不稳定的;因此,如果您正在使用大型数据集,则INDEX()是更好的方法。
2 个解决方案
#1
3
Similar to Scott's suggestion, SUBTOTAL can process an array of ranges created by an OFFSET function, so with the first argument of SUBTOTAL as 1 (for AVERAGE) this formula will give an array of rounded averages for each row
与Scott的建议类似,SUBTOTAL可以处理由OFFSET函数创建的范围数组,因此将SUBTOTAL的第一个参数设置为1(对于AVERAGE),此公式将为每行提供一个舍入平均值数组
=ROUND(SUBTOTAL(1,OFFSET(C7:J30,ROW(C7:J30)-ROW(C7),0,1)),2)
entered in a vertical range and confirmed with CTRL+SHIFT+ENTER
在垂直范围内输入并使用CTRL + SHIFT + ENTER确认
or you can use the same OFFSET inside an AVERAGE function like this:
或者您可以在AVERAGE函数中使用相同的OFFSET,如下所示:
=ROUND(AVERAGE(OFFSET(C7:J30,ROW(C7:J30)-ROW(C7),0,1)),2)
#2
3
Here is a formula that can be entered as one array formula and get results. It can also be entered normally and copied down.
这是一个公式,可以作为一个数组公式输入并获得结果。它也可以正常输入并向下复制。
=ROUND(AVERAGE(INDEX($C$7:$J$30,ROW()-MIN(ROW($C$7:$J$30))+1,0)),2)
#1
3
Similar to Scott's suggestion, SUBTOTAL can process an array of ranges created by an OFFSET function, so with the first argument of SUBTOTAL as 1 (for AVERAGE) this formula will give an array of rounded averages for each row
与Scott的建议类似,SUBTOTAL可以处理由OFFSET函数创建的范围数组,因此将SUBTOTAL的第一个参数设置为1(对于AVERAGE),此公式将为每行提供一个舍入平均值数组
=ROUND(SUBTOTAL(1,OFFSET(C7:J30,ROW(C7:J30)-ROW(C7),0,1)),2)
entered in a vertical range and confirmed with CTRL+SHIFT+ENTER
在垂直范围内输入并使用CTRL + SHIFT + ENTER确认
or you can use the same OFFSET inside an AVERAGE function like this:
或者您可以在AVERAGE函数中使用相同的OFFSET,如下所示:
=ROUND(AVERAGE(OFFSET(C7:J30,ROW(C7:J30)-ROW(C7),0,1)),2)
#2
3
Here is a formula that can be entered as one array formula and get results. It can also be entered normally and copied down.
这是一个公式,可以作为一个数组公式输入并获得结果。它也可以正常输入并向下复制。
=ROUND(AVERAGE(INDEX($C$7:$J$30,ROW()-MIN(ROW($C$7:$J$30))+1,0)),2)