Динамические массивы и поведение массивов с сбросом

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

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

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

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

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

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

Сортировка значений в ячейках D2: D11 с = SORT (D2: D11; 1;-1)

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

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

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

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

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

    Формула массива с выделенным диапазоном выходных данных с синей рамкой

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

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

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

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

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

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

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

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

См. также

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

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

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

Функция СОРТ

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

Функция УНИК

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

Неявное пересечение: @

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

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

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

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

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

×