Array Functions
В эту категорию входят функции массива.
Что такое массив?
Массив - связанный диапазон ячеек на листе, содержащих какие-либо значения. Диапазон, состоящий из 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 (массив из двух смежных ячеек).
Что такое формула массива?
Формула, которая служит для оценки отдельных значений в диапазоне ячеек, называется формулой массива. Различие между формулой массива и другими типами формул заключается в том, что формула массива одновременно обрабатывает несколько значений.
Формула массива может не только обрабатывать, но и возвращать несколько значений. Результаты формулы массива также организованы в массив.
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 ряда, состоящий из ячеек со значением ИСТИНА.
Когда следует использовать формулы массива?
Формулы массива можно использовать, когда требуется выполнить расчёты с разными значениями. Если метод расчёта нужно изменить, следует только изменить формулу массива. Чтобы добавить формулу массива, выделите весь массив и внесите в формулу массива необходимые изменения.
Формулы массивов также позволяют экономить пространство, если требуется рассчитать несколько значений, поскольку эти формулы не отличаются высокими требованиями к памяти. Кроме того, массивы представляют собой ценный инструмент для сложных расчётов, поскольку в вычисления можно включить несколько диапазонов ячеек. В LibreOffice имеются разнообразные математические функции для массивов, например, функция МУМНОЖ для умножения двух массивов или функция СУММПРОИЗВ для вычисления скалярных произведений двух массивов.
Использование формул массива в LibreOffice Calc
Implicit intersection of array formulas
You can also create a "normal" formula in which the reference range, such as parameters, indicate an array formula. This formula is also known as "implicit intersection" of array formula. The result is obtained from the intersection of the reference range and the rows or columns in which the formula is found. If there is no intersection or if the range at the intersection covers several rows or columns, a #VALUE! error message appears. The following example illustrates this concept:
In the table above, place the array formula in D1:
{=A1:A3 + 100}
Cells D1, D2, D3 have values 107, 195, 105 respectively.
Insert the formula below in E2, do not enter as array formula.
=A1:A3 + 100
Cells E1 and E3 are empty, Cell E2 has value 195. This is the implicit intersection of array formulas.
Insert formula below in E4, as in E2.
=A1:A3 + 100
Cell E4 display #VALUE!. Row 4 is out of the range A1:A3 of the formula.
Создание формул массива
При создании формулы массива с помощью мастера функций следует каждый раз устанавливать флажок Массив, чтобы результаты возвращались в виде массива. В противном случае будет возвращаться только значение в верхней левой ячейке массива.
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 формулы массива заключаются в фигурные скобки. Введение фигурных скобок вручную невозможно.
The cells in a results array are automatically protected against changes. However, you can edit, delete or copy the array formula by selecting the entire array cell range.
Использование строчных констант-массивов в формулах
Calc поддерживает в формулах строчные матрицы/массивы констант. Строчный массив заключается в фигурные скобки «{» и «}». Элементы могут быть как числовыми (в т.ч. отрицательными), так и логическими (ИСТИНА, ЛОЖЬ) или строковыми литералами. Выражения, не являющиеся константами, не допускаются. Возможен ввод массивов с одной или несколькими строками, а также с одним или несколькими столбцами. Все строки должны состоять из одинакового числа элементов; все столбцы также должны состоять из одинакового числа элементов.
Разделитель столбцов (отделяющий элементы строки друг от друга) и разделитель строк зависят от языка и локализации. Однако в этой справке в качестве разделителей столбцов и строк используются символы точки с запятой «;» и конвейера «|» соответственно. Например, в английской локализации разделителем столбцов служит запятая «,», а точка с запятой «;» используется в качестве разделителя строк.
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|ЛОЖЬ; ИСТИНА; "два"}
Массив смешанных данных.
=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.
Отдельные части массива доступны для форматирования. Например, можно изменить цвет шрифта. Выделите диапазон ячеек и измените нужный атрибут.
Deleting Array Formulae
-
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.
-
Press Delete to delete the array contents, including the array formula, or press Backspace and this brings up the Delete Contents dialog box. Select Formula or Delete All and click OK.
Копирование формул массива
-
Выделите диапазон ячеек или массив, содержащий формулу массива.
-
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 |
{=ЕСЛИ(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).
LOGEST(DataY [; DataX [; FunctionType [; Stats]]])
ДанныеY - массив данных Y.
ДанныеX (необязательно) - массив данных X.
Тип функции (необязательно). Если тип функции = 0, будут вычислены функции в форме y = m^x. В противном случае будут вычислены функции y = b*m^x.
Статистика (необязательно): если этот параметр равен 0, рассчитывается только коэффициент регрессии.
В функциях LibreOffice Calc параметры, отмеченные, как "необязательные" могут быть пропущены, только если нет параметров, идущих после. Например, в функции с четырьмя параметрами, в которой последние два параметра "необязательные", вы можете пропустить 4-й параметр или 3-й и 4-й, но нельзя пропустить только 3-й параметр.
Больше пояснений в верхней части этой страницы.
См. функцию LINEST. Однако в данном случае сумма квадратов не рассчитывается.
ЛИНЕЙН
Возвращает таблицу статистических данных для прямой линии, которая больше всего соответствует набору данных.
LINEST(data_Y [; data_X [; linearType [; stats]]])
данные_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: КВПИРСОН
F4. Стандартная ошибка регрессии, рассчитанной для значения Y.
E5. Значение F, полученное с помощью дисперсионного анализа.
F5. Степени свободы, полученные с помощью дисперсионного анализа.
E6. Сумма квадратов отклонений для примерных значений Y от линейного среднего.
F6. Сумма квадратов отклонений для примерных значений Y от заданных значений Y.
Больше пояснений в верхней части этой страницы.
МЕДИН
Вычисляет единичную матрицу заданного размера. Единичная матрица - квадратный массив, в котором элементы по главной диагонали равны 1, а все остальные элементы равны 0.
МЕДИН(Размерности)
Размерности: размер единичной матрицы.
Общие сведения о функциях массива см. в верхней части страницы.
Выделите на листе квадратный диапазон, например, A1:E5.
Не отменяя выделения, выберите функцию МЕДИН. Установите флажок Массив. Введите размерность для единичной матрицы (в данном случае 5) и нажмите кнопку ОК.
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 становится единичной матрицей.
Больше пояснений в верхней части этой страницы.
МОБР
Возвращает обратную матрицу.
МОБР(Массив)
Массив: квадратный массив для получения обратной матрицы.
Больше пояснений в верхней части этой страницы.
Выделите квадратный диапазон и выберите функцию МОБР. Выберите конечный массив, установите флажок Массив и нажмите клавишу ОК.
МОПРЕД
Возвращает определитель матрицы. Эта функция возвращает значение в текущую ячейку. Для получения результатов необязательно выделять диапазон.
МОПРЕД(Массив)
Массив: квадратный массив для получения определителей.
You can find a general introduction to using Array functions on top of this page.
Больше пояснений в верхней части этой страницы.
МУМНОЖ
Служит для расчёта произведения двух массивов. Количество столбцов в массиве 1 должно совпадать с количеством строк в массиве 2. В квадратном массиве количество строк и столбцов совпадает.
МУМНОЖ(Массив 1; Массив 2)
Array 1 represents the first array used in the array product.
Array 2 represents the second array with the same number of rows.
Больше пояснений в верхней части этой страницы.
Select a square range. Choose the MMULT function. Select Array 1, then select Array 2. Using the Function Wizard, mark the Array check box. Click OK. The output array will appear in the first selected range.
РОСТ
Служит для расчёта точек экспоненциального тренда в массиве.
GROWTH(DataY [; [ DataX ] [; [ NewDataX ] [; FunctionType ] ] ])
Данные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.
СУММКВРАЗН
Служит для сложения квадратов разниц между соответствующими значениями в двух массивах.
СУММКВРАЗН(МассивX; МассивY)
МассивX - первый массив, элементы которого необходимо вычесть и возвести в квадрат.
МассивY - второй массив, элементы которого необходимо вычесть и возвести в квадрат.
Больше пояснений в верхней части этой страницы.
СУММПРОИЗВ
Служит для умножения соответствующих элементов в заданных массивах; возвращает сумму произведений.
SUMPRODUCT(Array 1[; Array 2;][...;[Array 255]])
Array 1[; Array 2;][...;[Array 255]] represent arrays whose corresponding elements are to be multiplied.
At least one array must be part of the argument list. If only one array is given, all array elements are summed. If more than one array is given, they must all be the same size.
|
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: второй массив, элементы которого необходимо возвести в квадрат и суммировать.
Больше пояснений в верхней части этой страницы.
ТЕНДЕНЦИЯ
Возвращает значения по линейному тренду.
TREND(DataY [; DataX [; NewDataX [; LinearType]]])
Данные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.
ТРАНСП
Служит для транспонирования строк и столбцов массива.
ТРАНСП(Массив)
Массив: массив для транспонирования.
Общие сведения об использовании функций массива см. в верхней части страницы.
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:
ТРАНСП(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 |
ЧАСТОТА
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.
ЧАСТОТА(Данные; Классы)
Данные: ссылка на значения для подсчёта.
Классы: массив предельных значений.
This function ignores any text or empty cell within a data range. If you suspect wrong results from this function, look for text in the data ranges. To highlight text contents in a data range, use the value highlighting feature.
Общие сведения о функциях массива см. в верхней части страницы.
В приведённой ниже таблице в столбце 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.
Больше пояснений в верхней части этой страницы.