Этот раздел содержит примеры, которые демонстрируют дополнительные возможности формул массива, возвращающих несколько значений (формулы массива, которые вводятся в диапазон ячеек). Эти формулы массива открывают новые возможности: создание массивов из значений, использование операторов и функций, транспонирование массивов и генерирование последовательных чисел.
Создание массива на основе значений диапазона
Приведенная ниже формула создает массив значений в ячейках. На рисунке представлен рабочий лист с данными, введенными в диапазон A1:С4. Диапазон D8:F11 содержит формулу массива.
{=А1:С4}
Массив в диапазоне D8:F11 связан с диапазоном А1:С4. Внесение изменений в диапазон A1:С4 приведет к соответствующим изменениям в ячейках D8:F11.
Создание массива констант на основе значений диапазона
В предыдущем примере формула массива в D8:F11 была связана с диапазоном ячеек A1:С4. Существует возможность разорвать эту связь и создать массив констант, состоящий из значений в диапазоне A1:С4.
Для этого выделите ячейки, которые включают формулу массива (диапазон D8:F11 в этом примере). Нажмите <F2> для запуска режима редактирования, а затем <F9> – для преобразования ссылок на ячейки в значения. Воспользуйтесь клавишами <Ctrl+Shift+Enter> для фиксации формулы массива констант. Массив констант будет иметь следующий вид:
{1;“собака”;3:4;5;“кошка”:“обезьяна”;11 ;12}
На рисунке показано, как выглядит после описанных преобразований строка формул.
Выполнение операций над массивом
Представленная ниже формула массива создает прямоугольный массив, каждый элемент которого вычисляется путем умножения исходного массива на 2.
{={1;2;3;4:5;6;7;8:9;10;11;12}*2}
На рисунке показан результат ввода этой формулы в диапазон.
Следующая формула массива умножает каждый элемент массива на этот же элемент.
{={1;2;3;4:5;6;7;8:9;10;11;12}*{1;2;3;4:5;6;7;8:9;10;11;12}}
Приведенная ниже формула массива приводит к тому же результату, но более простым способом.
{={1;2;3;4:5;6;7;8:9;10;11;12}^2}
Если массив хранится в диапазоне ячеек (в данном случае А1:С4), для возведения в квадрат каждого элемента этого массива можно воспользоваться следующей формулой массива.
{=А1:С4^2}
В некоторых примерах фигурные скобки, использованные в формулах, вводятся программой автоматически после нажатия <Ctrl+Shift+Enter>, другие же нужно вводить вручную. Чтобы не запутаться, запомните следующий принцип: если открывающая фигурная скобка расположена до знака равенства, она вставляется автоматически, если после – ее следует вводить вручную.
Использование функций в операциях с массивами
Естественно, существует возможность использовать функции при работе с массивами. Например, можно создать формулу массива, которая вычисляет квадратный корень каждого из десяти элементов массива констант.
{=КОРЕНЬ({1:2:3:4:5:6:7:8:9:10})}
Если массив хранится в диапазоне (в данном случае А1:А10), можно вычислить квадратный корень каждого элемента, входящего в массив, с помощью следующей формулы массива.
{=КОРЕНЬ(А1:А10)}
Транспонирование массива
При транспонировании массива происходит преобразование строк в столбцы и столбцов в строки. Другими словами, таким образом можно преобразовать горизонтальный массив в вертикальный или наоборот. Для транспонирования в Excel используется специальная функция ТРАНСП.
Рассмотрим следующий одномерный горизонтальный массив:
{1;2;3;4;5}
Можно ввести данный массив в вертикальный диапазон ячеек, используя функцию ТРАНСП. Для этого выделите диапазон из пяти ячеек, который располагается в пяти строках и одном столбце. Затем введите приведенную ниже формулу и нажмите <Ctrl+Shift+Enter>.
{=ТРАНСП({1;2;3;4;5})}
Горизонтальный массив транспонируется, и его элементы появятся в вертикальном диапазоне.
Транспонирование двухмерных массивов происходит подобным образом. На рисунке показан двухмерный массив, введенный в диапазон ячеек без транспонирования, однако с использованием функции ТРАНСП. В диапазоне А1:D3 находится формула массива.
{={1;2;3;4:5;6;7;8:9;10;11;12}}
Диапазон А6:С9 содержит следующую формулу:
{=ТРАНСП({1;2;3;4:5;6;7;8:9;10;11;12})}
Как и раньше, функцию транспонирования можно применять и к массиву, хранящемуся в диапазоне ячеек. Например, в приведенной ниже формуле используется массив, хранящийся в диапазоне A1:С4 (четыре строки, три столбца). Формула массива вводится в диапазон из трех строк и четырех столбцов.
{=ТРАНСП(A1:С4)}
Обычные функции, возвращающие массив
Некоторые стандартные функции Excel используют массивы. Следует ввести формулу, в которой используется одна из этих функций, во множество ячеек, подобно тому, как и формулу массива. К таким функциями относятся: ПРЕДСКАЗ, ЧАСТОТА, РОСТ, ЛИНЕЙН, ЛГРФПРИБЛ, ТЕНДЕНЦИЯ и т.д.
Генерирование массива последовательных целых чисел
Для решения многих задач необходимо генерировать массивы, состоящие из последовательных целых чисел, а затем использовать их в формулах массива. Идеальным инструментом для этого является функция СТРОКА, возвращающая значение номера строки. Представленная формула введена в вертикальный диапазон из 12 ячеек.
{=СТРОКА(1:12)}
Эта формула генерирует массив из 12 элементов, содержащий целые числа от 1 до 12. Выделите диапазон, состоящий из 12 строк и 1 столбца, и введите формулу массива. Диапазон ячеек будет заполнен последовательными целыми числами.
Представленная выше формула используется для создания последовательности чисел, но она не идеальна. Чтобы узнать о недостатках ее использования, вставьте новую строку над диапазоном. Excel внесет изменения в формулу. Теперь она будет иметь следующий вид:
{=СТРОКА(2:13)}
Формула, которая изначально возвращала значения целых чисел от 1 до 12, теперь генерирует значения от 2 до 13.
Лучшим решением является другая формула:
{=СТРОКА(ДВССЫЛ(“1:12”))}
В этой формуле используется функция ДВССЫЛ, которая в качестве аргумента принимает текстовую последовательность. Excel не изменяет ссылку, содержащуюся в аргументе функции ДВССЫЛ. Поэтому данная формула массива всегда возвращает целые числа от 1 до 12.