Функции массива
В эту категорию входят функции массива.
Что такое массив?
Массив - связанный диапазон ячеек на листе, содержащих какие-либо значения. Диапазон, состоящий из 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 CommandCtrl + 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 + CommandCtrl + 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 LibreOffice - PreferencesTools - Options - 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 CommandCtrl + Shift + Enter.
={1;2;3|4;5;6}
Массив с двумя строками и тремя значениями в каждой строке.
={0;1;2|FALSE;TRUE;"two"}
Массив смешанных данных.
=SIN({1;2;3})
Вводится как матричная формула, возвращает три результата функции SIN с аргументами 1, 2 и 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.
-
Either press F2 or position the cursor in the input line. Both of these actions let you edit the formula.
-
After you have made changes, press CommandCtrl + Shift + Enter.
Отдельные части массива доступны для форматирования. Например, можно изменить цвет шрифта. Выделите диапазон ячеек и измените нужный атрибут.
Копирование формул массива
-
Выделите диапазон ячеек или массив, содержащий формулу массива.
-
Either press F2 or position the cursor in the input line.
-
Copy the formula into the input line by pressing CommandCtrl + C.
-
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.
-
Paste the formula by pressing CommandCtrl + V in the selected space and confirm it by pressing CommandCtrl + Shift + Enter. The selected range now contains the array formula.
Изменение массива
Для редактирования конечного массива следует выполнить следующие действия.
-
Выделите диапазон ячеек или массив, содержащий формулу массива.
-
В правом нижнем углу выделенной области появится небольшой значок, с помощью которого диапазон можно увеличить или уменьшить.
При изменении массива формула не изменяется. Изменяется только диапазон, в котором будут отображаться результаты.
By holding down the CommandCtrl 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.
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.
Больше пояснений в верхней части этой страницы.
GROWTH
Служит для расчета точек экспоненциального тренда в массиве.
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.
В функциях LibreOffice Calc параметры, отмеченные, как "необязательные" могут быть пропущены, только если нет параметров, идущих после. Например, в функции с четырьмя параметрами, в которой последние два параметра "необязательные", вы можете пропустить 4-й параметр или 3-й и 4-й, но нельзя пропустить только 3-й параметр.
Больше пояснений в верхней части этой страницы.
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
Возвращает таблицу статистических данных для прямой линии, которая больше всего соответствует набору данных.
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 CommandCtrl + Shift + Return rather than just Return).
В функциях 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, как показано в примере.
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.
Больше пояснений в верхней части этой страницы.
LOGEST
Эта функция служит для расчета корректировки для введенных данных в виде кривой экспоненциальной регрессии (y=b*m^x).
LOGEST(ДанныеY; ДанныеX; Тип функции; Статистика)
ДанныеY - массив данных Y.
ДанныеX (необязательно) - массив данных X.
Тип функции (необязательно). Если тип функции = 0, будут вычислены функции в форме y = m^x. В противном случае будут вычислены функции y = b*m^x.
Статистика (необязательно): если этот параметр равен 0, рассчитывается только коэффициент регрессии.
В функциях LibreOffice Calc параметры, отмеченные, как "необязательные" могут быть пропущены, только если нет параметров, идущих после. Например, в функции с четырьмя параметрами, в которой последние два параметра "необязательные", вы можете пропустить 4-й параметр или 3-й и 4-й, но нельзя пропустить только 3-й параметр.
Больше пояснений в верхней части этой страницы.
См. функцию LINEST. Однако в данном случае сумма квадратов не рассчитывается.
MDETERM
Возвращает определитель матрицы. Эта функция возвращает значение в текущую ячейку. Для получения результатов необязательно выделять диапазон.
MDETERM(Массив)
Массив: квадратный массив для получения определителей.
You can find a general introduction to using Array functions on top of this page.
Больше пояснений в верхней части этой страницы.
MINVERSE
Возвращает обратную матрицу.
MINVERSE(Массив)
Массив: квадратный массив для получения обратной матрицы.
Больше пояснений в верхней части этой страницы.
Выделите квадратный диапазон и выберите функцию MINVERSE. Выберите конечный массив, установите флажок Массив и нажмите клавишу ОК.
MMULT
Служит для расчета произведения двух массивов. Количество столбцов в массиве 1 должно совпадать с количеством строк в массиве 2. В квадратном массиве количество строк и столбцов совпадает.
MMULT(Массив; Массив)
Массив (первый). Первый массив для расчета произведения.
Массив (второй). Второй массив с тем же количеством строк.
Выделите квадратный диапазон. Выберите функцию MMULT. Выберите первый массив, затем - второй массив. В мастере функций установите флажок Массив. Нажмите кнопку OK. Конечный массив будет отображен в первом выделенном диапазоне.
MUNIT
Вычисляет единичную матрицу заданного размера. Единичная матрица - квадратный массив, в котором элементы по главной диагонали равны 1, а все остальные элементы равны 0.
MUNIT(Размерности)
Размерности: размер единичной матрицы.
Общие сведения о функциях массива см. в верхней части страницы.
Выделите на листе квадратный диапазон, например, A1:E5.
Не отменяя выделения, выберите функцию MUNIT. Установите флажок Массив. Введите размерность для единичной матрицы (в данном случае 5) и нажмите кнопку OK.
You can also enter the =MUNIT(5) formula in the last cell of the selected range (E5), and press Shift + Command + EnterShift + Ctrl + Enter.
В результате диапазон A1:E5 становится единичной матрицей.
Больше пояснений в верхней части этой страницы.
SUMPRODUCT
Служит для умножения соответствующих элементов в заданных массивах; возвращает сумму произведений.
SUMPRODUCT(Array1; Array2; ...; Array30)
Array1, Array2; ...; Array30 represent arrays whose corresponding elements are to be multiplied.
В список аргументов должен входить хотя бы один массив. Если указан только один массив, суммируются все его элементы.
|
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(Массив X; Массив Y)
МассивX - первый массив, элементы которого необходимо возвести в квадрат и сложить.
Массив Y - второй массив, элементы которого необходимо возвести в квадрат и вычесть.
Больше пояснений в верхней части этой страницы.
SUMX2PY2
Возвращает сумму сумм квадратов соответствующих значений в двух массивах.
SUMX2PY2(МассивX; МассивY)
МассивX - первый массив, элементы которого необходимо возвести в квадрат и сложить.
Массив Y: второй массив, элементы которого необходимо возвести в квадрат и суммировать.
Больше пояснений в верхней части этой страницы.
SUMXMY2
Служит для сложения квадратов разниц между соответствующими значениями в двух массивах.
SUMXMY2(МассивX; МассивY)
МассивX - первый массив, элементы которого необходимо вычесть и возвести в квадрат.
МассивY - второй массив, элементы которого необходимо вычесть и возвести в квадрат.
Больше пояснений в верхней части этой страницы.
TRANSPOSE
Служит для транспонирования строк и столбцов массива.
TRANSPOSE(Массив)
Массив: массив для транспонирования.
Общие сведения об использовании функций массива см. в верхней части страницы.
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 Shift + Command + EnterShift + Ctrl + Enter. 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 Shift + Command + EnterShift + Ctrl + Enter. 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, которые используются для повторного расчета значений.
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.
В функциях LibreOffice Calc параметры, отмеченные, как "необязательные" могут быть пропущены, только если нет параметров, идущих после. Например, в функции с четырьмя параметрами, в которой последние два параметра "необязательные", вы можете пропустить 4-й параметр или 3-й и 4-й, но нельзя пропустить только 3-й параметр.
Больше пояснений в верхней части этой страницы.
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.