Формулы массива — это мощные формулы, которые позволяют выполнять сложные вычисления, которые часто не могут быть выполнены с помощью стандартных функций листа. Они также называются формулами 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 или получить поддержку в сообществах.