Excel數組公式-入門

Excel數組公式-入門

什麼是數組公式？

數組公式昰一種專門用於數組的公式類型。

數組公式可以可以產生單個結果，也可以同時分列顯示多個結果。

數組可以在單個單元格中使用，也可以同時在一批單元格中使用。

一個數組其實就是一組同類型的數據，可以當做一個整體來處理。要記住這一點。

對於不熟悉數組概念的讀者來說，數組就是一組數。

使用數組公式的主要優點是，可以把數據當成一個整體來處理，傳遞給函數或公式。

可以對一批單元格應用一個公式，返回結果可以是一個數，也可以是一組數（每個數佔一個單元格）。

數組公式可以對一批單元格統一處理，而無須對每個單元格一一應用公式。

Excel自動在數組公式外加上花括弧{}。手動加{}無效。 

使用數組常數

數組常數是一組數，可以當作數組參數來用。

根據行數和列數的不同，數組可以是一維或二維的。

一維數組就是一列或一行數。

一維數組{1,2,3,4}

二維數組就是一片單元格，包含多行和多列。

二維數組{1,2,3,4;5,6,7,8;9,10,11,12}

注意縱向數字由分號（；）隔開，橫向數字由逗號隔開。

一個數組常數可以包括數字、文字、邏輯值和錯誤值。文本必須用雙引號圍起來（例如，"文本"）。

數組常數不能包含帶有逗號、美元符號、括弧、百分號的數字，也不能包括函數和其他數組。

在計算當中，數組存在單元格中和內存中均可。

計算中，數組存在內存中可以使公式計算的更快一點。 

數組的優勢

數組公式可以確保公式一致。

數組可以存儲在內存中，因此使用數組公式可以加快公式的執行時間。

使用數組可以減少內存佔用。

使用數組公式可以防止公式意外更改。應用了數組公式的單元格，不能單獨修改，只能同時更改所有使用了該公式的單元格。

數組公式看起來比較深奧，足以嚇到那些初學者，讓他們不敢輕易更改你的公式。

一些運算用傳統公式無法實現。

數組的不足 

避免在一個工作薄中使用太多大型數組公式。雖說那樣做能夠提高執行效率，但複雜的公式也會把你自己給搞糊塗。

太多的數組公式將會使再次計算、保存、打開和關閉操作減慢。

數組公式能夠減少內存佔用，但是會減慢再次計算的速度，並且使其更為複雜。

你不能在數組公式中使用列引用（例如，"A:A"或"D:D"）。

每次輸入完成後，都必須按(Ctrl + Shift + Enter)，才能完成數組公式。

數組公式實例

可以用多種不同的函數方法來實現同一目標。大部分情況下，無需使用數組公式即可得到同樣的結果。

下面的例子演示了兩種使用數組公式的方法。

1）數字加總[使用多格數組公式]

這個例子演示了兩種用來計算一片區域數字總和的方法。 

第8和9行的公式使用SUM()工作表函數對數字加總。

第11和12行使用了數組公式對數字加總，鍵入公式後必須按(Ctrl + Shift + Enter)。數組公式計算完成後，生成一個包含四個值的數組。

Excel在公式外加上花括弧{}，指示數組公式。花括弧不能手工輸入。

數組公式"{=A1:B2+A4:B5}" 返回一個包含四個數字的數組。

一個單元格只能顯示一個數字，因此公式必須佔據四個單元格才能顯示完全。

2）計算部分和（使用單一數組公式）

本例演示了求部分單元格之和的兩種方法。

C12和C13單元格里的公式，使用加號對單個單元格進行加總。

如果需要加總的項目很多，那麼手工輸入就會很費時間，而上述方法也只能用手工輸入。

C15和C15單元格使用數組公式求部分和，公式輸入結束後，必須按(Ctrl + Shift + Enter)，否則會顯示#VALUE! 。

上述數組公式使用了2個一維數組，C3:C10 和 D3:D10。

IF()函數比較第一個數組C3:C10中的值，並返回第二個數組D3:D10中的對應值。

SUM()函數得到新生成的數組（內存中），計算並返回總和。

注意 

在一張工作表中，使用大量數組公式將會增加再次計算的時間。

大部分數組公式能執行的計算，用普通公式也能完成。不過仍有一些例外。

如果鍵入數組公式之後，沒有按(Ctrl + Shift + Enter) ，那麼公式就會返回不正確的結果或者返回 #VALUE!。

使用快捷鍵 (Ctrl + / )可以快速選擇應用了相同數組公式的單元格。

使用數組公式後，不能單獨修改其中的某一個單元格。

在數組公式中不能使用列引用 (例如 "A:A" 或 "D:D")。

公式應用的範圍內，行數必須都要相同。這是為了確保所有臨時性的數組也有同樣的長度。 

【本文翻譯僅為外語學習及閱讀目的，原文作者個人觀點與譯者及譯言網無關

推薦閱讀：