В excel не работает функция впр - Учим Эксель

Почему не работает формула ВПР (VLOOKUP) в Excel — Решение

Почему не работает формула ВПР (VLOOKUP) в Excel — Решение

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

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

Столбец поиска искомого значения не является последним левым.

Самая всераспространенная ошибка, когда ВПР не работает. Давайте разглядим этот же пример, что мы разглядывали при описании функции ВПР. У нас есть таблица заказов и Прайс лист.

Не работает функция ВПР

Направьте внимание, что в данном примере таблица Прайс лист различается. Наименования продуктов сейчас находятся не в первом столбце, а в 3-ем, опосля цены продукта.

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

ВПР не работает - крайний левый столбец

Решение

1. Если есть возможность, то перенесите столбец, где ищется разыскиваемое значение в последнее левое положение (или просто левее столбца из которого нужно возвращать значение, в нашем примере относительно столбца с ценой) как обозначено на рисунке выше.

2. Обычно переносить столбец не весьма комфортно, к примеру в вариантах, когда эту таблицу нам присылаются повсевременно в таком формате, потому обычно используют прием прибавления слева таблицы дублирующего вспомогательного столбца.

Выделяем 1-ый столбец прайс листа, избираем «Основная», «Ячейка», «Вставить», «Вставить столбцы на лист». Или используйте пустой столбец, если он у вас уже есть.
Встаем в первую ячейку вспомогательного столбца и просто делаем ссылку на подходящий нам столбец и протягиваем формулу вниз.

Не работает ВПР - вспомогательный столбец

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

Не работает ВПР - использование вспомогательного столбца

3. Можно применять комбинацию функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), как наиболее гибкую кандидатуру для ВПР.

Функция ВПР возвращает ошибку #Н/Д (#N/A)

#Н/Д (#N/A) значит «Нет данных» («not available»). При протягивании формулы ВПР у вас возникает #Н/Д. Вопросец даже не в том, что ВПР не работает, а почему возникают данные значения.

Не работает ВПР - Нет Данных

Решение

1. Не закреплен спектр таблицы

Если при протягивании формулы ВПР у вас 1-ое значение подтянулось а в других отобразилось #Н/Д то быстрее всего вы не закрепили спектр таблицы при помощи $ и при протягивании у вас таблица начала съезжать вниз. Снова прочитайте пристально описание функции ВПР с примером.

2. Разыскиваемое значение и значение в просматриваемом спектре не совпадает

На рисунке выше есть #Н/Д дважды. Один раз напротив продукта «Савок» и иной напротив слова «Стол».

В первом случае понятно — продукта «Савок» у нас нет в прайс листе, отсюда и ошибка.

Во 2-м случае неясно, продукт «Стол» у нас есть, но все равно Н/Д#. Обычно ошибка заключается в написании слов и форматах или в таблице заказов или на страничке Прайс лист.

В нашем случае у нас опосля слова «Стол » стоит пробел, потому данное слово не находится в прайс листе. Для начала проверьте содержимое ячеек на невидимые пробелы. В особенности нередко они встречаются в конце строчки. Кликните на ячейку и переместитесь в конец формулы. Удалите излишние пробелы, если они есть.

В данном примере можно так же применять функцию СЖПРОБЕЛЫ

=ВПР( СЖПРОБЕЛЫ(A3) ;$F$2:$I$16;3;0)

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

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

Еще наиболее редчайший вариант, это когда в слове быть может заместо российских слов латинские и напротив. К примеру A и А это различные буковкы различных алфавитов, потому и слова будут различными для ВПР. Чтоб проверить, просто сделайте в отдельном столбце ссылку одной ячейки на другую =A4=F6 в нашем примере эта формула возвратит «Ересь» потому что эти ячейки не равны (в одном слове есть излишний пробел), подобная ситуация будет если заместо российской буковкы «С» будет британская буковка «C».

Интересно почитать:  Excel как пользоваться функцией если в excel

Что любопытно, используя #Н/Д можно отыскивать неуникальные значения в перечнях. К примеру, у вас есть перечень служащих компании и для вас присылают иной перечень служащих и требуют из этого перечня отыскать тех людей, которые не работают в вашей компании. Применяем ВПР 1-го перечня к другому и там где #Н/Д и будут люди, которые не работают в вашей компании.

Функция ВПР возвращает ошибку #ЗНАЧ! (#VALUE!)

Обычно Excel докладывает о ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подступает по типу данных. Если брать функцию ВПР, то обычно стоит разглядеть две главные предпосылки ошибки #ЗНАЧ!.

1. Разыскиваемое значение больше 255 знаков.

В функции ВПР есть ограничение на длину искомого значения – оно не обязано быть наиболее 255 знаков. Если вы столкнулись с таковой неувязкой, то советуем применять для этих целей все ту же связку ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH).

2. Некорректно указан полный путь на другую книжку.

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

3. Аргумент номер_столбца в функции ВПР меньше 1

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

Остальные принципиальные индивидуальности работы функции ВПР

Если вы желаете разбить не саму ячейку, а текст в ячейке по столбцам, то тогда для вас необходимо глядеть другую статью «Как разбить текст по столбцам в Excel»

1. ВПР не чувствительна к регистру

Функция ВПР не чувствительна к регистру и для нее все знаки нижнего и верхнего регистра будут схожие. Другими словами, слово «Стол», «СТОЛ» и «стол» для функции ВПР будут схожими.

Если для вас нужно учесть регистр при использовании ВПР, то используйте другую функцию Excel, наприме (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр и ворачивается ИСТИНУ либо ЛОЖЬ при совпадении либо не совпадении с разыскиваемым значением.

2. ВПР возвращает 1-ое отысканное значение

Постоянно помните, что ВПР возвращает 1-ое отысканное значение, потому если в нашем примере в прайс листе по ошибке будет два схожих продукта с разной ценой, то ворачиваться будет лишь 1-ый стоимость.

3. Работа функции ВПР и добавлении либо удалении столбца

Обычно при написании функции ВПР мы в ручную отсчитываем столбец и указываем его в виде числа. В нашем примере с прайс листом. Вы зрительно обусловили, что совместно с вспомогательным столбцом, стоимость продукта находится в 3-ем столбце и указали цифру 3 в функцию.

Если кто-то добавит перед столбцом с ценой какую-нибудь информацию, к примеру о остатках продукта, то сейчас в 3-ем столбце будет остаток продуктов, а стоимость будет в 4-м столбце, но формуле это автоматом не обменяется, мы должны держать в голове о этом и поменять информацию.

Для решения данной задачи можно применять функцию ПОИСКПОЗ для поиска номера столбца. В качестве искомого значения мы можем указать текст «Стоимость» и находить в строке — шапке таблице Прайс лист. Сейчас если добавлять столбцы в таблицу Прайс лист, то функция ПОИСКПОЗ все равно отыщет ячейку со словом «Стоимость» и возвратит на номер позиции данной ячейки.

Почему не работает ВПР в Excel?

Функция ВПР – это весьма мощнейший инструмент поиска. Но если он по каким-то причинам закончился безуспешно, то вы получите сообщение о ошибке #Н/Д (#N/A в британском варианте).

Давайте попытаемся совместно ответим на вопросец: «Почему функция ВПР не работает?»

В первую очередь, сообщение о ошибке вы увидите, если данные, которое вы ищете, вправду отсутствуют в обозначенном спектре поиска. Тут мы бессильны.

Все другие случаи соединены с тем, что мы что-то не учли либо сделали ошибочно.

Итак, почему ВПР не находит значение, хотя оно есть?

Некорректность при вводе данных.

В этом случае ничего отыскать не получится, и ВПР вернет ошибку #Н/Д. Самое обычное решение тут весьма нередко оказывается самым верным. Может быть, вы просто ошиблись при вводе аспекта поиска.

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

Опечатка при вводе функции.

Если вы видите ошибку #ИМЯ?, то это значит, что при записи наименования самой функции вы допустили некорректность – перепутали либо добавили лишнюю буковку.

Проверьте синтаксис, и всё будет в порядке.

Неправильные ссылки.

Проверьте, правильно ли вы указали спектр поиска, также координаты значения, которое мы будем находить.

Случается, что в формуле вы указали обыденные (относительные) ссылки, забыв поменять их на абсолютные ($), а потом произвели какие-то деяния с таблицей. Например, добавили столбец. Ваши ссылки в формуле сейчас будут указывать на неправильные координаты.

Интересно почитать:  Count функция в excel

Принципиально! При таком изменении ссылок вы сможете и не знать, что найдено не то, что необходимо. Ведь некое значение все таки возвращено, хоть и неправильное. Потому будьте внимательны в использовании относительных и абсолютных ссылок! Советуем по данной причине стараться применять «умные» таблицы либо именованные спектры.

Ошибочно указан параметр «интервальный просмотр».

Включен поиск до первого ориентировочного совпадения в отсортированном спектре (параметр = ИСТИНА либо совсем опущен), но по сути данные не отсортированы.

О данной дилемме мы тщательно гласили ранее.

Столбец поиска не является первым слева столбцом спектра поиска.

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

Если ваши просматриваемые данные находятся не в первом, а во 2-м либо другом столбце, то она не сумеет отыскать ни 1-го совпадения, и вы вновь увидите сообщение #Н/Д.

Это принципиальное ограничение недозволено забывать.

Несовпадение форматов данных.

Формат ячейки, откуда берется разыскиваемое значение наименования (к примеру D3 в нашем случае), и формат ячеек первого столбца (A3:A21) из спектра поиска различаются (к примеру, числовой и текстовый). Этот вариант в особенности нередко встречается при использовании заместо текстовых наименований числовых кодов (номера счетов, артикулы, идентификаторы, даты и т.п.). Ведь число, записанное в таблицу Excel, быть может в 2-ух принципно различных состояниях — как число и как текст. И зрительно их отличить фактически нереально.

Преобразуем число в текст.

Видите ли, на вид записи схожи, но в D6 артикул сохранен как число, а в A10 – как текст (на таковых ячейках обычно видна пометка — зелёный уголок). Текст не быть может равнозначен числу, потому получаем #Н/Д. ВПР не находит значение, хотя оно на вид есть.

Как решить эту делему со случайным несоответствием в формате записи? Можно применять функцию ТЕКСТ, которая конвертирует хоть какой тип данных в текстовый вид.

Как видно, обыденную ссылку на D6 мы заменяем конструкцией

1-ый аргумент – это адресок, а 2-ой аргумент значит формат, в который мы будем преобразовывать значение (в нашем случае – текстовый).

преобразуем данные

Можно поступить и проще — «наклеить» к числу пустую строчку с помощью оператора клейки &.

В excel не работает функция впр

В данной статье описаны более всераспространенные предпосылки неверного результата функции В ФУНКЦИИ ВЛП, также советы по использованию функций ИНДЕКС иПОЗ.

Совет: Не считая того, обратитесь к коротким справочнику: советы по устранению проблем с #NA в комфортном PDF-файле. Вы сможете поделиться PDF-файлом с иными людьми либо распечатать его для справки.

Неувязка: разыскиваемое значение не находится в первом столбце аргумента таблица

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

В последующей таблице нам необходимо выяснить количество проданной капусты.

Ошибка #Н/Д в функции ВПР: искомое значение не находится в первом столбце массива таблицы

Ошибка #N/A, потому что разыскиваемый аргумент «Ели» отображается во 2-м столбце (Продукты) аргумента table_array A2:C10. В этом случае Excel находить его в столбце A, а не в столбце B.

Решение:вы сможете попробовать убрать эту делему, настроив в данной области ссылку на верный столбец. Если это нереально, попытайтесь передвигать столбцы. Это также быть может очень неописуемым, если у вас огромные либо сложные электрические таблицы, в каких значения ячеей являются результатом остальных вычислений, либо, может быть, есть и остальные логические предпосылки, по которым просто нереально перемещать столбцы. Решением является внедрение сочетания функций ИНДЕКС и ПОИСКПОЧ, которые могут находить значения в столбце независимо от их положения в таблице подытого. См. последующий раздел.

Заместо нее можно применять индекс либо СОВПАДЕНИЕ

Функции ИНДЕКС и ВЫБОРПОЗ являются неплохими вариациями для почти всех случаев, когда функции ВЛИО8 не отвечают вашим требованиям. Ключевое преимущество index/MATCH состоит в том, что вы сможете находить значения в столбце в любом месте таблицы подытогов. Индекс возвращает значение из обозначенной таблицы либо спектра в согласовании с его положением. Возвращает относительную позицию значения в таблице либо спектре. Чтоб отыскать значение в таблице либо массиве, укажите относительное положение значения в таблице либо массиве, используйте функции ИНДЕКС и НАЙТИ В ФОРМУЛЕ.

Существует несколько преимуществ использования индекса и СОВПАДЕНИЯ заместо ВЛ ПРОСМОТР:

При всем этом возвращаемая величина не обязана быть в том же столбце, что и в столбце подытовки. Это различается от ВЛП, в каком возвращаемая величина обязана быть в обозначенном спектре. Почему это принципиально? При использовании функции ВПР для вас необходимо знать номер столбца, содержащего значение. Хотя это может показаться не сложной задачей, это может оказаться мучительным, если у вас большая таблица и для вас необходимо подсчитать количество столбцов. Не считая того, если вы добавите либо удалите столбец в таблице, для вас придется перечесть и обновить col_index_num столбца. При использовании функций ИНДЕКС и ПОИСКПОЗ не необходимо подсчитывать столбцы.

Интересно почитать:  Функции даты и времени в excel

При помощи функций ИНДЕКС и MATCH можно указать или строчку, или столбец в массиве, или указать и то, и другое. Это значит, что значения можно находить по вертикали и по горизонтали.

При помощи функций ИНДЕКС и ПОИСКПОЗ можно отыскивать значения в любом столбце. В отличие от ВРОТ.В.В., в какой можно находить лишь значение в первом столбце таблицы, индекс и ВЫБОРПОЗ будут работать, если разыскиваемые значения есть в первом столбце, крайнем либо в любом другом месте меж ними.

Индекс и MATCH обеспечивают упругость динамической ссылки на столбец, содержащий возвращаемое значение. Это значит, что вы сможете добавлять столбцы в таблицу, не нарушая индекс и MATCH. С иной стороны, при добавлении столбца в таблицу ВЛП разрывается, так как она делает статическую ссылку на таблицу.

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

Синтаксис

Чтоб сделать синтаксис функции ИНДЕКС либо НАЙТИВКА, нужно применять аргумент массива либо ссылки функции ИНДЕКС и вложенный в нее синтаксис ФУНКЦИИ НАЙТИВ. Это форма:

=ИНДЕКС(массив либо ссылка; ПОИСКПОЗ(искомое_значение;массив;[тип_совпадения])

Заменим В ПРОСМОТР в примере выше при помощи индекса либо MATCH. Синтаксис будет смотреться последующим образом:

=ИНДЕКС(C2:C10;ПОИСКПОЗ(B13;B2:B10;0))

=ИНДЕКС(возвращает значение из C2:C10, которое будет ФУНКЦИЕЙ НАЙТИВ(Ольга, которая находится в массиве B2:B10, где возвращаемая величина является первым значением, подходящим Значению Ольга))

Функции ИНДЕКС и ПОИСКПОЗ можно использовать вместо функции ВПР

Формула отыскивает в C2:C10 1-ое значение, соответственное значению Капуста (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.

Ссылка на основную публикацию
Adblock
detector