如下圖1所示，根據(jù)工作表中提供的2010年至2013年一些國家的人口數(shù)據(jù)，在單元格A1中使用一個公式來確定該期間人口平均年增長率最大的區(qū)域。
圖1
公式要求：
1. 不僅結(jié)果正確，而且要使用最少的字符。
2. 必須同時包括行列引用，而不能只是行引用或列引用（例如3:2或A:F）。
3. 不允許使用名稱。
先不看答案，自已動手試一試。
公式
在單元格A1中的數(shù)組公式為：
=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)
公式解析
這里，為了測量平均同比增長，實(shí)際計算任何形式的數(shù)學(xué)平均值并不是必需的。因此，只需為每個區(qū)域簡單地計算該區(qū)域內(nèi)所有國家的所有同比比率之和即可。公式結(jié)合使用MMULT、OFFSET和SUMIF函數(shù)來實(shí)現(xiàn)。
1. 先看看公式中的這部分：
SUMIF(B3:B12,B3:B12,OFFSET(D3,,{0,1,2}))
有效地執(zhí)行了一系列三個不同的SUMIF計算，使用以下部分生成了參數(shù)sum_ranges的三個值：
OFFSET(D3,,{0,1,2}
對于OFFSET函數(shù)來說，一般情況下如果省略參數(shù)height和參數(shù)width意味著則默認(rèn)它們的值為1。然而，在本例的情形下，它等價于：
OFFSET(D3,,{0,1,2},10)
這是由于將上面的結(jié)果構(gòu)造傳遞給另一個函數(shù)（本例中為SUMIF函數(shù)），并且由于該函數(shù)要求其參數(shù)sum_range的大小和位移等于其參數(shù)range的大小和位移，因此Excel擴(kuò)展了OFFSET函數(shù)生成的范圍以滿足該要求。
因此，雖然在正常情況下，公式中的構(gòu)造：
OFFSET(D3,,{0,1,2}
會解析為由單個單元格引用組成的數(shù)組：
{D3,E3,F3}
但是，在本例中，Excel會接受每個引用并將其擴(kuò)展為與指定單元格區(qū)域（即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列的數(shù)組，其每列中的元素等于下面三個公式單獨(dú)計算的結(jié)果：
SUMIF(B3:B12,B3:B12,D3:D12)
SUMIF(B3:B12,B3:B12,E3:E12)
SUMIF(B3:B12,B3:B12,F3:F12)
因此，公式中的構(gòu)造：
SUMIF(B3:B12,B3:B12,OFFSET(D3,,{0,1,2}))
轉(zhuǎn)換為：
{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}
該數(shù)組的10行中每行的3個元素分別代表列B中每個區(qū)域相對應(yīng)的列D、列E、列F中的數(shù)據(jù)之和，例如第一行中的{1345097171,1351670528,1358363416}是“East Asia &Pacific”對應(yīng)的列D、列E和列F中數(shù)據(jù)的和，依此類推。
2. 要確定同比變化，只需生成與上述值相對應(yīng)的值矩陣，但這一次是針對列C、列D和列E，然后將上面的數(shù)組中的每個值除以新數(shù)組中相應(yīng)的元素。因此，通過與上述類似的邏輯，可以驗(yàn)證：
SUMIF(B3:B12,B3:B12,OFFSET(C3,,{0,1,2}))
轉(zhuǎn)換為：
{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. 執(zhí)行除法操作：
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}
例如，在這個數(shù)組中的第一個值1.00480618912193表示2010年至2011年之間“East Asia & Pacific”區(qū)域的人口比例增長。
4. 為了確定這十個區(qū)域中哪個區(qū)域的同比平均值最高，只需要對代表每個區(qū)域的上述數(shù)組中的三個比例求和，并確定其中的最大值（如前所述，實(shí)際上無需計算這里的數(shù)學(xué)平均值）。這意味著將上述矩陣的十行中的每行中的三個元素相加，可使用MMULT實(shí)現(xiàn)，從而：
MMULT(SUMIF(B3:B12,B3:B12,OFFSET(D3,,{0,1,2}))/SUMIF(B3:B12,B3:B12,OFFSET(C3,,{0,1,2})),{1;1;1})
轉(zhuǎn)換為：
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}
到這里，我們可能會想到使用標(biāo)準(zhǔn)的INDEX、MATCH和MAX函數(shù)組合技術(shù)來求出上述數(shù)組中的最大值：
=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))
然而，既然要尋找最簡短的公式來解決問題，我們使用一項(xiàng)不同的技術(shù)：LOOKUP和FREQUENCY函數(shù)的組合，更簡潔，也更令人驚奇！
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)
使用上面已經(jīng)獲取的中間值替換，可轉(zhuǎn)換為：
=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)
轉(zhuǎn)換為：
=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)
原理很簡單，之前數(shù)組中的最大值除1后必然成為后面數(shù)組中的最小值。
通常，如果將值數(shù)組（都在0到1之間）作為參數(shù)bins_array的值傳遞給FREQUENCY函數(shù)，而將0作為其參數(shù)data_array的值，則1將賦給參數(shù)bins_array中的最小值，其余的將為空或?yàn)榱?。因此，公式轉(zhuǎn)換為：
=LOOKUP(,0/{0;0;1;0;0;0;0;0;0;0;0},B3:B4)
轉(zhuǎn)換為：
=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函數(shù)中忽略第一個參數(shù)lookup_value的值，這等效于指定該參數(shù)的值為0。
注意到，公式中并沒有指定區(qū)域B3:B12，而是使用了縮小的單元格區(qū)域B3:B4，這與前面所講的OFFSET函數(shù)的工作原理相同，Excel將單元格區(qū)域B3:B4自動擴(kuò)展為所需的B3:B12。
最后得到的結(jié)果為：
Sub-Saharan Africa
小結(jié)
進(jìn)一步理解SUMIF函數(shù)、OFFSET函數(shù)和LOOKUP函數(shù)的工作原理。
