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

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

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

Что означает "пролитие"?

Примечание: Более старые формулы массива, известные как формулы массива прежних поколений, всегда возвращают фиксированный размер— они всегда протекают в одно и то же количество ячеек. Поведение, описанное в этом разделе, не относится к устаревшим формулам массива.

Пролитие означает, что формула содержит несколько значений, которые были помещены в соседние ячейки. Например, =СОРТ(D2:D11;1;-1) для сортировки массива в порядке убыния возвращает соответствующий массив высотой 10 строк. Но вам нужно ввести формулу только в левую верхнюю ячейку , или F2 в этом случае, и она автоматически перетекает на ячейку F11.

Сортировка значений в ячейках D2:D11 с помощью =СОРТ(D2:D11;1;-1)

Основные моменты

  • При нажатии ввода для подтверждения формулы Excel динамический размер диапазона вывода и поместите результаты в каждую ячейку в этом диапазоне.

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

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

  • После ввода формулы разлитого массива при выделении любой ячейки в области Excel вокруг диапазона будет выделена граница. При выборе ячейки за пределами области граница исчезнет.

    Формула массива с диапазоном вывода, выделенным синей границей

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

    Изображение формулы массива, которая не является редактируемой, так как это не первая ячейка в диапазоне массива

  • Перекрытие формул: формулы массива не могут вводиться, если диапазон выходных данных блокируется. в этом случае Excel возвращает ошибку #SPILL! о том, что она заблокирована. Если удалить помеху, формула будет перенесена должным образом. В приведенном ниже примере диапазон вывода формулы перекрывает другой диапазон с данными и отображается с перекрывающимися ячейками с пунктирной границей со значениями, указывающими на то, что их нельзя пролить. Удалите заблокированные данные или скопируйте их в другом месте, и формула будет пролита, как ожидалось.

    Изображение #SPILL! ошибка, указывающая на то, что выход формулы массива заблокирован, что предотвращает его пролиение.

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

  • Приложение Excel ограничило поддержку динамических массивов в операциях между книгами, и этот сценарий поддерживается, только если открыты обе книги. Если закрыть исходную книгу, все связанные формулы динамического массива вернут ошибку #ССЫЛКА! после обновления.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Функция ФИЛЬТР

Функция СЛУЧМАССИВ

Функция ПОСЛЕДОВ

Функция СОРТ

Функция СОРТПО

Функция УНИК

Ошибки #ПЕРЕНОС! в Excel

Оператор неявного пересечения: @

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

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

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

Насколько вы удовлетворены качеством перевода?
Что повлияло на вашу оценку?

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

×