ifempty

    Урок 5. Функция ВПР

    Оглавление

    Введение

    Функция ВПР с интервальным просмотром 0 (ЛОЖЬ);

    Функция ВПР с интервальным просмотром 1 (ИСТИНА);

    Файлы для скачивания

     

    Введение

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

    У функции есть четыре параметра:

    1. Искомое значение - значение, по которому будет осуществляться поиск;
    2. Таблица - таблица или диапазон ячеек, среди которых, по левому крайнему столбцу, будет осуществляться поиск искомого значения;
    3. Номер столбца - порядковый номер столбца таблицы, указанной выше, значение которого нужно перенести в текущую таблицу;
    4. Интервальный просмотр - переменная, которая отвечает за механизм сопоставления искомого значения и левого крайнего столбца таблицы. Может принимать значения 0 или 1 (ЛОЖЬ или ИСТИНА). Если указать 0 - то сопоставляться искомое значение в таблице будет по полному совпадению. Если указать 1 - то поиск будет осуществляться приближенно к искомому значению. 

     ВПР рис1

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

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

    Откройте лист - Интервальный просмотр 0. На листе даны две таблицы. В Таблице1 находятся ID и наименования товаров. В Таблице2 известен ID, но неизвестно наименование. Наименование нужно установить в ячейку E3 из Таблицы1 с помощью функции ВПР. 

    Настройки функции будут выглядеть так:

     ВПР рис2

    Рисунок 2. Настройки функции ВПР с интервальным просмотром 0

    Красной рамкой указано искомое значение, синей рамкой - таблица (в данном случае это Таблица1). Номер_столбца  - 2, потому что Товар - второй столбец в Таблице1. Интервальный_просмотр в данном случае 0 (ЛОЖЬ)

    Теперь откройте лист Интервальный просмотр 1. На листе также даны две таблицы. В Таблице1 указана информация о скидках - порог заказа и размер скидки для каждого порога заказа. Таблицу1 можно прочитать так:

    Если заказали от 0 до 4 единиц товара, то скидка будет в размере 0%,если заказали от 5 до 9 единиц товара, то скидка будет в размере 10%. Если заказали от 10 до 14 единиц товара, то размер скидки будет 15%. Если заказали от 15 и выше единиц товара, то размер скидки будет 20%.

    В Таблице2 дан перечень заказов и процент скидки. Процент скидки был определен с помощью функции ВПР из Таблицы1. Настройки функции в ячейке Е3 выглядят следующим образом:

     ВПР рис3

    Рисунок 3. Настройки функции ВПР с интервальным просмотром 1

    Значение ячейки D3  - это искомое значение. Данное искомое значение функция будет искать в левом крайнем столбце Таблицы1. Размер скидки в Таблице1 находится во втором столбце, поэтому номер_столбца - 2. Интервальный просмотр -1. 

    В данном случае, функция в Таблице1 по левому крайнему столбцу находит значение ближайшее к искомому значению в сторону уменьшения.  Например, если поступившие заказы в Таблице2 - 10, то ближайший к нему порог заказа в Таблице1 - 10, и размер скидки соответственно - 15%. Если поступившие заказы в Таблице2  - 12, то ближайший к нему порог заказа в сторону уменьшения также будет 10 и размер скидки, соответственно  - 15%.

    У функции ВПР есть три ограничения:

    1. Поиск искомого значения производится по левому крайнему столбцу таблицы;
    2. Функция находит только первый элемент сверху вниз, который соответствует искомому значению и затем останавливает поиск;
    3. Крайний левый столбец таблицы, в которой будет производиться поиск искомого значения должен быть выстроен по возрастанию. Это относится только к функции ВПР с интервальным просмотром 1

     

    Функция ВПР с интервальным просмотром 0 (ЛОЖЬ)

    Скачайте файл Функция ВПР с интервальным просмотром 0 (без решения).xlsx по ссылке ниже.

    Отройте лист  - Задача без решения. Если у вас стоит защищенный просмотр, то нужно нажать на кнопку Разрешить редактирование в правом верхнем углу окна, как показано на рисунке 4.

     ВПР рис4

    Рисунок 4. Разрешение редактирования файла

    Задача:

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

     ВПР рис5

    Рисунок 5. Задача по практикуму 1

     

    Предварительный разбор:

    Функцию нужно будет вызывать в ячейке B2. Но прежде чем вызывать функцию, таблицу на листе Список сотрудников нужно изменить: столбец Табельный номер нужно скопировать и вставить слева от столбца Фамилия, как показано на рисунке 6. 

     ВПР рис6

    Рисунок 6. Установка столбца Табельный номер перед столбцом Фамилия

    Это нужно сделать, потому что искомое значение функция ищет по левому крайнему столбцу таблицы. Искомым значением в данном случае будет табельный номер в ячейке А2 на листе Задача без решения.. В качестве таблицы будет указан диапазон от А2 до D24 на листе Список сотрудников. Так как для начала нужно найти фамилию сотрудника, то номер столбца будет 2. Потому что на листе Список сотрудников столбец Фамилия является вторым по порядку. Обратите внимание, что речь не идет о столбцах листа, важно учитывать порядковый номер столбца таблицы. Интвервальный просмотр  - 0, так как нам нужно полное совпадение табельных номеров.

      ВПР рис7

    Рисунок 7. Обозначение цветом параметров функции ВПР

     

    Вызов функции ВПР:

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

    1. В поле Искомое_значение указываем: $A2
    2. В поле Таблица указываем: 'Список сотрудников'!$A$2:$F$24
    3. В поле Номер_столбца указываем: 2
    4. В поле Интервальный просмотр указываем 0

      ВПР рис8

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

    После того, как закончите ввод данных нажмите ОК. У вас должно получиться как на рисунке 9:

     ВПР рис9

     Рисунок 9. Результат вызова фукнции ВПР.

    Далее с помощью маркера автозаполнения функцию в ячейке B2 нужно протянуть вправо. У вас должно получиться как на рисунке 10:

     ВПР рис10

     Рисунок 10. Результат вызова фукнции ВПР

    Обратите внимание, что вместо имени и отчества отображаются фамилии. Это связано с тем, что Номер_столбца в функции указан 2. Тогда как на листе Список сотрудников порядковый номер столбца Имя - 3, а Отчество - 4. Поэтому в ячейке С2 внутри функции Номер_столбца нужно указать - 3, а в ячейке  D2 внутри функции Номер_столбца нужно указать - 4.

      ВПР рис11

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

    Чтобы изменить Номер_столбца внутри функции встаньте на ячейку С2 и нажмите на нопку ƒx. У вас откроется окно Аргументы функции, где в поле Номер_столбца нужно указать 3, как показано на рисунке 12.. И нажать на кнопку ОК.

     ВПР рис12

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

    У вас должно получиться как на рисунке 13. Далее то же самое проделайте с ячейкой D2, только вместо 3 в поле Номер_столбца укажите 4. В результате у вас должно получиться как на рисунке 14.

     ВПР рис13

     Рисунок 13. Результат вызова функции ВПР 

     ВПР рис14

    Рисунок 14. Результат вызова функции ВПР 

    Далее выделите ячейки В2, С2 и D2 и протяните их вниз с помощью маркера автозаполнения. У вас должно получиться как на рисунке 15.

     ВПР рис15

     Рисунок 15. Результат работы функции ВПР 

     

    Разбор проделанной работы:

     Встаньте на ячейку B2  и нажмите кнопку ƒx, у вас откроется окно Аргументы функции. 

     ВПР рис16

     Рисунок 16. Заполненные аргументы функции ВПР

    Искомое значение - это табельный номер в текущей таблице, в которой мы пишем формулу. Обратите внимание, что используется смешанный тип ссылок с фиксацией столбца А- $A2. Это сделано для того, чтобы при копировании формулы вправо с помощью маркера автозаполнения, искомое значение для строки не менялось. Иначе, искомое значение будет плавать вправо: А2 станет В2 и т.д.

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

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

    В поле Интервальный_просмотр указываем  - 0, так как нужно, чтобы табельные номера в текущей таблице полностью совпадали с табельными номерами в таблице на листе Список сотрудников.

    Один раз написав функцию, мы ее протянули вправо, после чего в ячейках C2 и D2 внутри функций в полях Номер_столбца поставили значения 3 и 4 соответственно. Это сделано, потому что в таблице на листе Список сотрудников столбцы с именами и отчествами имеют порядковые номера 3 и 4. Далее, выделив строку, протянули формулы вниз с помощью маркера автозаполнения.

    Посмотреть результат решения вы можете в файле Функция ВПР с интервальным просмотром 0 (с решением) по ссылке ниже.

     

    Функция ВПР с интервальным просмотром 1 (ИСТИНА)

    Скачайте файл Функция ВПР с интервальным просмотром 1 по ссылке ниже.

    Отройте лист  - Задача без решения. Если у вас стоит защищенный просмотр, то нажмите на кнопку Разрешить редактирование в правом верхнем углу окна, как показано на рисунке 4. 

     

    Задача:

    На листе даны две таблицы. В Таблице1 указаны поступившие заказы со стоимостью. Нужно определить стоимость с учетом скидки. Размер скидки неизвестен. Размер скидки нужно определить из Таблицы2. В Таблице2 указаны порог заказа и соответствующая скидка.

     

    Предварительный разбор:

    Функцию нужно будет вызывать в ячейку С3. Один раз создав функцию, ее нужно будет протянуть вниз с помощью маркера автозполнения для ячейки С12. Искомым значением в данном случае будет являться ячейка А3 - в столбце Поступившие заказы. В качестве таблицы нужно указать Таблицу2. Номер_столбца - 2, потому что в размеры скидкок в Таблице2 находятся во втором столбце. Интервальный просмотр  - 1. Так как искомое значение в Таблице2 должно быть определено приближенно к порогу заказа. 

      ВПР рис17

    Рисунок 17. Соответствие ячеек аргументам функции ВПР

     

    Вызов функции ВПР:

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

    1. В поле Искомое_значение указываем: A3
    2. В поле Таблица указываем: $G$2:$H$6
    3. В поле Номер_столбца указываем: 2
    4. В поле Интервальный просмотр указываем: 1

     ВПР рис18

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

    После того, как закончите ввод данных нажмите ОК. У вас должно получиться как на рисунке 19:

     ВПР рис19

    Рисунок 19. Результат вызова функции ВПР

    Далее нужно протянуть формулу вниз до ячейки С12 включительно с помощью маркера автозаполнения. В результате у вас должно получиться как на рисунке 20.

     ВПР рис20

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

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.