База Знаний: Функции Calc. Использование массивов
 |
Формулы массивов требуют значительных вычислительных ресурсов и существенно замедляют работу компьютера. |
Множества могут быть полезными в сокращении количества используемых вами ячеек, но они могут быть немного сложны для понимания. До некоторой степени, они расширяют то, что электронная таблица может сделать сверх того, для чего были предназначены электронные таблицы. Решение почти всех задачи возможно без их использования.
Массив — просто прямоугольный информационный блок, которым InfraOffice.pro Calc может манипулировать в формуле — то есть, это — информация, организованная в строки и столбцы. Массив может быть ячейками в электронной таблице, или может содержаться внутри Calc.
Есть 2 метода определения массива в формуле:
- как диапазон — например A2:C3;
- как «встроенный массив», например {1; 5; 3 | 6; 2; 4} (они полностью функциональны начиная с OpenOffice.org 2.4, но существуют и в более ранних версиях — см. «Проблемы» далее по тексту). Вы вводите фигурные скобки { } вокруг встроенного массива. Элементы в строке разделяются точкой с запятой “;”, а строки — символом вертикальной черты “|”. Каждая строка должна иметь одно и то же число элементов (неправильно записать {1; 2; 3 | 4; 5}, потому что есть 3 элемента в первой строке и только 2 в следующей). Встроенный массив может иметь смешанное содержимое, например {4; 2; “собака” | -22; “кот”; 0}. Однако он не может содержать ссылки (например, A4), или формулы (например, PI (), 2 * 3), или проценты (например, 5%).
Вы можете дать имя к диапазону ячеек: выберите диапазон и выполните Вставка → Названия → Определить….
Вы можете дать имя встроенному массиву: выберите Вставка → Названия → Определить…; введите массив (например, {1; 3; 2}, включая фигурные скобки) в поле Назначен на .
Функции, понимающие массивы в качестве параметров
Некоторые функции, такие как SUM(), AVERAGE(), MATCH(), LOOKUP(), принимают один или более своих параметров в виде массива.
Например:
- SUM(A2:C3) возвращает сумму чисел в диапазоне A2:C3;
- SUM({3; 2; 4}), возвращает 9, сумму чисел в встроенном массиве {3; 2; 4};
- SUM также ожидает / понимает одиночные значения («скаляр») — SUM(B5; 7), возвращает сумму значения ячейки B5 и 7.
Функции, не ожидающие в качестве параметров массивы
Некоторые функции, такие как ABS(), SQRT(), COS(), LEN() ожидают, что их параметры «скаляры» — то есть, одиночные значения, такие как 354 или «собака» или содержимое ячейки, например, B5.
Например:
- SQRT(4) возвращает 2;
- LEN(“собака”) возвращает 6.
Однако, нельзя использовать массив там, где ожидается одиночное значение — например, SQRT({9; 4}). Если ввести формулу «обычно», нажав ↵ Enter , то InfraOffice.pro Calc в этом случае вычислит формулу, используя одиночное значение из массива следующим образом:
- Если это встроенный массив, Calc будет использовать первое значение («верхнее, левое»).
Пример:
=SQRT({9; 4 | 25; 16}), после нажатия ↵ Enter возвращает 3, квадратный корень первого элемента в массиве (9). - Если это диапазон:
- Calc возвратит ошибку, если массив содержит не одну строку или один столбец;
- Для диапазона из одной строки или одного столбца, Calc будет использовать значение, где столбец / строка ячейки формулы пересекается с массивом (или возвратит ошибку, если такого пересечения нет).
Например:
Формула =ABS(B2:B5) введена '”обычно» в ячейке D3, которая находится в строке 3. Строка 3 пересекает B2:B5 в ячейке B3, таким образом вычисляется формула =ABS (B3).
Формула =LEN(B5:D5) введена «обычно» в ячейке B1, которая находится в столбце B. Столбец B пересекает диапазон B5:D5 в ячейке B5, таким образом вычисляется формула =LEN (B5).
Формулы массивов
Действительная мощь массивов проявляется, когда формула вводится специальным способом, как «формула массива». Делается это, нажимая Ctrl + ⇧ Shift + ↵ Enter вместо ↵ Enter (или помечая флажок Массив , если вы используете Мастер функций).
Если в ячейке B1 ввести ={3; 4}, «обычно» нажимая ↵ Enter , первое значение 3 отображается в ячейке.
Если в ячейке B2 ввести ={3; 4}, но нажимаете Ctrl + ⇧ Shift + ↵ Enter вместо ↵ Enter , ячейка становится «формулой массива». Формула теперь возвращает весь массив {3; 4}. Ячейка B2 отображает 3, а ячейка C2 — 4.
Заметьте, что, если ввести формулу, используя клавишу ↵ Enter , просто выбрав ячейку и нажав Ctrl + ⇧ Shift + ↵ Enter , ячейка не преобразуется в выражение массива — для этого необходимо выполнить фактическое редактирование (например, добавив, а затем удалив символ), или запустить Мастера Функций и пометить флажок Массив .
Если теперь попробовать отредактировать ячейку B2, будет выдано сообщение, что «Вы не можете изменить только часть массива». Чтобы отредактировать массив, необходимо выбрать весь массив, или при помощи мыши или нажав Ctrl + / (удерживая клавишу Ctrl нажать клавишу / ).
Панель формул указывает, что формула — формула массива, заключая её в фигурные скобки {}. Их не нужно вводить с клавиатуры — они исчезнут, при редактировании формулы, и Calc отобразит их снова по окончании редактирования.
Вычисления формулы массива
Когда InfraOffice.pro Calc вычисляет формулу массива, он рассматривает «непредвиденный массив» как ряд значений (вместо того, чтобы использовать одиночное значение), вычисляя результат для каждого из элементов массива, и возвращая массив результатов.
Например:
=SQRT({16; 4; 25}), когда введено нажатием Ctrl + ⇧ Shift + ↵ Enter вместо ↵ Enter , возвращает массив результатов, с 1 строкой и 3 столбцами — {4; 2; 5}. Если формула находится в ячейке B2, Calc помещает результаты в ячейки B2:D2. 4 помещается в B2, 2 — в C2 и 5 — в D2.
Процесс в действительности работает следующим образом:
- Все «непредвиденные массивы» в одном и том же вычислении массива должны быть одинакового размера;
- Результат будет возвращён в виде массива того же размера;
- Вычисление выполняется для каждого элемента по очереди, с результатом, возвращаемым в соответствующем элементе производимого массива.
Например, с формулой массива =SQRT({16; 4; 25}):
- Есть только один массив, с 1 строкой и 3 столбцами;
- Результат будет возвращён в массиве с 1 строкой и 3 столбцами;
- Вычисление выполняется сначала для 16, затем для 4, и затем для 25, давая в результате массив {4; 2; 5}.
Например, с формулой множества =SQRT({8 | 18} * 2) в ячейке A5:
- Есть только один массив, с 2 строками и 1 столбцом;
- Результат будет возвращён в массиве с 2 строками и 1 столбцом;
- Вычисление: первый элемент SQRT(8*2) = 4; второй элемент SQRT(18*2) = 6; результирующий массив, таким образом, {4 | 6} — то есть, 4 в ячейке A5 и 6 в ячейке A6.
Результат выражения массива — массив, который может использоваться в пределах формулы.
Например, =SUM(SQRT({16; 4; 25})) как формула массива. Вычисление SQRT({16; 4; 25}), как и прежде, приводит к результату {4; 2; 5}, таким образом давая SUM({4; 2; 5}), которая возвращает конечный результат в ячейке 4+2+5 = 11.
Например, с формулой массива =SUM(IF(A1:A4> 0; B1:B4; 0))
- Два массива A1:A4 и B1:B4 и имеют 4 строки и 1 столбец.
- Результатом обработки массива функцией IF() получается массив того же размера, который будет суммировать SUM();
- Если A1>0 первый элемент — B1; иначе 0. Если A2> 0 второй элемент — B2; иначе 0.... Массив предоставляемый SUM(), имеет значения в B1:B4, когда смежные значения в A1:A4 — >0. Окончательный результат — сумма значений в B1:B4, для которых смежные значения в A1:A4 — >0.
Функции массива
Некоторые функции вычисляют свой результат в виде массива. Если они должны возвратить массив, они должны быть введены как формула массива, нажатием Ctrl + ⇧ Shift + ↵ Enter (или установкой флажка Массив , если используется Мастер формул). Если, взамен, они будут введены «обычно», клавишей ↵ Enter , то будет возвращён только (единственный) верхний левый элемент рассчитанного массива. Вот эти функции: FREQUENCY, GROWTH, LINEST, LOGEST, MDETERM, MINVERSE, MMULT, MUNIT, SUMPRODUCT, SUMX2MY2, SUMX2PY2, SUMXMY2, TRANSPOSE, TREND.
Например: =MUNIT(2) введённая как формула массива в ячейку B1 возвращает единичную матрицу 2 × 2 в виде массива {1; 0 | 0; 1} — то есть, ячейки B1, B2, C1, C2 отображают 1, 0, 0, 1. Если эта формула введена «обычно», только «верхнее, левое» значение (1) возвращается в ячейке B1.
Некоторые функции (включая некоторые упомянутые выше) берут параметры, которые вынуждены оценивать как формулу массива, даже если формула вводится «обычно»: MDETERM, MINVERSE, MMULT, SUMPRODUCT, SUMX2MY2, SUMX2PY2, SUMXMY2, CORREL, COVAR, FORECAST, FTEST, INTERCEPT, MODE, PEARSON, PROB, RSQ, SLOPE, STEYX, TTEST.
Например: MODE(ABS(A1:A3)) (в нормальном режиме) вынуждает ABS(A1:A3) оцениваться как формулу массива, возвращая массив абсолютных значений A1:A3, из которого MODE выбирает наиболее распространённое значение, которое и будет возвращено.
Например: PROB(A1:A5; B1:B5/100; 3) (в нормальном режиме) заставляет B1:B5/100 оцениваться как формула массива, возвращая массив, где каждое значение является сотой частью из значений в B1:B5 (возможно, потому, что вероятности выражаются в процентах).
Некоторые из этих функций имеют некоторые параметры (массивы), которые вынуждены оценивать в виде формулы массива, а другие параметры (одиночное значение), которые оценивают обычно.
Например: PROB(A1:A5; B1:B5; {3; 4}) (в нормальном режиме) не оценивает {3; 4} как массив, потому что это означало бы, что PROB должна возвратить массив. Она вычисляет PROB(A1:A5; B1:B5; 3) — то есть, она интерпретирует {3; 4} в скалярном режиме, беря «верхнее левое» значение 3. Однако, если она введена как формула массива, она конечно возвратит массив.
Проблемы
Встроенные массивы работают в OpenOffice.org 2.3, но вызывают ошибку, если есть какие-нибудь пробелы или отрицательные числа. Это установлено для OpenOffice.org 2.4, хотя пробелы удаляются, а не игнорируются.
Можно будет включать в формулу массивы различного размера, как определено процесс вычисления в готовящемся международном стандарте ODFF.
Некоторые функции все же не могут использоваться в формулах массивов: COUNTIF, SUMIF, MATCH, ISLOGICAL.
OFFSET имеет ограниченное поведение в пределах формулы массива.
Имена, определённые через Вставка → Названия → Определить…, могут использоваться в пределах формул массивов, но надписи (добавленные через Вставка → Названия → Подписи или автоматически распознанные в заголовке столбца), не должны использоваться.
В MS Excel, не все функции могут использоваться в формулах массивов. Например CONCATENATE работает в InfraOffice.pro Calc, но не в MS Excel. Знайте об ограничениях MS Excel, если планируется использовать электронную таблицу Calc в MS Excel.
Изменчивые функции, такие как RAND, RANDBETWEEN, NOW не могут повторно вычисляться в формулах массивов; например в {=A1:A3+RAND()} порождается только одно случайное число.
InfraOffice.pro 3.1.x
