Формулы Excel, возвращающие набор значений, также известный как массив, возвращают эти значения в смежные ячейки. Это действие называется переносом.
Формулы, которые могут возвращать массивы переменной размера, называются формулами динамических массивов . Формулы, которые в настоящее время возвращают массивы, которые успешно разливаются, можно назвать формулами разливаемых массивов .
Ниже приведены несколько заметок, которые помогут вам в понимании и использовании формул такого типа.
Что означает перенос?
Примечание
Более старые формулы массива, известные как устаревшие формулы массива, всегда возвращают результат фиксированного размера — они всегда переносятся в одно и то же количество ячеек. Поведение при переносе, описанное в этом разделе, не применяется к устаревшим формулам массива.
Перенос означает, что формула привела к нескольким значениям, и эти значения были помещены в смежные ячейки. Например, =SORT(D2:D11;1,-1),, сортирующая массив в порядке убывания, возвращает соответствующий массив высотой 10 строк. Но вам нужно только ввести формулу в верхнюю левую ячейку или в данном случае F2, и она автоматически переместится в ячейку F11.
Ключевые моменты
- При нажатии клавиши ВВОД для подтверждения формулы Excel динамически изменит размер выходного диапазона и поместит результаты в каждую ячейку в этом диапазоне.
- Если вы пишете формулу динамического массива для работы со списком данных, может быть удобно поместить его в таблицу Excel, а затем использоватьструктурированные ссылки для ссылки на данные. Это происходит потому, что структурированные ссылки автоматически корректируются при добавлении или удалении строк из таблицы.
- Формулы перенесенного массива не поддерживаются в самих таблицах Excel, поэтому их следует размещать в сетке за пределами таблицы. Таблицы лучше всего подходят для хранения строк и столбцов независимых данных.
- После ввода формулы перенесенного массива при выделении любой ячейки в области переноса Excel выделит рамкой границу диапазона. Граница исчезнет при выборе ячейки за пределами области.
- Редактировать можно только первую ячейку в области переноса. При выборе другой ячейки в области переноса, формула будет видна в строке формул, но текст будет "фантомным" и недоступным для изменения. При необходимости обновить формулу, необходимо выбрать верхнюю левую ячейку в диапазоне массива, изменить ее по мере необходимости. При нажатии Enter Excel автоматически обновит остальную часть области переноса.
- Перекрытие формул. Формулы массива нельзя ввести, если что-то блокирует выходной диапазон. и если это происходит, Excel возвращает ошибку #ПЕРЕНОС!, указывающую на наличие блокировки. Если удалить помеху, формула будет перенесена должным образом. В приведенном ниже примере выходной диапазон формулы перекрывает другой диапазон с данными и отображается с пунктирной рамкой, перекрывающей ячейки со значениями, указывающими, что он не может быть перенесен. Удалите блокирующие данные или скопируйте их в другое место, и формула перенесется, как и ожидалось.
- Устаревшие формулы массива, введенные с помощью CTRL+SHIFT+ENTER (CSE), по-прежнему поддерживаются по соображениям обратной совместимости, но их больше нельзя использовать. Если хотите, вы можете преобразовать устаревшие формулы массива в формулы динамического массива, найдя первую ячейку в диапазоне массива, скопировав текст формулы, удалив весь диапазон устаревшего массива, а затем повторно введя формулу в верхней левой ячейке. Перед обновлением устаревших формул массива до динамических формул массива следует учитывать некоторые различия вычислений между ними.
- Приложение Excel ограничило поддержку динамических массивов в операциях между книгами, и этот сценарий поддерживается, только если открыты обе книги. При закрытии исходной книги все связанные формулы динамического массива возвращают ошибку #REF! при обновлении.
Дополнительные сведения
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществах.