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

Опубликовано: 15.05.2024

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

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

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

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

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

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

Решение

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

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

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

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

Решение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рис. 2.1. BG33-9 – это новый товар, которого нет в таблице подстановки

Такие ситуации встречаются, когда искомое значение не найдено в первом столбце Таблицы. В нашем примере товар BG33-9 не найден в таблице постановки (в диапазоне F2:H29). Добавьте элемент BG33-9 в таблицу подстановки (рис. 2.2). Исправьте формулу ВПР, расширив диапазон таблицы =ВПР(A2;$F$2:$H$30;3;ЛОЖЬ).

Рис. 2.2. Новый товар добавлен в таблицу подстановки

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

Вставьте новые ячейки в любом месте в середине таблицы подстановки. Например, выделите ячейки F28:H28, кликните правой кнопкой мыши, выберите опцию Вставить… и в открывшемся окне укажите Ячейки со сдвигом вниз.
В качестве таблицы подстановки выберите не диапазон ячеек ($F$2:$H$29), а столбцы целиком – F:H. Теперь, вы можете добавлять элементы внизу таблицы подстановки без переписывания формул. Excel достаточно сообразителен, чтобы использовать только непустые ячейки при расчетах.
Выберите любую ячейку в таблице подстановки и нажмите сочетание клавиш Ctrl+Т (Т английское). Это превращает диапазон в таблицу. При вводе новых значений в пустые строки ниже таблицы, она автоматически расширится. Теперь ваша ВПР будет ссылаться не на диапазон, а на ТАБЛИЦУ (рис. 2.3).

Рис. 2.3. Превратите диапазон таблицы подстановки в инструмент Excel ТАБЛИЦА; это позволит автоматически расширять его при добавлении новых строк

Существует несколько способов решить эту проблему. Если вы используете Excel 2007 или более позднюю версию, воспользуйтесь функциями ЕСЛИОШИБКА() или ЕСНД(). Если у вас более ранняя версия Excel (или требуется совместимость с более ранней версией) подойдет связка функций ЕСЛИ() и ЕНД().

Если исходная формула =ВПР(A67;$F$2:$H$29;3;ЛОЖЬ), то замените ее на =ЕСЛИОШИБКА(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ);0), или на =ЕСНД(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ);0).

Функция ЕСЛИОШИБКА() появилась в Excel 2007, что стало значительным улучшение по сравнению с предыдущими методами. С ЕСЛИОШИБКА() Excel сначала вычисляет формулу в первом аргументе функции. В 99% случаев возвращается правильный ответ, и работа функции останавливается. В тех редких случаях, когда возвращается ошибка, Excel переходит к обработке второго аргумента функции ЕСЛИОШИБКА(). В нашем примере, просто возвращает ноль.

Если вы предпочитаете в случае ошибки получить пустую ячейку, используйте формулу =ЕСЛИОШИБКА(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ); " " ), но помните, что ячейка не будет по-настоящему пустой. Она будет содержать пустую текстовую строку, что, например, выдаст ошибку при попытке суммировать ее с другими числовыми значениями.

В более ранних версиях Excel 2007 используйте формулу: =ЕСЛИ(ЕНД(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ));0;ВПР(A67;$F$2:$H$29;3;ЛОЖЬ)).

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

Рис. 2.5. Ни одна из ВПР не работает, хотя совпадения наблюдаются

Воспользуйтесь функцией СЖПРОБЕЛЫ() для удаления начальных и конечных пробелов. Если есть пробелы между словами, функция изменит несколько пробелов подряд на один. Таким образом, формулу =ВПР(A3;$F$3:$G$30;2;ЛОЖЬ) замените на =ВПР(СЖПРОБЕЛЫ(A3);$F$3:$G$30;2;ЛОЖЬ).

Если лишние пробелы отображаются в таблице подстановки, вы можете воспользоваться формулой массива. Наберите с клавиатуры =ВПР(A3;СЖПРОБЕЛЫ($F$3:$G$30);2;ЛОЖЬ). Но не жмите Enter после редактирования. Вместо этого, нажмите одновременно Ctrl + Shift + Enter (подробнее см. Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel). Ваша формула будет выглядеть так <=ВПР(A2;СЖПРОБЕЛЫ($F$3:$G$30);2;ЛОЖЬ)>. Появятся фигурные скобки.

Подводный камень: формулы массива работают медленнее обычных формул. Это не проблема, если вы хотите произвести впечатление на коллег. Но в реальной жизни, лучше временно создать столбец, например, Н, в который ввести формулу =СЖПРОБЕЛЫ(F3). Затем скопировать его и вставить как значения в столбец F.

Числа и текст не распознаются как одинаковые. Проблема: часть значений ВПР работают корректно, а часть – возвращают ошибку (рис. 2.6).

Рис. 2.6. Часть значений ВПР возвращают ошибку

Вероятно, некоторые ячейки содержат числовые значения, а некоторые – числа, сохраненные как текст. На приведенном выше рисунке вы можете увидеть крошечный зеленый треугольник в левом верхнем углу ячеек В8, В11, В13. Если вы наведете курсор на ячейку, содержащую треугольник, слева появится символ с восклицательным знаком. Если раскрыть список, вы увидите, сообщение Число, сохраненное как текст (рис. 2.7).

Рис. 2.7. Сообщение: Число, сохраненное как текст

То, что числа хранятся в виде текста не обязательно плохо. Например, если это – модели продукта. Проблема в другом: формат данных в столбцах В и F – разный. Когда значение в B3 хранится в виде числа, а в F2 – в виде текста (см. рис. 2.6), ВПР не находит соответствия. Существуют функции, которые позволяют изменять формат данных. Однако, прежде чем их применить, нужно определить, в каком месте данные хранятся в виде чисел, а в каком – в виде текста. Для этого на свободном месте листа введите формулу =ЕЧИСЛО(В2) и =ЕЧИСЛО(F3). Значение ИСТИНА соответствует числу, ЛОЖЬ – тексту.

Если в столбце В хранится текст, а в столбце F – числа, используйте функцию ЗНАЧЕН, которая позволяет изменить текст на число. К сожалению, она выдаст ошибку, если наименование продукта буквенно-цифровое, например, 1120TS. Поэтому, нужно использовать конструкцию ЕСЛИОШИБКА вместе со ЗНАЧЕН (рис. 2.8).

Рис. 2.8. Преобразования текстовых значений в числовые

Если ситуация обратная, то можно использовать функцию ТЕСКТ. Буквенно-цифровые значения при этом не изменятся (рис. 2.9). Второй аргумент функции ТЕКСТ ( " 0 " ) задает формат числа, на основании которого произойдет конвертация в текст (подробнее см. Пользовательский формат числа в Excel).

Рис. 2.9. Преобразования числовых значений в текстовые

Что делать, если проблема не укладывается ни в один из шаблонов? Т.е., когда, и столбец B, и столбец F, содержат комбинацию чисел и текста (рис. 2.10).

Рис. 2.10. Произвольное сочетание числовых и текстовых форматов в столбцах В и F

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

Выделите ячейку В2.
Нажмите Ctrl+Shift+Стрелка вниз. Выделится весь столбец В с данными.
Наберите Alt+Ы, не отпуская Alt, продолжайте набирать П, и наконец Г (или кликните на кнопке Готово). Это позволит преобразовать текстовые значения во всем столбце в числа.
Повторите пункты 1–3 для столбца F.

Примечание: сочетание клавиш Alt+Ы+П+Г – это быстрый способ выбрать все настройки по умолчанию в Мастере распределения текста по столбцам (вкладка ДАННЫЕ, область Работа с данными). Использование Мастера позволяет преобразовать все данные обратно в общий формат, что, как правило, решает проблему.

Альтернативные стратегии. Существуют и иные способы преобразования текста в числа. Самый простой способ собрать все текстовые ячейки рядом – отсортировать данные по столбцу B по убыванию. Ячейки с текстом попадут в начало списка. Если текстовых ячеек немного, их можно обработать вручную.

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

Контроль дубликатов при использовании ВПР. Проблема: у меня есть первоначальная таблица, и данные о продажах за 19.06.14. Я использовал функцию ВПР, чтобы извлечь продажи из списка, и отразить их в первоначальной таблице. Затем я получил файл с продажами за 20.06.14. Я воспользовался функцией ПОИСКПОЗ и нашел одного нового клиента в списке продаж 20.06.14 – Sun Life Fincl.

Рис. 2.11. Действительно ли это новый клиент?

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

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

Если у вас нет полной уверенности, что клиенты в таблице подстановки уникальны, вы не должны использовать ВПР. Вместо этого используйте функцию СУММЕСЛИ. На рис. 2.13 использована следующая формула: =СУММЕСЛИ($A$2:$A$10;E2;$C$2:$C$10). Эта формула предписывает выполнять сканирование по столбцу Клиент (диапазон А2:А10), чтобы найти клиента, соответствующего значению в ячейке Е2. Каждый раз, когда в Excel находит соответствие Выручка из диапазона С2:С10 добавляется к значению в ячейке F2.

Рис. 2.13. Используйте СУММЕСЛИ вместо ВПР для таблиц подстановки, содержащих дубли

Чтобы выяснить, сколько знаков в ячейке A2, перейдите в любую пустую ячейку и введите =ДЛСТР(А2). ВПР не будет работать, если символов >255. В определенных обстоятельствах вы могли бы усечь содержимое ячейки с помощью формулы =ЛЕВСИМВ(A2;255). Но, иногда это не удастся, если ключи не уникальны при усечении до 255 символов.

Таблица подстановки имеет неверную ориентацию. Проблема: таблица подстановки расположена по горизонтали (рис. 2.14). Можно ли использовать ВПР?

Рис. 2.14. Таблица подстановки расположена по горизонтали

Стратегия: буква “В” в ВПР обозначает вертикальный просмотр. В Excel также есть функция ГПР для горизонтальных таблиц подстановки. Если вы хотите почудачить, то могли бы использовать формулу =ГПР(B3,$F$2:$Q$3,2,ЛОЖЬ).

Альтернативная стратегия: вы, скорее всего, поступите также, как и большинство других пользователей. Скопируете диапазон F2:В3, выделите ячейку F5, выполните специальную вставку, задав параметр Транспонировать. Это предаст таблице подстановки вертикальное положение. Затем вы введете обычную формулу с ВПР. Заметьте, что, начиная с версии 2010 в Excel для транспонирования используется специальный значок:

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

Синтаксис и описание функции ВПР в Excel

Итак, так как второе название этой статьи «Функция ВПР в Excel для чайников», начнем с того что узнаем, что же такое функция ВПР и что она делает? Функция ВПР на английском VLOOKUP, ищет указанное значение и возвращает соответствующее значение из другого столбца.

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

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

Функция ВПР доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

Синтаксис функции ВПР выглядит следующим образом:

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

искомое_значение – это значение для поиска.

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

Поиск числа : =ВПР(40; A2:B15; 2) - формула будет искать число 40.
Поиск текста : =ВПР(«яблоки»; A2:B15; 2) - формула будет искать текст «яблоки». Обратите внимание, что вы всегда включаете текстовые значения в «двойные кавычки».
Поиск значения из другой ячейки : =ВПР(C2; A2:B15; 2) - формула будет искать значение в ячейке C2.

таблица - это два или более столбца данных.

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

Итак, наша формула =ВПР(40; A2:B15; 2) будет искать «40» в ячейках от A2 до A15, потому что A - это первый столбец таблицы A2: B15.

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

Самый левый столбец в указанной таблице равен 1, второй столбец - 2, третий - 3 и т. д.

Итак, теперь вы можете прочитать всю формулу =ВПР(40; A2:B15; 2). Формула ищет «40» в ячейках от A2 до A15 и возвращает соответствующее значение из столбца B (потому что B является вторым столбцом в указанной таблице A2:B15).

4. интервальный_просмотр определяет, ищете ли вы точное соответствие (ЛОЖЬ) или приблизительное соответствие (ИСТИНА или опущено). Этот последний параметр является необязательным, но очень важным.

Функция ВПР в Excel примеры

Теперь давайте рассмотрим несколько примеров использования функции ВПР для реальных данных.

Функция ВПР на разных листах

На практике формулы ВПР редко используются для поиска данных на одном листе. Чаще всего вам придется искать и вытаскивать соответствующие данные с другого листа.

Чтобы использовать функцию ВПР с другого листа Excel, вы должны ввести имя рабочего листа и восклицательный знак в аргументе таблица перед диапазоном ячеек, например, =ВПР(40;Лист2!A2:B15;2). Формула указывает, что диапазон поиска A2:B15 находится в Лист2.

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

Формула, которую вы видите на изображении ниже, ищет текст в ячейке А2 (« Продукт 3 ») в столбце A (1-й столбец диапазона поиска A2:B9) на листе « Цены »:

Функция ВПР в Excel – Функция ВПР на разных листах

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

Как использовать именованный диапазон или таблицу в формулах ВПР

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

Чтобы создать именованный диапазон, просто выберите ячейки и введите любое имя в поле « Имя », слева от панели « Формула ».

Функция ВПР в Excel – Присвоение имени диапазону

Теперь вы можете написать следующую формулу ВПР, чтобы получить цену Продукта 1:

Функция ВПР в Excel – Пример функции ВПР с именем диапазона

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

Если вы преобразовали диапазон ячеек в полнофункциональную таблицу Excel (вкладка «Вставка» --> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, а Microsoft Excel автоматически добавит имена колонок или имя таблицы в формулу:

Функция ВПР в Excel – Пример функции ВПР с именем таблицы

Полная формула может выглядеть примерно так:

или даже =ВПР("Продукт 1";Таблица6;2).

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

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

Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:

Функция ВПР в Excel – Таблица исходных данных

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

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

Итак на листе « Цены » вставляем столбец и в ячейке А2 вводим следующую формулу:

При помощи этой формулы мы сцепляем значение столбца « Продукт » и « Тип ». Заполняем все ячейки.

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

Функция ВПР в Excel – Добавление вспомогательного столбца

Теперь в ячейке С2 на листе « Продажи » напишем следующую формулу ВПР:

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

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

Теперь разберем ошибки функции ВПР.

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

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

Неверное расположение столбца, по которому происходит поиск

Столбец таблицы, по которому происходит поиск ОБЯЗАТЕЛЬНО должен быть крайним левым.

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

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

Используйте абсолютные ссылки ($) для закрепления диапазона таблицы, чтобы при заполнении формула использовала один и тот же диапазон.
Или используйте именованный диапазон

Не удалось найти точное совпадение (если в интервальном просмотре выбран поиск точного значения (0)

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

Отсортируйте первый столбец таблицы по возрастанию наименований.

Данные содержат лишние пробелы, недопустимые кавычки или непечатаемые символы.

Используйте функции ПЕЧСИМВ или СЖПРОБЕЛЫ.

Значение номер столбца превышает число столбцов в таблице

Проверьте номер столбца, содержащий возвращаемое значение.

В формуле пропущены кавычки

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

=ВПР("Продукт 1"; Цены!$A$2:$B$8;2;0)

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

Пошаговые инструкции, как использовать функции, формулы и другие инструменты табличного редактора Microsoft Excel

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

Как вызвать функцию ВПР. Функция ВПР в Excel

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

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

Теперь перейдем непосредственно к вариантам применения функции ВПР.

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

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

Перед тем, как вызвать функцию ВПР, выбираем нужную нам ячейку, в которой будет находиться наша формула функции и соответственно значение, которое мы хотим увидеть. В нашем случае это ячейка G3. Эта ячейка находиться в столбце Цена, Таблица №2. Функция ВПР позволит взять из Таблицы №1 цену Конфеты А и вставить эту цену в столбец Цена, Таблицы №2, напротив Конфеты А.

Вызываем функцию ВПР, как описано выше.

Аргументы функции. Функция ВПР в Excel.

Искомое_значение.

Значение поиска, которое должно быть найдена в указанном нами диапазоне, в строке Таблица. В нашем примере мы указываем Конфеты Ж (ячейка Е3, Таблица №2). Так как это значение идет первое в столбце Название конфет, Таблица №2. (Это не принципиально, но удобно). Это значение, которое будет искать наша функция в Таблице №1.

Что бы выбрать нужную нам ячейку с значением, достаточно просто стать курсором в строку Искомое_значение, а потом клацнуть левой кнопкой мыши, по нужной ячейке в таблице ( В нашем примере ячейка Е3).

Таблица.

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

Как это сделать. Выбираем нужный нам диапазон. Таблица №1. Выбираем закладку Формулы, кнопка Задать имя. Нажимаем. Появляется диалоговое окно Создание имени. Пишем любое имя. Но нужно его запомнить. Например Конфеты. Нажимаем ОК.

В строке Таблица, вместо диапазона нужно будет ввести имя, которое мы присвоили – Конфеты

Номер_столбца.

Функция ВПР осуществляет поиск значение в самом левом столбце таблицы указанного диапазона поиска. Функция присваивает этому столбцу номер 1, по умолчанию. В нашем примере самый левый столбце, это Название конфет в Таблице №1. А в строке Номер_столбца, нам нужно указать, какой номер по порядку имеет столбец, из которого нужно перенести данные. В нашем примере это столбце Цена в Таблице №1. Он «второй» по порядку, если считать слева на право, от столбца Название конфет, Таблица №1. Поэтому в строку Номер_столбца мы пишем цифру 2. Если бы столбец Цена, Таблица №1 был бы расположен по порядку не на втором месте, а предположим на десятом, то мы соответственно указывали бы в строке Номер_столбца цифру 10.

Интервальный _просмотр.

В этой строке мы пишем цифру ноль «0». Это значит, что функция ВПР будет осуществлять поиск точных совпадений между значениями поиска (Искомое_значение) и значениями в крайнем левом столбце диапазона поиска (Таблица). В нашем примере поиск точных совпадений будет происходить между столбцом Название конфет, Таблица №1, и столбцом Название конфет в Таблице №2.

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

Вот как это выглядит все вместе.

Протягиваем формулу по всему столбцу Цена в Таблице №2. Все цены перенесены с Таблице №1 в Таблицу №2.

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

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

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

В диалоговом окне, Аргументы функции прописываем следующие данные:

Искомое_значение. Это значение ячейки из Таблицы №2, наличие которой мы проверяем в Таблице №1. В нашем примере, это ячейка F3 (Значение 9).

Таблица. В данном случае мы указываем не диапазон всей таблицы, а только диапазон конкретного столбца, который мы сравниваем. Можно выделять столбец в таблице. А можно выделять весь столбец листа. В том случае, если в нем больше нет других данных. Вместо диапазона можно указать заданное имя столбца (Задаем имя).

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

Интервальный _просмотр. Здесь ставим ноль «0», так как хотим, чтобы функция искала точные совпадения.

Можно проверить с точностью наоборот. И найти какие данные есть в Таблице №1 но нет в Таблице № 2.

Обратите внимание. Функция ВПР в Excel.

Функция ВПР осуществляет поиск значений (это значения, которые указаны в строке Искомое_значение) в первом (самом левом) столбец таблицы, диапазон которой указан в строке Таблица.

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

Если в диапазоне Таблица указан один, конкретный столбец, то функция ВПР проверяет только его. И данное правило не обязательно.

Можно осуществлять поиск на разных Листах. Алгоритм работы такой же. Формула функции будет выгладить вот так: =ВПР(E6;Лист1!$B$1:$C$11;2;0). В нашем примере формулы функция ВПР расположена на Листе 2, а поиск значения и перенос данных с диапазона поиска происходит на Листе 1. Вместо диапазона можно использовать Заданное имя. Например Конфеты. Тогда формула функции будет выглядеть вот так: =ВПР(E6;Конфеты;2;0).

Возможные ошибки.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

9 комментариев к “ ВПР не работает ”

Спасибо за комментарий! Да, конечно, это все совершенно верно для ВПР с аргументом ИСТИНА (или без него). Но поскольку статья является фактически продолжением статьи, в которой рассматривалась исключительно ВПР с аргументом ЛОЖЬ, то данная ошибка не приведена.

Читайте также: