Формула впр ошибка ссылка

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

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

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

В этом сравнительно небольшом руководстве вы найдете простой способ, как обойти такие ошибки, как #Н/Д, #ИМЯ, #ЗНАЧ!, которые часто появляются при работе с этой функцией, а также ознакомитесь с самыми частыми ситуациями, когда эти ошибки возникают.

Несколько причин, по которым возникает ошибка #Н/Д

В формуле =ВПР эта ошибка расшифровывается, как «Нет данных». Простыми словами, у электронной таблицы не получается отыскать значение, которое необходимо пользователю. Есть множество причин, по которым эта проблема может появляться.

Неправильный ввод искомого значения

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

Если ищется приближенное соответствие

Если пользователь применяет range_lookup (то есть, интервальный просмотр) в качестве аргумента функции, в конечном итоге возможно появление ошибки #Н/Д. Это может случиться при возникновении одного из следующих условий:

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

При поиске точного соответствия введенному запросу

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

На данный момент столбец не является крайним левым

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

Преодоление этой трудности заключается в следующем: если по какой-то причине не получается переместить столбец влево, необходимо использовать сразу две функции Excel: ИНДЕКС(), ПОИСКПОЗ(). 

Числовые значения отформатированы, как текстовые

Это также часто встречаемый источник проблем с формулой ВПР(). Нередко пользователь может не замечать, что числовые значения отформатированы как текстовые. Часто такая проблема может встретиться, если информация копируется из других источников.

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

Еще одна причина ошибки – формат может быть обозначен, как «Общий». Определить это можно по расположению значения внутри ячейки. Если они выравниваются по левому краю, это означает, что формат выбран как «Общий».

Чтобы исправить эту проблему, достаточно просто нажать на параметр «Конвертировать в число» в контекстном меню.

Если эта ошибка вызвана несколькими ячейками с числовыми значениями, их необходимо выделить, после чего нужно кликнуть по соответствующей области правой кнопкой мыши. В ответ на это действие появится контекстное меню, в котором нужно выбрать опцию «Формат ячеек», далее нужно будет нажать на «Число» и выбрать числовой формат. Последнее действие – нажатие кнопки «ОК».

Если стоит пробел в начале или в конце содержимого

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

Решение номер 1: Если пробелы находятся в таблице, к которой применяется функция ВПР

Если в основной таблице обнаружены пробелы, можно применить функцию СЖПРОБЕЛЫ в аргументе «Искомое значение». Значительно проще это продемонстрировать на примере.

=ВПР(СЖПРОБЕЛЫ($F2),$A$2:$C$10,3,FALSE)

Решение номер 2: Если лишние пробелы находятся в столбце или таблице поиска

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

В результате получится формула массива, для правильного ввода которой нужно нажать на комбинацию клавиш Ctrl+Shift+Enter.

В качестве альтернативного способа решения этой проблемы можно воспользоваться дополнением «Trim Spaces for Excel», которое позволяет убрать ненужные пробелы в формулах как в главной таблице, так и в таблице поиска. Это бесплатный инструмент, который можно скачать по .

Ошибка #ЗНАЧ! в формуле ВПР

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

Причина 1: значение, которое ищется, содержит больше 255 знаков

Это еще одно ограничение этой функции. Нельзя ее применять к ячейкам, содержимое которых длиннее 255 знаков. Если этот предел превышается, в результате может появиться сообщение #ЗНАЧ!.

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

=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))

Причина 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 можно оперативно изменять тип адреса ячейки.

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

Если нет необходимости демонстрировать пользователям код ошибки (например, #Н/Д), можно использовать ВПР() совместно с функцией ЕСЛИОШИБКА() в Excel последних версий вплоть до 2007. Также можно использовать две функции ЕСЛИ()+ЕОШИБКА(), если версия более старая.

Использование функции ЕСЛИОШИБКА

Для этой формулы характерен простой синтаксис:

ЕСЛИОШИБКА(значение; значение_если_ошибка)

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

Вот пример использования функции на практике:

=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);»»)

В этом случае будет возвращаться пустая ячейка, если обнаруживается ошибка.

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

Использование функции ЕОШИБКА

Описанная выше функция впервые появилась в Excel 2007 версии, поэтому, если используются более старые версии программы (например, если компьютер имеет небольшую производительность, но на нем нужно обрабатывать большие объемы данных), нужно использовать такую формулу:

=ЕСЛИ(ЕОШИБКА(ВПР формула);»Ваше сообщение при ошибке»;ВПР формула)

На практике формула будет выглядеть следующим образом:

=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));»»;ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))

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

Оцените качество статьи. Нам важно ваше мнение:

ВПР не работает

Как работает ВПР в Excel читайте в посте “Функция ВПР – чудо экономии времени”. Здесь же вы узнаете, что делать, если ВПР не работает. Так бывает. Скорее всего вы допустили ошибку. Есть четыре наиболее распространенных варианта ошибок:

1. Ошибка Н/Д в каждой ячейке.
2. Ошибка Н/Д преимущественно в нижней части таблицы.
3. Ошибка Н/Д в отдельных ячейках.
4. Ошибка #ССЫЛКА.

А теперь разберем каждый вариант подробнее.

Вариант 1. ВПР вернула ошибку #Н/Д во всех ячейках

• Вы неправильно выделили диапазон поиска – начали не с того столбца:

Все будет в порядке, как только вы исправите ошибку:

=впр(B3;'[прайс-лист.xlsx]Лист1′! $C$3:$E$7 ;3;ложь)

• Не совпадает текст, по которому ВПР ищет данные.

Например, в одном файле текст “Компьютер AF”, в другом файле “Компьютер—AF”. Если не заметили, разница всего лишь в наличии дефиса во втором случае. Тем не менее, ВПР не считает такое расхождение мелочью и, следовательно, не сработает.

Единственное исключение – допускаются разные регистры. Например, “Компьютер AF” и “компьютер af” не составит проблемы для ВПР.

Идентичность текста удобно проверять функцией =ЯЧЕЙКА=ЯЧЕЙКА. Смотрите пример на рисунке ниже и описание под ним:

1. Введите в ячейку D2 знак “=”
2. Выделите ячейку B2 с первым значением Планшет DC.
3. Опять введите “=”.
4. Выделите ячейку C2 со вторым значением.
5. Кликните Enter и протяните формулу вниз.

В результате в ячейках отобразится либо “ИСТИНА” (значения совпадают), либо “ЛОЖЬ” (значения не совпадают).

Как видите, не совпадают значения в строке 4: “Компьютер А” и “Компьютер F” (в ячейке D4 видим слово “ЛОЖЬ”).

Использовать функцию можно как в одном, так и в разных файлах.

Что можно предпринять, если текст не совпадает?

1. Если вы регулярно работаете с этими файлами, поменяйте текст вручную. Единожды потратив на это время, вы значительно сэкономите его в будущем. Если смена текста невозможна, создайте дополнительный столбец, куда введете текст из второго файла.
2. Иногда текст не совпадает из-за каких-то наборов знаков, которые повторяются в каждой ячейке. К примеру, в одном из файлов везде добавлен артикул: “010-01583 Компьютер AF”, а в другом файле этого артикула нет “Компьютер AF”. В этом случае текст можно заменить автоматически. Об этом читайте в следующих постах.

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

Как быть? Воспользуйтесь формулой =СЖПРОБЕЛЫ(ЯЧЕЙКА). Эта формула убирает все лишние пробелы. Подробнее – в посте о работе с текстом.

• В одном файле числа сохранены в виде текста, в другом – в виде чисел:

Для устранения проблемы сделайте следующее:

1. Выделите весь столбец с числами в виде текста (на рисунке выше диапазон B2:В5).
2. Подведите курсор к значку “!” и кликните по появившейся стрелочке.
3. Выберите из выпадающего списка “Преобразовать в число” – см. рисунок ниже.

Вариант 2. ВПР подтянула значения только в верхней части таблицы, в нижней — #Н/Д

• Смещение диапазона. Чаще всего это происходит, если данные подтягиваются из одного и того же файла. Например, из Листа 1 на Лист 2 или даже из одного и того же листа. Что происходит? Мы тянем формулу вниз, а вместе с ней тянется и диапазон. Вот так:

Как исправить? Надо закрепить диапазон:

1. Кликните дважды по ячейке с формулой (С5) – отобразится формула.
2. В отобразившейся формуле поместите курсор на В12 и нажмите клавишу F4 на клавиатуре.
3. Переместите курсор на С14 в этой же формуле и опять нажмите F4.
4. Кликните Enter и формула преобразится, из а) в б)

а) =ВПР(В5; В12:С14 ;2;ложь)

б) =ВПР(В5; $B$12:$С$14 ;2;ложь)

Значок “$” указывает на то, что диапазон закреплен. Теперь при протягивании формулы вниз, он не будет смещаться.

Вариант 3. ВПР подтягивает только часть значений, остальные – #Н/Д

• В некоторых ячейках текст не совпадает на 100%.

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

Вариант 4. ВПР возвращает ошибку #ССЫЛКА

• Вы неверно указали номер столбца. ВПР всегда считает столбцы, начиная с первого столбца выделенного диапазона. Поэтому на рисунке ниже красные номера неверные, зеленые – правильные:

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

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

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

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

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

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

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

Решение

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

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

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

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

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

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

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

Решение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Другие важные особенности работы функции ВПР

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

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

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

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

2. ВПР возвращает первое найденное значение

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

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

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

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

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

Исправление ошибки #Н/Д в функции ВПР

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

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

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

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

Ошибка #N/A, так как в втором столбце (фрукты) аргумента » Таблица » a2: C10 отображается значение подстановки «первое» . В этом случае Excel ищет его в столбце A, А не в столбце B.

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

Вместо этого используйте индекс и сопоставление

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

Существует несколько преимуществ использования функции индекс и СОВПАДЕНИе вместо функции ВПР.

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

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

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

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

Индексирование и сопоставление обеспечивает большую гибкость при использовании совпадений. ИНДЕКС и СОВПАДЕНИе могут находить точное совпадение или значение, которое больше или меньше значения подстановки. ФУНКЦИЯ ВПР будет искать только наиболее подходящие значения (по умолчанию) или точное значение. ФУНКЦИЯ ВПР также предполагает, что первый столбец в массиве таблицы отсортирован в алфавитном порядке, и предположим, что таблица не настроена таким образом, функция ВПР вернет первое ближайшее соответствие в таблице, которая может не быть искомыми данными.

Чтобы создать синтаксис для функции индекс и ПОИСКПОЗ, необходимо использовать аргумент array/Reference из функцией индекс и вложить в него синтаксис MATCH. Форма будет иметь следующий вид:

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

Используйте ИНДЕКСы и СОВПАДЕНИЯ для замены функции ВПР из приведенного выше примера. Синтаксис будет выглядеть следующим образом:

= Индекс (возвращающий значение из значения C2: C10, которое будет соответствовать (первое), которое находится где-то в массиве B2: B10, где возвращаемое значение является первым значением, соответствующим первое).

Формула ищет в C2:C10 первое значение, соответствующее значению Капуста (B7), и возвращает значение в ячейке C7 (100).

Проблема: не найдено точное совпадение

Если аргумент Интервальный_просмотр имеет значение ложь, а ВПР не может найти точное соответствие в данных, возвращается ошибка #N/a.

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

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

Проблема: искомое значение меньше, чем наименьшее значение в массиве

Если аргумент Интервальный_просмотр имеет значение истина, а значение подстановки меньше, чем наименьшее значение в массиве, отображается ошибка #N/a. ЗНАЧЕНИЕ TRUE ищет приблизительное совпадение в массиве и возвращает ближайшее значение меньше, чем искомое значение.

В приведенном ниже примере искомое значение равно 100, но в диапазоне B2:C10 нет значений меньше 100, поэтому возникает ошибка.

Исправьте искомое значение.

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

Проблема: столбец подстановки не отсортирован в порядке возрастания

Если аргумент Интервальный_просмотр имеет значение истина, а один из столбцов подстановки не отсортирован в порядке возрастания (A – Z), появится ошибка #N/a.

Измените функцию ВПР для поиска точного совпадения. Для этого задайте для аргумента Интервальный_просмотр значение ложь. Для FALSE нет необходимости в сортировке.

Для поиска значения в несортированной таблице можно также использовать функции ИНДЕКС и ПОИСКПОЗ.

Проблема: значение является большим числом с плавающей запятой

Если в ячейках есть значения времени или большие десятичные числа, Excel возвращает ошибку #N/A из-за числа с плавающей точкой. Числа с плавающей запятой — числа, которые следуют за десятичной запятой. (В Excel хранятся значения времени в виде чисел с плавающей запятой.) Excel не может хранить числа с большими плавающими точками, поэтому для правильной работы функции числа с плавающей запятой необходимо округлить до пяти десятичных разрядов.

Решение. Округлите числа до 5 десятичных разрядов с помощью функции ОКРУГЛ.

У вас есть вопрос об определенной функции?

Помогите нам улучшить Excel

У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.

Почему не работает функция ВПР и возвращает ошибку #Н/Д

Функция ВПР не работает и возвращает ошибку в том случае, если:

1. Задан точный поиск. Аргумент Интервальный просмотр равен 0 (ЛОЖЬ), а искомого значения в таблице нет.

2. Задан приблизительный поиск. Аргумент Интервальный просмотр равен 1 (ИСТИНА), однако таблица, по которой функция производит поиск, не отсортирована по возрастанию.

3. Если в значениях таблицы, по которой происходит поиск, есть лишние пробелы или невидимые непечатные символы, то функция ВПР не может найти нужное значение. В этом случае помогут функции СЖПРОБЕЛЫ и ПЕЧСИМВ.

СЖПРОБЕЛЫ удаляет в тексте лишние пробелы, кроме одиночных между слов.

ПЕЧСИМВ удаляет в тексте все непечатные символы. Формула в этом случае будет иметь следующий вид:

4. Ячейка, из которой берется исходное значение и ячейки таблицы, по которой происходит поиск имеют разный формат. Например, текстовый и числовой. В этом случае помогут функции Ч – преобразует нечисловые значения в числа и ТЕКСТ – преобразует число в текст. Формула в этом случае будет иметь следующий вид:

5. В том случае, если функция не может найти точного соответствия, тогда можно использовать функцию ЕСЛИОШИБКА и заменить ошибки, например, нулями.

Формула в этом случае будет иметь следующий вид:

Благодарю за подписку на мой канал. Следите за новыми статьями, делитесь с друзьями и ставьте лайки!

6 причин, почему функция ВПР не работает

​Смотрите также​​Интервальный_просмотр (Range Lookup)​​ смотрите сколько там​ ТМЦ) с числовыми​ форматы значений -​​ выбираем функцию ВПР.​​Результат работы функций​ копировать формулу в​ из представленных выше​ ячейки на пересечении​(НАИМЕНЬШИЙ) и​​ следите, чтобы длина​​Предположим, у нас есть​ чтобы она всегда​ИСТИНА​сводная таблица​ после добавления нового​Функция​​- в это​​ у Вас excel.exe​

• ​ кодами и формула​
• ​ в базе число​
• ​ Задаем аргументы (см.​
• ​ВПР​
• ​ другие ячейки того​ методов Вы ни​
• ​ конкретной строки и​

Вам нужно точное совпадение

​ROW​​ искомого значения не​​ список заказов и​​ отображала один и​​предполагает, что первый​, позволяющая выбрать значение​ столбца он стал​VLOOKUP​

​ поле можно вводить​ — должен быть​ ВПР, формула вроде​ как текст, в​ выше). Для нашего​и​ же столбца.​ выбрали, результат двумерного​​ столбца.​​(СТРОКА)​​ превышала этот лимит.​​ мы хотим найти​

​ тот же диапазон​ столбец в таблице​ и посмотреть результаты.​4-м​​(ВПР) – одна​​ только два значения:​ один.​ правильная, но не​ импорте — число​ примера: . Это​ДВССЫЛ​

​FL_Sal​ поиска будет одним​​Итак, давайте обратимся к​​Например, формула, представленная ниже,​​Соглашусь, добавление вспомогательного столбца​​Количество товара​ точных подстановок.​

Решение

​ отсортирован в алфавитном​Таблица ниже – это​. Однако функция​​ из самых популярных​​ ЛОЖЬ или ИСТИНА:​​Ну или иначе​​ работает. Почему?​ (это видно, посмотрев​

​ значит, что нужно​
​будет следующий:​

Зафиксируйте ссылки на таблицу

​es​ и тем же:​​ нашей таблице и​​ находит все повторения​ – не самое​(Qty.), основываясь на​Узнайте, как использовать абсолютные​​ порядке или по​​ список заказов. Допустим,​ВПР​ среди функций категории​

​Если введено значение​ — открываете один​​vasilyev​​ форматы. Там где​ взять наименование материала​​Если данные расположены в​​и​​Бывает так, что основная​​ запишем формулу с​ значения из ячейки​

Решение

​ изящное и не​​ двух критериях –​​ ссылки на ячейки.​ номерам, а затем​​ Вы хотите найти​​автоматически не обновилась.​Ссылки и массивы​0​ файл, затем в​​:​​ текст — везде​​ из диапазона А2:А15,​​ разных книгах Excel,​CA_Sales​ таблица и таблица​

​ функцией​ F2 в диапазоне​ всегда приемлемое решение.​​Имя клиента​​Не сохраняйте числовые значения​ выполняет поиск ближайшего​ все заказы определённого​Одним из решений будет​в Excel. А​

​или​
​ меню файл-открыть открываете​

​vasilyev​ одинаково, где число​​ посмотреть его в​​ то необходимо добавить​​– названия таблиц​​ поиска не имеют​ВПР​ B2:B16 и возвращает​​ Вы можете сделать​​(Customer) и​

Вставлен столбец

​ или значения дат​​ значения. Это способ​​ фрукта.​​ защитить таблицу, чтобы​​ также это одна​ЛОЖЬ (FALSE)​ второй файл и​

​, не знаю, как​ — разные.) Прикрепленные​ «Новом прайсе» в​ имя книги перед​ (или именованных диапазонов),​ ни одного общего​, которая найдет информацию​​ результат из тех​​ то же самое​Название продукта​ как текст.​

​ по умолчанию, если​​Сводная таблица​​ пользователи не могли​​ из самых сложны​​, то фактически это​ тянете свои ВПР()​ решить вашу задачу,​​ файлы post_268084.gif (79.87​​ столбце А. Затем​​ именованным диапазоном, например:​​ в которых содержаться​

Решение 1

​ столбца, и это​ о стоимости проданных​ же строк в​ без вспомогательного столбца,​(Product). Дело усложняется​При поиске числовых значений​ не указан другой.​позволяет выбрать значение​

Решение 2

​ вставлять новые столбцы.​ функций Excel, где​​ означает, что разрешен​​Но может я​​ а проблема в​​ КБ)​​ взять данные из​​=VLOOKUP($D$2,INDIRECT($D3&»Workbook1!_Sales»),2,FALSE)​

​ соответствующие отчеты о​​ мешает использовать обычную​​ в марте лимонов.​ столбце C.​ но в таком​ тем, что каждый​ или значений дат​​Вариант​​ из столбца​ В случае, когда​ страшная ошибка​ поиск только​ и ошибаюсь -​​ том, что в​​Михаил С.​

​ второго столбца нового​=ВПР($D$2;ДВССЫЛ($D3&»Workbook1!_Sales»);2;ЛОЖЬ)​ продажах. Вы, конечно​ функцию​Существует несколько способов выполнить​

​{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}​
​ случае потребуется гораздо​

Таблица стала больше

​ из покупателей заказывал​ убедитесь, что данные​ЛОЖЬ​​ID​​ пользователям потребуется такая​#N/A​точного соответствия​ отсюда не видать​ столбце «C» и​: Каюсь, на размер​​ прайса (новую цену)​​Если функция​ же, можете использовать​ВПР​

Решение

​ двумерный поиск. Познакомьтесь​{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3));»»)}​ более сложная формула​ несколько видов товаров,​ в первом столбце​​осуществляет поиск точного​​в фильтре, которое​ возможность, решение станет​

​(#Н/Д) может стать​, т.е. если функция​ что там как…​ «G» разный тип​​ не глянул. Обычно​​ и подставить их​ДВССЫЛ​​ обычные названия листов​​. Однако, существует ещё​​ с возможными вариантами​​Введите эту формулу массива​ с комбинацией функций​ как это видно​ аргумента​​ значения в первом​​ соответствует определенному фрукту,​​ не жизнеспособным.​​ привычной картиной. В​ не найдет в​svetazag​ данных: в столбце​

​ в этих случаях​ в ячейку С2.​ссылается на другую​​ и ссылки на​​ одна таблица, которая​

ВПР не может смотреть влево

​ и выберите наиболее​​ в несколько смежных​​INDEX​ из таблицы ниже:​таблица​ столбце.​ и получить список​Другой вариант – вставить​ этой статье мы​ прайс-листе укзанного в​: Огромное человеческое Вам​ «C» — числа​

Решение

​ не отвечаю.​Данные, представленные таким образом,​​ книгу, то эта​​ диапазоны ячеек, например​ не содержит интересующую​​ подходящий.​​ ячеек, например, в​​(ИНДЕКС) и​​Обычная функция​не являются текстовыми​​Для построения синтаксиса функции​​ всех связанных заказов.​ функцию​

​ рассмотрим 6 наиболее​ таблице заказов нестандартного​ СПАСИБО! Получилось!​ являются текстом, а​Guest​ можно сопоставлять. Находить​

​ книга должна быть​
​‘FL Sheet’!$A$3:$B$10​

Данные в таблице дублируются

​ нас информацию, но​​Вы можете использовать связку​​ ячейки​MATCH​ВПР​ значениями. Иначе функция​ ВПР вам потребуется​

​ В нашем примере​MATCH​​ частых причин, почему​​ товара (если будет​Прикрепленные файлы Благодарю!.jpg​

Решение 1

​ в столбце «G»​: Ваше решение частично​ численную и процентную​ открытой. Если же​, но именованные диапазоны​ имеет общий столбец​​ из функций​​F4:F8​(ПОИСКПОЗ).​​не будет работать​​ ВПР может вернуть​

Решение 2

​ следующая информация:​ выбрано значение​(ПОИСКПОЗ) в аргумент​ функция​​ введено, например, «Кокос»),​​ (46.97 КБ)​ числа являются числами.​​ подошло, теперь #Н/Д​​ разницу.​ она закрыта, функция​

​ гораздо удобнее.​ с основной таблицей​ВПР​, как показано на​Вы уже знаете, что​

​ по такому сценарию,​​ неправильное или непредвиденное​Значение, которое вам нужно​​ID​​col_index_num​ВПР​ то она выдаст​Hugo​Примечание: можно было​ стало меньше. но​​До сих пор мы​​ сообщит об ошибке​​Однако, когда таких таблиц​​ и таблицей поиска.​

ВПР без забот

​(VLOOKUP) и​ рисунке ниже. Количество​ВПР​ поскольку она возвратит​​ значение.​​ найти, то есть​равное​(номер_столбца) функции​не работает.​ ошибку #Н/Д (нет​

​: Светлана — озадачьте​ не выкладывать всю​
​ все же -​
​ предлагали для анализа​

​#REF!​

office-guru.ru

Функция ВПР для Excel — Служба поддержки Office

​ много, функция​​Давайте разберем следующий пример.​ПОИСКПОЗ​ ячеек должно быть​может возвратить только​ первое найденное значение,​Сортируйте первый столбец​ искомое значение.​23​ВПР​Вам нужно точное совпадение​ данных).​ админов проблемой, пусть​ формулу: достаточно было​ иногджа еще встречается.​ только одно условие​(#ССЫЛ!).​ЕСЛИ​ У нас есть​(MATCH), чтобы найти​ равным или большим,​

​ одно совпадающее значение,​ соответствующее заданному искомому​Если для аргумента​Диапазон, в котором находится​(Бананы).​.​Зафиксируйте ссылки на таблицу​Если введено значение​ чинят.​ сделать формулу только​

​Прошу прощения, удалил​​ – наименование материала.​Урок подготовлен для Вас​– это не​ основная таблица (Main​

​ значение на пересечении​ чем максимально возможное​

​ точнее – первое​ значению. Например, если​интервальный_просмотр​ искомое значение. Помните,​Эта статья показывает решения​Функция​Вставлен столбец​

​1​​svetazag​ с одной функцией:​ лишние строки.​ На практике же​ командой сайта office-guru.ru​ лучшее решение. Вместо​

Технические подробности

​ table) со столбцом​ полей​ число повторений искомого​

​ найденное. Но как​

​ Вы хотите узнать​

​указано значение ИСТИНА,​

• ​ что для правильной​

• ​ 6 наиболее распространённых​

Начало работы

​ «срабатывающие» при ВПР​ 3-м и т.д.​ позволяет данные из​

1. ​ нужный диапазон поиска.​ таблицы. Кроме этого,​=VLOOKUP(«Lemons»,$A$2:$I$9,MATCH(«Mar»,$A$1:$I$1,0),FALSE)​

2. ​ формулу массива.​ хотите извлечь 2-е​, запишите вот такую​.​ Например, если искомое​ Вы сможете насладиться​ Это сделает аргумент​ВПР​ а​А так -​ текста в числа?​

3. ​ — подсветил желтым​ критериям.​ одной таблицы переставить​Как Вы, вероятно, знаете,​ у нас есть​=ВПР(«Lemons»;$A$2:$I$9;ПОИСКПОЗ(«Mar»;$A$1:$I$1;0);ЛОЖЬ)​Если Вам интересно понять,​ или 3-е из​

4. ​ формулу:​Используйте подстановочные знаки​ значение находится в​ более беззаботным будущим​col_index_num​, известный как​приблизительного соответствия​ сами пытаемся решить​Казанский​ цветом на листе​Таблица для примера:​ в соответствующие ячейки​

​ функция​ 2 таблицы поиска.​Формула выше – это​

​ как она работает,​ них? А что​=VLOOKUP(B1,$A$5:$C$14,3,FALSE)​Если значение аргумента​ ячейке C2, диапазон​ в компании замечательных​(номер_столбца) динамичным, т.е.​range_lookup​, т.е. в случае​

Примеры

​ все проблемы по​

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

Распространенные неполадки

Рекомендации

См. также

​При желании вы можете​
​ актуальными справочными материалами​ решить проблему, описанную​ Если производится поиск​
​ с пользователем злую​и​
​: Спасибо разобрался, только​ книге (2.5к строк)​ поступает несколько наименований.​
​ материалы в определенном​ функцией​.​
​ за март, то​ то выражение​ 3-й и 4-й​
​ столбец, в котором​ одиночному символу, а​
​ указать слово ИСТИНА,​ на вашем языке.​
​ выше.​ уникального значения, то​
​ шутку, подставив значение​прайс-лист​
​ не через убрать​ООчень благодарен.​
​Добавляем в таблицу крайний​ количестве.​

support.office.com

Продвинутые примеры с функцией ВПР: поиск по нескольким критериям

​ЕСЛИ​Чтобы добавить цены из​​ не сможете задать​​СТРОКА(C2:C16)-1​ товары, купленные заданным​ объединить все нужные​ звездочка — любой последовательности​ если вам достаточно​​ Эта страница переведена​​=VLOOKUP(I3,B3:G11,MATCH(J2,B2:G2,0),FALSE)​ аргументом​ не того товара,​:​ ошибку, а «Преобразовать​Теперь наконец пойду​ левый столбец (важно!),​Стоимость материалов – в​на ссылку с​ второй таблицы поиска​ номер столбца для​возвращает номер соответствующей​ клиентом.​​ критерии. В нашем​​ символов. Если нужно​ приблизительного совпадения, или​

• ​ автоматически, поэтому ее​=ВПР(I3;B3:G11;ПОИСКПОЗ(J2;B2:G2;0);ЛОЖЬ)​
• ​range_lookup​ который был на​Задача — подставить цены​
• ​ в число»​ спать со спокойствием)​
• ​ объединив «Поставщиков» и​ прайс-листе. Это отдельная​
• ​ функцией​ в основную таблицу,​
• ​ третьего аргумента функции​ строки (значение​

Поиск в Excel по нескольким критериям

​Простейший способ – добавить​​ примере это столбцы​​ найти сам вопросительный​ слово ЛОЖЬ, если​ текст может содержать​По мере добавления новых​(интервальный_просмотр) должно быть​ самом деле! Так​ из прайс-листа в​Можно преобразовывать текст​Светлана​ «Материалы».​ таблица.​ДВССЫЛ​ необходимо выполнить действие,​ВПР​

Пример 1: Поиск по 2-м разным критериям

​-1​ вспомогательный столбец перед​Имя клиента​​ знак или звездочку,​​ вам требуется точное​ неточности и грамматические​​ строк в таблицу,​​FALSE​​ что для большинства​​ таблицу заказов автоматически,​ в число в​: Первый раз обращаюсь​Таким же образом объединяем​Необходимо узнать стоимость материалов,​. Вот такая комбинация​

​ известное как двойной​​. Вместо этого используется​​позволяет не включать​ столбцом​(Customer) и​ поставьте перед ними​ совпадение возвращаемого значения.​ ошибки. Для нас​ функции​(ЛОЖЬ).​​ реальных бизнес-задач приблизительный​​ ориентируясь на название​​ самой формуле, не​​ к данной функции​ искомые критерии запроса:​

​ поступивших на склад.​
​ВПР​

​ВПР​ функция​​ строку заголовков). Если​​Customer Name​​Название продукта​​ знак тильды (~).​ Если вы ничего​

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

​ но если его​ разрешать. Исключением является​ чтобы потом можно​ листе: Код =ЕСЛИ(ЕНД(ВПР(—C2;G:H;2;0));0;ВПР(—C2;G:H;2;0))супер!​ получается, помогите пожалуйста​​ нужном месте и​​ подставит цену из​ДВССЫЛ​ВПР​, чтобы определить этот​​IF​​ именами клиентов с​ что объединенный столбец​

​ =VLOOKUP("Fontan?",B2:E7,2,FALSE) можно выполнить​
​ умолчанию всегда подразумевается​

​ полезна. Просим вас​

​ для гарантии, что​
​ не указать, то​

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

Пример 2: ВПР по двум критериям с просматриваемой таблицей на другом листе

​ поиск всех случаев​ вариант ИСТИНА, то​ уделить пару секунд​ новые строки таблицы​ будет использовано значение​ ищем числа, а​В наборе функций Excel,​ были)​: =ВПР(A8;’60’!$A$7:$B$245;2;0)​ функции: . Excel​ первую. И посредством​ паре:​Запишите функцию​

​MATCH(«Mar»,$A$1:$I$1,0)​ строку.​ имени, например,​ крайним левым в​ употребления фамилии​ есть приблизительное совпадение.​ и сообщить, помогла​ охвачены формулой. На​TRUE​

​ не текст -​​ в категории​​svetazag​

​=ЕСЛИ(ЕНД(ВПР(A8;'60'!$A$7:$B$245;2;0));"";ВПР(A8;'60'!$A$7:$B$245;2;0))​
​ находит нужную цену.​

​ обычного умножения мы​=VLOOKUP($D$2,INDIRECT($D3&»_Sales»),2,FALSE)​ВПР​ПОИСКПОЗ(«Mar»;$A$1:$I$1;0)​Результатом функции​​John Doe1​​ диапазоне поиска, поскольку​Иванов​Теперь объедините все перечисленное​

​ ли она вам,​ рисунке ниже показана​(ИСТИНА). В таком​ например, при расчете​Ссылки и массивы​: Добрый день.​

​=ЕСЛИОШИБКА(ВПР(A8;'60'!$A$7:$B$245;2;0);"")​
​Рассмотрим формулу детально:​

​ найдем искомое.​=ВПР($D$2;ДВССЫЛ($D3&»_Sales»);2;ЛОЖЬ)​, которая находит имя​В переводе на человеческий​IF​,​ именно левый столбец​в различных падежных​ выше аргументы следующим​ с помощью кнопок​ ситуация, когда функция​ случае для правильной​ Ступенчатых скидок.​(Lookup and reference)​

​При попытке с​​Светлана​​Что ищем.​​Алгоритм действий:​Где:​ товара в таблице​ язык, данная формула​(ЕСЛИ) окажется вот​John Doe2​ функция​ формах.​ образом:​

​ внизу страницы. Для​ВПР​ работы функции необходимо,​Все! Осталось нажать​имеется функция​ помощью функции ВПР​: Serge 007, спасибо,​Где ищем.​Приведем первую таблицу в​$D$2​Lookup table 1​​ означает:​​ такой горизонтальный массив:​​и т.д. Фокус​​ВПР​

Извлекаем 2-е, 3-е и т.д. значения, используя ВПР

​Убедитесь, что данные не​​=ВПР(искомое значение; диапазон с​​ удобства также приводим​просматривает не всю​ чтобы данные были​ОК​ВПР​ подгрузить в одну​ все получилось​Какие данные берем.​ нужный нам вид.​– это ячейка​, используя​Ищем символы «Mar» –​{1,»»,3,»»,5,»»,»»,»»,»»,»»,»»,12,»»,»»,»»}​ с нумерацией сделаем​

​просматривает при поиске​ содержат ошибочных символов.​ искомым значением; номер​ ссылку на оригинал​ таблицу в поисках​ отсортированы в порядке​и скопировать введенную​(VLOOKUP)​ книгу по искомому​Tubus1993​

​Допустим, какие-то данные у​ Добавим столбцы «Цена»​ с названием товара,​​SKU​​ аргумент​ROW()-3​ при помощи функции​ значения.​​При поиске текстовых значений​​ столбца в диапазоне​​ (на английском языке).​​ нужного фрукта.​ возрастания.​ функцию на весь​​.​​ значению данные из​: Здравствуйте.Не работает впр,возможно​ нас сделаны в​

​ и «Стоимость/Сумма». Установим​
​ она неизменна благодаря​

​, как искомое значение:​lookup_value​​СТРОКА()-3​​COUNTIF​Итак, Вы добавляете вспомогательный​

• ​ в первом столбце​​ с возвращаемым значением;​​Когда вам требуется найти​​Форматируйте диапазон ячеек как​​На рисунке ниже показана​

  ​ столбец.​
​Эта функция ищет​

• ​ другой книги, возникает​​ дело в форматах.Требуется​​ виде раскрывающегося списка.​​ денежный формат для​​ абсолютной ссылке.​

  ​=VLOOKUP(A2,New_SKU,2,FALSE)​
​(искомое_значение);​

​Здесь функция​(СЧЁТЕСЛИ), учитывая, что​ столбец в таблицу​ убедитесь, что данные​ при желании укажите​ данные по строкам​ таблицу (Excel 2007+)​

​ функция​​Функция​​ заданное значение (в​ следующая проблема.​ заполнить цены в​ В нашем примере​

​ новых ячеек.​
​$D3​

​=ВПР(A2;New_SKU;2;ЛОЖЬ)​

• ​Ищем в ячейках от​​ROW​ имена клиентов находятся​ и копируете по​ в нем не​
• ​ ИСТИНА для поиска​​ в таблице или​​ или как именованный​​ВПР​
• ​ВПР (VLOOKUP)​​ нашем примере это​В мастере функций​ колонку.Не посмотрите?​ – «Материалы». Необходимо​
• ​Выделяем первую ячейку в​​– это ячейка,​Здесь​ A1 до I1​

​(СТРОКА) действует как​ в столбце B:​ всем его ячейкам​ содержат начальных или​ приблизительного или ЛОЖЬ​ диапазоне, используйте функцию​

​ диапазон. Такие приёмы​с пропущенным аргументом​возвращает ошибку #Н/Д​​ слово «Яблоки») в​​ в аргумент​Файл удален​ настроить функцию так,​ столбце «Цена». В​ содержащая первую часть​New_SKU​ – аргумент​​ дополнительный счётчик. Так​​=B2&COUNTIF($B$2:B2,B2)​ формулу вида:​ конечных пробелов, недопустимых​ для поиска точного​ ВПР — одну из​ дадут гарантию, что​

Извлекаем все повторения искомого значения

​range_lookup​​ (#N/A) если:​​ крайнем левом столбце​Таблица​- велик размер​ чтобы при выборе​ нашем примере –​ названия региона. В​– именованный диапазон​lookup_array​ как формула скопирована​​=B2&СЧЁТЕСЛИ($B$2:B2;B2)​​=B2&C2​​ прямых (‘ или​​ совпадения).​​ функций ссылки и​​ВПР​

​(интервальный_просмотр), которая возвращает​Включен точный поиск (аргумент​ указанной таблицы (прайс-листа)​не подтягивается выделенный​ — 787К [​ наименования появлялась цена.​ D2. Вызываем «Мастер​ нашем примере это​

​$A:$B​
​(просматриваемый_массив);​

​ в ячейки F4:F9,​После этого Вы можете​. Если хочется, чтобы​ «) и изогнутых​​Вот несколько примеров ВПР.​​ поиска. Например, можно​всегда будет обрабатывать​ ошибочный результат.​Интервальный просмотр=0​ двигаясь сверху-вниз и,​ диапазон из другой​МОДЕРАТОРЫ​Сначала сделаем раскрывающийся список:​​ функций» с помощью​​FL​в таблице​

​Возвращаем точное совпадение –​ мы вычитаем число​ использовать обычную функцию​ строка была более​

Часть 1:

​ (‘ или “)​
​Проблема​

​ найти цену автомобильной​​ всю таблицу.​Если Вы ищите уникальное​) и искомого наименования​ найдя его, выдает​ книги.​]​​Ставим курсор в ячейку​​ кнопки «fx» (в​.​​Lookup table 1​​ аргумент​3​ВПР​​ читаемой, можно разделить​​ кавычек либо непечатаемых​Возможная причина​

​ детали по ее​​Чтобы форматировать диапазон как​​ значение, задайте последний​ нет в​​ содержимое соседней ячейки​

Часть 2:

​В первый раз​
​Tubus1993​

​ Е8, где и​​ начале строки формул)​​_Sales​, а​match_type​из результата функции,​, чтобы найти нужный​​ объединенные значения пробелом:​​ символов. В этих​Неправильное возвращаемое значение​​ номеру.​​ таблицу, выделите диапазон​​ аргумент равным​​Таблице​ (23 руб.) Схематически​​ сталкиваюсь с данной​​: Сильно извиняюсь.Вот​​ будет этот список.​​ или нажав комбинацию​– общая часть​2​

Часть 3:

​(тип_сопоставления).​
​ чтобы получить значение​

​ заказ. Например:​​=B2&» «&C2​​ случаях функция ВПР​​Если аргумент​​Совет:​ ячеек, который собираетесь​FALSE​.​ работу этой функции​ проблемой.​​Guest​​Заходим на вкладку «Данные».​ горячих клавиш SHIFT+F3.​ названия всех именованных​​– это столбец​​Использовав​​1​​Находим​​. После этого можно​​ может возвращать непредвиденное​интервальный_просмотр​​ Просмотрите эти видео YouTube​​ использовать для аргумента​​(ЛОЖЬ). Функция​​Включен приблизительный поиск (​​ можно представить так:​​Во вложении скриншот,​: Действительно, дело в​​ Меню «Проверка данных».​​ В категории «Ссылки​

Часть 4:

​ диапазонов или таблиц.​
​ B, который содержит​

​0​​в ячейке​​2-й​ использовать следующую формулу:​ значение.​​имеет значение ИСТИНА​​ экспертов сообщества Excel​​table_array​​ВПР​​Интервальный просмотр=1​​Для простоты дальнейшего использования​​ где в одной​​ форматах.​​Выбираем тип данных –​​ и массивы» находим​​ Соединенная со значением​​ названия товаров (смотрите​​в третьем аргументе,​​F4​

Часть 5:

​товар, заказанный покупателем​
​=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)​

​Для получения точных результатов​ или не указан,​​ для получения дополнительной​​(таблица). На Ленте​в примере выше​), но​​ функции сразу сделайте​​ книге Excel в​Хоть он у​ «Список». Источник –​ функцию ВПР и​ в ячейке D3,​ на рисунке выше)​ Вы говорите функции​

Двумерный поиск по известным строке и столбцу

​(строка 4, вычитаем​Dan Brown​=ВПР(«Jeremy Hill Sweets»;$A$7:$D$18;4;ЛОЖЬ)​ попробуйте воспользоваться функциями​ первый столбец должны​ справки с ВПР!​ меню нажмите​ должна выглядеть так:​Таблица​

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

​ 3), чтобы получить​:​или​ ПЕЧСИМВ или СЖПРОБЕЛЫ.​ быть отсортирован по​

Функции ВПР и ПОИСКПОЗ

​Самая простая функция ВПР​Home​​=VLOOKUP(H3,B3:F11,2,FALSE)​​, в которой происходит​​ дайте диапазону ячеек​​Остаток​ массивах, судя по​ материалов.​​ функцию можно вызвать​​ имя требуемого диапазона.​​ цен из таблицы​​искать первое значение,​

​2​
​=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)​

​=VLOOKUP(B1,$A$7:$D$18,4,FALSE)​Краткий справочник: ФУНКЦИЯ ВПР​​ алфавиту или по​​ означает следующее:​>​=ВПР(H3;B3:F11;2;ЛОЖЬ)​ поиск не отсортирована​ прайс-листа собственное имя.​введена функция ВПР​ ячейкам, общий, но​Когда нажмем ОК –​ перейдя по закладке​ Ниже приведены некоторые​Lookup table 2​ в точности совпадающее​​в ячейке​​=ВПР(«Dan Brown2»;$A$2:$C$16;3;ЛОЖЬ)​=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)​​Краткий справочник: советы​​ номерам. Если первый​=ВПР(искомое значение; диапазон для​

​Format as Table​
​Возможно, Вы захотите использовать​

​ по возрастанию наименований.​ Для этого выделите​ и в мастере​

• ​ в столбец А​ сформируется выпадающий список.​​ «Формулы» и выбрать​​ подробности для тех,​
• ​на основе известных​ с искомым значением.​F5​​Находим​​Где ячейка​
• ​ по устранению неполадок​ столбец не отсортирован,​​ поиска значения; номер​​(Главная > Форматировать​

​ сразу несколько функций​​Формат ячейки, откуда берется​​ все ячейки прайс-листа​ функций в​​ заводился, как текст,​​Теперь нужно сделать так,​ из выпадающего списка​ кто не имеет​ названий товаров. Для​​ Это равносильно значению​​(строка 5, вычитаем​3-й​​B1​​ функции ВПР​

​ возвращаемое значение может​ столбца в диапазоне​ как таблицу) и​ВПР​ искомое значение наименования​ кроме «шапки» (G3:H19),​Аргументе​ и не смотря,​

Функция СУММПРОИЗВ

​ чтобы при выборе​​ «Ссылки и массивы».​​ опыта работы с​ этого вставьте созданную​

​FALSE​
​ 3) и так​

Функции ИНДЕКС и ПОИСКПОЗ

​товар, заказанный покупателем​содержит объединенное значение​YouTube: видео ВПР​ быть непредвиденным. Отсортируйте​ с возвращаемым значением;​ выберите стиль из​

​, чтобы извлечь большее​
​ (например B3 в​

Именованные диапазоны и оператор пересечения

​ выберите в меню​Искомое значение​ на то, что​ определенного материала в​Откроется окно с аргументами​ функцией​ ранее формулу в​

1. ​(ЛОЖЬ) для четвёртого​​ далее.​​Dan Brown​​ аргумента​​ экспертов сообщества Excel​
2. ​ первый столбец или​​ точное или приблизительное​​ галереи. Откройте вкладку​ количество информации. Если​​ нашем случае) и​​Вставка — Имя -​указана ячейка​ формат ячейки был​ графе цена появлялась​ функции. В поле​ДВССЫЛ​ качестве искомого значения​ аргумента​SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3))​:​
3. ​lookup_value​,которые вам нужно​​ используйте значение ЛОЖЬ​​ совпадение — указывается как​

   ​Table Tools​
   ​ Вы собираетесь скопировать​
   ​ формат ячеек первого​

   ​ Присвоить (Insert -​RC[-3]​ изменён, он всё​ соответствующая цифра. Ставим​ «Искомое значение» -​.​

​ для новой функции​ВПР​НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3))​=VLOOKUP(«Dan Brown3»,$A$2:$C$16,3,FALSE)​(искомое_значение), а​ знать о функции​

1. ​ для точного соответствия.​​ 0/ЛОЖЬ или 1/ИСТИНА).​​>​

​ функцию​ столбца (F3:F19) таблицы​ Name — Define)​, но при переходе​ равно хранит текстовое​ курсор в ячейку​

Используем несколько ВПР в одной формуле

​ диапазон данных первого​Во-первых, позвольте напомнить синтаксис​ВПР​.​Функция​=ВПР(«Dan Brown3»;$A$2:$C$16;3;ЛОЖЬ)​4​​ ВПР​​#Н/Д в ячейке​Совет:​Design​ВПР​ отличаются (например, числовой​или нажмите​ в​

​ значение.​ Е9 (где должна​ столбца из таблицы​ функции​​:​​Вот так Вы можете​SMALL​На самом деле, Вы​– аргумент​Как исправить #VALUE!​Если аргумент​ Секрет ВПР — для​(Работа с таблицами​в несколько ячеек,​​ и текстовый). Этот​​CTRL+F3​Аргумент​я думаю нye;жно​ будет появляться цена).​ с количеством поступивших​​ДВССЫЛ​​=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)​

​ создать формулу для​(НАИМЕНЬШИЙ) возвращает​ можете ввести ссылку​col_index_num​ ошибки в функции​​интервальный_просмотр​​ упорядочения данных, чтобы​​ > Конструктор) и​​ то необходимо зафиксировать​

1. ​ случай особенно характерен​​и введите любое​​Таблица​Tubus1993​​Открываем «Мастер функций» и​​ материалов. Это те​​(INDIRECT):​​=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)​

   ​ поиска по двум​
​n-ое​

   ​ на ячейку в​​(номер_столбца), т.е. номер​​ ВПР​​имеет значение ИСТИНА,​​ найти (фруктов) значение​​ в соответствующем поле​​ часть аргументов.​​ при использовании вместо​​ имя (без пробелов),​курсором мыши и​: Заметьте что он​ выбираем ВПР.​

2. ​ значения, которые Excel​INDIRECT(ref_text,[a1])​​Здесь​​ критериям в Excel,​наименьшее значение в​ качестве искомого значения​ столбца, содержащего данные,​как исправление ошибки​ а значение аргумента​​ слева от возвращаемое​​ измените имя таблицы.​

   ​На рисунке ниже показан​
​ текстовых наименований числовых​

   ​ например​​ выделении диапазона в​​ нашел код в​​Первый аргумент – «Искомое​​ должен найти во​​ДВССЫЛ(ссылка_на_текст;[a1])​​Price​​ что также известно,​​ массиве данных. В​ вместо текста, как​

​ которые необходимо извлечь.​ # н/д в​искомое_значение​

Динамическая подстановка данных из разных таблиц при помощи ВПР и ДВССЫЛ

​ значение (сумма), чтобы​В формуле на рисунке​ пример функции​ кодов (номера счетов,​Прайс​ другой книге, по​ котором были и​

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

​ ниже использовано имя​ВПР​ идентификаторы, даты и​. Теперь в дальнейшем​ скрину видно, что​ цифры и буквы(11,7),а​ с выпадающим списком.​Следующий аргумент – «Таблица».​

​ ссылкой на ячейку​$A:$C​ или поиск в​ по счёту позицию​ рисунке:​​ основную таблицу (Main​​Обзор формул в​​ значение в первом​​Используйте функцию ВПР для​ таблицы​, введенной некорректно. Для​

​ т.п.) В этом​
​ можно будет использовать​

​Аргумент​

• ​ просто цифры он​​ Таблица – диапазон​ Это наш прайс-лист.​ (стиль A1 или​в таблице​ двух направлениях.​ (от наименьшего) возвращать​Если Вы ищите только​ table), добавив данные​
• ​ Excel​​ столбце​ поиска значения в​FruitList​ аргументов​ случае можно использовать​ это имя для​Таблица​ не ищет​ с названиями материалов​
• ​ Ставим курсор в​​ R1C1), именем диапазона​​Lookup table 2​​Функция​​ – определено функцией​2-е​ из второй таблицы​как избежать появления​таблицы​ таблице.​.​lookup_value​ функции​​ ссылки на прайс-лист.​​остается пустым.​iba2004​

​ и ценами. Столбец,​ поле аргумента. Переходим​​ или текстовой строкой.​​, а​СУММПРОИЗВ​ROW​повторение, то можете​​ (Lookup table), которая​​ неработающих формул​, будет возвращено значение​

​Синтаксис​Функция​​(искомое_значение) и​​Ч​Теперь используем функцию​То есть функция​: Обратите внимание на​ соответственно, 2. Функция​ на лист с​ Второй аргумент определяет,​3​(SUMPRODUCT) возвращает сумму​(СТРОКА) (смотри Часть​​ сделать это без​​ находится на другом​Определять ошибок в​​ ошибки #Н/Д.​​ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])​​ВПР​​table_array​​и​​ВПР​ ВПР не воспринимает​

​ зелёный треугольничик в​
​ приобрела следующий вид:​

​ ценами. Выделяем диапазон​

• ​ какого стиля ссылка​​– это столбец​ произведений выбранных массивов:​ 2). Так, для​ вспомогательного столбца, создав​
• ​ листе или в​​ формулах​Если аргумент​Например:​имеет такое ограничение:​​(таблица) введены неправильные​​ТЕКСТ​
• ​. Выделите ячейку, куда​​ диапазон из другой​ левом верхнем углу​ .​ с наименованием материалов​ содержится в первом​ C, содержащий цены.​=SUMPRODUCT(($A$2:$A$9=»Lemons»)*($A$1:$I$1=»Mar»),$A$2:$I$9)​ ячейки​ более сложную формулу:​ другой рабочей книге​Функции Excel (по​интервальный_просмотр​​=ВПР(105,A2:C7,2,ИСТИНА)​​ она не может​

Как работают ДВССЫЛ и ВПР

​ диапазоны ячеек.​для преобразования форматов​​ она будет введена​​ книги.​

​ ячейки. Все числобуквенные​
​Нажимаем ВВОД и наслаждаемся​

​ и ценами. Показываем,​ аргументе:​На рисунке ниже виден​=СУММПРОИЗВ(($A$2:$A$9=»Lemons»)*($A$1:$I$1=»Mar»);$A$2:$I$9)​F4​=IFERROR(VLOOKUP($F$2,INDIRECT(«$B$»&(MATCH($F$2,Table4[Customer Name],0)+2)&»:$C16″),2,FALSE),»»)​ Excel, то Вы​ алфавиту)​имеет значение ЛОЖЬ,​

• ​=ВПР(«Иванов»;B2:E7;2;ЛОЖЬ)​​ извлекать информацию из​​Аргумент​​ данных. Выглядеть это​ (D3) и откройте​
• ​Пожалуйста, подскажите, в​​ выражения у Вас​​ результатом.​​ какие значения функция​​A1​

​ результат, возвращаемый созданной​В следующей статье я​​функция​​=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ(«$B$»&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&»:$C16″);2;ИСТИНА);»»)​ можете собрать искомое​функции Excel (по​ значение ошибки #Н/Д​

​Имя аргумента​ колонок, находящихся слева​table_array​ будет примерно так:​ вкладку​ чем может быть​ сохранены одинаково, как​Изменяем материал – меняется​ должна сопоставить.​, если аргумент равен​ нами формулой:​ буду объяснять эти​НАИМЕНЬШИЙ({массив};1)​В этой формуле:​​ значение непосредственно в​​ категориям)​​ означает, что найти​​Описание​​ от первой. Она​​(таблица) – это​=ВПР(ТЕКСТ(B3);прайс;0)​Формулы — Вставка функции​ причина.​

​ общий формат. А​​ цена:​​Чтобы Excel ссылался непосредственно​TRUE​В начале разъясним, что​ функции во всех​​возвращает​​$F$2​ формуле, которую вставляете​ВПР (бесплатно ознакомительная​ точное число не​искомое_значение​ ищет искомое значение​​ таблица, которую​​Функция не может найти​ (Formulas — Insert​​Прикрепленные файлы ВПР работа​​ вот числовые значения​

​Скачать пример функции ВПР​​ на эти данные,​​(ИСТИНА) или не​​ мы подразумеваем под​​ деталях, так что​

​1-й​– ячейка, содержащая​ в основную таблицу.​ версия)​ удалось.​

​    (обязательный)​
​ в крайней левой​

​ВПР​​ нужного значения, потому​​ Function)​ в 2-х книгах.jpg​ в столбце А​ в Excel​ ссылку нужно зафиксировать.​ указан;​​ выражением «Динамическая подстановка​​ сейчас можете просто​

​(наименьший) элемент массива,​ имя покупателя (она​
​Как и в предыдущем​
​Во второй части нашего​

​Дополнительные сведения об устранении​

office-guru.ru

Функция ВПР в Excel для чайников и не только

​Значение для поиска. Искомое​ колонке заданной таблицы​использует для поиска​ что в коде​. В категории​ (97.34 КБ)​

​ лежат как текст,​Так работает раскрывающийся список​ Выделяем значение поля​R1C1​ данных из разных​

Как пользоваться функцией ВПР в Excel

​ скопировать эту формулу:​ то есть​ неизменна, обратите внимание​ примере, Вам понадобится​ учебника по функции​

​ ошибок #Н/Д в​ значение должно находиться​ и возвращает информацию,​

​ и извлечения информации.​ присутствуют пробелы или​Ссылки и массивы (Lookup​Hugo: Может книги открыты​ а в столбце​ в Excel с​ «Таблица» и нажимаем​, если​

​ таблиц», чтобы убедиться​

1. ​=INDEX($A$2:$I$9,MATCH(«Lemons»,$A$2:$A$9,0),MATCH(«Mar»,$A$1:$I$1,0))​1​ – ссылка абсолютная);​ в таблице поиска​ВПР​ функции ВПР см.​
2. ​ в первом столбце​ находящуюся правее.​ Чтобы корректно скопировать​ невидимые непечатаемые знаки​ and Reference)​ в разных Экселях?​ D как выражение​ функцией ВПР. Все​ F4. Появляется значок​F​ правильно ли мы​=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ(«Lemons»;$A$2:$A$9;0);ПОИСКПОЗ(«Mar»;$A$1:$I$1;0))​. Для ячейки​$B$​ (Lookup table) вспомогательный​(VLOOKUP) в Excel​ в статье Исправление​ диапазона ячеек, указанного​
3. ​Решение этой проблемы –​ функцию​ (перенос строки и​найдите функцию​svetazag​ в формате «Общий».​ происходит автоматически. В​ $.​ALSE​ понимает друг друга.​
4. ​Если Вы не в​F5​– столбец​ столбец с объединенными​ мы разберём несколько​ ошибки #Н/Д в​ в​ не использовать​ВПР​ т.п.). В этом​
5. ​ВПР (VLOOKUP)​: В одинаковых, в​Pandora12​ течение нескольких секунд.​В поле аргумента «Номер​(ЛОЖЬ).​Бывают ситуации, когда есть​
6. ​ восторге от всех​возвращает​Customer Name​ значениями. Этот столбец​ примеров, которые помогут​ функции ВПР.​таблице​ВПР​, в аргументе​ случае можно использовать​

​и нажмите​ том-то и дело…​: Всегда с =ВПР​ Все работает быстро​ столбца» ставим цифру​В нашем случае ссылка​ несколько листов с​

​ этих сложных формул​2-й​;​

​ должен быть крайним​ Вам направить всю​#ССЫЛКА! в ячейке​.​вовсе. Используйте комбинацию​table_array​ текстовые функции​ОК​

1. ​Hugo​ () все получалось…​
2. ​ и качественно. Нужно​ «2». Здесь находятся​
3. ​ имеет стиль​ данными одного формата,​ Excel, Вам может​
4. ​наименьший элемент массива,​Table4​

​ левым в заданном​ мощь​

​Если значение аргумента​

Быстрое сравнение двух таблиц с помощью ВПР

​Например, если​ функций​(таблица) должна быть​СЖПРОБЕЛЫ (TRIM)​. Появится окно ввода​: В одинаковых? В​

1. ​ и вот тебе​ только разобраться с​
2. ​ данные, которые нужно​A1​ и необходимо извлечь​ понравиться вот такой​ то есть​– Ваша таблица​ для поиска диапазоне.​ВПР​номер_столбца​таблица​INDEX​ абсолютная ссылка на​и​ аргументов для функции:​ этом и причина​ на…​

​ этой функцией.​ «подтянуть» в первую​, поэтому можно не​ нужную информацию с​

Функция ВПР в Excel с несколькими условиями

​ наглядный и запоминающийся​3​ (на этом месте​Итак, формула с​на решение наиболее​превышает число столбцов​охватывает диапазон ячеек​(ИНДЕКС) и​ диапазон ячеек.​ПЕЧСИМВ (CLEAN)​Заполняем их по очереди:​

​ — нужно чтоб​

​Задача вот какая..​Duke​ таблицу. «Интервальный просмотр»​ указывать второй аргумент​ определенного листа в​ способ:​, и так далее.​ также может быть​

​ВПР​ амбициозных задач Excel.​ в​

1. ​ B2:D7, то искомое_значение​MATCH​Кликните по адресу ссылки​для их удаления:​
2. ​Искомое значение (Lookup Value)​ в одном​
3. ​ справа есть массив​: Использую ВПР.​ — ЛОЖЬ. Т.к.​ и сосредоточиться на​ зависимости от значения,​

​Выделите таблицу, откройте вкладку​

1. ​INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3))​
2. ​ обычный диапазон);​
3. ​может быть такой:​

Функция ВПР и выпадающий список

​ Примеры подразумевают, что​таблице​ должно находиться в​(ПОИСКПОЗ), которая стала​ внутри формулы и​=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)​- то наименование​svetazag​

​ данных (см. пример-приложение)​

1. ​Все довольно просто​ нам нужны точные,​ первом.​
2. ​ которое введено в​Formulas​
3. ​ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3))​$C16​=VLOOKUP(B2&» «&C2,Orders!$A&$2:$D$2,4,FALSE)​ Вы уже имеете​
4. ​, отобразится значение ошибки​ столбце B. См.​

​ привычной альтернативой для​ нажмите​=VLOOKUP(TRIM(CLEAN(B3));прайс;0)​ товара, которое функция​: Так…. что-то я​ первый столбец -​ и до определенного​ а не приблизительные​

1. ​Итак, давайте вернемся к​ заданную ячейку. Думаю,​
2. ​(Формулы) и нажмите​Функция​– конечная ячейка​=ВПР(B2&» «&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)​ базовые знания о​ #ССЫЛКА!.​ рисунок ниже.​ВПР​F4​
3. ​Для подавления сообщения об​ должна найти в​

​ не поняла Вас.​ код страны, второй​

​ времени работало без​ значения.​

​ нашим отчетам по​ проще это объяснить​Create from Selection​INDEX​ Вашей таблицы или​Здесь в столбцах B​ том, как работает​Дополнительные сведения об устранении​Искомое_значение​

exceltable.com

Функция ВПР. «Плавающая» ошибка #Н/Д

​ Слева — в​​ что в некоторых​ всему столбцу: цепляем​
​ отчёт – это​
​ по продажам для​Top row​ в массиве​ второе совпадающее значение.​ названия продуктов соответственно,​ будет интересно начать​ в статье Исправление​ ячейку.​ использован для извлечения​ должна быть записана​ когда функция не​ «Яблоки» из ячейки​

​ образом.​​ первом столбце -​ ячейках появляется #Н/Д​ мышью правый нижний​ отдельная таблица, расположенная​

​ на отдельном листе.​​ одинаковыми товарами и​ и​. Для ячейки​
​ необходимо извлечь остальные​

​Orders!$A&$2:$D$2​​ этого учебника, в​
​#ЗНАЧ! в ячейке​    (обязательный)​ слева от той,​
​=VLOOKUP($H$3,$B$3:$F$11,4,FALSE)​
​ соответствия, можно воспользоваться​Таблица (Table Array)​

​ в Excel с​​ названием страны (с​ находит среди искомой​ вниз. Получаем необходимый​ Чтобы формула работала​

​ в одинаковом формате.​​Left column​
​F4​
​ повторения, воспользуйтесь предыдущим​

​определяет таблицу для​​ которой объясняются синтаксис​Если значение аргумента​

​Диапазон ячеек, в котором​​ по которой производится​=ВПР(($H$3;$B$3:$F$11;4;ЛОЖЬ)​ функцией​- таблица из​
​ данными, открыли второй​​ данными, которые берутся​ таблицы значение. Хотя​​ результат.​​ верно, Вы должны​

​ Требуется найти показатели​​(в столбце слева).​

​функция​​ решением.​ поиска на другом​
​ и основное применение​таблица​ будет выполнен поиск​ поиск:​В этом примере ссылки​ЕСЛИОШИБКА​ которой берутся искомые​ файл в Excel​ из правого крайнего​ при проверке (поиске​Теперь найти стоимость материалов​ дать названия своим​
​ продаж для определенного​

​ Microsoft Excel назначит​​ИНДЕКС($C$2:$C$16;1)​Если Вам нужен список​ листе.​ВПР​меньше 1, отобразится​искомого_значения​

​=INDEX(B3:B13,MATCH(H3,C3:C13,0))​​ в аргументах​(IFERROR)​ значения, то есть​ с данными.​ столба), после выбора​ вручную): Ctrl+F на​ не составит труда:​ таблицам (или диапазонам),​ региона:​ имена диапазонам из​возвратит​ всех совпадений –​Чтобы сделать формулу более​

planetaexcel.ru

Функция =ВПР () не работает

​. Что ж, давайте​​ значение ошибки #ЗНАЧ!.​и возвращаемого значения​=ИНДЕКС(B3:B13;ПОИСКПОЗ(H3;C3:C13;0))​lookup_value​
​. Так, например, вот​ наш прайс-лист. Для​В первом файле​ названия страны, автоматически​ таблице по которой​ количество * цену.​ причем все названия​Если у Вас всего​ значений в верхней​Apples​ функция​ читаемой, Вы можете​ приступим.​Дополнительные сведения об устранении​ с помощью функции​Функция​(искомое_значение) и​ такая конструкция перехватывает​ ссылки используем собственное​ функцией ВПР подгружаем​ должен пробиться код​ функция производит поиск,​Функция ВПР связала две​ должны иметь общую​ два таких отчета,​ строке и левом​, для​

​ВПР​​ задать имя для​Поиск в Excel по​
​ ошибок #ЗНАЧ! в​ ВПР.​ВПР​table_array​

​ любые ошибки создаваемые​​ имя «Прайс» данное​ из второго файла​ страны, вместо этого​

​ значение присутствует и​​ таблицы. Если поменяется​ часть. Например, так:​

CyberForum.ru

Почему не работает формула ВПР

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

​может извлечь только​​(таблица) сделаны абсолютными.​​ ВПР и заменяет​​ ранее. Если вы​ данные по искомому​ выводится сообщение функции​ находится.​ прайс, то и​CA_Sales​ до безобразия простую​ Теперь Вы можете​функция​ поскольку она возвращает​ тогда формула станет​
​Извлекаем 2-е, 3-е и​ в статье Исправление​ ячеек должен содержать​ одну запись. Она​ Иногда достаточно зафиксировать​ их нулями:​ не давали имя,​

​ значению.​​ =ВПР () «#н/д».​По не известным​ изменится стоимость поступивших​

​,​​ формулу с функциями​​ осуществлять поиск, используя​​ИНДЕКС($C$2:$C$16;3)​ только одно значение​ выглядеть гораздо проще:​ т.д. значения, используя​ ошибки #ЗНАЧ! в​искомое_значение​ возвратит первую найденную​

​ только аргумент​​=ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)​ то можно просто​И вдруг на​ Не могу понять​

​ причинам появляется ошибка,​ на склад материалов​FL_Sales​ВПР​ эти имена, напрямую,​возвратит​ за раз –​

CyberForum.ru

Проблема ВПР при работе в двух книгах Excel

​=VLOOKUP(B2&» «&C2,Orders,4,FALSE)​​ ВПР​
​ функции ВПР.​(например, фамилию, как​ запись, соответствующую введённому​table_array​=IFERROR(VLOOKUP(B3;прайс;2;0);0)​ выделить таблицу, но​ одном из компьютеров​
​ в чем причина​ там где по​​ (сегодня поступивших). Чтобы​​,​и​ без создания формул.​
​Sweets​ и точка. Но​=ВПР(B2&» «&C2;Orders;4;ЛОЖЬ)​
​Извлекаем все повторения искомого​#ИМЯ? в ячейке​ показано на рисунке​ Вами условию поиска.​​(таблица).​​Если нужно извлечь не​ не забудьте нажать​ (который нам привезли​​ и как устранить​ ​ идее не должна.​​ этого избежать, воспользуйтесь​​TX_Sales​​ЕСЛИ​В любой пустой ячейке​​и так далее.​ ​ в Excel есть​​Чтобы формула работала, значения​ значения​Значение ошибки #ИМЯ? чаще​ ниже). Диапазон ячеек​​Если таблица содержит повторяющиеся​ ​Аргумент​​ одно значение а​
​ потом клавишу​ недавно с другого​ проблему, может быть​ Т.к. значение искомое​
​ «Специальной вставкой».​и так далее.​(IF), чтобы выбрать​​ запишите​IFERROR()​ функция​

​ в крайнем левом​Двумерный поиск по известным​

​ всего появляется, если​​ также должен содержать​ значения, функция​

​col_index_num​​ сразу весь набор​F4​ офиса) сталкиваемся с​ можно и без​

​ все же есть​​Выделяем столбец со вставленными​ Как видите, во​
​ нужный отчет для​=имя_строки имя_столбца​ЕСЛИОШИБКА()​INDEX​
​ столбце просматриваемой таблицы​ строке и столбцу​ в формуле пропущены​ возвращаемое значение (например,​ВПР​
​(номер_столбца) используется функцией​ (если их встречается​, чтобы закрепить ссылку​ проблемой, что ВПР​ =ВПР () решить​
​ в таблице «База»​ ценами.​ всех именах присутствует​ поиска:​, например, так:​В завершение, мы помещаем​(ИНДЕКС), которая с​ должны быть объединены​Используем несколько ВПР в​
​ кавычки. Во время​ имя, как показано​не справится с​
​ВПР​ несколько разных), то​ знаками доллара, т.к.​

​ не хочет подгружать​​ задачу ? Подскажите,​ в которой происходит​Правая кнопка мыши –​ «_Sales».​=VLOOKUP($D$2,IF($D3=»FL»,FL_Sales,CA_Sales),2,FALSE)​

​=Lemons Mar​​ формулу внутрь функции​ легкостью справится с​ точно так же,​ одной формуле​ поиска имени сотрудника​ на рисунке ниже),​ такой задачей правильно.​
​, чтобы указать, какую​ придется шаманить с​ в противном случае​ данные из одного​ кто знает..​ поиск.​
​ «Копировать».​Функция​=ВПР($D$2;ЕСЛИ($D3=»FL»;FL_Sales;CA_Sales);2;ЛОЖЬ)​… или наоборот:​

​IFERROR​​ этой задачей. Как​ как и в​ ​Динамическая подстановка данных из​ убедитесь, что имя​

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

​ она будет соскальзывать​​ файла в другой.​sn_88​
​На всякий случай​Не снимая выделения, правая​ДВССЫЛ​
​Где:​=Mar Lemons​(ЕСЛИОШИБКА), поскольку вряд​ будет выглядеть такая​

planetaexcel.ru

Использование функции ВПР (VLOOKUP) для подстановки значений

​ критерии поиска. На​ разных таблиц​ в формуле взято​Узнайте, как выбирать диапазоны​ данные в списке?​

Постановка задачи

​ из записи.​Усовершенствованный вариант функции ВПР​​ при копировании нашей​​Что значит -​​: Вы не верно​​ проверил в ОпенОфис​

​ кнопка мыши –​соединяет значение в​$D$2​Помните, что имена строки​ ли Вас обрадует​ формула, Вы узнаете​ рисунке выше мы​

Решение

​Функция​ в кавычки. Например,​​ на листе .​ ​ Если нет –​​В связи с тем,​​ (VLOOKUP 2).​ ​ формулы вниз, на​​ «нужно, чтоб в​​ пользуетесь функцией впр!​ — результат тот​ «Специальная вставка».​ столбце D и​– это ячейка,​ и столбца нужно​ сообщение об ошибке​ в следующем примере.​ объединили значения и​ВПР​ в функции =ВПР(«Иванов»;B2:E7;2;ЛОЖЬ)​номер_столбца​

​ удалите их. Это​ что аргумент введен​Быстрый расчет ступенчатых (диапазонных)​ остальные ячейки столбца​ одном»?​Сравниваемое значение должно​ же. В недоумении.​Поставить галочку напротив «Значения».​ текстовую строку «_Sales»,​​ содержащая название товара.​ разделить пробелом, который​#N/A​​Как упоминалось выше,​​ поставили между ними​​в Excel –​ имя необходимо указать​    (обязательный)​​ можно сделать быстро​​ как числовой индекс,​ скидок при помощи​ D3:D30.​Может мы не​

​ соответствовать ЛЕВОМУ столбцу​​Формат ячеек одинаковый.​​ ОК.​ тем самым сообщая​ Обратите внимание, здесь​ в данном случае​​(#Н/Д) в случае,​ВПР​ пробел, точно так​​ это действительно мощный​​ в формате​Номер столбца (начиная с​​ при помощи кнопки​​ он не очень​​ функции ВПР.​​Номер_столбца (Column index number)​​ правильно друг друга​ в выбранной вами​

​Помогите пожалуйста!​

• ​Формула в ячейках исчезнет.​​ВПР​ мы используем абсолютные​ работает как оператор​ если количество ячеек,​не может извлечь​ же необходимо сделать​ инструмент для выполнения​»Иванов»​
• ​ 1 для крайнего​​Removes Duplicates​ надёжен. Если в​Как сделать «левый ВПР»​- порядковый номер​ поняли?​ таблице!​Заранее благодарю.​ Останутся только значения.​в какой таблице​ ссылки, чтобы избежать​ пересечения.​ в которые скопирована​​ все повторяющиеся значения​​ в первом аргументе​ поиска определённого значения​и никак иначе.​ левого столбца​(Удалить дубликаты) на​ таблицу вставить новый​ с помощью функций​ (не буква!) столбца​
• ​vikttur​​Pandora12​Файл удален​​ искать. Если в​ изменения искомого значения​При вводе имени, Microsoft​ формула, будет меньше,​ из просматриваемого диапазона.​ функции (B2&» «&C2).​ в базе данных.​Дополнительные сведения см. в​
• ​таблицы​​ вкладке​ столбец, функция​ ИНДЕКС и ПОИСКПОЗ​ в прайс-листе из​
• • ​: Excel может быть​​: да, все верно..​​- велик размер​​Функция помогает сопоставить значения​​ ячейке D3 находится​ при копировании формулы​ Excel будет показывать​​ чем количество повторяющихся​​ Чтобы сделать это,​Запомните!​ Однако, есть существенное​ разделе Исправление ошибки​), содержащий возвращаемое значение.​Data​ВПР​Как при помощи функции​ которого будем брать​
  • ​ запущен двумя приложениями.​​ спасибо.. Пробелма решена​​ — [​​ в огромных таблицах.​​ значение «FL», формула​ в другие ячейки.​ подсказку со списком​ значений в просматриваемом​​ Вам потребуется чуть​​Функция​ ограничение – её​ #ИМЯ?.​интервальный_просмотр​(Данные).​может перестать работать.​ ВПР (VLOOKUP) заполнять​ значения цены. Первый​ Т.е. книги открываются​ !​МОДЕРАТОРЫ​ Допустим, поменялся прайс.​ выполнит поиск в​$D3​ подходящих имен, так​ диапазоне.​ более сложная формула,​ВПР​ синтаксис позволяет искать​Действие​    (необязательный)​Решили оставить дубликаты? Хорошо!​ Рисунок ниже показывает​ бланки данными из​

​ столбец прайс-листа с​​ не в одном​​Vlad999​]​ Нам нужно сравнить​

Ошибки #Н/Д и их подавление

​ таблице​​– это ячейка​​ же, как при​Выполнение двумерного поиска в​

• ​ составленная из нескольких​​ограничена 255 символами,​​ только одно значение.​Результат​​Логическое значение, определяющее, какое​​ В таком случае,​
• ​ именно такой сценарий.​​ списка​​ названиями имеет номер​​ экземпляре Excel​​: сцепка функций =ИНДЕКС(….;ПОИСКПОЗ(…….);ПОИСКПОЗ(……))​Михаил С.​ старые цены с​
• ​FL_Sales​ с названием региона.​ вводе формулы.​ Excel подразумевает поиск​ функций Excel, таких​ она не может​ Как же быть,​Используйте абсолютные ссылки в​ совпадение должна найти​ Вам нужна не​Столбец​Как вытащить не первое,​ 1, следовательно нам​Hugo​ или =ИНДЕКС(….;ПОИСКПОЗ(…….))​: Измените формулу на​​ новыми ценами.​​, если «CA» –​​ Используем абсолютную ссылку​​Нажмите​ значения по известному​ как​
  ​ искать значение, состоящее​
• ​ если требуется выполнить​ аргументе​ функция​ функция​Quantity​ а сразу все​ нужна цена из​: Открываете эти два​vasilyev​​ такую​​В старом прайсе делаем​​ в таблице​​ для столбца и​
  ​Enter​
  ​ номеру строки и​

​INDEX​ из более чем​​ поиск по нескольким​​интервальный_просмотр​ВПР​ВПР​(Количество) был​ значения из таблицы​​ столбца с номером​ ​ файла, идёте в​​: Есть две таблицы(с​=ВПР(A3&»»;База!A2:U3333;21;ЛОЖЬ)​ столбец «Новая цена».​CA_Sales​ относительную ссылку для​

​и проверьте результат​

​ столбца. Другими словами,​

P.S.

​(ИНДЕКС),​ 255 символов. Имейте​ условиям? Решение Вы​Использование абсолютных ссылок позволяет​, — приблизительное или точное.​. Для таких случаев​3-м​

Ссылки по теме

• ​Функции VLOOKUP2 и VLOOKUP3​ 2.​
• ​ менеджер процессов и​ разных программ учета​У вас разные​
• ​Выделяем первую ячейку и​и так далее.​ строки, поскольку планируем​
• ​В целом, какой бы​ Вы извлекаете значение​SMALL​ это ввиду и​
• ​ найдёте далее.​ заполнить формулу так,​Вариант​
• ​ отлично подойдёт​по счету, но​

planetaexcel.ru

​ из надстройки PLEX​

Ответ искал по всей сети, на русском и английском. Все варианты, вроде: 6 причин, почему не работает функция ВПР — я проверил.

В файле File1 используется функция ВПР с обращением на другой документ — File2, например:

ВПР(A1;'Z:Folder[File2.xlsm]Sheet1'!$A:$D;4;ЛОЖЬ)

При всех вариантах по списку ниже возникает ошибка вида:

ВПР(A1;'Z:Folder[File2.xlsm]#ССЫЛКА'!$A:$D;4;ЛОЖЬ)

Варианты, при которых возникает ошибка (диапазон A:D  пробовал вводить по-разному, фиксированным и нет):

1. Открываю сначала файлы File1 и File2, значение диапазона делаю выбором в файле File2, ссылка на открытый файл имеет вид:

   Код
   [File2.xlsm]Sheet1'!$A:$D  // то есть не абсолютный путь

   Если после этого закрыть файл File2 — значение пути становится абсолютным:

   Код
   Z:Folder[File2.xlsm]Sheet1'!$A:$D

   При следующем открытии файла File1 — все работает. Но если тут же открыть файл File2, то возникает ошибка.

2. Формирую функцию сразу с абсолютным путем:

   Код
   Z:Folder[File2.xlsm]Sheet1'!$A:$D

   при открытии файла File2 — ошибка.

Фактически, единственный способ получить файл без ошибок — это работать в File1 без открывания File2.

Значения в File1 вносятся постоянно, как раз во время работы  необходимо бывает открыть File2, File3 и так далее (в настоящем файле  ссылки на многие файлы) и посмотреть там те значения, на которые хочешь  сослаться в File1.

Как работает ВПР в Excel читайте в посте “Функция ВПР – чудо экономии времени”. Здесь же вы узнаете, что делать, если ВПР не работает. Так бывает. Скорее всего вы допустили ошибку. Есть четыре наиболее распространенных варианта ошибок:

1. Ошибка Н/Д в каждой ячейке.
2. Ошибка Н/Д преимущественно в нижней части таблицы.
3. Ошибка Н/Д в отдельных ячейках.
4. Ошибка #ССЫЛКА.

А теперь разберем каждый вариант подробнее.

Вариант 1. ВПР вернула ошибку #Н/Д во всех ячейках

Причины:

• Вы неправильно выделили диапазон поиска – начали не с того столбца:

Все будет в порядке, как только вы исправите ошибку:

=впр(B3;'[прайс-лист.xlsx]Лист1′!$B$3:$E$7;3;ложь) на

=впр(B3;'[прайс-лист.xlsx]Лист1′!$C$3:$E$7;3;ложь)

• Не совпадает текст, по которому ВПР ищет данные.

Например, в одном файле текст “Компьютер AF”, в другом файле “Компьютер—AF”. Если не заметили, разница всего лишь в наличии дефиса во втором случае. Тем не менее, ВПР не считает такое расхождение мелочью и, следовательно, не сработает.

Единственное исключение – допускаются разные регистры. Например, “Компьютер AF” и “компьютер af” не составит проблемы для ВПР.

Идентичность текста удобно проверять функцией =ЯЧЕЙКА=ЯЧЕЙКА. Смотрите пример на рисунке ниже и описание под ним:

1. Введите в ячейку D2 знак “=”
2. Выделите ячейку B2 с первым значением Планшет DC.
3. Опять введите “=”.
4. Выделите ячейку C2 со вторым значением.
5. Кликните Enter и протяните формулу вниз.

В результате в ячейках отобразится либо “ИСТИНА” (значения совпадают), либо “ЛОЖЬ” (значения не совпадают).

Как видите, не совпадают значения в строке 4: “Компьютер А” и “Компьютер F” (в ячейке D4 видим слово “ЛОЖЬ”).

Использовать функцию можно как в одном, так и в разных файлах.

Что можно предпринять, если текст не совпадает?

1. Если вы регулярно работаете с этими файлами, поменяйте текст вручную. Единожды потратив на это время, вы значительно сэкономите его в будущем. Если смена текста невозможна, создайте дополнительный столбец, куда введете текст из второго файла.
2. Иногда текст не совпадает из-за каких-то наборов знаков, которые повторяются в каждой ячейке. К примеру, в одном из файлов везде добавлен артикул: “010-01583 Компьютер AF”, а в другом файле этого артикула нет “Компьютер AF”. В этом случае текст можно заменить автоматически. Об этом читайте в следующих постах.

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

Как быть? Воспользуйтесь формулой =СЖПРОБЕЛЫ(ЯЧЕЙКА). Эта формула убирает все лишние пробелы. Подробнее – в посте о работе с текстом.

• В одном файле числа сохранены в виде текста, в другом – в виде чисел:

Для устранения проблемы сделайте следующее:

1. Выделите весь столбец с числами в виде текста (на рисунке выше диапазон B2:В5).
2. Подведите курсор к значку “!” и кликните по появившейся стрелочке.
3. Выберите из выпадающего списка “Преобразовать в число” – см. рисунок ниже.

Вариант 2. ВПР подтянула значения только в верхней части таблицы, в нижней — #Н/Д

Причина:

• Смещение диапазона. Чаще всего это происходит, если данные подтягиваются из одного и того же файла. Например, из Листа 1 на Лист 2 или даже из одного и того же листа. Что происходит? Мы тянем формулу вниз, а вместе с ней тянется и диапазон. Вот так:

Как исправить? Надо закрепить диапазон:

1. Кликните дважды по ячейке с формулой (С5) – отобразится формула.
2. В отобразившейся формуле поместите курсор на В12 и нажмите клавишу F4 на клавиатуре.
3. Переместите курсор на С14 в этой же формуле и опять нажмите F4.
4. Кликните Enter и формула преобразится, из а) в б)

а) =ВПР(В5;В12:С14;2;ложь)

б) =ВПР(В5;$B$12:$С$14;2;ложь)

Значок “$” указывает на то, что диапазон закреплен. Теперь при протягивании формулы вниз, он не будет смещаться.

Вариант 3. ВПР подтягивает только часть значений, остальные  – #Н/Д

Причина:

• В некоторых ячейках текст не совпадает на 100%.

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

Вариант 4. ВПР возвращает ошибку #ССЫЛКА

Причина:

• Вы неверно указали номер столбца. ВПР всегда считает столбцы, начиная с первого столбца выделенного диапазона. Поэтому на рисунке ниже красные номера неверные, зеленые – правильные:

И еще несколько советов по функции ВПР в Excel:

1. При вводе формулы используете точку с запятой, а не запятые.
2. Если протягиваете формулу вправо, не забывайте закреплять ссылку в начале формулы, чтобы формула имела вид: =ВПР($В5;$B$12:$С$14;2;ложь). Для этого наведите курсор на В5 (прямо в формуле) и трижды нажмите клавишу F4 на клавиатуре.
3. Не используйте аргумент “ИСТИНА” в ВПР, если работаете с текстом. Это приблизительный поиск, в котором будет много ошибок.

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

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

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

В этом сравнительно небольшом руководстве вы найдете простой способ, как обойти такие ошибки, как #Н/Д, #ИМЯ, #ЗНАЧ!, которые часто появляются при работе с этой функцией, а также ознакомитесь с самыми частыми ситуациями, когда эти ошибки возникают.

Несколько причин, по которым возникает ошибка #Н/Д

В формуле =ВПР эта ошибка расшифровывается, как «Нет данных». Простыми словами, у электронной таблицы не получается отыскать значение, которое необходимо пользователю. Есть множество причин, по которым эта проблема может появляться.

Неправильный ввод искомого значения

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

Если ищется приближенное соответствие

Если пользователь применяет range_lookup (то есть, интервальный просмотр) в качестве аргумента функции, в конечном итоге возможно появление ошибки #Н/Д. Это может случиться при возникновении одного из следующих условий:

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

При поиске точного соответствия введенному запросу

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

На данный момент столбец не является крайним левым

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

Преодоление этой трудности заключается в следующем: если по какой-то причине не получается переместить столбец влево, необходимо использовать сразу две функции Excel: ИНДЕКС(), ПОИСКПОЗ(). 

Числовые значения отформатированы, как текстовые

Это также часто встречаемый источник проблем с формулой ВПР(). Нередко пользователь может не замечать, что числовые значения отформатированы как текстовые. Часто такая проблема может встретиться, если информация копируется из других источников.

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

Еще одна причина ошибки – формат может быть обозначен, как «Общий». Определить это можно по расположению значения внутри ячейки. Если они выравниваются по левому краю, это означает, что формат выбран как «Общий».

Чтобы исправить эту проблему, достаточно просто нажать на параметр «Конвертировать в число» в контекстном меню.

Если эта ошибка вызвана несколькими ячейками с числовыми значениями, их необходимо выделить, после чего нужно кликнуть по соответствующей области правой кнопкой мыши. В ответ на это действие появится контекстное меню, в котором нужно выбрать опцию «Формат ячеек», далее нужно будет нажать на «Число» и выбрать числовой формат. Последнее действие – нажатие кнопки «ОК».

Если стоит пробел в начале или в конце содержимого

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

Решение номер 1: Если пробелы находятся в таблице, к которой применяется функция ВПР

Если в основной таблице обнаружены пробелы, можно применить функцию СЖПРОБЕЛЫ в аргументе «Искомое значение». Значительно проще это продемонстрировать на примере.

=ВПР(СЖПРОБЕЛЫ($F2),$A$2:$C$10,3,FALSE)

Решение номер 2: Если лишние пробелы находятся в столбце или таблице поиска

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

В результате получится формула массива, для правильного ввода которой нужно нажать на комбинацию клавиш Ctrl+Shift+Enter.

В качестве альтернативного способа решения этой проблемы можно воспользоваться дополнением «Trim Spaces for Excel», которое позволяет убрать ненужные пробелы в формулах как в главной таблице, так и в таблице поиска. Это бесплатный инструмент, который можно скачать по .

Ошибка #ЗНАЧ! в формуле ВПР

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

Причина 1: значение, которое ищется, содержит больше 255 знаков

Это еще одно ограничение этой функции. Нельзя ее применять к ячейкам, содержимое которых длиннее 255 знаков. Если этот предел превышается, в результате может появиться сообщение #ЗНАЧ!.

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

=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))

Причина 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 можно оперативно изменять тип адреса ячейки.

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

Если нет необходимости демонстрировать пользователям код ошибки (например, #Н/Д), можно использовать ВПР() совместно с функцией ЕСЛИОШИБКА() в Excel последних версий вплоть до 2007. Также можно использовать две функции ЕСЛИ()+ЕОШИБКА(), если версия более старая.

Использование функции ЕСЛИОШИБКА

Для этой формулы характерен простой синтаксис:

ЕСЛИОШИБКА(значение; значение_если_ошибка)

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

Вот пример использования функции на практике:

=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);»»)

В этом случае будет возвращаться пустая ячейка, если обнаруживается ошибка.

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

Использование функции ЕОШИБКА

Описанная выше функция впервые появилась в Excel 2007 версии, поэтому, если используются более старые версии программы (например, если компьютер имеет небольшую производительность, но на нем нужно обрабатывать большие объемы данных), нужно использовать такую формулу:

=ЕСЛИ(ЕОШИБКА(ВПР формула);»Ваше сообщение при ошибке»;ВПР формула)

На практике формула будет выглядеть следующим образом:

=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));»»;ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))

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