Как в кабинете
Excel в ячейке не работают формулы
Функция ВПР самая пользующаяся популярностью и нередко применяемая функция. Познание данной функции весьма поможет для вас в вашей работе. Описание работы функции ВПР с примером вы сможете поглядеть в специальной статье, а в данном уроке мы разглядим примеры, когда функция ВПР не работает либо выдает ошибки.
Если вы научились применять функцию, но у вас возникает ошибка, то в данной нам статье мы разглядим вероятные ошибки и пути их решения.
Столбец поиска искомого значения не является последним левым.
Самая всераспространенная ошибка, когда ВПР не работает. Давайте разглядим этот же пример, что мы разглядывали при описании функции ВПР. У нас есть таблица заказов и Прайс лист.
Направьте внимание, что в данном примере таблица Прайс лист различается. Наименования продуктов сейчас находятся не в первом столбце, а в 3-ем, опосля цены продукта.
В текущем виде недозволено будет пользоваться функцией ВПР, потому что функция находит разыскиваемое значение в Последнем левом столбце и возвращает значение из обозначенного вами номера столбца который находится правее последнего левого столбца. В нашем случае правее ничего нет, потому ВПР не будет работать.
Решение
1. Если есть возможность, то перенесите столбец, где ищется разыскиваемое значение в последнее левое положение (или просто левее столбца из которого нужно возвращать значение, в нашем примере относительно столбца с ценой) как обозначено на рисунке выше.
2. Обычно переносить столбец не весьма комфортно, к примеру в вариантах, когда эту таблицу нам присылаются повсевременно в таком формате, потому обычно используют прием прибавления слева таблицы дублирующего вспомогательного столбца.
Выделяем 1-ый столбец прайс листа, избираем «Основная», «Ячейка», «Вставить», «Вставить столбцы на лист». Или используйте пустой столбец, если он у вас уже есть. Встаем в первую ячейку вспомогательного столбца и просто делаем ссылку на подходящий нам столбец и протягиваем формулу вниз.
Получили точную копию столбца с наименованием продукта левее цены, сейчас можно применять функцию ВПР. Таблицу сейчас нужно брать начиная с нашего вспомогательного столбца.
3. Можно применять комбинацию функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), как наиболее гибкую кандидатуру для ВПР.
Функция ВПР возвращает ошибку #Н/Д (#N/A)
#Н/Д (#N/A) значит «Нет данных» («not available»). При протягивании формулы ВПР у вас возникает #Н/Д. Вопросец даже не в том, что ВПР не работает, а почему возникают данные значения.
Решение
1. Не закреплен спектр таблицы
Если при протягивании формулы ВПР у вас 1-ое значение подтянулось а в других отобразилось #Н/Д то быстрее всего вы не закрепили спектр таблицы при помощи $ и при протягивании у вас таблица начала съезжать вниз. Снова прочитайте пристально описание функции ВПР с примером.
2. Разыскиваемое значение и значение в просматриваемом спектре не совпадает
На рисунке выше есть #Н/Д дважды. Один раз напротив продукта «Савок» и иной напротив слова «Стол».
В первом случае понятно — продукта «Савок» у нас нет в прайс листе, отсюда и ошибка.
Во 2-м случае неясно, продукт «Стол» у нас есть, но все равно Н/Д#. Обычно ошибка заключается в написании слов и форматах или в таблице заказов или на страничке Прайс лист.
В нашем случае у нас опосля слова «Стол » стоит пробел, потому данное слово не находится в прайс листе. Для начала проверьте содержимое ячеек на невидимые пробелы. В особенности нередко они встречаются в конце строчки. Кликните на ячейку и переместитесь в конец формулы. Удалите излишние пробелы, если они есть.
В данном примере можно так же применять функцию СЖПРОБЕЛЫ
=ВПР( СЖПРОБЕЛЫ(A3) ;$F$2:$I$16;3;0)
другими словами разыскиваемое значение сходу пропускается через данную функцию, чтоб удалить в разыскиваемом значении излишние пробелы.
В нашем случаем мы отыскиваем текстовые значения — «Наименование продукта», время от времени поиск идет по числовым значениям. В этом случае #Н/Д может появляться, когда разыскиваемое значение, к примеру в формате числа, а в таблице в виде текста. Для исправления ошибку будет нужно перевести формат текста в числа либо напротив, другими словами создать однообразный формат.
Еще наиболее редчайший вариант, это когда в слове быть может заместо российских слов латинские и напротив. К примеру A и А это различные буковкы различных алфавитов, потому и слова будут различными для ВПР. Чтоб проверить, просто сделайте в отдельном столбце ссылку одной ячейки на другую =A4=F6 в нашем примере эта формула возвратит «Ересь» потому что эти ячейки не равны (в одном слове есть излишний пробел), подобная ситуация будет если заместо российской буковкы «С» будет британская буковка «C».
Функция ВПР возвращает ошибку #ЗНАЧ! (#VALUE!)
Обычно Excel докладывает о ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подступает по типу данных. Если брать функцию ВПР, то обычно стоит разглядеть две главные предпосылки ошибки #ЗНАЧ!.
1. Разыскиваемое значение больше 255 знаков.
В функции ВПР есть ограничение на длину искомого значения — оно не обязано быть наиболее 255 знаков. Если вы столкнулись с таковой неувязкой, то советуем применять для этих целей все ту же связку ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH).
2. Некорректно указан полный путь на другую книжку.
Эта ошибка может появиться, если вы извлекаете данных из иной книжки Excel прописали неполный путь до нее. Чтоб не ошибиться при написании формулы не пишите путь к иной книжке от руки, а откройте эту книжку и выделяйте подходящий спектр.
3. Аргумент номер_столбца в функции ВПР меньше 1
Это может произойти если номер столбца вы отыскиваете при помощи некий иной функции. Просто проверьте и удостоверьтесь, что функция работает корректно.
Остальные принципиальные индивидуальности работы функции ВПР
Если вы желаете разбить не саму ячейку, а текст в ячейке по столбцам, то тогда для вас необходимо глядеть другую статью «Как разбить текст по столбцам в Excel»
1. ВПР не чувствительна к регистру
Функция ВПР не чувствительна к регистру и для нее все знаки нижнего и верхнего регистра будут схожие. Другими словами, слово «Стол», «СТОЛ» и «стол» для функции ВПР будут схожими.
Если для вас нужно учесть регистр при использовании ВПР, то используйте другую функцию Excel, наприме (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр и ворачивается ИСТИНУ либо ЛОЖЬ при совпадении либо не совпадении с разыскиваемым значением.
2. ВПР возвращает 1-ое отысканное значение
Постоянно помните, что ВПР возвращает 1-ое отысканное значение, потому если в нашем примере в прайс листе по ошибке будет два схожих продукта с разной ценой, то ворачиваться будет лишь 1-ый стоимость.
3. Работа функции ВПР и добавлении либо удалении столбца
Обычно при написании функции ВПР мы в ручную отсчитываем столбец и указываем его в виде числа. В нашем примере с прайс листом. Вы зрительно обусловили, что вкупе с вспомогательным столбцом, стоимость продукта находится в 3-ем столбце и указали цифру 3 в функцию.
Если кто-то добавит перед столбцом с ценой какую-нибудь информацию, к примеру о остатках продукта, то сейчас в 3-ем столбце будет остаток продуктов, а стоимость будет в 4-м столбце, но формуле это автоматом не обменяется, мы должны держать в голове о этом и поменять информацию.
Для решения данной нам препядствия можно применять функцию ПОИСКПОЗ для поиска номера столбца. В качестве искомого значения мы можем указать текст «Стоимость» и находить в строке — шапке таблице Прайс лист. Сейчас если добавлять столбцы в таблицу Прайс лист, то функция ПОИСКПОЗ все равно отыщет ячейку со словом «Стоимость» и возвратит на номер позиции данной ячейки.
