Excel. Некоторые примеры использования формул массива

Если ранее вы не сталкивались с формулами массива, рекомендую прочитать:

После этого вы, вероятно, сможете оценить возможности и достоинства формул массива, и вам захочется узнать о них побольше. Если вас не нужно агитировать, и вы полны желания разобраться в этом мощном оружии Excel, но не знаете, с чего начать, прочитайте Введение в формулы массива.

Основное достоинство формул массивов состоит в том, что они позволяют выполнять очень широкий круг вычислений, который другими способами выполнить нельзя. К сожалению, формулы массивов – это наиболее сложное и непонятное средство Excel.

Если вы уже постигли азы, предлагаю вам продолжить знакомство с формулами массива вместе с Джоном Уокенбахом и его книгой MS Excel 2007. Библия пользователя. – М.: Издательский дом «Вильямс», 2008. – 816 с.

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

На рис. 1 показан рабочий лист, содержащий данные в диапазоне A1:C4. Диапазон D8:F11 содержит массив, созданный на основе этих данных с помощью формулы

Рис. 1. Создание массива на основе значений ячеек диапазона

Массив в диапазоне D8:F11 связан со значениями диапазона A1:C4. Если изменить какое-либо значение в последнем диапазоне, то автоматически изменится соответствующее значение в массиве D8:F11.

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

В предыдущем примере формула массива в ячейках D8:F11, по существу, являлась ссылкой на диапазон A1:C4. Чтобы «разорвать» эту связь и создать массив констант, выполните ряд действий.

1. Выделите диапазон, содержащий формулу массива (в этом примере – D8:F11).
2. Нажмите клавишу , чтобы перейти в режим редактирования формулы.
3. Нажмите клавишу , чтобы преобразовать ссылки на ячейки в соответствующие значения.
4. Нажмите комбинацию клавиш .

Теперь в диапазоне D8:F11 находится следующий массив: . На рис. 2 показано, как этот массив выглядит в строке формул.

Рис. 2. После нажатия клавиши в строке формул отобразится массив констант

Следующая формула массива создает прямоугольный массив и умножает каждый его элемент на 2: *2> (рис. 3)

Следующая формула умножает каждый элемент массива на самого себя: *. Эту формулу можно переписать в более компактном виде: ^2>. Если массив хранится в диапазоне ячеек (допустим, А1:С4), то подобная формула возвратит квадрат каждого элемента этого массива: (рис. 4).

В операциях над массивами можно использовать функции. Следующая формула массива, которую нужно ввести в вертикальный диапазон, состоящий из 10 ячеек, вычисляет квадратные корни каждого элемента массива: <=КОРЕНЬ(. Если массив хранится в диапазоне ячеек (например, А1:А10), подобная формула выполнит эти же вычисления для каждого элемента массива: (рис. 5).

Рис. 5. Извлечение квадратного корня из элементов массива

Транспонирование двухмерных массивов выполняется подобным образом. На рис. 7 показан двухмерный массив в диапазоне A1:D3. Формула создает в диапазоне F1:H4 транспонированный массив.

Генерирование последовательности натуральных чисел

Рис. 8. Последовательности натуральных чисел на основе функции СТРОКА

Если вы поэкспериментируете с приведенной формулой, то обнаружите присущий ей недостаток: при добавлении новой строки выше диапазона, где расположена эта формула, Excel изменит номера строк, и формула автоматически преобразуется в формулу, возвращающую значения от 2 до 13: (рис. 9).

Рис. 9. Добавление строки над массивом изменяет ряд чисел

Приведем формулу массива, которая лишена этого недостатка: . Эта формула использует функцию ДВССЫЛ, аргументом которой является текстовая строка. Excel не изменяет этот аргумент при вставке или удалении строк на рабочем листе, поэтому данная формула всегда возвращает правильный результат, состоящий из чисел от 1 до 12 (рис. 10).

Рис. 10. Последовательность натуральных чисел на основе функции ДВССЫЛ не меняется при добавлении строк над массивом

Формулы массивов для отдельных ячеек

До этого рассматривались формулы массивов, вносимые в диапазоны, состоящие из нескольких ячеек. Но настоящая мощь формул массивов проявляется тогда, когда они вводятся в отдельную ячейку.

Подсчет количества символов в диапазоне

Рис. 11. Одна формула массива заменяет целый ряд отдельных формул

Суммирование трех наименьших значений диапазона

Рис. 12. Формула массива возвращает сумму трех наименьших значений поименованного диапазона Данные (A1:А10)

Подсчет количества ячеек, содержащих текст

Рис. 13. Формула массива возвращает число ячеек диапазона, содержащих текст

Следующая формула выполняет туже работу, но записана в более компактном виде . Здесь вместо функции ЕСЛИ используется тот факт, что ИСТИНА*1=1, а ЛОЖЬ*1=0

Рис. 14. Вычисление среднего с помощью промежуточных формул и с помощью одной формулы массива

Аналогично создаются и работают формулы массивов, вычисляющие различные характеристики диапазонов данных. Например, следующая формула находит максимальное число среди разностей значений диапазонов С2:С11 и В2:В11: . Эта формула вернет число 27. А эта формула возвращает наименьшее число разностей значений двух диапазонов. Формула вернет число -8.

Использование массивов вместо ссылок на диапазоны

Если в формулах используются ссылки на диапазоны, то их можно заменить на массивы констант. Это очень удобно, если значения в диапазонах, на которые указывают ссылки, не будут изменяться. [1]

Рис. 15. Таблицу, в которой производится поиск, можно заменить массивом констант

[1] К сожалению, в функциях, работающих с базами данных (например, БДСУММ), нельзя заменить ссылку на диапазон, содержащий критерии, массивом констант. Работа с динамическим многомерным массивом синтаксически полностью совпадает с работой с многомерным C-массивом. (Цитирую предыдущий раздел.) Синтаксически — да, но между этими массивами есть глубокое различие, о котором начинающие программисты часто забывают.

Многомерные массивы в C — практическое пособие

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

Как работать с двумерным массивом?

Как и другие переменные, двумерные массивы мы можем инициализировать при их объявлении. Для этого нам нужно указать все элементы массива. Делается это следующим образом.

Записанный выше код создает массив с двумя строчками и четырьмя столбцами, и присваивает каждому элементу определенные значения. Получится вот такой массив.

Кроме того, мы можем задать только некоторые элементы массива, тогда остальные будут заполнены нулями. Например:

Понятно, что если у нас будет большой массив, то мы замучаемся так все записывать. Да и редко такое бывает нужно.

Было бы гораздо удобнее, если бы элементы массива нумеровались начиная с 1, а не с 0. VBA позволяет задавать начальное число для элементов массива, используя директиву компилятора OptioNBase для указания того, должна ли нумерация индексов начинаться с 0 или с 1.

Операции с массивами и матрицами в MS Excel

Это моя личная ссылка-приглашение на Stepik для вас. Регистрируясь по этой ссылке, записываясь на курсы и решая задачи, Вы помогаете автору данного сайта принять участие в конкурсе платформы Stepik! Подробности конкурса здесь: https://vk.cc/75rKuS