我們經常在一些excel函數公式兩邊看到添加有大括號{},我想大部分新手同學看到大括號就暈。以後蘭色寫的函數教程中也常會出現它。到底這個大括號是什麼神祕符號,今天蘭色很有必要提前介紹一下。下面是小編為大家整理的Excel數組公式入門教程,歡迎參考~
先從一個簡單的計算公式説起:
=A1*B1
它的結果為20,只有一個數。
而如果讓多個數與B1相乘,會是什麼結果呢?
=A1:A5*B1
結果是分別返回一個相乘的結果值。即返回的是一組值:20;40;50;60;30,由於單元格無法同時顯示多個結果,所以顯示為錯誤值。
如果讓一列數與另一列數相乘是什麼結果呢?
=A1:A5*B1:B5
結果是相對應的行一對一相乘,幾行數會返回幾個結果:20;8;35;48;27
説了這麼多,同學們只需要瞭解:excel中的運算後返回值的個數有兩種:1個數 和 1組數。
那麼如果一個公式中含有返回一組數的表達式時,就需要用數組運算。即在公式後按ctrl+shift+enter三鍵自動添加大括號{}。當然也有例外,象lookup、sumproduct函數就可以直接執行數組運算,而不需要添加大括號,單一使用常量數組也不需要。
説到這裏有些同學還是有些迷惑,這個倒底有什麼用,蘭色下面舉兩個小例子。
【例1】如下圖所示表銷售統計表中,要求根據銷售數量,計算所有人員提成之和(提成 10元/個)
如果用一般的方法,公式應該為:
=2*10+4*10+5*10+6*10+3*10=200
用數組方法:
{=SUM(B2:B6*10)}
套用開始的理論,因為B2:B6*10計算後返回多個結果,所以公式要添加大括號。
【例2】計劃B2:B2區域總共有多少字數。
公式:{=SUM(LEN(B2:B4))}
len(B2:B4)要返回每個單元格的字符數,返回的是一組數,所以該公式也要添加大括號。
【例3】截取字串中最後的`數字。
分析:
B1的值:excel精英培訓平台會員226574
如果用right函數從右邊截取N個字符
Right(b1,1) 結果為 4
Right(b1,2) 結果為 74
........
Right(b1,6) 結果為 226574
Right(b1,7) 結果為 員226574
RIGHT(B1,ROW(1:9) ) 的結果是什麼?Row(1:9) 返回9個數字,分別1~9行的行數,結果是從右邊截了9次。
{"4";"74";"574";"6574";"26574";"226574";"員226574";"會員226574";"台會員226574"}
*1後,把非數字轉換成錯誤值,文本型數字轉換成數字
{4;74;574;6574;26574;226574;#VALUE!;#VALUE!;#VALUE!}
最後用lookup用一個足夠大的數字9^9查找最後一個數字
最後的公式為:
=LOOKUP(9^9,RIGHT(B1,ROW(1:9))*1)
由於lookup函數可以直接支持數組運算,所以公式不需要添加大括號
蘭色説:通過今天的教程,同學們能大概知道,在什麼情況下添加大括號即可。至數組公式,在以後蘭色將會有更深入的介紹。