Array Functions

此类中包含矩阵函数。

什么是矩阵?

包含数值的电子表格中互相关联的单元格范围称为矩阵。一个由 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 + 回车」确认该输入。计算结果是一个 3 x 3 的数组,单元格范围 (A1:C3) 中的每个数值都被乘以 10。

除乘法以外,还可在引用区域 (矩阵) 中运用其他运算符。在 LibreOffice Calc 中,可进行加 (+)、减 (-)、乘 (*)、除 (/)、乘方 (^)、字符连接 (&) 和比较 (=、<>、<、>、<=、>=) 等运算。如果已输入矩阵公式,这些运算符还可用于单元格区域中的单个数值,并以矩阵的形式返回结果。

矩阵公式中的比较符处理空单元格的方式与普通公式一样,也就是说,当为 0 或者空字符串时返回结果一样。例如,当单元格 A1 和 A2 为空,矩阵公式「{=A1:A2=""}」和「{=A1:A2=0}」都将返回一个 1 列 2 行且包含 TRUE 的单元格矩阵。

何时用矩阵公式?

当需要对不同的数值重复执行相同的计算时,使用矩阵公式可以带来不少方便。如果以后要更改计算方法,也只需更新矩阵公式。要添加任何矩阵公式,请选择整个矩阵范围,然后对矩阵公式进行所需更改。

由于数组公式不会占用大量内存,因此在需要计算多个值时,使用数组公式还可以节省空间。此外,由于可以在计算中包括多个单元格范围,因此在进行复杂计算时,数组也是非常重要的工具。LibreOffice 为数组提供了不同的数学函数,如用于两个数组相乘的 MMULT 函数,或用于计算两个数组标量乘积的 SUMPRODUCT 函数。

使用 LibreOffice Calc 中的矩阵公式

也可创建一个「普通」公式,其中的引用区域 (例如参数) 表示一个矩阵公式。结果根据引用区域与公式所在行或列的交集而得。如果没有交集或者交集区域包含多行或列,则显示错误消息「#VALUE!」。下面将通过具体示例加以说明:

创建矩阵公式

如果使用「函数向导创建矩阵公式,就必须每次都选中矩阵」复选框,使结果返回矩阵中。否则,将只返回被计算矩阵左上角单元格中的数值。

若直接在单元格中输入数组公式,必须使用组合键「Shift + + Enter」代替「Enter」键。只有这样公式才能变为数组公式。

批注图标

在 LibreOffice Calc 中,矩阵公式前后会加上大括号。不能通过手动输入大括号来创建矩阵公式。


警告图标

结果矩阵的单元格将自动保护,无法对其进行修改。但是,您可以选择整个矩阵单元格区域后,对其进行编辑或复制矩阵公式。


在公式中使用内联数组常量

Calc 支持在函数中使用内联矩阵/数组常量。一个内联数组用一对花括号括起来。数组元素可以是数字 (包括负数) 、逻辑常量 (TRUE、FALSE) 或字符串,但不可以是非常量表达式。一个数组可以输入一行或多行,也可以输入一列或多列。所有行必须含有同样数目的元素, 所有列必须含有同样数目的元素。

列分隔符 (分隔行内元素) 与行分隔符受语言与区域决定。但本帮助内容中,「;」分号与「|」符号分别作为列与行分隔符。例如在「英语」区域中,「,」用作列分隔符,而「;」用作行分隔符。

提示图标

您可以在「 - Calc - 公式 - 分隔符」查看与修改水平/垂直分隔符。


数组不可以嵌套。

示例」:

={1;2;3}

由 1、2、3 三个数字组成的单行数组。

要输入数组常量,请选择同列的三个单元格,然后输入公式「={1;2;3}」,注意公式中使用花括号与分号,然后按「 + Shift + Enter」。

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

一个 2 行且每行有 3 个数值的数组。

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

一个混合数据数组。

=SIN({1;2;3})

作为一个矩阵公式输入,参数为 1、2、3,输出 3 个正弦计算结果。

编辑矩阵公式

  1. 选择单元格范围或包含数组公式的数组。要选择整个数组,请将单元格指针置于数组范围内,然后按「 + /」,其中「/」是数字小键盘区域的「除号」键。

  2. 按「F2」或将光标置于输入行中,即可进行公式编辑。

  3. 作出变更后,请按「 + Shift + Enter」。

提示图标

可设置矩阵各部分的格式。例如,可更改字体颜色。只需选择单元格区域,然后根据需要更改其属性即可。


复制矩阵公式

  1. 选择包含矩阵公式的单元格区域或矩阵。

  2. 按「F2」或将光标置于输入行内。

  3. 按「 + C」将公式复制到输入行中。

  4. 选择要插入数组公式的单元格区域, 然后按「F2」或将光标放置在输入行中。

  5. 在选中空间按「 + V」粘贴公式,并按「 + Shift + Enter」确认。选中范围现在包含数组公式。

调整矩阵区域

如果要编辑输出矩阵,请执行以下操作:

  1. 选择包含矩阵公式的单元格区域或矩阵。

  2. 在选中区域的右下方,将显示一个小图标,使用鼠标点击此图标可放大或缩小此区域。

批注图标

调整矩阵区域时,不会自动调整矩阵公式。更改的只是将显示结果的区域。


按住「」键可在指定范围创建数组公式的副本。

有条件矩阵计算

有条件矩阵计算是包括 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」表示限制值的矩阵。

批注图标

您可以在此页顶部找到矩阵函数的一般性介绍。


示例

在下表中,列 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 返回如下的统计报表 (数组),并且必须作为数组公式输入 (例如,通过使用「 + 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; Array)

第一个位置的「矩阵」代表用于计算矩阵乘积的第一个矩阵。

第二个位置的「Array」表示行数相同的第二个矩阵。

批注图标

在此页面顶部有更多说明。


示例

选择一个正方形范围。选择 MMULT 函数。选择第一个「矩阵」,然后选择第二个「矩阵」。使用「函数向导」,选中「矩阵」复选框。点击「确定」。输出矩阵将显示在第一个选中的范围内。

MUNIT

返回给定行列数的单位矩阵。单位矩阵是主对角线元素均等于 1、其他矩阵元素均等于 0 的方阵。

语法

MUNIT(Dimensions)

维数」是指单位矩阵的大小。

批注图标

您可以在此页顶部找到矩阵函数的一般性介绍。


示例

在电子表格中选择一个正方形区域,例如从 A1 至 E5。

在不取消选择区域的情况下,选择 MUNIT 函数。选中「矩阵」复选框。输入所需的单位矩阵维数,在本例中为「5」,然后点击「确定」。

也可以在选中范围的最后一个单元格 (E5) 中输入公式「=MUNIT(5)」,然后按「」。

此时将显示一个单位矩阵,区域为 A1:E5。

在此页面顶部有更多说明。

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 将返回一个数字,无需输入函数作为矩阵函数。


在此页面顶部有更多说明。

SUMX2MY2

返回两个矩阵中对应值的平方差之和。

语法

SUMX2MY2(ArrayX; ArrayY)

ArrayX」表示将元素平方求和的第一个矩阵。

ArrayY」表示元素将平方相减的第二个矩阵。

在此页面顶部有更多说明。

SUMX2PY2

返回两个矩阵中对应值平方和的总和。

语法

SUMX2PY2(ArrayX; ArrayY)

ArrayX」表示将元素平方求和的第一个矩阵。

ArrayY」表示元素将平方相减的第二个矩阵。

在此页面顶部有更多说明。

SUMXMY2

将两个矩阵中对应值之差的平方值相加。

语法

SUMXMY2(ArrayX; ArrayY)

ArrayX」表示元素将被相减并平方的第一个矩阵。

ArrayY」表示元素将被相减并平方的第二个矩阵。

在此页面顶部有更多说明。

TRANSPOSE

将矩阵的行和列转置。

语法

TRANSPOSE(数组)

矩阵」代表电子表格中要进行转置的矩阵。

批注图标

您可以在此页顶部找到矩阵函数使用方法的一般性介绍。


示例

在电子表格中,选择能够显示转置后数组的区域。如果原始数组包含 n 行 m 列,那么您选择的区域就至少需要包含 m 行 n 列。然后直接输入公式,选择原始数组并按「」组合键。或者,如果您使用的是「函数向导」,请标记「数组」复选框。转置后的数组就会出现在选中的目标区域内,并且自动保护,不会被更改。

A

B

C

D

1

2

3

4

5

2

6

7

8

9


上面的表格有两行 x 四列。要转置它,必须选择四行 x 两列。假设您希望将上表转置到范围 A7:B10 (四行,两列) 您必须选择整个范围,然后输入:

TRANSPOSE(A1:D2)

然后通过「」确保作为矩阵公式输入。结果如下:

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。

在此页面顶部有更多说明。

示例

选择一个用于显示回归数据的电子表格区域,选择函数,输入输出数据或利用鼠标将其选中,选中「矩阵」字段,然后点击「确定」,将显示根据输出数据计算的回归数据。