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 (масив з двох суміжних комірок).

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

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

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

Для множення значень окремих комірок на 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 рядки, що складається з комірок зі значенням TRUE.

Коли слід використовувати формули масиву?

Формули масиву можна використовувати, коли потрібно виконати розрахунки з різними значеннями. Якщо метод розрахунку потрібно змінити, слід тільки змінити формулу масиву. Щоб додати формулу масиву, виберіть весь масив і внесіть у формулу масиву потрібні зміни.

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

Використання формул масиву в 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.

Створення формул масиву

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

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

note

В LibreOffice Calc формули масиву беруться у фігурні дужки. Ввести фігурні дужки вручну неможливо.


warning

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.


Using Inline Array Constants in Formulas

Calc supports inline matrix/array constants in formulas. An inline array is surrounded by curly braces '{' and '}'. Elements can be each a number (including negatives), a logical constant (TRUE, FALSE), or a literal string. Non-constant expressions are not allowed. Arrays can be entered with one or more rows, and one or more columns. All rows must consist of the same number of elements, all columns must consist of the same number of elements.

The column separator (separating elements in one row) and the row separator are language and locale dependent. But in this help content, the ';' semicolon and '|' pipe symbol are used to indicate the column and row separators, respectively. For example, in the English locale, the ',' comma is used as the column separator, while the ';' semicolon is used as the row separator.

tip

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;"два"}

Масив змішаних даних.

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

tip

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


Deleting Array Formulae

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

Копіювання формул масиву

  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. У правому нижньому куті вибраної ділянки з'явиться невеликий значок, за допомогою якого діапазон можна збільшити або зменшити.

note

При зміні масиву формула не змінюється. Змінюється тільки діапазон, в якому будуть виводитися результати.


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(Дані; Класи)

Дані: посилання на значення для підрахунку.

Класи: масив граничних значень.

note

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.


note

Загальні відомості про функції масиву наведено вгорі сторінки.


Example

У наведеній нижче таблиці в стовпці A міститься несортований список результатів вимірювань. У стовпці B містяться верхні межі для класів, на які потрібно розділити дані зі стовпця A. Відповідно до межі в комірці B1 функція FREQUENCY повертає кількість значень, які менші або дорівнюють 5. Межа в комірці B2 дорівнює 10, тому функція FREQUENCY повертає кількість значень, які більші від 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. Викличте функцію FREQUENCY за допомогою помічника функцій. Виберіть діапазон даних у масиві (A1:A11), а потім діапазон класів у масиві (B1:B6). Встановіть прапорець Масив і натисніть кнопку Гаразд. Частоти буде виведено в діапазоні C1:C6.

Більше пояснень – вгорі цієї сторінки.

GROWTH

Calculates the points of an exponential trend in an array.

Syntax

GROWTH(DataY [; [ DataX ] [; [ NewDataX ] [; FunctionType ] ] ])

DataY represents the Y Data array.

DataX (optional) represents the X Data array.

NewDataX (optional) represents the X data array, in which the values are recalculated.

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 необов'язкові параметри можна залишати порожніми лише тоді, коли після них немає інших параметрів. Наприклад, якщо функція має чотири параметри і два останні параметри не є обов'язковими, то можна залишити порожніми параметри 3 і 4 або параметр 4. Залишити порожнім параметр 3 не можна.

Більше пояснень – вгорі цієї сторінки.

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

Returns a table of statistics for a straight line that best fits a data set.

Syntax

LINEST(data_Y [; data_X [; linearType [; stats]]])

data_Y is a single row or column range specifying the y coordinates in a set of data points.

data_X is a corresponding single row or column range specifying the x coordinates. If data_X is omitted it defaults to 1, 2, 3, ..., n. If there is more than one set of variables data_X may be a range with corresponding multiple rows or columns.

LINEST finds a straight line y = a + bx that best fits the data, using linear regression (the "least squares" method). With more than one set of variables the straight line is of the form 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).

У функціях LibreOffice Calc необов'язкові параметри можна залишати порожніми лише тоді, коли після них немає інших параметрів. Наприклад, якщо функція має чотири параметри і два останні параметри не є обов'язковими, то можна залишити порожніми параметри 3 і 4 або параметр 4. Залишити порожнім параметр 3 не можна.

Більше пояснень – вгорі цієї сторінки.

Example

This function returns an array and is handled in the same way as the other array functions. Select a range for the answers and then the function. Select data_Y. If you want, you can enter other parameters. Select Array and click OK.

The results returned by the system (if stats = 0), will at least show the slope of the regression line and its intersection with the Y axis. If stats does not equal 0, other results are to be displayed.

Other LINEST Results:

Examine the following examples:

A

B

C

D

E

F

G

1

x1

x2

y

LINEST value

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


Column A contains several X1 values, column B several X2 values and column C the Y values. You have already entered these values in your spreadsheet. You have now set up E2:G6 in the spreadsheet and activated the Function Wizard. For the LINEST function to work, you must have marked the Array check box in the Function Wizard. Next, select the following values in the spreadsheet (or enter them using the keyboard):

data_Y is C2:C8

data_X is A2:B8

linearType and stats are both set to 1.

As soon as you click OK, LibreOffice Calc will fill the above example with the LINEST values as shown in the example.

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

This represents the calculated LINEST values:

E2 and F2: Slope m of the regression line y=b+m*x for the x1 and x2 values. The values are given in reverse order; that is, the slope for x2 in E2 and the slope for x1 in F2.

G2: Intersection b with the y axis.

E3 and F3: The standard error of the slope value.

G3: The standard error of the intercept

E4: RSQ

F4: The standard error of the regression calculated for the Y value.

E5: The F value from the variance analysis.

F5: The degrees of freedom from the variance analysis.

E6: The sum of the squared deviation of the estimated Y values from their linear mean.

F6: The sum of the squared deviation of the estimated Y value from the given Y values.

Більше пояснень – вгорі цієї сторінки.

LOGEST

This function calculates the adjustment of the entered data as an exponential regression curve (y=b*m^x).

Syntax

LOGEST(DataY [; DataX [; FunctionType [; Stats]]])

DataY represents the Y Data array.

DataX (optional) represents the X Data array.

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

Stats (optional). If Stats=0, only the regression coefficient is calculated.

У функціях LibreOffice Calc необов'язкові параметри можна залишати порожніми лише тоді, коли після них немає інших параметрів. Наприклад, якщо функція має чотири параметри і два останні параметри не є обов'язковими, то можна залишити порожніми параметри 3 і 4 або параметр 4. Залишити порожнім параметр 3 не можна.

Більше пояснень – вгорі цієї сторінки.

Example

See LINEST. However, no square sum will be returned.

MDETERM

Returns the array determinant of an array. This function returns a value in the current cell; it is not necessary to define a range for the results.

Syntax

MDETERM(Array)

Array represents a square array in which the determinants are defined.

note

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


Більше пояснень – вгорі цієї сторінки.

MINVERSE

Повертає обернену матрицю.

Syntax

MINVERSE(Array)

Array represents a square array that is to be inverted.

Більше пояснень – вгорі цієї сторінки.

Example

Select a square range and select MINVERSE. Select the output array, select the Array field and click OK.

MMULT

Calculates the array product of two arrays. The number of columns for array 1 must match the number of rows for array 2. The square array has an equal number of rows and columns.

Syntax

MMULT(Array 1; Array 2)

Array 1 represents the first array used in the array product.

Array 2 represents the second array with the same number of rows.

note

Більше пояснень – вгорі цієї сторінки.


Example

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.

MUNIT

Returns the unitary square array of a certain size. The unitary array is a square array where the main diagonal elements equal 1 and all other array elements are equal to 0.

Syntax

MUNIT(Dimensions)

Dimensions refers to the size of the array unit.

note

You can find a general introduction to Array functions at the top of this page.


Example

Select a square range within the spreadsheet, for example, from A1 to E5.

Without deselecting the range, select the MUNIT function. Mark the Array check box. Enter the desired dimensions for the array unit, in this case 5, and click OK.

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

You now see a unit array with a range of A1:E5.

Більше пояснень – вгорі цієї сторінки.

SUMPRODUCT

Multiplies corresponding elements in the given arrays, and returns the sum of those products.

Syntax

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.

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

Calculation: A1*C1 + B1*D1 + A2*C2 + B2*D2 + A3*C3 + B3*D3

You can use SUMPRODUCT to calculate the scalar product of two vectors.

note

SUMPRODUCT returns a single number, it is not necessary to enter the function as an array function.


Більше пояснень – вгорі цієї сторінки.

SUMX2MY2

Returns the sum of the difference of squares of corresponding values in two arrays.

Syntax

SUMX2MY2(ArrayX; ArrayY)

ArrayX represents the first array whose elements are to be squared and added.

ArrayY represents the second array whose elements are to be squared and subtracted.

Більше пояснень – вгорі цієї сторінки.

SUMX2PY2

Returns the sum of the sum of squares of corresponding values in two arrays.

Syntax

SUMX2PY2(ArrayX; ArrayY)

ArrayX represents the first array whose elements are to be squared and added.

ArrayY represents the second array, whose elements are to be squared and added.

Більше пояснень – вгорі цієї сторінки.

SUMXMY2

Adds the squares of the variance between corresponding values in two arrays.

Syntax

SUMXMY2(ArrayX; ArrayY)

ArrayX represents the first array whose elements are to be subtracted and squared.

ArrayY represents the second array, whose elements are to be subtracted and squared.

Більше пояснень – вгорі цієї сторінки.

TRANSPOSE

Transposes the rows and columns of an array.

Syntax

TRANSPOSE(Array)

Array represents the array in the spreadsheet that is to be transposed.

note

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


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

Returns values along a linear trend.

Syntax

TREND(DataY [; DataX [; NewDataX [; LinearType]]])

DataY represents the Y Data array.

DataX (optional) represents the X Data array.

NewDataX (optional) represents the array of the X data, which are used for recalculating values.

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 необов'язкові параметри можна залишати порожніми лише тоді, коли після них немає інших параметрів. Наприклад, якщо функція має чотири параметри і два останні параметри не є обов'язковими, то можна залишити порожніми параметри 3 і 4 або параметр 4. Залишити порожнім параметр 3 не можна.

Більше пояснень – вгорі цієї сторінки.

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.

Будь ласка, підтримайте нас!