ВПР в Excel - это мощное средство, но оно не сработает в определенных ситуациях. Сегодня мы расскажем, как устранить неполадки с ВПР.
ВПР - моя любимая функция в 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 начинает работать, но # N / A становится более заметным: забыл $ в таблице поиска
- Несколько # N / A: элементы отсутствуют в таблице
- Ни одна из функций ВПР не работает: проверьте наличие конечных пробелов
- Удалите конечные пробелы с помощью TRIM
- Цифры и числа, хранящиеся в виде текста
- Выберите оба столбца и используйте alt = "" + DEF
- В эпизоде есть шутка, которую и бухгалтеры, и айтишники находят смешной, но по разным причинам.
Стенограмма видео
Изучите Excel из подкаста, выпуск 2027 - Устранение неполадок ВПР!
Хорошо, подкастирую всю книгу, нажмите «i» в правом верхнем углу, чтобы перейти к списку воспроизведения!
ВПР - моя любимая функция во всем Excel, и я всегда рассказываю эту шутку на одном из своих семинаров, и это вызывает смех, независимо от того, являетесь ли вы ИТ-специалистом или нет. Я всегда говорю: «Послушайте, у нас есть этот набор данных здесь слева, и я могу посмотреть на эти данные и сказать, что данные пришли из ИТ-отдела, потому что это именно то, что я просил, но не совсем то, что мне нужно. Я спросил дату и количество товара, и они дали мне номер товара, дату и количество, но они не удосужились дать мне описание, верно? » И бухгалтеры всегда смеются над этим, потому что это происходит с ними постоянно, а айтишники такие: «Ну, я дал вам то, о чем вы просили, это не моя вина!» Итак, они оба думают, что это забавно по разным причинам, так что, знаете, а айтишник занят, он не может вернуться к переписыванию запроса,так что мы должны что-то делать, чтобы спасти это сами.
И вот эта маленькая таблица, которую я скопировал откуда-то еще на своем компьютере, простым английским языком, то, что делает ВПР, говорит: «Эй, у нас есть элемент номер W25-6». У нас есть таблица, я хочу пройтись по первому столбцу таблицы, пока не найду этот номер элемента, а затем верну что-нибудь из этой строки. Хорошо, в данном случае мне нужен второй столбец из этой строки. И затем в конце каждой ВПР мы должны поставить либо ЛОЖЬ, либо 0. Теперь, прямо здесь, после того, как я выберу диапазон, я собираюсь нажать клавишу F4, а затем мне нужен второй столбец, а затем ЛОЖЬ для точного матч. Никогда не выбирайте приблизительное совпадение, никогда, никогда! Это не примерное совпадение, это особенная вещь, не всегда работает. Если вы хотите повторно сообщить свои цифры Комиссии по ценным бумагам и биржам, не стесняйтесь использовать,TRUE или просто оставьте FALSE выключенным. Но каждая создаваемая вами ВПР должна заканчиваться на ЛОЖЬ или 0, 0 короче, чем ЛОЖЬ, вы должны поместить туда ЛОЖЬ, но 0 - то же самое, что ЛОЖЬ.
Хорошо, теперь устранение неполадок, во-первых, когда вы выполняете целую кучу ВПР, вполне нормально иметь пару # Н / Д, верно? И я всегда нахожу # N / A, выбирая Data, ZA, который выводит # N / As наверх, прямо там, BG33-9, либо это опечатка, либо это совершенно новый элемент, хорошо, и мы получили чтобы понять это. Итак, вот здесь, справа, у меня есть новые данные, я вырежу их, а затем Alt + IED, вставьте эти ячейки посередине, они не должны быть алфавитными, эту таблицу не нужно сортировать. Когда вы используете версию FALSE, таблицы нет - вы можете просто поместить ее куда угодно, я не поставил в конце, потому что мне не нужно было переписывать формулу, я просто хочу, чтобы формула Работа. Хорошо, пара # Н / Д, чрезвычайно, очень нормально, но посмотрите это.
Когда вы начинаете с ответов, которые работают, но затем # N / A начинают появляться немного чаще, а затем, в конечном итоге, они появляются полностью вниз, это верный признак того, что вы не заблокировали ссылку на таблицу. Итак, вот таблица движется, когда я копирую формулу вниз, вправо, и поэтому мне повезло, что я выбрал несколько из них, которые были в конце списка. Но в конце концов я дохожу до того, что он смотрит сюда в совершенно пустые ячейки, и, конечно же, ничего не обнаруживается. Хорошо, это первое, у вас есть пара, которая работает, несколько # N / A, еще пара, которые работают, а затем все # N / A остальное - верный признак того, что вы не поставили $ в.
Просто вернитесь, F2 для режима редактирования, выберите двоеточие, нажмите F4, чтобы вставить все $, дважды щелкните, чтобы сбить его, и все снова начнет работать, хорошо. Следующая, точно такая же формула, формула идеальна, видите ли, BG33-8, мы не получаем ничего из этого правильно. Хорошо, поэтому я иду посмотреть, BG33-8, эй, вот он, он прямо здесь, он красный, я должен был это увидеть! Я подхожу к этому справа, нажимаю F2, смотрю на мигающую точку вставки и вижу, что она сразу после восьмерки, вот так, а потом подхожу сюда и нажимаю F2, там есть некоторые конечные пробелы там. Это было очень, очень распространено во времена COBOL, они говорили: «Знаешь, смотри, мы дадим вам 10 пробелов для номера позиции, и если вы не введете все 10 пробелов, они заполнят пробелы. . » И это очень часто,и отличное решение здесь - избавиться от этих начальных и конечных пробелов с помощью функции TRIM. Вместо A2 используйте TRIM (A2), дважды щелкните, чтобы сбить его, хорошо, но BG33-9 все еще находится в другом столе.
Хорошо, просто чтобы вы понимали, как работает TRIM, я ввел кучу пробелов, Джон, кучу пробелов, Дурран и кучу пробелов, а затем я объединил * до и после, чтобы вы могли увидеть, как это выглядит . Когда я прошу TRIM (P4), мы избавляемся от всех начальных пробелов, от всех конечных пробелов, а затем несколько внутренних пробелов сокращаются до одного пробела. Хорошо, так что вы можете видеть, как бы визуализировать, что они избавляются от начальных и конечных пробелов, любые удвоенные пробелы в середине становятся таким единым пространством. Итак, TRIM, отличный, отличный инструмент в вашем арсенале, хорошо, вот еще один действительно распространенный.
Если это не конечные пробелы, то чаще всего я видел, где здесь у нас есть истинные числа, а здесь у нас числа, хранящиеся в виде текста. И VLOOKUP не увидит это как совпадение, хотя 4399, это число, это текст, не работает. Самый быстрый способ преобразовать столбец текста в числа, выбрать столбец, 3 буквы подряд, alt = "" DEF, и внезапно наши ВПР снова начинают работать, отличный, отличный совет примерно 1500 подкастов назад. Хорошо, это наиболее распространенные проблемы ВПР: либо вы забыли знак $, либо у вас есть конечные пробелы, либо у вас есть числа и числа, хранящиеся в виде текста. Все эти советы находятся в этой книге «MrExcel XL». Нажмите «i» в правом верхнем углу, и вы сможете купить книгу.
Хорошо, краткий обзор: ВПР решает многие проблемы, если ВПР начинает работать, но # Н / Д! становится более заметным, вы забыли вставить $ в таблицу поиска. Если есть несколько # N / A, это просто элементы, отсутствующие в таблице. Если ни одна из ВПР не работает, а это текст, проверьте наличие конечных пробелов, вы можете использовать функцию TRIM. Если у вас есть числа и числа, сохраненные в виде текста, выберите любой столбец, тот, который имеет текст, затем выполните alt = "" DEF для всех этих чисел обратно к числам.
Хорошо, эй, я хочу поблагодарить вас за то, что заглянули, увидимся в следующий раз на другом сетевом трансляции от!
Скачать файл
Загрузите образец файла здесь: Podcast2027.xlsx
