Использование многоячеечных формул массива

Этот раздел содержит примеры, которые демонстрируют дополнительные возможности формул массива, возвращающих несколько значений (формулы массива, которые вводятся в диапазон ячеек). Эти формулы массива открывают новые возможности: создание массивов из значений, использование операторов и функций, транспонирование массивов и генерирование последовательных чисел.

Создание массива на основе значений диапазона

Приведенная ниже формула создает массив значений в ячейках. На рисунке представлен рабочий лист с данными, введенными в диапазон 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.

В начало

Полезное

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *