excel如何获得最大年增长率对应的值-英雄云拓展知识分享

以下图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}))

行之有效的地履行了一系列3个不同的SUMIF计算，使用以下部份生成了参数sum_ranges的3个值：

OFFSET(D3,,{0,1,2}

对OFFSET函数来讲，一般情况下如果省略参数height和参数width意味着则默许它们的值为1。但是，在本例的情形下，它等价于：

OFFSET(D3,,{0,1,2},10)

这是由于将上面的结果构造传递给另外一个函数（本例中为SUMIF函数），并且由于该函数要求其参数sum_range的大小和位移等于其参数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列的数组，其每列中的元素等于下面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}是“East Asia &Pacific”对应的列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(B3:B12,B3:B12,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年之间“East Asia & Pacific”区域的人口比例增长。

4. 为了肯定这10个区域中哪一个区域的同比平均值最高，只需要对代表每一个区域的上述数组中的3个比例求和，并肯定其中的最大值（如前所述，实际上无需计算这里的数学平均值）。这意味着将上述矩阵的10行中的每行中的3个元素相加，可以使用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.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;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,B3:B12,OFFSET(C3,,{0,1,2})),{1;1;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}),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之间）作为参数bins_array的值传递给FREQUENCY函数，而将0作为其参数data_array的值，则1将赋给参数bins_array中的最小值，其余的将为空或为零。因此，这种情况下，公式转换为：

=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函数中疏忽第一个参数lookup_value的值，这等效于指定该参数的值为0。

注意到，公式中并没有指定区域B3:B12，而是使用了缩小的单元格区域B3:B4，这与前面所讲的OFFSET函数的工作原理相同，Excel将单元格区域B3:B4自动扩大为所需的B3:B12。

最后得到的结果为：

Sub-Saharan Africa

小结

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