Как вводить формулу массива в excel

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

Формулы массива — это мощные формулы, которые позволяют выполнять сложные вычисления, которые часто не могут быть выполнены с помощью стандартных функций листа. Они также называются формулами CTRL-SHIFT-ВВОД или CSE, так как для их ввода необходимо нажать клавиши CTRL+SHIFT+ВВОД. Формулы массива можно использовать для выполнения, казалось бы, невозможного, например

• Подсчитайте количество символов в диапазоне ячеек.
• Суммирование чисел, соответствующих определенным условиям, например наименьших значений в диапазоне или чисел, которые попадают между верхней и нижней границами.
• Суммирование всех n-х значений в диапазоне значений.

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

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

Создание формулы массива для вычисления одного результата

Этот тип формулы позволяет упростить модель листа благодаря замене нескольких отдельных формул.

1. Щелкните ячейку, в которую нужно ввести формулу массива.
2. Введите необходимую формулу. В формулах массива используется синтаксис обычных формул. Все они начинаются со знака равенства (=), и вы можете использовать любую из встроенных функций Excel в формулах массива. Например, эта формула вычисляет общее значение массива цен на акции и поместит результат в ячейку рядом с полем «Общее значение».

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

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

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

1. Выделите диапазон ячеек, в который нужно ввести формулу массива.
2. Введите необходимую формулу. В формулах массива используется синтаксис обычных формул. Все они начинаются со знака равенства (=), и вы можете использовать любую из встроенных функций Excel в формулах массива. В следующем примере формула умноживает акции по цене в каждом столбце, а формула находится в выбранных ячейках в строке 5.

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

Если необходимо включить новые данные в формулу массива, см. раздел Развертывание формулы массива. Вы также можете попробовать:

• Правила изменения формул массива (они могут быть привередливыми)
• Удаление формулы массива (здесь также можно нажать клавиши CTRL+SHIFT+ВВОД).
• Использование констант массива в формулах массива (они могут быть удобны)
• Присвойте константе массива имя (они могут упростить использование констант)

Попробуйте попрактиковаться

Если вы хотите поэксперитировать с константами массива, прежде чем опробовать их с собственными данными, можно использовать пример данных здесь.

В приведенной ниже книге показаны примеры формул массива. Чтобы лучше работать с примерами, скачайте книгу на компьютер, щелкнув значок Excel в правом нижнем углу, а затем откройте ее в классической программе Excel.

Скопируйте приведенную ниже таблицу и вставьте ее в Excel в ячейке A1. Выделите ячейки E2:E11, введите формулу =C2:C11*D2:D11, а затем нажмите клавиши CTRL+SHIFT+ВВОД, чтобы сделать ее формулой массива.

Формулы массива в Эксель и Гугл таблицах

И Эксел, и Гугл Таблицы зарекомендовали себя как два мощных табличных редактора. Разработчики обеих программ предусмотрели множество функций и инструментов, при помощи которых пользователь реализовывает свои задумки. Одной из полезных «фишек», встроенных в эти редакторы, является формула массива. Но далеко не все пользователи программ знакомы с этим полезным инструментом.

Формула массива: что это и для чего нужно

Массивы данных представляют собой генеральную совокупность величин, которые были объединены в цельную группу.

Формула суммы массива присутствует и в Эксел, и в Гугл Таблицах. Это формулы -инструменты для работы с составляющими массива.

Использование этой функции в обоих редакторах преследует следующие цели:

• пересчет количества символов в выбранном диапазоне;
• сложить определенные показатели, соответствующие заданным условиям. В этом случае результат выполнения формулы массива представляет собой, к примеру, сумму минимальных или самых больших в массиве чисел;
• сложить числа, находящиеся в конкретном табличном диапазоне.

Ввести и заполнить значение массива по формуле можно только при нажатии «Ctrl», «Enter» и «Shift» одновременно. Пользователь сразу увидит фигурные скобки, в которые нужно вводить данные. При этом нельзя ввести этот символ вручную, с клавиатуры. Он не будет распознан программами как формула.

Типы и состав формул в Экселе

В программу Эксель встроено четыре базовых формата формул. Это арифметический, текстовый, сравнительный оператор, а также оператор ссылок. По своему составу экселевские формулы, в том числе и для массива, включают в себя три «комплектующих» — это символ «Равно», непосредственно оператор (один из четырех доступных) и синтаксис нужной функции.

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

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

Типы и состав формул в Гугл Таблицах

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

Постоянные величины – это показатели, имеющие фиксированное значение. К примеру, какая-либо цифра. Оператором в Гугл Таблицах называется непосредственно символ, выражающий тип операции – знак вычитания, знак умножения и т. п. Ссылки в Гугл Sheets могут быть относительными, абсолютными и комбинированными (смешанными).

В Гугл Таблице пользователь также может задать наименование табличному диапазону. Это упрощает вычислительные операции. Функции в сервисе от Гугл аналогичны Экселю.

Как ввести формулу массива

Формула массива в Excel может быть двух разновидностей:

1. Первая категория – формулы, исполняющие множество вычислений ради одного ответа.
2. Вторая категория – формулы, исполняющие множество вычислений.

В первом случае ввод формулы осуществляется по следующему алгоритму:

1. Выбирается табличная ячейка, в которую надо ввести формулу.
2. Вводится соответствующая формула. В случае массивов синтаксис точно такой же, как и при стандартных функциях. В начале текста всегда проставляется символ «Равно».
3. Нажимается «Ввод» (клавиша на клавиатуре – «Enter»).

Во втором случае ввод производится по похожей схеме:

1. Пользователь выбирает диапазон табличных ячеек, куда будет вводиться формула.
2. Вводится синтаксис. В самом начале также проставляется равенство.
3. Нажимается «Ввод».

В некоторых версиях Экселя для активации формулы нужно зажать другую комбинацию кнопок на клавиатуре – «Ctrl», «Enter», «Shift».

Формула массива в Гугл таблицах

ArrayFormula в Гугл Sheets – это прямой аналог рассматриваемой формулы массива в Эксел. Формула вычисления массива в облачных Гугл Таблицах, по аналогии с Экселем, начинается с символа «Равно».

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

Формула массива может иметь два разных формата. Так, формула, отраженная в формате «1; 2», считается «вертикальной». Единица – элемент, расположенный сверху, двойка – элемент нижний. Формат «1/2» — «горизонтальный», т. е. обе цифры – это правый и левый элемент соответственно.

Как быстро овладеть формулами массива в Эксел

Формула массива в Экселе – удобный инструмент, облегчающий исполнение необходимых расчетов и операций с набором цифровых данных. Массивы могут быть одномерными, двухмерными и даже трехмерными. А индекс массива – это элемент, указывающий на конкретную его составляющую. Сам Excel является мощнейшим табличным процессором, который пригодится и в работе, и дома.

О том, как упростить и ускорить работу в Excel и Google Таблицах, можно говорить долго. Но гораздо удобнее и эффективнее записаться на уроки Эксель онлайн. Дистанционный формат их прохождения представляется наиболее комфортным в современном ритме жизни. Емкая теоретическая часть и углубленная практика дадут гораздо больше результатов, чем чтение нудных сложных лекций.

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

Как в Excel ввести формулу массива?

Из данной статьи вы узнаете, как в Excel ввести формулу массива.

Принцип ввода формулы массива расскажу на примере 2-х формул =ЛИНЕЙН() и =ТРАНСП().

Для того, чтобы с помощью формулы =ЛИНЕЙН() рассчитать коэффициенты линейного тренда y=a+bx (a) и (b), необходимо:

1. Ввести в формулу данные =ЛИНЕЙН(известные значения y (например, объём продаж по месяцам), известные значения x (номера периодов), константа (коэффициент (a) в формуле y=a+bx, для его расчета ставим «1»), статистика (вводим «0»)) (см. файл с примером).

2. Установить курсор в ячейку с формулой и выделить соседнюю справа, как на рисунке:

3. Для ввода формулы массива нажимаем клавишу F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

Коэффициенты линейного тренда y=a+bx (a) и (b) рассчитаны.

2-й пример (см. вложенный файл), в нём мы рассмотрим, как перевернуть диапазон и сделать из горизонтального вертикальный. Для этого воспользуемся функцией =ТРАНСП().

Как она работает:

1. В формулу вводим горизонтальный диапазон, который хотим сделать вертикальным:

2. Выделяем вертикальный диапазон, равный по количеству ячеек выделенному горизонтальному, вверху диапазона должна быть введена формула =ТРАНСП();

3. Для ввода формулы массива нажимаем клавишу F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

Горизонтальный диапазон стал вертикальным. Теперь, если мы внёсем изменения в горизонтальный диапазон, они тут же отобразятся в вертикальном диапазоне.

Для ввода формулы массива необходимо

1. выделить массив — это диапазон ячеек, в которые Excel выведет данные,
2. и нажать чудо комбинацию клавиш — F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

Всё просто, попробуйте, и если остались вопросы, задавайте их в комментариях.

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

• Novo Forecast Lite — автоматический расчет прогноза в Excel .
• 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
• Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

• Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Формулы массива

Вы можете создавать формулы массива, которые часто называют формулами CTRL-SHIFT-ВВОД или CSE-формулами, для выполнения вычислений, возвращающих один или несколько результатов. Подробнее о них вы узнаете из этого видео.

Назначение формул массива

Формулы массива часто называют CSE-формулами, поскольку для их ввода используется сочетание клавиш CTRL+SHIFT+ВВОД, а не простое нажатие клавиши ВВОД.

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

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

Ввод формулы массива

1. Выберите ячейки, в которые вы хотите поместить результат.
2. Введите формулу.
3. Нажмите клавиши CTRL+SHIFT+ВВОД. Excel заполнит результатом каждую из выбранных ячеек.