В этом разделе рассматриваются вопросы выделения ячеек, содержащих массивы, а также обсуждаются проблемы ввода и редактирования формул массива. Эти процедуры имеют определенные отличия от методов управления обычными диапазонами ячеек и формулами.
Ввод формулы массива
Для того чтобы программа знала, какая вводится формула (массива или обычная), необходимо при вводе формулы массива в диапазон ячеек придерживаться определенных правил. Обычная формула вводится в ячейку путем нажатия <Enter>, а формула массива – путем нажатия <Ctrl+Shift+Enter>.
Формулу массива несложно узнать: в строке формул она заключена в фигурные скобки. Например, следующая формула является формулой массива:
{=СУММ(ДЛСТР(А1:А5))}
Не вводите фигурные скобки при создании формулы массива. Excel вставит их автоматически при нажатии <Ctrl+Shift+Enter>. Если результат вычисления формулы массива состоит из нескольких значений, необходимо вначале выделить весь диапазон ячеек, в который помещается результат, и только затем приступать к вводу формулы. Если этого не сделать, будет отображен только первый элемент возвращаемых значений.
Выделение диапазона формулы массива
Можно выделить диапазон ячеек, в котором находится формула массива, используя обычную процедуру выделения ячеек либо использовав следующие методы.
• Активизируйте любую ячейку в диапазоне формулы массива. Выберите команду Главная→Редактирование→Найти и выделить→Выделение группы ячеек или нажмите клавишу <F5>. Установите переключатель текущий массив. Щелкните на кнопке ОК для закрытия диалогового окна.
• Активизируйте любую ячейку в диапазоне формулы массива и нажмите <Ctrl+/> для выделения всего массива. Если включена русская раскладка клавиатуры, в которой косая черта приведена в верхнем регистре, нажмите <Ctrl+Shift+/>.
Редактирование формулы массива
Если формула массива находится в разных ячейках, то необходимо редактировать весь диапазон. Помните, что нельзя изменить содержимое только одного элемента формулы массива. Если попытаться это сделать, появится окно предупреждения.
Существует ряд правил, которых следует придерживаться при работе с формулой массива, расположенной в ячейках. Если не выполнить хотя бы одно из них, Excel сразу даст знать об этом.
• Нельзя изменять содержимое отдельной ячейки, входящей в формулу массива.
• Нельзя перемещать ячейки, которые входят в формулу массива (но можно переместить всю формулу массива).
• Нельзя удалять ячейки, которые входят в формулу массива (но можно удалить весь массив).
• Нельзя вставлять новые элементы в диапазон массива. Это правило распространяется и на вставку строк или столбцов, которые добавят новые ячейки в диапазон массива.
• Нельзя использовать многоячеечные формулы массива в таблице (имеется в виду таблица, созданная с помощью команды Вставка→Таблицы→Таблица). Аналогично, если диапазон содержит многоячеечную формулу массива, его нельзя преобразовать в таблицу.
Чтобы отредактировать формулы массива, выделите все ячейки его диапазона. Перейдите в строку формул, щелкнув в ней или нажав клавишу <F2>. Excel удалит фигурные скобки вокруг формулы. Отредактируйте формулу и нажмите <Ctrl+Shift+Enter> для сохранения внесенных изменений. Внесенные изменения будут отображены во всех ячейках массива.
Если после редактирования формулы массива по ошибке нажать <Ctrl+Enter> (вместо <Ctrl+Shift+Enter>), формула будет введена в каждую выделенную ячейку, но она уже не будет формулой массива. Соответственно, она, скорее всего, вернет неправильный результат. Чтобы исправить ошибку, выделите ячейки и нажмите сначала <F2>, а затем <Ctrl+Shift+Enter>.
Несмотря на то, что невозможно изменить отдельную ячейку, входящую в состав формулы массива, к ней можно применить особое форматирование.
Расширение или сокращение формулы массива
Часто возникает необходимость увеличить или уменьшить количество ячеек в формуле массива. Для этого потребуется выполнить следующие действия.
1. Выделите весь диапазон, содержащий формулу массива. Это можно сделать с помощью клавиш <Ctrl+/> (если включена русская раскладка клавиатуры, в которой символ косой черты расположен в верхнем регистре, нужно нажать <Ctrl+Shift+/>).
2. Нажмите <F2> для перехода в режим редактирования.
3. Нажмите <Ctrl+Enter>. Это действие вводит одну и ту же формулу (не формулу массива) в каждую выделенную ячейку.
4. Измените диапазон выделения, добавив или исключив ячейки.
5. Нажмите <F2>, чтобы вновь включить режим редактирования.
6. Нажмите <Ctrl+Shift+Enter>.
Недостатки формул массива
Конечно, главным преимуществом использования формул массива является возможность выполнения ранее недоступных методов вычислений. Преимущества применения массивов очевидны, однако есть и существенные недостатки.
Формулы массива являются одной из самых сложных возможностей Excel. Естественно, желательно избегать применения формул массива, если предполагается совместное редактирование рабочей книги. Многие пользователи Excel, сталкиваясь с формулами массива, чувствуют себя совершенно беспомощными.
Не забывайте нажимать <Ctrl+Shift+Enter> для ввода формулы массива. При редактировании существующего массива необходимо всякий раз использовать эту комбинацию клавиш после завершения редактирования. Помимо логических ошибок, вышеуказанная ошибка – наиболее распространенная среди пользователей. Если при редактировании формулы массива по ошибке нажата клавиша <Enter>, необходимо нажать <F2>, т.е. вернуться обратно в режим редактирования, и затем нажать <Ctrl+Shift+Enter>.
Другая потенциальная проблема, возникающая при работе с формулами массивов, – это замедление пересчета рабочего листа. Однако эта проблема ощутимо сказывается только при использовании очень больших массивов. Для ее разрешения потребуется высокопроизводительный компьютер. В то же время формулы массива позволяют получить необходимые данные быстрее, чем это возможно при применении пользовательских функций VBA.