#ПРОЛИВАТЬ! ошибка с Excel VLOOKUP, INDEX MATCH и SUMIF решена
В руководстве объясняется, что может вызвать ошибку #SPILL в формулах ПОИСКПОЗ ПОИСКПОЗ, ВПР, СУММЕСЛИ и СЧЁТЕСЛИ, а также способы эффективного устранения этой ошибки.
Достаточно грустно, когда совершенно новая функция отказывается работать в вашем Excel. Но еще больше разочаровывает, когда старая добрая штука внезапно перестает работать, и вы получаете сообщение об ошибке формулы, которая отлично работала годами.
В приведенных ниже примерах показано, как исправить несколько распространенных формул, которые были нарушены из-за того, что неявное пересечение больше не выполняется невидимо в Excel. Если вы никогда раньше не слышали этот термин, я рекомендую вам внимательно прочитать руководство по ошибке #SPILL, чтобы понять, что происходит за кулисами.
#Ошибка SPILL с формулой Excel VLOOKUP
Вот стандартная формула ВПР, которая отлично работает в предварительно динамическом Excel (2019 и более ранних версиях) и срабатывает при ошибке #SPILL в Excel 365:
=ВПР(А:А, Г:Д, 2, ЛОЖЬ)
Как мы можем разумно предположить, проблема заключается в первом аргументе (красная ссылка выше), который заставляет функцию ВПР искать все значения в столбце А, который содержит более миллиона ячеек (точное число — 1 048 576)! Раньше это не было проблемой — Excel мог искать только одно значение за раз, поэтому он отбрасывал все значения, кроме одного, в той же строке, что и формула. Такое поведение называется подразумеваемым или неявным пересечением.
С введением динамических массивов все функции Excel получили возможность обрабатывать и выводить несколько значений, даже те, которые изначально не были предназначены для работы с массивами! Таким образом, каждый раз, когда функция ВПР получает массив значений поиска, она пытается обработать их все. Если для вывода всех результатов недостаточно места, вы увидите ошибку #SPILL.
Чтобы устранить ошибку разлива Excel VLOOKUP, вы можете использовать один из следующих методов.
Ищите диапазон, а не столбец
Поскольку у нас есть только 3 значения поиска, мы ограничиваем аргумент искомое_значение тремя ячейками:
=ВПР(A3:A5, D:E, 2, ЛОЖЬ)
Формулу нужно ввести только в одну ячейку, и она автоматически заполнит столько ячеек, сколько необходимо. Результатом является диапазон разлива, подобный этому:
Поиск одного значения
Напишите формулу для первого искомого значения и скопируйте ее в нужное количество ячеек:
=ВПР(A3, D:E, 2, ЛОЖЬ)
Это мой предпочтительный вариант, поскольку его проще всего реализовать и он безупречно работает во всех версиях Excel, в пределах обычных диапазонов и таблиц.
Принудительное неявное пересечение
Чтобы ограничить массив одним искомым значением, поместите оператор пересечения @ перед ссылкой на столбец:
=ВПР(@А:А, Г:Д, 2, ЛОЖЬ)
Как и в предыдущем примере, вы вводите формулу в одну ячейку и перетаскиваете ее вниз по столбцу.
Какое бы решение вы ни выбрали, ошибка #SPILL должна исчезнуть, и ваша формула ВПР начнет нормально работать в Excel 365.
Ошибка #SPILL в формуле Excel INDEX MATCH
В случае, если вы используете комбинацию функций ИНДЕКС и ПОИСКПОЗ для извлечения совпадений, ошибка #SPILL может возникнуть по той же причине — недостаточно свободного места для пролитого массива.
Например, вот формула, которая безупречно возвращает данные о продажах в Excel 2019 и более ранних версиях, но отказывается работать в Excel 365:
=ИНДЕКС(E:E, ПОИСКПОЗ(A:A, D:E, 0))
Средство уже известно — уменьшите количество искомых значений, применив один из следующих приемов.
- Поиск диапазона, а не столбца:=ИНДЕКС(E:E, MATCH(A3:A5, D:D, 0))
- Найдите одно значение: = ИНДЕКС (E: E, MATCH (A3, D: D, 0))
- Включить неявное пересечение — добавьте символ @ перед ссылкой на столбец, чтобы Excel обрабатывал только одно значение: = ИНДЕКС (E: E, MATCH (@ A: A, D: D, 0))
Результатом 1-й формулы является динамический разлитый массив, который избавляет вас от необходимости копировать формулу в другие ячейки. Ограничение состоит в том, что динамические массивы работают только в диапазоне, а не в таблице.
Вторая и третья формулы возвращают одно значение, которое также может принимать таблица. Если ваши данные организованы как обычный диапазон, перетащите формулу вниз, чтобы скопировать ее в ячейки ниже. В таблице формула будет распространяться автоматически. В последнем случае вы также можете использовать структурированную справочную нотацию, относящуюся к заголовкам столбцов:
=ИНДЕКС(E:E, СОВПАДЕНИЕ([@[Seller ]], Д:Д, 0))
На скриншотах ниже показана третья формула в действии:
Ошибка #SPILL в формулах Excel СУММЕСЛИ и СЧЁТЕСЛИ
Когда формула СУММЕСЛИ, СЧЁТЕСЛИ, СУММЕСЛИМН или СЧЁТЕСЛИМН возвращает ошибку #SPILL, это может быть вызвано множеством различных факторов. Ниже рассмотрены наиболее часто встречающиеся из них.
Диапазон разлива слишком большой
Очень типичной причиной является предоставление целого столбца для критериев. Да, раньше это работало в старых версиях Excel, но не сейчас, так как новая функция переполнения выдает почти 1,05 миллиона результатов, и для их размещения недостаточно места!
Для этого примера попробуем найти общий объем продаж трех продавцов (A3:A5). В Excel 2019 и более ранних версиях вы можете успешно использовать приведенный ниже синтаксис. В Excel 365 вы получите ошибку #SPILL:
=СУММЕСЛИ(Д:Д, А:А, Е:Д)
Если вы внимательно следили за предыдущими примерами, то знаете, что ошибка может быть устранена тремя различными способами:
- Используйте диапазон для ваших критериев, а не весь столбец:=СУММЕСЛИ(D:D, A3:A5, E:E)
- Определите одну ячейку для критериев и скопируйте формулу вниз:=СУММЕСЛИ(D:D, A3, E:E)
- Включите неявный оператор пересечения (@), чтобы ограничить ссылку на столбец одной ячейкой:=СУММЕСЛИ(D:D, @A:A, E:E)
Аналогичным образом вы можете получить количество продаж для каждого человека, используя функцию СЧЁТЕСЛИ:
=СЧЁТЕСЛИ(D:D, A3:A5)
=СЧЁТЕСЛИ (D:D, A3)
=СЧЁТЕСЛИ (D:D, @A:A)
Помните, что первая формула автоматически переносится в строки ниже и может использоваться только в диапазоне, а не в таблице.
Вторая и третья формулы возвращают одно значение, поэтому вы вводите их в первую ячейку, а затем, как обычно, копируете вниз по столбцу.
Критерии сформулированы неправильно
Критерии СУММЕСЛИ и СЧЁТЕСЛИ также являются распространённым источником проблем. Иногда люди переусердствуют и пишут критерии как D3=”carter” или D3:D11=”carter” или D:D=”carter”. Все три выражения неверны и приводят к тому, что формула возвращает ноль или ошибку #SPILL!
Правильным способом является либо ссылка на диапазон/ячейку, как в приведенных выше примерах, либо текст, заключенный в кавычки:
=СУММЕСЛИ(D:D, “перевозчик”, E:E)
Чтобы узнать больше о том, что приемлемо в критериях, а что нет, могут быть полезны следующие рекомендации: Синтаксис критериев СУММЕСЛИ.
Неправильный порядок аргументов
В версиях функции СУММЕСЛИ в единственном и множественном числе порядок аргументов разный:
- С SUMIF вы сначала определяете диапазонтогда критериитогда сумма_диапазон (по желанию).
- В случае СУММЕСЛИМН первым аргументом должен быть сумма_диапазона потом диапазон/критерии пары.
Если вы что-то путаете, возникает ошибка #SPILL.
В нашем примере критерии_диапазон это Д:Д и сумма_диапазон Понимаю. Если вы поставите одно вместо другого, формула снова выдаст ошибку #SPILL:
=СУММЕСЛИМН(D:D, A3:A5, E:E)
Расположите аргументы в правильном порядке, и СУММЕСЛИ даст вам желаемый результат:
=СУММЕСЛИМН(E:E, D:D, A3:A5)
Вот как можно устранить ошибку #SPILL с помощью Excel INDEX MATCH, VLOOKUP, SUMIF и других функций. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
