Функция впр в excel: пошаговая инструкция с примерами для чайников

Аргументы функции ВПР

Формула ВПР в Excel имеет 4 аргумента:

искомое значение в формате текстовой строки, ссылки на какую-либо ячейку, значения – это параметр в первом столбце слева;
таблица – диапазон для поиска в виде именного диапазона или ссылки на определенный диапазон (это бывает и вся таблица, и отдельные столбцы);
номер столбца – отсюда берется искомая величина, указывается номер столбца из диапазона;
интервальный просмотр – 0 или 1.

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

Ошибки #Н/Д

С схожей неувязкой сталкиваются почти все юзеры, которые лишь начинают воспользоваться данной нам функцией. Обычно, ошибка «#Н/Д» возникает в последующих вариантах:

вы включили четкий поиск (крайний параметр 0/ЛОЖЬ), а это значение в таблице отсутствует;
вы включили неточный поиск (крайний параметр 1/ИСТИНА), но при всем этом данные не отсортированы по возрастанию (если употребляется ориентировочный поиск, то создатели Microsoft советуют употреблять упорядоченные данные);
аргументы функции имеют разный формат (к примеру, что-то в текстовом виде, а остальное – в числовом);
в формуле находятся опечатки либо излишние знаки (пробелы, непечатаемые знаки, переносы и так дальше).

Ищем по форматам данных

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

К примеру, возникла необходимость все значения с курсивного текста заменить на жирный текст. Для этого необходимо на вкладке «Заменить», включить расширенный функционал кнопкой «Параметры» и в настройках «Формат» указать параметры поиска и параметр замены. При клике на кнопку «Формат», откроется новое диалоговое окно «Найти формат» и перейдя на закладку «Шрифт» в настройке «Начертание», выбрать «Курсив». Эти операции необходимо сделать два раза, первый раз, для нахождения и второй, для замены. Кнопки «Формат» размещены рядышком, одна над второй. Теперь при нажатии кнопки «Заменить все», произойдет подмена форматов с курсива на жирное начертание.

Что такое функция ВПР в Эксель и как ей пользоваться

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

Если вы из такой категории пользователей, обязательно прочитайте дальше.

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

Впр excel — расшифровка и пояснение

Объяснение для чайников начать нужно с расшифровки названия функции. ВПР — всего лишь сокращение от Вертикального Поиска. На английском выглядит название с аббревиатурой так: VLOOKUP – от Vertical LOOKUP.

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

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

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

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

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

Вот самый простой пошаговый пример с картинками, как с использованием ВПР в excel найти нужную информацию:

Открыть таблицу эксель.
В любой свободной ячейке справа — на картинке-примере ячейка «Е», — указать формулу.
Формула **ВПР **начинается со знака «=».
Далее нужно задать функцию — это и есть ВПР (соответствует VLOOKUP).
Затем открываем круглые скобки, вводим аргументы и закрываем круглые скобки.

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

Третий элемент — номер столбца, в котором следует искать нужный ответ. У нас это цена предмета, находится в третьем столбце. Значит, задать нужно сначала название элемента, указанного в первом столбце, затем диапазон поиска, и номер столбца, в котором будет находиться искомая информация.

Есть еще четвертый аргумент. Он определяет, следует искать точное совпадение или все приблизительные. Если мы ищем точное совпадение, то четвертым аргументом указываем False (ложь). Если же достаточно приблизительных совпадений, то ввести следует True (истина).
Вот и все. На этом формула для ВПР в эксель составлена. Осталось нажать клавишу «энтер», и получить нужную информацию.

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

Если же поиск нужно продолжить на следующем листе, то между функцией и диапазоном столбцов просто вводится «Лист 1» или любой другой.
Надеемся, даже чайники поняли смысл и особенности применения ВПР в excel. А если у вас есть свои интересные применения ВПР, поделитесь с нами в х.

Функция ВПР, пошаговая инструкция

Итак, изначально у нас есть две таблицы, которые Вы можете видеть на скриншоте ниже. Общим столбцом для обеих таблиц является «Должность».

Цель: добавить в первую (верхнюю) таблицу в столбец «Зарплата» данные из аналогичного столбца второй (нижней) таблицы.

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

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

Функция ВПР, инструкция

Добавим ВПР в ячейку.Здесь это ячейка С4. Вставить формулу можно либо вручную (соблюдая синтаксис, описанный ранее), либо при помощи Мастера функций.

Указываем параметр «Искомое значение»В нашем примере нужно указать Excel, что мы будем искать во второй таблицы. Поскольку общим столбцом в обеих таблицах является Должность, то в качестве аргумента укажем адрес ячейки, содержащей должность (в примере это B4).

Указываем параметр «Таблица».Указываем диапазон ячеек, в котором нужно искать должность из первой таблицы. В нашем примере это будет «A$10:B$12»

Ещё раз обращаем внимание на то, что заголовки таблицы в диапазон попадать не должны.

Указываем параметр «Номер столбца».Поскольку мы хотим в первую таблицу добавить значение зарплаты, то укажем номер столбца 2 («Должность» это первый столбец указанной в предыдущем аргументе таблицы, а «Зарплата» — второй столбец).

Указываем параметр «Интервальный просмотр».В данном случае нас интересует именно точное совпадение названия должности в обеих таблицах поэтому укажем интервальный просмотр 0.

Обратите внимание на символ «$» перед номерами строк в диапазоне (аргумент ВПР номер 2). Это нужно для того, чтобы номера строк оставались неизменными при копировании формулы в другие ячейки

В результате для ячейки C4 мы получим следующую формулу:=ВПР(B4; A$10:B$12; 2; 0)

Как работает функция ВПР

В нашем примере функция ВПР в Эксель для ячейки C4 работает следующим образом. Excel ищет слово «Директор» в колонке «Должность» второй таблицы (как и было указано ранее, поиск выполняется в первом столбце указанного диапазона). Искомое значение найдено во второй строке второй таблицы (заголовки не считаем).

Поскольку в качестве параметра «Номер столбца» мы указали 2, то функция ВПР вернёт в результате то, что указано в колонке «Зарплата».

Итого полученный результат: «40 000»

Это значение и будет результатом выполнения функции ВПР в данном конкретном случае. На скриншоте выше Вы можете видеть, что значение «40 000» вписано в ячейку C4 первой таблицы.

Скачать файл Excel (*.xlsx) с этим примером Вы можете после статьи.

Заполним аналогичными формулами остальные ячейки

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

В данном случае лишь необходимо учесть, что диапазон ячеек, в котором производится поиск (аргумент 2), не должен меняться. Именно поэтому перед номерами строк в диапазоне поставлены символы «$». Для первого аргумента (искомое значение) это, напротив, не нужно, поскольку для каждой строки первой таблицы искомое значение будет в разной ячейке (в нашем примере: A4, A5, A6).

Как искать / находить и заменять значения в выделенных или выбранных ячейках в Excel?

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

Поиск или поиск и замена значений в выделенном фрагменте с помощью функции поиска и замены

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

1. Выберите диапазон или ячейки, в которых нужно выполнить поиск или найти и заменить значения, а затем нажмите Ctrl + F одновременно клавиши, чтобы открыть диалоговое окно «Найти и заменить».

2. В диалоговом окне «Найти и заменить», если вы просто хотите найти значения, щелкните значок Найдите на вкладке, введите значение, которое вы хотите найти, а затем нажмите Найти все кнопку.

Но если вы хотите найти значения и заменить их другим содержимым, нажмите Замените вкладка, введите значение поиска и значение замены отдельно в поле Найти то, что и Заменить коробки и, наконец, щелкните Заменить все кнопка. Смотрите скриншот:

3. После нажатия на Найти все кнопки, все результаты поиска будут перечислены внизу Найти и заменить диалоговое окно. И вы можете легко перейти к ячейке, нажав соответствующую ссылку. Смотрите скриншот:

Для поиска и замены значений после нажатия кнопки «Заменить все» появится диалоговое окно, как показано на скриншоте ниже. OK кнопку.

Найдите или найдите и замените значения в выборе с помощью Kutools for Excel

Расширенный поиск и замена панель Kutools for Excel может не только искать / находить и заменять значения в выделенных или выбранных ячейках, но также искать / находить и заменять значения во всех открытых книгах, а также в активной книге или листе.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. После установки Kutools и запуска Excel, Панель навигации Kutools будет открыт по умолчанию.

Советы: Если панель не отображается, щелкните Кутулс > Навигация чтобы открыть ее.

2. в Найти и заменить панель настройте следующим образом:

2.1) Переход к Найти и заменить панель, щелкнув кнопку.
2.2) Выбрать Выбор в В раскрывающийся список;
2.3) Включите Найдите or Замените вкладка в зависимости от ваших потребностей;
2.4) Введите определенные значения в Найти то, что и Заменить коробки;
2.5) Нажмите кнопку, чтобы выбрать диапазон (диапазоны), в котором вы будете искать значения,
2.6) Наконец, нажмите Найти все or Заменить все кнопку.

Затем значения в выборке ищутся или заменяются другим содержимым.

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

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

Ошибки при использовании функции ВПР

На начальном этапе использования вместо нужных значений функция часто указывает на различные виды ошибок. Знать, что означает та или иная ошибка, — верный путь к ее быстрому исправлению. Самые часто возвращаемые ошибки:

«Н/Д» — самый распространенный тип ошибки. Может возникнуть по нескольким причинам.

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

«ССЫЛКА» — данная ошибка часто возникает при неправильном указании номера столбца, если столько столбцов нет в выбранном диапазоне. В таких случаях необходимо помнить, что номер проставляют, считая с левого столбца выделенного диапазона, а не таблицы в целом.
«ИМЯ» — ошибка возвращается часто при неправильном занесении текста в «Искомое значение». Текст необходимо прописывать в кавычках.

Знания, как пользоваться VLOOKUP в Excel, приходят, как и все в этой программе, с практикой. Одно только изучение теории вместо ясности, вероятнее всего, принесет хаос в понимании той или иной операции в программе. При изучении любой функции «Эксель» пользователю всегда эффективнее опираться на конкретную, пусть и небольшую, табличку-пример. Это позволяет глубже понять сущность анализа, закрепить полученные знания. Так и с функцией ВПР в Excel. При большом объеме теории практическое применение показывает, что она не настолько сложна, сколько полезна.

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

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

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

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

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

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

Вот так.

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

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

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

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

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

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

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

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

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

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

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

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

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

Точное или приближенное совпадение в функции ВПР

И, наконец, давайте рассмотрим поподробнее последний аргумент, который указывается для функции ВПР – range_lookup (интервальный_просмотр). Как уже упоминалось в начале урока, этот аргумент очень важен. Вы можете получить абсолютно разные результаты в одной и той же формуле при его значении TRUE (ПРАВДА) или FALSE (ЛОЖЬ).

Для начала давайте выясним, что в Microsoft Excel понимается под точным и приближенным совпадением.

Если аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ), формула ищет точное совпадение, т.е. точно такое же значение, что задано в аргументе lookup_value (искомое_значение). Если в первом столбце диапазона table_array (таблица) встречается два или более значений, совпадающих с аргументом lookup_value (искомое_значение), то выбрано будет первое из них. Если совпадения не найдены, функция сообщит об ошибке #N/A (#Н/Д).Например, следующая формула сообщит об ошибке #N/A (#Н/Д), если в диапазоне A2:A15 нет значения 4:
Если аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА), формула ищет приблизительное совпадение. Точнее, сначала функция ВПР ищет точное совпадение, а если такое не найдено, выбирает приблизительное. Приблизительное совпадение – это наибольшее значение, не превышающее заданного в аргументе lookup_value (искомое_значение).

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

Чтобы лучше понять важность выбора TRUE (ИСТИНА) или FALSE (ЛОЖЬ), давайте разберём ещё несколько формул с функцией ВПР и посмотрим на результаты

Пример 1: Поиск точного совпадения при помощи ВПР

Как Вы помните, для поиска точного совпадения, четвёртый аргумент функции ВПР должен иметь значение FALSE (ЛОЖЬ).

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

Обратите внимание, что наш диапазон поиска (столбец A) содержит два значения 50 – в ячейках A5 и A6. Формула возвращает значение из ячейки B5

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

Пример 2: Используем ВПР для поиска приблизительного совпадения

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

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

Вот теперь можно использовать одну из следующих формул:

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

Как видите, формула возвратила результат Антилопа (Antelope), скорость которой 61 миля в час, хотя в списке есть также Гепард (Cheetah), который бежит со скоростью 70 миль в час, а 70 ближе к 69, чем 61, не так ли? Почему так происходит? Потому что функция ВПР при поиске приблизительного совпадения возвращает наибольшее значение, не превышающее искомое.

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

Как сравнить две таблицы: пошаговая инструкция для «чайников»

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

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

Делается это в несколько шагов:

Открыть первую со старой информацией.
Добавить дополнительный столбик для новых данных «Новая стоимость».
Выделить первое пустое поле в созданном столбце (С2).
Выбрать раздел «ВПР Формулы» и «Вставить функцию».
Найти категорию Excel «Ссылки и массивы».
Выбрать ВПР.
Задать «Аргументы».

– то, что важно будет найти во второй таблице. Чтобы значение появилось в строке, нужно выделить первый столбик с наименованиями товаров (А2 – А5)

– с чем программа будет сравнивать. Для заполнения нужно перейти на вторую страницу и отметить два наименования – предметы и цена (А2 – В5). И зафиксировать результат кнопкой F4.

Номер столбца – второй, так как именно стоимость переносится в новую.

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

Заполненное окно выглядит так:

После нажатия кнопки «ОК» новые значения появятся в таблице. Чтобы ценовая информация появилась у всех предметов нужно растянуть ячейку.

Теперь администратор может работать с данными стандартными функциями Excel, благодаря инструкции.