Неявное пересечение Excel и оператор @
Неявное пересечение использовалось в Excel уже много лет, но мало кто заботился об этом. Теперь, когда это больше не поведение по умолчанию в Excel 365, возникает много вопросов. Этот учебник призван дать ответы.
У вас возникает ощущение, что вы знаете о своих книгах все, а затем в начале ваших формул из ниоткуда появляется символ @. Что это значит? И как именно это работает? В двух словах, это неявный оператор пересечения, который отключает новое поведение массива по умолчанию для формулы и сообщает Excel, что нужно вернуть одно значение. Для более подробной информации, пожалуйста, продолжайте читать.
Неявное пересечение Excel
Неявное пересечение в Excel означает сокращение нескольких значений до одного значения. Обычно это происходит, когда в формулу передается массив или диапазон, который должен выводить только одно значение в одной ячейке.
Неявное пересечение в Excel 2019 – 2000
В традиционном Excel неявное пересечение является поведением по умолчанию. Он выполняется в фоновом режиме для всех формул. Логика неявного пересечения выглядит следующим образом:
- Если формула возвращает одно значение, верните это значение (на самом деле неявное пересечение в этом случае ничего не делает).
- В случае диапазона используйте значение из ячейки в той же строке или столбце, что и формула.
- В случае массива используйте верхнее левое значение.
Например, при умножении двух столбцов чисел Excel выбирает только одно число из каждого столбца в той же строке, где находится формула, и выводит результат только в одну ячейку (в нашем случае D2):
=В2:В5*С2:С5
Чтобы умножить числа в других ячейках, нужно скопировать формулу вниз.
Чтобы отключить неявное пересечение, вы должны ввести формулу массива с помощью Ctrl + Shift + Enter (поэтому традиционные формулы массива иногда называют формулы СПП). Это четко указывает Excel на необходимость обработки нескольких входных значений в виде диапазонов или массивов.
В нашем случае выберите ячейки D2:D5, введите приведенную выше формулу и подтвердите ее, нажав одновременно клавиши Ctrl + Shift + Enter. Как только вы это сделаете, формула будет заключена в {фигурные скобки}, указывая на то, что это формула массива. В результате числа в каждой строке перемножаются сразу:
Неявное пересечение в Excel 365
Внедрение динамических массивов изменило поведение по умолчанию всех формул в Excel 365. Теперь любая формула, которая потенциально может давать несколько результатов, автоматически переносит их на лист. Это делает неявное пересечение ненужным, и оно больше не запускается по умолчанию. По этой причине Excel 365 иногда называют динамический массив Excel или же ДА Excel.
Здесь все диапазоны умножаются на обычную формулу, которая вводится только в самую верхнюю ячейку (D2):
=В2:В5*С2:С5
В результате получается диапазон разливов, состоящий из 4 ячеек:
Если вы хотите, чтобы формула возвращала только одно значение, вам нужно явно включить неявный перехват. Для этого они ввели специальный оператор, и в следующем разделе вы найдете полную информацию о нем.
Оператор неявного пересечения Excel — символ @
Неявный оператор пересечения был введен в Excel 365, чтобы предотвратить поведение динамического массива по умолчанию. Если вы хотите, чтобы формула возвращала только одно значение, поставьте @ перед именем функции (или перед определенным диапазоном или массивом внутри формулы), и она будет вести себя как обычная формула без массива в додинамических версиях.
Например:
=@B2:B5*@C2:C5
Примечание. Оператор неявного пересечения поддерживается только в подписках Microsoft 365. Если вы попытаетесь добавить знак @ в более старых версиях, он будет автоматически удален после завершения формулы.
Почему @ добавляется к старым формулам?
В Excel 365 вы можете заметить символ @, добавленный к некоторым вашим формулам при открытии книги, созданной в более старой версии. По большей части это делается для того, чтобы заставить формулу вести себя так же, как в исходной версии, в которой она была создана. Другими словами, если формула возвращала одно значение в более старой версии, но возвращала несколько результатов в Excel 365, она будет автоматически иметь префикс @, чтобы деактивировать поведение массива.
Обычно оператор пересечения вставляется перед функцией, которая может возвращать многоячеечные массивы или диапазоны, такие как OFFSET, INDEX или определяемые пользователем функции.
Например, следующая формула, созданная в предварительно динамическом Excel:
=ИНДЕКС(B2:C5,,F1)
примет следующий вид в динамическом массиве Excel:
=@ИНДЕКС(B2:C5,,F1)
Причина в том, что без оператора @ формула вернет все значения из C2:C5, потому что row_num аргумент функции ИНДЕКС опущен. Чтобы обеспечить согласованное поведение во всех версиях, становится очевидным ранее незаметное неявное пересечение. Пожалуйста, сравните результаты:
В случае, если функция с потенциальным многоячеечным выводом вложена в другую функцию, которая может обрабатывать массивы и выводить один результат (например, СУММ, СЧЁТ, СРЗНАЧ и т. д.), то нет причин запускать неявное пересечение, и формула переносится в динамический Excel как есть без добавления знака @. Например:
=СРЗНАЧ(ИНДЕКС(B2:C5,,F1))
Могу ли я удалить символ @ из своих формул?
Что вы можете. Excel выполняет преобразование формулы только один раз. Если оператор @ нарушает или негативно меняет поведение ваших формул, вы можете удалить @ вручную, и он больше не появится после сохранения книги.
Каковы последствия удаления оператора неявного пересечения? В зависимости от того, что возвращает часть формулы, следующая за знаком @, возможны три результата:
- Если возвращается одно значение, изменений не будет.
- Если массив возвращается, он будет распространяться на соседние ячейки.
- Если массив возвращается, но пустых ячеек недостаточно для отображения всех значений, возникает ошибка #SPILL.
Зачем использовать оператор @ в Excel 365?
Как уже упоминалось, Excel для Microsoft 365 по умолчанию обрабатывает все формулы как формулы массива, и вам не нужно нажимать Ctrl + Shift + Enter, как вы делали это в предыдущих версиях. Если вы хотите отключить поведение массива, вставьте неявный оператор пересечения.
Пример 1. Избавьтесь от #РАЗЛИВ! ошибка
Очень распространенный сценарий — предотвращение или исправление ошибок #SPILL. Если вам удобно ссылаться на целые столбцы (что вообще не очень хорошая идея, потому что тормозит Excel), но тем не менее такая формула будет нормально работать в преддинамических версиях:
=ВПР(А:А, Г:Д, 2, ЛОЖЬ)
В динамическом Excel это приведет к ошибке #SPILL, поскольку недостаточно места для отображения почти 1,05 миллиона результатов.
Добавление @ перед аргументом lookup_value решает проблему:
=ВПР(@А:А, Г:Д, 2, ЛОЖЬ)
Когда Excel предвидит, что формула может выйти за пределы рабочего листа, он предложит исправление, и вам будет разумно его принять:
Пример 2. Заставьте формулу работать правильно в старых версиях Excel
Если вы используете подписку на Microsoft 365 и делитесь своими файлами с кем-то, кто использует более старую версию, важно обеспечить согласованное поведение формулы для всех.
Предположим, вы написали эту формулу динамического массива в Excel 365:
=В2:В5*С2:С5
В более старых версиях он будет преобразован в устаревшую формулу массива CSE:
{=B2:B5*C2:C5}
и вернуть диапазон значений в обоих случаях:
Если вы хотите вывести только один результат, добавьте @ перед каждым выражением, а затем скопируйте формулу в необходимое количество ячеек (чтобы сохранить диапазоны без изменений, не забудьте заблокировать их абсолютными ссылками на ячейки):
=@$B$2:$B$5*@$C$2:$C$5
В старых версиях Excel символ @ будет автоматически удален, и формула примет обычный вид:
=$B$2:$B$5*$C$2:$C$5
Чего не следует делать, так это смешивать в одной формуле неявное пересечение и вычисление массива! Например, если вы попытаетесь ввести что-то подобное в Excel 365:
=@B2:B5*C2:C5
Вы будете уведомлены, что такая формула не поддерживается в более старых версиях:
Если вы отклоните предложенный вариант, смешанная формула будет принята и принесет некоторые результаты (хотя они могут быть не такими, как вы ожидали). Но когда вы открываете эту формулу в предварительно динамическом Excel, функция _xlfn.SINGLE появится вместо неявного оператора пересечения:
=_xlfn.SINGLE(B2:B5)*C2:C5
Когда эта формула оценивается старой версией Excel, #ИМЯ! будет возвращена ошибка.
Неявное пересечение в таблицах Excel
Вообще говоря, поведение неявного пересечения в таблицах соответствует вашей версии Excel.
В Excel 2019 и более ранних версиях вы можете ссылаться на весь столбец, и формула будет разрешаться в одну ячейку в текущей строке.
Например, эта формула успешно умножает числа в Цена а также Кол-во столбцы:
знак равно[Price]*[Qty.]
Несмотря на то, что он относится ко всем столбцам, преддинамический Excel по-прежнему работает с отдельными значениями на уровне строки.
В Excel 365 этот подход не работает, так как неявное пересечение отключено по умолчанию. Поскольку формула возвращает несколько значений, и Excel не может поместить их все в таблицу, она предложит добавить к именам столбцов префикс с символом @:
Если у вас есть опыт работы со ссылками на таблицы, этот синтаксис должен быть вам знаком — символ @ указывает, что формула будет обрабатывать значения из столбцов. Цена а также Кол-во в том же ряду.
знак равно[@Price]*[@[Qty.]]
И это будет хорошо работать во всех версиях:
Кончик. Если вы намерены использовать функцию динамического массива для вычисления данных таблицы, обязательно поместите формулу вне таблицы, поскольку динамические массивы внутри таблиц не поддерживаются.
Вот как работает неявное пересечение в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
