Впр функция excel расшифровка

Впр функция excel расшифровка

Автор: rf-webmaestro · Опубликовано 27.04.2018 · Обновлено 08.02.2019

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

Расшифровка

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    =ВПР($B$3:$B$11;Лист2!$B$3:$E$11;4;ЛОЖЬ)

    • $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 рекомендуют использовать упорядоченные данные);
    • аргументы функции имеют различный формат (например, что-то в текстовом виде, а остальное – в числовом);
    • в формуле присутствуют опечатки или лишние символы (пробелы, непечатаемые знаки, переносы и так далее).
    Читайте также:  Как конвертировать wav в mp3

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

    Данный инструмент практически точно такой же, только ищет по горизонтали. Более подробно о нем можно узнать на официальном сайте Microsoft.

    Заключение

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

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

    Видеоинструкция

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

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

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

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

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

    Стоимость материалов – в прайс-листе. Это отдельная таблица.

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

    1. Приведем первую таблицу в нужный нам вид. Добавим столбцы «Цена» и «Стоимость/Сумма». Установим денежный формат для новых ячеек.
    2. Выделяем первую ячейку в столбце «Цена». В нашем примере – D2. Вызываем «Мастер функций» с помощью кнопки «fx» (в начале строки формул) или нажав комбинацию горячих клавиш SHIFT+F3. В категории «Ссылки и массивы» находим функцию ВПР и жмем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и выбрать из выпадающего списка «Ссылки и массивы».
    3. Откроется окно с аргументами функции. В поле «Искомое значение» — диапазон данных первого столбца из таблицы с количеством поступивших материалов. Это те значения, которые Excel должен найти во второй таблице.
    4. Следующий аргумент – «Таблица». Это наш прайс-лист. Ставим курсор в поле аргумента. Переходим на лист с ценами. Выделяем диапазон с наименованием материалов и ценами. Показываем, какие значения функция должна сопоставить.
    5. Чтобы Excel ссылался непосредственно на эти данные, ссылку нужно зафиксировать. Выделяем значение поля «Таблица» и нажимаем F4. Появляется значок $.
    6. В поле аргумента «Номер столбца» ставим цифру «2». Здесь находятся данные, которые нужно «подтянуть» в первую таблицу. «Интервальный просмотр» — ЛОЖЬ. Т.к. нам нужны точные, а не приблизительные значения.

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

    Теперь найти стоимость материалов не составит труда: количество * цену.

    Функция ВПР связала две таблицы. Если поменяется прайс, то и изменится стоимость поступивших на склад материалов (сегодня поступивших). Чтобы этого избежать, воспользуйтесь «Специальной вставкой».

    1. Выделяем столбец со вставленными ценами.
    2. Правая кнопка мыши – «Копировать».
    3. Не снимая выделения, правая кнопка мыши – «Специальная вставка».
    4. Поставить галочку напротив «Значения». ОК.

    Формула в ячейках исчезнет. Останутся только значения.

    Быстрое сравнение двух таблиц с помощью ВПР

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

    1. В старом прайсе делаем столбец «Новая цена».
    2. Выделяем первую ячейку и выбираем функцию ВПР. Задаем аргументы (см. выше). Для нашего примера: . Это значит, что нужно взять наименование материала из диапазона А2:А15, посмотреть его в «Новом прайсе» в столбце А. Затем взять данные из второго столбца нового прайса (новую цену) и подставить их в ячейку С2.

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

    Функция ВПР в Excel с несколькими условиями

    До сих пор мы предлагали для анализа только одно условие – наименование материала. На практике же нередко требуется сравнить несколько диапазонов с данными и выбрать значение по 2, 3-м и т.д. критериям.

    Таблица для примера:

    Предположим, нам нужно найти, по какой цене привезли гофрированный картон от ОАО «Восток». Нужно задать два условия для поиска по наименованию материала и по поставщику.

    Дело осложняется тем, что от одного поставщика поступает несколько наименований.

    1. Добавляем в таблицу крайний левый столбец (важно!), объединив «Поставщиков» и «Материалы».
    2. Таким же образом объединяем искомые критерии запроса:
    3. Теперь ставим курсор в нужном месте и задаем аргументы для функции: . Excel находит нужную цену.

    Рассмотрим формулу детально:

    1. Что ищем.
    2. Где ищем.
    3. Какие данные берем.

    Функция ВПР и выпадающий список

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

    Сначала сделаем раскрывающийся список:

    1. Ставим курсор в ячейку Е8, где и будет этот список.
    2. Заходим на вкладку «Данные». Меню «Проверка данных».
    3. Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов.
    4. Когда нажмем ОК – сформируется выпадающий список.

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

    Читайте также:  Как сделать интересную открытку

    1. Открываем «Мастер функций» и выбираем ВПР.
    2. Первый аргумент – «Искомое значение» — ячейка с выпадающим списком. Таблица – диапазон с названиями материалов и ценами. Столбец, соответственно, 2. Функция приобрела следующий вид: .
    3. Нажимаем ВВОД и наслаждаемся результатом.

    Изменяем материал – меняется цена:

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

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

    Что такое ВПР в Excel?

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

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

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

    Совет: научиться правильно применять функцию VLOOKUP чуть сложнее, чем построить диаграмму в Excel; если планируется однократный поиск по единственной или нескольким строкам таблицы, проще будет воспользоваться комбинацией клавиш Ctrl + F и обработать данные вручную.

    Как пользоваться ВПР в Excel?

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

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

    Пример №1

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

    Решение:

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

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

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

    • Выделить нажатием ячейку, следующую за наименованием товара, и перейти на вкладку Excel «Формулы».

    • Щёлкнуть по кнопке «Вставить функцию» — самой крайней слева в открывшейся «ленте».

    • В открывшемся окошке вызвать верхний выпадающий список и выбрать кликом мыши пункт «Полный алфавитный перечень».

    • Пролистать перечень вниз, выделить строчку «ВПР» и щёлкнуть по кнопке «ОК».

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

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

    • Во второй строчке того же окна — зажав левую клавишу мыши, выделить исходную таблицу Excel целиком.

    • В третьей — порядковый номер столбца, в котором требуется осуществить поиск…

    • …Или сам столбец, выделив его аналогичным образом.

    • В четвёртой — указать, требуется точный поиск по таблице Excel (значение «0») или программа должна будет подобрать ближайший подходящий параметр (значение «1»). В этом примере и при решении большинства других задач, связанных с применением функции ВПР, нужны точные совпадения — следовательно. Необходимо вписать в текстовое поле «0», а затем нажать на «ОК».

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

    • В нашем примере в результате должно получиться не абсолютное значение, а дата. Чтобы привести данные в требуемый вид, необходимо щёлкнуть по «неправильной» ячейке правой кнопкой мыши и выбрать в открывшемся контекстном меню пункт «Формат ячеек»…

    • …После чего найти требуемый — «Дата» — и кликнуть по кнопке «ОК».

    • Готово! Ячейка приобрела приемлемый для работы вид — таким он и будет оставаться вплоть до нового изменения.

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

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

    • …После чего вставить её в нужную строку…

    • …Не забыв заменить номер столбца, в котором на этот раз должен быть осуществлён поиск — теперь это не «3», а «4».

    • Нажав на клавишу Enter и при необходимости вновь подкорректировав формат ячейки, пользователь получит заполненную краткую таблицу с достоверной информацией из большой сводной.
    Читайте также:  Айфон 5s греется и выключается

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

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

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

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

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

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

    Пример №2

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

    Решение:

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

    • Теперь нужно добавить к одной из таблиц Excel (обычно — более содержательной) новый столбец и назвать его в соответствии с переносимыми данными.

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

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

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

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

    • Щёлкнув по кнопке «ОК», пользователь получит в первой строке искомый результат.

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

    • Соответствия сохранятся даже при пересортировке строк в первой таблице.

    • А кроме того, в неё не будут включены «лишние» данные, отсутствующие во второй.

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

    Вопросы от новичков

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

    Как сравнить таблицы с помощью ВПР?

    Сравнение двух и более таблиц проводится почти так же, как добавление нового ряда данных:

    • Открыть обе таблицы, а при необходимости — перенести их на один лист.

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

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

    • Когда в ячейке появится требуемой значение, достаточно «растянуть» его на все позиции — данные подставятся автоматически.

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

    Как с помощью ВПР сделать выпадающий список?

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

    • Открыть исходную таблицу и способом, подробно описанным в первом примере, подготовить «результирующую».

    • Перейти на вкладку «Данные» и найти в «ленте» инструмент «Проверка данных».

    • Щёлкнув по нему, вызвать выплывающее меню, а далее — кликнуть по одноимённой строчке.

    • Выбрать в новом выплывающем меню «Тип данных» пункт «Список».

    • В строке «Источник» указать требуемый диапазон наименований, после чего нажать «ОК».

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

    Важно: при изменении параметров в исходной таблице соответствующие коррективы будут внесены Excel и в «результирующую».

    Подводим итоги

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

    Ссылка на основную публикацию
    Logitech deluxe 250 keyboard драйвер
    Ниже показаны совместимые с ОС Windows 7 драйвера для Logitech Deluxe 250 USB Keyboard. Каждый драйвер клавиатуры Logitech Deluxe 250...
    Adblock detector