excel技巧：获取最大年增长率对应的值( 二 ) _知识经验

例如，在这个数组中的第一个值1.00480618912193表示2010年至2011年之间“East Asia & Pacific”区域的人口比例增长。
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.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
小结
【excel技巧：获取最大年增长率对应的值】进一步理解SUMIF函数、OFFSET函数和LOOKUP函数的工作原理。

excel技巧：获取最大年增长率对应的值( 二 )

猜你喜欢