Массивы в excel 2019: Формулы динамического массива и поведение перенесенного массива

Формулы динамического массива и поведение перенесенного массива

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Еще…Меньше

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

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

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

Что означает перенос?

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

Поведение при переносе, описанное в этом разделе, не применяется к устаревшим формулам массива.

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

Ключевые моменты

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

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

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

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

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

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

• Устаревшие формулы массива, введенные с помощью

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

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

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

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

См. также

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

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

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

Функция СОРТ

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

Функция УНИК

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

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

Формулы динамического массива в Excel, не поддерживающими динамические функции

Excel для Microsoft 365 Еще.

..Меньше

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

Примечание: Поддержка формул динамического массива была выпущена для подписчиков Microsoft 365 в Current Channel в январе 2020 г.

Подробные сведения

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

   

• Ниже приведены примеры функции MUNIT, введенной в виде формулы динамического массива и устаревшей формулы CSE. Обратите внимание, что устаревшие формулы массива CSE ведут себя очень аналогично формулам динамического массива. Основное различие заключается в том, что они не могут изменять размер и не имеют границы сброса. Сравнение этих двух значений см. в разделе » Формулы динамического массива и устаревшие формулы массива CSE».

Динамический массив

Устаревшая CSE

• org/ListItem»>

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

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

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

См. также

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

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

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

Функция СОРТ

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

Функция УНИК

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

Поведение разлитого массива

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

Формулы динамического массива в Excel без динамической поддержки

Excel для Microsoft 365 Больше. ..Меньше

В сентябре 2018 года мы представили формулы динамического массива. Формулы динамического массива могут автоматически заполняться или «перетекать» в соседние пустые ячейки и устранять необходимость в устаревших формулах массива Ctrl+Shift+Enter (CSE). При открытии книги, содержащей формулы динамического массива, в более старой версии Excel необходимо учитывать некоторые соображения совместимости.

Примечание. Поддержка формулы динамического массива была выпущена для подписчиков Microsoft 365 в Current Channel в январе 2020 г.

Особенности

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

• Ниже приведены примеры функции MUNIT, введенной как формула динамического массива и как устаревшая формула CSE. Обратите внимание, что устаревшие формулы массива СПП ведут себя очень похоже на формулы динамического массива. Основное отличие состоит в том, что они не могут изменять размер и не имеют границы разлива. Сравнение этих двух способов см. в разделе Формулы динамического массива и устаревшие формулы массива CSE.

Динамический массив

Устаревший CSE

• org/ListItem»>

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

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

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

См. также

ФИЛЬТР

Функция СЛУЧАЙ

ПОСЛЕДОВАТЕЛЬНОСТЬ

СОРТИРОВКА функция

СОРТИРОВАТЬ функцию

УНИКАЛЬНАЯ функция

#ПРОЛИВАТЬ! ошибки в экселе

Поведение пролитого массива

Неявный оператор пересечения: @

Динамические массивы, функции и формулы Excel

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

Формулы массива Excel всегда считались прерогативой гуру и экспертов по формулам. Если кто-то скажет: «Это можно сделать с помощью формулы массива», многие пользователи немедленно отреагируют: «А нет ли другого способа?».

Введение динамических массивов — долгожданное и долгожданное изменение. Благодаря своей способности работать с несколькими значениями простым способом, без каких-либо уловок и причуд, формулы динамического массива — это то, что каждый пользователь Excel может понять и получить удовольствие от создания.

Динамические массивы Excel

Динамические массивы — это массивы с изменяемым размером, которые автоматически вычисляют и возвращают значения в несколько ячеек на основе формулы, введенной в одну ячейку.

За более чем 30-летнюю историю Microsoft Excel претерпел множество изменений, но одно осталось неизменным — одна формула, одна ячейка. Даже с традиционными формулами массива необходимо было вводить формулу в каждую ячейку, где должен появиться результат. С динамическими массивами это правило больше не выполняется. Теперь любая формула, возвращающая массив значений, автоматически переносится в соседние ячейки, и вам не нужно нажимать Ctrl + Shift + Enter или выполнять какие-либо другие действия. Другими словами, работать с динамическими массивами становится так же просто, как с одной ячейкой.

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

В преддинамических версиях Excel приведенная ниже формула будет работать только для первой ячейки, если вы не введете ее в несколько ячеек и не нажмете Ctrl + Shift + Enter, чтобы явно сделать ее формулой массива:

=A3:A5*B2:D2

Теперь посмотрите, что происходит, когда та же формула используется в Excel 365. Вы вводите ее всего в одну ячейку (в нашем случае B3), нажимаете клавишу Enter… и вся ярость сразу заполняется результатами:

Заполнение нескольких ячеек одной формулой называется разливом , а заполненный диапазон ячеек называется диапазоном разброса.

Важно отметить, что недавнее обновление — это не просто новый способ обработки массивов в Excel. По сути, это революционное изменение всего механизма расчета. Благодаря динамическим массивам в библиотеку функций Excel было добавлено множество новых функций, а существующие стали работать быстрее и эффективнее. В конце концов, предполагается, что новые динамические массивы полностью заменят устаревшие формулы массивов, которые вводятся с помощью сочетания клавиш Ctrl + Shift + Enter.

Доступность динамических массивов Excel

Динамические массивы были представлены на конференции Microsoft Ignite в 2018 г. и выпущены для подписчиков Office 365 в январе 2020 г. В настоящее время они доступны в подписках Microsoft 365 и Excel 2021.

Динамические массивы поддерживаются в следующих версиях:

• Excel 365 для Windows
• Excel 365 для Mac
• Excel 2021
• Excel 2021 для Mac
• Excel для iPad
• Excel для iPhone
• Excel для планшетов Android
• Excel для телефонов Android
• Excel для Интернета

Функции динамического массива Excel

В рамках новой функциональности в Excel 365 было представлено 6 новых функций, которые изначально обрабатывают массивы и выводят данные в диапазон ячеек. Вывод всегда динамичен — когда в исходных данных происходит какое-либо изменение, результаты обновляются автоматически. Отсюда и название группы — функций динамического массива .

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

Ниже вы найдете краткое описание того, что делает каждая функция, а также ссылки на подробные руководства:

1. UNIQUE — извлекает уникальные элементы из ряда ячеек.
2. ФИЛЬТР — фильтрует данные на основе заданных вами критериев.
3. SORT — сортирует диапазон ячеек по указанному столбцу.
4. SORTBY — сортирует диапазон ячеек по другому диапазону или массиву.
5. RANDARRAY — генерирует массив случайных чисел.
6. ПОСЛЕДОВАТЕЛЬНОСТЬ — генерирует список порядковых номеров.
7. TEXTSPLIT — разбивает строки по указанному разделителю между столбцами и/или строками.
8. TOCOL — преобразовать массив или диапазон в один столбец.
9. TOROW — преобразовать диапазон или массив в одну строку.
10. WRAPCOLS — преобразует строку или столбец в двумерный массив на основе указанного количества значений в строке.
11. WRAPROWS — преобразует строку или столбец в двумерный массив на основе указанного количества значений в столбце.
12. TAKE — извлекает указанное количество непрерывных строк или столбцов из начала или конца массива.
13. DROP — удаляет определенное количество строк или столбцов из массива.
14. EXPAND — увеличить массив до указанного количества строк и столбцов.
15. CHOOSECOLS — возвращает указанные столбцы из массива.
16. CHOOSEROWS — извлекает указанные строки из массива.

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

XLOOKUP — это более мощный преемник VLOOKUP, HLOOKUP и LOOKUP, который может искать как в столбцах, так и в строках и возвращать несколько значений.

XMATCH — это более универсальный преемник функции MATCH, который может выполнять вертикальный и горизонтальный поиск и возвращать относительное положение указанного элемента.

Формулы динамического массива Excel

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

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

Пример 1. Новая функция динамического массива

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

Чтобы извлечь список уникальных значений из столбца, вы обычно используете сложную формулу CSE, подобную этой. В динамическом Excel все, что вам нужно, это УНИКАЛЬНАЯ формула в ее базовой форме:

=УНИКАЛЬНЫЙ(B2:B10)

Вы вводите формулу в любую пустую ячейку и нажимаете Enter. Excel немедленно извлекает все разные значения из списка и выводит их в диапазон ячеек, начиная с ячейки, в которую вы ввели формулу (в нашем случае D2). При изменении исходных данных результаты автоматически пересчитываются и обновляются.

Пример 2. Объединение нескольких функций динамического массива в одну формулу

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

=СОРТИРОВАТЬ(ФИЛЬТР(A2:C13, B2:B13=F1, "Нет результатов"))

Где A2:C13 — исходные данные, B2:B13 — значения для проверки, а F1 — критерий.

Пример 3. Использование новых функций динамического массива вместе с существующими

Поскольку новый механизм вычислений, реализованный в Excel 365, может легко преобразовывать обычные формулы в массивы, ничто не мешает вам комбинировать новые и старые функции вместе.

Например, чтобы подсчитать количество уникальных значений в определенном диапазоне, вложите функцию динамического массива UNIQUE в старый добрый COUNTA:

=COUNTA(UNIQUE(B2:B10))

Пример 4. Существующие функции поддерживают динамические массивы

Если вы укажете диапазон ячеек для функции TRIM в более старой версии, такой как Excel 2016 или Excel 2019, она вернет один результат для первой ячейки:

=ОТРЕЗАТЬ(A2:A6)

В динамическом Excel одна и та же формула обрабатывает все ячейки и возвращает несколько результатов, как показано ниже:

Пример 5. Формула ВПР для возврата нескольких значений

Как всем известно, функция ВПР предназначена для возврата одного значения на основе указанного вами индекса столбца. Однако в Excel 365 вы можете указать массив номеров столбцов, чтобы получить совпадения из нескольких столбцов:

.

=ВПР(F1, A2:C6, {1,2,3}, ЛОЖЬ)

Пример 6. Формула ТРАНСПОНС стала проще

В более ранних версиях Excel синтаксис функции ТРАНСП не оставлял места для ошибок. Чтобы повернуть данные на вашем листе, вам нужно было подсчитать исходные столбцы и строки, выбрать такое же количество пустых ячеек, но изменить ориентацию (невероятная операция в огромных рабочих листах!), ввести формулу ТРАНСП в выбранном диапазоне и нажмите Ctrl + Shift + Enter, чтобы завершить его правильно. Фу!

В динамическом Excel вы просто вводите формулу в крайнюю левую ячейку выходного диапазона и нажимаете Enter:

=ТРАНСП(A1:B6)

Готово!

Диапазон заполнения — одна формула, несколько ячеек

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

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

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

Примечание. Если какие-то другие данные блокируют диапазон разлива, возникает ошибка #SPILL. Как только мешающие данные будут удалены, ошибка исчезнет.

Дополнительные сведения см. в разделе Диапазон сброса Excel.

Ссылка на диапазон разлива (символ #)

Чтобы указать диапазон разлива, поставьте хеш-тег или символ решетки (#) после адреса верхней левой ячейки диапазона.

Например, чтобы узнать, сколько случайных чисел сгенерировано формулой СЛУЧАЙ в A2, укажите ссылку на диапазон разброса для функции СЧЕТЧИК:

=СЧЕТЧИК(A2#)

Чтобы сложить значения в диапазоне разливов, используйте:

=СУММ(A2#)

Советы:

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

Подробнее см. Оператор диапазона разлива.

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

В динамическом массиве Excel есть еще одно существенное изменение в языке формул — введение символа @, известного как оператор неявного пересечения .

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

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

Чтобы увидеть, как это работает на практике, взгляните на скриншот ниже.

В C2 есть формула динамического массива, которая распределяет результаты по многим ячейкам:

=УНИКАЛЬНЫЙ(A2:A9)

В E2 перед функцией стоит символ @, который вызывает неявное пересечение. В результате возвращается только первое уникальное значение:

=@УНИКАЛЬНЫЙ(A2:A9)

Дополнительные сведения см. в разделе Неявное пересечение в Excel.

Преимущества динамических массивов Excel

Несомненно, динамические массивы — одно из лучших усовершенствований Excel за последние годы. Как и у любой новой функции, у них есть сильные и слабые стороны. К счастью для нас, сильные стороны новых формул динамических массивов Excel просто огромны!

Простой и более мощный

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

• Извлечение уникальных значений: традиционные формулы | функции динамического массива
• Подсчет уникальных и различных значений: традиционные формулы | функции динамического массива
• Сортировка столбцов по алфавиту: традиционные формулы | функции динамического массива

Собственный для всех формул

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

Вложенные функции динамического массива

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

Относительные и абсолютные ссылки менее важны

Благодаря подходу «одна формула, много значений» нет необходимости блокировать диапазоны знаком $, поскольку технически формула находится только в одной ячейке. Таким образом, по большей части не имеет значения, использовать ли абсолютные, относительные или смешанные ссылки на ячейки (что всегда было источником путаницы для неопытных пользователей) — формула динамического массива в любом случае будет давать правильные результаты!

Ограничения динамических массивов

Новые динамические массивы — это здорово, но, как и в случае с любой новой функцией, есть несколько предостережений и соображений, о которых вам следует знать.

Результаты не могут быть отсортированы обычным способом

Диапазон разброса, возвращаемый формулой динамического массива, не может быть отсортирован с помощью функции сортировки Excel. Любая такая попытка приведет к ошибке « Вы не можете изменить часть массива ». Чтобы упорядочить результаты от меньшего к большему или наоборот, оберните текущую формулу функцией СОРТИРОВКИ. Например, так вы можете одновременно фильтровать и сортировать.

Невозможно удалить любое значение в диапазоне разлива

Ни одно из значений в диапазоне разлива не может быть удалено по той же причине: вы не можете изменить часть массива. Такое поведение ожидаемо и логично. Традиционные формулы массива CSE также работают таким же образом.

Не поддерживаются в таблицах Excel

Эта функция (или ошибка?) довольно неожиданная. Формулы динамического массива не работают в таблицах Excel, только в пределах обычных диапазонов. Если вы попытаетесь преобразовать диапазон разброса в таблицу, Excel сделает это. Но вместо результатов вы увидите только #РАЗЛИВ! ошибка.

Не работает с Excel Power Query

Результаты формул динамического массива не могут быть загружены в Power Query. Скажем, если вы попытаетесь объединить два или более диапазонов сброса вместе с помощью Power Query, это не сработает.

Динамические массивы и традиционные формулы массивов CSE

С введением динамических массивов мы можем говорить о двух типах Excel:

1. Dynamic Excel , который полностью поддерживает динамические массивы, функции и формулы. В настоящее время это только Excel 365 и Excel 2021.
2. Устаревший Excel , также известный как традиционный или преддинамический Excel, в котором поддерживаются только формулы массива Ctrl + Shift + Enter. Это Excel 2019, Excel 2016, Excel 2013 и более ранние версии.

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

Вот самые существенные отличия:

• Формула динамического массива вводится в одну ячейку и завершается обычным нажатием клавиши Enter. Чтобы завершить старомодную формулу массива, вам нужно нажать Ctrl + Shift + Enter.
• Новые формулы массива автоматически переносятся во многие ячейки. Формулы СПП должны быть скопированы в диапазон ячеек, чтобы возвращать несколько результатов.
• Выходные данные формул динамического массива автоматически изменяются по мере изменения данных в исходном диапазоне. Формулы CSE усекают выходные данные, если возвращаемая область слишком мала, и возвращают ошибки в дополнительных ячейках, если возвращаемая область слишком велика.
• Формулу динамического массива можно легко редактировать в одной ячейке. Чтобы изменить формулу СПП, необходимо выделить и отредактировать весь диапазон.
• Невозможно удалять и вставлять строки в диапазон формул СПП — сначала необходимо удалить все существующие формулы. С динамическими массивами вставка или удаление строк не является проблемой.

Обратная совместимость: динамические массивы в устаревшей версии Excel

Когда вы открываете книгу, содержащую формулу динамического массива в старом Excel, она автоматически преобразуется в обычную формулу массива, заключенную в {фигурные скобки}. Когда вы снова откроете лист в новом Excel, фигурные скобки будут удалены.

В прежних версиях Excel новые функции динамического массива и ссылки на диапазоны разлива получают префикс _xlfn, чтобы указать, что эта функция не поддерживается. Знак ссылки диапазона разброса (#) заменяется функцией ANCHORARRAY.

Например, вот как выглядит УНИКАЛЬНАЯ формула в Excel 2013 :

Большинство формул динамического массива (но не все!) будут продолжать отображать свои результаты в прежней версии Excel, пока вы не внесете в них какие-либо изменения. Редактирование формулы немедленно прерывает ее и отображает одно или несколько слов #ИМЯ? значения ошибок.

Формулы динамического массива Excel не работают

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

#РАЗЛИВ! ошибка

Когда динамический массив возвращает несколько результатов, но что-то блокирует диапазон разлива, #SPILL! возникает ошибка.

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

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

• Ошибка Excel #SPILL — причины и исправления
• Как исправить ошибку #РАЗЛИВ! ошибка с ВПР, ПОИСКПОЗ ИНДЕКС, СУММЕСЛИ

#ССЫЛКА! ошибка

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