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

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

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

Массив - связанный диапазон ячеек на листе, содержащих какие-либо значения. Диапазон, состоящий из 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 можно использовать сложение (+), вычитание (-), умножение (*), деление (/), возведение в степень (^), конкатенацию (&) и сравнение (=, <>, <, >, <=, >=). Эти операторы можно применять к отдельным значениям диапазона в виде формулы массива для получения массива результатов.

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

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

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

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

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

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

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

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

Если вводить формулу массива напрямую в ячейку, вместо клавиши ВВОД необходимо воспользоваться комбинацией клавиш SHIFT++ВВОД. Только после этого формула станет формулой массива.

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

В 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+ВВОД.

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

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

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

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

=SIN({1;2;3})

Вводится как матричная формула, возвращает три результата функции SIN с аргументами 1, 2 и 3.

Редактирование формул массива

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

  2. Нажмите клавишу F2 или поместите курсор в строку ввода. В результате формула станет доступной для редактирования.

  3. После внесения изменений нажмите +SHIFT+ВВОД.

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

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


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

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

  2. Нажмите клавишу F2 или поместите курсор в строку ввода.

  3. Скопируйте формулу в строку ввода, нажав +C.

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

  5. Вставьте формулу, нажав +V в выбранном месте, и подтвердите вставку, нажав +SHIFT+ВВОД. Выбранный диапазон теперь содержит формулу массива.

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

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

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

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

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

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


Можно создать копию формулы массива в данном диапазоне, удерживая клавишу .

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

Условные расчеты массива - формула массива или матрицы, которая содержит функцию 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

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

Синтаксис

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

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

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

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

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


Пример

В приведенной ниже таблице в столбце 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

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

Синтаксис

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

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

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

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

Тип функции(необязательно). Если тип функции = 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.

Пример

Эта функция возвращает массив и обрабатывается так же, как и другие функции массива. Выделите диапазон для отображения результатов и выберите функцию. Выберите "ДанныеY". Введите другие параметры, установите флажок Массив и нажмите кнопку ОК.

LINEST

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

Синтаксис

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 возвращает таблицу (массив) статистических данных, как показано ниже, и должна быть введена в виде формулы массива (например, при помощи комбинации клавиш +SHIFT+ВВОД вместо одной клавиши ВВОД).

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.

Пример

Эта функция возвращает массив и обрабатывается так же, как и другие функции массива. Выделите диапазон для возвращаемых значений, а затем выберите функцию. Выберите данные_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, как показано в примере.

Формула на панели Формула соответствует каждой ячейке матрицы функции LINEST {=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).

Синтаксис

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.

Пример

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

MDETERM

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

Синтаксис

MDETERM(Массив)

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

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

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


More explanations on top of this page.

MINVERSE

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

Синтаксис

MINVERSE(Массив)

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

More explanations on top of this page.

Пример

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

MMULT

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

Синтаксис

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

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

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

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

More explanations on top of this page.


Пример

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

MUNIT

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

Синтаксис

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

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

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

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


Пример

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

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

Можно также ввести формулу =Munit(5) в последнюю ячейку выбранного диапазона (E5) и нажать комбинацию клавиш .

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

More explanations on top of this page.

SUMPRODUCT

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

Синтаксис

SUMPRODUCT(Массив1; Массив2... Массив30)

Массив1, Массив2 ... Массив30 - массивы, элементы которых требуется умножить.

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

Пример

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(Массив X; Массив Y)

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

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

More explanations on top of this page.

SUMX2PY2

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

Синтаксис

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

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

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

More explanations on top of this page.

SUMXMY2

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

Синтаксис

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

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

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

More explanations on top of this page.

TRANSPOSE

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

Синтаксис

TRANSPOSE(Массив)

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

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

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


Пример

В электронной таблице выберите диапазон, в котором может появиться транспонированный массив. Если оригинальный массив содержит 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(ДанныеY; ДанныеX; Новые данныеX; Тип линии)

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

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

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

Тип линии(необязательно). Если тип линии = 0, линии считаются проходящими через начало координат. В противном случае линии смещаются. Тип линии по умолчанию <> 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.

Пример

Выделите на листе диапазон для отображения данных тренда. Выберите функцию. Введите исходящие данные или выберите их с помощью мыши. Установите флажок Массив и нажмите кнопку ОК. Будут отображены данные тренда для исходящих данных.