Функция ВПР в Excel и её секреты. Поиск VLOOKUP в базах данных

Функция ВПР в Excel и её секреты. Поиск VLOOKUP в базах данных.

Функция ВПР в Excel дозволяет работать с большенными таблицами и базами данных. В статье разглядим примеры использования функции ВПР в Excel. Добавочно с функцией Excel VLOOKUP разберем динамический индекс Col index num, IF и поиск в нескольких столбцах либо базе ДВССЫЛ INDIRECT.

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

Это дозволяет сберечь много времени и избежать возможных ошибок при сопоставлении огромных таблиц и принципиальных баз данных. В вашем распоряжении есть две функции – VLOOKUP и HLOOKUP. Единственная разница меж данной парой выражений заключается в том, что 1-ая отыскивает информацию по вертикали, а 2-ая – по горизонтали. Функция ВПР в Excel имеет 4 аргумента:

• Аспект поиска (Lookup_value)
• Матрица (Table_array)
• Индекс (Col_index_num)
• Порядок сортировки (Range_lookup).

Для исследования всякого из этих аргументов будет применено приятное пособие – расчеты зарплаты служащих.

Структура формулы ВПР в Excel

1-ая строчка – значение, которое вы желаете отыскать в базе данных Excel. В примере нужная информация находится в ячейке B3. Если вы введете ее адресок в 1-ое поле, с помощью выражения можно отыскать нужные сведения в перечне со сведениями о персонале.

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

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

Существует два варианта значений для параметра «Порядок сортировки». Введете 0 – тогда результатом вставки будет ошибка #Н/Д, если не сумеет отыскать в базе Экселя результаты по запросу.

Итог отображается лишь в том случае, если меж данным аспектом отбора (Lookup_value) и плодами в последней первой колонке массива (Table_array) будет найдено абсолютное совпадение.

Введете 1 – и формула тоже покажет итог, если отыщет полное совпадение меж Lookup_value и содержимым какой-нибудь строчки в первом столбце матрицы (Table_array).

Но если нет соответствия, то будет выбрано значение, приближенное к этому числу. Иными словами, если вы в примере заместо B3 напишете 12, функция возвратит ответ «Gwendy», поэтому что 10 является числом, очень приближенным к 12. 1-ая колонка в таблице заблаговременно обязана быть отсортирована по возрастанию, если вы вставляете 1 в Range_lookup.

Поиск ориентировочного значения ВПР в Excel

В примере (снимок № 2) ВПР употребляется для вычисления надбавки на базе текущего оклада. Аспект отбора – это зарплата, написана в F3. Матрица – это спектр $I$3:$J$10 (весь массив).

Адресок является абсолютным (это демонстрируют знаки $), чтоб осталась возможность копировать всю получившуюся систему без конфигурации спектра. Col_index_num равен 2 (процент увеличения находится в примыкающем массиве).

Параметр порядка сортировки Range_lookup – 1, поэтому что необходимо, чтоб формула выдавала итог, даже если нет безупречного соответствия. Если Lookup_value составляет 35 850, ВПР не может отыскать безупречное совпадение в массиве, но наиблежайшее малое число – 35 000, потому ответом на выражение будет 4%. На этом ликбез «ВПР в Excel для чайников» завершается.

Сопоставление 2-ух списков при помощи ВПР в Excel

Вы сможете употреблять ВПР в Excel для сопоставления 2-ух баз данных . Аспект отбора в этом примере (снимок экрана № 3) – идентификатор персонала (он должен быть неповторимым значением) в первой колонке.

Матрица – это спектр идентификаторов персонала во 2-м перечне либо массиве. Сделайте ссылку на массив абсолютной, используя знаки $ в адресах ячеек ($I$3:$I$9), чтоб иметь возможность копировать выражение без конфигурации ссылок на ячейки спектра.

В аргументе «Порядок сортировки» введите 0 – это непременно поэтому, что для вас необходимо лишь четкое пересечение. Скопируйте получившееся, и если в ответ отображается #Н/Д, это означает, что во 2-м перечне либо в базе разыскиваемая запись отсутствует.

Динамический индекс (Col_index_num) с внедрением нумерованных столбцов

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

Это дозволяет сберечь весьма много времени и не вводить номер столбца всякий раз. На снимке № 4 нумерация колонок производится в строке 4. В поле Col_index_num необходимо ввести C4, и получится 1-ый динамический индекс.

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

Динамический индекс со вложенными IF (ЕСЛИ)

Вы сможете употреблять формулы IF (ЕСЛИ) в Экселе для сотворения динамического индекса. В примере (снимок экрана № 5) премия рассчитывается в зависимости от отдела и группы.

Если сотрудник, работая в отделе продаж, заходит в группу 1 бонусной группы, формула VLOOKUP обязана возвратить 3%, если в группу 5 бонусной группы – 4%, а если в группу 10 – 5%.

Выражение IF с 2-мя границами в Col_index_num инспектирует информацию в колонке G (категория призов), помогая ВПР избрать значение из массива справа.

Аспект поиска – это отдел, E3. Матрица – спектр $J$3:$M$7. В поле «Индекс» указывается система по типу IF(G3=«Группа 1»,2,IF(G3=«Группа 5»,3,4)).

При помощи первой функции IF вы можете выяснить, соответствует ли G3 запросу «Группа 1». Если это так, формула VLOOKUP изберет приз из второго столбца. Если это неправда, 2-ое выражение IF инспектирует, соответствует ли G3 запросу «Группа 5».

Если это вправду так, функция ВПР выбирает приз из третьей колонки. Если и это ошибочно, анализируется 4-ая. В строке Range_lookup обязательно введите 0, поэтому что необходимо отыскать лишь буквальное совпадение.

Поиск в нескольких столбцах либо базе Excel с INDIRECT (ДВССЫЛ)

В примере (снимок № 6) имеется 3 таблицы. Спектры данных в каждой из их имеют наименования «Деньги», «Создание» и «Реализации».

В этом примере для вас необходимо отыскать сотрудника из отдела продаж с идентификатором 3. Заглавие спектра вводится в C19, идентификатор – в E19. Ячейка B22 связана с C19, и B22 (идентификатор) – это Lookup_value. Выражение с ДВССЫЛ находится в таблице – INDIRECT принимает содержимое C19 как имя спектра.

Дальше указываете номер колонки в матрице, откуда функция обязана извлекать сведения и показывать их в ячейке с помощью VLOOKUP. В Range_lookup введите 0, поэтому что тут требуется лишь четкое пересечение.

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

Индивидуальности функции вертикального поиска в Excel

• Регистр в запросах не учитывается.
• В строке «Порядок сортировки» значение по дефлоту равно 1 (TRUE-ПРАВДА). Потому лучше не пропускать этот аргумент, если для вас необходимы лишь четкие совпадения.
• В первом поле (там, где вы пишете запрос) допускается внедрение подстановочных символов.

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

Функция ВПР. Внедрение функции ВПР. Excel — ВПР

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

Как работает ВПР Excel

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

Функция ВПР производит поиск данного аспекта, который может иметь хоть какой формат (текстовый, числовой, валютный, по дате и времени и т. д.) в таблице. В случае нахождения записи она выдает (подставляет) значение, занесенное в той же строке, но с искомого столбца таблицы, другими словами соответственное данному аспекту. Если разыскиваемое значение не находится, то выдается ошибка #Н/Д (в английском варианте #N/А).

Необходимость использования

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

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

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

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

Метод наполнения формулы

Размещена формула ВПР во вкладке «Мастер функций» и разделе «Ссылки и массивы». Диалоговое окно функции имеет последующий вид:

Аргументы в формулу вносятся в порядке очереди:

• Разыскиваемое значение — то, что обязана отыскать функция, и вариациями которого являются значения ячейки, ее адресок, имя, данное ей оператором. В нашем случае — это фамилия и имя менеджера.
• Таблица — спектр строк и столбцов, в котором ищется аспект.
• Номер столбца — его порядковое число, в котором размещается сумма продаж, другими словами итог работы формулы.
• Интервальный просмотр. Он вмещает значение или ЛОЖЬ, или ИСТИНА. При этом ЛОЖЬ возвращает лишь четкое совпадение, ИСТИНА — разрешает поиск ориентировочного значения.

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

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

Приятный пример организации таблицы

А	В	С	Д
продукт 1	90	продукт 3	60
продукт 2	120	продукт 1	90
продукт 3	60	продукт 4	100
продукт 4	100	продукт 2	120

Формула, записанная в Д, будет смотреться так: =ВПР (С1; А1:В5; 2; 0), другими словами =ВПР (разыскиваемое значение; спектр данных таблицы; порядковый номер столбца; 0). В качестве 4-ого аргумента заместо 0 можно употреблять ЛОЖЬ.

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

Закрепить область рабочего спектра данных можно с помощью абсолютных ссылок. Для этого вручную проставляются знаки $ перед буквенными и численными значениями адресов последних левых и правых ячеек таблицы. В нашем случае формула воспринимает вид: =ВПР (С1; $А$1:$В$5; 2; 0).

Ошибки при использовании

Функция ВПР не работает, тогда и возникает сообщение в столбце вывода результата о ошибке (#N/A либо #Н/Д). Это происходит в таковых вариантах:

1. Формула введена, а столбец разыскиваемых критериев не заполнен (в данном случае колонка С).
2. В столбец С внесено значение, которое отсутствует в колонке А (в спектре поиска данных). Для проверки наличия искомого значения следует выделить столбец критериев и во вкладке меню «Правка» — «Отыскать» вставить данную запись, запустить поиск. Если программка не находит его, означает оно отсутствует.
3. Форматы ячеек колонок А и С (разыскиваемых критериев) различны, к примеру, у одной — текстовый, а у иной — числовой. Поменять формат ячейки можно, если перейти в редактирование ячейки (F2). Такие препядствия обычно появляются при импортировании данных с остальных прикладных программ. Для избежания подобного рода ошибок в формулу ВПР есть возможность встраивать последующие функции: ЗНАЧЕН либо ТЕКСТ. Выполнение данных алгоритмов автоматом конвертирует формат ячеек.
4. В коде функции находятся непечатные знаки либо пробелы. Тогда следует пристально проверить формулу на наличие ошибок ввода.
5. Задан ориентировочный поиск, другими словами 4-ый аргумент функции ВПР имеет значение 1 либо ИСТИНА, а таблица не отсортирована по восходящему значению. В этом случае столбец разыскиваемых критериев требуется отсортировать по возрастанию.

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

Индивидуальности использования в качестве интервального просмотра 1 либо ИСТИНЫ

Ошибка под №5 является достаточно всераспространенной и наглядно изображена на рисунке ниже.

В данном примере перечень имен согласно нумерации отсортирован не по возрастанию, а по ниспадающему значению. При этом в качестве интервального просмотра применен аспект ИСТИНА (1), который сходу прерывает поиск при обнаружении значения большего, чем разыскиваемое, потому выдается ошибка.

При применении 1 либо ИСТИНЫ в четвертом аргументе необходимо смотреть, чтоб столбец с разыскиваемыми аспектами был отсортирован по возрастанию. При использовании 0 либо ЛЖИ данная необходимость отпадает, но также отсутствует тогда возможность интервального просмотра.

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

Остальные аспекты при работе с функцией ВПР

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

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

Иной вариант — озаглавить — предполагает выделение спектра данных, позже переход в меню «Вставка»- «Имя»- «Присвоить».

Для того чтоб употреблять данные, размещенные на другом листе рабочей книжки, с помощью функции ВПР, нужно во 2-м аргументе формулы прописать размещение спектра данных. К примеру, =ВПР (А1; Лист2!$А$1:$В$5; 2; 0), где Лист2! — является ссылкой на требуемый лист книжки, а $А$1:$В$5 — адресок спектра поиска данных.

Пример организации учебного процесса с ВПР

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

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

Функция ВПР непревзойденно совладевает с решением данной задачки. В столбце G под заголовком «Оценки» записывается соответственная формула: =ВПР (Е4, В3:С13, 2, 0). Ее необходимо скопировать на всю колонку таблицы.

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

Пример организации поисковой машины с ВПР

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

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

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

Функция Vlookup excel — это интегрированная функция ссылок, которая употребляется для поиска определенных данных из группы данных либо спектра, также известного как массив таблиц, формула vlookup употребляет всего четыре аргумента, 1-ый аргумент — это ссылочная ячейка, а 2-ой Аргумент — это массив таблицы, 3-ий аргумент — номер столбца, в котором находятся наши данные, а 4-ый — аспект соответствия.

При работе с 2-мя либо наиболее наборами данных мы сталкиваемся с ситуацией, когда нужно сопоставить либо связать наборы данных. Не считая того, некие временные цели могут заключаться в извлечении совпадающих либо соответственных данных из определенного набора данных для набора идентификаторов. В Excel эту делему можно решить при помощи разных функций, к примеру ВПР, Индекс, совпадение, ЕСЛИ и т. Д., Где Функция ВПР в Excel это самый обычный и обширно применяемый для решения хоть какой препядствия Excel.

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

V в ВПР значит вертикальный поиск (в одном столбце), а H в HLOOKUP значит горизонтальный поиск (в одной строке).

В Функция ВПР в Excel — это интегрированная функция в Microsoft Excel, которая была отнесена к группы функций поиска / ссылок.

Формула ВПР

Формула ВПР в Excel смотрится последующим образом:

Разъяснение

Формула ВПР в excel воспринимает последующие аргументы:

1. Lookup_value: Значение либо идентификатор, который необходимо сохранить в таблице
2. Table_array: Таблица либо спектр, в котором будет производиться поиск Lookup_value
3. Col_index: Номер столбца в таблице, из которого обязано быть возвращено совпадающее значение. 1-ый столбец — 1.
4. Range_lookup: [Optional]. Если этот параметр опущен, по дефлоту он равен «1». Range_lookup может принимать последующий параметр:

• ‘0’ либо ‘FALSE’ для четкого совпадения
• ‘1’ либо ‘ИСТИНА’ для ориентировочного соответствия

Как употреблять ВПР в Excel?

До этого чем приступить к работе с функцией V-lookup в Excel, ознакомьтесь со последующими главными советами:

1. В общем, вы обнаружите, что поисковое значение термина употребляется заместо значения, которое необходимо находить. Оба схожие.
2. В ссылке на таблицу либо в спектре, в котором находится значение поиска, столбец значения поиска постоянно должен находиться в первом столбце для правильной работы функции ВПР. Для ВПР в примере Excel, если значение поиска находится в ячейке D2, тогда ваш спектр должен начинаться с D.
3. Ты должен считать Номер столбца, который вы желаете возвратить в формулу Vlookup в Excel с начала столбца таблицы. Для ВПР в примере Excel, если вы укажете C2: E5 в качестве спектра, вы должны считать C первым столбцом, C — вторым и так дальше.
4. При желании вы сможете ввести ИСТИНА либо «0», если вы желаете ориентировочное совпадение, либо ЛОЖЬ «1», если вы желаете четкое совпадение возвращаемого значения. Значение по дефлоту для Vlookup постоянно будет иметь значение ИСТИНА либо ориентировочное совпадение.

Ниже приведена формула Vlookup в Excel опосля объединения всего перечисленного выше:

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

Примеры

Давайте разглядим несколько примеров ВПР в Excel перед внедрением функции Excel Vlookup:

Пример # 1

В таблице ниже у нас есть данные о сотрудниках. Наша цель — получить надлежащие данные для набора идентификаторов служащих, которые представлены в правой таблице.

Снутри функции VLOOKUP в Excel мы передали нужные характеристики. Шаги,

изберите поисковое значение, что ячейка G4,

передать ссылку на таблицу, которая B4: D10,

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

match-type как «0», что значит, что нам необходимо четкое совпадение.

Формулу ВПР в Excel можно переписать как = Vlookup (G9, $ B $ 4: $ D $ 10,3,0), которая возвращает Gagan@яху.com.

Если вы думаете, какова цель использования ссылки на таблицу $ sign-in, продолжайте читать это. Символ $ употребляется для фиксации ссылки на таблицу с внедрением главного слова функции F4, что именуется абсолютной ссылкой. Таковым образом, если вы перетащите формулу VLOOKUP Excel в ячейки ниже, ссылка на таблицу остается постоянной.

Заметка: Для поискового значения = 169 нет записи с таковым же значением в Идентификатор сотрудника столбец. Как следует, формула ВПР в Excel возвращает тут ошибку как # Н / Д. Если ничего не найдено, ворачивается # N / A.

Пример # 2

Необязательно, чтоб значение поиска было числом. Это быть может строчка либо серия знаков.

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

Снутри формулы VLOOKUP в Excel мы передали последующие нужные характеристики:

изберите значение поиска что ячейка H8 либо из столбца H,

передать ссылку на таблицу, которая B4: D10,

то столбец количество возвращаемой таблицы нам необходимо ID сотрудника, так что это 3, и

match-type как «0», что значит, что нам необходимо четкое совпадение.

Вы увидели, что в наборе данных поиска мы переместили столбец «Электрическая почта» в левую часть таблицы. Это ограничение формулы Vlookup в Excel; другими словами столбец значений поиска должен быть слева.

Мы можем переписать формулу VLOOKUP в excel = Vlookup (gagan @ яху.com, B4: E10,2,0), это возвращает значение 427.

Пример # 3

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

= ВПР (F4; A3: C15; 3)

F4 — значение для поиска

A3: C15 — массив таблиц

3 — индекс столбца, содержащий требуемое возвращаемое значение