Формулы массива — это мощные формулы, которые позволяют выполнять сложные вычисления, которые часто не могут быть выполнены с помощью стандартных функций листа. Они также называются формулами CTRL-SHIFT-ВВОД или CSE, так как для их ввода необходимо нажать клавиши CTRL+SHIFT+ВВОД. Формулы массива можно использовать для выполнения, казалось бы, невозможного, например
-
Подсчитайте количество символов в диапазоне ячеек.
-
Суммирование чисел, соответствующих определенным условиям, например наименьших значений в диапазоне или чисел, которые попадают между верхней и нижней границами.
-
Суммирование всех n-х значений в диапазоне значений.
Excel предоставляет два типа формул массива: формулы массива, которые выполняют несколько вычислений для создания одного результата, и формулы массива, которые вычисляют несколько результатов. Некоторые функции возвращают массивы значений или требуют массив значений в качестве аргумента. Дополнительные сведения см. в разделе Рекомендации и примеры формул массива.
Примечание: Если у вас есть текущая версия Microsoft 365, можно просто ввести формулу в левую верхнюю ячейку выходного диапазона, а затем нажать клавишу ВВОД , чтобы подтвердить формулу как формулу динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Этот тип формулы позволяет упростить модель листа благодаря замене нескольких отдельных формул.
-
Щелкните ячейку, в которую нужно ввести формулу массива.
-
Введите необходимую формулу.
В формулах массива используется синтаксис обычных формул. Все они начинаются со знака равенства (=), и вы можете использовать любую из встроенных функций Excel в формулах массива.
Например, эта формула вычисляет общее значение массива цен на акции и поместит результат в ячейку рядом с полем "Общее значение".
Формула сначала умножает акции (ячейки B2 – F2) на их цены (ячейки B3 – F3), а затем добавляет эти результаты, чтобы создать общий объем в 35525. Это пример формулы массива с одной ячейкой, так как формула находится только в одной ячейке.
-
Нажмите клавишу ВВОД (если у вас есть текущая подписка на Microsoft 365 ); В противном случае нажмите клавиши CTRL+SHIFT+ВВОД.
При нажатии клавиш CTRL+SHIFT+ВВОД Excel автоматически вставляет формулу между { } (пара открывающих и закрывающих фигурных скобок).
Примечание: Если у вас есть текущая версия Microsoft 365, можно просто ввести формулу в левую верхнюю ячейку выходного диапазона, а затем нажать клавишу ВВОД , чтобы подтвердить формулу как формулу динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Чтобы вычислить несколько результатов с помощью формулы массива, введите массив в диапазон ячеек, содержащий точно такое же количество строк и столбцов, которое будет использоваться в аргументах массива.
-
Выделите диапазон ячеек, в который нужно ввести формулу массива.
-
Введите необходимую формулу.
В формулах массива используется синтаксис обычных формул. Все они начинаются со знака равенства (=), и вы можете использовать любую из встроенных функций Excel в формулах массива.
В следующем примере формула умноживает акции по цене в каждом столбце, а формула находится в выбранных ячейках в строке 5.
-
Нажмите клавишу ВВОД (если у вас есть текущая подписка на Microsoft 365 ); В противном случае нажмите клавиши CTRL+SHIFT+ВВОД.
При нажатии клавиш CTRL+SHIFT+ВВОД Excel автоматически вставляет формулу между { } (пара открывающих и закрывающих фигурных скобок).
Примечание: Если у вас есть текущая версия Microsoft 365, можно просто ввести формулу в левую верхнюю ячейку выходного диапазона, а затем нажать клавишу ВВОД , чтобы подтвердить формулу как формулу динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Если необходимо включить новые данные в формулу массива, см. раздел Развертывание формулы массива. Вы также можете попробовать:
-
Правила изменения формул массива (они могут быть привередливыми)
-
Удаление формулы массива (здесь также можно нажать клавиши CTRL+SHIFT+ВВОД).
-
Использование констант массива в формулах массива (они могут быть удобны)
-
Присвойте константе массива имя (они могут упростить использование констант)
Если вы хотите поэксперитировать с константами массива, прежде чем опробовать их с собственными данными, можно использовать пример данных здесь.
В приведенной ниже книге показаны примеры формул массива. Чтобы лучше работать с примерами, скачайте книгу на компьютер, щелкнув значок Excel в правом нижнем углу, а затем откройте ее в классической программе Excel.
Скопируйте приведенную ниже таблицу и вставьте ее в Excel в ячейке A1. Выделите ячейки E2:E11, введите формулу =C2:C11*D2:D11, а затем нажмите клавиши CTRL+SHIFT+ВВОД, чтобы сделать ее формулой массива.
Продавец |
Тип автомобиля |
Число проданных единиц |
Цена за единицу |
Итоги продаж |
---|---|---|---|---|
Зуева |
Седан |
5 |
2200 |
=C2:C11*D2:D11 |
Купе |
4 |
1800 |
||
Егоров |
Седан |
6 |
2300 |
|
Купе |
8 |
1700 |
||
Еременко |
Седан |
3 |
2000 |
|
Купе |
1 |
1600 |
||
Климов |
Седан |
9 |
2150 |
|
Купе |
5 |
1950 |
||
Шашков |
Седан |
6 |
2250 |
|
Купе |
8 |
2000 |
Создание формулы массива с несколькими ячейками
-
В примере книги выберите ячейки С2 по E11. Эти ячейки будут содержать ваши результаты.
Вы всегда выбираете ячейку или ячейки, которые будут содержать результаты, прежде чем вводить формулу.
И всегда, мы имеем в виду 100 процентов времени.
-
Введите эту формулу. Чтобы ввести его в ячейку, просто начните вводить (нажмите знак равенства), и формула появится в последней выбранной ячейке. Можно также ввести формулу в строке формул:
=C2:C11*D2:D11
-
Нажмите клавиши CTRL+SHIFT+ВВОД.
Создание формулы массива с одной ячейкой
-
В примере книги щелкните ячейку B13.
-
Введите эту формулу с помощью любого из методов из шага 2 выше:
=СУММ(C2:C11*D2:D11)
-
Нажмите клавиши CTRL+SHIFT+ВВОД.
Формула умножает значения в диапазонах ячеек C2:C11 и D2:D11, а затем добавляет результаты для вычисления общего итога.
В Excel в Интернете можно просмотреть формулы массива, если они уже есть в открытой книге. Но вы не сможете создать формулу массива в этой версии Excel, нажав клавиши CTRL+SHIFT+ВВОД, чтобы вставить формулу между парой открывающих и закрывающих фигурных скобок({ }). Ввод этих фигурных скобок вручную также не приведет к преобразованию формулы в формулу массива.
Если у вас есть классическое приложение Excel, можно открыть книгу и создать формулу массива с помощью кнопки Открыть в Excel.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.