Remontnouta.ru

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

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

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

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

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

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

впр в excel примеры

Содержание:

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

Сама аббревиатура ВПР означает «вертикальный просмотр».

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

Также, в Excel есть противоположная функция под названием HLOOKUP или ГПР – горизонтальный просмотр.

Единственное отличие работы опций состоит в том, что ГПР производит поиск в таблице по перебору столбцов, а не строчек.

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

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

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

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

Младший сотрудник

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

Новый столбец

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

Значение таблицы

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

Растягивание таблицы

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

Успешное копирование

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

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

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

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

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

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

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

Для сортировки первого столбца следует:

  • выделить фамилии-имена первого столбца от Морозова до Петровой (визуальное отсутствие выделения фамилии Морозов – кажущееся);
  • в контекстном меню выделенного первого столбца установить курсор на строку «Сортировка»;
  • в её выпадающем списке щёлкнуть пункт «Сортировка от А до Я».

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

В следующем окне программа Excel предлагает пользователю «умную» сортировку». Иными словами, будет произведена сортировка не только первого левого столбца, но и связанных с ним остальных трёх. Очевидно, что если этого не сделать, то список сотрудников будет абсурдным. Поэтому в этом окне, ничего не меняя, следует щёлкнуть пункт «Сортировка».

Читайте так же:
Можно ли указывать пол в вакансии

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

ВПР пример 1

  1. После создания отсортированного учебного файла щёлкнуть любую свободную ячейку (например, F9): в ней будет возвращён результат.
  1. На строке формул щёлкнуть fx.

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

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

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

4. Выделить функцию ВПР и кликнуть OK.

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

5. В появившемся окне «Аргументы функции» обратите внимание на рекомендацию, подчёркнутую синим цветом. Её игнорирование делает выдаваемые функцией результаты непредсказуемыми. Правильной будет только часть результатов, поэтому перед началом работы сортировка ключевого столбца обязательна.

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

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

6. Как видно, заданное или ключевое значение в окне аргументов именуется «Искомым». Такое название не кажется мне удачным: оно задаётся изначально, а не ищется, и используется как ключ к поиску. Предположим, что нас интересует зарплата, получаемая сотрудницей Ириной Соколовой. Самый простой способ задать верхний параметр – щёлкнуть на ячейке A8, после чего – кликнуть на строке параметра «Таблица».

7. В качестве параметра «Таблица» выделяется весь заполненный участок Excel, исключая заголовки столбцов. А на строке «Номер столбца» следует ввести номер столбца, содержащего список зарплат, т. е. 2. Как видно, правильное значение зарплаты сотрудницы Ирины Соколовой появляется прямо в окне аргументов.

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

8. После клика на OK возвращённое функцией значение появится и в ячейке, зарезервированной для результата.

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

ВПР пример 2

Естественно, что аналогичным образом могут быть извлечены данные из других информационных столбцов. Предположим, что нам нужно узнать дату рождения всё той же Ирины Соколовой. Аргументы функции ВПР претерпят единственное изменение на строке «Номер столбца». Как видно, функция возвращает, на первый взгляд, непонятное число 29025.

Читайте так же:
Как восстановить дефрагментацию диска

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

После щелчка на ОК оно появится и в ячейке результата.

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

Опытные пользователи Excel знают, что в этой программе даты (и время) хранятся в числовом формате, начиная с условно принятой даты 0 января 1900 года. Для отображения полученного значения в привычном человеку виде достаточно придать ячейке результата формат даты. Для этого сначала открыть её контекстное меню, и щёлкнуть пункт «Формат ячеек».

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

В появившемся одноимённом окне:

  • щёлкнуть пункт «Дата»;
  • в правом списке при необходимости поменять вариант отображения;
  • щёлкнуть OK.

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

После этого отображаемая в ячейке результата дата примет «человеческий» вид.

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

ВПР пример 3

Этот пример отличается от первого двумя аргументами – первым и заполненным последним. Как видно, в первом аргументе вместо имени использован подстановочный знак звёздочки *. Если не заполнять последний аргумент, то возвращаемое функцией значение будет ошибочным. Ввод логического значения ЛОЖЬ сделает результат правильным.

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

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

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

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

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

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

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

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

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

Связь цен с материалами.

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

Итоги

Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз.

Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).

Функцию можно вписать вручную или в специальном окне (Shift + F3).

Искомое значение – относительная ссылка, а таблица – абсолютная.

Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением.

Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.

БЫСТРОЕ СРАВНЕНИЕ ДВУХ ТАБЛИЦ С ПОМОЩЬЮ ВПР

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

Новый прайс.

    1. В старом прайсе делаем столбец «Новая цена».

    Добавить колонку новая цена в стаырй прайс.

    1. Выделяем первую ячейку и выбираем функцию ВПР. Задаем аргументы (см. выше). Для нашего примера: .

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

    Заполнение новых цен.

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

    Ошибки в ВПР

    При использовании ВПР, иногда сталкиваются с ошибками. В этом разделе мы объясняем некоторые из наиболее распространенных ошибок ВПР.

    # N / A ошибка в ячейке

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

    = ВПР (3, D3: E14, 2, TRUE)

    Вы получите # N / A ошибка , так как наименьшее значение в столбце количество 5 и вы просите ВПР , чтобы найти 3.

    Если вы сохраняете приближение к FALSE , и вы получите # N / A ошибка, то простая причина заключается в том , что ВПР не может найти справочную значение , которое вы просили. Например,

    = ВПР (100, D3: E14, 2, FALSE)

    выбросит # N / A ошибка , так как значение 100 не существует в столбце Количество.

    #REF Ошибка в ячейке

    #REF Ошибка возникает при указании столбца индекса выше , чем число столбцов в диапазоне ячеек. Например:

    = ВПР (100, D3: E14, 3, FALSE)

    выбросит ошибку #REF , поскольку общее количество столбцов в указанном диапазоне ячеек составляет 2 и вы пытаетесь обратиться к колонке с индексом 3.

    #СТОИМОСТЬ! Ошибка в ячейке

    #VALUE! ошибка возникает , когда количество столбцов в диапазоне ячеек составляет менее 1

    #ИМЯ? Ошибка в ячейке

    Эта ошибка возникает, когда вы не заключите Посмотрите значение в кавычки. Если вы ищете для значения строки, например имя, то вы должны заключить его в кавычки. Однако, если посмотреть значение является числом, например , количество, то не следует использовать двойные кавычки. Следующий пример будет бросать #NAME? ошибка:

    = ВПР (пункт, A3: E14, 3, FALSE)

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

    В конце концов, мы представляем следующее видео-учебник для тех, кто предпочитает учиться, наблюдая!

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

    голоса
    Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector