Letysite.ru

IT Новости с интернет пространства
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Использование функции впр в excel примеры

Функция ВПР в программе Microsoft Excel

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

Определение функции ВПР

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

Пример использования ВПР

Взглянем, как работает функция ВПР на конкретном примере.

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

    Кликаем по верхней ячейке (C3) в столбце «Цена» в первой таблице. Затем, жмем на значок «Вставить функцию», который расположен перед строкой формул.

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

Так как у нас искомое значение для ячейки C3, это «Картофель», то и выделяем соответствующее значение. Возвращаемся к окну аргументов функции.

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

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

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

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

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

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Использование функции ВПР в программе Excel

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

    Описание функции ВПР

    ВПР – это аббревиатура, которая расшифровывается как “функция вертикального просмотра”. Английское название функции – VLOOKUP.

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

    Применение функции ВПР на практике

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

    Наша задача – заполнить недостающие в основной таблице цены, чтобы мы могли посчитать итоговые суммы по продажам.

    Порядок действий в данном случае следующий:

    1. Щелкаем по самой верхней ячейке столбца, значения которого мы хотим заполнить (в нашем случае – это C2). После этого нажимаем на кнопку “Вставить функцию” (fx) слева от строки формул.
    2. В окне вставки функции нам нужна категория “Ссылки и массивы”, в которой выбираем оператор “ВПР” и щелкаем OK.
    3. Теперь предстоит правильно заполнить аргументы функции:
      • в поле “Искомое_значение” указываем адрес ячейки в основной таблице, по значению которой будет производиться поиск соответствия во второй таблице с ценами. Координаты можно прописать вручную, либо, находясь курсивом в поле для ввода информации просто кликнуть в самой таблице по нужной ячейке.
      • переходим к аргументу “Таблица”. Здесь мы указываем координаты таблицы (или ее отдельной части), в которой будет выполняться поиск искомого значения. При этом важно, чтобы первый столбец указанного диапазона содержал именно те данные, по которым будет осуществляться поиск и сопоставление значений (в нашем случае – это наименования позиций). И, конечно же, в указанные координаты должны попадать ячейки с информацией, которая будет “подтягиваться” в основную таблицу (в нашем случае – это цены).
        Примечание: Таблица может располагаться как на том же листе, что и основная, так и на других листах книги.
      • Чтобы координаты, указанные в аргументе “Таблица” не сместились при возможных дальнейших корректировках данных, делаем их абсолютными, так как по умолчанию они являются относительными. Для этого выполняем выделение всей ссылки в поле и нажимаем кнопку F4. В результате перед всеми обозначениями строк и столбцов будут добавлены символы “$”.
      • в поле аргумента “Номер_столбца” указываем порядковый номер столбца, значения которого нужно вставить в основную таблицу при совпадении искомого значения. В нашем случае это столбец с ценами, который занимает вторую позицию в указанной выше области (аргумент “Таблица”).
      • в значении аргумента “Интервальный_просмотр” можно указать два значения:
        • ЛОЖЬ (0) – результат будет выводиться только в случае точного совпадения;
        • ИСТИНА (1) – будут выводиться результаты по приближенным совпадениям.
        • мы выбираем первый вариант, так как нам важна предельная точность.
      • Когда все готово, нажимаем OK.
    4. В выбранной ячейку, куда мы вставили функцию, автоматически вставилась требуемая цена.Причем, если мы изменим значение во второй таблице с ценами, так как данные взаимосвязаны посредством функции, то и в основной таблице произойдут соответствующие изменения.
    5. Чтобы автоматически заполнить аналогичными данными другие ячейки столбца, воспользуемся Маркером заполнения. Для этого наводим курсор мыши на нижний правый угол ячейки с результатом, когда появится черный плюсик, зажав левую кнопку мыши тянем его вниз до конца таблицы или до той ячейки, которую нужно заполнить.
    6. В итоге нам удалось получить в основной таблице все данные по ценам, а также посчитать итоговые суммы по продажам, что и требовалось сделать.
    Читать еще:  Как улучшить качество видеозаписи

    Заключение

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

    Функция ВПР в Excel

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

    Синтаксис и описание функции ВПР в Excel

    Итак, так как второе название этой статьи «Функция ВПР в Excel для чайников», начнем с того что узнаем, что же такое функция ВПР и что она делает? Функция ВПР на английском VLOOKUP, ищет указанное значение и возвращает соответствующее значение из другого столбца.

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

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

    Функция ВПР доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

    Синтаксис функции ВПР выглядит следующим образом:

    Как видите, функция ВПР имеет 4 параметра или аргумента. Первые три параметра обязательные, последний — необязательный.

    1. искомое_значение – это значение для поиска.

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

    • Поиск числа : =ВПР(40; A2:B15; 2) — формула будет искать число 40.
    • Поиск текста : =ВПР(«яблоки»; A2:B15; 2) — формула будет искать текст «яблоки». Обратите внимание, что вы всегда включаете текстовые значения в «двойные кавычки».
    • Поиск значения из другой ячейки : =ВПР(C2; A2:B15; 2) — формула будет искать значение в ячейке C2.
    1. таблица — это два или более столбца данных.

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

    Итак, наша формула =ВПР(40; A2:B15; 2) будет искать «40» в ячейках от A2 до A15, потому что A — это первый столбец таблицы A2: B15.

    1. номер_столбца — номер столбца в таблице, из которой должно быть возвращено значение в соответствующей строке.

    Самый левый столбец в указанной таблице равен 1, второй столбец — 2, третий — 3 и т. д.

    Итак, теперь вы можете прочитать всю формулу =ВПР(40; A2:B15; 2). Формула ищет «40» в ячейках от A2 до A15 и возвращает соответствующее значение из столбца B (потому что B является вторым столбцом в указанной таблице A2:B15).

    4. интервальный_просмотр определяет, ищете ли вы точное соответствие (ЛОЖЬ) или приблизительное соответствие (ИСТИНА или опущено). Этот последний параметр является необязательным, но очень важным.

    Функция ВПР в Excel примеры

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

    Функция ВПР на разных листах

    На практике формулы ВПР редко используются для поиска данных на одном листе. Чаще всего вам придется искать и вытаскивать соответствующие данные с другого листа.

    Чтобы использовать функцию ВПР с другого листа Excel, вы должны ввести имя рабочего листа и восклицательный знак в аргументе таблица перед диапазоном ячеек, например, =ВПР(40;Лист2!A2:B15;2). Формула указывает, что диапазон поиска A2:B15 находится в Лист2.

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

    Формула, которую вы видите на изображении ниже, ищет текст в ячейке А2 (« Продукт 3 ») в столбце A (1-й столбец диапазона поиска A2:B9) на листе « Цены »:

    Функция ВПР в Excel – Функция ВПР на разных листах

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

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

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

    Чтобы создать именованный диапазон, просто выберите ячейки и введите любое имя в поле « Имя », слева от панели « Формула ».

    Функция ВПР в Excel – Присвоение имени диапазону

    Теперь вы можете написать следующую формулу ВПР, чтобы получить цену Продукта 1:

    Функция ВПР в Excel – Пример функции ВПР с именем диапазона

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

    Если вы преобразовали диапазон ячеек в полнофункциональную таблицу Excel (вкладка «Вставка» —> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, а Microsoft Excel автоматически добавит имена колонок или имя таблицы в формулу:

    Функция ВПР в Excel – Пример функции ВПР с именем таблицы

    Полная формула может выглядеть примерно так:

    Читать еще:  Расширенный фильтр в excel

    или даже =ВПР(«Продукт 1»;Таблица6;2).

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

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

    Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:

    Функция ВПР в Excel – Таблица исходных данных

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

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

    Итак на листе « Цены » вставляем столбец и в ячейке А2 вводим следующую формулу:

    При помощи этой формулы мы сцепляем значение столбца « Продукт » и « Тип ». Заполняем все ячейки.

    Теперь таблица для поиска выглядит следующим образом:

    Функция ВПР в Excel – Добавление вспомогательного столбца
    1. Теперь в ячейке С2 на листе « Продажи » напишем следующую формулу ВПР:

    Заполняем для остальных ячеек и в результате получаем цены для каждого продукта в соответствии с типом:

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

    Теперь разберем ошибки функции ВПР.

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

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

    Как работать с функцией ВПР в Эксель: инструкция для новичков

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

    Использование ВПР в программе Excel

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

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

    Первая – это список предметов, единицы их измерения и количество.

    Вторая – содержит тот же список, но в ней ещё есть цена за 1 штуку.

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

    Этапы работы (инструкция):

    1. Для начала в первую Excel таблицу добавляются два столбца: «Цена за 1 шт.» и «Общая сумма».
    2. Отметить верхнее поле в новом.
    3. Выбрать раздел формулы, и нажать «Вставить функцию».
    4. Из предложенных категорий Excel отметить «Ссылки и массивы».
    5. Найти ВПР, и нажать «ОК».
    6. Заполнить открывшееся окно «Аргументы».

    – это товары из первой таблицы, которые необходимо будет определить во второй. Их значение выставляется таким образом: X: Y, где Х – это адрес первой ячейки столбика с товарами, а Y – последней. В рассматриваемой это А2 и А5.

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

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

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

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

    Интервальный просмотр – логическое значение Excel, где точно это ЛОЖЬ, а приближённо – ИСТИНА. Если пользователю нужны точные, он должен написать «ЛОЖЬ».

    В конечном итоге, окно «Аргументы» выглядит так:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    2. Нажать на любое свободное место и самостоятельно ввести, что нужно найти (ЛасточкаГелевая ручка).
    3. Выбрать ячейку где будет отображен результат и заполнить Аргументы функции.

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

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

    Читать еще:  Как сделать большую таблицу в excel

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

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

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

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

    Чтобы сделать выпадающий список из существующего нужно следовать инструкции:

    1. Выбрать поле, в котором будет сформированы показатели. Например, Е2.
    2. Зайти в раздел «Данные», и выбрать «Проверка данных».
    3. Установить тип данных, как список.

    1. В появившуюся строку «Источник» ввести информацию (выделить с первой до последней ячейки – А2:А5).

    Выпадающий список готов.

    Теперь с помощью функции ВПР нужно добавить возможность просмотра цены, при выборе товара. Как это работает в Эксель? (Инструкция).

    1. Создать новое поле с названием «Цена».
    2. Вставить аргументы.

    – ячейка Excel, в которой находится выпадающий список (Е2).

    – выделенный фрагмент с предметами и ценами (А2-В5).

    Номер столбца – 2 (в нём находятся цены).

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

    После подтверждения команды можно пользоваться списком и просмотром цены.

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

    ВПР (функция ВПР)

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

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

    Совет: Чтобы получить дополнительную справку о функции ВПР, просмотрите эти видео с YouTube от экспертов сообщества Excel!

    Самая простая функция ВПР означает следующее:

    = ВПР (необходимые условия для поиска, номер столбца в диапазоне, содержащий возвращаемое значение, возвращаемое приближенное или точное соответствие — обозначено как 1/истина или 0/ложь).

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

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

    ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])

    = ВПР (A2; A10: C20; 2; ИСТИНА)

    = ВПР (a2; сведения о клиенте)! А:Ф, 3, ЛОЖЬ)

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

    Например, если Таблица-массив охватывает ячейки B2: D7, то lookup_value должен находиться в столбце B.

    Искомое_значение может являться значением или ссылкой на ячейку.

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

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

    Номер столбца (начиная с 1 для самого левого столбца table_array), содержащего возвращаемое значение.

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

    Приближенное соответствие: 1/истина предполагает, что первый столбец в таблице отсортирован либо в числовом формате, либо в алфавитном порядке, а затем будет искать ближайшее значение. Это способ по умолчанию, если не указан другой. Например, = ВПР (90; a1: B100; 2; истина).

    Точное совпадение: 0/ложь осуществляет поиск точного значения в первом столбце. Например, = ВПР («Смит»; a1: B100; 2; ложь).

    Начало работы

    Для построения синтаксиса функции ВПР вам потребуется следующая информация:

    Значение, которое вам нужно найти, то есть искомое значение.

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

    Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона задано значение B2: D11, число B должно быть первым столбцом, а в качестве второго — «C» и т. д.

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

    Теперь объедините все перечисленное выше аргументы следующим образом:

    = ВПР (искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением, приближенное соответствие (истина) или точное совпадение (ложь)).

    Примеры

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

    Пример 1

    Пример 2

    Пример 3

    Пример 4

    Пример 5

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

    Здесь в столбцах 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, ложь).

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

    Ссылка на основную публикацию
    Adblock
    detector