Отключение #SPILL! Ошибки (Microsoft Excel)

Компания Яна недавно перешла на Office 365 профессиональный плюс. Вместе с ним появился # РАЗЛИВ! ошибка при использовании ВПР, которую он использует МНОГО. Теперь он не может использовать ВПР с фильтром. Ян знает, что было бы легко отсортировать и / или удалить # N / A, полученное из предыдущей ВПР, но эта ошибка требует у него много дополнительного времени и усилий. Он задается вопросом, есть ли способ отключить эту «функцию».

В очень недалеком прошлом Microsoft изменила способ расчета рабочих листов.

Это было ОГРОМНОЕ изменение, и вы, возможно, читали об этом в другом месте. Вот одна статья, в которой содержится полезная информация об изменении:

Статья довольно длинная; вы захотите выделить время, чтобы переварить содержащуюся в нем информацию. Если вы создаете много формул в 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 для первого параметра означает, что функция VLOOKUP возвращает 7 значений. Другими словами, он автоматически возвращает массив значений. Если все эти значения не могут быть отображены, вы получите сообщение #SPILL! ошибка. Вот почему вы видите # РАЗЛИВ! ошибка в ячейках F2: F7; под ними есть вещи, которые не позволяют отображать все значения, возвращаемые в этих формулах. Вы не видите ошибку в ячейке F8, потому что под этой ячейкой нет ничего, что останавливает отображение.

Итак, как это исправить? На самом деле есть три способа исправить это.

В этом конкретном примере проще всего просто удалить все в ячейках F3: F8. Это позволяет формуле в ячейке F2 «просачиваться»

правильно для остальных ячеек под ним.

Второй подход — изменить формулу в ячейке F2, чтобы она выглядела так:

Обратите внимание на использование символа @ непосредственно перед первым параметром. Это говорит Excel, что вы хотите, чтобы формула ВПР возвращала только одно значение. Другой способ изменить формулу — сделать так, чтобы в ячейке F2 она выглядела так:

Теперь вы можете скопировать любую формулу из ячейки F2 в полный диапазон F2: F8, и у вас не будет проблем. Зачем? Потому что (опять же) ВПР в этих случаях возвращает только одно значение, а не массив значений.

В результате лучший способ изменить ваши формулы — это либо (1)

убедитесь, что ничто не блокирует отображение полного массива значений, которые вы запрашиваете с помощью функции ВПР, или (2) измените первый параметр так, чтобы он ссылался только на одну ячейку.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13750) применим к Microsoft Excel Excel в Office 365.