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

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

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

Массив — это связанный диапазон ячеек со значениями на листе. Диапазон, состоящий из 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 и подтвердите этот ввод с помощью комбинации клавиш + Shift + Enter. В результате получится массив 3 x 3, отдельные ячейки в которого из диапазона (A1:C3) будут помножены на коэффициент 10.

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

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

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

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

Формулы массивов также позволяют экономить пространство при необходимости рассчитать несколько значений, так как эти формулы не отличаются высокими требованиями к памяти. Кроме того, массивы являются прекрасным инструментом для выполнения сложных расчётов, поскольку в вычисления можно включить несколько диапазонов ячеек. В LibreOffice имеются разнообразные математические функции для массивов, например, функция МУМНОЖ для умножения двух массивов или функция СУММПРОИЗВ для вычисления скалярных произведений двух массивов.

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

Неявное пересечение формул массива

Также можно создать и «обычную» формулу, в диапазоне ссылок которой, например, в составе параметров, будет указана формула массива. Такую формулу также называют «неявным пересечением» формулы массива. Получаемый результат пересекает диапазон ссылок со строками или столбцами, которые содержат формулу. При отсутствии пересечения или, когда диапазон пересечения занимает несколько строк или столбцов, появляется сообщение об ошибке #ЗНАЧ!. Пример ниже иллюстрирует эту идею:

В таблице выше поместите формулу массива в ячейку D1:

{=A1:A3 + 100}

Тогда ячейки D1, D2, D3, соответственно, получают значения 107, 195, 105.

Вставьте формулу в ячейку E2, но не в качестве формулы массива.

=A1:A3 + 100

Ячейки E1 и E3 будут пусты, а ячейка E2 получит значение 195. Это называется неявным пересечением формул массива.

Вставьте в ячейку E4 ниже такую же формулу, как и в E2.

=A1:A3 + 100

В ячейке E4 будет выведена ошибка #ЗНАЧ!. Строка 4 находится за пределами диапазона формулы A1:A3.

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

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

При вводе формулы массива непосредственно в ячейку, необходимо использовать комбинацию клавиш Shift + + Enter вместо клавиши Enter. Только в таком случае формула становится формулой массива.

Использование строчных констант-массивов в формулах

Calc поддерживает в формулах строчные матрицы/массивы констант. Строчный массив заключается в фигурные скобки «{» и «}». Элементы могут быть как числовыми (в т.ч. отрицательными), так и логическими (ИСТИНА, ЛОЖЬ), а также строковыми литералами. Выражения, не являющиеся константами, не допускаются. Возможен ввод массивов с одной или несколькими строками, а также с одним или несколькими столбцами. Все строки должны состоять из одинакового числа элементов; все столбцы также должны состоять из одинакового числа элементов.

Разделитель столбцов (отделяющий элементы строки друг от друга) и разделитель строк зависят от языка и локализации. Однако данная справка в качестве разделителей столбцов и строк использует символы точки с запятой «;» и конвейера «|» соответственно. Например, в английской локализации разделителем столбцов служит запятая «,», а точка с запятой «;» используется в качестве разделителя строк.

Использование вложенных массивов не допускается.

Примеры:

={1; 2; 3}

Массив с одной строкой, состоящей из трёх чисел: 1, 2 и 3.

Для ввода этого массива констант выбираются три ячейки в строке, затем вводится формула ={1;2;3} с использованием фигурных скобок и точек с запятой, после чего необходимо нажать + Shift + Enter.

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

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

={0; 1; 2|ЛОЖЬ; ИСТИНА; "два"}

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

=SIN({1; 2; 3})

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

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

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

2. Либо нажмите F2, либо расположите курсор на строке ввода. Оба эти действия позволяют отредактировать формулу.

3. После внесения изменений нажмите + Shift + Enter.

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

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

2. Нажмите Delete для удаления содержимого массива, включая формулу массива, либо нажмите Backspace, что приведёт к открытию диалогового окна Удалить содержимое. Выберите Формула или Удалить все и щёлкните OK.

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

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

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

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

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

5. Вставьте формулу нажатием клавиш + V в выбранное место и подтвердите операцию нажатием клавиш + Shift + Enter. Теперь выбранный диапазон содержит формулу массива.

Изменение диапазона массива

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

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

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

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

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

Условные расчёты массива представляют собой формулу массива или матрицы, содержащую функцию ЕСЛИ() или ВЫБОР(). Аргумент условия в формуле задан в виде ссылки на область или результата матрицы.

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

	A	B (формула)	B (результат)
1	1	{=ЕСЛИ(A1:A3>0; "да"; "нет")}	да
2	0	{=ЕСЛИ(A1:A3>0; "да"; "нет")}	нет
3	1	{=ЕСЛИ(A1:A3>0; "да"; "нет")}	да

Следующие функции обеспечивают принудительную обработку массива: КОРРЕЛ, КОВАР, ПРЕДСКАЗ, ФТЕСТ, ОТРЕЗОК, МОПРЕД, МОБР, МУМНОЖ, МОДА, ПИРСОН, ВЕРОЯТНОСТЬ, КВПИРСОН, НАКЛОН, СТОШYX, СУММПРОИЗВ, СУММРАЗНКВ, СУММСУММКВ, СУММКВРАЗН, ТТЕСТ. Если при вызове этих функций в качестве аргументов используются ссылки на область, функции считаются функциями массива. В следующей таблице приводится пример принудительной обработки массива.

	A	B (формула)	B (результат)	C (принудительная формула массива)	C (результат)
1	1	=A1:A2+1	2	=СУММПРОИЗВ(A1:A2+1)	5
2	2	=A1:A2+1	3	=СУММПРОИЗВ(A1:A2+1)	5
3		=A1:A2+1	#ЗНАЧ!	=СУММПРОИЗВ(A1:A2+1)	5

ЛГРФПРИБЛ

Эта функция служит для расчёта корректировки введённых данных в виде кривой экспоненциальной регрессии (y=b*m^x).

ЛГРФПРИБЛ(ДанныеY [; ДанныеX [; Тип_функции [; Статистика]]])

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

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

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

Статистика (необязательно): если этот параметр равен 0, рассчитывается только коэффициент регрессии.

В функциях LibreOffice Calc параметры, отмеченные, как "необязательные" могут быть пропущены, только если нет параметров, идущих после. Например, в функции с четырьмя параметрами, в которой последние два параметра "необязательные", вы можете пропустить 4-й параметр или 3-й и 4-й, но нельзя пропустить только 3-й параметр.

Больше пояснений в верхней части этой страницы.

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

ЛИНЕЙН

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

ЛИНЕЙН(данные Y [; данные X [; Тип линии [; статистика]]])

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

данные X: соответствующий диапазон из одной строки или столбца, указывающий координаты x. Если данные X не указаны, по умолчанию используется 1, 2, 3, ..., n. Если имеется более одного набора переменных, данные X могут быть диапазоном с соответствующим количеством строк или столбцов.

LINEST определяет наиболее соответствующую данным прямую линию y = a + bx, используя линейную регрессию (метод «наименьших квадратов»). Если имеется более одного набора переменных, прямая линия имеет форму y = a + b1x1 + b2x2 ... + bnxn.

Если Тип линии содержит значение ЛОЖЬ, то найденная прямая линия принудительно проводится через центр координат (константа a содержит ноль; y = bx). Если параметр не указан, Тип линии по умолчанию имеет значение ИСТИНА (линия не будет принудительно проводиться через центр координат).

Если параметр статистика пропущен или содержит значение ЛОЖЬ, то возвращается только верхняя строка статистики. Если параметр имеет значение ИСТИНА, возвращается вся таблица.

ЛИНЕЙН возвращает таблицу (массив) статистики, как указано ниже, и должен вводится как формула массива (например, с помощью + Shift + Enter, а не только с помощью Enter).

В функциях LibreOffice Calc параметры, отмеченные, как "необязательные" могут быть пропущены, только если нет параметров, идущих после. Например, в функции с четырьмя параметрами, в которой последние два параметра "необязательные", вы можете пропустить 4-й параметр или 3-й и 4-й, но нельзя пропустить только 3-й параметр.

Больше пояснений в верхней части этой страницы.

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

Формула в панели Формула соответствует каждой ячейке массива ЛИНЕЙН {=ЛИНЕЙН(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: КВПИРСОН

F4. Стандартная ошибка регрессии, рассчитанной для значения Y.

E5. Значение F, полученное с помощью дисперсионного анализа.

F5. Степени свободы, полученные с помощью дисперсионного анализа.

E6. Сумма квадратов отклонений для примерных значений Y от линейного среднего.

F6. Сумма квадратов отклонений для примерных значений Y от заданных значений Y.

Больше пояснений в верхней части этой страницы.

МЕДИН

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

МЕДИН(Размерности)

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

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

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

Также можно ввести формулу =MUNIT(5) в последней ячейке выбранного диапазона (E5), после чего нужно нажать .

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

Больше пояснений в верхней части этой страницы.

МОБР

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

МОБР(Массив)

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

Больше пояснений в верхней части этой страницы.

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

МОПРЕД

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

МОПРЕД(Массив)

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

Больше пояснений в верхней части этой страницы.

МУМНОЖ

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

МУМНОЖ(Массив 1; Массив 2)

Массив 1: первый массив для произведения массивов.

Массив 2: второй массив с тем же числом строк.

Выберите квадратный диапазон. Выберите функцию МУМНОЖ. Выберите Массив 1, затем выберите Массив 2. С помощью Мастера функций, установите флажок Массив. Щёлкните OK. Массив с результатом вычисления появится в первом выделенном диапазоне.

РОСТ

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

РОСТ(Данные Y [; [ Данные X ] [; [ Новые данные X ] [; Тип функции ] ] ])

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

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

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

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

В функциях LibreOffice Calc параметры, отмеченные, как "необязательные" могут быть пропущены, только если нет параметров, идущих после. Например, в функции с четырьмя параметрами, в которой последние два параметра "необязательные", вы можете пропустить 4-й параметр или 3-й и 4-й, но нельзя пропустить только 3-й параметр.

Больше пояснений в верхней части этой страницы.

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

СУММКВРАЗН

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

СУММКВРАЗН(Массив X; Массив Y)

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

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

Больше пояснений в верхней части этой страницы.

СУММПРОИЗВ

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

СУММПРОИЗВ(Массив 1[; Массив 2;][...;[Массив 255]])

Массив 1[; Массив 2;][...;[Массив 255]] представляет массивы, соответствующие элементы которых перемножаются.

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

	A	B	C	D
1	2	3	4	5
2	6	7	8	9
3	10	11	12	13

=СУММПРОИЗВ(A1:B3; C1:D3) возвращает 397.

Вычисление: A1*C1 + B1*D1 + A2*C2 + B2*D2 + A3*C3 + B3*D3

Функцию СУММПРОИЗВ можно использовать для расчёта скалярного произведения двух векторов.

Больше пояснений в верхней части этой страницы.

СУММРАЗНКВ

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

СУММРАЗНКВ(Массив X; Массив Y)

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

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

Больше пояснений в верхней части этой страницы.

СУММСУММКВ

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

СУММСУММКВ(Массив X; Массив Y)

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

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

Больше пояснений в верхней части этой страницы.

ТЕНДЕНЦИЯ

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

ТЕНДЕНЦИЯ(Данные Y [; Данные X [; Новые данные X [; Тип линии]]])

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

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

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

Тип линии (необязательно). Если Тип линии = 0, то будут рассчитываться линии, проходящие через ноль. В противном случае рассчитываться будут и линии со смещением. По умолчанию Тип линии <> 0.

В функциях LibreOffice Calc параметры, отмеченные, как "необязательные" могут быть пропущены, только если нет параметров, идущих после. Например, в функции с четырьмя параметрами, в которой последние два параметра "необязательные", вы можете пропустить 4-й параметр или 3-й и 4-й, но нельзя пропустить только 3-й параметр.

Больше пояснений в верхней части этой страницы.

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

ТРАНСП

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

ТРАНСП(Массив)

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

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

	A	B	C	D
1	2	3	4	5
2	6	7	8	9

Таблица вверху содержит 2 строки, 4 столбца. Для её транспонирования необходимо выбрать 4 строки, 2 столбца. Предполагается необходимость транспонировать представленную выше таблицу в диапазон A7:B10 (4 строки, 2 столбца) при этом необходимо выбрать весь диапазон, а затем ввести следующее:

ТРАНСП(A1:D2)

Затем важно ввести эту формулу как формулу матрицы с помощью клавиш . Результат будет выглядеть следующим образом:

	A	B
7	2	6
8	3	7
9	4	8
10	5	9

ЧАСТОТА

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

ЧАСТОТА(Данные; Классы)

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

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

В приведённой ниже таблице в столбце A содержится список несортированных значений измерения. В столбце B содержится верхний предел для классов, на которые требуется разделить данные из столбца A. В соответствии с пределом в ячейке B1 функция ЧАСТОТА возвращает количество значений, которые меньше либо равны 5. Предел в ячейке B2 равен 10, поэтому функция ЧАСТОТА возвращает количество значений, которые больше 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. Вызовите функцию ЧАСТОТА с помощью Мастера функций. Выделите диапазон Данных в массиве (A1:A11), а затем диапазон Классов, в массиве (B1:B6). Установите флажок Массив и нажмите кнопку ОК. Частота отображается в диапазоне C1:C6.

Больше пояснений в верхней части этой страницы.