Объяснение диапазона разлива Excel

В учебнике простым языком объясняется понятие «диапазон разлива» и даются ответы на наиболее распространенные вопросы.

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

Диапазон разлива в Excel

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

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

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

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

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

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

Оператор диапазона разлива (символ #)

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

Например, у нас есть УНИКАЛЬНАЯ формула в C2, которая возвращает несколько результатов. Чтобы сослаться на все эти значения, введите:

=С2#

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

=Лист1!C2#

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

знак равно[Book1.xlsx]Лист1!$C2#
Оператор диапазона разлива

Это то же самое, что и ссылка на весь диапазон (=C2:C8), но в отличие от обычной ссылки на диапазон, ссылка на разлив автоматически отражает изменения в размере диапазона. Такое поведение особенно полезно, когда вы «скармливаете» диапазон сброса другой функции, динамической или обычной. Вы даже можете использовать его для именованных диапазонов и проверки данных, например, для создания динамического зависимого выпадающего списка.

В нашем случае, чтобы узнать, сколько уникальных имен возвращает формула динамического массива в C2, укажите ссылку на диапазон разброса для функции COUNTA:

=СЧЕТЧИК(C2#)
Использование ссылки на разлив в Excel

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

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

3 вещи, которые вы должны знать о диапазоне разливов Excel

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

Редактировать можно только верхнюю левую ячейку

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

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

Что входит и что не входит в круг разливов

Как упоминалось выше, поведение сброса является динамическим — когда исходные данные изменяются, область сброса корректируется соответствующим образом. Например, если вы измените исходный список так, чтобы он содержал еще одно уникальное имя (Картер), диапазон разлива автоматически расширяется и включает это имя.
Диапазон разливов автоматически расширяется, чтобы отразить изменения в исходных данных.

Однако диапазон разлива не обновляется, когда новые записи добавляются за пределами указанного диапазона.

Например, если формула ссылается на A2:A10, а новый элемент вводится в A11, он не будет отображаться в результатах:
Диапазон разливов не отражает изменений за пределами указанного диапазона.

Чтобы новый элемент был включен в результаты формулы, вам необходимо изменить указанный диапазон на A2: A11.

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

#ИГРА ошибка

Если что-то блокирует диапазон переноса (например, другие данные, пробелы, непечатаемые символы, формулы в ячейках ниже и т. д.), возникает ошибка #SPILL. Чтобы устранить ошибку, очистите мешающие ячейки. Дополнительные сведения см. в статье Ошибка SPILL в Excel — причины и способы устранения.
Диапазон разлива заблокирован другими данными.

Чтобы подвести итоги, мы публикуем ответы на три часто задаваемых вопроса (3 кажется магическим числом в этом уроке 🙂

Как очистить диапазон разливов в Excel?

В зависимости от того, чего вы пытаетесь достичь, есть два решения:

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

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

Как изменить диапазон разлива в Excel?

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

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

Как исправить диапазон разлива, если он слишком большой?

Размер диапазона разброса определяется Excel и изменяется автоматически по мере обновления исходных данных или формулы.

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

Если вы столкнулись с ошибкой #SPILL, ознакомьтесь со следующими решениями: Как исправить ошибку #SPILL! ошибка в экселе.

Как избавиться от диапазона разлива в Excel?

Опять же, ответ зависит от вашей конечной цели.

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

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

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

Например, приведенная ниже формула динамического массива умножает каждое значение в A2:A5 на 10%. В результате получается диапазон разлива, занимающий 4 ячейки.

= А2:А5*10%

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

=@А:А*10%

или же

=А2*10%
Предотвратите попадание формулы в несколько ячеек.

Теперь вы уже не новичок в том, что касается диапазона разлива Excel, верно? Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также могут заинтересовать:

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *