Youtubezilla.ru

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

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

Итак, представим, что у нас есть две таблицы Excel: и в одной и в другой есть общее поле, или столбец, кому как удобно. К примеру, в первой находится код товара, наименование товара, а в другой тот же код товара и цена на него. Лично мне удобно разносить таблицы по листам, первая таблица будет на Лист1, вторая на Лист2. В свое время эта привычка пошла от срочной необходимости составлять отчеты на работе, из разных таблиц, в Access 2000. Её как раз и использовал вместо замечательной функции ВПР .

ВПР в Excel

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

ВПР в ExcelВПР в Excel

Далее,в окне аргументов функции впр заполняем данные: Искомое значение: B2-это ячейка кода товара Таблицы №1, Таблица: Лист2!$B$4:$C$6-это диапазон ячеек в Таблице №2,

ВПР в Excel

символ $ нужен для того чтоб при копировании или протягивании формулы по столбцу это значение не менялось динамически. У нас остались два аргумента, Номер_столбца и Интервальный_просмотр. Номер столбца:2 — в Таблице №2 во втором столбце находится цена товара, её нам и нужно вытянуть в Таблицу №1. Интервальный_просмотр: 0- это значит точное совпадение, если поставить 1, то это будет приблизительное значение. Оно вам нужно? ))) Когда все внесли, нажимаем Ок и смотрим результат.

ВПР в Excel

Функция ВПР — это один из самых полезных компьютерных трюков не только в Excel. Кто узнал о нем, тот без нее жить не может, серьезно. Итак, предположим, что у нас есть две таблицы с текстом. Нужно значения одного списка (Фамилии) передать в ячейки другого, в зависимости от текста-условий (Номера ТС). Если конкретнее пример ниже:

ВПР

Задача1. В одном файле хранится список ФИО сотрудников и транспортных средств (Таблица1). В Таблице2 для некоторых автомобилей заполнены номера накладных. Причем таблицы не совпадают по количеству строк. Цель. Для каждой строки Таблицы2 заполнить ФИО сотрудников. Для этого как раз пригодится функция ВПР.

Функция ВПР. Решение задачи

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

Функция ВПР ищет значение в левом столбце Таблицы1 и возвращает (записывает) значение ячейки, находящейся в столбце Таблицы2 под определенным номером, той же строки. Во как 🙂 Но на самом деле все проще. Для этого нам нужно понять из чего состоит ВПР

Переменные. Функция ВПР

  • Искомое_значение — то самое значение, которое мы ищем в левом столбце Таблицы1. Номер ТС в Таблице2.
  • Таблица — все столбцы Таблицы1, причем первый столбец должен быть, который мы ищем (Номер ТС)
  • Номер_столбца — номер столбца в Таблице1, из которого возвращаем значения (ФИО)
  • [интервальный_просмотр] — может принимать только два значения — Ложь или Истина: Ложь – ищет точное совпадение, Истина – приблизительное. В 95% случаев требуется искать точное значение, т.е. выбирать ЛОЖЬ.

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

Итак, напишем функцию для нашей задачи:

Читайте так же:
SiSoftware Sandra Lite 2018.12.28.69

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

E:E — это диапазон значений, по которым Excel будет сравнивать условия с Таблицей 1. A:B — вся Таблица1 (обязательно, чтобы первым столбцом был столбец для поиска условий). Число 2 это тот по счету столбец в Таблице 1, который мы будем переносить в ячейку F1. ЛОЖЬ — смотрите выше.

Я тысячу раз слышал как люди говорят: «Давайте заВПРим это» или «ну тут можно ВПРом сделать», и это отлично! Значит люди экономят время, зная о простых и действенных методах. Не отставайте!

Не забудьте растащить функцию до конца диапазона. Не знаете как это сделать быстро? Читайте тут.

А как можно сделать ту же функцию с удобными названными диапазонами (на картинке ниже)? Нравится? Читайте тут.

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

Комментарии по ВПР

  • Если для одной ячейки в Таблице2 есть несколько значений, в Таблице1 будет выдаваться первое значение.
  • Если совпадающих данных нет, будет возвращаться ошибка «не найдено» (Н/Д#). Чтобы ее не отображать, используйте формулы =ЕСЛИОШИБКА()
  • Чтобы определить какой именно номер столбца записать в формулу, при выделении диапазона показывается, номер столбца.

Номер столбца

  • Чтобы посчитать сумму для нескольких значений из одной таблицы в другой, воспользуйтесь СУММЕСЛИ

Что в итоге:

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

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

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

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

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

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

Функция ВПР или VLOOKUP

При помощи этой функции можно сводить данные из нескольких таблиц в одну: она подтягивает нужные значения. Объясню принцип ее работы на примере. На последнем чемпионате Oliver Wyman Impact мы анализировали, какие страховые компании в среднем росли быстрее остальных на протяжении последних пяти лет. Данные о выручке можно было найти на сайте Центрального Банка РФ, однако проблема заключалась в подготовке этих данных к анализу. В каждом из отчетов разное количество компаний и порядок их расположения, поэтому просто скопировать значения одного года и вставить рядом значения другого было не достаточно. Если же вручную искать каждую компанию, а затем копировать и вставлять ее показатели в нужное место, можно потратить целый день.

Читайте так же:
FontCreator Professional Edition (13.0.0.2663) RePack (& Portable) by elchupacabra На Русском

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

Искомое значение — это некий ключ, по которому происходит поиск данных. В нашем случае, это ID компании. Искомое значение не должно повторяться, иначе формула выберет только первое значение с данным ключом.

Таблица для поиска в нашем случае — это диапазон F1:H238. Ваш ключ всегда должен находиться в первом столбце таблицы.

Из номера указанного столбца берется то самое значение, которое вам необходимо. В нашем случае это номер 3. Обратите внимание: номер столбца нужно считать внутри того диапазона, который вы выделили на втором этапе, а не по всему листу Excel.

Модификатор поиска — это точность поиска ключа. TRUE — для приблизительного поиска, FALSE — для точного. Мы выбираем FALSE, так как все ID в первой и второй таблице одинаковы.

Работать с формулами будет проще, если знать горячие клавиши. Во-первых, TAB. Она поможет быстро выбрать необходимую формулу в выпадающем списке, когда вы начинаете что-то писать после знака «равно». Во-вторых, Ctrl + Shift + стрелка, по направлению которой необходимо выделить ячейки. В-третьих, клавиша F4, при помощи которой можно фиксировать ячейки, на которые вы ссылаетесь, чтобы формула не «съезжала». После нажатия на эту клавишу в формуле появляется знак, похожий на доллар.

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

Синтаксис функции ВПР ( VLOOKUP ) в Google Таблицах

Вот как выглядит формула VLOOKUP :

  • search_key — это значение или элемент, который вы ищете. Например, в случае с рестораном это будет бургер или пицца.
  • диапазон — это диапазон, который будет использоваться в функции Vlookup. В крайнем левом столбце этого диапазона будет выполняться поиск search_key.
  • index — это номер столбца, из которого вы хотите получить результат. Первый столбец в диапазоне — 1, второй столбец — 2 и так далее. Обратите внимание, что это значение должно быть от 1 до общего количества столбцов. В противном случае будет возвращено #VALUE! Ошибка.
  • is_sorted — [ ИСТИНА по умолчанию] — в этом аргументе вы можете указать, ищете ли вы точное или приблизительное совпадение. Вы можете использовать FALSE для точного совпадения и TRUE для приблизительного совпадения. Когда вы используете ИСТИНА, список необходимо отсортировать по возрастанию. Если вы не укажете здесь значение, по умолчанию будет ИСТИНА. Обратите внимание, что для использования

Теперь давайте рассмотрим несколько примеров, чтобы понять, как использовать функцию Google Sheets Vlookup в реальных сценариях.

Ошибки #Н/Д, #ССЫЛКА! и #ЗНАЧ!

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

Читайте так же:
Можно ли удалять комментарии в Инстаграме

Неправильный ввод формулы

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

sdvig diapazona v vpr

Результатом вычисления функции будет ошибка вида #Н/Д.

В нашем простом примере таблица состоит всего из двух столбцов, на практике же столбцов может быть больше. Важно, чтобы, во-первых, столбец, в котором будет производиться поиск искомых значений был крайним левым, а во-вторых правильно определить номер столбца. Если в таблице два столбца, а при вводе формулы в третьем аргументе указано число, которое больше двух, то результатом вычисления функции будет ошибка типа #ССЫЛКА!, а если меньше единицы, то #ЗНАЧ!

Число отформатировано как текст

В первой таблице, в ячейке с адресом «A2» число 2551 отформатировано как текст, а во второй таблице, в ячейке «D3» записано числовое значение 2551. Поскольку число 2551 не равно тексту 2551, функция выдает ошибку #Н/Д.

oshibki v funkcii vpr

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

Различие в написании текстовых значений

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

registr i alfavit v vpr

В ячейке с адресом «A2» написана буква кириллицы, а в «D3» — буква латиницы, в результате ошибка.

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

ЛОЖЬ и ИСТИНА

Аргумент «Интервальный_просмотр» не является обязательным для функции ВПР. Этот аргумент может принимать два значения ЛОЖЬ (если необходимо найти точное совпадение) и ИСТИНА (если необходимо найти приблизительное совпадение).

Этот момент не всегда понятен пользователям, поэтому требует уточнения, которое проще всего показать на примере.

istina v vpr chisla

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

Если искомое значение – это текст, то при интервальном просмотре, соответствующем значению «ИСТИНА» и при отсутствии точного совпадения, функция будет искать ближайшее меньшее значение по алфавиту.

istina v vpr tekst

Искомое значение – Петр, но в таблице нет значения Петр, поэтому функция ищет ближайшее меньшее значение по алфавиту. Буква «Ф» в алфавите находится после буквы «П», а вот «И» — до буквы «П», поэтому она и является меньшим значением. Соответственно результатом функции будет значение «Иванов».

Читайте так же:
Скачать XviD4PSP последнюю версию

В случае, когда необходимо найти неточное совпадение с текстовым значением, необходимо использовать интервальный просмотр «ЛОЖЬ», а к искомому значению подставлять специальные символы совпадения (?-одиночный символ и *-произвольная последовательность символов). Тильда (

) ставится в том случае, если необходимо найти сами знаки (?) и (*).

ВПР не работает

Как работает ВПР в Excel читайте в посте “Функция ВПР – чудо экономии времени”. Здесь же вы узнаете, что делать, если ВПР не работает. Так бывает. Скорее всего вы допустили ошибку. Есть четыре наиболее распространенных варианта ошибок:

  1. Ошибка Н/Д в каждой ячейке.
  2. Ошибка Н/Д преимущественно в нижней части таблицы.
  3. Ошибка Н/Д в отдельных ячейках.
  4. Ошибка #ССЫЛКА.

впр excel ошибки

А теперь разберем каждый вариант подробнее.

Вариант 1. ВПР вернула ошибку #Н/Д во всех ячейках

  • Вы неправильно выделили диапазон поиска – начали не с того столбца:

впр excel ошибка #Н/Д

Все будет в порядке, как только вы исправите ошибку:

=впр(B3;'[прайс-лист.xlsx]Лист1′! $C$3:$E$7 ;3;ложь)

  • Не совпадает текст, по которому ВПР ищет данные.

Например, в одном файле текст “Компьютер AF”, в другом файле “КомпьютерAF”. Если не заметили, разница всего лишь в наличии дефиса во втором случае. Тем не менее, ВПР не считает такое расхождение мелочью и, следовательно, не сработает.

Единственное исключение – допускаются разные регистры. Например, “Компьютер AF” и “компьютер af” не составит проблемы для ВПР.

Идентичность текста удобно проверять функцией =ЯЧЕЙКА=ЯЧЕЙКА. Смотрите пример на рисунке ниже и описание под ним:

excel проверка ячеек

  1. Введите в ячейку D2 знак “=”
  2. Выделите ячейку B2 с первым значением Планшет DC.
  3. Опять введите “=”.
  4. Выделите ячейку C2 со вторым значением.
  5. Кликните Enter и протяните формулу вниз.

В результате в ячейках отобразится либо “ИСТИНА” (значения совпадают), либо “ЛОЖЬ” (значения не совпадают).

Как видите, не совпадают значения в строке 4: “Компьютер А” и “Компьютер F” (в ячейке D4 видим слово “ЛОЖЬ”).

Использовать функцию можно как в одном, так и в разных файлах.

Что можно предпринять, если текст не совпадает?

  1. Если вы регулярно работаете с этими файлами, поменяйте текст вручную. Единожды потратив на это время, вы значительно сэкономите его в будущем. Если смена текста невозможна, создайте дополнительный столбец, куда введете текст из второго файла.
  2. Иногда текст не совпадает из-за каких-то наборов знаков, которые повторяются в каждой ячейке. К примеру, в одном из файлов везде добавлен артикул: “010-01583 Компьютер AF”, а в другом файле этого артикула нет “Компьютер AF”. В этом случае текст можно заменить автоматически. Об этом читайте в следующих постах.
  • В одном из файлов в тексте содержатся невидимые пробелы. Это хитрая разновидность несовпадения текста. Хитрая, потому что не всегда ее можно заметить. Вот полюбуйтесь:

excel как проверить текст в ячейке

Как быть? Воспользуйтесь формулой =СЖПРОБЕЛЫ(ЯЧЕЙКА). Эта формула убирает все лишние пробелы. Подробнее – в посте о работе с текстом.

  • В одном файле числа сохранены в виде текста, в другом – в виде чисел:

excel число в виде текста

Для устранения проблемы сделайте следующее:

  1. Выделите весь столбец с числами в виде текста (на рисунке выше диапазон B2:В5).
  2. Подведите курсор к значку “!” и кликните по появившейся стрелочке.
  3. Выберите из выпадающего списка “Преобразовать в число” – см. рисунок ниже.
Читайте так же:
Как открыть документ pdf в Word

excel преобразовать текст в число

Вариант 2. ВПР подтянула значения только в верхней части таблицы, в нижней — #Н/Д

  • Смещение диапазона. Чаще всего это происходит, если данные подтягиваются из одного и того же файла. Например, из Листа 1 на Лист 2 или даже из одного и того же листа. Что происходит? Мы тянем формулу вниз, а вместе с ней тянется и диапазон. Вот так:

впр выдает ошибку

Как исправить? Надо закрепить диапазон:

  1. Кликните дважды по ячейке с формулой (С5) – отобразится формула.
  2. В отобразившейся формуле поместите курсор на В12 и нажмите клавишу F4 на клавиатуре.
  3. Переместите курсор на С14 в этой же формуле и опять нажмите F4.
  4. Кликните Enter и формула преобразится, из а) в б)

а) =ВПР(В5; В12:С14 ;2;ложь)

б) =ВПР(В5; $B$12:$С$14 ;2;ложь)

Значок “$” указывает на то, что диапазон закреплен. Теперь при протягивании формулы вниз, он не будет смещаться.

Вариант 3. ВПР подтягивает только часть значений, остальные – #Н/Д

  • В некоторых ячейках текст не совпадает на 100%.

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

Вариант 4. ВПР возвращает ошибку #ССЫЛКА

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

впр excel номер столбца

И еще несколько советов по функции ВПР в Excel:

  1. При вводе формулы используете точку с запятой, а не запятые.
  2. Если протягиваете формулу вправо, не забывайте закреплять ссылку в начале формулы, чтобы формула имела вид: =ВПР( $В5 ;$B$12:$С$14;2;ложь). Для этого наведите курсор на В5 (прямо в формуле) и трижды нажмите клавишу F4 на клавиатуре.
  3. Не используйте аргумент “ИСТИНА” в ВПР, если работаете с текстом. Это приблизительный поиск, в котором будет много ошибок.

9 комментариев к “ ВПР не работает ”

Очень хорошая статья! Без воды и написано именно то, что надо по сути
Но, чтобы статья стала почти исчерпывающей я бы добавил, что иногда ВПР может возвращать ошибку, которая может быть не замечена пользователем, так как не появляется #Н/Д или #ССЫЛКА, однако, при этом функция ВПР() работает некорректно, так как результат ошибочный.
Это происходит в тех случаях, когда в формуле последний аргумент не ЛОЖЬ, а ИСТИНА (или вообще не приведен, а это приравнивается к тому, как если бы была указана ИСТИНА) и при этом первый столбец не отсортирован. Формула вида =ВПР(В5;$B$12:$С$14;2) корректно работать то будет.

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

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