ВПР - моя любимая функция в Excel. Если вы умеете использовать ВПР, вы можете решить многие проблемы в Excel. Но есть вещи, которые могут сбить с толку ВПР. В этой теме рассказывается о некоторых из них.
Но сначала основы ВПР на простом английском.
Данные в A: C поступили из ИТ-отдела. Вы запросили продажи по позициям и дате. Они дали вам номер позиции. Вам нужно описание товара. Вместо того чтобы ждать, пока ИТ-отдел перезапустит данные, вы найдете таблицу, показанную в столбце F: G.
Вы хотите, чтобы функция VLOOKUP находила элемент в A2 при поиске в первом столбце таблицы в $ F $ 3: $ G $ 30. Когда функция VLOOKUP находит совпадение в F7, вы хотите, чтобы функция VLOOKUP возвращала описание, найденное во втором столбце таблицы. Каждая ВПР, которая ищет точное совпадение, должна оканчиваться на Ложь (или ноль, что эквивалентно Ложи). Приведенная ниже формула настроена правильно.
Обратите внимание, что вы используете F4, чтобы добавить четыре знака доллара к адресу таблицы поиска. Когда вы копируете формулу в столбец D, вам нужно, чтобы адрес таблицы поиска оставался постоянным. Есть две распространенные альтернативы: вы можете указать столбцы F: G целиком в качестве таблицы поиска. Или вы можете назвать F3: G30 таким именем, как ItemTable. Если вы используете =VLOOKUP(A2,ItemTable,2,False), именованный диапазон действует как абсолютная ссылка.
Каждый раз, когда вы выполняете несколько операций ВПР, вам необходимо отсортировать столбец ВПР. Сортировка ZA, и любые ошибки # N / A будут наверху. В данном случае он есть. Элемент BG33-9 отсутствует в таблице поиска. Может это опечатка. Может, это новенькая вещь. Если он новый, вставьте новую строку в любое место в середине таблицы поиска и добавьте новый элемент.
Наличие нескольких ошибок # Н / Д - это вполне нормально. Но на рисунке ниже точно такая же формула не возвращает ничего, кроме # N / A. Когда это произойдет, посмотрите, сможете ли вы решить первую ВПР. Вы ищете BG33-8, найденный в A2. Начните перемещаться по первому столбцу таблицы поиска. Как видите, значение соответствия явно находится в F10. Почему вы это видите, а Excel не видит?
Перейдите в каждую ячейку и нажмите клавишу F2. На рисунке ниже показана F10. Обратите внимание, что курсор вставки появляется сразу после 8.
На следующем рисунке показана ячейка A2 в режиме редактирования. Курсор вставки находится на расстоянии пары пробелов от 8. Это признак того, что в какой-то момент эти данные были сохранены в старом наборе данных COBOL. Вернемся к COBOL, если поле Item было определено как 10 символов, а вы набрали только 6 символов, COBOL добавит в него 4 дополнительных пробела.
Решение? Вместо того, чтобы искать A2, найдите TRIM (A2).
Функция TRIM () удаляет начальные и конечные пробелы. Если у вас есть несколько пробелов между словами, TRIM преобразует их в один пробел. На рисунке ниже перед и после обоих имен в A1 есть пробелы. =TRIM(A1)удаляет все, кроме одного пробела в A3.
Кстати, а что, если проблема заключалась в конечных пробелах в столбце F вместо столбца A? Добавьте столбец функций TRIM () в E, указав на столбец F. Скопируйте их и вставьте как значения в F, чтобы поиск снова начал работать.
Здесь показана другая очень распространенная причина, по которой ВПР не работает. Столбец F содержит действительные числа. Столбец A содержит текст, похожий на числа.
Выберите весь столбец A. Нажмите Alt + D, E, F. При этом выполняется стандартная операция «Текст в столбцы» и все текстовые числа преобразуются в действительные числа. Поиск снова начинает работать.
Если вы хотите, чтобы функция VLOOKUP работала без изменения данных, вы можете использовать ее =VLOOKUP(1*A2,… )для обработки чисел, хранящихся в виде текста, или =VLOOKUP(A2&"",… )когда ваша таблица поиска имеет текстовые числа.
ВПР предложили Род Апфельбек, Патти Хан, Джон Хеннинг, @ExcelKOS и @tomatecaolho. Всем спасибо.
