Разлив в Excel — туториал с примерами
В этом руководстве вы найдете все, что вам нужно знать о функции Spill. Что такое разлив в Excel? Как избавиться от сыпи? Как исправить ошибку разлива? И многое другое.
Недавнее обновление динамического массива принесло множество вдохновляющих новых возможностей. Но все сосредоточено вокруг основной концепции «разлива». Итак, давайте внимательно рассмотрим эту функцию и то, как вы можете использовать ее для улучшения работы с Excel.
Что означает разлив в Excel?
Термин «разлив» или «разлив» относится к поведению Excel, когда формула, которая привела к нескольким значениям, выводит или «разливает» все эти значения в соседние ячейки.
Например, чтобы отфильтровать диапазон A2:C9 в соответствии с критериями в F1, вы можете использовать эту формулу:
=ФИЛЬТР(A2:C9, B2:B9=F1)
Теперь наблюдайте за красотой разлива. Вы вводите формулу всего в одну ячейку (E4) и нажимаете Enter. Excel определяет, сколько ячеек необходимо для размещения результатов, и автоматически заполняет все эти ячейки без необходимости выполнять какие-либо дополнительные действия.
Область разлива, также называемая диапазоном разлива, окружена выделенной рамкой, указывающей, что все значения внутри нее рассчитываются по единой формуле в верхней левой ячейке.
В динамическом Excel поведение сброса является родным для всех формул, включая традиционные, которые изначально не были предназначены для обработки массивов.
Какая версия Excel мне нужна для функции Spill?
Разлив — это эксклюзивная функция динамического Excel. Он был выпущен для подписчиков Office 365 в январе 2020 года. В настоящее время он поддерживается в следующих подписках Microsoft 365:
- Эксель 365 для Windows
- Excel 365 для Mac
- Excel 365 для Apple
- Excel 365 для Android
- Excel 365 для Windows Mobile
Excel Online не поддерживает переполнение или динамические массивы.
Что такое формула разлитого массива?
Формула расширенного массива — это другое название формулы динамического массива, которая возвращает результаты в нескольких ячейках.
Пожалуйста, не путайте его со старомодной формулой массива CSE, которая требует нажатия Ctrl + Shift + Enter для завершения. В отличие от устаревших формул массива, динамические формулы вводятся в одну ячейку и завершаются с помощью обычной клавиши Enter.
Раньше любая формула Excel возвращала результат фиксированного размера. Обычная формула всегда выводит только одно значение в одной ячейке. Формула массива может возвращать значения в нескольких ячейках, но вам нужно было ввести (или скопировать) ее во все эти ячейки, а затем нажать Ctrl + Shift + Enter, чтобы явно указать формуле вычислять массив. Формула динамического массива не нуждается в каких-либо инструкциях — если она потенциально может возвращать несколько значений, она сделает это автоматически!
Чтобы лучше понять разницу, давайте рассмотрим этот простой пример. Предположим, вы хотите вычислить 10% чисел в A3:A6. Это можно сделать тремя различными способами:
Обычная формула: вводится в ячейку B3 и копируется вниз по ячейке B6. В результате получается одно значение.
=А3*10%
Формула массива CSE с несколькими ячейками: вводится в B3:B6 и заполняется комбинацией клавиш Ctrl + Shift + Enter. Результатом является несколько значений в предопределенном количестве ячеек.
{=A3:A6*10%}
Формула динамического массива (иначе формула разлитого массива): вводится в ячейке B3 и завершается обычным нажатием Enter. Результатом является динамический диапазон разлива.
=А3:А6*10%
На изображении ниже показаны все три формулы в действии:
Что такое #РАЗБИВКА в Excel?
#ПРОЛИВАТЬ! является ошибкой, указывающей на то, что что-то предотвращает проливание формулы. Это может быть вызвано различными причинами, такими как непустые ячейки, перекрытие формул, объединенные ячейки и т. д.
Чтобы узнать основную причину проблемы, нажмите значок предупреждения с восклицательным знаком, который появляется рядом с ошибкой, и прочитайте текст в первой строке:
В большинстве случаев #РАЗЛИВ! ошибка вызвана некоторыми другими данными или формулами в предполагаемом диапазоне разлива. Как только блокировка будет устранена, ошибка исчезнет, и диапазон будет заполнен результатами формулы, как и ожидалось.
Для получения дополнительной информации см.:
Как удалить разлив в Excel?
В Excel 365 функция переноса данных глубоко интегрирована на уровне ядра. Глобально отключить разлив в Excel невозможно. Однако вы можете запретить определенной формуле заполнять несколько ячеек, используя неявный оператор пересечения (@).
Например, следующая формула XLOOKUP вызовет ошибку #SPILL после того, как не удастся передать более миллиона результатов:
=XLOOKUP(Е:Е, А:А, С:С)
А этот уменьшает массив lookup_value (E:E) до одного значения и прекрасно работает:
=XLOOKUP(@E:E, A:A, C:C)
Дополнительные сведения см. в разделе Неявное пересечение в Excel.
Эксплуатация Excel — основные выводы
Ниже приведен краткий обзор ключевых моментов функции разлива Excel:
- Формула разнесенного массива вводится только в одну ячейку и автоматически заполняет несколько ячеек.
- Размер диапазона сброса контролируется Excel, а не пользователем.
- Только верхняя левая ячейка в диапазоне разлива содержит формулу, и только эту ячейку можно редактировать. При изменении формулы значения в области разлива будут обновлены. При удалении формулы все результаты исчезнут.
- Глобально отключить разлив в Excel невозможно. Однако вы можете заставить определенную формулу возвращать одно значение, а не несколько значений.
- Формулы пропущенного массива не работают в таблицах Excel. Если вам нужно рассчитать данные таблицы, поместите формулу динамического массива вне таблицы.
- Формулы динамического массива, ссылающиеся на другую книгу, работают, только если открыты оба файла. Если исходная книга закрыта, формула связанного разлитого массива вернет #ССЫЛКА! ошибка.
Это основа разлива Excel. Если есть что-то еще, что вы хотели бы узнать об этом, просто напишите комментарий, и мы постараемся ответить 🙂
