6 причин, почему функция впр не работает

ВПР не работает (ограничения, оговорки и решения)

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

1. ВПР не чувствительна к регистру

Функция ВПР не различает регистр и принимает символы нижнего и ВЕРХНЕГО регистра как одинаковые. Поэтому, если в таблице есть несколько элементов, которые различаются только регистром символов, функция ВПР возвратит первый попавшийся элемент, не взирая на регистр.

Решение: Используйте другую функцию Excel, которая может выполнить вертикальный поиск (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр. Более подробно Вы можете узнать из урока – 4 способа сделать ВПР с учетом регистра в Excel.

2. ВПР возвращает первое найденное значение

Как Вы уже знаете, ВПР возвращает из заданного столбца значение, соответствующее первому найденному совпадению с искомым. Однако, Вы можете заставить ее извлечь 2-е, 3-е, 4-е или любое другое повторение значения, которое Вам нужно. Если нужно извлечь все повторяющиеся значения, Вам потребуется комбинация из функций ИНДЕКС (INDEX), НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW).

3. В таблицу был добавлен или удалён столбец

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

Решение: И снова на помощь спешат функции ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH). В формуле ИНДЕКС+ПОИСКПОЗ Вы раздельно задаёте столбцы для поиска и для извлечения данных, и в результате можете удалять или вставлять сколько угодно столбцов, не беспокоясь о том, что придётся обновлять все связанные формулы поиска.

4. Ссылки на ячейки исказились при копировании формулы

Этот заголовок исчерпывающе объясняет суть проблемы, правда?

Решение: Всегда используйте абсолютные ссылки на ячейки (с символом $) при записи диапазона, например $A$2:$C$100 или $A:$C. В строке формул Вы можете быстро переключать тип ссылки, нажимая F4.

Данные в таблице дублируются

Функция ВПР может извлечь только одну запись. Она возвратит первую найденную запись, соответствующую введённому Вами условию поиска.

Если таблица содержит повторяющиеся значения, функция ВПР не справится с такой задачей правильно.

Решение 1

Нужны ли Вам повторяющиеся данные в списке? Если нет – удалите их. Это можно сделать быстро при помощи кнопки Removes Duplicates (Удалить дубликаты) на вкладке Data (Данные).

Решение 2

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

Таблица ниже – это список заказов. Допустим, Вы хотите найти все заказы определённого фрукта.

Сводная таблица позволяет выбрать значение из столбца ID в фильтре, которое соответствует определенному фрукту, и получить список всех связанных заказов. В нашем примере выбрано значение ID равное 23 (Бананы).

Аргументы функции. Функция ВПР в Excel

Искомое_значение

Значение поиска, которое должно быть найдена в указанном нами диапазоне, в строке Таблица. В нашем примере мы указываем Конфеты Ж (ячейка Е3, Таблица №2). Так как это значение идет первое в столбце Название конфет, Таблица №2. (Это не принципиально, но удобно). Это значение, которое будет искать наша функция в Таблице №1.

Что бы выбрать нужную нам ячейку с значением, достаточно просто стать курсором в строку Искомое_значение, а потом клацнуть левой кнопкой мыши, по нужной ячейке в таблице ( В нашем примере ячейка Е3).

Таблица

Здесь необходимо указать диапазон таблицы, в которой будет происходить поиск нужного нам значения и данных, которые мы хотим перенести. В нашем примере это Таблица №1. Значение, по которому будет происходить поиск это название конфет. Данные, которые мы хотим перенести, это цена конфет. Мы просто ставим курсор в строку Таблица и выделяем нужный нам диапазон. В нашем примере это диапазон Таблицы №1 — B1:C12. При этом ссылки нужно сделать абсолютными, добавив знак $. Это можно сделать, просто добавив эти знаки к ячейкам диапазона, в строке Таблица — $B$1:$C$12.

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

Как это сделать. Выбираем нужный нам диапазон. Таблица №1. Выбираем закладку Формулы, кнопка Задать имя. Нажимаем. Появляется диалоговое окно Создание имени. Пишем любое имя. Но нужно его запомнить. Например Конфеты. Нажимаем ОК.

В строке Таблица, вместо диапазона нужно будет ввести имя, которое мы присвоили – Конфеты

Номер_столбца

Функция ВПР осуществляет поиск значение в самом левом столбце таблицы указанного диапазона поиска. Функция присваивает этому столбцу номер 1, по умолчанию. В нашем примере самый левый столбце, это Название конфет в Таблице №1. А в строке Номер_столбца, нам нужно указать, какой номер по порядку имеет столбец, из которого нужно перенести данные. В нашем примере это столбце Цена в Таблице №1. Он «второй» по порядку, если считать слева на право, от столбца Название конфет, Таблица №1. Поэтому в строку Номер_столбца мы пишем цифру 2. Если бы столбец Цена, Таблица №1 был бы расположен по порядку не на втором месте, а предположим на десятом, то мы соответственно указывали бы в строке Номер_столбца цифру 10.

Интервальный _просмотр

В этой строке мы пишем цифру ноль «0». Это значит, что функция ВПР будет осуществлять поиск точных совпадений между значениями поиска (Искомое_значение) и значениями в крайнем левом столбце диапазона поиска (Таблица). В нашем примере поиск точных совпадений будет происходить между столбцом Название конфет, Таблица №1, и столбцом Название конфет в Таблице №2.

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

Вот как это выглядит все вместе.

Нажимаем ОК.

Протягиваем формулу по всему столбцу Цена в Таблице №2. Все цены перенесены с Таблице №1 в Таблицу №2.

Описание синтаксиса и параметров функции ЕНД в Excel

Функция ЕНД имеет следующую синтаксическую запись:

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

Преобразование типов данных для значений, переданных в качестве аргумента функции ЕНД, не выполняется. Например, число «99» указанное в кавычках, будет рассматриваться как текстовые данные. Если в качестве аргумента была передана строка «#Н/Д», ЕНД вернет значение ЛОЖЬ. Если ячейка содержит код ошибки #Н/Д, функция ЕНД, получившая ссылку на данную ячейку в качестве аргумента, вернет – ИСТИНА.
Данная функция обычно используется в комбинации с ЕСЛИ и другими функциями для проверки выражения для своевременной проверки результатов вычислений и перехвата возможной ошибки.
Код ошибки #Н/Д генерируют функции в случаях, когда в формулах используются недопустимые значения. Распространенные примеры:

при использовании функций для поиска данных (ПОИСКОП, ВПР и других), если в качестве аргумента «искомое_значение» было введено несуществующее;
при использовании формул массивов, если длина массива результатов превышает длину исходных массивов;
если при использовании функции не были указаны один либо нескольких аргументов, обязательных для заполнения.

голоса

Рейтинг статьи

Вместо этого используйте индекс и сопоставление

Функции индекс и соответствие являются хорошими вариантами для многих случаев, в которых ВПР не отвечает вашим потребностям. Ключевое преимущество функции индекс/соответствие заключается в том, что вы можете найти значение в столбце в любом месте таблицы подстановки. Функция индекс возвращает значение из указанной таблицы или диапазона (в зависимости от его положения). Функция MATCH Возвращает относительное положение значения в таблице или диапазоне. Использование ИНДЕКСов и СОВПАДЕНИй в формуле для поиска значения в таблице или массиве путем указания относительной позиции значения в таблице или массиве.

Существует несколько преимуществ использования функции индекс и СОВПАДЕНИе вместо функции ВПР.

При использовании индекса и совпадения возвращаемое значение не обязательно должно находиться в том же столбце, что и столбец подстановки. Это отличается от функции ВПР, в которой возвращаемое значение должно находиться в указанном диапазоне. Как это делается? При использовании функции ВПР необходимо знать номер столбца, который содержит возвращаемое значение. Несмотря на то, что это может показаться несложным, оно может быть утомительным при наличии большой таблицы и для подсчета количества столбцов. Кроме того, при добавлении или удалении столбца в таблице необходимо пересчитать и обновить аргумент Номер_столбца . При использовании ИНДЕКСов и СОВПАДЕНИй подсчет не требуется, так как столбец подстановок отличается от столбца с возвращаемым значением.

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

Индекс и совпадение можно использовать для поиска значений в любом столбце. В отличие от функции ВПР, в которой можно найти значение в первом столбце таблицы, функция индекс и СОВПАДЕНИе будут работать, если искомое значение находится в первом столбце, в последнем или в любом месте.

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

Индексирование и сопоставление обеспечивает большую гибкость при использовании совпадений. ИНДЕКС и СОВПАДЕНИе могут находить точное совпадение или значение, которое больше или меньше значения подстановки. ФУНКЦИЯ ВПР будет искать только наиболее подходящие значения (по умолчанию) или точное значение. ФУНКЦИЯ ВПР также предполагает, что первый столбец в массиве таблицы отсортирован в алфавитном порядке, и предположим, что таблица не настроена таким образом, функция ВПР вернет первое ближайшее соответствие в таблице, которая может не быть искомыми данными.

Чтобы создать синтаксис для функции индекс и ПОИСКПОЗ, необходимо использовать аргумент array/Reference из функцией индекс и вложить в него синтаксис MATCH. Форма будет иметь следующий вид:

=ИНДЕКС(массив или ссылка; ПОИСКПОЗ(искомое_значение;массив;)

Используйте ИНДЕКСы и СОВПАДЕНИЯ для замены функции ВПР из приведенного выше примера. Синтаксис будет выглядеть следующим образом:

= Индекс (возвращающий значение из значения C2: C10, которое будет соответствовать (первое), которое находится где-то в массиве B2: B10, где возвращаемое значение является первым значением, соответствующим первое).

Формула ищет в C2:C10 первое значение, соответствующее значению Капуста (B7), и возвращает значение в ячейке C7 (100).

Примеры

Вот несколько примеров использования функции ВПР.

Пример 5

Неправильное возвращаемое значение

Если аргумент интервальный_просмотр имеет значение ИСТИНА или не указан, первый столбец должны быть отсортирован по алфавиту или по номерам. Если первый столбец не отсортирован, возвращаемое значение может быть непредвиденным. Отсортируйте первый столбец или используйте значение ЛОЖЬ для точного соответствия.

Если аргумент интервальный_просмотр имеет значение ИСТИНА, а значение аргумента искомое_значение меньше, чем наименьшее значение в первом столбце таблицы, будет возвращено значение ошибки #Н/Д.

Если аргумент интервальный_просмотр имеет значение ЛОЖЬ, значение ошибки #Н/Д означает, что найти точное число не удалось.

Дополнительные сведения об устранении ошибок #Н/Д в функции ВПР см. в статье Исправление ошибки #Н/Д в функции ВПР.

#ССЫЛКА! в ячейке

Если значение аргумента номер_столбца больше, чем число столбцов в таблице, появится значение ошибки #ССЫЛКА!.

Дополнительные сведения об устранении ошибок #ССЫЛКА! в функции ВПР см. в статье Исправление ошибки #ССЫЛКА!.

Если значение аргумента таблица меньше 1, появится значение ошибки #ЗНАЧ!.

Дополнительные сведения об устранении ошибок #ЗНАЧ! в функции ВПР см. в статье Исправление ошибки #ЗНАЧ! в функции ВПР.

Значение ошибки #ИМЯ? чаще всего появляется, если в формуле пропущены кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, в функции =ВПР(«Иванов»;B2:E7;2;ЛОЖЬ) имя необходимо указать в формате «Иванов» и никак иначе.

Дополнительные сведения см. в статье Исправление ошибки #ИМЯ?.

Ошибки #ПЕРЕНОС! в ячейке

Это конкретная ошибка #SPILL! Обычно это означает, что формула полагается на неявное пересечение для искомого значения и использует в качестве ссылки весь столбец. Например, = ВПР ( а:а, а:к, 2; ложь). Проблему можно устранить, заменив ссылку для подстановки с помощью оператора @ следующим образом: = ВПР ( @ а:а, а:к, 2, ложь). Кроме того, вы можете использовать традиционный метод ВПР и ссылаться на одну ячейку, а не на весь столбец: = ВПР ( a2; а:к; 2; ложь).

Используйте абсолютные ссылки в аргументе интервальный_просмотр

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

Не сохраняйте числовые значения или значения дат как текст.

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

Сортируйте первый столбец

Если для аргумента интервальный_просмотр указано значение ИСТИНА, прежде чем использовать функцию ВПР, отсортируйте первый столбец таблицы.

Используйте подстановочные знаки

Если значение аргумента интервальный_просмотр — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (

) перед искомым символом.

Например, функция = ВПР («Ивано?»; B2: E7; 2; ложь) будет искать все вхождения Иванов с последней буквой, которая может измениться.

Убедитесь, что данные не содержат ошибочных символов.

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

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

Копирование и протягивание в Excel

В Excel сохраняются стандартные способы копирования, вырезания и вставки данных (путём соответствующих команд основного и контекстного меню, «горячих» клавиш Ctrl-C (копировать), Ctrl-X (вырезать), Ctrl-V (вставить)), а также перемещения диапазона ячеек с помощью мыши. Чтобы переместить диапазон, нужно выделить его мышью, «зацепить» мышью за любую из его границ и переместить в нужное место. Если нужно не переместить, а скопировать диапазон, при перемещении необходимо нажать и удерживать клавишу Ctrl.

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

Далее рассмотрим это подробнее.

В Excel добавлен ещё один очень удобный метод копирования — протягивание, аналогичный вышеперечисленным командам, но выполняемый только с помощью мыши.

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

Обратите внимание на следующий пример: выделены две ячейки с числами 1 и 2, выполнена операция протягивания. Excel «понял» принцип заполнения ячеек и автоматически заполнил следующие ячейки числами от 3 до 10

Рис.5.1. Протягивание

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

Есть небольшая хитрость: если не тянуть за уголок, а два раза щёлкнуть мышью на маркере автозаполнения, то Excel автоматически выполнит операцию протягивания.

Дополнительная полезность протягивания – автоматическое формирование прогрессий, то есть последовательностей чисел или текстовых значений. Рассмотрим примеры.

Практическая работа: протягивание

Откройте лист Протягивание, абсолютные ссылки файла Учебник — основы. В ячейке А10 напишите «1», переместитесь снова на эту ячейку. Наведите указатель мыши на маркер заполнения, нажмите левую клавишу, и, удерживая её, потяните вниз, до ячейки А21. Содержимое исходной ячейки скопируется в них, теперь во всех ячейках стоят единицы. Таким образом работает протягивание по одному значению.

Теперь отмените последнюю операцию (через меню или комбинацией клавиш Ctrl-Z). Добавьте в ячейке А11 число «2», выделите диапазон А10:А11 и протяните его до ячейки А21. Excel «понял» зависимость двух ячеек А10 и А11 как арифметическую прогрессию, и при протягивании продолжил этот числовой ряд. Теперь все строчки этой таблицы пронумерованы по порядку.

То же самое сделайте со столбцом «Месяц», программа автоматически заполнит значения месяцев. То же со столбцом «Выручка»: выделите два первых значения и протяните их до строчки «Декабрь» (здесь предполагается, что выручка растёт на 15000 ежемесячно). Столбец с данными выручки заполняется автоматически.

Теперь разберёмся с разными видами ссылок.

Комментарии • 80

ВПР мне экономит уйму времени. Работа с большим массовом данных

День добрый. Есть вопрос — у меня есть данные ПРИХОД РАСХОД ОСТАТОК и нужно выбрать кол-во товара, который является неликвидным. Как это можно сделать. Номенклатура из 10 тыс позиций. ПОМОГИТЕ, ПОЖАЛУЙСТА.

Любовь, присылайте файл на почту — [email protected]. Так будет быстрее понять вашу проблему и помочь вам с решением)

@Easy Excel я можно сказать по пути познания функций exel. у меня есть еще данные о дате заказа товара и, соответственно, понятно, что движение либо есть либо нет (период 2 года). Просто хочется сделать это быстро и безболезненно. )

Если в таблице есть признак неликвидного товара, то варианты разные — фильтры, сортировка, сводная таблица, функции и пр.

Спасибо, про истину и ложь не знал

Это то что мне было нужно!

Подскажите пожалуйста если у меня не один номер столбца, как написать ( 2;3;4 или 2,3,4, или выделить , я уже все перепробовал) что бы работало ? Допустим искомое значение 1010, след столбец: овощи, след столбец помидоры, след столбец «цена 1000 руб» Мне нужно что бы вбивая ID 1010У меня находил овощи( 2 столбец) , помидоры (3 столбец), цена (4 столбец)

Путанее объяснение. Автор не очень понимает о чем говорит , используя массу слов-паразитов. Печалька.

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

Сколько Галкин запросил за озвучку?

100-200 мелкая200-300 средняяТак какая партия при 200 мелкая или средняя?

@Easy Excel А ровно 200?

до 200 — мелкая. С 200 уже средняя.

фу какой кошмар, довести человека до 4 минуты и сказать, что выпадающий список у нас в другом видео, а в другом видео еще сказать про другое видео. совсем наплевать на слушателя?

а это видео и не про выпадающий список. Данный ролик посвящен функции ВПР.

Спасибо) Сложно, но понятно!)

Формула работает не совсем так. Например при работе с отрицательными числами и процентами мне эксель выдаёт цифры в бОльшую сторону:)

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

а как вводить текст с одного листа, одновременно на несколько листов, и в разные ячейки?

Здравствуйте Павел!! Очень нравиться ваша подача материала, очень доступно!! Теперь непосредственно к вопросу, хотела в сводной таблице через функцию ВПР, собрать значения, не получается, почему?

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

Serg Voyageur Спасибо, про сортировку надо будет осмыслить, никогда такого не делал.

@Dennis да нет — не надо ничего конструировать, плюс еще можно сортировкой регулировать какое из значений нужно взять (например от старая дата или новая). И еще не заморачиваешься с подсчетом столбцов.

Serg Voyageur С индексом надо дополнительно конструировать выражение для получения значения из другого столбца, нет?

Вопрос по второму примеру.Почему Мелкая партия — это партия от 100 до 200?Может, просто » до 200″ ?

Возможно менее 100 заказать нельзя?

Ребят, не поверите, но я узнал об этой функции в армии от чувака, который учился в мгу, служил со мной. Ну с этого момента моя писарская служба облегчалась в over999 раз. Да и вообще в армии я так неплохо освоил Эксель, был полный ноль до.

Объяснене просто супер. Но моя тупость не дает мне повторить первую часть ролика (простой перенос). Я сделала две таблицы и перенесла название товара из первой таблицы во вторую. После этого во-второй таблицы поставила товар по алфавиту и ввела формулу. После этого часть данных скопировалось как положено, а часть данных показывает решетка Н/Д. Причем правильно скопированные данные и Н/Д идут не подрят, а в перемешку. С чем это связано, что именно я делаю не правильно?

Почему номер столбца — 2? Он всегда будет 2?

Номер столбца — 2, потому что во втором по порядку столбце выделенной нами таблицы мы будем искать нужное нам значение. Если бы в таблице был, например, третий столбец со стоимостью, и мы бы искали стоимость конкретного ID, то в поле «таблица» мы бы выделили диапазон из этих трех столбцов, а в поле «столбец» указали бы число 3, поскольку столбец «стоимость» стоял бы третьим по порядку в выделенном диапазоне.Это про первый пример. Во втором примере аналогично: если бы был третий или четвертый столбец с искомой нами характеристикой, мы бы указывали соответствующий номер.

Как проводятся ВПР в начальных классах

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

участники организации ВПР;
ответственные лица и проверяющие;
определение сроков, сколько должны храниться ВПР в школе, и лиц, ответственных за сохранность;
порядок проведения и оценивания и другие важные пункты;

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

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

Критерии оценки работ также являются едиными:

готовность продолжать обучение;
готовность использовать знания на практике.

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

Перед началом работы проводится инструктаж, затем ученики приступают непосредственно к выполнению контрольной, на что им дается один урок — 45 минут времени. Использование дополнительных материалов (книги, атласы, словари, калькуляторы) во время работы не допускается. Можно пользоваться только черновиками, но записи на них при проверке не учитываются.

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

Синтаксис

=ВПР(элемент_который_нужно_найти;адрес_таблицы_где_находится_искомое_значение;порядковый_номер_столбца;интервальный_просмотр) Элемент – может быть числовым (адрес ячейки) или текстовым («текст»).

Адрес таблицы – диапазон ячеек, где примерно находится значение.

Номер столбца – принимает целое число из диапазона от 1 до n, из него будет извлечен результат.

Интервальный просмотр – приблизительное (ближайшее) соответствие критерию обозначается как 1 (истина), а точное соответствие – 0 (ложь). Данный логический аргумент указывать необязательно, если таблица отсортирована от минимального к максимальному значению. Если таблица не отсортирована и аргумент опущен, это равносильно истине.

В русифицированной версии Excel аргументы вводятся через знак «;», в англоязычной – через запятую.

Поиск с помощью ВПР по нескольким условиям

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

Итак, имеется документ, в котором обозначены: компании, товары и цены.

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

Чтобы осуществить поиск следует:

Создать слева новый столбец с объединёнными данными (название компании и товара).

Делается это просто:

выделить крайнюю левую ячейку (А1);
щёлкнуть ПКМ и выбрать «Вставить»;
отметить добавление столбца и нажать «ОК».

Внести данные в новый столбец. Для этого нужно нажать на пустое поле А2, ввести формулу объединения (=B2&C2) и нажать кнопку Enter. Чтобы продлить список достаточно растянуть ячейку.
Нажать на любое свободное место и самостоятельно ввести, что нужно найти (ЛасточкаГелевая ручка).
Выбрать ячейку где будет отображен результат и заполнить Аргументы функции.

– что нужно найти (щёлкнуть по введенной — ЛасточкаГелевая ручка – А8).

– где искать нужное значение (выделить ячейки от первой до последней — А2 – D5).

Номер столбца – из какого столбца вывести результат (4).

Интервальный просмотр – ЛОЖЬ.

После нажатия команды «ОК», программа отобразит результат.

Исправляем ошибку #Н/Д функции ВПР в Excel

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

1. Искомое значение написано с опечаткой

Хорошая мысль проверить этот пункт в первую очередь! Опечатки часто возникают, когда Вы работаете с очень большими объёмами данных, состоящих из тысяч строк, или когда искомое значение вписано в формулу.

2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР

Если Вы используете формулу с условием поиска приближённого совпадения, т.е. аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, Ваша формула может сообщить об ошибке #Н/Д в двух случаях:

Искомое значение меньше наименьшего значения в просматриваемом массиве.
Столбец поиска не упорядочен по возрастанию.

3. Ошибка #Н/Д при поиске точного совпадения с ВПР

Если Вы ищете точное совпадение, т.е. аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ) и точное значение не найдено, формула также сообщит об ошибке #Н/Д. Более подробно о том, как искать точное и приближенное совпадение с функцией ВПР.

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

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

Решение: Если нет возможности изменить структуру данных так, чтобы столбец поиска был крайним левым, Вы можете использовать комбинацию функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), как более гибкую альтернативу для ВПР.

5. Числа форматированы как текст

Другой источник ошибки #Н/Д в формулах с ВПР – это числа в текстовом формате в основной таблице или в таблице поиска.

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

Наиболее очевидные признаки числа в текстовом формате показаны на рисунке ниже:

Кроме этого, числа могут быть сохранены в формате General (Общий). В таком случае есть только один заметный признак – числа выровнены по левому краю ячейки, в то время как стандартно они выравниваются по правому краю.

Решение: Если это одиночное значение, просто кликните по иконке ошибки и выберите Convert to Number (Конвертировать в число) из контекстного меню.

Если такая ситуация со многими числами, выделите их и щелкните по выделенной области правой кнопкой мыши. В появившемся контекстном меню выберите Format Cells (Формат ячеек) > вкладка Number (Число) > формат Number (Числовой) и нажмите ОК.

6. В начале или в конце стоит пробел

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

Решение 1: Лишние пробелы в основной таблице (там, где функция ВПР)

Если лишние пробелы оказались в основной таблице, Вы можете обеспечить правильную работу формул, заключив аргумент lookup_value (искомое_значение) в функцию TRIM (СЖПРОБЕЛЫ):

=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)

Решение 2: Лишние пробелы в таблице поиска (в столбце поиска)

Если лишние пробелы оказались в столбце поиска – простыми путями ошибку #Н/Д в формуле с ВПР не избежать. Вместо ВПР Вы можете использовать формулу массива с комбинацией функций ИНДЕКС (INDEX), ПОИСКПОЗ (MATCH) и СЖПРОБЕЛЫ (TRIM):

=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))

Так как это формула массива, не забудьте нажать Ctrl+Shift+Enter вместо привычного Enter, чтобы правильно ввести формулу.

Функция ВПР в Экселе: пошаговая инструкция

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

Во второй – цены:

Если перечень товаров в обеих таблицах совпадает, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные о ценах можно легко подставить к данным о количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится.

Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.

Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.

Вот так.

Функция ВПР в Эксель легко справится с задачей.

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

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

Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.

Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».

Следующее поле «Таблица». В нем нужно указать диапазон данных, где будет осуществляться поиск нужных значений. В нашем случае это вторая таблица с ценой. При этом крайний левый столбец выделяемого диапазона должен содержать те самые критерии, по которым осуществляется поиск (столбец с наименованиями товаров). Затем таблица выделяется вправо минимум до того столбца, где находятся искомые значения (цены). Можно и дальше вправо выделить, но это уже ни на что не влияет. Главное, чтобы выделенная таблица начиналась со столбца с критериями и захватывала нужный столбец с данными

Также следует обратить внимание на тип ссылок, они должны быть абсолютными, т.к. формула будет копироваться в другие ячейки. Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно

То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов

Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов

Это не важно, Excel все сосчитает

Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.

Нажимаем ОК. Если все правильно и значение критерия есть в обеих таблицах, то на месте только что введенной формулы появится некоторое значение. Остается только протянуть (или просто скопировать) формулу вниз до последней строки таблицы.

Теперь легко рассчитать стоимость простым умножением количества на цену.

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