Почему не работает впр в excel остается формула
Не работает ВПР в Excel н д? Причина в том, что формула не может найти необходимое значение, к примеру, из-за отсутствия искомого параметра в файле. Убедитесь, что такой показатель имеется в первоначальных данных, проверьте тип значений, удалите лишние пробелы, используйте способы точного / ориентировочного совпадения, задействуйте правильные аргументы и т. д. Ниже рассмотрим, в чем могут быть причины, и как действовать для восстановления работоспособности Эксель.
Причины и пути решения в Excel
Существует много причин, почему не работает ВПР в Excel и появляется надпись Н / Д. В каждой из ситуаций необходимо индивидуально подходить к решению вопроса с учетом возникшей неисправности.
Наиболее эффективный метод
В ситуации, когда не работает функция ВПР в Excel, проверьте наличие элемента на листе или задействуйте в формуле функцию обработки ошибок, к примеру, =ЕСЛИОШИБКА(ФОРМУЛА();0). В таком случае при появлении сбоев в расчете показывается ноль, а в ином случае — результат формулы. Можно дополнить запись “”, чтобы ничего не показывалось, или внести в скобки какую-либо запись.
Ошибка в типе параметров
Характерная причина, почему не работает ВПР в Excel — нахождение исходных / искомых данных к различным типам. К примеру, если вы используете ВПР в виде числа, а исходные данные сохраняются в качестве текста. Для решения вопроса убедитесь, что типы информации идентично. Для проверки формата сделайте следующее:
- Выберите ячейку (одну или несколько).
- Жмите правой кнопкой мышки.
- Выберите формат ячеек, а дальше Число.
Для принудительного внесения изменений нужно изменить формат для всего столбца. Для начала примените требуемое форматирование, а после выберите «Данные», «Текст по столбцам» и «Готово». После этого проверьте, появляется Н Д или нет.
Лишние пробелы
Распространенная причина, почему не работает формула ВПР в Excel, состоит в наличии пробелов. Для их удаления используйте функцию СЖПРОБЕЛЫ.
Ошибки метода поиска совпадения
Следующее объяснение, почему не срабатывает ВПР в Excel и возвращается Н Д — ошибки в применении метода совпадения. По умолчанию у опции ВПР имеется аргумент «интервальный просмотр», который дает команду на поиск точного совпадения даже при отсутствии сортировки данных в таблице.
Для поиска точного совпадения введите для аргумента «интервальный_просмотр» показатель ЛОЖЬ.
При этом учтите, что ИСТИНА, которое дает возможность поиска приблизительного параметра, может вернуть ошибку Н / Д. При использовании опции ПОИСКПОЗ попробуйте поменять параметр аргумента «тип_сопоставления» для указания порядке сортировки таблицы.
Не соответствие числа строк / столбцов заданному диапазону
В ситуации, когда не работает ВПР в Excel и вылетает ошибка Н / Д, сделайте дополнительную проверку. Вам нужно убедиться, что диапазон, в отношении которого ссылается формула, правильный. Как вариант, можно ввести формулу массива в меньшее / большее количество ячеек с учетом ссылки на диапазон.
В ячейке введена надпись Н / Д или Н Д
Другие ошибки
Дополнительно стоит выделить и ряд других ситуаций, когда ВПР в Excel по какой-то причине не работает:
- В используемой формуле нет одного или более аргументов. Для исправления проблемы введите все необходимые документы и проверьте, работает опция или нет. Для контроля можно использовать Visual Basic.
- Пользовательская опция недоступна. Для исправления проблемы убедитесь, что документ Excel с пользовательской функцией открыт, а опция работает корректно.
- Макрос имеет функцию, которая возвращает Н Д. Если ВПР не работает по этой причине, для исправления ошибки убедитесь в правдивости аргументов и их нахождении в нужных местах.
- Изменение защищенного файл с опцией ЯЧЕЙКА. Для исправления ситуации, когда ВПР в Excel не работает, жмите на комбинацию Ctrl+Alt+F9.
- Столбец не является первым слева дли поискового диапазона. Для решения проблемы нужно ввести соответствующий параметр и проверить, появляется ли Н Д. Как вариант, можно использовать функции ИНДЕКС и ПОИСКПОК в качестве гибкой альтернативы для ВПР.
- Неправильное форматирование числа. Бывают ситуации, когда цифры указаны в текстовом формате. Это часто происходит при импортировании сведений из внешней базы данных или при вводе апострофа перед числом для сохранения нуля в начале. Для решения проблемы жмите по ошибке и укажите Convert to Number. При появлении Н Д для многих чисел выделите их и жмите правой кнопкой мышки, а после выберите Format Cells и вкладку Число и Числовой.
Выше рассмотренные основные причины, почему не работает ВПР в Excel, а также описаны базовые шаги для устранения ошибки Н Д. Всегда начинайте с проверки правильности ввода параметра, а после переходите к другим вариантам.
Что за функция
В завершение кратко рассмотрим, что это за опция ВПР в Excel, и как она работает. Простыми словами, это опция, позволяющая переставлять данные из одной таблицы в соответствующие параметры другой. Английское название опции звучит как VLOOKUP. Это очень полезная опция, позволяющая сэкономить время и одновременно обработать большое количество параметров.
К примеру, в вас есть две таблицы. Первая — цены и названиями, вторая — заказ на покупку продукции. Осуществлять поиск в первом документе и пытаться вписать цену в заказ трудно. Необходимо, чтобы работа проходила автоматически. Для этого достаточно найти нужное значение в 1-м столбце и вернуть его содержимое из столбца той же строки, где находится название.
В комментариях расскажите, приходилось ли вам пользовался опцией ВПР, и случалась ли ситуация, когда она не работает в Excel, и появляется ошибка Н Д. Отдельно поделитесь, какие варианты решения вопроса можно использовать.
В нескольких предыдущих статьях мы изучили различные грани функции ВПР в Excel. Если Вы читали их внимательно, то сейчас должны быть экспертом в этой области. Однако не без причины многие специалисты по Excel считают ВПР одной из наиболее сложных функций. Она имеет кучу ограничений и особенностей, которые становятся источником многих проблем и ошибок.
1. Искомое значение написано с опечаткой
Хорошая мысль проверить этот пункт в первую очередь! Опечатки часто возникают, когда Вы работаете с очень большими объёмами данных, состоящих из тысяч строк, или когда искомое значение вписано в формулу.
- Искомое значение меньше наименьшего значения в просматриваемом массиве.
- Столбец поиска не упорядочен по возрастанию.
4. Столбец поиска не является крайним левым
Решение: Если нет возможности изменить структуру данных так, чтобы столбец поиска был крайним левым, Вы можете использовать комбинацию функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), как более гибкую альтернативу для ВПР.
5. Числа форматированы как текст
Это обычно случается, когда Вы импортируете информацию из внешних баз данных или когда ввели апостроф перед числом, чтобы сохранить стоящий в начале ноль.
Наиболее очевидные признаки числа в текстовом формате показаны на рисунке ниже:
Кроме этого, числа могут быть сохранены в формате General (Общий). В таком случае есть только один заметный признак – числа выровнены по левому краю ячейки, в то время как стандартно они выравниваются по правому краю.
Решение: Если это одиночное значение, просто кликните по иконке ошибки и выберите Convert to Number (Конвертировать в число) из контекстного меню.
Если такая ситуация со многими числами, выделите их и щелкните по выделенной области правой кнопкой мыши. В появившемся контекстном меню выберите Format Cells (Формат ячеек) > вкладка Number (Число) > формат Number (Числовой) и нажмите ОК.
6. В начале или в конце стоит пробел
Решение 1: Лишние пробелы в основной таблице (там, где функция ВПР)
Если лишние пробелы оказались в основной таблице, Вы можете обеспечить правильную работу формул, заключив аргумент lookup_value (искомое_значение) в функцию TRIM (СЖПРОБЕЛЫ):
Решение 2: Лишние пробелы в таблице поиска (в столбце поиска)
Так как это формула массива, не забудьте нажать Ctrl+Shift+Enter вместо привычного Enter, чтобы правильно ввести формулу.
1. Искомое значение длиннее 255 символов
Решение: Используйте связку функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH). Ниже представлена формула, которая отлично справится с этой задачей:
2. Не указан полный путь к рабочей книге для поиска
Если Вы извлекаете данные из другой рабочей книги, то должны указать полный путь к этому файлу. Если говорить точнее, Вы должны указать имя рабочей книги (включая расширение) в квадратных скобках [ ], далее указать имя листа, а затем – восклицательный знак. Всю эту конструкцию нужно заключить в апострофы, на случай если имя книги или листа содержит пробелы.
Вот полная структура функции ВПР для поиска в другой книге:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Настоящая формула может выглядеть так:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Эта формула будет искать значение ячейки A2 в столбце B на листе Sheet1 в рабочей книге New Prices и извлекать соответствующее значение из столбца D.
Для получения дополнительной информации о функции ВПР, ссылающейся на другой файл Excel, обратитесь к уроку: Поиск в другой рабочей книге с помощью ВПР.
3. Аргумент Номер_столбца меньше 1
Трудно представить ситуацию, когда кто-то вводит значение меньше 1, чтобы обозначить столбец, из которого нужно извлечь значение. Хотя это возможно, если значение этого аргумента вычисляется другой функцией Excel, вложенной в ВПР.
Решение очевидно – проверьте правописание!
ВПР не работает (ограничения, оговорки и решения)
Помимо достаточно сложного синтаксиса, ВПР имеет больше ограничений, чем любая другая функция Excel. Из-за этих ограничений, простые на первый взгляд формулы с ВПР часто приводят к неожиданным результатам. Ниже Вы найдёте решения для нескольких распространённых сценариев, когда ВПР ошибается.
1. ВПР не чувствительна к регистру
Функция ВПР не различает регистр и принимает символы нижнего и ВЕРХНЕГО регистра как одинаковые. Поэтому, если в таблице есть несколько элементов, которые различаются только регистром символов, функция ВПР возвратит первый попавшийся элемент, не взирая на регистр.
Решение: Используйте другую функцию Excel, которая может выполнить вертикальный поиск (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр. Более подробно Вы можете узнать из урока — 4 способа сделать ВПР с учетом регистра в Excel.
2. ВПР возвращает первое найденное значение
Как Вы уже знаете, ВПР возвращает из заданного столбца значение, соответствующее первому найденному совпадению с искомым. Однако, Вы можете заставить ее извлечь 2-е, 3-е, 4-е или любое другое повторение значения, которое Вам нужно. Если нужно извлечь все повторяющиеся значения, Вам потребуется комбинация из функций ИНДЕКС (INDEX), НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW).
3. В таблицу был добавлен или удалён столбец
К сожалению, формулы с ВПР перестают работать каждый раз, когда в таблицу поиска добавляется или удаляется новый столбец. Это происходит, потому что синтаксис ВПР требует указывать полностью весь диапазон поиска и конкретный номер столбца для извлечения данных. Естественно, и заданный диапазон, и номер столбца меняются, когда Вы удаляете столбец или вставляете новый.
Решение: И снова на помощь спешат функции ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH). В формуле ИНДЕКС+ПОИСКПОЗ Вы раздельно задаёте столбцы для поиска и для извлечения данных, и в результате можете удалять или вставлять сколько угодно столбцов, не беспокоясь о том, что придётся обновлять все связанные формулы поиска.
4. Ссылки на ячейки исказились при копировании формулы
Этот заголовок исчерпывающе объясняет суть проблемы, правда?
Решение: Всегда используйте абсолютные ссылки на ячейки (с символом $) при записи диапазона, например $A$2:$C$100 или $A:$C. В строке формул Вы можете быстро переключать тип ссылки, нажимая F4.
ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
ВПР: работа с функцией ЕСЛИОШИБКА
Синтаксис функции ЕСЛИОШИБКА (IFERROR) прост и говорит сам за себя:
То есть, для первого аргумента Вы вставляете значение, которое нужно проверить на предмет ошибки, а для второго аргумента указываете, что нужно возвратить, если ошибка найдётся.
Например, вот такая формула возвращает пустую ячейку, если искомое значение не найдено:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
ВПР: работа с функцией ЕОШИБКА
Так как функция ЕСЛИОШИБКА появилась в Excel 2007, при работе в более ранних версиях Вам придётся использовать комбинацию ЕСЛИ (IF) и ЕОШИБКА (ISERROR) вот так:
Например, формула ЕСЛИ+ЕОШИБКА+ВПР, аналогична формуле ЕСЛИОШИБКА+ВПР, показанной выше:
Вам нужно точное совпадение
Последний аргумент функции ВПР, известный как range_lookup (интервальный_просмотр), спрашивает, какое совпадение Вы хотите получить – приблизительное или точное.
В большинстве случаев люди ищут конкретный продукт, заказ, сотрудника или клиента, и потому хотят точное совпадение. Если производится поиск уникального значения, то аргументом range_lookup (интервальный_просмотр) должно быть FALSE (ЛОЖЬ).
Этот аргумент не обязателен, но если его не указать, то будет использовано значение TRUE (ИСТИНА). В таком случае для правильной работы функции необходимо, чтобы данные были отсортированы в порядке возрастания.
На рисунке ниже показана функция ВПР с пропущенным аргументом range_lookup (интервальный_просмотр), которая возвращает ошибочный результат.
Решение
Если Вы ищите уникальное значение, задайте последний аргумент равным FALSE (ЛОЖЬ). Функция ВПР в примере выше должна выглядеть так:
Зафиксируйте ссылки на таблицу
Возможно, Вы захотите использовать сразу несколько функций ВПР, чтобы извлечь большее количество информации. Если Вы собираетесь скопировать функцию ВПР в несколько ячеек, то необходимо зафиксировать часть аргументов.
На рисунке ниже показан пример функции ВПР, введенной некорректно. Для аргументов lookup_value (искомое_значение) и table_array (таблица) введены неправильные диапазоны ячеек.
Решение
Аргумент table_array (таблица) – это таблица, которую ВПР использует для поиска и извлечения информации. Чтобы корректно скопировать функцию ВПР, в аргументе table_array (таблица) должна быть абсолютная ссылка на диапазон ячеек.
Кликните по адресу ссылки внутри формулы и нажмите F4 на клавиатуре, чтобы превратить относительную ссылку в абсолютную. Формула должна быть записана так:
В этом примере ссылки в аргументах lookup_value (искомое_значение) и table_array (таблица) сделаны абсолютными. Иногда достаточно зафиксировать только аргумент table_array (таблица).
Вставлен столбец
Аргумент col_index_num (номер_столбца) используется функцией ВПР, чтобы указать, какую информацию необходимо извлечь из записи.
В связи с тем, что аргумент введен как числовой индекс, он не очень надёжен. Если в таблицу вставить новый столбец, функция ВПР может перестать работать. Рисунок ниже показывает именно такой сценарий.
Столбец Quantity (Количество) был 3-м по счету, но после добавления нового столбца он стал 4-м. Однако функция ВПР автоматически не обновилась.
Решение 1
Одним из решений будет защитить таблицу, чтобы пользователи не могли вставлять новые столбцы. В случае, когда пользователям потребуется такая возможность, решение станет не жизнеспособным.
Решение 2
Другой вариант – вставить функцию MATCH (ПОИСКПОЗ) в аргумент col_index_num (номер_столбца) функции ВПР.
Функция ПОИСКПОЗ может быть использована для того, чтобы найти и возвратить номер требуемого столбца. Это сделает аргумент col_index_num (номер_столбца) динамичным, т.е. можно будет вставлять новые столбцы в таблицу, не влияя на работу функции ВПР.
Формула, показанная ниже, может быть использована в этом примере, чтобы решить проблему, описанную выше.
Таблица стала больше
По мере добавления новых строк в таблицу, функции ВПР может потребоваться корректировка для гарантии, что новые строки таблицы охвачены формулой. На рисунке ниже показана ситуация, когда функция ВПР просматривает не всю таблицу в поисках нужного фрукта.
Решение
Форматируйте диапазон ячеек как таблицу (Excel 2007+) или как именованный диапазон. Такие приёмы дадут гарантию, что ВПР всегда будет обрабатывать всю таблицу.
Чтобы форматировать диапазон как таблицу, выделите диапазон ячеек, который собираетесь использовать для аргумента table_array (таблица). На Ленте меню нажмите Home > Format as Table (Главная > Форматировать как таблицу) и выберите стиль из галереи. Откройте вкладку Table Tools > Design (Работа с таблицами > Конструктор) и в соответствующем поле измените имя таблицы.
В формуле на рисунке ниже использовано имя таблицы FruitList.
ВПР не может смотреть влево
Функция ВПР имеет такое ограничение: она не может извлекать информацию из колонок, находящихся слева от первой. Она ищет искомое значение в крайней левой колонке заданной таблицы и возвращает информацию, находящуюся правее.
Решение
Решение этой проблемы – не использовать ВПР вовсе. Используйте комбинацию функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ), которая стала привычной альтернативой для ВПР. Это намного более гибкое решение
Пример, приведённый ниже, был использован для извлечения информации из колонки слева от той, по которой производится поиск:
Данные в таблице дублируются
Функция ВПР может извлечь только одну запись. Она возвратит первую найденную запись, соответствующую введённому Вами условию поиска.
Если таблица содержит повторяющиеся значения, функция ВПР не справится с такой задачей правильно.
Решение 1
Нужны ли Вам повторяющиеся данные в списке? Если нет – удалите их. Это можно сделать быстро при помощи кнопки Removes Duplicates (Удалить дубликаты) на вкладке Data (Данные).
Решение 2
Решили оставить дубликаты? Хорошо! В таком случае, Вам нужна не функция ВПР. Для таких случаев отлично подойдёт сводная таблица, позволяющая выбрать значение и посмотреть результаты.
Таблица ниже – это список заказов. Допустим, Вы хотите найти все заказы определённого фрукта.
Сводная таблица позволяет выбрать значение из столбца ID в фильтре, которое соответствует определенному фрукту, и получить список всех связанных заказов. В нашем примере выбрано значение ID равное 23 (Бананы).
ВПР без забот
Эта статья показывает решения 6 наиболее распространённых причин сбоя в работе функции ВПР. Вооружившись этой информацией, Вы сможете насладиться более беззаботным будущим в компании замечательных функций Excel.
В этом руководстве описывается, как можно быстро решить проблему, при которой функция Excel ВПР() показывает пользователю ошибку в разных версиях программы электронных таблиц. Также будут описаны ошибки, получившие наибольшее распространение, а также будут приведены рекомендации, как можно обойти ограничения этой функции.
Раньше вы могли уже читать о том, для чего предназначена функция ВПР, и где ее можно применять. Особенно, если вы до этого активно интересовались функциями программы электронных таблиц Excel. В таком случае вы уже должны стать экспертом в этой области.
Но даже если вы слышали об этой функции, вы все равно можете столкнуться со сложностями. По разным причинам эта функция считается самой сложной в программе электронных таблиц. Для ее использования есть множество ограничений, а также незаметных с первого взгляда нюансов, которые являются источником большого количества проблем и ошибок.
В формуле = ВПР эта ошибка расшифровывается, как «Нет данных». Простыми словами, у электронной таблицы не получается отыскать значение, которое необходимо пользователю. Есть множество причин, по которым эта проблема может появляться.
Неправильный ввод искомого значения
Наиболее частая причина возникновения этой ошибки – ввод значения с опечаткой. Например, случайно была написана буква вместо цифры. Особенно часто эта ошибка появляется, если обрабатываются огромные массивы данных.
Если ищется приближенное соответствие
- Если значение, которое необходимо найти в конкретном диапазоне, меньше, чем самое крошечное в анализируемой совокупности данных.
- Если перед введением функции пользователь не упорядочил по возрастанию связанную с ней колонку.
При поиске точного соответствия введенному запросу
Если значение, которое пользователь пытается найти, ищется с помощью формулы, и найти его не удалось, это тоже может быть причиной возникновения этой ошибки.
На данный момент столбец не является крайним левым
Наиболее существенным ограничением при использовании формулы ВПР – невозможность применить ее к столбцу, который не является крайним левым. Обычно пользователь забывает об этом, и в результате формула выдает описанную выше ошибку.
Преодоление этой трудности заключается в следующем: если по какой-то причине не получается переместить столбец влево, необходимо использовать сразу две функции Excel: ИНДЕКС(), ПОИСКПОЗ().
Числовые значения отформатированы, как текстовые
Это также часто встречаемый источник проблем с формулой ВПР() . Нередко пользователь может не замечать, что числовые значения отформатированы как текстовые. Часто такая проблема может встретиться, если информация копируется из других источников.
Еще одна причина подобной ошибки – пользователь забыл о том, что он поставил апостроф перед числом, чтобы сохранить ноль, который находится перед значением. Например, может быть так, как показано на следующей картинке.
Еще одна причина ошибки – формат может быть обозначен, как «Общий». Определить это можно по расположению значения внутри ячейки. Если они выравниваются по левому краю, это означает, что формат выбран как «Общий».
Если эта ошибка вызвана несколькими ячейками с числовыми значениями, их необходимо выделить, после чего нужно кликнуть по соответствующей области правой кнопкой мыши. В ответ на это действие появится контекстное меню, в котором нужно выбрать опцию «Формат ячеек», далее нужно будет нажать на «Число» и выбрать числовой формат. Последнее действие – нажатие кнопки «ОК».
Если стоит пробел в начале или в конце содержимого
Причина возникновения этой ошибки наименее заметна. Если таблица существенно большая, тяжело увидеть, в каких клетках располагаются пробелы. Особенно если часть ячеек оказывается за пределами экрана.
Решение номер 1: Если пробелы находятся в таблице, к которой применяется функция ВПР
Если в основной таблице обнаружены пробелы, можно применить функцию СЖПРОБЕЛЫ в аргументе «Искомое значение». Значительно проще это продемонстрировать на примере.
=ВПР(СЖПРОБЕЛЫ($F2),$A$2:$C$10,3,FALSE)
Решение номер 2: Если лишние пробелы находятся в столбце или таблице поиска
В таком случае не получится легко предотвратить ошибку. Здесь необходимо использовать несколько функций: ИНДЕКС(), ПОИСКПОЗ(), СЖПРОБЕЛЫ().
В результате получится формула массива, для правильного ввода которой нужно нажать на комбинацию клавиш Ctrl+Shift+Enter.
В качестве альтернативного способа решения этой проблемы можно воспользоваться дополнением «Trim Spaces for Excel», которое позволяет убрать ненужные пробелы в формулах как в главной таблице, так и в таблице поиска. Это бесплатный инструмент, который можно скачать по .
В целом, Excel отображает эту ошибку, если в формуле используется неправильный тип данных. Выделяется несколько причин этой ошибки.
Причина 1: значение, которое ищется, содержит больше 255 знаков
Чтобы решить эту проблему, необходимо использовать связку формул ИНДЕКС()+ПОИСКПОЗ() . Вот пример формулы, демонстрирующий на практике реализацию этой задачи:
Причина 2: не прописан полный путь к рабочей книге, используемой для поиска
При указании данных из другого файла, необходимо ввести полный путь к нему. Проще говоря, необходимо указать как его имя, так и расширение, после чего ввести имя листа и не забыть про восклицательный знак. Лучше всего это все заключить в апострофы для предотвращения возникновения ошибок из-за наличия пробелов в папке к файлу или в самой книге Excel.
Вот как эта формула выглядит:
=ВПР(искомое_значение;'[имя_книги]имя_листа’!таблица;номер_столбца;ЛОЖЬ)
А вот пример использования этой формулы на практике:
=ВПР($A$3;'[New Price.xls]Sheet2′!$B:$C;3;ЛОЖЬ)
Здесь программа будет искать содержимое ячейки A3 столбца c листа Sheet2 файла «NewPrice». Данные формула будет извлекать из столбца C.
Причина 3: пользователь ввел значение меньше 1 в аргумент «Номер столбца»
Тяжело вообразить, что кто-то может вводить для обозначения столбца значение меньше единицы. Но такая ситуация бывает, если в аргументе прописана переменная, значение которой вычисляется другой функцией Excel.
Почему еще может не работать функция ВПР?
Функция ВПР() имеет довольно сложный синтаксис. Но не только он является причиной сложностей в работе с этой формулой. В ходе пользования программой может появиться множество неприятностей даже в, казалось бы, простых случаях. Далее приводятся самые распространенные ситуации, когда ВПР() может выдавать ошибку. Также описываются ограничения, которые нужно учитывать.
Нечувствительность к регистру
Эта функция не может понять, где большая буква, а где – маленькая. Поэтому в наборе данных, где есть аналогичные за исключением регистра символов элементы, она вернет первый попавшийся, без учета регистра.
Чтобы решить эту проблему, можно использовать другую функцию Excel, которая может искать нужное значение в столбце (ИНДЕКС(), ПОИСКПОЗ(), ПРОСМОТР() и другие) вместе с СОВПАД(). Последняя функция может различать регистр.
Возвращение первого найденного значения
Кроме использования другой функции для поиска значения с учетом регистра, можно использовать другую формулу, если точно известно, какое по очередности значение нужно найти. Для этого необходимо использовать функции ИНДЕКС(), НАИМЕНЬШИЙ() и СТРОКА(). Так можно будет выбирать 2, 3, 4 или любое другое требуемое значение.
Новая колонка была вставлена в таблицу или убрана из таблицы
К сожалению, формула ВПР() перестает работать при каждом добавлении в таблицу столбца. Все потому, что ее синтаксис требует введения всего массива ячеек, в которых осуществляется поиск информации. Конечно, ситуация меняется, если туда вставляется столбец.
Здесь также нужно использовать функции ИНДЕКС() и ПОИСКПОЗ(). Они позволяют указать не массив всего диапазона, а лишь требуемые столбцы, и поэтому можно редактировать все остальные без необходимости обновлять связанные с ними формулы.
Искажение ссылок на ячейки при копировании функции
Вам сразу стало понятно, в чем проблема, верно? Решить ее просто: используя абсолютные ссылки на те ячейки, которые нужно оставить стабильными при копировании функции. Для этого необходимо перед названием столбца или строки поставить значок доллара ($). Например, прописать диапазон таким образом: $A$2:$C$100. Более простой вариант: $A:$C. С помощью клавиши F4 можно оперативно изменять тип адреса ячейки.
Обработка ошибок при использовании функции ВПР
Использование функции ЕСЛИОШИБКА
Для этой формулы характерен простой синтаксис:
ЕСЛИОШИБКА(значение; значение_если_ошибка)
Таким образом, на месте первого аргумента вставляется значение, которое тестируется на наличие ошибки, а на месте второго необходимо прописать текст или число, выдаваемые при возникновении ошибки.
Вот пример использования функции на практике:
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);»»)
В этом случае будет возвращаться пустая ячейка, если обнаруживается ошибка.
Можно в кавычки написать любую другую фразу. Например, ту, которая показана на скриншоте.
Использование функции ЕОШИБКА
Описанная выше функция впервые появилась в Excel 2007 версии, поэтому, если используются более старые версии программы (например, если компьютер имеет небольшую производительность, но на нем нужно обрабатывать большие объемы данных), нужно использовать такую формулу:
На практике формула будет выглядеть следующим образом:
Также мы постараемся показать несколько несложных приёмов, которые позволят подстраховать себя от случайной ошибки с функцией ВПР.
Почему не работает? Разбираем наиболее типичные ошибки при использовании функции ВПР в Google таблицах.
Итак, разберём наиболее распространённые ошибки.
Ошибка при вводе данных
Проверьте, правильно ли введены данные.
Ошибка при вводе функции.
Проверьте синтаксис написания функции, и всё бедет в порядке.
Неверные ссылки в функции ВПР
Проверьте, правильно ли вы указали диапазон поиска а также ячейку со значением, которое мы будем искать.
К примеру, мы ищем цену бананов.
Но в функции вы указали обычные (относительные) ссылки, забыв заменить их на абсолютные ($), и затем произвели какие-то действия с таблицей. К примеру, добавили столбец. В результате ваш диапазон поиска автоматически изменился и стал шире на 1 столбец.
Ваши ссылки в формуле теперь будут ссылаться на неверные ячейки. Цена у вас находится теперь в 3-м столбце, а вы берёте данные из второго. В результате ВПР не работает.
Неверно указан параметр “сортировка” (is_sorted).
Включен поиск до первого приблизительного совпадения в отсортированном диапазоне (параметр is_sorted = TRUE), но на самом деле данные не отсортированы.
Об этой ошибке мы подробно говорили, когда рассматривали правила использования функции ВПР в Google таблицах.
Столбец поиска не является первым слева столбцом диапазона поиска.
Часто забывают о том, что функция ВПР (VLOOKUP) ищет совпадающие значения только в первом слева столбце диапазона, который вы указали.
Это важное ограничение функции ВПР нельзя забывать.
Несовпадение форматов данных.
Формат ячейки, откуда берется искомое значение наименования (например D3 в нашем случае), и формат ячеек первого столбца (A3:A21) из диапазона поиска отличаются (например, числовой и текстовый). Этот случай особенно часто встречается при использовании вместо текстовых наименований числовых кодов (номера счетов, артикулы, идентификаторы, даты и т.п.)
Итак, здесь мы пытаемся найти текстовое значение среди чисел. Закономерно функция ВПР возвращает нам ошибку.
Как видите, с виду записи одинаковы, однако в ячейке D6 значение сохранено как число, а в ячейке A10 – как текст. Текст не может быть равен числу, поэтому функция возвращает ошибку.
Как решить эту проблему со случайной ошибкой в формате записи? Можно использовать функцию ТЕКСТ, которая преобразует любые значения в текстовый вид.
Теперь функция ВПР нашей Google таблицы выглядит так:
Обычную ссылку на ячейку D6 мы заменяем функцией
Первый аргумент – это сама ячейка, а второй аргумент означает формат, в который мы будем преобразовывать значение этой ячейки. В данном случае – в текстовый.
Как видите, использование вложенной функции помогло решить проблему с несовпадением форматов данных.
Как видите, ошибка исчезла, поскольку теперь текстовое значение сравнивается с таким же текстовым значением.
Лишние пробелы и непечатаемые знаки.
Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.).
В нашем примере функция ВПР возвратила ошибку, так как в ячейке D4 при вводе данных случайно были добавлены два пробела после значения. И, поскольку сравниваются символьные значения, поиск, естественно, завершился неудачно.
Такая ошибка может встеречаться довольно часто, и обнаружить её визуально практически невозможно.
К примеру, если наименование состоит из 2 слов, то вполне возможно случайное появление лишнего пробела между этими словами.
Могут быть также случайно добавлены символы, невидимые на экране и на печати (табуляция, перевод строки и т.д.). Визуально определить такие ошибки практически невозможно.
В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для удаления лишних пробелов либо других невидимых символов. Вместо
Функция СЖПРОБЕЛЫ (TRIM) удаляет пробелы, а функция ПЕЧСИМВ (CLEAN) удаляет все непечатаемые и невидимые символы из текстового значения.
Эту комбинацию функций рекомендуем применять всегда «на всякий случай», чтобы подстраховать себя от случайных ошибок.
Неправильно указан номер столбца.
Иногда неправильно указывают номер столбца, значение из которого вы хотите получить (третий аргумент функции ВПР).
Когда вы видите такую ошибку, пересчитайте количество столбцов в диапазоне, который вы указали в функции, и убедитесь, что это значение не меньше, чем номер столбца, указанный третьим аргументом функции ВПР.
И в любом случае – номер столбца должен быть больше 1. Вряд ли кто-то из вас будет руками вводить значение 0 или -1, но если вы получаете номер столбца при помощи каких-то функций, вложенных в ВПР, то здесь-то и может быть ошибка.
Ошибка в ссылке на данные из другой таблицы
Мы с вами говорили о том, что диапазон поиска может быть расположен в другой таблице или даже в другом файле Google таблиц.
Это будет означать, что вы ссылаетесь на неверный диапазон, который программа найти не может.
После изменения таблицы функция перестала работать
Вы сделали все правильно и до тех пор, пока вы не вставили несколько строк или стрлбцов в вашу таблицу, функция работала верно.
А дело всё в том, что вы не использовали в функции ВПР абсолютные ссылки, не добавляли к адресам ячеек, строк и столбцов знак $. В результате после добавления (или удаления) строк или столбцов ваши ссылки изменились и формула перестала работать.
Постарайтесь отменить слеланные изменения, благо Google таблицы хранят всю историю изменений вашей таблицы.
Важно! Всегда используйте абсолютные ссылки в формуле, где это необходимо, чтобы застраховать себя от подобных неожиданных изменений.
Но прежде чем приступать к таким радикальным мерам, всё же проверьте – а всё ли вы сделали правильно?
Во-первых, проверьте адрес диапазона поиска. Действительно ли то значение, которое вы ищете, может находиться в первом столбце диапазона.
Во-вторых, проверьте правильно ли вы указали тип параметра “сортировка” (is_sorted): ИСТИНА или ЛОЖЬ.
В-третьих, проверьте возможные несовпадения форматов, наличие лишних пробелов в условии поиска. Используйте рекомендации, которые мы давали выше.
Если вы все сделали правильно, но диапазоне поиска всё же нет нужного значения, дополните формулу функцией ЕСЛИОШИБКА (IFERROR). Например, так:
Синтаксис функции ЕСЛИОШИБКА очень простой. Первый аргумент – это выражение, значение которого мы проверяем на возниконвение ошибки.
Если ошибки нет, товозвращается значение этого выражения. Если же возникает ошибка, то функция возвращает значение второго аргумента. В данном случае в ячейке F9 будет указано “Не найдено”.
Читайте также: