Excel｜細說數組公式及其優勢

06-05

數組是由一個或者多個元素按照行、列排列方式組成的集合。用戶可以將數組視為一行值、一列值或行值和列值的組合。數組公式可以返回多個結果（一個數組，並將數組各元素值分別賦給對應區域的單元格），也可返回單個結果（對數組的各元素分別參與指定的計算，形成一個結果）。例如，可以在單元格區域中創建數組公式，並使用數組公式計算列或行的小計。也可以將數組公式放入單個單元格中，然後計算單個量。包括多個單元格的數組公式稱為多單元格公式，位於單個單元格中的數組公式稱為單個單元格公式。

數組公式在編輯狀態下不是只按「Enter」，而是按「Shift+Ctrl+Enter」組合鍵才能得出公式計算結果。此組合鍵相當於下達執行多項運算的命令。

1 輸入數組公式

如有以下數據：

物品	數量	單價	金額
印表機	5	1650
碎紙機	9	980
顯示器	5	2500
傳真機	25	390
掃描儀	33	290
錄音筆	19	980
匯總	96	6790

「金額」列的值也可以使用數組公式，如下表：

物品	數量	單價	金額	金額(數組公式)
印表機	5	1650	=B11*C11	=B11:B16*C11:C16
碎紙機	9	980	=B12*C12	=B11:B16*C11:C16
顯示器	5	2500	=B13*C13	=B11:B16*C11:C16
傳真機	25	390	=B14*C14	=B11:B16*C11:C16
掃描儀	33	290	=B15*C15	=B11:B16*C11:C16
錄音筆	19	980	=B16*C16	=B11:B16*C11:C16
匯總	=SUM(B11:B16)	=SUM(C11:C16)	=SUM(D11:D16)	=SUM(E11:E16)

金額匯總				=SUM(B11:B16*C11:C16)

上表各公式的值如下：

物品	數量	單價	金額	金額(數組公式)
印表機	5	1650	8250	8250
碎紙機	9	980	8820	8820
顯示器	5	2500	12500	12500
傳真機	25	390	9750	9750
掃描儀	33	290	9570	9570
錄音筆	19	980	18620	18620
匯總	96	6790	67510	67510

金額匯總				67510

以上在多個單元格中使用同一公式並按照數組公式的方法按「Shift+Ctrl+Enter」組合鍵結束編輯形成的公式，稱為多單元格數組公式。使用多單元格數組公式能夠保證在同一範圍內的公式具有同一性，並在選定的範圍內分別顯示數組公式的各個運算結果。（返回的結果是一個數組，並將數組各元素值分別賦給對應區域的單元格，如上表的「金額(數組公式)」列。）

使用此類公式後，公式所在的任何單元格都不能被單獨編輯。

也可以是單個單元格公式。（對數組的各元素分別參與指定的計算，形成一個結果，對應上述的「金額匯總」右邊的第4個單元格。）

2 常量數組

常量數組的組成元素均為常量數據，其中文本必須由半形雙引號包括，其表示方式為一對大括弧」{}」將常量包括起來，可以使用的分隔符包括半形分號」;」和半形逗號」,」，其中分號用於分隔按行排列的元素，逗號用於分隔按列排列的元素。

3 數組的維度和尺寸

如果數組的元素都在同一行或者同一列中，稱之為「一維數組」。同時包含行、列兩個方向的元素的數組稱為「二維數組」。

數組的尺寸是由構成數組的元素個數來確定的，二維數組是由M*N個元素構成M行N列的數組。

4 單值或單元素數組直接運算

單值或單元素數組可以與另一個數組自由運算，返回與另一個數組相同尺寸的結果，如下表所示：

公式	說明
=3+{1;2;3;4}	返回{4;5;6;7}，尺寸與{1;2;3;4}相同
=column(B:B)*{1,2,3,4}	返回{2,4,6,8}，尺寸與{1,2,3,4}相同
=column(B:B)*{1;2;3;4}	返回{2;4;6;8}，尺寸與{1;2;3;4}相同

5 一維水平數組

公式	=COLUMN(B:B)*{1,2,3,4}	=COLUMN(B:B)*{1,2,3,4}	=COLUMN(B:B)*{1,2,3,4}	=COLUMN(B:B)*{1,2,3,4}
對應的值	2	4	6	8

6 一維垂直數組

公式	對應的值
=COLUMN(B:B)*{1;2;3;4}	2
=COLUMN(B:B)*{1;2;3;4}	4
=COLUMN(B:B)*{1;2;3;4}	6
=COLUMN(B:B)*{1;2;3;4}	8

7 二維數組

二維數組公式：

={1,2,3;4,5,6;7,8,9}	={1,2,3;4,5,6;7,8,9}	={1,2,3;4,5,6;7,8,9}
={1,2,3;4,5,6;7,8,9}	={1,2,3;4,5,6;7,8,9}	={1,2,3;4,5,6;7,8,9}
={1,2,3;4,5,6;7,8,9}	={1,2,3;4,5,6;7,8,9}	={1,2,3;4,5,6;7,8,9}

二維數組公式對應的值：

1	2	3
4	5	6
7	8	9

由上述內容可知，使用多單元格數組公式能夠保證在同一範圍內的公式具有同一性，並在選定的範圍內分別顯示數組公式的各個運算結果。而單個單元格公式則可以對區域的數據進行需要的計算，這是非數組公式所不能比擬的優勢，當然類似supproduct()函數其參數也可以使用數組。