Гугл таблицы не работает впр

Почему функция ВПР (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?

Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте).

Давайте постараемся вместе ответим на вопрос: «Почему функция ВПР не работает?»

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

Все остальные случаи связаны с тем, что мы что-то не учли или сделали неверно.

Итак, почему ВПР не находит значение, хотя оно есть?

Неточность при вводе данных.

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

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

Опечатка при вводе функции.

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

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

Неверные ссылки.

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

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

Важно! При таком изменении ссылок вы можете и не знать, что найдено не то, что нужно. Ведь какое-то значение все же возвращено, хоть и неверное. Поэтому будьте внимательны в использовании относительных и абсолютных ссылок! Рекомендуем по этой причине стараться использовать «умные» таблицы или именованные диапазоны.

Неверно указан параметр «интервальный просмотр».

Включен поиск до первого приблизительного совпадения в отсортированном диапазоне (параметр = ИСТИНА или вовсе опущен), но на самом деле данные не отсортированы.

Об этой проблеме мы подробно говорили ранее.

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

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

Если ваши просматриваемые данные находятся не в первом, а во втором или другом столбце, то она не сможет найти ни одного совпадения, и вы вновь увидите сообщение #Н/Д.

Это важное ограничение нельзя забывать.

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

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

Преобразуем число в текст.

Как видите, с виду записи одинаковы, однако в D6 артикул сохранен как число, а в A10 – как текст (на таких ячейках обычно видна пометка — зелёный уголок). Текст не может быть равнозначен числу, поэтому получаем #Н/Д. ВПР не находит значение, хотя оно с виду есть.

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

Как видно, обычную ссылку на D6 мы заменяем конструкцией

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

Можно поступить и проще — «приклеить» к числу пустую строку при помощи оператора склейки &.

Источник

Функция VLOOKUP (ВПР) в Google таблице

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

Синтаксис данной функции:

• Запрос (search_key) — наше искомое значение или текст (часть текста). Если ищем текст — помещаем его в кавычки. Так же, искомым значение может выступать другая функция.
• Диапазон (range) — или таблица/столбец/строка, где мы планируем искать наш запрос. Так же можно искать данные из других документов google, указывая вместо диапазона ссылку на таблицу.
• Номер_столбца (index) — порядковый номер столбца в google документе или excel таблице от столбца, по которому мы хотим отображать информацию нашего запроса .
• [Отсортировано] ([is_sorted]) — тип поиска нашего запроса: где точное совпадение искомой информации — 0 (либо FALSE («ЛОЖЬ»)); или приблизительное — 1 ( либо TRUE «ИСТИНА»)).

Как найти нужное значение или текст в Google (Excel) таблице?

Чаще всего стоит задача найти в большом массиве данных конкретную информацию по заданным параметрам — нашему запросу . Это какой-нибудь SKU (Артикул) в прайс-листе или цены на конкретные товары. Есть ситуации, где поставщики часто изменяют какие-нибудь описания в товарах/услугах и т.д., и нам нужно так же быстро изменить эту информацию в нашей таблице. В этих случаях нам поможет функция VLOOKUP (ВПР).

Простой пример поиска данных через ВПР в Google (Excel) таблицах.

Рассмотрим реальный пример из действующего интернет-магазина. Поставщик прислал прайс лист, в котором нам нужно накидать заказ. Но у нас:

• во-первых , не все товары из прайса представлены в нашем интернет-магазине;
• во-вторых , SKU (Артикулы) / EAN / Штрихкоды могут отличаться или иметь свои внутренние дополнения. Нам нужно как-то состыковать имеющийся у нас ассортимент с ассортиментом поставщика;
• в-третьих , сортировка наименований у нас в базе магазина и у поставщика может (чаще всего так и есть) отличаться. Это когда у нас товары отсортированы по категориям, а у поставщика по алфавиту.

Сопоставляем данные

В том же самом файле поставщика вы вставляете данные своей базы склада интернет-магазина или любой другой базы данных, по которым будете сопоставлять данные поставщика. У меня это SKU (Артикул). В том случае, если в вашей базе данных склада SKU имеют внутренние дополнения — их стоит привести к виду, какой изначально вам задавал поставщик, либо наоборот.

В ячейке K2 пишем формулу, поставив знак «равно» («=»):

Виды ошибок в работе с функцией VLOOKUP (ВПР)

Вы заметили, что некоторые ячейки выдали ошибку #N/A (#Н/Д). Это потому, что в искомом столбце не нашлись некоторые артикулы, которые есть у нас в базе данных склада. Либо мы неверно написали артикул у себя в базе, либо у поставщика на данный момент просто такого товара нет в наличии и он его не отобразил, либо искомая информация у нас или у поставщика записана в другом формате (вместо числа — текст, или наоборот).

1. В первом случае (если мы ошибочно написали SKU у себя) — просто ищем и исправляем ошибку у себя (или у поставщика, если уверены, что ошибку допустил он).
2. Во втором (когда у поставщика просто временно нет этого товара и он его просто не указал в файле) — внедряем в конструкцию формулы проверку на ошибку. Дописав в конструкцию: IFERROR (ЕОШИБКА).
3. В третьем (когда форматы ячеек отличаются) — внедряем в конструкцию формулы параметр, преобразующий все значения в текст. TEXT (ТЕКСТ).

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

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

Сложный пример поиска данных через ВПР в Google (Excel) таблицах

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

Задача стоит следующая: по определенным параметрам из списка статусов, находящегося в специальном столбце и его (списка статусов) положения в строках — нужно отобразить название товара из соседней вкладки со списком товаров, ссылаясь на его артикул:

1. формула ищет этот ключевой статус «Facebook» во вкладке склада в определяемом формулой столбце;
2. узнает номер строки, на пересечении ячейки (столбца) которой стоит этот статус;
3. переходит в нужную вкладку формирования фида для массового импорта товаров в магазин на Facebook;
4. считывает артикул в столбце A;
5. переходит обратно во вкладку со списком товаров интернет-магазина;
6. и динамически отображает название товара, соответствующее считанному артикулу из вкладки фида. Т.е., если статус изменится, отображаться в ячейке будет другой товар со статусом «Facebook», находящийся выше или ниже по строкам в списке товаров в базе данных склада магазина.

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

Синтаксический вид этой формулы может изначально озадачить, но я вас уверяю, со временем вы тоже сможете разобраться в сложных составных формулах:

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

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

Источник