В этой статье описаны наиболее распространенные причины неправильного результата функции В ФУНКЦИИ ВЛП, а также рекомендации по использованию функций ИНДЕКС иПОЗ.
Совет: Кроме того, обратитесь к кратким справочнику: советы по устранению неполадок с #NA в удобном PDF-файле. Вы можете поделиться PDF-файлом с другими людьми или распечатать его для справки.
Проблема: искомое значение не находится в первом столбце аргумента таблица
Одним из ограничений функции ВЛП является то, что она может искать только значения в левом большинстве столбцов в таблице. Если искомого значения нет в первом столбце массива, вы увидите #N/A.
В следующей таблице нам нужно узнать количество проданной капусты.
Ошибка #N/A, так как искомый аргумент «Ели» отображается во втором столбце (Продукты) аргумента table_array A2:C10. В этом случае Excel искать его в столбце A, а не в столбце B.
Решение:вы можете попытаться устранить эту проблему, настроив в этой области ссылку на правильный столбец. Если это невозможно, попробуйте передвигать столбцы. Это также может быть крайне невероятным, если у вас большие или сложные электронные таблицы, в которых значения ячеей являются результатом других вычислений, или, возможно, есть и другие логические причины, по которым просто невозможно перемещать столбцы. Решением является использование сочетания функций ИНДЕКС и ПОИСКПОЧ, которые могут искать значения в столбце независимо от их положения в таблице подытого. См. следующий раздел.
Вместо нее можно использовать индекс или СОВПАДЕНИЕ
Функции
ИНДЕКС и ВЫБОРПОЗ являются хорошими вариантами для многих случаев, когда функции ВЛИО8 не отвечают вашим требованиям. Ключевое преимущество index/MATCH заключается в том, что вы можете искать значения в столбце в любом месте таблицы подытогов. Индекс возвращает значение из указанной таблицы или диапазона в соответствии с его положением. Возвращает относительную позицию значения в таблице или диапазоне. Чтобы найти значение в таблице или массиве, укажите относительное положение значения в таблице или массиве, используйте функции ИНДЕКС и НАЙТИ В ФОРМУЛЕ.
Существует несколько преимуществ использования индекса и СОВПАДЕНИЯ вместо ВЛ ПРОСМОТР:
-
При этом возвращаемая величина не должна быть в том же столбце, что и в столбце подытовки. Это отличается от ВЛП, в котором возвращаемая величина должна быть в указанном диапазоне. Почему это важно? При использовании функции ВПР вам нужно знать номер столбца, содержащего значение. Хотя это может показаться не сложной задачей, это может оказаться утомительным, если у вас большая таблица и вам нужно подсчитать количество столбцов. Кроме того, если вы добавите или удалите столбец в таблице, вам придется пересчитать и обновить col_index_num столбца. При использовании функций ИНДЕКС и ПОИСКПОЗ не нужно подсчитывать столбцы.
-
С помощью функций ИНДЕКС и MATCH можно указать либо строку, либо столбец в массиве, либо указать и то, и другое. Это означает, что значения можно искать по вертикали и по горизонтали.
-
С помощью функций ИНДЕКС и ПОИСКПОЗ можно находить значения в любом столбце. В отличие от ВРОТ.В.В., в которой можно искать только значение в первом столбце таблицы, индекс и ВЫБОРПОЗ будут работать, если искомые значения есть в первом столбце, последнем или в любом другом месте между ними.
-
Индекс и MATCH обеспечивают гибкость динамической ссылки на столбец, содержащий возвращаемое значение. Это означает, что вы можете добавлять столбцы в таблицу, не нарушая индекс и MATCH. С другой стороны, при добавлении столбца в таблицу ВЛП разрывается, поскольку она создает статическую ссылку на таблицу.
-
Индекс и MATCH обеспечивает большую гибкость при совпадениях. Они могут найти точное совпадение или значение, которое больше или меньше искомой величины. ВПР ищет только наиболее близкое (по умолчанию) или точное значение. Кроме того, функция ВПР предполагает, что первый столбец в таблице отсортирован в алфавитном порядке, и возвращает первое наиболее близкое совпадение, поэтому вы можете получить не те данные, которые ожидали.
Синтаксис
Чтобы создать синтаксис функции ИНДЕКС или НАЙТИВКА, необходимо использовать аргумент массива или ссылки функции ИНДЕКС и вложенный в нее синтаксис ФУНКЦИИ НАЙТИВ. Это форма:
=ИНДЕКС(массив или ссылка; ПОИСКПОЗ(искомое_значение;массив;[тип_совпадения])
Заменим В ПРОСМОТР в примере выше с помощью индекса или MATCH. Синтаксис будет выглядеть следующим образом:
=ИНДЕКС(C2:C10;ПОИСКПОЗ(B13;B2:B10;0))
Что означает:
=ИНДЕКС(возвращает значение из C2:C10, которое будет ФУНКЦИЕЙ НАЙТИВ(Ольга, которая находится в массиве B2:B10, где возвращаемая величина является первым значением, соответствующим Значению Ольга))
Формула ищет в C2:C10 первое значение, соответствующее значению Капуста (B7), и возвращает значение в ячейке C7 (100).
Проблема: не найдено точное совпадение
Если range_lookup ложь и не удается найти точное совпадение в данных, возвращается #N/A.
Решение.Если вы уверены в том, что в вашей книге есть соответствующие данные, но не можете найти их, убедитесь, что в ячейках, на которые ссылается ссылка, нет скрытых пробелов или непечатаемых символов. Кроме того, убедитесь, что ячейки следуют за правильным типом данных. Например, ячейки с числами должны иметь формат «Число»,а не «Текст».
Кроме того, для очистки данных в ячейках можно использовать функцию CLEAN или TRIM.
Проблема: искомое значение меньше, чем наименьшее значение в массиве
Если range_lookup имеет значение ИСТИНА и искомого значения меньше наименьшего значения в массиве, вы увидите #N/A. Функция ищет приблизительное совпадение в массиве и возвращает ближайшее значение, которое меньше искомого.
В приведенном ниже примере искомое значение равно 100, но в диапазоне B2:C10 нет значений меньше 100, поэтому возникает ошибка.
Решение.
-
Исправьте искомое значение.
-
Если вы не можете изменить искомые значения и вам нужна большая гибкость, думайте об использовании индексов иПОЗ вместо ВЛИО (см. раздел выше в этой статье). Они позволяют находить значения больше или меньше искомого, а также равные ему. Дополнительные сведения см. в предыдущем разделе этой статьи.
Проблема: столбец подстановки не отсортирован в порядке возрастания
Если range_lookup имеет вид ИСТИНА и один из столбцов подытог не отсортировали в порядке возрастания (A–Z), вы увидите ошибку #N/A.
Решение.
-
Измените функцию ВПР так, чтобы искать точное совпадение. Для этого укажите для аргумента диапазон_поиска значение ЛОЖЬ. Для этого не требуется сортировка.
-
Для поиска значения в несортированной таблице можно также использовать функции ИНДЕКС и ПОИСКПОЗ.
Проблема: значение является большим числом с плавающей запятой
При наличии в ячейках значений времени или больших десятичных чисел Excel возвращает ошибку «#Н/Д» из-за точности чисел с плавающей запятой. Числа с плавающей запятой включают цифры после десятичной запятой. (Excel значения времени в качестве чисел с плавающей за точкой.) Excel не удается сохранить числа с очень большими плавающей за точкой, поэтому для правильной работы функции числа с плавающей за дробной частью необходимо округлять до 5 десятичных дробных мест.
Решение. Округлите числа до 5 десятичных разрядов с помощью функции ОКРУГЛ.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
-
Исправление ошибки #Н/Д
-
ВLOOKUP: больше нет #NA
-
Арифметические данные с плавающей за точкой могут привести к неверным результатам в Excel
-
Краткий справочник: функция ВПР
-
Функция ВПР
-
Полные сведения о формулах в Excel
-
Рекомендации, позволяющие избежать появления неработающих формул
-
Обнаружение ошибок в формулах
-
Все функции Excel (по алфавиту)
-
Функции Excel (по категориям)
Этот урок объясняет, как быстро справиться с ситуацией, когда функция ВПР (VLOOKUP) не хочет работать в Excel 2013, 2010, 2007 и 2003, а также, как выявить и исправить распространённые ошибки и преодолеть ограничения ВПР.
В нескольких предыдущих статьях мы изучили различные грани функции ВПР в Excel. Если Вы читали их внимательно, то сейчас должны быть экспертом в этой области. Однако не без причины многие специалисты по Excel считают ВПР одной из наиболее сложных функций. Она имеет кучу ограничений и особенностей, которые становятся источником многих проблем и ошибок.
В этой статье Вы найдёте простые объяснения ошибок #N/A (#Н/Д), #NAME? (#ИМЯ?) и #VALUE! (#ЗНАЧ!), появляющихся при работе с функцией ВПР, а также приёмы и способы борьбы с ними. Мы начнём с наиболее частых случаев и наиболее очевидных причин, почему ВПР не работает, поэтому лучше изучать примеры в том порядке, в каком они приведены в статье.
- Исправляем ошибку #Н/Д
- Исправляем ошибку #ЗНАЧ! в формулах с ВПР
- Ошибка #ИМЯ? в ВПР
- ВПР не работает (проблемы, ограничения и решения)
- ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
Содержание
- Исправляем ошибку #Н/Д функции ВПР в Excel
- 1. Искомое значение написано с опечаткой
- 2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР
- 3. Ошибка #Н/Д при поиске точного совпадения с ВПР
- 4. Столбец поиска не является крайним левым
- 5. Числа форматированы как текст
- 6. В начале или в конце стоит пробел
- Ошибка #ЗНАЧ! в формулах с ВПР
- 1. Искомое значение длиннее 255 символов
- 2. Не указан полный путь к рабочей книге для поиска
- 3. Аргумент Номер_столбца меньше 1
- Ошибка #ИМЯ? в ВПР
- ВПР не работает (ограничения, оговорки и решения)
- 1. ВПР не чувствительна к регистру
- 2. ВПР возвращает первое найденное значение
- 3. В таблицу был добавлен или удалён столбец
- 4. Ссылки на ячейки исказились при копировании формулы
- ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
- ВПР: работа с функцией ЕСЛИОШИБКА
- ВПР: работа с функцией ЕОШИБКА
Исправляем ошибку #Н/Д функции ВПР в Excel
В формулах с ВПР сообщение об ошибке #N/A (#Н/Д) – означает not available (нет данных) – появляется, когда Excel не может найти искомое значение. Это может произойти по нескольким причинам.
1. Искомое значение написано с опечаткой
Хорошая мысль проверить этот пункт в первую очередь! Опечатки часто возникают, когда Вы работаете с очень большими объёмами данных, состоящих из тысяч строк, или когда искомое значение вписано в формулу.
2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР
Если Вы используете формулу с условием поиска приближённого совпадения, т.е. аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, Ваша формула может сообщить об ошибке #Н/Д в двух случаях:
- Искомое значение меньше наименьшего значения в просматриваемом массиве.
- Столбец поиска не упорядочен по возрастанию.
3. Ошибка #Н/Д при поиске точного совпадения с ВПР
Если Вы ищете точное совпадение, т.е. аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ) и точное значение не найдено, формула также сообщит об ошибке #Н/Д. Более подробно о том, как искать точное и приближенное совпадение с функцией ВПР.
4. Столбец поиска не является крайним левым
Как Вы, вероятно, знаете, одно из самых значительных ограничений ВПР это то, что она не может смотреть влево, следовательно, столбец поиска в Вашей таблице должен быть крайним левым. На практике мы часто забываем об этом, что приводит к не работающей формуле и появлению ошибки #Н/Д.
Решение: Если нет возможности изменить структуру данных так, чтобы столбец поиска был крайним левым, Вы можете использовать комбинацию функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), как более гибкую альтернативу для ВПР.
5. Числа форматированы как текст
Другой источник ошибки #Н/Д в формулах с ВПР – это числа в текстовом формате в основной таблице или в таблице поиска.
Это обычно случается, когда Вы импортируете информацию из внешних баз данных или когда ввели апостроф перед числом, чтобы сохранить стоящий в начале ноль.
Наиболее очевидные признаки числа в текстовом формате показаны на рисунке ниже:
Кроме этого, числа могут быть сохранены в формате General (Общий). В таком случае есть только один заметный признак – числа выровнены по левому краю ячейки, в то время как стандартно они выравниваются по правому краю.
Решение: Если это одиночное значение, просто кликните по иконке ошибки и выберите Convert to Number (Конвертировать в число) из контекстного меню.
Если такая ситуация со многими числами, выделите их и щелкните по выделенной области правой кнопкой мыши. В появившемся контекстном меню выберите Format Cells (Формат ячеек) > вкладка Number (Число) > формат Number (Числовой) и нажмите ОК.
6. В начале или в конце стоит пробел
Это наименее очевидная причина ошибки #Н/Д в работе функции ВПР, поскольку зрительно трудно увидеть эти лишние пробелы, особенно при работе с большими таблицами, когда большая часть данных находится за пределами экрана.
Решение 1: Лишние пробелы в основной таблице (там, где функция ВПР)
Если лишние пробелы оказались в основной таблице, Вы можете обеспечить правильную работу формул, заключив аргумент lookup_value (искомое_значение) в функцию TRIM (СЖПРОБЕЛЫ):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Решение 2: Лишние пробелы в таблице поиска (в столбце поиска)
Если лишние пробелы оказались в столбце поиска – простыми путями ошибку #Н/Д в формуле с ВПР не избежать. Вместо ВПР Вы можете использовать формулу массива с комбинацией функций ИНДЕКС (INDEX), ПОИСКПОЗ (MATCH) и СЖПРОБЕЛЫ (TRIM):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Так как это формула массива, не забудьте нажать Ctrl+Shift+Enter вместо привычного Enter, чтобы правильно ввести формулу.
Ошибка #ЗНАЧ! в формулах с ВПР
В большинстве случаев, Microsoft Excel сообщает об ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подходит по типу данных. Что касается ВПР, то обычно выделяют две причины ошибки #ЗНАЧ!.
1. Искомое значение длиннее 255 символов
Будьте внимательны: функция ВПР не может искать значения, содержащие более 255 символов. Если искомое значение превышает этот предел, то Вы получите сообщение об ошибке #ЗНАЧ!.
Решение: Используйте связку функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH). Ниже представлена формула, которая отлично справится с этой задачей:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
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, вложенной в ВПР.
Итак, если случилось, что аргумент col_index_num (номер_столбца) меньше 1, функция ВПР также сообщит об ошибке #ЗНАЧ!.
Если же аргумент col_index_num (номер_столбца) больше количества столбцов в заданном массиве, ВПР сообщит об ошибке #REF! (#ССЫЛ!).
Ошибка #ИМЯ? в ВПР
Простейший случай – ошибка #NAME? (#ИМЯ?) – появится, если Вы случайно напишите с ошибкой имя функции.
Решение очевидно – проверьте правописание!
ВПР не работает (ограничения, оговорки и решения)
Помимо достаточно сложного синтаксиса, ВПР имеет больше ограничений, чем любая другая функция Excel. Из-за этих ограничений, простые на первый взгляд формулы с ВПР часто приводят к неожиданным результатам. Ниже Вы найдёте решения для нескольких распространённых сценариев, когда ВПР ошибается.
1. ВПР не чувствительна к регистру
Функция ВПР не различает регистр и принимает символы нижнего и ВЕРХНЕГО регистра как одинаковые. Поэтому, если в таблице есть несколько элементов, которые различаются только регистром символов, функция ВПР возвратит первый попавшийся элемент, не взирая на регистр.
Решение: Используйте другую функцию Excel, которая может выполнить вертикальный поиск (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр. Более подробно Вы можете узнать из урока — 4 способа сделать ВПР с учетом регистра в Excel.
2. ВПР возвращает первое найденное значение
Как Вы уже знаете, ВПР возвращает из заданного столбца значение, соответствующее первому найденному совпадению с искомым. Однако, Вы можете заставить ее извлечь 2-е, 3-е, 4-е или любое другое повторение значения, которое Вам нужно. Если нужно извлечь все повторяющиеся значения, Вам потребуется комбинация из функций ИНДЕКС (INDEX), НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW).
3. В таблицу был добавлен или удалён столбец
К сожалению, формулы с ВПР перестают работать каждый раз, когда в таблицу поиска добавляется или удаляется новый столбец. Это происходит, потому что синтаксис ВПР требует указывать полностью весь диапазон поиска и конкретный номер столбца для извлечения данных. Естественно, и заданный диапазон, и номер столбца меняются, когда Вы удаляете столбец или вставляете новый.
Решение: И снова на помощь спешат функции ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH). В формуле ИНДЕКС+ПОИСКПОЗ Вы раздельно задаёте столбцы для поиска и для извлечения данных, и в результате можете удалять или вставлять сколько угодно столбцов, не беспокоясь о том, что придётся обновлять все связанные формулы поиска.
4. Ссылки на ячейки исказились при копировании формулы
Этот заголовок исчерпывающе объясняет суть проблемы, правда?
Решение: Всегда используйте абсолютные ссылки на ячейки (с символом $) при записи диапазона, например $A$2:$C$100 или $A:$C. В строке формул Вы можете быстро переключать тип ссылки, нажимая F4.
ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
Если Вы не хотите пугать пользователей сообщениями об ошибках #Н/Д, #ЗНАЧ! или #ИМЯ?, можете показывать пустую ячейку или собственное сообщение. Вы можете сделать это, поместив ВПР в функцию ЕСЛИОШИБКА (IFERROR) в Excel 2013, 2010 и 2007 или использовать связку функций ЕСЛИ+ЕОШИБКА (IF+ISERROR) в более ранних версиях.
ВПР: работа с функцией ЕСЛИОШИБКА
Синтаксис функции ЕСЛИОШИБКА (IFERROR) прост и говорит сам за себя:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
То есть, для первого аргумента Вы вставляете значение, которое нужно проверить на предмет ошибки, а для второго аргумента указываете, что нужно возвратить, если ошибка найдётся.
Например, вот такая формула возвращает пустую ячейку, если искомое значение не найдено:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Если Вы хотите показать собственное сообщение вместо стандартного сообщения об ошибке функции ВПР, впишите его в кавычках, например, так:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
ВПР: работа с функцией ЕОШИБКА
Так как функция ЕСЛИОШИБКА появилась в Excel 2007, при работе в более ранних версиях Вам придётся использовать комбинацию ЕСЛИ (IF) и ЕОШИБКА (ISERROR) вот так:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Например, формула ЕСЛИ+ЕОШИБКА+ВПР, аналогична формуле ЕСЛИОШИБКА+ВПР, показанной выше:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
На сегодня всё. Надеюсь, этот короткий учебник поможет Вам справиться со всеми возможными ошибками ВПР и заставит Ваши формулы работать правильно.
Оцените качество статьи. Нам важно ваше мнение:
Skip to content
Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте).
Давайте постараемся вместе ответим на вопрос: «Почему функция ВПР не работает?»
В первую очередь, сообщение об ошибке вы увидите, если данные, которое вы ищете, действительно отсутствуют в указанном диапазоне поиска. Здесь мы бессильны.
Все остальные случаи связаны с тем, что мы что-то не учли или сделали неверно.
Итак, почему ВПР не находит значение, хотя оно есть?
- Неточность при вводе данных.
- Опечатка при вводе функции.
- Неверные ссылки.
- Неверно указан параметр «интервальный просмотр».
- Столбец поиска не является первым слева столбцом диапазона поиска.
- Несовпадение форматов данных.
- Преобразуем число в текст.
- Преобразуем текст в число.
- Если числовой и текстовый форматы беспорядочно перемешаны?
- Лишние пробелы и непечатаемые знаки.
- Неправильно указан номер столбца.
- Неверная ссылка на данные из другой таблицы
- После изменения таблицы функция ВПР перестала работать
- Как убрать сообщение #Н/Д в ВПР?
Неточность при вводе данных.
В этом случае ничего найти не получится, и ВПР возвратит ошибку #Н/Д. Самое простое решение здесь очень часто оказывается самым верным. Быть может, вы просто ошиблись при вводе критерия поиска.
Это вполне может случиться, особенно если вы записываете его прямо в формулу.
Опечатка при вводе функции.
Если вы видите ошибку #ИМЯ?, то это означает, что при записи названия самой функции вы допустили неточность – перепутали или добавили лишнюю букву.
Проверьте синтаксис, и всё будет в порядке.
Неверные ссылки.
Проверьте, верно ли вы указали диапазон поиска, а также координаты значения, которое мы будем искать.
Случается, что в формуле вы указали обычные (относительные) ссылки, забыв заменить их на абсолютные ($), а затем произвели какие-то действия с таблицей. К примеру, добавили столбец. Ваши ссылки в формуле теперь будут указывать на неверные координаты.
Важно! При таком изменении ссылок вы можете и не знать, что найдено не то, что нужно. Ведь какое-то значение все же возвращено, хоть и неверное. Поэтому будьте внимательны в использовании относительных и абсолютных ссылок! Рекомендуем по этой причине стараться использовать «умные» таблицы или именованные диапазоны.
Неверно указан параметр «интервальный просмотр».
Включен поиск до первого приблизительного совпадения в отсортированном диапазоне (параметр = ИСТИНА или вовсе опущен), но на самом деле данные не отсортированы.
Об этой проблеме мы подробно говорили ранее.
Столбец поиска не является первым слева столбцом диапазона поиска.
Часто забывают о том, что ВПР ищет совпадающие данные только в первом слева столбце диапазона, который вы указали.
Если ваши просматриваемые данные находятся не в первом, а во втором или другом столбце, то она не сможет найти ни одного совпадения, и вы вновь увидите сообщение #Н/Д.
Это важное ограничение нельзя забывать.
Несовпадение форматов данных.
Формат ячейки, откуда берется искомое значение наименования (например D3 в нашем случае), и формат ячеек первого столбца (A3:A21) из диапазона поиска отличаются (например, числовой и текстовый). Этот случай особенно часто встречается при использовании вместо текстовых наименований числовых кодов (номера счетов, артикулы, идентификаторы, даты и т.п.). Ведь число, записанное в таблицу Excel, может быть в двух принципиально разных состояниях — как число и как текст. И визуально их отличить практически невозможно.
Преобразуем число в текст.
Как видите, с виду записи одинаковы, однако в D6 артикул сохранен как число, а в A10 – как текст (на таких ячейках обычно видна пометка — зелёный уголок). Текст не может быть равнозначен числу, поэтому получаем #Н/Д. ВПР не находит значение, хотя оно с виду есть.
Как решить эту проблему со случайным несоответствием в формате записи? Можно использовать функцию ТЕКСТ, которая преобразует любой тип данных в текстовый вид.
Используем формулу
=ВПР(ТЕКСТ(D6;»#»);$A$3:$B$21;2;ЛОЖЬ)
Как видно, обычную ссылку на D6 мы заменяем конструкцией
TEXT(D6,»#»)
Первый аргумент – это адрес, а второй аргумент означает формат, в который мы будем преобразовывать значение (в нашем случае – текстовый).
Можно поступить и проще — «приклеить» к числу пустую строку при помощи оператора склейки &.
=ВПР(D6&»»;$A$3:$B$21;2;ЛОЖЬ)
Запомните простое правило:
Если какую-либо текстовую операцию мы пытаемся провести над ячейкой, в которой записано число, то Excel обязательно попытается превратить это число в текст.
Если несовпадение форматов встречается редко, то можно просто исправить формат записи данных. Измените формат ячейки на текстовый и пересохраните ее содержание (F2 и затем ENTER). Содержимое будет преобразовано в текст.
Как видите, ошибка исчезла, поскольку теперь текстовое значение сравнивается с таким же текстом.
Преобразуем текст в число.
А если нужно сделать обратное преобразование? Преобразовать число как текст в обычное число, чтобы все данные в таблице были одного типа? Давайте рассмотрим на примере.
Добавьте перед проблемным значением двойной минус. Это означает — дважды умножить на минус 1. А еще со школы мы помним, что минус на минус дает плюс. В результате ничего не изменится. Но есть важный момент.
Если какую-либо математическую операцию мы пытаемся провести над ячейкой, в которой записан текст, то Excel обязательно попытается преобразовать его в число, чтобы выполнить вычисления.
Так же и в нашем случае. Текст преобразуется в число. Итак, наша формула выглядит следующим образом:
=ВПР(—A2;$D$2:$E$8;2;0)
Если числовой и текстовый форматы беспорядочно перемешаны?
А теперь — самый сложный случай. И здесь тоже есть простое решение.
Если поиск с преобразованием значения в число не удался, то будем искать его как текст. Объединяем оба варианта при помощи функции ЕСЛИОШИБКА.
=ЕСЛИОШИБКА(ВПР(—O2;$R$2:$S$8;2;0);ВПР(O2&»»;$R$2:$S$8;2;0))
В результате получаем немного громоздкий, но зато вполне универсальный вариант, который позволит избежать ошибок в формуле ВПР из-за несовпадения форматов значений.
Лишние пробелы и непечатаемые знаки.
Формула не работает, потому что в артикуле присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.).
В нашем примере на картинке чуть выше формула ВПР возвратила ошибку, так как в D8 при вводе данных случайно были добавлены два пробела после слова. И, поскольку сравниваются символьные значения, поиск, естественно, завершился неудачно.
Такая неточность может встречаться довольно часто, и обнаружить её визуально практически невозможно. К примеру, если наименование состоит из 2 слов, то вполне возможно случайное появление лишнего пробела между этими словами.
Могут быть также случайно добавлены символы, невидимые на экране и на печати (табуляция, перевод строки и т.д.). Визуально определить такие неточности ввода вы не сможете.
В этом случае можно использовать функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для удаления лишних пробелов либо других невидимых символов. Вместо
=ВПР(D8;$A$3:$B$21;2;ЛОЖЬ)
вводим
=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(D8));$A$3:$B$21;2;ЛОЖЬ)
или
=VLOOKUP(TRIM(CLEAN(D8)),$A$3:$B$21,2,FALSE)
Функция СЖПРОБЕЛЫ убирает пробелы, а ПЕЧСИМВ удаляет все непечатаемые и невидимые символы.
Эту комбинацию функций рекомендуем применять всегда «на всякий случай», чтобы подстраховать себя от случайных неточностей ввода данных.
Неправильно указан номер столбца.
Иногда функция ВПР не срабатывает, потому что неправильно указан номер столбца, значение из которого вы хотите получить (третий аргумент).
Он не может быть меньше 1 и не может быть больше, чем количество столбцов в указанном для поиска диапазоне. Если он указан неверно, то получим ошибку #ЗНАЧ!
Когда вы видите такое, пересчитайте количество колонок в диапазоне, который вы указали, и убедитесь, что оно не меньше, чем номер, указанный третьим аргументом функции. Быть может, вы указали 4, а колонок всего 3.
И в любом случае – это число должно быть больше 1. Вряд ли кто-то из вас будет руками вводить 0 или -1, но если вы получаете его при помощи каких-то вычислений, вложенных в формулу, то здесь-то и может быть проблема.
Неверная ссылка на данные из другой таблицы
Мы с вами говорили о том, что диапазон поиска может быть расположен в другой таблице или даже в другом файле Excel.
Если вы случайно допустили неточность при указании ссылки на эти данные, то увидите сообщение #ССЫЛКА!.
Это будет означать, что вы ссылаетесь на неверный диапазон, который Excel найти не может.
После изменения таблицы функция ВПР перестала работать
Вы сделали все правильно и до тех пор, пока вы не вставили несколько строк или столбцов в вашу таблицу, все работало верно.
Теперь же появилась ошибка #Н/Д либо результаты стали явно неверны. В чем дело?
А дело всё в том, что вы не использовали абсолютные ссылки, не добавляли к адресам ячеек, строк и столбцов знак $. В результате после добавления (или удаления) строк или столбцов ваши ссылки изменились, и все сломалось.
Кроме того, в третьем аргументе вы указываете порядковый номер столбца, из которого нужно извлечь данные. Но если вы вставили дополнительные колонки, то и количество столбцов, и их порядковые номера изменятся. А у вас в формуле используются «старые» номера.
Постарайтесь отменить сделанные изменения, благо Excel хранит всю историю изменений вашей таблицы.
Важно! Всегда используйте абсолютные ссылки, где это необходимо, чтобы застраховать себя от подобных неожиданных изменений.
Как убрать сообщение #Н/Д в ВПР?
Если функция не находит точного совпадения или искомого объекта просто нет в таблице, то она выдает сообщения, о которых мы говорили выше. С одной стороны, это хорошо, поскольку отрицательный результат- это тоже результат.
Однако, иногда это не очень удобно, т.к. такие сообщения потом порождают новые проблемы в расчете итогов, попадают в распечатку и т.д. Ведь если на эту ячейку ссылаются какие-то другие формулы, то они будут неправильно работать.
Можно легко перехватить ошибки и заменить их на любое другое подходящее нам значение (например, на ноль) с помощью функции ЕСЛИОШИБКА.
Синтаксис её подобен ЕСЛИ. В качестве условия используется выражение, которое потенциально может возвратить сообщение об ошибке.
=ЕСЛИОШИБКА([выражение];[значение_если_ошибка])
Задача – заменить его числом или выражением, указанным во втором аргументе. Рассмотрим это на примере.
В нашем случае маракуйя отсутствует в прайсе. Но вместо #Н/Д мы выведем ноль.
Вот как нужно будет преобразовать наши формулы, чтобы все выглядело красиво.
=ЕСЛИОШИБКА(ВПР(A4;$D$2:$E$7;2;0);0)
Естественно, вместо нуля можно вписать «не найдено» либо любое другое сообщение, а можно просто поставить пробел. Но в случае, когда на основе этих ячеек затем производятся какие-то вычисления, ноль, возможно, будет предпочтительнее.
Надеемся, теперь вы сможете ответить на вопрос: «Почему не работает функция ВПР в Excel?».
[the_ad_group id=»48″]
Еще полезные примеры:
Функция ЕСЛИОШИБКА – примеры формул — В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального… 
Как объединить две или несколько таблиц в Excel — В этом руководстве вы найдете некоторые приемы объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается на одном… 
Вычисление номера столбца для извлечения данных в ВПР — Задача: Наиболее простым способом научиться указывать тот столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем изменять саму формулу, поскольку это может привести в случайным ошибкам.… 
4 способа, как сделать левый ВПР в Excel. — Функция ВПР – одна из самых популярных, когда нужно найти и извлечь из таблицы какие-либо данные. Но при этом она имеет один существенный недостаток. Поиск она производит в крайнем левом… 
ВПР с несколькими условиями: 5 примеров. — Очень часто наши требования к поиску данных не ограничиваются одним условием. К примеру, нам нужна выручка по магазину за определенный месяц, количество конкретного товара, проданного определенному покупателю и т.д. Обычными…
4 основных ошибки в режиме VLOOKUP и как их исправить?
ВПР просто не может выдавать ошибки из-за несоответствия данных; он вернет ошибки.
- # Н / Д Ошибка
- #ИМЯ? ошибка
- # ССЫЛКА! ошибка
- #ЗНАЧЕНИЕ! ошибка
Давайте подробно обсудим каждую ошибку на примере —
Вы можете скачать этот шаблон Исправить ошибки в шаблоне VLOOKUP Excel здесь — Исправить ошибки в шаблоне VLOOKUP Excel
# 1 Исправление ошибки # N / A в VLOOKUP
Эта ошибка обычно возникает по одной из многих причин. # N / A означает просто Недоступен является результатом формулы ВПР, если формула не может найти требуемое значение.
Прежде чем приступить к устранению этой проблемы, нам нужно знать, почему он выдает ошибку как # N / A. Эта ошибка возникает из-за ошибки ввода данных, из-за приблизительных критериев соответствия, из-за неправильных ссылок на таблицы, неправильного ссылочного номера столбца, данных не в вертикальной форме и т. Д.
У меня есть простая таблица отчета о продажах в таблице 1. В таблице 2 я применил формулу VLOOKUP и попытался извлечь значения из таблицы 1.
В ячейках F4 и F9 я получил ошибки как № Н / Д. Значение в ячейке E4 выглядит точно так же, как значение в ячейке A4, но все же я получил ошибку a № Н / Д. Теперь вы, должно быть, думаете, почему ВПР вернула результат как № Н / Д. Не о чем беспокоиться; выполните следующие действия, чтобы исправить ошибку.
- Шаг 1: Примените формулу LEN excel и найдите, сколько символов содержится в ячейках A4 и E4.
В ячейке C4 я применил функцию LEN, чтобы проверить, сколько символов находится в ячейке A4, и аналогичным образом я применил функцию LEN в ячейке D4, чтобы узнать, сколько символов в ячейке E4.
В ячейке A4 у меня 11 символов, но в ячейке E4 у меня 12 символов. Один лишний символ находится в ячейке E4 при сравнении с ячейкой A4.
С самого начала оба выглядят похожими друг на друга. Однако там есть один лишний символ, и это должен быть конечный пробел.
Мы можем просто отредактировать ячейку E4 и удалить пробел. Если мы удалим это лишнее пространство, мы получим результат.
Но это неправильный способ решения проблемы.
- Шаг 2: Мы можем удалить конечные пробелы, используя функцию TRIM в excel. Применяя VLOOKUP вместе с функцией TRIM, мы можем автоматически удалять пробелы.
Функция TRIM удаляет лишнее нежелательное пространство.
# 2 Исправление #VALUE! Ошибка в ВПР
Эта ошибка возникает из-за того, что в функции отсутствует один из параметров. Взгляните, например, на приведенную ниже таблицу.
ВПР начинается со значения ПРОСМОТР, затем диапазона таблицы, за которым следует порядковый номер столбца и тип соответствия. Если вы посмотрите на изображение выше, параметры формулы не в идеальном порядке. На месте таблицы значений поиска находится диапазон, в месте диапазона таблицы — номер индекса столбца и так далее.
Нам может просто нужно правильно указать формулу, чтобы устранить эту ошибку.
# 3 Исправление ошибки VLOOKUP #REF
Эта ошибка возникает из-за неправильного ссылочного номера. Когда мы применяем или упоминаем номер индекса столбца, нам нужно указать точный номер столбца, из которого мы смотрим на требуемый результат. Если мы укажем номер индекса столбца, который находится за пределами диапазона выбора, тогда будет возвращено #REF! ошибка.
Значение поиска идеальное; диапазон таблицы идеален, но порядковый номер столбца здесь не идеален. Я выбрал диапазон таблиц от A3 до B8, то есть только диапазон таблиц от A3 до B8, то есть только два столбца, которые я выбрал.
В номере индекса столбца я упомянул 3, что выходит за пределы диапазона таблицы, поэтому функция VLOOKUP возвращает #REF! результат ошибки.
Укажите правильный порядковый номер столбца, чтобы исправить эту ошибку.
# 4 Исправление ошибки VLOOKUP #NAME
Мы получаем эту ошибку VLOOKUP #NAME из-за неправильного упоминания формулы. По моему личному опыту, я обычно набираю CLOOKUP вместо VLOOKUP.
В Excel нет формулы под названием Clookup, поэтому значения возвращаются как #NAME? тип ошибки.
Решение: Решение простое; нам просто нужно проверить написание формулы.
Что нужно помнить здесь
- # Н / Д ошибка из-за несоответствия данных.
- Ошибка #NAME из-за неправильного типа формулы.
- Ошибка #REF связана с неправильным порядковым номером столбца.
- #ЗНАЧЕНИЕ! Ошибка возникает из-за отсутствия или неправильного ввода параметров.
УЗНАТЬ БОЛЬШЕ >>
Post Views: 1 435
Перейти к содержимому
В данной статье расскажу о двух ошибках которые может выдать функция ВПР() :
- Ошибка #Н/Д;
- Ошибка #ЗНАЧ.
Перечисленные выше ошибки наиболее часто встречаться при использовании функции ВПР() и очень часто вызывают трудности с устранением у начинающих пользователей Excel .
Когда возникает ошибка #Н/Д и как от нее избавиться при использовании ВПР().
Сообщение об ошибке Н/Д можно расшифровать как аббревиатуру (НД) – нет данных, то есть функции ВПР() нечего отобразить, и она как бы сообщает: «нет данных для отображения».
Почему возникает ошибка Н/Д (НД)?
- Ошибка может возникать потому, что в Вашем списке (диапазоне) для сравнения нет искомого функцией ВПР() значения.
- Ошибка может возникать потому, что в Вашем списке (диапазоне) для сравнения значения ячеек имеют ошибки. Иногда ошибки нельзя увидеть «не вооружённым глазом», например, если в ячейке добавлен лишний пробел или едва заметная точка. ВПР() воспринимает значение ячейки без пробела и с пробелом как совершенно разные данные и выдает ошибку «Н/Д».
- Ошибка может возникать потому, что в искомой ячейке уже стоит значение «Н/Д», то есть ВПР() подтягивает эту ошибку из другой ячейки (искомой).
Как исправить ошибки Н/Д?
- Первый способ – применить обработку ошибок – функцию ЕСЛИОШИБКА(ВПР(*;*;*;0);”Здесь была ошибка”). Эта функция заменяет сообщение об ошибке на любое значение, которое Вы укажете.
- Способ №2 – удалить все пробелы и, по возможности, знаки препинания из ячеек. Для этого нужно нажатием клавиш ctrl+H вызвать окно замены значений, потом в поле «Найти» ввести пробел или знак препинания, а в поле «Заменить на:» не вводить ничего и нажить кнопку «Заменить все».
- Способ №3 – поставить в функции ВПР() допуск ошибки. Как нам извесчтно 4 –й аргумент функции это число ошибок которые может допускать в сравниваемой строке функция ВПР(). То есть, если поставить число «1», то допускается 1 ошибка при сравнении [ВПР(*;*;*;1)]. В таком случае строка без пробела и с одним пробелом будут считаться идентичными. Но в таком способе есть подвох — очень высока вероятность неверных результатов, например, слово «полка» и «палка» имеют отличие всего в один знак и будут восприняты функцией, как одно и то же.
Когда возникает ошибка #ЗНАЧ и как от нее избавиться при использовании ВПР().
Ошибка #ЗНАЧ может выводиться функцией ВПР(), если введенные значения аргументов функции некорректны и функция не может их обработать.
Казалось бы какие значения могут быть некорректными, если ВПР() необходимо просто сравнить одно значение с другим и присвоить ячейке данные из совпавших ячеек, но эта ошибка возникает.
Появляется ошибка #ЗНАЧ в функции ВПР() тогда, когда длина строки сравниваемой функцией слишком большая и не может быть обработана. Например, в Excel 2010 максимальная длина строки обрабатываемой функцией всего 255 символов, и если Вы будете сравнивать строки длиной 256 и более символов, то получите ошибку #ЗНАЧ.
Исправить ошибку #ЗНАЧ в таком случае можно уменьшив длины сравниваемых строк.
Еще ошибка #ЗНАЧ может возникнуть если Вы пропустили(не указали) один из аргументов в функции.
Не работает ВПР в 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, их возможные причины, и как их устранить.
Ошибка #ИМЯ?
Ошибка #ИМЯ появляется, когда имя, которое используется в формуле, было удалено или не было ранее определено.
Причины возникновения ошибки #ИМЯ?:
- Если в формуле используется имя, которое было удалено или не определено.
Ошибки в Excel – Использование имени в формуле
Устранение ошибки: определите имя. Как это сделать описано в этой статье.
- Ошибка в написании имени функции:
Ошибки в Excel – Ошибка в написании функции ПОИСКПОЗ
Устранение ошибки: проверьте правильность написания функции.
- В ссылке на диапазон ячеек пропущен знак двоеточия (:).
Ошибки в Excel – Ошибка в написании диапазона ячеек
Устранение ошибки: исправьте формулу. В вышеприведенном примере это =СУММ(A1:A3).
- В формуле используется текст, не заключенный в двойные кавычки. Excel выдает ошибку, так как воспринимает такой текст как имя.
Ошибки в Excel – Ошибка в объединении текста с числом
Устранение ошибки: заключите текст формулы в двойные кавычки.
Ошибки в Excel – Правильное объединение текста
Ошибка #ЧИСЛО!
Ошибка #ЧИСЛО! в Excel выводится, если в формуле содержится некорректное число. Например:
- Используете отрицательное число, когда требуется положительное значение.
Ошибки в Excel – Ошибка в формуле, отрицательное значение аргумента в функции КОРЕНЬ
Устранение ошибки: проверьте корректность введенных аргументов в функции.
- Формула возвращает число, которое слишком велико или слишком мало, чтобы его можно было представить в Excel.
Ошибки в Excel – Ошибка в формуле из-за слишком большого значения
Устранение ошибки: откорректируйте формулу так, чтобы в результате получалось число в доступном диапазоне Excel.
Ошибка #ЗНАЧ!
Данная ошибка Excel возникает в том случае, когда в формуле введён аргумент недопустимого значения.
Причины ошибки #ЗНАЧ!:
- Формула содержит пробелы, символы или текст, но в ней должно быть число. Например:
Ошибки в Excel – Суммирование числовых и текстовых значений
Устранение ошибки: проверьте правильно ли заданы типы аргументов в формуле.
- В аргументе функции введен диапазон, а функция предполагается ввод одного значения.
Ошибки в Excel – В функции ВПР в качестве аргумента используется диапазон, вместо одного значения
Устранение ошибки: укажите в функции правильные аргументы.
- При использовании формулы массива нажимается клавиша Enter и Excel выводит ошибку, так как воспринимает ее как обычную формулу.
Устранение ошибки: для завершения ввода формулы используйте комбинацию клавиш Ctrl+Shift+Enter.
Ошибки в Excel – Использование формулы массива
Ошибка #ССЫЛКА
В случае если формула содержит ссылку на ячейку, которая не существует или удалена, то Excel выдает ошибку #ССЫЛКА.
Ошибки в Excel – Ошибка в формуле, из-за удаленного столбца А
Устранение ошибки: измените формулу.
Ошибка #ДЕЛ/0!
Данная ошибка Excel возникает при делении на ноль, то есть когда в качестве делителя используется ссылка на ячейку, которая содержит нулевое значение, или ссылка на пустую ячейку.
Ошибки в Excel – Ошибка #ДЕЛ/0!
Устранение ошибки: исправьте формулу.
Ошибка #Н/Д
Ошибка #Н/Д в Excel означает, что в формуле используется недоступное значение.
Причины ошибки #Н/Д:
- При использовании функции ВПР, ГПР, ПРОСМОТР, ПОИСКПОЗ используется неверный аргумент искомое_значение:
Ошибки в Excel – Искомого значения нет в просматриваемом массиве
Устранение ошибки: задайте правильный аргумент искомое значение.
- Ошибки в использовании функций ВПР или ГПР.
Устранение ошибки: см. раздел посвященный ошибкам функции ВПР
- Ошибки в работе с массивами: использование не соответствующих размеров диапазонов. Например, аргументы массива имеют меньший размер, чем результирующий массив:
Ошибки в Excel – Ошибки в формуле массива
Устранение ошибки: откорректируйте диапазон ссылок формулы с соответствием строк и столбцов или введите формулу массива в недостающие ячейки.
- В функции не заданы один или несколько обязательных аргументов.
Ошибки в Excel – Ошибки в формуле, нет обязательного аргумента
Устранение ошибки: введите все необходимые аргументы функции.
Ошибка #ПУСТО!
Ошибка #ПУСТО! в Excel возникает когда, в формуле используются непересекающиеся диапазоны.
Ошибки в Excel – Использование в формуле СУММ непересекающиеся диапазоны
Устранение ошибки: проверьте правильность написания формулы.
Ошибка ####
Причины возникновения ошибки
- Ширины столбца недостаточно, чтобы отобразить содержимое ячейки.
Ошибки в Excel – Увеличение ширины столбца для отображения значения в ячейке
Устранение ошибки: увеличение ширины столбца/столбцов.
- Ячейка содержит формулу, которая возвращает отрицательное значение при расчете даты или времени. Дата и время в Excel должны быть положительными значениями.
Ошибки в Excel – Разница дат и часов не должна быть отрицательной
Устранение ошибки: проверьте правильность написания формулы, число дней или часов было положительным числом.