Array Functions
什么是矩阵？
包含数值的电子表格中互相关联的单元格范围称为矩阵。一个由 3 行 3 列组成的正方形单元格范围是一个 3 x 3 矩阵:
|
A |
B |
C |
D |
E |
|
|---|---|---|---|---|---|
|
1 |
7 |
31 |
33 |
||
|
2 |
95 |
17 |
2 |
||
|
3 |
5 |
10 |
50 |
||
|
4 |
最小的矩阵是由两个相邻单元格组成的 1 x 2 或者 2 x 1 矩阵。
什么是矩阵公式？
能够对单元格范围内各值分别进行计算的公式称为矩阵公式。矩阵公式与其他公式的区别在于，矩阵公式可同时处理多个值，而不局限于一个值。
矩阵公式不仅能处理多个值，也能返回多个值。矩阵公式的结果也是一个矩阵。
要将上述矩阵中各单元格的数值都乘以 10，不必分别对各单元格或各数值应用公式。只需使用数组公式即可搞定。在电子表格的其他位置选择 3 x 3 的单元格区域，输入公式「=10*A1:C3」，并按组合键「CommandCtrl + Shift + 回车」确认该输入。计算结果是一个 3 x 3 的数组，单元格范围 (A1:C3) 中的每个数值都被乘以 10。
除乘法以外，还可在引用区域 (矩阵) 中运用其他运算符。在 LibreOffice Calc 中，可进行加 (+)、减 (-)、乘 (*)、除 (/)、乘方 (^)、字符连接 (&) 和比较 (=、<>、<、>、<=、>=) 等运算。如果已输入矩阵公式，这些运算符还可用于单元格区域中的单个数值，并以矩阵的形式返回结果。
矩阵公式中的比较符处理空单元格的方式与普通公式一样，也就是说，当为 0 或者空字符串时返回结果一样。例如，当单元格 A1 和 A2 为空，矩阵公式「{=A1:A2=""}」和「{=A1:A2=0}」都将返回一个 1 列 2 行且包含 TRUE 的单元格矩阵。
何时用矩阵公式？
当需要对不同的数值重复执行相同的计算时，使用矩阵公式可以带来不少方便。如果以后要更改计算方法，也只需更新矩阵公式。要添加任何矩阵公式，请选择整个矩阵范围，然后对矩阵公式进行所需更改。
由于数组公式不会占用大量内存，因此在需要计算多个值时，使用数组公式还可以节省空间。此外，由于可以在计算中包括多个单元格范围，因此在进行复杂计算时，数组也是非常重要的工具。LibreOffice 为数组提供了不同的数学函数，如用于两个数组相乘的 MMULT 函数，或用于计算两个数组标量乘积的 SUMPRODUCT 函数。
使用 LibreOffice Calc 中的矩阵公式
Implicit intersection of array formulas
You can also create a "normal" formula in which the reference range, such as parameters, indicate an array formula. This formula is also known as "implicit intersection" of array formula. The result is obtained from the intersection of the reference range and the rows or columns in which the formula is found. If there is no intersection or if the range at the intersection covers several rows or columns, a #VALUE! error message appears. The following example illustrates this concept:
In the table above, place the array formula in D1:
{=A1:A3 + 100}
Cells D1, D2, D3 have values 107, 195, 105 respectively.
Insert the formula below in E2, do not enter as array formula.
=A1:A3 + 100
Cells E1 and E3 are empty, Cell E2 has value 195. This is the implicit intersection of array formulas.
Insert formula below in E4, as in E2.
=A1:A3 + 100
Cell E4 display #VALUE!. Row 4 is out of the range A1:A3 of the formula.
创建矩阵公式
如果使用「函数向导创建矩阵公式，就必须每次都选中矩阵」复选框，使结果返回矩阵中。否则，将只返回被计算矩阵左上角单元格中的数值。
若直接在单元格中输入数组公式，必须使用组合键「Shift + 命令Ctrl + Enter」代替「Enter」键。只有这样公式才能变为数组公式。
在 LibreOffice Calc 中，矩阵公式前后会加上大括号。不能通过手动输入大括号来创建矩阵公式。
The cells in a results array are automatically protected against changes. However, you can edit, delete or copy the array formula by selecting the entire array cell range.
在公式中使用内联数组常量
Calc 支持在函数中使用内联矩阵/数组常量。一个内联数组用一对花括号括起来。数组元素可以是数字 (包括负数) 、逻辑常量 (TRUE、FALSE) 或字符串，但不可以是非常量表达式。一个数组可以输入一行或多行，也可以输入一列或多列。所有行必须含有同样数目的元素， 所有列必须含有同样数目的元素。
列分隔符 (分隔行内元素) 与行分隔符受语言与区域决定。但本帮助内容中，「;」分号与「|」符号分别作为列与行分隔符。例如在「英语」区域中，「,」用作列分隔符，而「;」用作行分隔符。
您可以在「LibreOffice - 偏好设置工具 - 选项 - Calc - 公式 - 分隔符」查看与修改水平/垂直分隔符。
数组不可以嵌套。
「示例」:
={1;2;3}
由 1、2、3 三个数字组成的单行数组。
要输入数组常量，请选择同列的三个单元格，然后输入公式「={1;2;3}」，注意公式中使用花括号与分号，然后按「命令Ctrl + Shift + Enter」。
={1;2;3|4;5;6}
一个 2 行且每行有 3 个数值的数组。
={0;1;2|FALSE;TRUE;"two"}
一个混合数据数组。
=SIN({1;2;3})
作为一个矩阵公式输入，参数为 1、2、3，输出 3 个正弦计算结果。
编辑矩阵公式
-
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 CommandCtrl + /, where / is the division key on the numeric keypad.
-
按「F2」或将光标置于输入行中，即可进行公式编辑。
-
作出变更后，请按「命令Ctrl + Shift + Enter」。
可设置矩阵各部分的格式。例如，可更改字体颜色。只需选择单元格区域，然后根据需要更改其属性即可。
Deleting Array Formulae
-
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 CommandCtrl + /, where / is the division key on the numeric keypad.
-
Press Delete to delete the array contents, including the array formula, or press Backspace and this brings up the Delete Contents dialog box. Select Formula or Delete All and click OK.
复制矩阵公式
-
选择包含矩阵公式的单元格区域或矩阵。
-
按「F2」或将光标置于输入行内。
-
按「命令Ctrl + C」将公式复制到输入行中。
-
选择要插入数组公式的单元格区域, 然后按「F2」或将光标放置在输入行中。
-
在选中空间按「命令Ctrl + V」粘贴公式，并按「命令Ctrl + Shift + Enter」确认。选中范围现在包含数组公式。
调整矩阵区域
如果要编辑输出矩阵，请执行以下操作:
-
选择包含矩阵公式的单元格区域或矩阵。
-
在选中区域的右下方，将显示一个小图标，使用鼠标点击此图标可放大或缩小此区域。
调整矩阵区域时，不会自动调整矩阵公式。更改的只是将显示结果的区域。
按住「命令Ctrl」键可在指定范围创建数组公式的副本。
有条件矩阵计算
有条件矩阵计算是包括 IF() 或 CHOOSE() 函数的矩阵公式。公式中的条件变量是区域引用或矩阵结果。
在以下示例中，{=IF(A1:A3>0;"yes";"no")} 公式的 >0 测试应用到区域 A1:A3 中的每个单元格，结果被复制到相应的单元格。
|
A |
B (公式) |
B (结果) |
|
|---|---|---|---|
|
1 |
1 |
{=IF(A1:A3>0;"yes";"no")} |
是 |
|
2 |
0 |
{=IF(A1:A3>0;"yes";"no")} |
否 |
|
3 |
1 |
{=IF(A1:A3>0;"yes";"no")} |
是 |
以下函数支持强制的矩阵处理: 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
以单列矩阵的形式指示频率分布。该函数用于计算「数据」矩阵中的值数目，这些值位于「分类」矩阵提供的值的范围之内。
FREQUENCY(Data; Classes)
「Data」表示对要计算的值的引用。
「Classes」表示限制值的矩阵。
This function ignores any text or empty cell within a data range. If you suspect wrong results from this function, look for text in the data ranges. To highlight text contents in a data range, use the value highlighting feature.
您可以在此页顶部找到矩阵函数的一般性介绍。
在下表中，列 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) 中选择「数据」范围，然后选择「分类」范围，在其中输入分类限制值 (B1:B6)。选中「数组」复选框并点击「确定」。将在 C1:C6 区域中看到频率计数。
在此页面顶部有更多说明。
GROWTH
计算矩阵中指数回归点。
GROWTH(DataY [; [ DataX ] [; [ NewDataX ] [; FunctionType ] ] ])
「DataY」表示 Y 数据矩阵。
「DataX」 (可选) 表示 X 数据矩阵。
「NewDataX」 (可选) 表示用于重新计算值的 X 数据矩阵。
「FunctionType」 (可选)。如果 FunctionType = 0，则按公式 y = m^x 计算。否则按 y = b*m^x 计算。
在 LibreOffice Calc 函数中，只有其后没有参数时，才可省略标记为「可选」的参数。例如，在带有四个参数的函数中，最后两个参数标记为「可选」时，可省略参数 4 或参数 3 和 4，但不能单独省略参数 3。
在此页面顶部有更多说明。
该函数返回一个数组，其处理方法与其他数组函数相同。选择一个用于显示结果的区域，然后选择此函数。选择「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」的形式。
如果「linearType」是 FALSE，找到的直线就一定通过原点 (常量 a 为 0；y = bx)。省略的话，「linearType」默认被设置为 TRUE (直线不一定通过原点)。
如果「stats」省略或是 FALSE，只返回统计报表的首行。如果是 TRUE，则返回整个报表。
LINEST 返回如下的统计报表 (数组)，并且必须作为数组公式输入 (例如，通过使用「命令Ctrl + Shift + 回车」组合键，而不是只按「回车」键)。
在 LibreOffice Calc 函数中，只有其后没有参数时，才可省略标记为「可选」的参数。例如，在带有四个参数的函数中，最后两个参数标记为「可选」时，可省略参数 4 或参数 3 和 4，但不能单独省略参数 3。
在此页面顶部有更多说明。
该函数返回一个数组，其处理方法与其他数组函数相同。选择一个用于显示结果的区域，然后选择此函数。选择「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 函数，就必须在「函数向导」中选中「矩阵」复选框。然后在工作表中选择 (或通过键盘输入) 以下数据:
「data_Y」是 C2:C8
「data_X」是 A2:B8
「linearType」和「stats」都被设置为 1。
只要点击「确定」，LibreOffice Calc 就会使用 LINEST 值填充上述示例，如该示例中所示。
在「公式」栏中的公式与 LINEST 矩阵「{=LINEST(C2:C8;A2:B8;1;1)}」每个单元格一一对应。
「以下是对 LINEST 计算结果的说明」:
E2 和 F2: 根据 x1 和 x2 的值计算回归线 y=b+m*x 的斜率 m。以相反的顺序给出结果；即在 E2 中给出 x2 的斜率，在 F2 中给出 x1 的斜率。
G2: 与 y 轴的交点 b。
E3 和 F3: 斜率值的标准误差。
G3: 截断的标准误差
E4: RSQ
F4: 应用回归法计算出的 y 值标准误差。
E5: 从方差计算得出 F 值。
F5: 从方差分析中得出自由度。
E6: y 估计值与其算术平均值的均方差和。
F6: y 估计值与给定 y 值的均方差和。
在此页面顶部有更多说明。
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，则只计算回归系数。
在 LibreOffice Calc 函数中，只有其后没有参数时，才可省略标记为「可选」的参数。例如，在带有四个参数的函数中，最后两个参数标记为「可选」时，可省略参数 4 或参数 3 和 4，但不能单独省略参数 3。
在此页面顶部有更多说明。
请参阅 LINEST。但它不返回平方和。
MDETERM
返回矩阵行列式的值。该函数在当前单元格中返回一个值；因此无需为计算结果定义一个区域。
MDETERM(数组)
「Array」代表一个要确定其行列式值的方阵。
您可以在此页顶部找到矩阵函数使用方法的一般性介绍。
在此页面顶部有更多说明。
MINVERSE
返回逆矩阵。
MINVERSE(数组)
「Array」表示要计算逆矩阵的方阵。
在此页面顶部有更多说明。
选择一个正方形区域，然后选择 MINVERSE。选择输出矩阵，然后选择「矩阵」字段，并点击「确定」。
MMULT
计算两个矩阵的乘积。矩阵 1 的列数必须与矩阵 2 的行数相同，方阵的行列数都必须相等。
MMULT(Array 1; Array 2)
Array 1 represents the first array used in the array product.
Array 2 represents the second array with the same number of rows.
在此页面顶部有更多说明。
Select a square range. Choose the MMULT function. Select Array 1, then select Array 2. Using the Function Wizard, mark the Array check box. Click OK. The output array will appear in the first selected range.
MUNIT
返回给定行列数的单位矩阵。单位矩阵是主对角线元素均等于 1、其他矩阵元素均等于 0 的方阵。
MUNIT(Dimensions)
「维数」是指单位矩阵的大小。
您可以在此页顶部找到矩阵函数的一般性介绍。
在电子表格中选择一个正方形区域，例如从 A1 至 E5。
在不取消选择区域的情况下，选择 MUNIT 函数。选中「矩阵」复选框。输入所需的单位矩阵维数，在本例中为「5」，然后点击「确定」。
也可以在选中范围的最后一个单元格 (E5) 中输入公式「=MUNIT(5)」，然后按「Shift + Command + EnterShift + Ctrl + Enter」。
此时将显示一个单位矩阵，区域为 A1:E5。
在此页面顶部有更多说明。
SUMPRODUCT
将给定矩阵中的对应元素相乘，然后将乘积相加。
SUMPRODUCT(Array 1[; Array 2;][...;[Array 255]])
Array 1[; Array 2;][...;[Array 255]] represent arrays whose corresponding elements are to be multiplied.
At least one array must be part of the argument list. If only one array is given, all array elements are summed. If more than one array is given, they must all be the same size.
|
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 将返回一个数字，无需输入函数作为矩阵函数。
在此页面顶部有更多说明。
SUMX2MY2
返回两个矩阵中对应值的平方差之和。
SUMX2MY2(ArrayX; ArrayY)
「ArrayX」表示将元素平方求和的第一个矩阵。
「ArrayY」表示元素将平方相减的第二个矩阵。
在此页面顶部有更多说明。
SUMX2PY2
返回两个矩阵中对应值平方和的总和。
SUMX2PY2(ArrayX; ArrayY)
「ArrayX」表示将元素平方求和的第一个矩阵。
「ArrayY」表示元素将平方相减的第二个矩阵。
在此页面顶部有更多说明。
SUMXMY2
将两个矩阵中对应值之差的平方值相加。
SUMXMY2(ArrayX; ArrayY)
「ArrayX」表示元素将被相减并平方的第一个矩阵。
「ArrayY」表示元素将被相减并平方的第二个矩阵。
在此页面顶部有更多说明。
TRANSPOSE
将矩阵的行和列转置。
TRANSPOSE(数组)
「矩阵」代表电子表格中要进行转置的矩阵。
您可以在此页顶部找到矩阵函数使用方法的一般性介绍。
在电子表格中，选择能够显示转置后数组的区域。如果原始数组包含 n 行 m 列，那么您选择的区域就至少需要包含 m 行 n 列。然后直接输入公式，选择原始数组并按「Shift + Command + EnterShift + Ctrl + Enter」组合键。或者，如果您使用的是「函数向导」，请标记「数组」复选框。转置后的数组就会出现在选中的目标区域内，并且自动保护，不会被更改。
|
A |
B |
C |
D |
|
|---|---|---|---|---|
|
1 |
2 |
3 |
4 |
5 |
|
2 |
6 |
7 |
8 |
9 |
上面的表格有两行 x 四列。要转置它，必须选择四行 x 两列。假设您希望将上表转置到范围 A7:B10 (四行，两列) 您必须选择整个范围，然后输入:
TRANSPOSE(A1:D2)
然后通过「Shift + 命令 + EnterShift + Ctrl + Enter」确保作为矩阵公式输入。结果如下:
|
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。
在 LibreOffice Calc 函数中，只有其后没有参数时，才可省略标记为「可选」的参数。例如，在带有四个参数的函数中，最后两个参数标记为「可选」时，可省略参数 4 或参数 3 和 4，但不能单独省略参数 3。
在此页面顶部有更多说明。
选择一个用于显示回归数据的电子表格区域，选择函数，输入输出数据或利用鼠标将其选中，选中「矩阵」字段，然后点击「确定」，将显示根据输出数据计算的回归数据。