Как пользоваться программой впр

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

1. Таблицы должны располагаться в одной книге Excel.
2. Искать можно только среди статических данных (не формул).
3. Условие поиска должно располагаться в первом столбце используемых данных.

Формула ВПР в Excel

Синтаксис ВПР в русифицированном Excel имеет вид:

ВПР (критерий поиска; диапазон данных; номер столбца с результатом; условие поиска)

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

Критерий поиска

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

Диапазон данных

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

Функция ВПР excel за 5 минут ► VLOOKUP in Excel for 5 minutes

Номер столбца для итогового значения

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

Условие для поиска

Логическое значение (истина/1 или ложь/0), которое указывает приблизительное совпадение искать (1) или точное (0).

ВПР в Excel: примеры функции

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

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

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

Появится окно ВПР для таблицы Excel.

Чтобы указать первый аргумент (критерий), поставьте курсор в первую строку и щелкните по ячейке H3. Ее адрес появится в строке. Для выделения диапазона поставьте курсор во вторую строку и начните выделять мышью. Окно свернется до строки. Это делается для того, чтобы окно не мешало видеть Вам весь диапазон и не мешало выполнять действия.

Как использовать функцию ВПР (VLOOKUP) в Excel

Как только Вы закончите выделение и отпустите левую кнопку мыши, окно вернется в свое нормальное состояние, а во второй строке появится адрес диапазона. Он вычисляется от левой верхней ячейки до правой нижней. Их адреса разделены оператором «:» — берутся все адреса между первым и последним.

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

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

Результат обескураживает. «Н/Д» означает некорректные данные для функции. Мы не указали значение в ячейке H3, и функция ищет пустое значение.

Введем название месяца и значение изменится.

Только оно не соответствует действительности, ведь настоящее фактическое количество выпущенной продукции в январе равно 2000.
Это влияние аргумента «Условие поиска». Изменим его на 0. Для этого поставьте указатель на ячейку с формулой и снова нажмите Fx. В открывшемся окне введите «0» в последнюю строку.

Нажимайте «ОК». Как видим, результат изменился.

Чтобы проверить второе условие из начала нашей статьи (среди формул функция не ищет) изменим условия для функции. Увеличим диапазон и попробуем вывести значение из столбца с вычисляемыми значениями. Укажите значения как на скриншоте.

Нажмите «Ок». Как видите, результат поиска оказался 0, хотя в таблице стоит значение 85%.

ВПР в Excel «понимает» только фиксированные значения.

Сравнение данных двух таблиц Excel

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

На двух листах мы имеем одинаковые таблицы с разными данными.

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

В ячейку B2 введем функцию ВПР. В качестве первого аргумента укажем ячейку с месяцем на текущем листе, а диапазон выберем с листа «Цех1». Чтобы при копировании диапазон не смещался, нажмите F4 после выбора диапазона. Это сделает ссылку абсолютной.

Растяните формулу на весь столбец.

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

После копирования Вы получите сводный отчет с двух листов.

Подстановка данных из одной таблицы Excel в другую

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

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

В результате столбец второй таблицы будет скопирован в первую.

Вот и вся информация о незаметной, но полезной функции ВПР в Excel для чайников. Надеемся, она поможет Вам при решении задач.

Источник: windowstips.ru

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

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

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

Особенности использования функции

Принцип действия и синтаксис функции не меняются в зависимости от версии табличного процессора — она остается актуальной и для пакета MS Office 1997, и для Офис 365. Но для того, чтобы лучше разобраться с ее работой, стоит рассмотреть ВПР в Excel для чайников с примерами — такими, как списки продуктов на складе магазина.

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

Чтобы объединить таблицы, пользователь должен воспользоваться таким алгоритмом:

1. Привести одну из таблиц, на базе которой будет создаваться объединенный массив в подходящий вид. В том числе добавить столбец «Стоимость», изменив его формат на денежный. Одновременно можно создать колонку для подсчета общей стоимости.
2. Выделить самую верхнюю ячейку в новой колонке. Вызвать «Мастер функций», нажав на кнопку «fx» или комбинацию Shift + F3.
3. Найти и выделить в списке функций, который появится в открывшемся окне, опцию ВПР. Нажать «ОК». Если среди недавно использовавшихся функций такого варианта нет, найти его можно в категории «Ссылки и массивы». Причем не только в окне вставки, но и на панели «Формулы».
4. Начать заполнение окна с аргументами функций. Первое, что стоит указать — «Искомое значение» или «Lookup Value». В данном случае это будут названия товаров — то есть столбец от D3 до D17. Именно их функция будет искать во второй таблице и сравнивать с данными первой.
5. Указать аргумент под названием «Таблица», включающий в себя информацию из второго массива. Чтобы функция сработала, следует выделить столбец с названиями, которые должны совпадать с указанными в «Искомом значении». Вторую колонку (с единицами) придется убрать, после чего окажется, что в качестве аргумента должны быть подставлены значения от D3 до E17. Чтобы упростить задачу при работе с несколькими таблицами или постоянном использовании одних и тех же данных, массиву можно дать свое название — например, «Прайс».
6. Указать в поле «Номер столбца» ту колонку второй таблицы, из которой будут браться и подставляться в общий массив нужные данные. Здесь следует указывать только цифры. В данном случае, это «2», потому что подставляемая информация находится во втором столбце («Цена, руб.») из выделенной области.
7. Ввести в поле «Интервальный просмотр» логическое значение — «ИСТИНА» или «ЛОЖЬ». При выборе первого («ИСТИНА», «TRUE» или «1») будет выполняться поиск не по точному соответствию, а по похожим значениям. Из-за этого может произойти ошибка, и при сравнении текста указывать «1» не стоит. Чтобы функция работала правильно, в указанном примере указывается «0» («ЛОЖЬ» или «FALSE»). Это позволит искать только точные соответствия.
8. Нажать «ОК», после чего результаты поиска будут указаны только в нужной ячейке. Для распространения на всю таблицу формулу придется «размножить». Для этого достаточно захватить крестик в правом нижнем углу ячейки и протянуть его вниз.

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

Теперь можно закончить процесс подстановки и расчета общей стоимости продуктов. Для этого достаточно указать в верхней ячейке соответствующей колонки простую формулу: «Стоимость» = «Кол-во на складе» * «Цена, руб.». В данном случае это будет: «=F3*G3».

Данные «размножаются» стандартным способом: захватите крест и протяните его до конца колонки.

Если нужно, чтобы результат оставался постоянным, можно создать дополнительный столбец, скопировав данные из «Общей стоимости» и выполнив специальную вставку — только «Значений». Правда, данные не будут изменяться и при уменьшении или увеличении количества продуктов, потому для нашего примера этот способ не подходит.

Принцип действия функции

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

1. Сначала функция выполняет проверку верхней ячейки колонки «Наименования» первого массива с соответствующим столбцом второй таблицы. В данном случае информация («Баклажан») совпадает сразу, и к количеству «120» добавляется цена «80».
2. Проверка продолжается, и теперь проверяется значение «Петрушка». Функция продолжает сравнивать данные до тех пор, пока не доходит до 10-й строчки списка. После этого к количеству «12» добавляется соответствующая цена «270».
3. Процесс продолжается до тех пор, пока нужные сведения не будут найдены.

Из-за того, что при настройке работы функции был указан параметр «ЛОЖЬ», данные в колонках «Наименование» должны точно соответствовать друг другу. Даже если в столбцах будет изменение на 1 знак (например, «Помидоры 1 сорт» и «Помидоры 2 сорт»), сравнение получится не корректным и сопоставления данных не произойдет.

Применение функции в выпадающих списках

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

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

1. Поставить курсор в ячейку, где будет располагаться список.
2. Перейти на вкладку «Данные» и выбрать меню проверки данных.
3. Указать тип данных «Список», а в качестве источника — диапазон с наименованиями.
4. Нажать «ОК».
5. Перейти к ячейке, где должна появляться цена (обычно это соседняя с той, в которой находится выпадающий список).
6. Открыть «Мастер функций» нажатием на кнопку «fx» и выбрать «ВПР».
7. Указать среди аргументов ячейку, где находится выпадающий список, диапазон названий и цен, столбец №2 и «ЛОЖЬ».
8. Нажать «ОК».

Источник: ichip.ru

Функция ВПР в Эксель

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

Расшифровка

Большинство пользователей не знают, что аббревиатура «ВПР» расшифровывается как «Вертикальный Просмотр». На английском функция называется «VLOOKUP», которая означает «Vertical LOOK UP»

Как пользоваться функцией

Для того чтобы понять, как работает этот инструмент, необходимо выполнить следующие шаги.

1. Создайте таблицу, по которой можно будет сделать какой-нибудь поиск информации.

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

1. В поле «Искомая фамилия» введем какую-нибудь на выбор из тех, что есть в таблице.
2. Затем переходим на следующую ячейку и вызываем окно «Вставка функции».
3. Выбираем категорию «Полный алфавитный перечень».
4. Находим нужную нам функцию «ВПР». Для продолжения нажимаем на кнопку «OK».

1. Затем нас попросят указать «Аргументы функции»:
2. В поле «Искомое выражение» указываем ссылку на ячейку, в которой мы написали нужную нам фамилию.
3. Для того чтобы заполнить поле «Таблица», достаточно просто выделить все наши данные при помощи мышки. Ссылка подставится автоматически.
4. В графе «Номер столбца» указываем номер 2, поскольку в нашем случае имя находится во второй колонке.
5. Последнее поле может принимать значения «0» или «1» («ЛОЖЬ» и «ИСТИНА»). Если укажете «0», то редактор будет искать точное совпадение по заданным критериям. Если же «1» – то во время поиска не будут учитываться полные совпадения.
6. Для сохранения кликните на кнопку «OK».

1. В результате этого мы получили имя «Томара». То есть, всё правильно.

Теперь нужно воспользоваться этой же формулой и для остальных полей. Простое копирование ячейки при помощи [knopka]Ctrl[/knopka]+[knopka]C[/knopka] и [knopka]Ctrl[/knopka]+[knopka]V[/knopka] не подойдёт, поскольку у нас используются относительные ссылки и каждый раз будет меняться номер столбца.

Для того чтобы всё сработало правильно, нужно сделать следующее:

1. Кликните на ячейку с первой функцией.
2. Перейдите в строку ввода формул.
3. Скопируйте текст при помощи [knopka]Ctrl[/knopka]+[knopka]C[/knopka].

1. Сделайте активной следующее поле.
2. Снова перейдите в строку ввода формул.
3. Нажмите на горячие клавиши [knopka]Ctrl[/knopka]+[knopka]V[/knopka].

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

1. Затем меняем номер столбца на нужный. В нашем случае это 3. Нажимаем на клавишу [knopka]Enter[/knopka].

1. Благодаря этому мы видим, что данные из столбца «Год рождения» определились правильно.

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

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

То есть нумерация начинается не с начала листа, а с начала указанной области ячеек.

Как использовать функцию «ВПР» для сравнения данных

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

1. Добавим второй лист с точно такой же таблицей (копировали при помощи горячих клавиш [knopka]Ctrl[/knopka]+[knopka]C[/knopka] и [knopka]Ctrl[/knopka]+[knopka]V[/knopka]).
2. Повысим стажеров до «Младший сотрудник». Эта информация будет отправной точкой для сравнения.

1. Добавим ещё один столбец в нашу старую таблицу.

1. Переходим в первую клетку нового столбца и вводим там следующую формулу.

• $B$3:$B$11 – для поиска используются все значения первой колонки (применяются абсолютные ссылки);
• Лист2! – эти значения нужно искать на листе с указанным названием;
• $B$3:$E$11 – таблица, в которой нужно искать (диапазон ячеек);
• 4 – номер столбца в указанной области данных;
• ЛОЖЬ – искать точные совпадения.

1. Новая информация выведется в том месте, где мы указали формулу.
2. Результат будет следующим.

1. Теперь продублируйте эту формулу в остальные ячейки. Для этого нужно потянуть мышкой за правый нижний угол исходной клетки.

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

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

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

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

Функция «ВПР» и выпадающие списки

Рассмотрим примеры использования этих двух инструментов одновременно. Для этого нужно выполнить следующие действия.

1. Перейдите в ячейку, в которой происходит выбор фамилии.
2. Откройте вкладку «Данные».
3. Кликните на указанный инструмент и выберите пункт «Проверка данных».

1. В новом окне в графе «Тип данных» выберите пункт «Список».

1. После этого появится новое поле «Источник». Кликните туда.
2. Затем выделите первый столбец. Ссылка на ячейки подставится автоматически.
3. Для продолжения нажмите на «OK».

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

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

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

Ошибки #Н/Д

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

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

Отличие от функции «ГПР»