Функции массива

В эту категорию входят функции массива.

Что такое массив?

Массив - связанный диапазон ячеек на листе, содержащих какие-либо значения. Диапазон, состоящий из 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 (массив из двух смежных ячеек).

Что такое формула массива?

Формула, которая служит для оценки отдельных значений в диапазоне ячеек, называется формулой массива. Различие между формулой массива и другими типами формул заключается в том, что формула массива одновременно обрабатывает несколько значений.

Формула массива может не только обрабатывать, но и возвращать несколько значений. Результаты формулы массива также организованы в массив.

To multiply the values in the individual cells by 10 in the above array, you do not need to apply a formula to each individual cell or value. Instead you just need to use a single array formula. Select a range of 3 x 3 cells on another part of the spreadsheet, enter the formula =10*A1:C3 and confirm this entry using the key combination + Shift + Enter. The result is a 3 x 3 array in which the individual values in the cell range (A1:C3) are multiplied by a factor of 10.

Кроме умножения, к диапазону ссылки (массиву) можно применять и другие операторы. В LibreOffice Calc можно использовать сложение (+), вычитание (-), умножение (*), деление (/), возведение в степень (^), конкатенацию (&) и сравнение (=, <>, <, >, <=, >=). Эти операторы можно применять к отдельным значениям диапазона в виде формулы массива для получения массива результатов.

Операторы сравнения в формуле массива обрабатывают пустые ячейки так же, как и в обыкновенной формуле, т. е. либо как нуль, либо как пустую строку. Например, если ячейки A1 и A2 являются пустыми, формулы массива =A1:A2 =""} и =A1:A2=0} возвращают массив шириной в 1 столбец и высотой в 2 ряда, состоящий из ячеек со значением TRUE.

Когда следует использовать формулы массива?

Формулы массива можно использовать, когда требуется выполнить расчеты с разными значениями. Если метод расчета нужно изменить, следует только изменить формулу массива. Чтобы добавить формулу массива, выделите весь массив и внесите в формулу массива необходимые изменения.

Формулы массивов также позволяют экономить пространство, если требуется рассчитать несколько значений, поскольку эти формулы не отличаются высокими требованиями к памяти. Кроме того, массивы представляют собой ценный инструмент для сложных расчетов, поскольку в вычисления можно включить несколько диапазонов ячеек. В LibreOffice имеются разнообразные математические функции для массивов, например, функция MMULT для умножения двух массивов или функция SUMPRODUCT для вычисления скалярных произведений двух массивов.

Использование формул массива в LibreOffice Calc

Можно также создать обычную формулу, в которой диапазоны ссылки, например, параметры, указывают на формулу массива. Результат рассчитывается для пересечения массива ссылки и строк или столбцов, содержащих формулу. Если пересечение отсутствует или массив на пересечении включает несколько строк или столбцов, отображается сообщение об ошибке "#ЗНАЧ!". Такое использование формул демонстрируется в следующем примере.

Создание формул массива

При создании формулы массива с помощью мастера функций следует каждый раз устанавливать флажок Массив, чтобы результаты возвращались в виде массива. В противном случае будет возвращаться только значение в верхней левой ячейке массива.

If you enter the array formula directly into the cell, you must use the key combination Shift + + Enter instead of the Enter key. Only then does the formula become an array formula.

Значок примечания

В 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.

To enter this array constant, you select three cells in a row, then you type the formula ={1;2;3} using the curly braces and the semicolons, then press + Shift + Enter.

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

Массив с двумя строками и тремя значениями в каждой строке.

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

Массив смешанных данных.

=SIN({1;2;3})

Вводится как матричная формула, возвращает три результата функции SIN с аргументами 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. Either press F2 or position the cursor in the input line. Both of these actions let you edit the formula.

  3. After you have made changes, press + Shift + Enter.

Значок подсказки

Отдельные части массива доступны для форматирования. Например, можно изменить цвет шрифта. Выделите диапазон ячеек и измените нужный атрибут.


Копирование формул массива

  1. Выделите диапазон ячеек или массив, содержащий формулу массива.

  2. Either press F2 or position the cursor in the input line.

  3. Copy the formula into the input line by pressing + C.

  4. Select a range of cells where you want to insert the array formula and either press F2 or position the cursor in the input line.

  5. Paste the formula by pressing + V in the selected space and confirm it by pressing + Shift + Enter. The selected range now contains the array formula.

Изменение массива

Для редактирования конечного массива следует выполнить следующие действия.

  1. Выделите диапазон ячеек или массив, содержащий формулу массива.

  2. В правом нижнем углу выделенной области появится небольшой значок, с помощью которого диапазон можно увеличить или уменьшить.

Значок примечания

При изменении массива формула не изменяется. Изменяется только диапазон, в котором будут отображаться результаты.


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

Условные расчеты массива

Условные расчеты массива - формула массива или матрицы, которая содержит функцию IF() или CHOOSE(). Аргумент условия в формуле представляет собой ссылку на область или результат в виде матрицы.

В приведенном ниже примере проверка >0 в формуле {=IF(A1:A3>0;"да";"нет")} применяется к каждой ячейке в диапазоне A1:A3, а результат копируется в соответствующую ячейку.

A

B (формула)

B (результат)

1

1

{=IF(A1:A3>0;"да";"нет")}

да

2

0

{=IF(A1:A3>0;"да";"нет")}

нет

3

1

{=IF(A1:A3>0;"да";"нет")}

да


Следующие функции обеспечивают обязательную обработку массива: 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

#ЗНАЧ

=SUMPRODUCT(A1:A2+1)

5


FREQUENCY

Indicates the frequency distribution in a one-column-array. The function counts the number of values in the Data array that are within the values given by the Classes array.

Syntax

FREQUENCY(Данные; Классы)

Данные: ссылка на значения для подсчета.

Классы: массив предельных значений.

Значок примечания

Общие сведения о функциях массива см. в верхней части страницы.


Example

В приведенной ниже таблице в столбце A содержится список несортированных измеренных значений. В столбце B содержится верхний предел для классов, на которые требуется разделить данные из столбца A. В соответствии с пределом в ячейке B1 функция FREQUENCY возвращает количество значений, которые меньше либо равны 5. Предел в ячейке B2 равен 10, поэтому функция FREQUENCY возвращает количество значений, которые больше 5 или больше либо равны 10. Текст в ячейке B6 - ">25" - используется в качестве примера.

А

В

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.

More explanations on top of this page.

GROWTH

Служит для расчета точек экспоненциального тренда в массиве.

Syntax

GROWTH(ДанныеY; ДанныеX; Новые данныеX; Тип функции)

ДанныеY - массив данных Y.

ДанныеX (необязательно) - массив данных X.

Новые данныеX (необязательно) - массив данных X, в котором выполняется повторный расчёт значений.

FunctionType (optional). If FunctionType = 0, functions in the form y = m^x will be calculated. Otherwise, y = b*m^x functions will be calculated.

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.

Example

This function returns an array and is handled in the same way as the other array functions. Select a range where you want the answers to appear and select the function. Select DataY. Enter any other parameters, mark Array and click OK.

LINEST

Возвращает таблицу статистических данных для прямой линии, которая больше всего соответствует набору данных.

Syntax

LINEST(данные_Y; данные_X; Тип линии; статистика)

данные_Y - это диапазон из одной строки или столбца, указывающий координаты y в виде набора точек данных.

данные_X - это соответствующий диапазон из одной строки или столбца, указывающий координаты x. Если данные_X не указаны, по умолчанию используется 1, 2, 3, ..., n. Если имеется более одного набора переменных, данные_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 returns a table (array) of statistics as below and must be entered as an array formula (for example by using + Shift + Return rather than just 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.

Example

Эта функция возвращает массив и обрабатывается так же, как и другие функции массива. Выделите диапазон для возвращаемых значений, а затем выберите функцию. Выберите данные_Y. При необходимости можно ввести и другие параметры. Установите флажок Массив и нажмите кнопку ОК.

Возвращаемые результаты будут включать по меньшей мере наклон линии регрессии и точку её пересечения с осью Y (если для параметра статистика указано значение 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 выполнялась правильно, следует установить флажок Массив в мастере функций. Затем необходимо выделить в таблице (или ввести вручную) следующие значения:

Данные_Y - C2:C8

Данные_X - A2:B8

Тип линии и статистика имеют значение 1.

После нажатия кнопки ОК LibreOffice Calc заполняет выделенный диапазон результатами функции LINEST, как показано в примере.

The formula in the Formula bar corresponds to each cell of the LINEST array {=LINEST(C2:C8;A2:B8;1;1)}.

Ниже представлены результаты функции LINEST.

E2 и F2: наклон линии регрессии y=b+m*x для значений x1 и x2. Значения даются в обратном порядке; т. е. наклон для x2 в ячейке E2 и наклон для x1 в ячейке F2.

G2. Пересечение линии b с осью Y.

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).

Syntax

LOGEST(ДанныеY; ДанныеX; Тип функции; Статистика)

ДанныеY - массив данных Y.

ДанныеX (необязательно) - массив данных X.

Тип функции (необязательно). Если тип функции = 0, будут вычислены функции в форме y = m^x. В противном случае будут вычислены функции y = b*m^x.

Статистика (необязательно): если этот параметр равен 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.

Example

См. функцию LINEST. Однако в данном случае сумма квадратов не рассчитывается.

MDETERM

Возвращает определитель матрицы. Эта функция возвращает значение в текущую ячейку. Для получения результатов необязательно выделять диапазон.

Syntax

MDETERM(Массив)

Массив: квадратный массив для получения определителей.

Значок примечания

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


More explanations on top of this page.

MINVERSE

Возвращает обратную матрицу.

Syntax

MINVERSE(Массив)

Массив: квадратный массив для получения обратной матрицы.

More explanations on top of this page.

Example

Выделите квадратный диапазон и выберите функцию MINVERSE. Выберите конечный массив, установите флажок Массив и нажмите клавишу ОК.

MMULT

Служит для расчета произведения двух массивов. Количество столбцов в массиве 1 должно совпадать с количеством строк в массиве 2. В квадратном массиве количество строк и столбцов совпадает.

Syntax

MMULT(Массив; Массив)

Массив (первый). Первый массив для расчета произведения.

Массив (второй). Второй массив с тем же количеством строк.

Значок примечания

More explanations on top of this page.


Example

Выделите квадратный диапазон. Выберите функцию MMULT. Выберите первый массив, затем - второй массив. В мастере функций установите флажок Массив. Нажмите кнопку OK. Конечный массив будет отображен в первом выделенном диапазоне.

MUNIT

Вычисляет единичную матрицу заданного размера. Единичная матрица - квадратный массив, в котором элементы по главной диагонали равны 1, а все остальные элементы равны 0.

Syntax

MUNIT(Размерности)

Размерности: размер единичной матрицы.

Значок примечания

Общие сведения о функциях массива см. в верхней части страницы.


Example

Выделите на листе квадратный диапазон, например, A1:E5.

Не отменяя выделения, выберите функцию MUNIT. Установите флажок Массив. Введите размерность для единичной матрицы (в данном случае 5) и нажмите кнопку OK.

You can also enter the =MUNIT(5) formula in the last cell of the selected range (E5), and press .

В результате диапазон A1:E5 становится единичной матрицей.

More explanations on top of this page.

SUMPRODUCT

Служит для умножения соответствующих элементов в заданных массивах; возвращает сумму произведений.

Syntax

SUMPRODUCT(Array1; Array2; ...; Array30)

Array1, Array2; ...; Array30 represent arrays whose corresponding elements are to be multiplied.

В список аргументов должен входить хотя бы один массив. Если указан только один массив, суммируются все его элементы.

Example

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

Возвращает сумму разниц квадратов соответствующих значений в двух массивах.

Syntax

SUMX2MY2(Массив X; Массив Y)

МассивX - первый массив, элементы которого необходимо возвести в квадрат и сложить.

Массив Y - второй массив, элементы которого необходимо возвести в квадрат и вычесть.

More explanations on top of this page.

SUMX2PY2

Возвращает сумму сумм квадратов соответствующих значений в двух массивах.

Syntax

SUMX2PY2(МассивX; МассивY)

МассивX - первый массив, элементы которого необходимо возвести в квадрат и сложить.

Массив Y: второй массив, элементы которого необходимо возвести в квадрат и суммировать.

More explanations on top of this page.

SUMXMY2

Служит для сложения квадратов разниц между соответствующими значениями в двух массивах.

Syntax

SUMXMY2(МассивX; МассивY)

МассивX - первый массив, элементы которого необходимо вычесть и возвести в квадрат.

МассивY - второй массив, элементы которого необходимо вычесть и возвести в квадрат.

More explanations on top of this page.

TRANSPOSE

Служит для транспонирования строк и столбцов массива.

Syntax

TRANSPOSE(Массив)

Массив: массив для транспонирования.

Значок примечания

Общие сведения об использовании функций массива см. в верхней части страницы.


Example

In the spreadsheet, select the range in which the transposed array can appear. If the original array has n rows and m columns, your selected range must have at least m rows and n columns. Then enter the formula directly, select the original array and press . Or, if you are using the Function Wizard, mark the Array check box. The transposed array appears in the selected target range and is protected automatically against changes.

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

Возвращает значения по линейному тренду.

Syntax

TREND(ДанныеY; ДанныеX; Новые данныеX; Тип линии)

ДанныеY - массив данных Y.

ДанныеX (необязательно) - массив данных X.

Новые данныеX (необязательно) - массив данных X, которые используются для повторного расчета значений.

LinearType (optional). If LinearType = 0, then lines will be calculated through the zero point. Otherwise, offset lines will also be calculated. The default is 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.

Example

Select a spreadsheet range in which the trend data will appear. Select the function. Enter the output data or select it with the mouse. Mark the Array field, click OK. The trend data calculated from the output data is displayed.

Please support us!