Как устранить ошибки VLOOKUP в Excel

2 min


Боретесь с функцией VLOOKUP в Microsoft Excel? Вот несколько советов по устранению неполадок, которые могут вам помочь.

Есть несколько вещей, которые выявляют пот у пользователей Microsoft Excel больше, чем мысль об ошибке VLOOKUP. Если вы не слишком знакомы с Excel, VLOOKUP является одной из самых сложных или, по крайней мере, одной из наименее понятных доступных функций.

Целью VLOOKUP является поиск и возврат данных из другого столбца в вашей электронной таблице Excel. К сожалению, если вы неправильно укажете формулу VLOOKUP, Excel выдаст вам ошибку. Давайте рассмотрим некоторые распространенные ошибки VLOOKUP и объясним, как их устранять.

Ограничения VLOOKUP

Прежде чем начать использовать VLOOKUP, вы должны знать, что это не всегда лучший вариант для пользователей Excel.

Начнем с того, что его нельзя использовать для поиска данных слева от него. Он также отображает только первое найденное значение, что означает, что VLOOKUP не является опцией для диапазонов данных с дублированными значениями. Ваш столбец поиска также должен быть самым дальним левым столбцом в вашем диапазоне данных.

В приведенном ниже примере самый дальний столбец (колонка А) используется в качестве столбца поиска. В этом диапазоне нет дублированных значений и данных поиска (в данном случае данные из колонка B) находится справа от столбца поиска.

Пример формулы VLOOKUP, используемой в Excel

Функции INDEX и MATCH могли бы стать хорошей альтернативой, если возникнет проблема с любой из этих проблем, как и новая функция XLOOKUP в Excel, которая в настоящее время находится в стадии бета-тестирования.

VLOOKUP также требует, чтобы данные были упорядочены в строки, чтобы иметь возможность точного поиска и возврата данных. HLOOKUP будет хорошей альтернативой, если это не так.

Есть некоторые дополнительные ограничения для формул VLOOKUP, которые могут вызвать ошибки, как мы объясним далее.

Ошибки VLOOKUP и # N / A

Одной из самых распространенных ошибок VLOOKUP в Excel является # N / A ошибка. Эта ошибка возникает, когда VLOOKUP не может найти искомое значение.

Начнем с того, что значение поиска может отсутствовать в вашем диапазоне данных, или вы, возможно, использовали неправильное значение. Если вы видите ошибку N / A, дважды проверьте значение в формуле VLOOKUP.

Если значение верное, то вашего поискового значения не существует. Это предполагает, что вы используете VLOOKUP для поиска точных совпадений, с range_lookup аргумент установлен в ЛОЖНЫЙ,

VLOOKUP возвращает ошибку NA в Excel

В приведенном выше примере, поиск Студенческий билет с номер 104 (в клетке G4) возвращает Ошибка # N / A потому что минимальный идентификационный номер в диапазоне 105,

Если range_lookup аргумент в конце формулы VLOOKUP отсутствует или установлен в ПРАВДА тогда VLOOKUP вернет ошибку # N / A, если ваш диапазон данных не отсортирован в порядке возрастания.

Он также вернет ошибку # N / A, если ваше значение поиска меньше, чем самое низкое значение в диапазоне.

Другой пример ошибки NA с VLOOKUP в Excel, из-за того, что диапазон данных не сортируется должным образом

В приведенном выше примере Студенческий билет значения перепутаны. Несмотря на ценность 105 существующий в ассортименте, VLOOKUP не может выполнить правильный поиск с помощью range_lookup аргумент установлен в ПРАВДА так как колонка А не отсортировано по возрастанию

Другие распространенные причины ошибок # N / A включают использование столбца поиска, который расположен не дальше всего, и использование ссылок на ячейки для значений поиска, которые содержат числа, но отформатированы как текст или содержат лишние символы, такие как пробелы.

Устранение ошибок #VALUE

#ЗНАЧЕНИЕ ошибка обычно является признаком того, что формула, содержащая функцию VLOOKUP, в некотором роде неверна.

В большинстве случаев это обычно происходит из-за ячейки, на которую вы ссылаетесь в качестве значения поиска. максимальный размер значения поиска VLOOKUP 255 символов,

Если вы имеете дело с ячейками, которые содержат более длинные строки символов, VLOOKUP не сможет их обработать.

Формула VLOOKUP, возвращающая ошибку VALUE в Excel

Единственный обходной путь для этого – заменить формулу VLOOKUP комбинированной формулой INDEX и MATCH. Например, если в одном столбце содержится ячейка со строкой из более чем 255 символов, для поиска этих данных можно использовать вложенную функцию MATCH внутри INDEX.

В приведенном ниже примере ПОКАЗАТЕЛЬ возвращает значение в ячейке B4 используя диапазон в колонка А определить правильный ряд. Это использует вложенный СОВПАДЕНИЕ функция для идентификации строки в колонка А (содержит 300 символов), который соответствует ячейке H4,

В данном случае это клетка A4, с ПОКАЗАТЕЛЬ возврате 108 (значение B4).

Пример комбинированной формулы INDEX и MATCH в Excel

Эта ошибка также появится, если вы использовали неправильную ссылку на ячейки в своей формуле, особенно если вы используете диапазон данных из другой книги.

Для правильной работы формулы ссылки на рабочие тетради должны быть заключены в квадратные скобки.

Формула VLOOKUP, ссылающаяся на несколько книг Excel

Если вы столкнулись с ошибкой #VALUE, дважды проверьте формулу VLOOKUP, чтобы убедиться, что ссылки на ваши ячейки верны.

#NAME и VLOOKUP

Если ваша ошибка VLOOKUP не является ошибкой #VALUE или ошибкой # N / A, то это, вероятно, #ИМЯ ошибка. Прежде чем паниковать при мысли об этом, будьте уверены – это самая простая ошибка VLOOKUP, которую нужно исправить.

Формула опечатки VLOOKUP в Excel, возвращающая ошибку NAME

Ошибка #NAME появляется, когда вы ошиблись в функции в Excel, будь то VLOOKUP или другая функция, такая как SUM. Нажмите на ячейку VLOOKUP и проверьте, правильно ли вы написали VLOOKUP.

Если других проблем нет, ваша формула VLOOKUP будет работать после исправления этой ошибки.

Использование других функций Excel

Это смелое утверждение, но такие функции, как VLOOKUP, изменят вашу жизнь. По крайней мере, это изменит вашу рабочую жизнь, сделав Excel более мощным инструментом для анализа данных.

Если VLOOKUP вам не подходит, воспользуйтесь этими функциями Excel.

 


0 Comments

Добавить комментарий