Почему может не работать формула впр

Почему функция ВПР (VLOOKUP) не работает в Google таблицах?

Рассматривая синтаксис функции ВПР (VLOOKUP), мы уже отмечали, что в случае, если поиск завершится неудачей, функция возвратит ошибку “#Н/Д” (#N/A). Давайте постараемся вместе попробовать ответить на вопрос: «Почему функция ВПР не работает?»

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

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

Почему не работает? Разбираем наиболее типичные ошибки при использовании функции ВПР в Google таблицах.

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

Итак, разберём наиболее распространённые ошибки.

Ошибка при вводе данных

В этом случае ничего найти не удастся и вы увидите ошибку #Н/Д. Самое простое решение здесь очень часто оказывается самым верным. Быть может, вы просто ошиблись при вводе данных в ячейку поиска. Это вполне может случиться, в особенности если вы вводите значение поиска не в ячейку, а прямо в формулу.

Проверьте, правильно ли введены данные.

Ошибка при вводе функции.

Если вы видите сообщение об ошибке #ИМЯ? (#NAME?), то это означает, что при вводе названия самой функции ВПР вы допустили ошибку – перепутали или добавили лишнюю букву.

Проверьте синтаксис написания функции, и всё бедет в порядке.

Неверные ссылки в функции ВПР

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

К примеру, мы ищем цену бананов.

Но в функции вы указали обычные (относительные) ссылки, забыв заменить их на абсолютные ($), и затем произвели какие-то действия с таблицей. К примеру, добавили столбец. В результате ваш диапазон поиска автоматически изменился и стал шире на 1 столбец.

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

Важно! При изменении ссылок сообщение об ошибке часто не появляется. Поэтому будьте внимательны в использовании относительных и абсолютных ссылок!

Неверно указан параметр “сортировка” (is_sorted).

Включен поиск до первого приблизительного совпадения в отсортированном диапазоне (параметр is_sorted = TRUE), но на самом деле данные не отсортированы.

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

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

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

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

Это важное ограничение функции ВПР нельзя забывать.

Несовпадение форматов данных.

Формат ячейки, откуда берется искомое значение наименования (например D3 в нашем случае), и формат ячеек первого столбца (A3:A21) из диапазона поиска отличаются (например, числовой и текстовый). Этот случай особенно часто встречается при использовании вместо текстовых наименований числовых кодов (номера счетов, артикулы, идентификаторы, даты и т.п.)

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

Как видите, с виду записи одинаковы, однако в ячейке D6 значение сохранено как число, а в ячейке A10 – как текст. Текст не может быть равен числу, поэтому функция возвращает ошибку.

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

Теперь функция ВПР нашей Google таблицы выглядит так:

Обычную ссылку на ячейку D6 мы заменяем функцией

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

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

Если же такая ошибка только одна, то можно просто исправить формат данных в ячейке. Нажмите Меню -> Формат-> Число-> Обычный текст. Значение в ячейке будет преобразовано в текст.

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

Лишние пробелы и непечатаемые знаки.

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

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

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

К примеру, если наименование состоит из 2 слов, то вполне возможно случайное появление лишнего пробела между этими словами.

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

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

Функция СЖПРОБЕЛЫ (TRIM) удаляет пробелы, а функция ПЕЧСИМВ (CLEAN) удаляет все непечатаемые и невидимые символы из текстового значения.

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

Неправильно указан номер столбца.

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

Номер столбца не может быть меньше 1 и не может быть больше, чем количество столбцов в указанном для поиска диапазоне. Если номер столбца указан неверно, то ВПР возвращает ошибку #VALUE!

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

И в любом случае – номер столбца должен быть больше 1. Вряд ли кто-то из вас будет руками вводить значение 0 или -1, но если вы получаете номер столбца при помощи каких-то функций, вложенных в ВПР, то здесь-то и может быть ошибка.

Ошибка в ссылке на данные из другой таблицы

Мы с вами говорили о том, что диапазон поиска может быть расположен в другой таблице или даже в другом файле Google таблиц.

Если вы случайно допустили ошибку при указании ссылки на эти данные, то увидите сообщение об ошибке #ССЫЛКА! (#REF!)

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

После изменения таблицы функция перестала работать

Вы сделали все правильно и до тех пор, пока вы не вставили несколько строк или стрлбцов в вашу таблицу, функция работала верно.

Теперь же появилась ошибка #Н/Д. В чем дело?

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

Постарайтесь отменить слеланные изменения, благо Google таблицы хранят всю историю изменений вашей таблицы.

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

Как убрать сообщение об ошибке?

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

Но прежде чем приступать к таким радикальным мерам, всё же проверьте – а всё ли вы сделали правильно?

Итак, что нужно сделать прежде всего, увидев сообщение “#Н/Д”.

Во-первых, проверьте адрес диапазона поиска. Действительно ли то значение, которое вы ищете, может находиться в первом столбце диапазона.

Во-вторых, проверьте правильно ли вы указали тип параметра “сортировка” (is_sorted): ИСТИНА или ЛОЖЬ.

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

Если вы все сделали правильно, но диапазоне поиска всё же нет нужного значения, дополните формулу функцией ЕСЛИОШИБКА (IFERROR). Например, так:

Синтаксис функции ЕСЛИОШИБКА очень простой. Первый аргумент – это выражение, значение которого мы проверяем на возниконвение ошибки.

Если ошибки нет, товозвращается значение этого выражения. Если же возникает ошибка, то функция возвращает значение второго аргумента. В данном случае в ячейке F9 будет указано “Не найдено”.

Согласитесь, это гораздо более красиво и информативно, чем стандартное сообщение об ошибке.

Источник

Не работает ВПР в Excel: с чем это связано

Не работает ВПР в Excel

Windows 11

Не отображается текст в ячейке Excel

Как отобразить строки в Excel

Как закрыть Эксель, если не закрывается

Как сделать, чтобы Эксель не округлял числа

Не работает ВПР в Excel н д? Причина в том, что формула не может найти необходимое значение, к примеру, из-за отсутствия искомого параметра в файле. Убедитесь, что такой показатель имеется в первоначальных данных, проверьте тип значений, удалите лишние пробелы, используйте способы точного / ориентировочного совпадения, задействуйте правильные аргументы и т. д. Ниже рассмотрим, в чем могут быть причины, и как действовать для восстановления работоспособности Эксель.

Причины и пути решения в Excel

Существует много причин, почему не работает ВПР в Excel и появляется надпись Н / Д. В каждой из ситуаций необходимо индивидуально подходить к решению вопроса с учетом возникшей неисправности.

Наиболее эффективный метод

В ситуации, когда не работает функция ВПР в Excel, проверьте наличие элемента на листе или задействуйте в формуле функцию обработки ошибок, к примеру, =ЕСЛИОШИБКА(ФОРМУЛА();0). В таком случае при появлении сбоев в расчете показывается ноль, а в ином случае — результат формулы. Можно дополнить запись “”, чтобы ничего не показывалось, или внести в скобки какую-либо запись.

Ошибка в типе параметров

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

• Выберите ячейку (одну или несколько).
• Жмите правой кнопкой мышки.
• Выберите формат ячеек, а дальше Число.

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

Лишние пробелы

Распространенная причина, почему не работает формула ВПР в Excel, состоит в наличии пробелов. Для их удаления используйте функцию СЖПРОБЕЛЫ.

Ошибки метода поиска совпадения

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

Для поиска точного совпадения введите для аргумента «интервальный_просмотр» показатель ЛОЖЬ.

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

Не соответствие числа строк / столбцов заданному диапазону

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

В ячейке введена надпись Н / Д или Н Д

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

Другие ошибки

Дополнительно стоит выделить и ряд других ситуаций, когда ВПР в Excel по какой-то причине не работает:

1. В используемой формуле нет одного или более аргументов. Для исправления проблемы введите все необходимые документы и проверьте, работает опция или нет. Для контроля можно использовать Visual Basic.
2. Пользовательская опция недоступна. Для исправления проблемы убедитесь, что документ Excel с пользовательской функцией открыт, а опция работает корректно.
3. Макрос имеет функцию, которая возвращает Н Д. Если ВПР не работает по этой причине, для исправления ошибки убедитесь в правдивости аргументов и их нахождении в нужных местах.
4. Изменение защищенного файл с опцией ЯЧЕЙКА. Для исправления ситуации, когда ВПР в Excel не работает, жмите на комбинацию Ctrl+Alt+F9.
5. Столбец не является первым слева дли поискового диапазона. Для решения проблемы нужно ввести соответствующий параметр и проверить, появляется ли Н Д. Как вариант, можно использовать функции ИНДЕКС и ПОИСКПОК в качестве гибкой альтернативы для ВПР.
6. Неправильное форматирование числа. Бывают ситуации, когда цифры указаны в текстовом формате. Это часто происходит при импортировании сведений из внешней базы данных или при вводе апострофа перед числом для сохранения нуля в начале. Для решения проблемы жмите по ошибке и укажите Convert to Number. При появлении Н Д для многих чисел выделите их и жмите правой кнопкой мышки, а после выберите Format Cells и вкладку Число и Числовой.

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

Что за функция

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

К примеру, в вас есть две таблицы. Первая — цены и названиями, вторая — заказ на покупку продукции. Осуществлять поиск в первом документе и пытаться вписать цену в заказ трудно. Необходимо, чтобы работа проходила автоматически. Для этого достаточно найти нужное значение в 1-м столбце и вернуть его содержимое из столбца той же строки, где находится название.

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

Источник