Почему не работает впр в excel остается формула
Функция ВПР в Экселе – это то, что позволит вам экономить десятки часов рабочего времени. При помощи функции ВПР вы ускоритесь буквально в разы и будете вспоминать с ужасом о сравнении таблиц вручную.
Так было со мной. На моей первой официальной работе, мне приходилось раз в несколько дней сравнивать два списка. Списки были относительно небольшие, около 100 строк каждый. Но сравнивал я их при помощи ручки и линейки. Это было мучение, более того это было неэффективное мучение, которое каждый раз, занимало у меня (по меньшей мере) пол рабочего дня.
Продолжался этот мазохизм ровно до тех пор, пока однажды коммерческий директор не увидел, как я безжалостно орудую линейкой над двумя листиками.
Он очень удивленно поинтересовался чем это я занимаюсь. Потом, весьма выдержанно сел за мой компьютер, открыл эти два файла и за несколько минут сравнил их при помощи функции ВПР в Экселе.
Сказать, что я был удивлён – это значит ничего не сказать. Я лицезрел настоящее чудо.
Это была потрясающая демонстрации силы автоматизации.
Функция ВПР в Экселе одинаково нужна и маркетологом, и логистам, и закупщикам – всем тем, кто работает с таблицами данных, это просто Must Have.
Функция ВПР в Экселе – быстрый перенос данных
Самое простое применение функция ВПР это быстрый перенос данных из одной таблицы в другую.
Например, у вас есть большой прайс на 500 позиций и запрос от покупателя, скажем на 50 позиций (в реальности и прайс и запрос могут быть гораздо больше, но принцип от этого не меняется).
Вам нужно быстро найти цены на эти 50 позиций. Разумеется, можно отдельно искать каждую позицию в большом прайсе и потратить на это 30 – 60 минут, а можно сделать это менее чем за минуту при помощи функции ВПР.
Итак, у нас в прайсе 500 позиций. Позиции обозначаются следующим образом, буквами обозначается вид позиции, а цифрами модификация.
Например, «Стул_1» и «Стул_21» это два совершенно разных стула.
Цены в прайсе указаны для примера и вряд ли имеют отношение к реальным ценам.
В ООО «ЫкэА» пришел запрос от «Петровича».
Петрович человек простой, любит всё делать быстро, но не очень чётко. Поэтому его запросы отличаются особым сумбуром в позициях.
Однако это нас не страшит, во-первых, у нас есть ВПР, во-вторых мы и не такое видали.
Вот собственно и сам запрос:
Петрович требует, чтобы мы очень быстро проставили цены в его запросе. Ждать он намерен максимум 5 минут. Ведь другие поставщики уже завалили его предложениями.
Нам не хочется терять такого клиента и мы практически мгновенно открываем прайс:
Получается у нас должно быть открыто два файла (две книги в Эксель). Запрос от Петровича и Прайс.
Это как раз то что необходимо, осталось только перенести цены из прайса в запрос.
Для этого перейдем в таблицу запроса и в первой ячейке столбца «Цены» (D4) введем «=впр» и два раза кликнем на значок функции:
Сразу же после этого, в строке формулы нужно поставить курсор внутри надписи ВПР и нажать Fx, перед вами появится окно с аргументами функции ВПР:
В аргументах функции вы говорите Экселю что и где нужно искать:
Искомое значение — это значение (в данном случае наименование), цену которого вы хотите найти в прайсе. Соответственно кликайте на первую ячейку столбца «Наименование».
Далее, сразу переходите в «Прайс»:
Теперь в аргументах функции заполните следующие поля:
Таблица — выделяете столбцы, которые содержат искомые наименования и цены, таким образом, чтобы наименования были крайним левым столбцом.
Так работает функция ВПР — ищет искомые значения в крайнем левом столбце (для ВПР это столбец №1). Когда ВПР находит искомое значение он начинает смотреть правее, в тот столбец, который вы указали в «Номере столбца».
Там вы должны указать столбец, содержащий необходимые для переноса данные. В нашем случае это цены и в нашем случае это столбец под номером два, относительно той таблицы, которую вы указали в аргументах.
Интервальный просмотр — ставьте 0. Ноль обозначает точное соответствие.
После заполнения аргументов функции нажимайте «Ок» и если всё сделано верно, то в столбце «Цена» (файл «Запрос от Петровича»), появится цена.
Вам нужно протянуть цены на оставшиеся ячейки:
Коллеги, вот и всё, вы овладели функцией ВПР.
Очень важное замечание!
Обратите внимание на то, что сейчас мы работали в двух разных файлах (книгах).
Когда работа идёт в двух разных книгах, Эксель автоматически закрепляет таблицу в функции ВПР:
Делает это он при помощи значка $, который проставляет перед столбцами и строками таблицы.
Это позволяет не съезжать формуле когда вы протягиваете её вниз. Это очень актуально когда вы работаете в рамках одного листа или одной книги (в этом случае Эксель автоматически Не закрепляет ячейки).
Давайте посмотрим что получиться если протянуть формулу «без закрепления»:
Посему, если не хотите чтобы от вас уезжали, закрепляйте диапазон.
Очень важное замечание №2
Как вы заметили, формулы ссылаются на определённые ячейки, другими словами между формулами и исходными данными есть связь. Стоит изменить исходные данные и значения в формулах сразу изменятся.
Особенно остро это чувствуется в ВПР. Если вы вдруг забудетесь и в исходной табличке добавите лишний столбец в «неположенном месте», то в формула ВПР выдаст совсем неожиданные значения.
Поэтому, если вам не нужна связь между таблицами, рекомендую формулы превратить в данные.
Для этого нужно выделить столбец с формулами, нажать Ctrl+C и в левом верхнем углу выбрать «Вставить» — «Вставить значения».
Для тех кто не любит изучать картинки, я записал небольшое видео в котором показываю всё то, что мы проговорили выше (кроме вставки значений):
Видео — «Быстрый перенос данных с помощью функции ВПР в Экселе»
Перенос данных при помощи ВПР, можно использовать не только для того, чтобы быстро получать данные из одной таблицы в другой, но и для того, чтобы сравнивать две таблицы.
Это очень актуально для тех кто работает в закупках и отправляет заказы поставщику.
Обычно происходит следующая ситуация. Вы отправляете заказ поставщику, через некоторое время получаете ответ в виде счёта и сверяете заказ с счётом.
Всё ли есть в счёте, в нужном ли количестве, по правильным ли ценам и т.д.
Функция ВПР в Экселе – сравнение двух таблиц
Итак, у вас есть «Заказ поставщику» (1) и ответ поставщика в виде «Счёта на оплату» (2).
Для удобства восприятия я разместил их на одном листе:
Ваша задача сверить количество позиций и их цены.
Для начала проверим все ли позиции и по правильной ли цене указал в счёте поставщик.
Для этого нужно из Счёта перетянуть данные в Заказ при помощи функции ВПР.
Перед «перетяжкой», в таблицу «Заказ поставщику» нужно добавить два «сравнительных» столбца:
После добавления столбцов, нужно перетянуть соответствующие данные при помощи ВПР:
Обратите внимание, я закрепил диапазоны ячеек.
Теперь когда данные перенесены, нужно их сравнить, для это необходимо добавить еще два столбца (Разница 1 и Разница 2):
В столбце «Разница 1» нужно вычесть от исходного количества (D4) количество в счёте (E4).
В столбце «Разница 2» нужно вычесть от исходной цены (G4) цену в счёте (H4).
Таким образом мы сможем увидеть разницу и в количестве и в цене.
Если значение «0», то значит всё хорошо и данные одинаковые.
Если значение плюсовое (например «+3»), то это значит что в счёте не хватает 3 штук.
Если значение отрицательное, это значит, что нам пытаются «впихнуть» лишнее.
Тоже самое и с ценами, если значения в плюсе, значит нам сделали скидку, а если значения в минусе — значит нам пытаются продать по завышенной цене.
Таким образом нужно просканировать всю таблицу и выяснить о причинах расхождения у поставщика:
Друзья, вот так мы проверили насколько соответствует Заказ, полученному Счёту и казалось бы что это всё что необходимо для счастливой жизни. Однако это не совсем так.
Нужно еще проверить соответствие Счёта, отправленному заказу, на предмет лишних позиций.
Вдруг хитрый поставщик, среди сотни позиций решил нам скрытно что-то «допродать».
Для этого в «Счёт на оплату» нужно добавить столбец «Кол/во в заказе» и «отвепээрить» туда значения из столбца «Количество» Заказа поставщику.
Теперь всё тоже самое продемонстрирую в небольшом видео.
Видео — «Сравнение двух таблиц с помощью функции ВПР в Экселе»
Эпилог
Коллеги, поздравляю, с этого момента ваша работа с данными значительно упростится и ускорится, ведь теперь вы «почтигуру» по применению функции ВПР в Экселе.
Вам нужно точное совпадение
Последний аргумент функции ВПР, известный как range_lookup (интервальный_просмотр), спрашивает, какое совпадение Вы хотите получить – приблизительное или точное.
В большинстве случаев люди ищут конкретный продукт, заказ, сотрудника или клиента, и потому хотят точное совпадение. Если производится поиск уникального значения, то аргументом range_lookup (интервальный_просмотр) должно быть FALSE (ЛОЖЬ).
Этот аргумент не обязателен, но если его не указать, то будет использовано значение TRUE (ИСТИНА). В таком случае для правильной работы функции необходимо, чтобы данные были отсортированы в порядке возрастания.
На рисунке ниже показана функция ВПР с пропущенным аргументом range_lookup (интервальный_просмотр), которая возвращает ошибочный результат.
Решение
Если Вы ищите уникальное значение, задайте последний аргумент равным FALSE (ЛОЖЬ). Функция ВПР в примере выше должна выглядеть так:
Зафиксируйте ссылки на таблицу
Возможно, Вы захотите использовать сразу несколько функций ВПР, чтобы извлечь большее количество информации. Если Вы собираетесь скопировать функцию ВПР в несколько ячеек, то необходимо зафиксировать часть аргументов.
На рисунке ниже показан пример функции ВПР, введенной некорректно. Для аргументов lookup_value (искомое_значение) и table_array (таблица) введены неправильные диапазоны ячеек.
Решение
Аргумент table_array (таблица) – это таблица, которую ВПР использует для поиска и извлечения информации. Чтобы корректно скопировать функцию ВПР, в аргументе table_array (таблица) должна быть абсолютная ссылка на диапазон ячеек.
Кликните по адресу ссылки внутри формулы и нажмите F4 на клавиатуре, чтобы превратить относительную ссылку в абсолютную. Формула должна быть записана так:
В этом примере ссылки в аргументах lookup_value (искомое_значение) и table_array (таблица) сделаны абсолютными. Иногда достаточно зафиксировать только аргумент table_array (таблица).
Вставлен столбец
Аргумент col_index_num (номер_столбца) используется функцией ВПР, чтобы указать, какую информацию необходимо извлечь из записи.
В связи с тем, что аргумент введен как числовой индекс, он не очень надёжен. Если в таблицу вставить новый столбец, функция ВПР может перестать работать. Рисунок ниже показывает именно такой сценарий.
Столбец Quantity (Количество) был 3-м по счету, но после добавления нового столбца он стал 4-м. Однако функция ВПР автоматически не обновилась.
Решение 1
Одним из решений будет защитить таблицу, чтобы пользователи не могли вставлять новые столбцы. В случае, когда пользователям потребуется такая возможность, решение станет не жизнеспособным.
Решение 2
Другой вариант – вставить функцию MATCH (ПОИСКПОЗ) в аргумент col_index_num (номер_столбца) функции ВПР.
Функция ПОИСКПОЗ может быть использована для того, чтобы найти и возвратить номер требуемого столбца. Это сделает аргумент col_index_num (номер_столбца) динамичным, т.е. можно будет вставлять новые столбцы в таблицу, не влияя на работу функции ВПР.
Формула, показанная ниже, может быть использована в этом примере, чтобы решить проблему, описанную выше.
Таблица стала больше
По мере добавления новых строк в таблицу, функции ВПР может потребоваться корректировка для гарантии, что новые строки таблицы охвачены формулой. На рисунке ниже показана ситуация, когда функция ВПР просматривает не всю таблицу в поисках нужного фрукта.
Решение
Форматируйте диапазон ячеек как таблицу (Excel 2007+) или как именованный диапазон. Такие приёмы дадут гарантию, что ВПР всегда будет обрабатывать всю таблицу.
Чтобы форматировать диапазон как таблицу, выделите диапазон ячеек, который собираетесь использовать для аргумента table_array (таблица). На Ленте меню нажмите Home > Format as Table (Главная > Форматировать как таблицу) и выберите стиль из галереи. Откройте вкладку Table Tools > Design (Работа с таблицами > Конструктор) и в соответствующем поле измените имя таблицы.
В формуле на рисунке ниже использовано имя таблицы FruitList.
ВПР не может смотреть влево
Функция ВПР имеет такое ограничение: она не может извлекать информацию из колонок, находящихся слева от первой. Она ищет искомое значение в крайней левой колонке заданной таблицы и возвращает информацию, находящуюся правее.
Решение
Решение этой проблемы – не использовать ВПР вовсе. Используйте комбинацию функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ), которая стала привычной альтернативой для ВПР. Это намного более гибкое решение
Пример, приведённый ниже, был использован для извлечения информации из колонки слева от той, по которой производится поиск:
Данные в таблице дублируются
Функция ВПР может извлечь только одну запись. Она возвратит первую найденную запись, соответствующую введённому Вами условию поиска.
Если таблица содержит повторяющиеся значения, функция ВПР не справится с такой задачей правильно.
Решение 1
Нужны ли Вам повторяющиеся данные в списке? Если нет – удалите их. Это можно сделать быстро при помощи кнопки Removes Duplicates (Удалить дубликаты) на вкладке Data (Данные).
Решение 2
Решили оставить дубликаты? Хорошо! В таком случае, Вам нужна не функция ВПР. Для таких случаев отлично подойдёт сводная таблица, позволяющая выбрать значение и посмотреть результаты.
Таблица ниже – это список заказов. Допустим, Вы хотите найти все заказы определённого фрукта.
Сводная таблица позволяет выбрать значение из столбца ID в фильтре, которое соответствует определенному фрукту, и получить список всех связанных заказов. В нашем примере выбрано значение ID равное 23 (Бананы).
ВПР без забот
Эта статья показывает решения 6 наиболее распространённых причин сбоя в работе функции ВПР. Вооружившись этой информацией, Вы сможете насладиться более беззаботным будущим в компании замечательных функций Excel.
Не работает ВПР в Excel н д? Причина в том, что формула не может найти необходимое значение, к примеру, из-за отсутствия искомого параметра в файле. Убедитесь, что такой показатель имеется в первоначальных данных, проверьте тип значений, удалите лишние пробелы, используйте способы точного / ориентировочного совпадения, задействуйте правильные аргументы и т. д. Ниже рассмотрим, в чем могут быть причины, и как действовать для восстановления работоспособности Эксель.
Причины и пути решения в Excel
Существует много причин, почему не работает ВПР в Excel и появляется надпись Н / Д. В каждой из ситуаций необходимо индивидуально подходить к решению вопроса с учетом возникшей неисправности.
Наиболее эффективный метод
В ситуации, когда не работает функция ВПР в Excel, проверьте наличие элемента на листе или задействуйте в формуле функцию обработки ошибок, к примеру, =ЕСЛИОШИБКА(ФОРМУЛА();0). В таком случае при появлении сбоев в расчете показывается ноль, а в ином случае — результат формулы. Можно дополнить запись “”, чтобы ничего не показывалось, или внести в скобки какую-либо запись.
Ошибка в типе параметров
Характерная причина, почему не работает ВПР в Excel — нахождение исходных / искомых данных к различным типам. К примеру, если вы используете ВПР в виде числа, а исходные данные сохраняются в качестве текста. Для решения вопроса убедитесь, что типы информации идентично. Для проверки формата сделайте следующее:
- Выберите ячейку (одну или несколько).
- Жмите правой кнопкой мышки.
- Выберите формат ячеек, а дальше Число.
Для принудительного внесения изменений нужно изменить формат для всего столбца. Для начала примените требуемое форматирование, а после выберите «Данные», «Текст по столбцам» и «Готово». После этого проверьте, появляется Н Д или нет.
Лишние пробелы
Распространенная причина, почему не работает формула ВПР в Excel, состоит в наличии пробелов. Для их удаления используйте функцию СЖПРОБЕЛЫ.
Ошибки метода поиска совпадения
Следующее объяснение, почему не срабатывает ВПР в Excel и возвращается Н Д — ошибки в применении метода совпадения. По умолчанию у опции ВПР имеется аргумент «интервальный просмотр», который дает команду на поиск точного совпадения даже при отсутствии сортировки данных в таблице.
Для поиска точного совпадения введите для аргумента «интервальный_просмотр» показатель ЛОЖЬ.
При этом учтите, что ИСТИНА, которое дает возможность поиска приблизительного параметра, может вернуть ошибку Н / Д. При использовании опции ПОИСКПОЗ попробуйте поменять параметр аргумента «тип_сопоставления» для указания порядке сортировки таблицы.
Не соответствие числа строк / столбцов заданному диапазону
В ситуации, когда не работает ВПР в Excel и вылетает ошибка Н / Д, сделайте дополнительную проверку. Вам нужно убедиться, что диапазон, в отношении которого ссылается формула, правильный. Как вариант, можно ввести формулу массива в меньшее / большее количество ячеек с учетом ссылки на диапазон.
В ячейке введена надпись Н / Д или Н Д
Другие ошибки
Дополнительно стоит выделить и ряд других ситуаций, когда ВПР в Excel по какой-то причине не работает:
- В используемой формуле нет одного или более аргументов. Для исправления проблемы введите все необходимые документы и проверьте, работает опция или нет. Для контроля можно использовать Visual Basic.
- Пользовательская опция недоступна. Для исправления проблемы убедитесь, что документ Excel с пользовательской функцией открыт, а опция работает корректно.
- Макрос имеет функцию, которая возвращает Н Д. Если ВПР не работает по этой причине, для исправления ошибки убедитесь в правдивости аргументов и их нахождении в нужных местах.
- Изменение защищенного файл с опцией ЯЧЕЙКА. Для исправления ситуации, когда ВПР в Excel не работает, жмите на комбинацию Ctrl+Alt+F9.
- Столбец не является первым слева дли поискового диапазона. Для решения проблемы нужно ввести соответствующий параметр и проверить, появляется ли Н Д. Как вариант, можно использовать функции ИНДЕКС и ПОИСКПОК в качестве гибкой альтернативы для ВПР.
- Неправильное форматирование числа. Бывают ситуации, когда цифры указаны в текстовом формате. Это часто происходит при импортировании сведений из внешней базы данных или при вводе апострофа перед числом для сохранения нуля в начале. Для решения проблемы жмите по ошибке и укажите Convert to Number. При появлении Н Д для многих чисел выделите их и жмите правой кнопкой мышки, а после выберите Format Cells и вкладку Число и Числовой.
Выше рассмотренные основные причины, почему не работает ВПР в Excel, а также описаны базовые шаги для устранения ошибки Н Д. Всегда начинайте с проверки правильности ввода параметра, а после переходите к другим вариантам.
Что за функция
В завершение кратко рассмотрим, что это за опция ВПР в Excel, и как она работает. Простыми словами, это опция, позволяющая переставлять данные из одной таблицы в соответствующие параметры другой. Английское название опции звучит как VLOOKUP. Это очень полезная опция, позволяющая сэкономить время и одновременно обработать большое количество параметров.
К примеру, в вас есть две таблицы. Первая — цены и названиями, вторая — заказ на покупку продукции. Осуществлять поиск в первом документе и пытаться вписать цену в заказ трудно. Необходимо, чтобы работа проходила автоматически. Для этого достаточно найти нужное значение в 1-м столбце и вернуть его содержимое из столбца той же строки, где находится название.
В комментариях расскажите, приходилось ли вам пользовался опцией ВПР, и случалась ли ситуация, когда она не работает в Excel, и появляется ошибка Н Д. Отдельно поделитесь, какие варианты решения вопроса можно использовать.
Как работает ВПР в Excel читайте в посте “Функция ВПР – чудо экономии времени”. Здесь же вы узнаете, что делать, если ВПР не работает. Так бывает. Скорее всего вы допустили ошибку. Есть четыре наиболее распространенных варианта ошибок:
А теперь разберем каждый вариант подробнее.
- Вы неправильно выделили диапазон поиска – начали не с того столбца:
Все будет в порядке, как только вы исправите ошибку:
- Не совпадает текст, по которому ВПР ищет данные.
Единственное исключение – допускаются разные регистры. Например, “Компьютер 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 комментариев к “ ВПР не работает ”
Спасибо за комментарий! Да, конечно, это все совершенно верно для ВПР с аргументом ИСТИНА (или без него). Но поскольку статья является фактически продолжением статьи, в которой рассматривалась исключительно ВПР с аргументом ЛОЖЬ, то данная ошибка не приведена.
Читайте также: