陣列函式

此分類包含陣列函式。

什麼是陣列?

陣列是包含值的試算表中儲存格的連結範圍。 3 列與 3 欄的方塊範圍是 3 x 3 陣列:

A

B

C

1

7

31

33

2

95

17

2

3

5

10

50


最小的可能陣列是由兩個相鄰儲存格組成的 1 x 2 或者 2 x 1 陣列。

什麼是陣列公式?

用於演算儲存格範圍中各個數值的公式即稱為陣列公式。陣列公式與其他公式之間的不同之處在於:陣列公式同時處理數個數值,而不是單個數值。

陣列公式不僅可以處理數個數值,還可以傳回數個數值。陣列公式的結果也是一個陣列。

若要在上述陣列中將個別儲存格的值乘以 10,您不需要將公式分別套用至每個儲存格或值,而只要使用單一陣列公式即可。在試算表的其他部分選取 3 x 3 儲存格範圍,輸入 =10*A1:C3 公式,然後使用組合鍵 +Shift+Enter 確認此輸入。結果會是 3 x 3 陣列,其儲存格範圍 (A1:C3) 中的每個值會分別乘以係數 10。

除了乘法之外,也可以在這個指定範圍(或稱之為陣列)使用其他運算子。在 OpenOfficeCalc 中,有加 (+),減 (-),乘 (*),除 (/),冪次方 (^),字串連接 (&),比較(=,<>,<,>,<=,>=) 等計算方式可供運用。以陣列公式輸入的運算子可用來對指定範圍內每個儲存格進行運算,並將結果以陣列方式儲存。

陣列公式中的比較運算子會以在正常公式中的相同方式,來處理空白儲存格,亦即當作零或空白字串。例如,若儲存格 A1 與 A2 為空的,陣列公式 {=A1:A2=""}{=A1:A2=0} 皆會傳回包含 TRUE 的 1 欄 2 列儲存格陣列。

什麼時候使用陣列公式?

如果要對不同的數值重複執行相同的計算,可使用陣列公式。以後如果您要變更計算方法,則僅需更新陣列公式。若要增加陣列公式,選取整個陣列範圍然後對陣列公式進行必要的變更。

因為陣列公式不會佔用大量記憶體,所以在計算數個數值時,使用陣列公式還可以節省記憶體空間。此外,由於陣列適用於數個儲存格範圍的計算,因此執行複雜計算時,它是重要的工具。LibreOffice 提供多個陣列數學函式,如 MMULT 函式可以將兩個陣列相乘,SUMPRODUCT 函式可以計算兩個陣列的純量乘積。

使用 LibreOffice Calc 中的陣列公式

您也可以建立「一般」公式,在此公式中參照範圍 (例如參數) 指示陣列公式。可從參照範圍與包含公式之列或欄的交集中得到結果。如果此處無交集或交集範圍涵蓋了數個列或欄,則螢幕上會顯示錯誤訊息 #VALUE!。以下範例說明了此概念:

建立陣列公式

如果您使用函式精靈建立陣列公式,每次都必須標記 [陣列] 核取方塊以便讓結果傳回陣列中。否則,僅陣列左上角儲存格中的數值之計算結果被傳回。

若直接在儲存格中輸入陣列公式,則必須使用 Shift++Enter 而非 Enter 鍵。唯有如此,輸入的公式才會成為陣列公式。

評註圖示

在 LibreOffice Calc 中,陣列公式前後會加上大括號。但是,您不能透過手動輸入大括號來建立陣列公式。


警告圖示

結果陣列中的儲存格自動受保護,無法變更。但是,您可以透過選取整個陣列儲存格範圍來編輯或複製陣列公式。


在公式中使用內嵌陣列常數

Calc 支援在公式中使用內嵌陣列/陣列常數。內嵌陣列會以大括號「{」和「}」括住。元素可以是數字 (包含負數)、邏輯常數 (TRUE、FALSE) 或文字字串。不允許非常數表示式。輸入的陣列可包含一或多列以及一或多欄。所有列和所有欄皆必須包含相同的元素數目。

欄分隔符(一列中的分隔元素)與列分隔符有賴語言與區域決定。但本說明內容中,';' 分號與 '|' 符號個別表示欄與列分隔符。例如在「英語」區域中,','半形逗號用作欄分隔符,而 ';' 半形分號用作列分隔符號。

提示圖示

You can view and change the row and column separator in - Calc - Formula - Separators.


您無法嵌套陣列。

範例:

={1;2;3}

此陣列具有一列,且內含三個數字 1、2 和 3。

若要輸入此陣列常數,請選取一列連續的三個儲存格,然後用大括號及分號輸入公式 ={1;2;3},然後按 +Shift+Enter。

={1;2;3|4;5;6}

此陣列具有兩列,且每列各有三個數值。

={0;1;2|FALSE;TRUE;"two"}

混合資料陣列。

=SIN({1;2;3})

以矩陣公式方式輸入,求得內含引數 1、2 和 3 的三個正弦計算結果。

編輯陣列公式

  1. Select the cell range or array containing the array formula. To select the whole array, position the cell cursor inside the array range, then press +/, where / is the Division key on the numeric keypad.

  2. 按下 F2 或將游標放在輸入欄中。這兩個動作都能讓您編輯公式。

  3. 在變更之後,按 +Shift+Enter。

提示圖示

您可以分別格式化陣列的各個部分。例如,您可以變更字型顏色。選取儲存格範圍,然後變更需要的屬性。


複製陣列公式

  1. 選取包含陣列公式的儲存格範圍或陣列。

  2. 按下 F2 或將游標放在輸入欄中。

  3. +C 將公式複製到輸入行。

  4. 選取要插入陣列公式的儲存格範圍,然後按 F2 鍵或將游標放置在輸入欄中。

  5. +V 將公式貼上所選位置,並按 Shift+Enter 進行確認。選取的範圍就會包含此陣列公式。

調整陣列範圍

如果要編輯匯出陣列,請執行以下操作:

  1. 選取包含陣列公式的儲存格範圍或陣列。

  2. 在選取區域下方往右,您會看見小圖示,這可讓您使用滑鼠來放大或縮小該區域。

評註圖示

調整陣列範圍時,不會自動調整陣列公式。您僅變更了將要顯示結果的範圍。


By holding down the key, you can create a copy of the array formula in the given range.

有條件的陣列計算

有條件的陣列計算是包括 IF() 或 CHOOSE() 函式的陣列或陣列公式。公式中的有條件引數是區域參照或陣列結果。

在下列範例中,{=IF(A1:A3>0;"yes";"no")} 公式的 >0 測試套用至 A1:A3 範圍中的每個儲存格,而結果會複製到對應儲存格。

A

B (公式)

B (結果)

1

1

{=IF(A1:A3>0;"yes";"no")}

yes

2

0

{=IF(A1:A3>0;"yes";"no")}

no

3

1

{=IF(A1:A3>0;"yes";"no")}

yes


下列函式提供強制陣列處理:CORREL, COVAR, FORECAST, FTEST, INTERCEPT, MDETERM, MINVERSE, MMULT, MODE, PEARSON, PROB, RSQ, SLOPE, STEYX, SUMPRODUCT, SUMX2MY2, SUMX2PY2, SUMXMY2, TTEST。當您呼叫這些函式之一時使用區域參照作為引數,這些函式會以陣列函式來運作。下列表格提供強制陣列處理的範例:

A

B (公式)

B (結果)

C (強制陣列公式)

C (結果)

1

1

=A1:A2+1

2

=SUMPRODUCT(A1:A2+1)

5

2

2

=A1:A2+1

3

=SUMPRODUCT(A1:A2+1)

5

3

=A1:A2+1

#VALUE

=SUMPRODUCT(A1:A2+1)

5


FREQUENCY

指示一個頻率分佈,以單欄陣列的形式顯示結果。函式統計 Data 陣列中在 Classes 陣列各值之內的值的個數。

語法

FREQUENCY(Data; Classes)

Data 表示要計數的數值組的參照。

Classes 表示限值陣列。

評註圖示

您可以在本頁上方找到陣列函式的一般簡介。


範例

在下表中,欄 A 列出了未排序的定量值。欄 B 列出您所輸入的類別上限值,用於劃分欄 A 中的資料。依在 B1 中輸入的限制值,函式 FREQUENCY 會傳回小於或等於 5 的定量值數目。由於 B2 中的限制值是 10,因此函式 FREQUENCY 第二次傳回大於 5 且小於或等於 10 的定量值數目。在 B6 中輸入的文字「>25」僅供參考。

A

B

C

1

12

5

1

2

8

10

3

3

24

15

2

4

11

20

3

5

5

25

1

6

20

>25

1

7

16

8

9

9

7

10

16

11

33


在單一欄中選取一個範圍,用於根據類別限制值輸入頻率。選取的欄位數必須比類別上限多一個。在此範例中,選取的範圍為 C1:C6。在 [函式精靈] 中呼叫 FREQUENCY 函式。選取 (A1:A11) 做為 Data 範圍,然後選取用於輸入類別限制值的 Classes 範圍 (B1:B6)。選取 [陣列] 核取方塊,然後按一下 [確定]。隨即會看到 C1:C6 範圍中的頻率計數。

More explanations on top of this page.

GROWTH

計算陣列中指數趨勢的點。

語法

GROWTH(DataY; DataX; NewDataX; FunctionType)

DataY 表示 Y 資料陣列。

DataX (選擇性) 表示 X 資料陣列。

NewDataX (選擇性) 表示 X 資料陣列,其中的值會重新計算。

FunctionType (選擇性)。若 FunctionType = 0,將以格式 y = m^x 計算函式。否則會以 y = b*m^x 計算函式。

In the LibreOffice Calc functions, parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.

More explanations on top of this page.

範例

此函式會傳回一個陣列,並以與其他陣列函式相同的方式進行處理。選取要顯示答案的範圍,然後依序選取函式與 DataY。輸入其他參數,核取 [陣列],然後按一下 [確定]

LINEST

傳回最符合資料集的直線統計表。

語法

LINEST(data_Y; data_X; linearType; stats)

data_Y 為指定一組資料點中 y 座標的單一列或欄範圍。

data_X 為指定 x 座標的對應單一列或欄範圍。若省略 data_X,會預設為 1, 2, 3, ..., n。若有超過一組變數,則 data_X 的範圍可對應多列或多欄。

LINEST 使用線性迴歸法 (「最小平方法」) 以求出最符合資料的直線 y = a + bx。變數超過一組時,直線形式為 y = a + b1x1 + b2x2 ... + bnxn

If linearType is FALSE the straight line found is forced to pass through the origin (the constant a is zero; y = bx). If omitted, linearType defaults to TRUE (the line is not forced through the origin).

If stats is omitted or FALSE only the top line of the statistics table is returned. If TRUE the entire table is returned.

LINEST 會傳回如下的統計表 (陣列),且必須以陣列公式的方式輸入 (例如使用 +Shift+Return,而不只是 Return 鍵)。

In the LibreOffice Calc functions, parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.

More explanations on top of this page.

範例

此函式會傳回一個陣列,並且處理此函式的的方式與其他陣列函式相同。為答案選取一個範圍,然後依序選取函式及 data_Y。您可視需要輸入其他參數。選取 [陣列],然後按一下 [確定]

系統傳回的結果 (若 stats = 0) 至少會顯示迴歸線的斜率,以及迴歸線與 Y 軸的交點。若 stats 不等於 0,則會顯示其他結果。

其他 LINEST 結果:

檢查下列範例:

A

B

C

D

E

F

G

1

x1

x2

y

剩餘價值

2

4

7

100

4,17

-3,48

82,33

3

5

9

105

5,46

10,96

9,35

4

6

11

104

0,87

5,06

#NA

5

7

12

108

13,21

4

#NA

6

8

15

111

675,45

102,26

#NA

7

9

17

120

8

10

19

133


欄 A 包含數個 X1 值,欄 B 包含數個 X2 值,欄 C 包含數個 Y 值。您已經將這些數值輸入試算表中。現在您可在試算表中設定 E2:G6,並啟動 [函式精靈]。對於所使用的 LINEST 函式,您必須在 [函式精靈] 中標記 [陣列] 核取方塊。接著,在試算表中選取 (或透過鍵盤輸入) 以下數值:

dataY 為 C2:C8

dataX 為 A2:B8

linearTypestats 皆設定為 1。

只要按一下[確定],LibreOffice Calc 就會使用範例中顯示的 LINEST 值填入上述範例。

[公式] 列中的公式對應至 LINEST 陣列 {=LINEST(C2:C8;A2:B8;1;1)} 的各儲存格。

這表示計算的 LINEST 值:

E2 與 F2:迴歸線 y=b+m*x 之 x1 與 x2 值的斜率 m。這兩個值會以反向指定;亦即 E2 為 x2 的斜率,而 F2 為 x1 的斜率。

G2:具有 y 軸的平均值 b

E3 與 F3:斜率值的標準離差。

G3:截取的標準誤差

E4:RSQ

F4:計算 Y 值回歸的標準誤差。

E5:變異數分析的 F 值。

F5:變異數分析的自由度。

E6:從線性平均估計的 Y 值之平方偏差的總和。

F6:從指定 Y 值估計的 Y 值之平方偏差的總和。

More explanations on top of this page.

LOGEST

此函式會將輸入之資料的調整計算成指數迴歸曲線 (y=b*m^x)。

語法

LOGEST(DataY; DataX; FunctionType; Stats)

DataY 表示 Y 資料陣列。

DataX (選擇性) 表示 X 資料陣列。

FunctionType (選擇性)。若 Function_Type = 0,則會以 y = m^x 格式計算函式。否則會以 y = b*m^x 計算函式。

Stats (可選擇的)。如果 Stats=0 則只計算回歸係數。

In the LibreOffice Calc functions, parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.

More explanations on top of this page.

範例

請參閱 LINEST。然而不會傳回平方總和。

MDETERM

傳回陣列的行列式。 此函式在目前儲存格中傳回一個值,因此無需為結果定義範圍。

語法

MDETERM(Array)

array 表示一個在其中定義了行列式的方陣。

評註圖示

You can find a general introduction to using Array functions on top of this page.


More explanations on top of this page.

MINVERSE

傳回逆陣列。

語法

MINVERSE(Array)

array 表示要被逆轉的方陣。

More explanations on top of this page.

範例

選取一個正方形範圍並選取函式 MINVERSE。依次選取匯出陣列和 [陣列] 欄位,並按一下 [確定]

MMULT

計算量兩個陣列的陣列乘積。 陣列 1 的欄數必須與陣列 2 的列數相同。方陣的列與欄數都必須相等。

語法

MMULT(Array; Array)

參數中的第一個 array 表示用於計算陣列乘積的第一個陣列。

參數中的第二個 array 表示用於計算陣列乘積的第二個陣列,其列數與第一個陣列的列數相等。

評註圖示

More explanations on top of this page.


範例

選取一個正方形範圍。選擇 MMULT 函式。選取第一個 array,然後選取第二個 array。使用 [函式精靈] 標記 [陣列] 核取方塊。按一下 [確定]。匯出陣列將顯示在第一個選取的範圍中。

MUNIT

傳回特定大小的單位方陣。 單位陣列是一種方陣,其中主對角元素等於 1 而其他陣列元素都等於 0。

語法

MUNIT(尺寸)

Dimensions 是指單位陣列的大小。

評註圖示

您可以在本頁上方找到陣列函式的一般簡介。


範例

選取試算表的方塊範圍,例如從 A1 至 E5。

在選取範圍的情況下,另外選取 MUNIT 函式。核取 [陣列] 核取方塊。輸入所需的陣列單位尺寸,在此例中為 5,然後按一下 [確定]

也可以在所選範圍最後一個儲存格 (E5) 中輸入公式 =Munit(5),然後按

您現在即可看到一個範圍為 A1:E5 的單位陣列。

More explanations on top of this page.

SUMPRODUCT

將指定陣列中對應的元素相乘,並傳回乘積的小計。

語法

SUMPRODUCT(Array1; Array2...Array30)

Array1, Array2...Array30 表示對應的元素將相乘的陣列。

至少一個陣列必須是引數清單的一部分。如果只提供一個陣列,則會總計所有陣列元素。

範例

A

B

C

D

1

2

3

4

5

2

6

7

8

9

3

10

11

12

13


=SUMPRODUCT(A1:B3;C1:D3) 傳回 397。

計算:A1*C1 + B1*D1 + A2*C2 + B2*D2 + A3*C3 + B3*D3

您可使用 SUMPRODUCT 來計算兩個向量的數量積。

評註圖示

SUMPRODUCT 傳回單一數字,不必要將函式輸入為陣列函式。


More explanations on top of this page.

SUMX2MY2

傳回兩個陣列中對應值的平方之差異值小計。

語法

SUMX2MY2(ArrayX; ArrayY)

ArrayX 表示其元素平方後相加的第一個陣列。

ArrayY 表示其元素平方後相減的第二個陣列。

More explanations on top of this page.

SUMX2PY2

傳回兩個陣列中對應值的平方小計。

語法

SUMX2PY2(ArrayX; ArrayY)

ArrayX 表示其元素平方後相加的第一個陣列。

ArrayY 表示其元素平方後相加的第二個陣列。

More explanations on top of this page.

SUMXMY2

在兩個陣列中對應值之間增加變異數的平方。

語法

SUMXMY2(ArrayX; ArrayY)

ArrayX 表示其元素相減後平方的第一個陣列。

ArrayY 表示其元素相減後平方的第二個陣列。

More explanations on top of this page.

TRANSPOSE

對陣列的列和欄進行轉置。

語法

TRANSPOSE(Array)

array 表示試算表中要進行轉置的陣列。

評註圖示

您可以在本頁上方找到使用陣列函式的一般簡介。


範例

在試算表中,選取轉置後的陣列可顯示之範圍。若原先的陣列有 n 列 m 欄,則所選取的範圍至少要有 m 列 n 欄。然後直接輸入公式,選取原來的陣列並按 。或者,若使用 [函式精靈],則請標記 [陣列] 核取方塊。轉置後的陣列會顯示在選取的目標範圍中,且會自動受保護而無法變更。

A

B

C

D

1

2

3

4

5

2

6

7

8

9


The above table is 2 rows, 4 columns. In order to transpose it, you must select 4 rows, 2 columns. Assuming you want to transpose the above table to the range A7:B10 (4 rows, 2 columns) you must select the entire range and then enter the following:

TRANSPOSE(A1:D2)

Then make sure to enter it as matrix formula with . The result will be as follows:

A

B

7

2

6

8

3

7

9

4

8

10

5

9


TREND

傳回伴隨線性趨勢的值。

語法

TREND(DataY; DataX; NewDataX; LinearType)

DataY 表示 Y 資料陣列。

DataX (選擇性) 表示 X 資料陣列。

NewDataX (選擇性) 表示 X 資料的陣列,可用於重新計算數值。

LinearType(選擇性)。若 LinearType = 0,則會計算通過零的直線。否則,也會計算偏移線。預設值為 LinearType <> 0。

In the LibreOffice Calc functions, parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.

More explanations on top of this page.

範例

選取一個用於顯示趨勢資料的試算表範圍。選取函式。輸入匯出資料,或透過滑鼠選取匯出資料。標記 [陣列] 欄位,並按一下 [確定]。螢幕上會顯示依匯出資料計算而得的趨勢資料。