Здесь в столбцах A-F и H есть значения или формулы, которые используют только значения на листе, а остальные столбцы используют функцию ВПР и значения столбца A (клиентский код) и столбец B (юрист) для получения данных из других таблиц.
Скопируйте таблицу с общими полями на новый лист и присвойте ей имя.
Чтобы открыть диалоговое окно Управление связями, нажмите кнопку данные > работа с данными > связей .
Для каждой из перечисленных связей обратите внимание на следующее:
Поле, связывающее таблицы (в диалоговом окне отображаются в круглых скобках). Это lookup_value формулы функции ВПР.
Имя связанной таблицы подстановки. Это table_array формулы функции ВПР.
Поле (столбец) в связанной таблице подстановки с данными, которые вы хотите добавить в новый столбец. Эти сведения не отображаются в диалоговом окне Управление связями. чтобы узнать, какое поле вы хотите извлечь, вам потребуется ознакомиться со связанной таблицей подстановки. Вы хотите отметить номер столбца (A = 1) – это col_index_num в формуле.
Чтобы добавить поле в новую таблицу, введите формулу ВПР в первом пустом столбце с помощью сведений, собранных в действии 3.
В нашем примере столбец G использует юрист ( lookup_value) для получения данных о тарифных курсах из четвертого столбца ( col_index_num = 4) из таблицы судебные тблатторнэйс ( table_array) с формулой = ВПР ([@Attorney], tbl_Attorneys, 4, ложь).
В формуле также могут использоваться ссылки на ячейки и ссылки на диапазоны. В нашем примере это было бы = ВПР (a2; “Юристы”! А:Д, 4, ложь).
Продолжайте добавлять поля, пока не будут созданы все нужные поля. Если вы пытаетесь подготовить книгу с функциями данных, которые используют несколько таблиц, измените источник данных функции данных в новой таблице.
Источник: support.office.com
Использование функции ВПР в программе Excel
Во время работы в Эксель нередко требуется перенести или скопировать определенную информацию из одной таблицы в другую. Выполнить подобную процедуру, конечно же, можно вручную, когда речь идет о небольших объемах данных. Но что делать, если нужно обработать большие массивы данных? В программе Microsoft Excel на этот случай предусмотрена специальная функция ВПР, которая автоматически все сделает в считанные секунды. Давайте посмотрим, как это работает.
Описание функции ВПР
ВПР – это аббревиатура, которая расшифровывается как “функция вертикального просмотра”. Английское название функции – VLOOKUP.
С помощью данного инструмента Excel можно отыскать совпадение значения в заданном столбце и “подтянуть” необходимые данные в выбранную ячейку из другой таблицы. Чтобы было понятнее, давайте разберем, как работает функция на практическом примере.
Применение функции ВПР на практике
Допустим, у нас есть таблица с продажами товаров. В ней отсутствуют данные по цене каждой позиции, однако, они есть в другой таблице рядом.
Наша задача – заполнить недостающие в основной таблице цены, чтобы мы могли посчитать итоговые суммы по продажам.
Порядок действий в данном случае следующий:
- Щелкаем по самой верхней ячейке столбца, значения которого мы хотим заполнить (в нашем случае – это C2). После этого нажимаем на кнопку “Вставить функцию” (fx) слева от строки формул.
- В окне вставки функции нам нужна категория “Ссылки и массивы”, в которой выбираем оператор “ВПР” и щелкаем OK.
- Теперь предстоит правильно заполнить аргументы функции:
- в поле “Искомое_значение” указываем адрес ячейки в основной таблице, по значению которой будет производиться поиск соответствия во второй таблице с ценами. Координаты можно прописать вручную, либо, находясь курсивом в поле для ввода информации просто кликнуть в самой таблице по нужной ячейке.
- переходим к аргументу “Таблица”. Здесь мы указываем координаты таблицы (или ее отдельной части), в которой будет выполняться поиск искомого значения. При этом важно, чтобы первый столбец указанного диапазона содержал именно те данные, по которым будет осуществляться поиск и сопоставление значений (в нашем случае – это наименования позиций). И, конечно же, в указанные координаты должны попадать ячейки с информацией, которая будет “подтягиваться” в основную таблицу (в нашем случае – это цены).
Примечание: Таблица может располагаться как на том же листе, что и основная, так и на других листах книги.
- Чтобы координаты, указанные в аргументе “Таблица” не сместились при возможных дальнейших корректировках данных, делаем их абсолютными, так как по умолчанию они являются относительными. Для этого выполняем выделение всей ссылки в поле и нажимаем кнопку F4. В результате перед всеми обозначениями строк и столбцов будут добавлены символы “$”.
- в поле аргумента “Номер_столбца” указываем порядковый номер столбца, значения которого нужно вставить в основную таблицу при совпадении искомого значения. В нашем случае это столбец с ценами, который занимает вторую позицию в указанной выше области (аргумент “Таблица”).
- в значении аргумента “Интервальный_просмотр” можно указать два значения:
- ЛОЖЬ (0) – результат будет выводиться только в случае точного совпадения;
- ИСТИНА (1) – будут выводиться результаты по приближенным совпадениям.
- мы выбираем первый вариант, так как нам важна предельная точность.
- Когда все готово, нажимаем OK.
- В выбранной ячейку, куда мы вставили функцию, автоматически вставилась требуемая цена.Причем, если мы изменим значение во второй таблице с ценами, так как данные взаимосвязаны посредством функции, то и в основной таблице произойдут соответствующие изменения.
- Чтобы автоматически заполнить аналогичными данными другие ячейки столбца, воспользуемся Маркером заполнения. Для этого наводим курсор мыши на нижний правый угол ячейки с результатом, когда появится черный плюсик, зажав левую кнопку мыши тянем его вниз до конца таблицы или до той ячейки, которую нужно заполнить.
- В итоге нам удалось получить в основной таблице все данные по ценам, а также посчитать итоговые суммы по продажам, что и требовалось сделать.
Заключение
Таким образом, функция ВПР – один из самых полезных инструментов при работе в Excel, когда нужно сопоставить данные двух таблиц и перенести значения из одной в другую при совпадении заданных значений. Правильное использование данной функции позволит сэкономить немало времени и сил, позволив автоматизировать процесс заполнения данных и исключив возможные ошибки из-за опечаток и т.д.
Источник: microexcel.ru
Функция ВПР в Excel для чайников и не только
Функция ВПР в Excel позволяет данные из одной таблицы переставить в соответствующие ячейки второй. Ее английское наименование – VLOOKUP.
Очень удобная и часто используемая. Т.к. сопоставить вручную диапазоны с десятками тысяч наименований проблематично.
Как пользоваться функцией ВПР в Excel
Допустим, на склад предприятия по производству тары и упаковки поступили материалы в определенном количестве.
Стоимость материалов – в прайс-листе. Это отдельная таблица.
Необходимо узнать стоимость материалов, поступивших на склад. Для этого нужно подставит цену из второй таблицы в первую. И посредством обычного умножения мы найдем искомое.
- Приведем первую таблицу в нужный нам вид. Добавим столбцы «Цена» и «Стоимость/Сумма». Установим денежный формат для новых ячеек.
- Выделяем первую ячейку в столбце «Цена». В нашем примере – D2. Вызываем «Мастер функций» с помощью кнопки «fx» (в начале строки формул) или нажав комбинацию горячих клавиш SHIFT+F3. В категории «Ссылки и массивы» находим функцию ВПР и жмем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и выбрать из выпадающего списка «Ссылки и массивы».
- Откроется окно с аргументами функции. В поле «Искомое значение» – диапазон данных первого столбца из таблицы с количеством поступивших материалов. Это те значения, которые Excel должен найти во второй таблице.
- Следующий аргумент – «Таблица». Это наш прайс-лист. Ставим курсор в поле аргумента. Переходим на лист с ценами. Выделяем диапазон с наименованием материалов и ценами. Показываем, какие значения функция должна сопоставить.
- Чтобы Excel ссылался непосредственно на эти данные, ссылку нужно зафиксировать. Выделяем значение поля «Таблица» и нажимаем F4. Появляется значок $.
- В поле аргумента «Номер столбца» ставим цифру «2». Здесь находятся данные, которые нужно «подтянуть» в первую таблицу. «Интервальный просмотр» – ЛОЖЬ. Т.к. нам нужны точные, а не приблизительные значения.
Нажимаем ОК. А затем «размножаем» функцию по всему столбцу: цепляем мышью правый нижний угол и тянем вниз. Получаем необходимый результат.
Теперь найти стоимость материалов не составит труда: количество * цену.
Функция ВПР связала две таблицы. Если поменяется прайс, то и изменится стоимость поступивших на склад материалов (сегодня поступивших). Чтобы этого избежать, воспользуйтесь «Специальной вставкой».
- Выделяем столбец со вставленными ценами.
- Правая кнопка мыши – «Копировать».
- Не снимая выделения, правая кнопка мыши – «Специальная вставка».
- Поставить галочку напротив «Значения». ОК.
Формула в ячейках исчезнет. Останутся только значения.
Быстрое сравнение двух таблиц с помощью ВПР
Функция помогает сопоставить значения в огромных таблицах. Допустим, поменялся прайс. Нам нужно сравнить старые цены с новыми ценами.
- В старом прайсе делаем столбец «Новая цена».
- Выделяем первую ячейку и выбираем функцию ВПР. Задаем аргументы (см. выше). Для нашего примера: . Это значит, что нужно взять наименование материала из диапазона А2:А15, посмотреть его в «Новом прайсе» в столбце А. Затем взять данные из второго столбца нового прайса (новую цену) и подставить их в ячейку С2.
Данные, представленные таким образом, можно сопоставлять. Находить численную и процентную разницу.
Функция ВПР в Excel с несколькими условиями
До сих пор мы предлагали для анализа только одно условие – наименование материала. На практике же нередко требуется сравнить несколько диапазонов с данными и выбрать значение по 2, 3-м и т.д. критериям.
Таблица для примера:
Предположим, нам нужно найти, по какой цене привезли гофрированный картон от ОАО «Восток». Нужно задать два условия для поиска по наименованию материала и по поставщику.
Дело осложняется тем, что от одного поставщика поступает несколько наименований.
- Добавляем в таблицу крайний левый столбец (важно!), объединив «Поставщиков» и «Материалы».
- Таким же образом объединяем искомые критерии запроса:
- Теперь ставим курсор в нужном месте и задаем аргументы для функции: . Excel находит нужную цену.
Рассмотрим формулу детально:
- Что ищем.
- Где ищем.
- Какие данные берем.
Функция ВПР и выпадающий список
Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена.
Сначала сделаем раскрывающийся список:
- Ставим курсор в ячейку Е8, где и будет этот список.
- Заходим на вкладку «Данные». Меню «Проверка данных».
- Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов.
- Когда нажмем ОК – сформируется выпадающий список.
Теперь нужно сделать так, чтобы при выборе определенного материала в графе цена появлялась соответствующая цифра. Ставим курсор в ячейку Е9 (где должна будет появляться цена).
- Открываем «Мастер функций» и выбираем ВПР.
- Первый аргумент – «Искомое значение» – ячейка с выпадающим списком. Таблица – диапазон с названиями материалов и ценами. Столбец, соответственно, 2. Функция приобрела следующий вид: .
- Нажимаем ВВОД и наслаждаемся результатом.
Изменяем материал – меняется цена:
Так работает раскрывающийся список в Excel с функцией ВПР. Все происходит автоматически. В течение нескольких секунд. Все работает быстро и качественно. Нужно только разобраться с этой функцией.
Источник: exceltable.com
Функция ВПР в Эксель
Автор: rf-webmaestro · Опубликовано 27.04.2018 · Обновлено 08.02.2019
Редактор Эксель – очень мощная программа для работы с таблицами. Иногда бывает так, что приходится работать с большим объемом данных. В таких случаях используются различные инструменты поиска информации. Функция «ВПР» в Excel – одна из самых востребованных для этой цели. Рассмотрим её более внимательно.
Расшифровка
Большинство пользователей не знают, что аббревиатура «ВПР» расшифровывается как «Вертикальный Просмотр». На английском функция называется «VLOOKUP», которая означает «Vertical LOOK UP»
Как пользоваться функцией
Для того чтобы понять, как работает этот инструмент, необходимо выполнить следующие шаги.
- Создайте таблицу, по которой можно будет сделать какой-нибудь поиск информации.
- Добавим несколько полей, которые будем использовать для демонстрации формул.
- В поле «Искомая фамилия» введем какую-нибудь на выбор из тех, что есть в таблице.
- Затем переходим на следующую ячейку и вызываем окно «Вставка функции».
- Выбираем категорию «Полный алфавитный перечень».
- Находим нужную нам функцию «ВПР». Для продолжения нажимаем на кнопку «OK».
- Затем нас попросят указать «Аргументы функции»:
- В поле «Искомое выражение» указываем ссылку на ячейку, в которой мы написали нужную нам фамилию.
- Для того чтобы заполнить поле «Таблица», достаточно просто выделить все наши данные при помощи мышки. Ссылка подставится автоматически.
- В графе «Номер столбца» указываем номер 2, поскольку в нашем случае имя находится во второй колонке.
- Последнее поле может принимать значения «0» или «1» («ЛОЖЬ» и «ИСТИНА»). Если укажете «0», то редактор будет искать точное совпадение по заданным критериям. Если же «1» – то во время поиска не будут учитываться полные совпадения.
- Для сохранения кликните на кнопку «OK».
- В результате этого мы получили имя «Томара». То есть, всё правильно.
Теперь нужно воспользоваться этой же формулой и для остальных полей. Простое копирование ячейки при помощи Ctrl + C и Ctrl + V не подойдёт, поскольку у нас используются относительные ссылки и каждый раз будет меняться номер столбца.
Для того чтобы всё сработало правильно, нужно сделать следующее:
- Кликните на ячейку с первой функцией.
- Перейдите в строку ввода формул.
- Скопируйте текст при помощи Ctrl + C .
- Сделайте активной следующее поле.
- Снова перейдите в строку ввода формул.
- Нажмите на горячие клавиши Ctrl + V .
Только таким способом редактор не изменит ссылки в аргументах функции.
- Затем меняем номер столбца на нужный. В нашем случае это 3. Нажимаем на клавишу Enter .
- Благодаря этому мы видим, что данные из столбца «Год рождения» определились правильно.
- После этого повторяем те же самые действия для последнего поля, но с корректировкой номера нужного столбца.
Принцип работы данной функции заключается в том, что Excel ищет фрагмент в указанной вами области и затем может вернуть любое значение с этой строки. От вас только требуется указать порядковый номер столбца в указанном диапазоне.
То есть нумерация начинается не с начала листа, а с начала указанной области ячеек.
Как использовать функцию «ВПР» для сравнения данных
Демонстрировать эту возможность будем при помощи двух таблиц. Представим, что у нас на втором листе появились обновлённые данные о сотрудниках. Наша задача состоит в том, чтобы узнать, что именно изменилось. Для этого нужно будет сделать следующее.
- Добавим второй лист с точно такой же таблицей (копировали при помощи горячих клавиш Ctrl + C и Ctrl + V ).
- Повысим стажеров до «Младший сотрудник». Эта информация будет отправной точкой для сравнения.
- Добавим ещё один столбец в нашу старую таблицу.
- Переходим в первую клетку нового столбца и вводим там следующую формулу.
=ВПР($B$3:$B$11;Лист2!$B$3:$E$11;4;ЛОЖЬ)
- $B$3:$B$11 – для поиска используются все значения первой колонки (применяются абсолютные ссылки);
- Лист2! – эти значения нужно искать на листе с указанным названием;
- $B$3:$E$11 – таблица, в которой нужно искать (диапазон ячеек);
- 4 – номер столбца в указанной области данных;
- ЛОЖЬ – искать точные совпадения.
- Новая информация выведется в том месте, где мы указали формулу.
- Результат будет следующим.
- Теперь продублируйте эту формулу в остальные ячейки. Для этого нужно потянуть мышкой за правый нижний угол исходной клетки.
- В итоге мы увидим, что написанная нами формула работает корректно, поскольку все новые должности скопировались как положено.
Теперь мы можем без труда определить, в записях какого сотрудника произошли изменения.
Единственный минус данной функции заключается в том, что «ВПР» не может работать с несколькими условиями.
В подобных случаях приходится использовать различные дополнительные столбцы, в которых объединяют информацию с нескольких колонок. А это выглядит некрасиво и не совсем удобно.
Функция «ВПР» и выпадающие списки
Рассмотрим примеры использования этих двух инструментов одновременно. Для этого нужно выполнить следующие действия.
- Перейдите в ячейку, в которой происходит выбор фамилии.
- Откройте вкладку «Данные».
- Кликните на указанный инструмент и выберите пункт «Проверка данных».
- В новом окне в графе «Тип данных» выберите пункт «Список».
- После этого появится новое поле «Источник». Кликните туда.
- Затем выделите первый столбец. Ссылка на ячейки подставится автоматически.
- Для продолжения нажмите на «OK».
- Благодаря этому у вас появится список всех фамилий, которые находятся в базе. Выберите что-нибудь из предложенного.
- Вы увидите, что данные автоматически изменились. При этом всё подставится согласно выбранной фамилии.
Сводные таблицы такой возможностью похвастаться не могут. Функция «ВПР» – очень мощный инструмент для поиска любой информации.
Ошибки #Н/Д
С подобной проблемой сталкиваются многие пользователи, которые только начинают пользоваться этой функцией. Как правило, ошибка «#Н/Д» возникает в следующих случаях:
- вы включили точный поиск (последний параметр 0/ЛОЖЬ), а это значение в таблице отсутствует;
- вы включили неточный поиск (последний параметр 1/ИСТИНА), но при этом данные не отсортированы по возрастанию (если используется приблизительный поиск, то разработчики Microsoft рекомендуют использовать упорядоченные данные);
- аргументы функции имеют различный формат (например, что-то в текстовом виде, а остальное – в числовом);
- в формуле присутствуют опечатки или лишние символы (пробелы, непечатаемые знаки, переносы и так далее).
Отличие от функции «ГПР»
Данный инструмент практически точно такой же, только ищет по горизонтали. Более подробно о нем можно узнать на официальном сайте Microsoft.
Заключение
В данной статье мы пошагово рассмотрели, как пользоваться функцией «ВПР» в редакторе Excel. Кроме этого, было показано несколько примеров. Данная инструкция ориентирована на новичков (чайников).
Если у вас что-то не получается, возможно, вы неправильно указываете аргументы функции. Например, неправильный номер столбца или неверный диапазон ячеек. Попробуйте повторить описанные выше действия заново, но на этот раз более внимательно. И у вас всё получится.
Видеоинструкция
Для тех, кому пошаговая инструкция не помогла, мы приготовили специальное видео, в котором всё рассказывается еще более детально с дополнительными замечаниями. Возможно, так вам будет проще разобраться.
Источник: os-helper.ru
Как работает функция ВПР
Здравствуй уважаемый читатель!
В этой статье я хочу описать, наверное, одну из наиболее полезных функций в Excel – это функция ВПР (VLOOKUP). О функции можно сказать многое, но самое первое, что стоить отметить, это то, что функция относится к сложным и наименее понятной.
В статье я постараюсь простым и доступным языком описать, как работает функция ВПР, а также на примерах показать ее особенности, описание и синтаксис.
Итак, что же в сущности это за функция, что она делает, и как она пишется. Самое простое объяснение, о работе функции ВПР, она ищет в перечне значений, по заданному вами критерию, уникальному идентификатору, определенную информацию, которая с ним связана.
Если розшифровать название функции ВПР, то с первой буквой станет понятно, как работает, эта функция, В – означает «вертикальная», то есть она ищет значения в вертикальных списках массивов, а вот для горизонтальных списков у нас будет функция ГПР. Впервые функция ВПР стала доступна нам с Excel 2000. Функция ВПР в Excel имеет следующий синтаксис:
=ВПР(_искомое значение_;_таблица_; _номер столбика_; _[интервальный просмотр]_), где:
- искомое_значение – это именно то значение, которое нам нужно искать, а это может быть любое значение: число, дата, текст, ссылка на ячейку, которая содержит нужное значение или значение, получаемое другой формулой;
- таблица – это два и больше столбика с разнообразными данными, кстати, регистр символов в поиске, функцией не учитывается;
- номер_столбца – это номер столбика в указанном диапазоне, из которого будет получено значение, которое находится в найденной строке;
- интервальный_просмотр – этот параметр определяет, что же именно будем искать, для точного совпадения аргумент будет равен «ЛОЖЬ» (FALSE) или приблизительное совпадение, аргумент станет равным ИСТИНА (TRUE). Этот параметр не является обязательным, но тем не менее он важен. В примерах, далее, я продемонстрирую, как создать формулы для точного и приблизительного совпадения.
Теперь на примере рассмотрим, как произвести поиск, каких-либо данных на другом листе, так как на практике, функцию ВПР очень редко используют для поиска на текущем листе:
Формула ищет текст «GM», в столбике А на текущем листе. Совет! При использовании аргумента «таблица», желательно использовать, такой вариант, как абсолютные ссылки (это адрес ячейки со знаком $). В этом случае диапазон поиска станет закреплённым и не изменится при копировании формулы.
Рассмотрим пример поиска значений, как работает функция ВПР в другой рабочей книге:
=ВПР(”GM”;[Путь к файлу]База!А2:В10;2)
Как видите ничего сложного, просто усложнился аргумент «таблица», теперь к нему прикреплена ссылка на файл с его именем.
Совет! Ввод формулы лучше делать при включённых двух файлах, на одном вы вводите функцию ВПР, а когда дойдете до аргумента «таблица», перейдете на второй файл и мышкой выделите нужный вам диапазон для поиска.
Еще можно рассмотреть пример, когда точное значение нам неизвестно мы будем использовать в формуле ВПР символы подстановки. Иногда нам нужно найти не что-то конкретное, а нечто обладающее общими признаками, как вариант у нас есть много диванов разных моделей, как и прочей мебели, но выбрать нам надо только их.
Функция ВПР, при подстановке,может использовать такие символы:
- «?» (знак вопроса) – позволит заменить один любой символ;
- «*» (звёздочка) – заменит любое количество и последовательность символов.
Совет! Для того что бы функция ВПР, корректно работала нужно в качестве четвертого аргумента использовать параметр «ЛОЖЬ». Ну, если мы уже затронули тему точного или приближенного совпадения в синтаксисе функции ВПР, то давайте ее рассмотрим поподробнее:
- если аргумент «интервальный просмотр» равен «ЛОЖЬ», в таком случае формула ищет точное совпадение с аргументом «искомое значение». Если формула встретит два и более значения, отвечающих аргументу «искомое значение», то будет выбрано первое из списка, в случае, когда совпадение не найдены, формула вернет ошибку #Н/Д;
- если же аргумент «интервальный просмотр» имеет значение «ИСТИНА» формула будет искать приблизительное совпадение, точнее будет, что функция сначала поищет точное совпадение, а уже потом, не найдя его, выберет приблизительное.
Совет! В случае, когда аргумент «интервальный просмотр» ровняется «ИСТИНА» или же не указан, значение в первом столбике нужно отсортировать по возрастанию, от меньшего к большому. Иначе имеется возможность ошибочного результата от функции ВПР.
Рассмотрим, как работает функция ВПР, когда производится поиск по точному. Для примера, попытаемся найти, какой автомобиль движется со скоростью 200 км/час. Я думаю, такая формула не будет для вас сложной:
=ВПР(200;$A$2:$B$15;2;ЛОЖЬ)
Несмотря на то, что значений 200 у нас несколько, получили только одно, так как при точном совпадении функции ВПР, система использует только первое значение которое было найдено в указанном диапазоне. Теперь испытаем, работу функции ВПР для поиска приблизительного совпадения значений. Поищем, какой автомобиль ездит со скоростью 260 км/час. Первое что вы делаете в случае, когда «интервальный просмотр» равняется «ИСТИНА» — вы выполняете сортировку вашего диапазона значений по первому столбику по порядку его возрастания. Это необходимо и важно, поскольку функция ВПР находит следующее наибольшее значение от заданного условия, а после поиск прекращается. Если же вы не последуете совету по сортировке, итогом будет сообщение об ошибке #Н/Д или другие странные результаты.
Для поиска используем ВПР следующего вида:
=ВПР(260;$A$2:$B$15;2;ИСТИНА)
Итак, наша формула вернула найденное значение 240, хотя у нас есть и значение 270 и это значение вроде как ближе, но особенность того, как работает функция ВПР, это то что при использовании точного совпадения она ищет самое большое значение в списке, но не превышающие его. Я думаю примеры, которые я рассматривал в статье, помогут вам понять всё о работе функции ВПР, как ее используют и для чего ее нужно использовать.
Хотя есть и множество более продвинутых примеров, которые мы рассмотрим в других статьях.
А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!
Не забудьте поблагодарить автора!
Без сомнения, бережливость лучше расточительности, но также неоспоримо, что она хуже полезной затраты.
Генри Форд
Источник: topexcel.ru
Функция ВПР в программе Microsoft Excel
Работа с обобщающей таблицей подразумевает подтягивание в неё значений из других таблиц. Если таблиц очень много, ручной перенос заберет огромное количество времени, а если данные постоянно обновляются, то это уже будет сизифов труд. К счастью, существует функция ВПР, которая предлагает возможность автоматической выборки данных. Давайте рассмотрим конкретные примеры работы этой функции.
Определение функции ВПР
Название функции ВПР расшифровывается, как «функция вертикального просмотра». По-английски её наименование звучит – VLOOKUP. Эта функция ищет данные в левом столбце изучаемого диапазона, а затем возвращает полученное значение в указанную ячейку. Попросту говоря, ВПР позволяет переставлять значения из ячейки одной таблицы, в другую таблицу. Выясним, как пользоваться функцией VLOOKUP в Excel.
Пример использования ВПР
Взглянем, как работает функция ВПР на конкретном примере.
У нас имеется две таблицы. Первая из них представляет собой таблицу закупок, в которой размещены наименования продуктов питания. В следующей колонке после наименования расположено значение количества товара, который требуется закупить. Далее следует цена. И в последней колонке – общая стоимость закупки конкретного наименования товара, которая рассчитывается по вбитой уже в ячейку формуле умножения количества на цену. А вот цену нам как раз и придется подтянуть с помощью функции ВПР из соседней таблицы, которая представляет собой прайс-лист.
Кликаем по верхней ячейке (C3) в столбце «Цена» в первой таблице. Затем, жмем на значок «Вставить функцию», который расположен перед строкой формул.
После этого открывается окно, в которое нужно вставить аргументы функции. Жмем на кнопку, расположенную справа от поля ввода данных, чтобы приступить к выбору аргумента искомого значения.
Так как у нас искомое значение для ячейки C3, это «Картофель», то и выделяем соответствующее значение. Возвращаемся к окну аргументов функции.
Точно таким же образом кликаем по значку справа от поля ввода данных, для выбора таблицы, откуда будут подтягиваться значения.
Для того, чтобы выбранные значения сделать из относительных абсолютными, а это нам нужно, чтобы значения не сдвинулись при последующем изменении таблицы, просто выделяем ссылку в поле «Таблица», и жмем на функциональную клавишу F4. После этого к ссылке добавляются знаки доллара и она превращается в абсолютную.
В следующей графе «Номер столбца» нам нужно указать номер того столбца, откуда будем выводить значения. Этот столбец располагается в выделенной выше области таблицы. Так как таблица состоит из двух столбцов, а столбец с ценами является вторым, то ставим номер «2».
В последней графе «Интервальный просмотр» нам нужно указать значение «0» (ЛОЖЬ) или «1» (ИСТИНА). В первом случае, будут выводиться только точные совпадения, а во втором — наиболее приближенные. Так как наименование продуктов – это текстовые данные, то они не могут быть приближенными, в отличие от числовых данных, поэтому нам нужно поставить значение «0». Далее, жмем на кнопку «OK».
Как видим, цена картофеля подтянулась в таблицу из прайс-листа. Чтобы не проделывать такую сложную процедуру с другими товарными наименованиями, просто становимся в нижний правый угол заполненной ячейки, чтобы появился крестик. Проводим этим крестиком до самого низа таблицы.
Таким образом мы подтянули все нужные данные из одной таблицы в другую, с помощью функции ВПР.
Как видим, функция ВПР не так сложна, как кажется на первый взгляд. Разобраться в её применении не очень трудно, зато освоение этого инструмента сэкономит вам массу времени при работе с таблицами.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Источник: lumpics.ru
Adblock
detector
Причем, если мы изменим значение во второй таблице с ценами, так как данные взаимосвязаны посредством функции, то и в основной таблице произойдут соответствующие изменения.
Здравствуй уважаемый читатель!
Функция ВПР в Excel имеет следующий синтаксис:
Совет! При использовании аргумента «таблица», желательно использовать, такой вариант, как абсолютные ссылки (это адрес ячейки со знаком $). В этом случае диапазон поиска станет закреплённым и не изменится при копировании формулы. 
Ну, если мы уже затронули тему точного или приближенного совпадения в синтаксисе функции ВПР, то давайте ее рассмотрим поподробнее:
Теперь испытаем, работу функции ВПР для поиска приблизительного совпадения значений. Поищем, какой автомобиль ездит со скоростью 260 км/час. Первое что вы делаете в случае, когда «интервальный просмотр» равняется «ИСТИНА» — вы выполняете сортировку вашего диапазона значений по первому столбику по порядку его возрастания. Это необходимо и важно, поскольку функция ВПР находит следующее наибольшее значение от заданного условия, а после поиск прекращается. Если же вы не последуете совету по сортировке, итогом будет сообщение об ошибке #Н/Д или другие странные результаты.
Я думаю примеры, которые я рассматривал в статье, помогут вам понять всё о работе функции ВПР, как ее используют и для чего ее нужно использовать.
