Tw-city.info

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

Аббревиатура впр excel

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

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

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

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

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

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

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

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

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

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

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

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

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

Но если нет соответствия, то будет выбрано значение, приближенное к этому числу. Другими словами, если вы в примере вместо B3 напишете 12, функция вернет ответ «Gwendy», потому что 10 является числом, максимально приближенным к 12. Первая колонка в таблице заранее должна быть отсортирована по возрастанию, если вы вставляете 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 для чайников » заканчивается.

Сравнение двух списков с помощью ВПР в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

Если это действительно так, функция ВПР выбирает бонус из третьей колонки. Если и это неверно, анализируется четвертая. В строке 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

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

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

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

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

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

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

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

Читать еще:  Как узнать количество видеопамяти

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

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

Заключение

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

Аббревиатура впр excel

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

ВПР — это аббревиатура от В ертикального ПР осмотра. Аналогично и VLOOKUP — V ertical LOOKUP . Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку). Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP). ГПР , в противоположность ВПР , производит горизонтальный поиск, однако, концепция Excel (да и вообще концепция организации данных) подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам. Если вы в Excel слишком часто пользуетесь функцией ГПР , то, вполне вероятно, что вы чего-то не поняли в этой жизни.

Синтаксис

Функция ВПР имеет 4 параметра:

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

— ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра

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

— это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона . В случае, если массив отсортирован, то мы указываем значение ИСТИНА (TRUE) или 1 , в противном случае ЛОЖЬ (FALSE) или 0 . В случае, если данный параметр опущен, то он по умолчанию принимается равным 1 .

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

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

Итак, как же конкретно работает формула ВПР :

  • Вид формулы I . Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое . Если такой строки не найдено, то возвращается последняя строка диапазона.
  • Вид формулы II . Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром , в противном случае возвращается код ошибки #Н/Д (#N/A).

Схемы работы формул

ВПР тип I

ВПР тип II

Следствия для формул вида I:

  1. Формулы можно использовать для распределения значений по диапазонам.
  2. Если первый столбец содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
  3. Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
  4. Данный вид вернёт ошибку #Н/Д только, если не найдёт значения меньше или равного искомому.
  5. Понять, что формула возвращает неправильные значения, в случае, если ваш массив не отсортирован, довольно затруднительно.

Следствия для формул вида II:

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

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

Вы добрались до кульминационного места статьи. Казалось бы, ну какая разница, укажу ли я в качестве последнего параметра ноль или единицу? В основном все указывают конечно же ноль, так как это довольно практично — не надо заботиться о сортировке первого столбца массива, сразу видно — найдено значение или нет. Но, если у вас на листе несколько тысяч формул ВПР (VLOOKUP), то вы заметите, что ВПР вида II работает медленно. При этом обычно все начинают думать 2 вещи:

  • мне нужен более мощный компьютер
  • мне нужна более быстрая формула, например, многие знают про ИНДЕКС + ПОИСКПОЗ (INDEX+MATCH), которая, якобы, быстрее на жалкие 5-10%.

И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ ! Данный факт я проверил вполне надёжно.

Секрет такой быстрой работы кроется в том, что на отсортированном массиве можно применять чрезвычайно эффективный алгоритм поиска, который носит название бинарного поиска (метод деления пополам, метод дихотомии). Так вот ВПР вида I его применяет, а ВПР вида II ищет без какой-либо оптимизации вообще. Тоже самое относится и к функции ПОИСКПОЗ (MATCH), которая включает в себя аналогичный параметр, а также и к функции ПРОСМОТР (LOOKUP), которая работает только на отсортированных массивах и включена в Excel ради совместимости с Lotus 1-2-3.

Читать еще:  Где хранятся автосохраненные файлы excel

Недостатки формулы

Недостатки ВПР очевидны: во-первых, она ищет только в первом столбце, указанного массива, и, во-вторых, — только справа от данного столбца. А как вы понимаете, вполне может случиться так, что столбец, содержащий необходимую информацию, окажется слева от столбца, в котором мы будем искать. Этого недостатка лишена уже упомянутая связка формул ИНДЕКС + ПОИСКПОЗ (INDEX+MATCH), что делает её наиболее гибким решением по извлечению данных из таблиц в сравнении с ВПР (VLOOKUP).

Реальное применение формулы

Диапазонный поиск

Классическая иллюстрация к диапазонному поиску — задача определения скидки по размеру заказа.

Поиск текстовых строк

Безусловно ВПР ищет не только числа, но и текст. При этом надо принимать во внимание, что регистр символов формула не различает. Если использовать символы подстановки, то можно организовать нечёткий поиск. Есть 2 символа подстановки: » ? » — заменяющий любой один символ в текстовой строке, и » * » — заменяющая любое количество любых символов.

Борьба с пробелами

Часто поднимается вопрос, как решить проблему лишних пробелов при поиске. Если справочную таблицу ещё можно вычистить от них, то первый параметр формулы ВПР не всегда зависит от вас. Поэтому, если риск засорения ячеек лишними пробелами присутствует, то можно применять для очистки функции СЖПРОБЕЛЫ (TRIM).

Разный формат данных

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

= ВПР(— D7 ; Продукты!$A$2:$C$5 ; 3; 0) — если A7 содержит текст, а таблица числа

= ВПР( D7 & «»; Продукты!$A$2:$C$5 ; 3; 0) — и наоборот.

Кстати, перевести текст в число можно сразу несколькими способами, выбирайте:

  • Двойное отрицание —D7
  • Умножение на единицу D7*1
  • Сложение с нулём D7+0
  • Возведение в первую степень D7^1

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

Как подавить выдачу #Н/Д

Это очень удобно делать при помощи функции ЕСЛИОШИБКА (IFERROR).

Например: = ЕСЛИОШИБКА( ВПР(D7; Продукты!$A$2:$C$5; 3; 0); «»)

Если ВПР вернёт код ошибки #Н/Д , то ЕСЛИОШИБКА его перехватит и подставит параметр 2 (в данном случае пустая строка), а если ошибки не произошло, то эта функция сделает вид, что её вообще нет, а есть только ВПР , вернувший нормальный результат.

Массив

Часто забывают ссылку массива сделать абсолютной и при протягивания массив «плывёт». Помните, что вместо A2:C5 следует использовать $A$2:$C$5 .

Хорошей идеей является размещение справочного массива на отдельном листе рабочей книги. Не путается под ногами, да и сохраннее будет.

Ещё более хорошей идеей будет объявление этого массива в виде именованного диапазона.

Многие пользователи при указании массива используют конструкцию вида A:C , указывая столбцы целиком. Этот подход имеет право на существование, так как вы избавлены от необходимости отслеживать тот факт, что ваш массив включает все необходимые строки. Если вы добавите строки на лист с первоначальным массивом, то диапазон, указанный как A:C не придётся корректировать. Безусловно эта синтаксическая конструкция заставляет Excel проводить несколько большую работу, чем при точном указании диапазона, но данными накладными расходами можно пренебречь. Речь идёт о сотых долях секунды.

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

Использование функции СТОЛБЕЦ для указания колонки извлечения

Если таблица, в которую вы извлекаете данные при помощи ВПР имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов. При этом все ВПР формулы будут одинаковыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что у первого параметра координата столбца абсолютная.

Создание составного ключа через &»|»&

Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем Код ), а числовым, то для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.

ITGuides.ru

Вопросы и ответы в сфере it технологий и настройке ПК

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

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

Видео по использованию функции ВПР Excel

Что такое ВПР в Excel?

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

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

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

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

Пример задания аргументов функции ВПР в экселе

Как всякая функция Excel, данная имеет собственный синтаксис, которому необходимо следовать. Она включает четыре параметра: искомое значение, ссылку на диапазон значений, номер столбца, из которого будет возвращено искомое значение, и последний параметр — «отсортировано». Он может быть представлен как истина или ложь или вовсе опущен. Тогда он считается как 1 — истина.

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

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

Примечательно, что Excel работает с разными типами данных, а не только с числовыми. ВПР в состоянии найти также текстовое значение. В этом случае существенным минусом, влияющим на качество поиска, могут стать лишние пробелы начала строки. Из-за них ВПР способна вернуть неправильное или необычное значение. Чтобы предотвратить искажение информации, используйте соответствующий параметр Excel для устранения пробелов. Таким образом, функция ВПР является очень лояльной и работает в сотрудничестве с другими, например, с СЖПРОБЕЛЫ и другими.

Кроме того, может случиться так, что в сравнении будут участвовать как целые, так и дробные значение. Здесь проблема заключается в разделительном знаке — точка или запятая. Если они различны, то выборку сделать окажется весьма сложно. Примените возможность Эксель «найти и заменить», что найдете на панели инструментов.

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

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

Читать еще:  Драйвера на базовый видеоадаптер майкрософт 10

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

Функция ВПР незаменима при необходимости объединить данные из разных таблиц, имеющих какое-либо общее поле (наименование, штрих-код и т.д.)

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

Отблагодари меня, поделись ссылкой с друзьями в социальных сетях:

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

Автор: rf-webmaestro · Опубликовано 27.04.2018 · Обновлено 08.02.2019

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

Расшифровка

Большинство пользователей не знают, что аббревиатура «ВПР» расшифровывается как «Вертикальный Просмотр». На английском функция называется «VLOOKUP», которая означает «Vertical LOOK UP»

Как пользоваться функцией

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

  1. Создайте таблицу, по которой можно будет сделать какой-нибудь поиск информации.

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

  1. В поле «Искомая фамилия» введем какую-нибудь на выбор из тех, что есть в таблице.
  2. Затем переходим на следующую ячейку и вызываем окно «Вставка функции».
  3. Выбираем категорию «Полный алфавитный перечень».
  4. Находим нужную нам функцию «ВПР». Для продолжения нажимаем на кнопку «OK».

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

  1. В результате этого мы получили имя «Томара». То есть, всё правильно.

Теперь нужно воспользоваться этой же формулой и для остальных полей. Простое копирование ячейки при помощи Ctrl + C и Ctrl + V не подойдёт, поскольку у нас используются относительные ссылки и каждый раз будет меняться номер столбца.

Для того чтобы всё сработало правильно, нужно сделать следующее:

  1. Кликните на ячейку с первой функцией.
  2. Перейдите в строку ввода формул.
  3. Скопируйте текст при помощи Ctrl + C .

  1. Сделайте активной следующее поле.
  2. Снова перейдите в строку ввода формул.
  3. Нажмите на горячие клавиши Ctrl + V .

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

  1. Затем меняем номер столбца на нужный. В нашем случае это 3. Нажимаем на клавишу Enter .

  1. Благодаря этому мы видим, что данные из столбца «Год рождения» определились правильно.

  1. После этого повторяем те же самые действия для последнего поля, но с корректировкой номера нужного столбца.

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

То есть нумерация начинается не с начала листа, а с начала указанной области ячеек.

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

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

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

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

  1. Переходим в первую клетку нового столбца и вводим там следующую формулу.

=ВПР($B$3:$B$11;Лист2!$B$3:$E$11;4;ЛОЖЬ)

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

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

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

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

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

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

Функция «ВПР» и выпадающие списки

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

  1. Перейдите в ячейку, в которой происходит выбор фамилии.
  2. Откройте вкладку «Данные».
  3. Кликните на указанный инструмент и выберите пункт «Проверка данных».

  1. В новом окне в графе «Тип данных» выберите пункт «Список».

  1. После этого появится новое поле «Источник». Кликните туда.
  2. Затем выделите первый столбец. Ссылка на ячейки подставится автоматически.
  3. Для продолжения нажмите на «OK».

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

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

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

Ошибки #Н/Д

С подобной проблемой сталкиваются многие пользователи, которые только начинают пользоваться этой функцией. Как правило, ошибка «#Н/Д» возникает в следующих случаях:

  • вы включили точный поиск (последний параметр 0/ЛОЖЬ), а это значение в таблице отсутствует;
  • вы включили неточный поиск (последний параметр 1/ИСТИНА), но при этом данные не отсортированы по возрастанию (если используется приблизительный поиск, то разработчики Microsoft рекомендуют использовать упорядоченные данные);
  • аргументы функции имеют различный формат (например, что-то в текстовом виде, а остальное – в числовом);
  • в формуле присутствуют опечатки или лишние символы (пробелы, непечатаемые знаки, переносы и так далее).

Отличие от функции «ГПР»

Данный инструмент практически точно такой же, только ищет по горизонтали. Более подробно о нем можно узнать на официальном сайте Microsoft.

Заключение

В данной статье мы пошагово рассмотрели, как пользоваться функцией «ВПР» в редакторе Excel. Кроме этого, было показано несколько примеров. Данная инструкция ориентирована на новичков (чайников).

Если у вас что-то не получается, возможно, вы неправильно указываете аргументы функции. Например, неправильный номер столбца или неверный диапазон ячеек. Попробуйте повторить описанные выше действия заново, но на этот раз более внимательно. И у вас всё получится.

Видеоинструкция

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

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