Формулы массива — это мощные формулы, позволяющие выполнять сложные вычисления, которые часто невозможно выполнить с помощью стандартных функций листа. Их также называют формулами «Ctrl-Shift-Enter» или «CSE», потому что для их ввода нужно нажать клавиши CTRL+SHIFT+ВВОД. Формулы массива позволяют выполнить, казалось бы, невозможное
- Подсчитывать числа знаков в диапазоне ячеек.
- Суммирование чисел, удовлетворяющих определенным условиям, например наименьших значений в диапазоне чисел, определенном верхней и нижней границами.
- Суммирование всех n-х значений в диапазоне значений.
В Excel есть два типа формул массива: формулы массива, выполняющие несколько вычислений для получения одного результата, и формулы массива, вычисляющие несколько результатов. Некоторые функции возвращают массивы значений или требуют массив значений в качестве аргумента. Дополнительные сведения см. в руководстве и примерах формул массива.
Примечание
Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхнюю левую ячейку диапазона вывода и нажать клавишу ВВОД , чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Создание формулы массива для вычисления одного результата
Этот тип формулы позволяет упростить модель листа благодаря замене нескольких отдельных формул.
Щелкните ячейку, в которую нужно ввести формулу массива.
Введите необходимую формулу.
В формулах массива используется синтаксис обычных формул. Они все начинаются со знака равенства (=) и могут содержать любые встроенные функции Excel.
Например, в указанной ниже формуле суммарное значение массива котировок акций и результат помещается в ячейку рядом с элементом "Общая стоимость".
Формула сначала перемножает доли (ячейки B2 – F2) на их цены (ячейки B3 – F3), а затем складывает полученные результаты, чтобы получить общую сумму 35 525. Это пример формулы массива с одной ячейкой, поскольку формула находится только в одной ячейке.
Нажмите клавишу ВВОД (если у вас есть текущая подписка на Microsoft 365); в противном случае нажмите Ctrl+Shift+Enter.
При нажатии CTRL+SHIFT+ВВОД Excel автоматически вставляет формулу между { } (парой открывающих и закрывающих скобок).Примечание
Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхнюю левую ячейку диапазона вывода и нажать клавишу ВВОД , чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Создание формулы массива для вычисления нескольких результатов
Чтобы вычислить несколько результатов с помощью формулы массива, введите массив в диапазон ячеек с таким же количеством строк и столбцов, которое будет использоваться в качестве аргументов массива.
Выделите диапазон ячеек, в который нужно ввести формулу массива.
Введите необходимую формулу.
В формулах массива используется синтаксис обычных формул. Они все начинаются со знака равенства (=) и могут содержать любые встроенные функции Excel.
В приведенном ниже примере формула умножает доли на значения цены в каждом столбце, а формула находится в выделенных ячейках в строке 5.
Нажмите клавишу ВВОД (если у вас есть текущая подписка на Microsoft 365); в противном случае нажмите Ctrl+Shift+Enter.
При нажатии CTRL+SHIFT+ВВОД Excel автоматически вставляет формулу между { } (парой открывающих и закрывающих скобок).Примечание
Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхнюю левую ячейку диапазона вывода и нажать клавишу ВВОД , чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Если необходимо включить новые данные в формулу массива, см. статью Расширение формулы массива. Также можно попробовать:
- Правила изменения формул массива (они могут быть привередливыми)
- Удаление формулы массива (нажмите клавиши CTRL+SHIFT+ВВОД)
- Использование констант массива в формулах массива (они могут быть удобными)
- присвоить имя константе массива (они упрощают использование констант);
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с константами массива, прежде чем опробовать их на собственных данных, вы можете использовать образец данных здесь.
В книге ниже приведены примеры формул массива. Для оптимальной работы с примерами следует скачать книгу на компьютер, щелкнув значок Excel в правом нижнем углу, и открыть ее в классической программе Excel.
Скопируйте таблицу ниже и вставьте ее в ячейку A1 в Excel. Выделите ячейки 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 |
Создание формулы массива с несколькими ячейками
- В образце книги выделите ячейки от E2 до E11. Эти ячейки будут содержать результаты.
Перед вводом формулы всегда выделяйте ячейки с результатами.
И под «всегда» мы имеем в виду 100 процентов времени.
- Введите следующую формулу. Чтобы ввести ее в ячейку, просто начните вводить текст (нажмите знак равенства), и формула появится в последней выделенной ячейке. Формулу также можно ввести в строке формул.
=C2:C11*D2:D11 - Нажмите клавиши CTRL+SHIFT+ВВОД.
Создание формулы массива с одной ячейкой
- В образце книги щелкните ячейку B13.
- Введите эту формулу, используя любой из способов из шага 2 выше:
=СУММ(C2:C11*D2:D11) - Нажмите клавиши CTRL+SHIFT+ВВОД.
Формула перемножает значения в диапазонах ячеек C2:C11 и D2:D11, а затем суммирует результаты, чтобы получить общий итог.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.