1. 首頁 > 生活百科 > >

excel技巧：獲取最大年增長率對應的值

生活百科小知識

根據工作表中提供的2010年至2013年一些國家的人口數據，在單元格A1中使用一個公式來確定該期間人口平均年增長率最大的區域 。

excel技巧：獲取最大年增長率對應的值


圖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}

猜你喜歡