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

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

  • Подсчет количества символов в диапазоне ячеек.

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

  • Суммирование всех n-х значений в диапазоне значений.

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

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

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

  1. Щелкните ячейку, в которую нужно ввести формулу массива.

  2. Введите необходимую формулу.

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

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

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

    Сначала формула умножает общие акции (ячейки B2 – F2) на их цены (ячейки B3 – F3), а затем добавляет эти результаты, чтобы получить общий итог в 35 525. Это пример формулы массива с одной ячейкой, так как она находится в одной ячейке.

  3. Нажмите ввод (если у вас есть текущая Microsoft 365 подписка); в противном случае нажмите CTRL+SHIFT+ВВОД.

    При нажатии CTRL+SHIFT+ВВОДExcel автоматически вставляет формулу между {} (парой открыва и закрываемой скобок).

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

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

  1. Выделите диапазон ячеек, в который нужно ввести формулу массива.

  2. Введите необходимую формулу.

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

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

    Пример формулы массива для вычисления нескольких результатов
  3. Нажмите ввод (если у вас есть текущая Microsoft 365 подписка); в противном случае нажмите CTRL+SHIFT+ВВОД.

    При нажатии CTRL+SHIFT+ВВОДExcel автоматически вставляет формулу между {} (парой открыва и закрываемой скобок).

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

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

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

В приведенной ниже книге показаны примеры формул массива. Чтобы лучше всего работать с примерами, скачайте книгу на свой компьютер, щелкнув значок 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

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

  1. В книге с примерами выберем ячейки E2-E11. Эти ячейки будут содержать результаты.

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

И всегда означает 100 % времени.

  1. Введите эту формулу. Чтобы ввести формулу в ячейку, просто начните вводить текст (нажмите знак равных), и формула появится в последней выбранной ячейке. Вы также можете ввести формулу в формулу:

    =C2:C11*D2:D11

  2. Нажмите клавиши CTRL+SHIFT+ВВОД.

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

  1. В книге с примерами щелкните ячейку B13.

  2. Введите эту формулу, используя один из способов из шага 2 выше:

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

  3. Нажмите клавиши CTRL+SHIFT+ВВОД.

Формула перемножет значения в диапазонах ячеев C2:C11 и D2:D11, а затем добавляет результаты для вычисления общего итога.

В Excel в Интернете вы можете просмотреть формулы массива, если они уже есть в открытой книге. Но в этой версии Excel вы не сможете создать формулу массива, нажав CTRL+SHIFT+ВВОД, чтобы вставить формулу между парой открыва которых выводится и закрывает скобки({ }). Ввод этих скобок вручную также не превратит формулу в формулу массива.

Если у вас есть настольное приложение Excel, вы можете нажать кнопку "Открыть в Excel", чтобы открыть книгу и создать формулу массива.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Нужна дополнительная помощь?

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×