Компания Яна недавно перешла на Office 365 профессиональный плюс. Вместе с ним появился # РАЗЛИВ! ошибка при использовании ВПР, которую он использует МНОГО. Теперь он не может использовать ВПР с фильтром. Ян знает, что было бы легко отсортировать и/или удалить # N/A, полученное из предыдущей ВПР, но эта ошибка требует у него много дополнительного времени и усилий. Он задается вопросом, есть ли способ отключить эту «функцию».
В очень недалеком прошлом Microsoft изменила способ вычисления рабочих листов. Это было ОГРОМНОЕ изменение, и вы, возможно, читали об этом в другом месте. Вот одна статья, которая предоставляет отличную информацию об изменении:
https://exceljet.net/dynamic-array-formulas-in-excel
Эта статья довольно длинный; вам нужно выделить время, чтобы переварить содержащуюся в нем информацию. Если вы создаете много формул в Excel, вы захотите это сделать – изменение программы означает, что в конечном итоге вы должны понять ее.
По сути, Microsoft отказалась от концепции функций массива (хотя они все равно будут работать), вместо этого позволив почти всем функциям, включая VLOOKUP, возвращать массив значений. Если массив возвращаемых значений не умещается в доступном пространстве, вы получаете новый #SPILL! ошибка.
Честно говоря, ответ заключается не в отключении #SPILL! ошибки; на самом деле нет способа сделать это. Ответ состоит в том, чтобы понять, что сейчас делает Excel, когда он вычисляет, а затем соответствующим образом изменяет ваши формулы.
Давайте рассмотрим пример. Допустим, у вас есть рабочий лист, в котором перечислены товары и их цены в виде простого набора данных из двух столбцов. Затем справа от него вы вводите несколько товаров и используете функцию ВПР, чтобы получить цены, связанные с каждым из этих товаров. Когда вы открываете эту книгу в более ранней версии Excel (2019 или более ранней), вы получаете отличные результаты. (См. Рис. 1.)
Рисунок 1. Простая формула ВПР в Excel 2010.
Этот снимок экрана был сделан с использованием системы Excel 2010, но он будет работать так же, если вы посмотрите на него в Excel 2016 или даже Excel 2019. Обратите внимание, что в этом примере формула ВПР в ячейке F2 (отображается в строке формул, поскольку ячейка F2 выбрана) копируется в диапазон F2: F8. Вы получаете желаемые результаты, потому что функция ВПР возвращает единственное значение из таблицы.
Теперь давайте посмотрим, что произойдет, если вы создадите ту же книгу, используя те же формулы, в версии Excel. поставляется с Office 365. В этом случае вы увидите ошибки. (См. Рис. 2.)
Рис. 2. Та же простая формула ВПР в последней версии Excel.
Обратите внимание на #SPILL! ошибки. Эта ошибка возникает из-за того, что формула ВПР теперь может возвращать более одного значения.. Фактически, когда вы используете диапазон ячеек в самом первом параметре для ВПР, теперь он будет возвращать значение для каждой ячейки в этом диапазоне. Таким образом, использование диапазона E2: E8 для первого параметра означает, что функция ВПР возвращает 7 значений. Другими словами, он автоматически возвращает массив значений. Если все эти значения не могут быть отображены, вы получите сообщение #SPILL! ошибка. Вот почему вы видите # РАЗЛИВ! ошибка в ячейках F2: F7; под ними есть вещи, которые не позволяют отображать все значения, возвращаемые в этих формулах. Вы не видите ошибку в ячейке F8, потому что под этой ячейкой нет ничего, что останавливает отображение.
Итак, как это исправить? На самом деле есть три способа исправить это. В этом конкретном примере проще всего просто удалить все в ячейках F3: F8. Это позволяет формуле в ячейке F2 правильно “перетекать” на остальные ячейки под ней.
Второй подход – изменить формулу в ячейке F2, чтобы она выглядела так:
= VLOOKUP (@ E $ 2: E $ 8, A $ 2: B $ 19,2)
Обратите внимание на использование символа @ непосредственно перед первым параметром. Это говорит Excel, что вы хотите, чтобы формула ВПР возвращала только одно значение. Другой способ изменить формулу – сделать так, чтобы в ячейке F2 она выглядела следующим образом:
= VLOOKUP (E2, A $ 2: B $ 19,2)
Теперь вы можете скопировать любую формулу из ячейки F2 в полный диапазон F2: F8, и у вас не будет проблем. Почему? Потому что (опять же) ВПР в этих случаях возвращает только одно значение, а не массив значений.
В результате лучший способ изменить ваши формулы – это либо (1) убедиться, что есть ничто не блокирует отображение полного массива значений, которые вы запрашиваете с помощью функции ВПР, или (2) изменить первый параметр так, чтобы он ссылался только на одну ячейку.