Создание формулы массива с одной ячейкой

1. В ячейке A13 книги введите текст Итоги продаж.
2. В ячейке B13 введите следующую формулу (и нажмите клавиши CTRL+SHIFT+ВВОД):

=СУММ(C2:C11*D2:D11)

В этом случае будет выполнено перемножение значений массива (диапазон ячеек C2 \endash D11), а затем \endash при помощи функции СУММА \endash сложение полученных результатов. Общий итог продаж составит 111 800 рублей. В данном примере демонстрируется, какие широкие возможности предоставляет данный тип формул. Например, предположим, что у вас есть 15 000 строк с данными. Вы можете частично или полностью сложить их значения, создав формулу массива в отдельной ячейке.

Кроме того, обратите внимание на то, что формула с одной ячейкой (в ячейке B13) полностью независима от формулы с несколькими ячейками (формула в ячейках E2—E11). Это указывает на еще одно преимущество использования формул массива — их гибкость. Можно выполнять с данными любые действия, например изменить формулы в столбце E или вообще удалить этот столбец, и это не повлияет на формулу с одной ячейкой.

Ниже перечислены дополнительные преимущества, которые обеспечивает использование формул массива.

• Согласованность Если щелкнуть несколько ячеек ниже ячейки E2, вы увидите, что все они содержат одну и ту же формулу. Такая согласованность помогает обеспечить более высокую точность результатов.
• Безопасность Компонент формулы массива с несколькими ячейками нельзя переписать. Например, щелкните ячейку E3 и нажмите клавишу DELETE. Вам придется выделить весь диапазон ячеек (E2 \endash E11) и изменить формулу для всех ячеек либо оставить массив без изменений. Помимо этого вы должны будете нажать сочетание клавиш CTRL+SHIFT+ВВОД, чтобы подтвердить изменение формулы, что еще больше повышает уровень безопасности.
• Меньший размер файлов Зачастую вместо нескольких промежуточ-ных формул можно использовать одну формулу массива. Например, в созданной для этого упражнения рабочей книге для вычисления результатов в столбце E применяется всего лишь одна формула. Если бы вы прибегли к обычным формулам (таким как =C2*D2), вам понадобилось бы 11 разных формул для получения таких же результатов.

Краткий обзор синтаксиса формулы массива

В общем случае для формул массива используется тот же синтаксис, что и для обычных формул. Все они начинаются со знака равенства (=) и могут содержать любые встроенные функции Excel. Ключевое отличие заключается в том, что при использовании формулы массива для ввода такой формулы необходимо нажать клавиши CTRL+SHIFT+ВВОД. При этом формула массива будет автоматически заключена в фигурные скобки. Если подставить скобки вручную, формула будет преобразована в текстовую строку и перестанет работать.

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

=C2*D2
=C3*D3

и так далее. Формула с одной ячейкой, находящаяся в ячейке B13, содержит в кратком виде все эти операции умножения, а также арифметические действия, требуемые для сложения промежуточных итогов: =E2+E3+E4 и так далее.

Правила ввода и изменения формул массива

Главное правило при создании формулы массива стоит повторить еще раз: нажимайте сочетание клавиш CTRL+SHIFT+ВВОД, когда хотите ввести или изменить формулу массива. Это правило действует как в отношении формул с одной ячейкой, так и в отношении формул с несколькими ячейками.

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

• Необходимо выделить диапазон ячеек, который будет содержать результаты, прежде чем вводить формулу. Вы сделали это в шаге 3 упражнения по созданию формулы массива с несколькими ячейками, когда выбрали ячейки E2 \endash E11.
• Содержимое отдельной ячейки в формуле массива изменить нельзя. Чтобы проверить это правило, выделите ячейку E3 в образце книги и нажмите клавишу DELETE.
• Формулу массива можно переместить или удалить только целиком. Другими словами, чтобы уменьшить формулу массива, сначала нужно удалить существующую формулу, а затем создать новую формулу с самого начала.

Чтобы удалить формулу массива, выделите ее целиком (например, =C2:C11*D2:D11), нажмите клавишу DELETE, а затем — клавиши CTRL+SHIFT+ВВОД.

Не нашли, что искали? Воспользуйтесь поиском: