Срок доставки товара в течении 1-3 дней !!!
|
|
Функция ВПР. Использование функции ВПР. Excel - ВПР. Функция впр в экселе пошаговая инструкция
Функция ВПР vlookup в экселе
Функция ВПР в эксель очень полезна для поиска данных по таблицам, когда Ваша таблица — это огромный справочник или база данных. Например, можно найти стоимость товара по его артикулу или размер заработной платы сотрудника по названию должности. Выглядит функция таким образом:
=ВПР или = vlookup (для английской версии)
=ВПР имеет 4 аргумента
- Адрес ячейки, данные из которой нужно найти в таблице;
- Диапазон данных, где будем искать — табличка с данными, наш справочник;
- Номер столбца таблицы-диапазона, где находится искомое значение;
- Ложь или Истина — ищем точное значение либо приблизительное
У нас есть прайс-лист, в котором по артикулу товара нужно выяснить цену для заказчика, в данном случае формула будет выглядеть так:
=ВПР(I3;C2:D33;2;ЛОЖЬ)
где
- I3 — ячейка с номером артикула товара, который мы ищем (Гюю12032556)
- C2:D33 — наша табличка-справочник с артикулом и ценой каждого товара
- 2 — во втором столбце таблички находится цена
- ЛОЖЬ — значит, что нам нужно точное совпадение
Результат работы функции впр в экселе — артикулу Гюю12032556 соответствует цена 4,9
vlookup excel как пользоваться
Другой пример использования функции ВПР в Экселе — заполняем ведомость по зарплате (нужно найти оклад по названию должности)
Имеется две таблички —
- штатное расписание, в котором есть название должности и соответствующий ей оклад
- зарплатная ведомость, где указаны фамилии сотрудников, их должности и сумма получаемой зарплаты
Для того, чтобы не проставлять оклады вручную, данные можно взять из таблицы штатного расписания.
Функция ВПР позволит нам проставить оклады всех сотрудников, ориентируясь на данные, взятые из штатного расписания. Чтобы не заморачиваться ручным вводом адресов (с учетом того, что таблицы находятся на разных листах книги) удобнее всего просто выбирать ячейки при вводе формул. Адреса заполнятся сами.
Итак, функцию ВПР начнем вводить в ячейке С4 таблицы «Зарплатная ведомость — июнь». Аргументами функции будут:
- В11 — ячейка с названием должности (в табличке Зарплатная ведомость), которую мы будем затем искать в штатном
- В10:С16 — диапазон значений, в которых мы будем искать нужные нам данные (оклад по должности) в таблице «Штатное расписание
- 2 — номер столбца в нашем диапазоне (В10:С16), где указан оклад
- ИСТИНА — так мы укажем, что ищем точное совпадение
Так как у нас есть несколько сотрудников и для поиска их окладов придется использовать автозаполнение (копирование формулы), диапазон дополним знаками доллара, чтобы его значение не менялось (абсолютная ссылка).
В итоге формула будет иметь такой вид:
=ВПР(B11;Лист2!$B$10:$C$16;2;ИСТИНА)
как пользоваться функцией впр в excel примеры
С радостью ответим на Ваши вопросы в комментариях:) Пишите
www.informburo.com.ua
Функция ВПР в Excel – примеры использования и советы
Найти значение в таблице поможет функция ВПР в Excel примеры которой описаны ниже в статье.
Во время работы с программой у пользователей часто появляется необходимость быстрого поиска информации в одной таблице и переносе её в другой объект листа.
Понимание принципа работы ВПР существенно упростит вашу работу в Excel и поможет быстрее выполнять задачи.
Содержание:
VLOOKUP (Vertical Lookup) – это еще одно название функции, которое можно встретить в англоязычной версии табличного процессора. Сама аббревиатура ВПР означает «вертикальный просмотр». Анализ данных и их поиск в таблице осуществляется с помощью постепенного перебора элементов от строки к строке в каждой колонке.
Также, в Excel есть противоположная функция под названием HLOOKUP или ГПР – горизонтальный просмотр. Единственное отличие работы опций состоит в том, что ГПР производит поиск в таблице по перебору столбцов, а не строчек. Чаще пользователи отдают предпочтение именно функции ВПР, ведь большинство таблиц имеют больше строк, нежели столбцов.
Как выглядит синтаксис ВПР?
Синтаксис функции в Excel – это набор параметров, с помощью которых её можно вызвать и задать. Запись аналогична с методом записи математических функций. Посмотреть правильный вид опции можно, открыв табличный процессор:
- Используйте уже созданный документ, либо откройте новый пустой лист;
- Кликните на клавишу «Формулы», как показано на рисунке ниже;
- В строке поиска напечатайте «ВПР» или «VLOOKUP» в зависимости от языка программы;
- Настройте категорию «Полный перечень»;
- Кликните на «Найти».
Рис.2 – поиск формул в Эксель
В результате поиска формулы вы увидите её нахождение в перечне. Нажав на элемент, внизу экрана отобразится его формула. За скобками указывается название функции, а внутри скобок – её параметры. Внутри формулы каждый отдельный параметр прописывается в угловых <> скобах. Общий вид описания для ВПР выглядит так:
Рис.3 – перечень параметров
Рассмотрим детальнее каждое из значений, которое описывается в скобках:
- <ЧТО> — первый элемент. Вместо него вам нужно прописать именно то значение, которое вы хотите найти в таблице. Также, можно вписывать адрес ячейки в таблице;
- <НОМЕР_СТОЛБЦА> — тут нужно напечатать номер столбика, в рамках которого будет осуществляться перебор данных.
- <ГДЕ> — здесь юзер определяет количество ячеек, задавая их размерность в виде двумерного массива данных. Первый столбик – это элемент «ЧТО»;
- <ОТСОРТИРОВАНО> — этот элемент функции ВПР отвечает за сортировку первого столбца по возрастанию (первый столбик для «ГДЕ»). В результате успешной сортировки, значение становится истинным (единица). Если возникают какие-либо неточности или ошибки во время ввода параметров – появляется ложное значение сортировки (ноль). Стоит заметить, что во время задания ВПР <ОТСОРТИРОВАНО> можно пропустить, и тогда его значение по умолчанию принимается как истина.
Как работает ВПР. Полезный пример
Чтобы лучше понять принцип работы VLOOKUP, перейдём к рассмотрению конкретных примеров. Возьмём простейшую таблицу с двумя колонками. Пусть она обозначает код и наименование товара.
После заполнения таблицы кликнем на пустую ячейку и выпишем в неё формулу и результат ВПР. Кликните на вкладку «Формулы» и выберите VLOOKUP. Затем введите все необходимые параметры в окно, которое изображено на рисунке 3. Подтвердите действие. В ячейке отобразится результат выполнения команды.
Рис.4 – пример поиска в простой таблице
На рисунке выше в цветных ячейках указывается значение для товара. Если вы не ввели значение для сортировки, то функция автоматически воспринимает это как единицу. Далее программа «думает», что элементы первого столбика вашей таблицы идут по возрастанию сверху-вниз. Благодаря этому, процедура поиска будет остановлена только когда будет достигнута строка со значением, номер которого уже превышает искомый объект.
Рассмотрим еще один пример использования функции, который часто встречается во время реальной работы с прайсами и листами наименований товара. В случае, когда пользователь пропечатывает, что последний элемент в скобках равен нулю, Эксель работает следующим образом: опция проверяет самый первый столбец в заданном диапазоне массива. Поиск будет остановлен автоматически, как только найдется совпадение параметра «ЧТО» и имени товара.
Если в таблице нет введенного вами идентификатора для имени продукции, в результате выполнения поиска VLOOKUP будет получено значение «Н/Д», что означает отсутствие элемента для заданного номера.
Рис.5 – второй пример для ВПР
Когда использовать ВПР?
Выше описаны два варианта применения VLOOKUP.
Первая вариация VLOOKUP подойдет для следующих случаев:
- Когда необходимо разделить значения объекта табличного процессора по его диапазонам;
- Для тех таблиц, в которых параметр ГДЕ может содержать несколько идентичных значений. В таком случае, формула вернет только то, которое находится в последней строчке относительно массива;
- Когда нужно искать значения, которые больше того, которое может содержаться в первом столбике. Так вы найдете последнюю строку таблицы практическим мгновенно.
Первый вариант правописания VLOOKUP не может найти элемент, если не было найдено значения меньше искомого или равное ему. В ячейке для результата вернется только «Н/Д».
Второй вариант для ВПР (с указанием «0» для сортировки) применяется для больших таблиц, в которых встречаются одинаковые названия для нескольких ячеек. VLOOKUP позволит легко оперировать данными, ведь возвращает первую найденную строку.
В реальной жизни опция используется, когда нужно осуществить поиск по заданному диапазону – он не обязательно должен соответствовать всей величине таблицы. В объектах листа, в которых встречаются разные виды значений, ВПР помогает найти текстовые строки.
Рис.6 – пример поиска текстового значения
ВПР бывает полезна, когда нужно удалить много лишних пробелов. Функция быстро находит все наименования с пробелами, и вы сможете быстро удалить их. Пример:
Рис.7 – ВПР при удалении пробелов
Быстродействие VLOOKUP
Большинство пользователей предпочитать не вписывать параметр <СОРТИРОВКА> во время работы с функцией. Конечно же, ноль вписать проще, но игнорирование оператора замедляет поиск. При работе с большими массивами данных Эксель может работать слишком медленно. На старых устройствах табличный процессор иногда даже зависает из-за слишком медленного поиска с ВПР.
Если на одном листе вашего документа представлено сразу несколько тысяч формул, лучше позаботиться о сортировке первого столбца. Это позволяет увеличить общую производительность поиска на целых 400%-500%.
Такая разница в скорости выполнении разных видов VLOOKUP заключается в том, что любой компьютерной программе намного проще работать с уже отсортированными данными, осуществляя бинарный поиск. В первом виде функции применяется бинарный поиск, а во втором – нет, ведь оптимизация этого способа задания функции все еще отсутствует.
Поделиться
ddr64.ru
Использование функции ВПР с примерами
Функция подстановки значений (ВПР)
Уверена, что каждый человек неоднократно сталкивался с необходимостью подставить значения из одной таблицы в другую. Например. На склад пришел товар – пусть это будет некая рекламная продукция. У нас есть перечень данного товара с указанием количества.
Так же мы имеем в отдельном файле прайс-лист на рекламную продукции.
В прайс-листе больше позиций, да и расположены они в другой последовательности. Согласитесь, идея сверить эти два файла и внести цены вручную, механически, оптимизма не внушает. В нашем примере не так много строк, но представьте себе прайс-лист, состоящий из 5000 наименований. Оптимизма еще поубавилось. Попробуем облегчить себе жизнь и заставим немного поработать Excel, тем более, что с этой функцией он справиться на «УРА».
Обратите внимание на то, что для корректной работы функции ВПР в заголовках таблицы не должно быть объединенных ячеек! Выберем ячейку в которую будем подставлять значения, взятые из другого файла. В начем случае это будет колонка «Цена». Выделим ячейку D3 (первую ячейку диапазона, в который необходимо подставить значения). В закладке Формулы найдем кнопку fxи нажмем ее. Появится диалоговое окно мастера фукнций.
Выберем категорию Ссылки и массивы, найдем там функцию ВПР и выделим ее.
Нажмем ОК. Появится окно для ввода аргументов функции. Первый аргумент называется «Искомое значение». В поле ввода напротив этого аргумента укажем блок ячеек столбца В. Сделаем это просто щелкнув на заголовке столбца мышкой.
Второй аргумент называется Таблица. Там необходимо указать диапазон ячеек таблицы, в которой содержится аргумент «Искомое значение» и то значение, которое нам необходимо подставить. Поскольку прайс-лист у нас находится на другом листе, то переходим на лист с названием «прас-лист» и выделяем там полностью столбцы А и В, т. к. в столбце А содержится аргумент «искомое значение» (тот параметр по которому EXCEL поймет, что определенная цена привязана именно к этому товару), а в столбце В содержится то значение, которое нам необходимо подставить в первую таблицу к каждому «искомому значению».
В поле ввода аргумента «Номер столбца» указываем «2», т.к. колонка с ценой в прайс-листе у нас — это второй по счету столбец.
В поле «Интервальный просмотр» пишем ЛОЖЬ, т.к. нам необходимо перенести точные значения, а не приблизительные
Нажмем ОК.
Мы видим, что в ячейку D3 подставилась цена, соответствующая в прайс-листе позиции «Каталог формат А4». Теперь нам осталось только растянуть эту формулу на весь диапазон ячеек, куда нам необходимо поставить значение цены.
Вот что у нас должно получиться.
В принципе можно было бы сказать, что на этом задача выполнена. Но есть одно маленькое НО. В столбце D3 работает функция, мы можем видеть это в строке формул. Это означает, что наши 2 файла неразрывно связаны, т.е. если поменять данные в прайс-листе, то обязательно изменятся и данные в нашем файле, за 15.01.2011. Этого лучше не допускать. Для этого выделим весь диапазон ячеек, в который мы подставили данные, щелкнем правой кнопкой мыши и выберем опцию «Копировать»
Далее не сбрасывая выделения с области ячеек снова щелкнем по ней правой кнопкой мыши и выберем опцию «Специальная вставка».
В появившемся окне установим галочку напротив опции «значения». Нажмем ОК.
Теперь мы видим, что в строке формул подставлены числовые значения, а не формула. Это значит, что связи между двумя файлами нет, а значит и нет угрозы изменения или исчезновения подтянутых нами значений при изменении, закрытии или перемещении «прайс-листа».
klarik.net
Функция ВПР. Использование функции ВПР. Excel
Прикладная программа Excel популярна благодаря своей доступности и простоте, так как не требует особых знаний и навыков. Табличный вид предоставления информации понятен любому пользователю, а широкий набор инструментов, включающих "Мастер функции", позволяет проводить любые манипуляции и расчеты с предоставленными данными.
Одной из широко известных формул Excel является вертикальный просмотр. Использование функции ВПР на первый взгляд кажется довольно сложным, но это только поначалу.
Как работает ВПР Excel
При работе с формулой ВПР следует учитывать, что она производит поиск искомого значения исключительно по столбцам, а не по строкам. Для применения функции требуется минимальное количество столбцов - два, максимальное отсутствует.
Функция ВПР производит поиск заданного критерия, который может иметь любой формат (текстовый, числовой, денежный, по дате и времени и т. д.) в таблице. В случае нахождения записи она выдает (подставляет) значение, занесенное в той же строке, но с искомого столбца таблицы, то есть соответствующее заданному критерию. Если искомое значение не находится, то выдается ошибка #Н/Д (в англоязычном варианте #N/А).
Необходимость использования
Функция ВПР приходит на помощь оператору, когда требуется быстро найти и применить в дальнейших расчетах, анализе или прогнозе определенное значение из таблицы больших размеров. Главное при использовании данной формулы - следить, чтобы заданная область поиска была правильно выбрана. Она должна включать все записи, то есть начиная с первой по последнюю.
Самый частый случай применения ВПР (функция Excel) - это сравнение или добавление данных, находящихся в двух таблицах, при использовании определенного критерия. Причем диапазоны поиска могут быть большими и вмещать тысячи полей, размещаться на разных листах или книгах.
Показана функция ВПР, как пользоваться ею, как проводить расчеты, в качестве примера на рисунке выше. Здесь рассматривается таблица размеров розничных продаж в зависимости от региона и менеджера. Критерием поиска служит конкретный менеджер (его имя и фамилия), а искомым значением является сумма его продаж.
В результате работы функции ВПР (VLOOKUP) формируется новая таблица, в которой конкретному искомому менеджеру быстро сопоставляется его сумма продаж.
Алгоритм заполнения формулы
Расположена формула ВПР во вкладке "Мастер функций" и разделе "Ссылки и массивы". Диалоговое окно функции имеет следующий вид:
Аргументы в формулу вносятся в порядке очереди:
- Искомое значение - то, что должна найти функция, и вариантами которого являются значения ячейки, ее адрес, имя, заданное ей оператором. В нашем случае - это фамилия и имя менеджера.
- Таблица - диапазон строк и столбцов, в котором ищется критерий.
- Номер столбца - его порядковое число, в котором располагается сумма продаж, то есть результат работы формулы.
- Интервальный просмотр. Он вмещает значение либо ЛОЖЬ, либо ИСТИНА. Причем ЛОЖЬ возвращает только точное совпадение, ИСТИНА - разрешает поиск приблизительного значения.
Пример использования функции
Функция ВПР пример использования может иметь следующий: при ведении дел торгового предприятия в таблицах Excel в столбце А записано наименование продукции, а в колонке В - соответствующая цена. Для составления предложения в столбце С нужно отыскать стоимость на определенный продукт, которую требуется вывести в колонке Д.
| А | В | С | Д |
| продукт 1 | 90 | продукт 3 | 60 |
| продукт 2 | 120 | продукт 1 | 90 |
| продукт 3 | 60 | продукт 4 | 100 |
| продукт 4 | 100 | продукт 2 | 120 |
Формула, записанная в Д, будет выглядеть так: =ВПР (С1; А1:В5; 2; 0), то есть =ВПР (искомое значение; диапазон данных таблицы; порядковый номер столбца; 0). В качестве четвертого аргумента вместо 0 можно использовать ЛОЖЬ.
Для заполнения таблицы предложения полученную формулу необходимо скопировать на весь столбец Д.
Закрепить область рабочего диапазона данных можно при помощи абсолютных ссылок. Для этого вручную проставляются знаки $ перед буквенными и численными значениями адресов крайних левых и правых ячеек таблицы. В нашем случае формула принимает вид: =ВПР (С1; $А$1:$В$5; 2; 0).
Ошибки при использовании
Функция ВПР не работает, и тогда появляется сообщение в столбце вывода результата об ошибке (#N/A или #Н/Д). Это происходит в таких случаях:
- Формула введена, а столбец искомых критериев не заполнен (в данном случае колонка С).
- В столбец С внесено значение, которое отсутствует в колонке А (в диапазоне поиска данных). Для проверки наличия искомого значения следует выделить столбец критериев и во вкладке меню "Правка" - "Найти" вставить данную запись, запустить поиск. Если программа не находит его, значит оно отсутствует.
- Форматы ячеек колонок А и С (искомых критериев) различны, например, у одной - текстовый, а у другой - числовой. Изменить формат ячейки можно, если перейти в редактирование ячейки (F2). Такие проблемы обычно возникают при импортировании данных с других прикладных программ. Для избежания подобного рода ошибок в формулу ВПР есть возможность встраивать следующие функции: ЗНАЧЕН или ТЕКСТ. Выполнение данных алгоритмов автоматически преобразует формат ячеек.
- В коде функции присутствуют непечатные знаки или пробелы. Тогда следует внимательно проверить формулу на наличие ошибок ввода.
- Задан приблизительный поиск, то есть четвертый аргумент функции ВПР имеет значение 1 или ИСТИНА, а таблица не отсортирована по восходящему значению. В этом случае столбец искомых критериев требуется отсортировать по возрастанию.
Причем при организации новой сводной таблицы заданные искомые критерии могут находиться в любом порядке и последовательности и не обязательно вмещаться полным списком (частичная выборка).
Особенности использования в качестве интервального просмотра 1 или ИСТИНЫ
Ошибка под №5 является довольно распространенной и наглядно изображена на рисунке ниже.
В данном примере список имен согласно нумерации отсортирован не по возрастанию, а по ниспадающему значению. Причем в качестве интервального просмотра использован критерий ИСТИНА (1), который сразу прерывает поиск при обнаружении значения большего, чем искомое, поэтому выдается ошибка.
При применении 1 или ИСТИНЫ в четвертом аргументе нужно следить, чтобы столбец с искомыми критериями был отсортирован по возрастанию. При использовании 0 или ЛЖИ данная необходимость отпадает, но также отсутствует тогда возможность интервального просмотра.
Просто следует учитывать, что особенно важно сортировать интервальные таблицы. Иначе функция ВПР будет выводить в ячейки неправильные данные.
Другие нюансы при работе с функцией ВПР
Для удобства работы с такой формулой можно озаглавить диапазон таблицы, в которой проводится поиск (второй аргумент), как это показано на рисунке.
В данном случае область таблицы продаж озаглавлена. Для этого выделяется таблица, за исключением заголовков столбцов, и в поле имени (слева под панелью вкладок) присваивается ей название.
Другой вариант - озаглавить - подразумевает выделение диапазона данных, потом переход в меню "Вставка"- "Имя"- "Присвоить".
Для того чтобы использовать данные, размещенные на другом листе рабочей книги, при помощи функции ВПР, необходимо во втором аргументе формулы прописать расположение диапазона данных. Например, =ВПР (А1; Лист2!$А$1:$В$5; 2; 0), где Лист2! - является ссылкой на требуемый лист книги, а $А$1:$В$5 - адрес диапазона поиска данных.
Пример организации учебного процесса с ВПР
Довольно удобно в Excel ВПР-функцию применять не только фирмам, занимающимся торговлей, но и учебным учреждениям для оптимизации процесса сопоставления учеников (студентов) с их оценками. Примеры данных задач показаны на рисунках ниже.
Существуют две таблицы со списками студентов. Одна с их оценками, вторая указывает возраст. Необходимо сопоставить обе таблицы так, чтобы наравне с возрастом учащихся выводились и их оценки, то есть ввести дополнительный столбец во втором списке.
Функция ВПР отлично справляется с решением данной задачи. В столбце G под заголовком "Оценки" записывается соответствующая формула: =ВПР (Е4, В3:С13, 2, 0). Ее нужно скопировать на всю колонку таблицы.
В результате выполнения функция ВПР выдаст оценки, полученные определенными студентами.
Пример организации поисковой системы с ВПР
Еще один пример применения функции ВПР - это организация поисковой системы, когда в базе данных согласно заданному критерию следует найти соответствующее ему значение. Так, на рисунке показан список с кличками животных и их принадлежность к определенному виду.
При помощи ВПР создается новая таблица, в которой легко найти по кличке животного его вид. Актуальны подобные поисковые системы при работе с большими списками. Для того чтобы вручную не пересматривать все записи, можно быстро воспользоваться поиском и получить требуемый результат.
fb.ru
что такое, для чего нужна и как она работает, примеры + видео
Microsoft Office Excel — это табличный редактор, который позволяет даже обычному пользователю персонального компьютера работать с числовыми данными: подсчитывать, делать выборку по различным параметрам, задавать функции, одной из которых является ВПР в Excel.
Видео по использованию функции ВПР Excel
Что такое ВПР в Excel?
Начать работу с функцией ВПР можно, создав ее при помощи мастера функций, либо вручную вбив название функции в ячейку после знака равно
Эта функция в Excel очень полезна, так как позволяет находить необходимое пользователю в одной таблице редактора и переносить в другую. Это аббревиатура, которая расшифровывается как «вертикальный просмотр». Офисная программа имеет ее аналог — функцию, которая находит искомое не в столбцах, а в строках, то есть по горизонтали (ГПР). Однако они никогда не станут равнозначными. Это объясняется просто. Excel, как правило, задействует больше строк, нежели столбцов. В связи с этим поиск данных по столбцам является более востребованным, чем по горизонтали, то есть по строкам.
Функция ВПР является одной из востребованных в Экселе, конечно, для тех, кто имеет представление о ее существовании.
Как работает ВПР?
Пример задания аргументов функции ВПР в экселе
Как всякая функция Excel, данная имеет собственный синтаксис, которому необходимо следовать. Она включает четыре параметра: искомое значение, ссылку на диапазон значений, номер столбца, из которого будет возвращено искомое значение, и последний параметр — «отсортировано». Он может быть представлен как истина или ложь или вовсе опущен. Тогда он считается как 1 — истина.
Если последний параметр «отсортировано» опущен, то есть равен 1, что означает, что столбец является отсортированным, при работе эта функция остановит сортировку данных на строке, предшествующей искомой. Если она не задана, то она возвращает последнюю строку.
Если же последний параметр в функции Excel ВПР задан как 0, то она последовательно проходит по первому столбцу, просматривая его, и сразу же останавливает поиск, когда находит в столбце точное соответствие с первым параметром. Однако может произойти так, что такое соответствие может быть не найдено, тогда ВПР вернет ошибку функции. Случается и так, что в первом столбце она встретит несколько одинаково подходящих значений, тогда выберет первое и перенесет в другую таблицу.
Примечательно, что Excel работает с разными типами данных, а не только с числовыми. ВПР в состоянии найти также текстовое значение. В этом случае существенным минусом, влияющим на качество поиска, могут стать лишние пробелы начала строки. Из-за них ВПР способна вернуть неправильное или необычное значение. Чтобы предотвратить искажение информации, используйте соответствующий параметр Excel для устранения пробелов. Таким образом, функция ВПР является очень лояльной и работает в сотрудничестве с другими, например, с СЖПРОБЕЛЫ и другими.
Кроме того, может случиться так, что в сравнении будут участвовать как целые, так и дробные значение. Здесь проблема заключается в разделительном знаке — точка или запятая. Если они различны, то выборку сделать окажется весьма сложно. Примените возможность Эксель «найти и заменить», что найдете на панели инструментов.
Еще одно, что может привести к неправильной работе этой функции в Excel, это разные форматы ячеек имеющейся таблицы. Коварство этого заключается в том, что пользователь видит два числа и не понимает, почему ВПР не работает. Несмотря на то, что в ячейке таблицы находится число, но формат этой ячейки задан как текстовый. В этом случае достаточно просто поменять его на числовой с помощью соответствующей команды.
Естественно, такой же поиск необходимого значения можно выполнить вручную, только сколько времени потребуется для этого? Не менее 20 минут для документа, имеющего около 100 строк. А ведь в документе может быть тысяча строк, и не одна тысяча, а десятки. Тогда ручной способ займет очень много времени, сил. ВПР позволяет сделать такой поиск за интервал времени чуть более одной секунды. Экономия времени большая, а собственные трудозатраты минимальны.
При всех плюсах ВПР имеет недостатки, которые могут помешать пользователям. Это и понятно, ведь функция ищет значения только в первом столбце указанного массива, а искомое значение может оказаться и в другом месте.
Функция ВПР незаменима при необходимости объединить данные из разных таблиц, имеющих какое-либо общее поле (наименование, штрих-код и т.д.)
Даже человек, который работает с Эксель и считает себя уверенным пользователем, может найти для себя много нового в работе функций Excel, в частности функция ВПР, помогающая находить разные данные в заданных столбцах, что важно при работе с подобным мощным редактором. Что можно найти с помощью ВПР? Плотность, время, фамилию, месяц, сумму, число, другие нужные в работе и повседневной жизни данные. Освойте, если не все, то большее количество возможностей табличного редактора, и оцените, как быстро и качественно вы сможете производить даже самые сложные расчеты. Недаром им пользуются бухгалтеры, экономисты, банковские служащие, работающие с информационными массивами.Занимаетесь бизнесом? Сопоставьте свои данные с конкурентными и высчитайте с помощью возможностей Экселя, например, собственные потери и доходы, трудозатраты, а затем найдите искомое значение с применением ВПР. Не бойтесь экспериментировать, самостоятельно и с помощью самоучителя находить новое, ведь даже опытные пользователи не могут похвастать знанием хотя бы 90% этого табличного редактора.
itguides.ru
Как применить функцию ВПР в Excel для поиска данных на листе
Функция ВПР в Excel нужна для того чтобы объединить несколько таблиц в одну или "подтянуть за уши" данные одной таблицы в другую. Название происходит от сокращения - вертикальный просмотр....Странное название, если учесть какую важную роль играет она в Excel.
Как в Excel использовать функция ВПР
Итак, представим, что у нас есть две таблицы Excel: и в одной и в другой есть общее поле, или столбец, кому как удобно... К примеру, в первой находится код товара, наименование товара, а в другой тот же код товара и цена на него. Лично мне удобно разносить таблицы по листам, первая таблица будет на Лист1, вторая на Лист2. В свое время эта привычка пошла от срочной необходимости составлять отчеты на работе, из разных таблиц, в Access 2000... Её как раз и использовал вместо замечательной функции ВПР ...
Итак, функция ВПР находится в категории Ссылки и Массивы, нажимаем на значок вставка функции, выбираем категорию, жмем Ок.
Далее,в окне аргументов функции впр заполняем данные: Искомое значение: B2-это ячейка кода товара Таблицы №1, Таблица: Лист2!$B$4:$C$6-это диапазон ячеек в Таблице №2,
символ $ нужен для того чтоб при копировании или протягивании формулы по столбцу это значение не менялось динамически. У нас остались два аргумента, Номер_столбца и Интервальный_просмотр. Номер столбца:2 - в Таблице №2 во втором столбце находится цена товара, её нам и нужно вытянуть в Таблицу №1. Интервальный_просмотр: 0- это значит точное совпадение, если поставить 1, то это будет приблизительное значение. Оно вам нужно? ))) Когда все внесли, нажимаем Ок и смотрим результат.
Функция впр в excel
Для удобства просмотра я перенес Таблицу №2 на первый лист. Все совпало, протягиваем или копируем формулу в ячейки D5 и D6, Таблице №1 добавляем название столбца- Цена товара. Вот так с помощью функции впр мы совместили данные из двух таблиц, находящихся на разных листах, хотя можно и на одном листе и даже в разных книгах.
Важное дополнение: функция ВПР будет работать корректно при двух важных условиях: и в первой таблице и во второй - общее, ключевое поле должно быть на первом месте, и эти таблицы должны быть отсортированы по этому полю!
В этом видео подробно показано, как использовать функцию ВПР в Excel:
Рекомендуем смотреть видео в полноэкранном режиме, в настойках качества выбирайте 1080 HD, не забывайте подписываться на канал в YouTube, там Вы найдете много интересного видео, которое выходит достаточно часто. Приятного просмотра!
С уважением, авторы сайта Компьютерапия
Понравилась статья? Поделитесь ею с друзьями и напишите отзыв в комментариях!
maxfad.ru
Функция ВПР в Excel (пример 2)
В статье «Функция ВПР в Excel с примером» мы рассмотрели основные понятия свзаные с функцией ВПР, синтаксис и пример использования функции. Сегодня мы рассмотрим пример использования функции ВПР, когда есть необходимость прописать быстро и много типовых функций ВПР. Допустим, у нас есть таблица данных с информацией о товаре по артикулу, нам требуется по определенным товарам подтянуть часть/всю таблицу.
Имеем две таблицы, первая с исходными данными а вторая таблица, которую нужно заполнить:
Таблица данных
пропишем в первую ячейку заполняемой таблицы обычную функцию ВПР =ВПР($A22;$A$4:$G$16;2;0):
Функция ВПР
Поменяем в функции ВПР цифру 2 (номер выводимого столбца) на переменную, в качестве которой будет выступать адрес ячейки и пропишем номера столбцов первой таблицы в строке 2 (т.е. мы не в самой формуле прописываем константу, а прописываем ее в ячейках, в формулах же будем ссылаться на эти ячейки). Таким образом, функция будет иметь вид:
=ВПР($A22;$A$4:$G$16;B$2;0) :
Измененная Функция ВПР
Теперь протаскиваем функцию ВПР вниз и вправо:
Протаскиваем функцию ВПР
Получили быстро заполненную таблицу (подтянули ВПРом данные по артикулу с динамическим номером солбца вывода):
Заполнение таблицы функцией ВПР
Очень надеемся, что наша статья помогла Вам в решении Вашей проблемы. Будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.
sirexcel.ru
