Прочитав статью, вы не только узнаете, как найти данные в таблице Excel и извлечь их в другую, но и приёмы, которые можно применять вместе с функцией ВПР. При работе в Excel очень часто возникает потребность найти данные в одной таблице и извлечь их в другую. Если вы ещё не умеете это делать, то, прочитав статью, вы не только научитесь этому, но и узнаете, при каких условиях вы сможете выжать из системы максимум быстродействия. Рассмотрено большинство весьма эффективных приёмов, которые стоит применять совместно с функцией ВПР. Даже если вы годами используете функцию ВПР, то с высокой долей вероятности эта статья будет вам полезна и не оставит равнодушным. Я, например, будучи IT-специалистом, а потом и руководителем в IT, пользовался VLOOKUP 15 лет, но разобраться со всеми нюансами довелось только сейчас, когда я на профессиональной основе стал обучать людей Excel. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы по вертикали — перебирая строки и фиксируя столбец , а не в столбцах по горизонтали — перебирая столбцы и фиксируя строку. Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР HLOOKUP. ГПР, в противоположность ВПР, производит горизонтальный поиск, однако концепция Excel да и вообще концепция организации данных подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам. Если вы в Excel слишком часто пользуетесь функцией ГПР, то, вполне вероятно, что вы чего-то не поняли в этой жизни. В случае, если массив отсортирован, мы указываем значение ИСТИНА TRUE или 1, в противном случае — ЛОЖЬ FALSE или 0. Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: Вот сейчас надо напрячься и читать следующий абзац несколько раз, пока не прочувствуете смысл сказанного до конца. Там важно каждое слово. Если искомое значение встречается в первом столбце массива несколько раз, то формула выберет первую строку для последующего извлечения данных. Вы добрались до кульминационного места статьи. Казалось бы, ну какая разница, укажу ли я в качестве последнего параметра ноль или единицу? В основном все указывают, конечно же, ноль, так как это довольно практично: Но если у вас на листе несколько тысяч формул ВПР VLOOKUP , то вы заметите, что ВПР вида II работает медленно. При этом обычно все начинают думать:. И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ! Данный факт я проверил вполне надёжно. Секрет такой быстрой работы кроется в том, что на отсортированном массиве можно применять чрезвычайно эффективный алгоритм поиска, который носит название бинарного поиска метод деления пополам, метод дихотомии. Так вот ВПР вида I его применяет, а ВПР вида II ищет без какой-либо оптимизации вообще. То же самое относится и к функции ПОИСКПОЗ MATCH , которая включает в себя аналогичный параметр, а также и к функции ПРОСМОТР LOOKUP , которая работает только на отсортированных массивах и включена в Excel ради совместимости с Lotus А как вы понимаете, вполне может случиться так, что столбец, содержащий необходимую информацию, окажется слева от столбца, в котором мы будем искать. Безусловно, ВПР ищет не только числа, но и текст. При этом надо принимать во внимание, что регистр символов формула не различает. Если использовать символы подстановки, то можно организовать нечёткий поиск. Есть два символа подстановки: Часто поднимается вопрос, как решить проблему лишних пробелов при поиске. Если справочную таблицу ещё можно вычистить от них, то первый параметр формулы ВПР не всегда зависит от вас. Поэтому если риск засорения ячеек лишними пробелами присутствует, то можно применять для очистки функции СЖПРОБЕЛЫ TRIM. Если первый параметр функции ВПР ссылается на ячейку, которая содержит число, но которое хранится в ячейке в текстовом виде, а первый столбец массива содержит числа в правильном формате, то поиск будет неудачным. Возможна и обратная ситуация. Проблема легко решается переводом параметра 1 в необходимый формат:. Перевод числа в текст производится через сцепку с пустой строкой, которая заставляет Excel преобразовать тип данных. Помните, что вместо A2: Хорошей идеей является размещение справочного массива на отдельном листе рабочей книги. Не путается под ногами, да и сохраннее будет. Многие пользователи при указании массива используют конструкцию вида A: C, указывая столбцы целиком. Этот подход имеет право на существование, так как вы избавлены от необходимости отслеживать тот факт, что ваш массив включает все необходимые строки. Если вы добавите строки на лист с первоначальным массивом, то диапазон, указанный как A: C, не придётся корректировать. Безусловно, эта синтаксическая конструкция заставляет Excel проводить несколько большую работу, чем при точном указании диапазона, но данными накладными расходами можно пренебречь. Речь идёт о сотых долях секунды. Ну и на грани гениальности — оформить массив в виде умной таблицы. Если таблица, в которую вы извлекаете данные при помощи ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ для автоматического расчёта номеров извлекаемых столбцов. При этом все ВПР-формулы будут одинаковыми с поправкой на первый параметр, который меняется автоматически! Обратите внимание, что у первого параметра координата столбца абсолютная. Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Это моя первая статья для Лайфхакера. Если вам понравилось, то приглашаю вас посетить мой сайт , а также с удовольствием прочту в комментариях о ваших секретах использования функции ВПР и ей подобных. Пожалуй, самое интересное, что Лайфхакер тестировал за последние месяцы. О том, как решить возникшие с вашей учётной записью проблемы и избежать их появления в будущем. Выбор электрического чайника вроде бы простая задача, но и здесь есть свои тонкости. Получай лучшее на почту. Чтобы получать самые полезные и популярные материалы, оставьте свой email:. Отправить Нажимая на кнопку, вы даёте согласие на обработку своих персональных данных. Синтаксис Функция ВПР имеет четыре параметра: Как же конкретно работает формула ВПР Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона. Схемы работы формул ВПР тип I ВПР тип II Следствия для формул вида I Формулы можно использовать для распределения значений по диапазонам. Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно. Понять, что формула возвращает неправильные значения, в случае если ваш массив не отсортирован, довольно затруднительно. Следствия для формул вида II Если искомое значение встречается в первом столбце массива несколько раз, то формула выберет первую строку для последующего извлечения данных. Производительность работы функции ВПР Вы добрались до кульминационного места статьи. При этом обычно все начинают думать: Недостатки формулы Недостатки ВПР очевидны: Некоторые аспекты применения формулы в реальной жизни Диапазонный поиск Классическая иллюстрация к диапазонному поиску — задача определения скидки по размеру заказа. Поиск текстовых строк Безусловно, ВПР ищет не только числа, но и текст. Борьба с пробелами Часто поднимается вопрос, как решить проблему лишних пробелов при поиске. Разный формат данных Если первый параметр функции ВПР ссылается на ячейку, которая содержит число, но которое хранится в ячейке в текстовом виде, а первый столбец массива содержит числа в правильном формате, то поиск будет неудачным. Проблема легко решается переводом параметра 1 в необходимый формат: Кстати, перевести текст в число можно сразу несколькими способами, выбирайте: Ещё более хорошей идеей будет объявление этого массива в виде именованного диапазона. Использование функции СТОЛБЕЦ для указания колонки извлечения Если таблица, в которую вы извлекаете данные при помощи ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ для автоматического расчёта номеров извлекаемых столбцов. Вот только ВПР не ищет "решение", а просматривает столбец и находит искомое значение: В статье при форматировании два знака минус в формуле превратились в дефис. Там, где идёт речь про "разный формат данных" формула должна выглядеть так: Может, поможете с такой фигней: К каждому этому столбцу, что в 1, что во 2 тянется еще строка с показателями. Мне надо совместить строки по параметру номера. А дальше обычным ВПР. Ну, то есть идея в том, чтобы создать для обеих таблиц общий "ключ". Сделать это можно, превратив ppp в , а дальше - дело техники. К единому значению-то привела У вас всё очень плохо с математикой и с опытом эффективного использования Экселя. Видимо, не приходилось делать ничего сложнее отчётов по продажам. И сразу в консультанты??? Трудно всерьёз воспринимать "специалиста по XLS", который не пользуется стилем R1C1. Знак "доллар" в формуле -- это уровень не выше секретарш. Ну, максимум менеджер по продажам, но никак не специалист. Похоже повсеместное падение уровня знаний уже необратимо. Мне действительно интересно, так как я не умею этого делать кроме как в RC. Послушайте, юноша со взором горящим: Невозможно всерьёз воспринимать человека, который делает глобальные выводы на основе не относящихся к делу или несущественных фактов. Статья разве про системы адресации в Excel? ВПР как-то по другому работает с адресацией A1? Если я в примерах использую вашу любимую RC, статью прочтёт больше людей? С процентами вы правильно заметили неточность. Но к чему этот неумный пафос? И вы ещё сетуете на падение уровня знаний, когда сами демонстрируете провалы в логике, неумение корректно вести дискуссию, а также полное непонимание абсурдности своей аргументации. Денис, спасибо за отличную статью. Добавьте пожалуйста, на своём сайте RSS-ленту. RSS только что прикрутил к блогу. Все новые статьи будут дублироваться анонсами в блоге. Спасибо, что напомнили мне об этом. Можно свою функцию написать без недостатков штатного ВПР - чтобы искал по указанному номеру столбца в массиве, а также чтобы находил указанный номер вхождения. Кому интересно, пишите, помогу. Да, некоторые пишут свой ВПР, но работать он будет куда медленнее, чем native функция. Ну и про бритву Оккама не забывайте: Хорошая статься, вполне доступным языком. Просто в народе больше известна и любима именно ВПР, так как одна функция проще, чем 2. Мб кому-нибудь пригодиться следующий совет по ускорению ВПР: Пусть, к примеру, A1 - адрес ячейки, значение которой мы будем искать; B1: D10 - диапазон, в котором будет проходить поиск, В столбец - содержит искомое значение, D столбец - значение, которое мы хотим получить в результате работы ВПР. Главное требование - отсортированность диапазона B1: D10 по столбцу B порядок сортировки не важен. Далее формула ВПР примет вид: B10; 1; 1 A1; "Значение отсутствует"; ВПР A1; B1: Прирост скорости грубо оценить по формуле не составит труда. Статья классная лишь по тому что дает ответы на вопросы, которые искал очень долго. Есть ряд вопросов по заполнению таблиц, но они выходят за рамки данной статьи. А можно делать поиск искомого значения не в ПЕРВОМ столбце массива а во втором? За счет того что бинарный поиск вместо поиска по каждой ячейке, последовательно делит диапазон ровно на 2, и сравнивает со значением в середине. Если меньше, то будет делить эту половину снова, если больше - будет делить вторую половину. А если искомому значению допустим, значение "Иван" соответствуют несколько значений список состоит из "Маша" и "Катя". Как можно в список вынести эти два значения, относящиеся к "Ивану"? Не нашел пока ответа на вот какой вопрос: Заранее благодарю за ответ! Дайджест Лайфхакера в Telegram. Один раз в день. Лучшее за неделю Просмотры Комментарии. Как убедить кого угодно: Лучше бы я умер вчера: Как повысить качество жизни, не увеличивая расходы. Новое Сейчас на главной. Подписаться Нажимая на кнопку, вы даёте согласие на обработку своих персональных данных. Android Ликбез Вдохновение AliExpress Секс. Материалы сайта предназначены для лиц старше 16 лет.
Возврат денег интернет магазином
Карты тюрьма для minecraft 1.7 2
Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую
Грамматические таблицы таблицы английский
Сон варить картошку
Стих кто рожден был в прошлом веке
Критерии выбора учителем методов обучения
Как в ютубе соединить 2 видео
Что означает понятие фраер
Жвачкадля рукиз клея пва
Расписание электричек киевская крекшино
Поиск текст сообщения phocaguestbook
Функция ВПР в Excel с примером (англ. VLOOKUP)
Samsung note 8 характеристики
Понятие алгоритма свойства алгоритма виды алгоритмов
Инвайт код на лтп 2017
Тест еду обществознание
Расписание рейсов табло
Функция ВПР для Excel — Служба поддержки Office
Возврат денежных переводов
Излишне удержали ндфл у сотрудника что делать
Дневник профгруппорга белпочта образец
Минет видео com