根據工作表中提供的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函數來說，一般情況下如果省略參數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列的數組，其每列中的元素等于下面三個公式單獨計算的結果：
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. 為了確定這十個區域中哪個區域的同比平均值最高，只需要對代表每個區域的上述數組中的三個比例求和，并確定其中的最大值（如前所述，實際上無需計算這里的數學平均值）。這意味著將上述矩陣的十行中的每行中的三個元素相加，可使用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函數的工作原理。
