Объяснение диапазона разлива Excel
В учебнике простым языком объясняется понятие «диапазон разлива» и даются ответы на наиболее распространенные вопросы.
Переполнение — одна из новых функций, представленных в Excel 365 как часть функциональных возможностей динамических массивов. И чтобы максимально использовать это, важно понимать ключевые термины, чтобы вы могли следовать примерам и эффективно создавать свои собственные формулы динамического массива. Основное внимание в этом руководстве уделяется пониманию концепции диапазона разлива.
Диапазон разлива в Excel
Когда формула динамического массива создает несколько значений в результате вычисления, она выводит или выводит все эти значения на лист.
Диапазон переполнения относится к массиву значений, возвращаемых формулой динамического массива в соседних ячейках.
Когда вы выбираете любую ячейку в разлитой области, весь диапазон выделяется синей рамкой, указывающей, что все внутри него вычисляется по формуле в самой верхней ячейке.
Раньше при работе с традиционными формулами массива СПП нам приходилось угадывать, в какое количество ячеек копировать формулу. Теперь вы просто вводите формулу в одну ячейку, а Excel позаботится обо всем остальном.
Диапазон разливов является динамическим и автоматически обновляется по мере изменения исходных данных. Когда вы добавляете элементы в исходные данные или удаляете их из них, разбросанный диапазон может расширяться или сужаться.
Если вы удалите формулу в первой ячейке, все результаты исчезнут.
Оператор диапазона разлива (символ #)
Чтобы обратиться ко всему диапазону разброса, поставьте хеш-тег (#) после адреса верхней левой ячейки в диапазоне (т. е. ячейки, содержащей формулу, которая возвращает массив значений).
Например, у нас есть УНИКАЛЬНАЯ формула в C2, которая возвращает несколько результатов. Чтобы сослаться на все эти значения, введите:
=С2#
Чтобы сослаться на тот же диапазон разливов с другого листа, укажите имя листа, за которым следует восклицательный знак:
=Лист1!C2#
Чтобы сослаться на диапазон сброса из другой книги, также включите имя файла, заключенное в квадратные скобки:
знак равно[Book1.xlsx]Лист1!$C2#
Это то же самое, что и ссылка на весь диапазон (=C2:C8), но в отличие от обычной ссылки на диапазон, ссылка на разлив автоматически отражает изменения в размере диапазона. Такое поведение особенно полезно, когда вы «скармливаете» диапазон сброса другой функции, динамической или обычной. Вы даже можете использовать его для именованных диапазонов и проверки данных, например, для создания динамического зависимого выпадающего списка.
В нашем случае, чтобы узнать, сколько уникальных имен возвращает формула динамического массива в C2, укажите ссылку на диапазон разброса для функции COUNTA:
=СЧЕТЧИК(C2#)
Кончик. Чтобы быстро перейти к диапазону разливов, выберите все ячейки внутри синего прямоугольника с помощью мыши. 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, верно? Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!