Excel works

Excel works!

Функция ВПР в Excel. Поиск и возвращение текста по списку

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

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

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

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

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

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

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

Внедрение ВПР в Excel и решение примера

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

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

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

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

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

Комменты по ВПР

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

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

Что в итоге:

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

ITGuides.ru

Вопросцы и ответы в сфере it технологий и настройке ПК (Персональный компьютер — компьютер, предназначенный для эксплуатации одним пользователем)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Спасаемся от рутинной работы средством функции ВПР в Excel

Наверное почти всем активным юзерам табличного редактора Excel временами приходилось сталкиваться с ситуациями, в которых возникала необходимость подставить значения из одной таблицы в другую. Вот представьте, на ваш склад зашёл некоторый продукт. В нашем распоряжении имеется два файла: один с списком наименований приобретенного продукта, 2-ой — прайс-лист этого самого продукта. Открыв прайс-лист, мы обнаруживаем, что позиций в нём больше и размещены они не в той последовательности, что в файле с списком наименований. Навряд ли кому-то из нас понравится мысль сверить оба файла и перенести цены из 1-го документа в иной вручную. Очевидно, в случае, когда речь идёт о 5–10 позициях, механическое внесение данных полностью может быть, но что созодать, если число наименований переваливает за 1000? В таком случае совладать с однотонной работой нам поможет Excel и его магическая функция ВПР (либо vlookup, если речь идёт о английской версии программки).

Что такое ВПР и как ею воспользоваться?

Итак, в начале нашей работы по преобразованию данных из одной таблицы в другую будет уместным создать маленькой обзор функции ВПР. Как вы, наверняка, уже успели осознать, vlookup дозволяет переносить данные из одной таблицы в другую, заполняя тем нужные нам ячейки автоматом. Для того чтоб функция ВПР работала корректно, направьте внимание на наличие в заголовках вашей таблицы объединённых ячеек. Если таковые имеются, для вас нужно будет их разбить.

Итак, перед нами стоит задачка — перенести цены имеющихся продуктов в таблицу с их наименованиями и высчитать общую стоимость всякого продукта. Чтоб это создать, нам предстоит выполнить последующий метод:

1. Для начала приведите таблицу Excel в нужный для вас вид. Добавьте к заготовленной матрице данных два столбца с наименованиями «Стоимость» и «Стоимость». Изберите для ячеек, находящихся в спектре новообразовавшихся столбцов, валютный формат.
   
2. Сейчас активируйте первую ячейку в блоке «Стоимость» и вызовите «Мастер функций». Создать это можно, нажав на клавишу «fx», расположенную перед строчкой формул, либо зажав комбинацию кнопок «Shift+F3». В открывшемся диалоговом окне найдете категорию «Ссылки и массивы». Тут нас не интересует ничего не считая функции ВПР. Изберите её и нажмите «ОК». К слову, следует сказать, что функция VLOOKUP быть может вызвана через вкладку «Формулы», в выпадающем перечне которой также находится категория «Ссылки и массивы».
   
3. Опосля активации ВПР перед вами раскроется окно с списком аргументов избранной вами функции. В поле «Разыскиваемое значение» для вас будет нужно внести спектр данных, содержащийся в первом столбце таблицы с списком поступивших продуктов и их количеством. Другими словами для вас необходимо сказать Excel, что конкретно ему следует отыскать во 2-ой таблице и перенести в первую.
   
4. Опосля того как 1-ый аргумент обозначен, можно перебегать ко второму. В нашем случае в роли второго аргумента выступает таблица с прайсом. Установите курсор мыши в поле аргумента и переместитесь в лист с списком цен. Вручную выделите спектр с ячейками, находящимися в области столбцов с наименованиями товарной продукции и их ценой. Укажите Excel, какие конкретно значения нужно сравнить функции VLOOKUP.
5. Для того чтоб Excel не путался и ссылался на нужные для вас данные, принципиально зафиксировать заданную ему ссылку. Чтоб создать это, выделите в поле «Таблица» требуемые значения и нажмите кнопку F4. Если всё выполнено правильно, на дисплее должен показаться символ $.
   
6. Сейчас мы перебегаем к полю аргумента «Номер странички» и задаём ему значения «2». В этом блоке находятся все данные, которые требуется выслать в нашу рабочую таблицу, а поэтому принципиально присвоить «Интервальному просмотру» неверное значение (устанавливаем позицию «ЛОЖЬ»). Это нужно для того, чтоб функция ВПР работала лишь с точными значениями и не округляла их.
   

Сейчас, когда все нужные деяния выполнены, нам остаётся только подтвердить их нажатием клавиши «ОК». Как в первой ячейке поменяются данные, нам необходимо будет применить функцию ВПР ко всему Excel документу. Для этого довольно размножить VLOOKUP по всему столбцу «Стоимость». Создать это можно с помощью перетягивания правого нижнего уголка ячейки с изменённым значением до самого низа столбца. Если все вышло, и данные поменялись так, как нам было нужно, мы можем приступить к расчёту общей цены наших продуктов. Для выполнения этого деяния нам нужно отыскать произведение 2-ух столбцов — «Количества» и «Цены». Так как в Excel заложены все математические формулы, расчёт можно предоставить «Строке формул», воспользовавшись уже знакомым нам значком «fx».

Принципиальный момент

Чудилось бы, всё готово и с нашей задачей VLOOKUP совладала, но не тут-то было. Дело в том, что в столбце «Стоимость» как и раньше остаётся активной функция ВПР, свидетельством этого факта является отображение крайней в строке формул. Другими словами обе наши таблицы остаются связанными одна с иной. Таковой тандем может привести к тому, что при изменении данных в таблице с прайсом, поменяется и информация, содержащаяся в нашем рабочем файле с списком продуктов.

Схожей ситуации лучше избежать средством разделения 2-ух таблиц. Чтоб это создать, нам нужно выделить ячейки, находящиеся в спектре столбца «Стоимость», и щёлкнуть по нему правой клавишей мыши. В открывшемся окошке изберите и активируйте опцию «Копировать». Опосля этого, не снимая выделения с избранной области ячеек, вновь нажмите правую клавишу мыши и изберите опцию «Особая вставка».

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

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

Как сопоставить две таблицы в Excel?

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

Возможность работы с несколькими критериями

Ещё одним бесспорным достоинством функции VLOOKUP является его способность работать с несколькими параметрами, присущими вашему товару. Чтоб отыскать продукт по двум либо наиболее чертам, нужно:

1. Сделать два (либо, по мере необходимости, наиболее) критерий для поиска. , в который в процессе работы функции добавятся все остальные столбцы, по которым происходит поиск продукта.
2. В приобретенном столбце, по вышеперечисленному методу, вводим уже знакомую нам формулу функции VLOOKUP.

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