excel如何得到最大年增长率对应的值？

时间：2021-09-28 来源：互联网编辑：宝哥软件园浏览：次

如下图1所示，根据工作表中提供的部分国家2010年至2013年的人口数据，在单元格A1中使用一个公式来确定该期间人口年均增长率最大的区域。

图1

配方要求：

1.不仅结果正确，而且应该使用最少的字符数。

2.必须同时包含行引用和列引用，而不仅仅是行引用或列引用(例如，3:2或A:F)。

3.名字是不允许的。

先不看答案，自己试试。

公式

单元格A1中的数组公式为：

=LOOKUP(，0/FREQUENCY(0，1/MMULT(SUMIF(B3:B12，B3:B12，OFFSET(D3，{0，1，2}))/SUMIF(B3:B12，B3:B12，OFFSET(C3，{0，1，2})))，{ 1；1;1}))，B3:B4)

公式分析

在这里，为了衡量平均同比增长，没有必要实际计算任何形式的数学平均值。因此，只需要简单地计算每个地区该地区所有国家的所有同比比率之和。该公式利用MMULT、OFFSET和SUMIF函数实现。

1.首先看公式的这一部分：

SUMIF(B3:B12，B3:B12，OFFSET(D3，{0，1，2}))

有效地执行一系列三种不同的SUMIF计算，并使用以下部分生成参数sum_ranges的三个值：

OFFSET(D3，{0，1，2}

对于OFFSET函数，如果省略了参数高度和参数宽度，则意味着它们的值默认为1。但是，在这种情况下，它相当于：

偏移量(D3，{0，1，2}，10)

这是因为上面的结果构造传递给了另一个函数(本例中是SUMIF函数)，并且因为这个函数要求其参数sum_range的大小和位移等于其参数范围的大小和位移，所以Excel扩展了OFFSET函数生成的范围来满足这个要求。

因此，虽然在正常情况下，公式中的结构：

OFFSET(D3，{0，1，2}

将解析为单个单元格引用的数组：

{D3，E3，F3}

但是，在本例中，Excel将接受每个引用，并将其扩展到与指定单元格区域相同的大小(即B3:B12)，因此：

SUMIF(B3:B12，B3:B12，OFFSET(D3，{0，1，2}))

决心：

SUMIF(B3:B12，B3:B12，{D3:D12，E3:E12，F3:F12})

而不是通常的：

SUMIF(B3:B12，B3:B12，{D3，E3，F3})

这样，将返回一个10行3列的数组，每一列中的元素都等于通过以下三个公式分别计算的结果：

SUMIF(B3:B12，B3:B12，D3:D12)

SUMIF(B3:B12，B3:B12，E3:E12)

SUMIF(B3:B12，B3:B12，F3:F12)

因此，公式中的结构为：

SUMIF(B3:B12，B3:B12，OFFSET(D3，{0，1，2}))

转换为：

{1345097171,1351670528,1358363416;8487319,8457915,8429700;22210006,22872070,23546083;6287968,6329821,6373552;1345097171,1351670528,1358363416;22210006,22872070,23546083;6287968,6329821,6373552;8487319,8457915,8429700;1345097171,1351670528,1358363416;8487319,8457915,8429700}

数组10行各3个元素表示b列各区域对应的d、e、f列数据之和，例如第一行的{1345097171、1351670528、1358363416}是“东亚太平洋”对应的d、e、f列数据之和，以此类推。

2.要确定同比变化，只需要生成上述值对应的值矩阵，但这次为C、D、E列，然后将上述数组中的每个值除以新数组中对应的元素。因此，通过类似上述的逻辑，可以验证：

SUMIF(B3:B12，B3:B12，OFFSET(C3，{0，1，2}))

转换为：

{1338663302,1345097171,1351670528;8522630,8487319,8457915;21558045,22210006,22872070;6249188,6287968,6329821;1338663302,1345097171,1351670528;21558045,22210006,22872070;6249188,6287968,6329821;8522630,8487319,8457915;1338663302,1345097171,1351670528;8522630,8487319,8457915}

3.执行除法运算：

SUMIF(B3:B12，B3:B12，OFFSET(D3，{0，1，2})/SUMIF(b 3: b 12，b : b 12，OFFSET(C3，{0，1，2 }))

获取：

{1.00480618912193,1.00488690121556,1.00495156760568;0.995856795378891,0.996535537311606,0.996664071464421;1.03024212074889,1.02980926704837,1.02946882376628;1.00620560623236,1.00665604532339,1.00690872617093;1.00480618912193,1.00488690121556,1.00495156760568;1.03024212074889,1.02980926704837,1.02946882376628;1.00620560623236,1.00665604532339,1.00690872617093;0.995856795378891,0.996535537311606,0.996664071464421;1.00480618912193,1.00488690121556,1.00495156760568;0.995856795378891,0.996535537311606,0.996664071464421}

例如，该数组中的第一个值1.00480618912193，表示“东亚太平洋”地区的人口比例从2010年到2011年有所增加。

4.为了确定这十个区域中哪一个区域的同比平均值最高，只需将上面代表每个区域的数组中的三个比例相加，并确定其中的最大值(如前所述，这里不需要计算数学平均值)。这意味着可以使用MMULT实现将上述矩阵的十行中的每一行中的三个元素相加，因此：

MMULT(SUMIF(B3:B12，B3:B12，OFFSET(D3，{0，1，2}))/SUMIF(B3:B12，B3:B12，OFFSET(C3，{0，1，2}))，{ 1；1;1})

转换为：

MMULT({1.00480618912193，1.0048690121556，1.0048690121556，1.004999999950.995856795378891,0.996535537311606,0.996664071464421;1.03024212074889,1.02980926704837,1.02946882376628;1.00620560623236,1.00665604532339,1.00690872617093;1.00480618912193,1.00488690121556,1.00495156760568;1.03024212074889,1.02980926704837,1.02946882376628;1.00620560623236,1.00665604532339,1.00690872617093;0.995856795378891,0.996535537311606,0.996664071464421;1.00480618912193,1.00488690121556,1.00495156760568;0.995856795378891,0.996535537311606,0.996664071464421},{1;1;1})

获取：

{3.01464465794317;2.98905640415492;3.08952021156354;3.01977037772668;3.01464465794317;3.08952021156354;3.01977037772668;2.98905640415492;3.01464465794317;2.98905640415492}

此时，我们可能会想到使用INDEX、MATCH和MAX函数的标准组合技术来查找上述数组中的最大值：

=INDEX(B3:B12，MATCH(MAX(MMULT(SUMIF(B3:B12，B3:B12，OFFSET(D3，{0，1，2}))/SUMIF(B3:B12，b 3: b 12，OFFSET(C3，{0，1，2}))，{ 1；1;1}))，MMULT(SUMIF(B3:B12，B3:B12，OFFSET(D3，{0，1，2})/SUMIF(B3:B12，b 3: b 12，OFFSET(C3，{0，1，2 }))，{ 1；1;1}),0))

然而，既然我们在寻找解决问题的最短公式，我们就用了不同的技巧：LOOKUP和FREQUENCY函数的结合，更简洁，更惊人！

5.公式：

=LOOKUP(，0/FREQUENCY(0，1/MMULT(SUMIF(B3:B12，B3:B12，OFFSET(D3，{0，1，2}))/SUMIF(B3:B12，B3:B12，OFFSET(C3，{0，1，2})))，{ 1；1;1}))，B3:B4)

替换为上面获得的中间值，可以转换为：

=LOOKUP(，0/FREQUENCY(0，1/{ 3.01464465794317；2.98905640415492;3.08952021156354;3.01977037772668;3.01464465794317;3.08952021156354;3.01977037772668;2.98905640415492;3.01464465794317;2.98905640415492})，B3:B4)

转换为：

=LOOKUP(，0/FREQUENCY(0，{ 0.331714053716128；0.334553740307462;0.323674852896956;0.331151006505605;0.331714053716128;0.323674852896956;0.331151006505605;0.334553740307462;0.331714053716128;0.334553740307462})，B3:B4)

原理很简单。前一个数组中的最大值除以1后必须成为后一个数组中的最小值。

通常，如果将一个值数组(都在0和1之间)作为参数bin _ array的值传递给FREQUENCY函数，并将0作为其参数data_array的值，则将1赋给参数bin _ array中的最小值，其余值将为null或零。因此，公式转换为：

=LOOKUP(，0/{ 0；0;1;0;0;0;0;0;0;0;0}，B3:B4)

转换为：

=LOOKUP(，{#DIV/0！#DIV/0！0;#DIV/0！#DIV/0！#DIV/0！#DIV/0！#DIV/0！#DIV/0！#DIV/0！#DIV/0！}，B3:B4)

这里，第一个参数LOOKUP _ value的值在LOOKUP函数中被忽略，这相当于将该参数的值指定为0。

注意公式中没有指定区域B3:B12，而是使用了缩小的单元格区域B3:B4，与上面提到的OFFSET函数的工作原理相同。Excel自动将单元格区域b:b4扩展到所需的b:b12。

最后的结果是：

撒哈拉以南非洲

总结

进一步了解SUMIF函数、OFFSET函数和LOOKUP函数的工作原理。