Найти данные из одной таблицы в другой
Найти данные из одной таблицы в другойСкачать файл - Найти данные из одной таблицы в другой
Формула настолько полезна, что может служить индикатором навыков работы в Excel. Кто не умеет пользоваться ВПР, тот находится на низшей ступени развития, питекантроп фактически. На самом деле функция ВПР не очень известная новичкам, но в то же время очень распространена среди бывалых юзеров и лузеров Excel. Кто часто использует Excel, и не только в качестве калькулятора, наверняка сталкивался с ситуацией, когда данные из одной таблички по какому-то характерному признаку нужно перенести в другую табличку, где присутствует тот же характерный признак. Например, складские остатки товаров из одной таблички необходимо поставить рядом с продажами соответствующих товаров в другой табличке и т. Такая задача довольно-таки распространена. Я лично сталкиваюсь с подобным по несколько раз в день, иногда даже в час. Давайте теперь посмотрим, что такое ВПР в целом, что она умеет, как работает и где следует применять. Диапазоны данных могут находиться где угодно: Собственно, как и любая другая функция. Работу формулы ВПР предлагаю рассмотреть на примере. Для этого представим, что перед нами стоит задача определить стоимость проданных товаров. Стоимость рассчитывается, как произведение количества и цены. Сделать это очень легко, если количество и цены находятся в соседних колонках. Однако данные могут быть представлены не в столь удобном виде. Например, исходная информация может находиться в совершенно разных таблицах диапазонах данных и в другом порядке. В первой таблице указаны количества проданных товаров:. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится — наименования товаров не совпадут. Данные по многим товарам не соответствуют друг другу. Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, то есть цену товара А к количеству товара А, цену Б к количеству Б и т. Есть любители делать подобную операцию вручную, однако данную задачу элегантно решает функция ВПР. Собственно, легко, хотя может быть и не совсем понятно с первого раза. Добавим вначале в первую таблицу новый столбец, в котором будут проставлены необходимые цены посредством функции ВПР. Формулу ВПР, как и любую другую, можно вызвать с помощью Мастера функций, а можно и вручную прописать. Рассмотрим оба варианта, хотя конечный результат будет одинаковым. Каждый выбирает, как ему удобно. Для вызова функции с помощью Мастера нужно активировать ячейку, где будет прописана формула и нажать кнопку f x в самом начале строки формул. Появится диалоговое окно Мастера, где из списка всех функций нужно выбрать ВПР. Теперь нужно заполнить предлагаемые поля. В нем нужно указать диапазон данных, где будет осуществляться поиск нужных значений. В нашем случае это вторая таблица с ценой. При этом нужно понимать важную особенность. Крайний левый то есть первый слева столбец выделяемого диапазона должен содержать те самые критерии, по которым осуществляется поиск, то есть это должен быть столбец с наименованиями товаров. Затем таблица выделяется вправо минимум до того столбца, где находятся искомые значения цены. Можно и дальше вправо выделить, но это уже ни на что не влияет, так как столбец с искомыми значениями будет однозначно определен следующим параметром. Главное, чтобы выделенная таблица начиналась со столбца с критериями и захватывала нужный столбец с данными. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 первый — критерий, второй - цены. Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов. Это не важно, Excel все сосчитает. Сейчас ставим 0 или ЛОЖЬ. Если все правильно и значение критерия есть в обеих таблицах, то на месте только что введенной формулы появится некоторое значение. Остается только протянуть или просто скопировать формулу вниз до последней строки таблицы. Собственно, все, расчет готов. Теперь можно рассчитать стоимость с помощью простого умножения количества на цену, а формулу затем протянуть вниз до конца таблицы, так как пары цена-количество теперь совпадают. Формулу ВПР можно прописать непосредственно в ячейке, как я обычно и делаю. Это позволяет увеличить скорость работы в Excel. Как ни странно, написать формулу вручную получается быстрее, чем заполнить кучу полей в Мастере функций. Так выглядит та же формула, написанная пальчиками. Для того, чтобы сократить количество возможных ошибок, неплохо бы представить, как формула ВПР находит нужные значения. После правильного указания параметров функции ВПР, алгоритм работает следующим образом. Функция ВПР имеет свои заморочки и особенности, о которых следует помнить, дабы не сильно расстраиваться, когда вместо значений появляются ошибки. Первую особенность можно считать общей для функций, которые используются для многих ячеек путем прописывания формулы в одной из них и дальнейшим копированием в остальные. Здесь нужно обращать внимание на относительность и абсолютность ссылок. Ну, это я уже говорил. Данная особенность многих раздражает, так как в этом случае трудно подсчитать итоги сумму , среднюю и др. Проблему можно обойти разными способами. Если в качестве критерия используются числовые значения числовые коды, артикулы и проч. Данное обстоятельство многих ставит в тупик и заставляет думать, что ВПР не работает. На самом деле пугаться не нужно. Часто достаточно проверить совпадение формата полей с критерием и, если надо, сделать их одинаковыми. Либо опять же использовать функцию СУММЕСЛИ — ей формат по барабану. Еще одно замечание на счет поля с критерием. К примеру, лишний пробел между словами или одна неправильная буква делает, казалось бы, одинаковые значения критерия несопоставимыми. Поэтому артикул или штрихкод товара, вполне годятся, а вот название в несколько слов для критерия лучше не использовать. Функция ВПР из диапазона с искомыми данными выдает первое сверху значение. Об этом следует помнить. К примеру, если мы хотим к цене товара подтянуть количество из другой таблицы, а там этот товар встречается несколько раз в нескольких строках , то к цене подтянется первое сверху количество. Последний параметр формулы, который 0 нуль , ставить нужно обязательно. Иначе формула может криво работать. После использования ВПР саму формулу лучше сразу удалить, оставив только полученные значения. Делается это очень просто. Есть еще одна похожая функция — ГПР. Сам никогда не пользовался, но чисто теоретически все то же самое. Контакты ИП Езепов Дмитрий Александрович. Этот адрес электронной почты защищён от спам-ботов. У вас должен быть включен JavaScript для просмотра. Главная Методы Описание данных Индексы Группировка Проверка гипотез Многомерный статанализ Динамика и прогнозирование Тервер Управление запасами. Формулы Форматирование Работа с данными Диаграммы Трюки Сводные таблицы Power Query. Статистическая обработка данных Разработка систем управления запасами Обработка данных в Excel Реклама на сайте. Главная Excel Формулы Функция Excel ВПР VLOOKUP и ГПР HLOOKUP с примерами использования. Дмитрий Езепов Формулы 05 января Функция Excel ВПР VLOOKUP и ГПР HLOOKUP с примерами использования. Добрый день, многоуважаемые читатели блога statanaliz. Пример использования функции ВПР Работу формулы ВПР предлагаю рассмотреть на примере. В первой таблице указаны количества проданных товаров: Во второй — данные о ценах: Открывается следующее диалоговое окно. Все, поля заполнены и выглядят примерно так. Все понятно, даже для новичка! Могли бы вы подсказать, какую формулу можно использовать для сложения данных, в том случае, если в критерий поиска не совпадет полностью. Помимо оплат в первом столбце есть 'Продажа.. Так вот нужно сложить все оплаты при условии, что номер у каждой свой Или в одном столбце есть разные критерии, которые нужно считать общими? Вариант выбора формулы сильно зависит от конкретных данных. Если сложно объяснить, пришлите файл. Это только кусочек из общего массива информации, поэтому СУММ применять неудобно. Если пришлете кусок файла, я вышлю готовую формулу. Нужно просто внимательно смотреть на данные, как они записаны. По тексту не всегда понятно. Возможно, придется добавлять дополнительный столбец, чтобы создать критерий нужного вида. Скорее всего нужна формула СУММЕСЛИ. Вопрос остается критерием только. Помогите пожалуйста новичку пример - Москва, проспект победы, в поле для адреса в формуляре наклодной не вписывать опять Москва, проспект. Внимательно посмотрите в статье, как прописываются ссылки. Если не получается, пришлите файл, помогу. Я же не вижу Ваших данных. Все доступно и понятно! Подскажите как это сделать? Скорее всего, потребуется функция ЕСЛИ. А дальше нужно смотреть по факту, что куда писать. На втором из листов ежедневно в колонку будет добавляться новое значение, то есть количество ячеек будет каждый день увеличиваться как бы вниз. А на первом листе нужно выводить последнее из введенных значений на втором листе из колонки. Но конкретная формула зависит от того, как расположены данные. Например, на втором листе в ячейке А1 находится название столбца. Далее вниз идет перечень значений, которые каждый день добавляются. Вначале берется точка отсчета А1 на втором листе. Далее функция СЧЁТЗ подсчитывает, сколько во всем столбце А непустых значений. Это число за минусом 1 будет аргументом для функции СМЕЩ, которая отсчитает от А1 получившееся число вниз и вернет последнее значение в диапазоне. Только нельзя, чтобы в этом столбце были пропуски, иначе фокус не получится. Если исходные данные выглядят по-другому, придется еще немного подумать. Но в целом, примерно так. У меня просто в функции СЧЕТ3 первый агрумент в скобках подхватывается только с номером строки то есть А1, но просто 'A' не ставится. Мой эксель по синтаксису просит 4 аргумента. A -1 - это смещение по строкам вниз до последней заполненной ячейки. Нам не нужно, поэтому ставим 0 указать обязательно. Там есть еще два аргумента, указывающие ширину и высоту диапазона, но Вам нужна только одна ячейка, поэтому последние аргументы не заполняются. Чтобы указать весь столбец, его нужно просто выделить. Вручную писать не обязательно. У меня две таблицы, в первой и второй по три столбца. В первой таблице два столбца совпадают по наименованию, а в третьем столбце идёт разная цена на данные наименования. Во второй таблице так же в первых двух столбцах наименование совпадает с первой таблицей. Мне нужно во вторую таблицу внести сумму из третьего столбца первой таблицы по наименованиям из двух таблиц, по совпадению. Уже голову сломала, не могу и все. Иначе пришлите кусок файла, чтобы было понятней что куда складывать. Формулу ВПР прописала, но она только для одного наименования из списка. Вообще, должно работать без проблем. С5;2;0 , где В3: С5 из первой таблицы. Получается только для Москвы, а как добавить Курск? Задача подтянуть артикул и 1-й табл во 2-ю по наименованию. Проблема в том, что наименование состоит из 5 и более слов и иногда отличается друг от друга, например, добавлено еще одно слово. Плохая идея подтягивать данные по названию из нескольких слов. Нужен другой, более короткий критерий. Или придется работать со словами, убирая лишнее, чтобы критерии совпадали. Предполагаю, что Ваша ГПР - это формула массива и тогда 3-й аргумент берет разные значения для номера строки. И нигде нет информации, что вместо номера строки, можно вставить последовательность чисел. Я посмотрю по факту. А то будем долго гадать. Когда смотришь, вроде всё понятно. Но как начинается реальная жизнь, то теряешься по причине несовпадения примеров. Вверху люди задают много вопросов, на которые Вы отвечаете по почте. И ответ не видят остальные. Поэтому поток вопросов продолжается. Не могли бы Вы создать всего по одной папке с документами Ексель на каждую тему. Люди будут туда отправлять прилагаемы таблицы к вопросам тут, а другие люди смогут сразу посмотреть различные примеры применения формул и решения возникающих проблем. Это намного нагляднее, лучше, проще. Вопросов к Вам будет немного меньше, а интерес выше, так как за примерами мне придется уходить от Вас и идти на другие страницы. Хотелось бы поучиться у Вас. ВПР действительно капризная функция и об этом есть отдельная статья http: Не уверен, что будет пользоваться спросом. Да и проблемы обычно разные. Поэтому напишите Ваш вопрос, я помогу, чем смогу. Для Вас это будет быстрее, чем читать чужие истории. Подскажите, пожалуйста, есть 2 списка, которые нужно скрестить. Никак не могу разобраться как это сделать в Ексель! Нужно 'подтянуть' данные из одной таблицы в другую по указанному критерию? Так это обычный ВПР. Или пришлите посмотреть данные адрес слева внизу. По другим сайтам пробовала, не получалось у меня, а здесь так же все сделала и так все понятно и все получилось. В данном случае цифру 35 нужно подтянуть из другой таблицы. Вместо переменного числа пишете формулу: Данные во второй таблице по ним есть. Есть таблица номенклатура,розничная цена и закупочная цена При выпадающей номенклатуре появляется розничная цена. Сейчас добавляется закупочная цена. Как привязать к номенклатуре обе цены? Пробовала сделать как здесь описано,розничная цена заменилась на закупочную. Как добавить два критерия поиска? Цены на разных листах. Из описания трудно понять. Пришлите, пожалуйста, пример с пояснением, что должно быть на выходе. Подтягиваю данные о стаже работы тз доп. Что я могу делать неправильно? Вы все делаете правильно. Просто ВПР 'подтягивает' содержимое ячейки, которое в Вашем случае равно 0, Сделаете для нужной ячейки процентный формат. Доброго дня вам Цитирую Дмитрий Езепов: Помогите пожалуйста с формулой. Имеется две таблицы данных. Исходная таблица содержит код товара, место где он должен лежать и количество которое должно быть на этом месте код место количество б1 20 Вторая таблица содержит аналогичные данные но только по фактическому расположению товара, при этом товар может лежать на нескольких местах код место количество б1 20 б2 2 б3 10 Необходимо в сводной таблице выявить лежит ли товар на заданном месте и в коком количестве, а так же определить места с которых его необходимо перенести, чтоб на заданном месте находилось нужное количество. ВПР подтягивает только одно значение из второй таблицы. ВПР здесь не очень поможет. Вставляете одну таблицу аккуратно под второй. При этом добавляете колонку рядом. Для первой таблицы, например, указываете План, для второй - Факт. В середину таблицы - количество. Должно получиться, если я все правильно понял. Через сводную все получилось. Теперь не могу поставить условное форматирование. Необходимо сравнить две соседние ячейки в строке. Начинаю протягивать условие по столбцу -встает условие к начальной ячейке а не к соседней. Мне необходимо чтобы Эксель нашёл в первом списке записи из второго и удалил их. Как это сделать не вручную? К первому списку подтянул бы по ВПР значения из второго. Поставил фильтр и удалил все что не НД то есть подтянутые значения. Cтатистический анализ данных в MS Excel и R. Хочешь заработать на моих курсах? Популярное Дисперсия, среднеквадратичное стандартное отклонение, коэффициент вариации Расчет дисперсии, среднеквадратичного стандартного отклонения, коэффициента вариации в Excel Медиана в статистике Функция Excel ВПР VLOOKUP и ГПР HLOOKUP с примерами использования Расчет средней арифметической в Excel Нормальный закон распределения - введение Мода в статистике Функции округления чисел в MS Excel Цепные и базисные индексы Несколько условий в функциях MS Excel ЕСЛИ IF и УСЛОВИЯ IFS. Материалы по теме Интервальный просмотр в функции ВПР Функции MS Excel ИНДЕКС INDEX и ПОИСКПОЗ MATCH — более гибкая альтернатива функции ВПР Функция MS Excel ПОИСКПОЗ MATCH Полезные советы по использованию функции Excle ВПР Функция Excel СУММЕСЛИ SUMMIF - примеры использования. Меню Главная Методы Описание данных Индексы Группировка Проверка гипотез Многомерный статанализ Динамика и прогнозирование Тервер Управление запасами Excel Формулы Форматирование Работа с данными Диаграммы Трюки Сводные таблицы Power Query Скачать Бесплатно Платно Курсы Блог Заметки Книги Реклама Услуги Статистическая обработка данных Разработка систем управления запасами Обработка данных в Excel Реклама на сайте.
Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую
На самом деле я в данной статье хочу рассказать про возможности не только функции ВПР , но так же хочу затронуть и ПОИСКПОЗ , как очень родственную с ВПР функцию. У каждой из данных функций есть как свои плюсы, так и минусы. Если в двух словах, то ВПР ищет некое указанное нами значение среди множества значений, расположенных в одном столбце. Пожалуй наиболее часто необходимость в ВПР возникает когда надо сравнить данные, найти данные в другой таблице, из одной таблицы добавить данные в другую, опираясь на какой-либо критерий и т. Чтобы чуть лучше понять принцип работы ВПР лучше начать с некоего практического примера. Имеется таблица такого вида: Для трех записей это не проблема и руками сделать - все очевидно. Но в жизни это таблицы на тысячи записей и поиск с подстановкой данных вручную может занять не один час. Плюс еще пара ложек дегтя: Я полагаю, что убедил вас в том, что подстановка данных руками вообще не вариант. Зато ВПР VLOOKUP здесь будет незаменима. При этом практически ничего не надо будет делать - только записать в первую ячейку столбца С второй таблицы туда, куда необходимо подставить даты из первой таблицы такую формулу: Теперь разберем поподробнее саму функцию, её аргументы и некоторые особенности. Прежде чем читать далее я бы настоятельно советовал прочитать в встроенной справке подробнее про данную функцию, а если уж будет непонятно - то дочитать статью. Сначала основной принцип работы: С интервальным просмотром разберемся чуть позже. ВПР может вернуть только одно значений - первое, подходящее под критерий. Не надо этого бояться - это даже полезно. Вы точно будете знать, каких записей нет и таким образом можете сравнивать две таблицы друг с другом. Иногда получается так, что Вы видите: Значит данные в Ваших таблицах не идентичны. В какой-то из них есть лишние неприметные пробелы обычно перед значением или после , либо знаки кириллицы перемешаны со знаками латиницы. Только диапазон должен содержать данные от первой ячейки с данными до самой последней. Это не обязательно должен быть указанный в примере диапазон. Если строк , то Лист1! Важно помнить три вещи: В противном случае ничего найдено не будет или результат будет совсем не тот, которого ожидаете. Это и есть закрепление если точнее, то это называется абсолютной ссылкой на диапазон. Выделяете текст ссылки только один диапазон - один критерий и жмете F4 до тех пор, пока не увидите, что и перед обозначением имени столбца и перед номером строки не появились доллары. Если этого не сделать, то при копировании формулы аргумент Таблица будет 'съезжать' и результат опять-таки будет неверным. И последнее - таблица должна содержать столбцы от первого в котором ищем до последнего из которого необходимо возвращать значения. Здесь просто указываем номер столбца в аргументе Таблица , значения из которого нам необходимо подставить в качестве результата. Если бы нужен был отдел, то указали бы 2, а если бы нам понадобилось просто сравнить есть ли фамилии одной таблицы в другой, то можно было бы указать и 1. Иначе результатом формулы будет ошибка ССЫЛКА! Если же попытаться указать номер столбца 3 каким по счету он является на листе , то получим ошибку ССЫЛКА! К слову в данном случае Лист1! Может быть равен либо ИСТИНА либо ЛОЖЬ. Все очень просто - Excel в формулах может воспринимать 0 как ЛОЖЬ, а 1 как ИСТИНА. Если в ВПР указать данный параметр равный 0 или ЛОЖЬ, то будет происходить поиск точного соответствия заданному Критерию. Если же использовать 1 или ИСТИНА или же вообще не указывать последний аргумент, так как по умолчанию он равен ИСТИНА , то Вкратце - ВПР будет искать наиболее похожее значение, подходящее под Критерий. Правда, если использовать данный параметр, то необходимо, чтобы список в аргументе Таблица был отсортирован по возрастанию. Если же 0 или ЛОЖЬ - сортировка не нужна. Многие наверняка заметили, что на картинке у меня попутаны отделы для ФИО. Это не ошибка записи. Мне показалось, что подобный пример вполне может пригодиться. Именно номер позиции, а не само значение. В принципе её я не буду расписывать так же подробно, потому как основные моменты ровно такие же. Если бы мы хотели применить её для таблицы выше, то она была бы такой: Здесь все ровно так же, как и с ВПР. Так же допустимы символы подстановки и ровно в таком же исполнении. Основное отличие от ВПР - допускается указать массив лишь с одним столбцом. Если попытаться указать более одного столбца, то функция вернет ошибку. С теми же особенностями. Отличается разве что возможностью поиска наименьшего от искомого или наибольшего. Но на этом я не буду останавливаться в данной статье. Но ведь нам надо вернуть не номер позиции, а само значение. Значит ПОИСКПОЗ в чистом виде нам не подходит. По крайней мере одна, сама по себе. Но если её использовать вместе с функцией ИНДЕКС - то это то, что нам нужно и даже больше. Аргументы функции ИНДЕКС Лист1! В качестве этого аргумента мы указываем диапазон, из которого хотим получить значения. Может быть как один столбец, так и несколько. В случае, если столбец один, то последний аргумент функции указывать не надо. На этом все и строится. И начинаться с одной и той же строки. Это в обычных случаях, если Вы не преследуете иные цели. И обойти подобные ошибки можно так же: Для всех версий Excel включая и раньше: Работа с критериями длиннее символов Есть у ИНДЕКС-ПОИСКПОЗ и еще одно преимущество перед ВПР. Дело в том, что ВПР не может искать значения, длина строки которых содержит более символов. Это случается редко, но случается. Можно, конечно, обмануть ВПР и урезать критерий: Да и к тому же далеко не всегда такая формула вернет нужный результат. Если первые символов идентичны первым символам в таблице, а дальше знаки различаются - формула этого уже не увидит. Да и возвращает формула исключительно текстовые значения, что в случаях, когда возвращаться должны числа, не очень удобно. Поэтому лучше использовать такую хитрую формулу: В данном случае ПОИСКПОЗ ищет позицию строки, в которой критерий равен значению в строке. Подстановочные символы здесь применить уже не получится. В прилагаемом к статье примере Вы найдете примеры использования всех описанных случаев и пример того, почему ИНДЕКС и ПОИСКПОЗ порой предпочтительнее ВПР. Андрей, файл скачать не пробовали? Там есть такая строка: Добрый день, очень помогла статья. Использовала поиспоз, вставились нужные цифры, правда целые, а нужны с 2 цифрами после запятой, помогите. После декрета забыла эту форму, если не сложно помогите Ирина, как Вы представляете помощь? Здесь не проводятся курсы по восстановлению памяти. Что Вы применяли и как чего сравнивали никто кроме Вас не знает. Обратитесь в форум с более детальным описанием задачи - тогда если уж не вспомните, то направление для решения задачи Вам дадут. Функций для сравнения в зависимости от задач море. Среди них и ВПР описанная выше , и СУММЕСЛИ, и СОВПАД, и простое равенство и ПОИСКПОЗ, и ПСТР, и СЧЁТЕСЛИ и много какие еще. У меня есть 3 одинаковых таблицы в разных книгах с одинаковыми значениями в столбцах и строках. Последний столбик в таблицах имеет разное значение тип строка , с условием что если в Книге1 ячейка J2 заполнена значением, то в Книге2 и Книге3 эта ячейка будет пуста. Как объединить значения со всех книг с столбца J в новую книгу4 в столбик J? В чем может быть причина? В другой книге подобная формула работает нормально. А возврат значений указываете в 8-ом. Дмитрий, помогите разрешить проблему пожалуйста описание проблемы: Возможно несколько сумбурно описала, но ооочень надо. Вы создали очень полезный сайт. Прошу помочь, имеется базовая таблица с ФИО и различными показателями в строке, мне необходимо по заданному неуникальному критерию показателю из одного столбца, создавать список из уникальных значений ФИО , находящихся в другом столбце. Я использовал Вашу формулу: Главная Мои программы Ошибка - 'Component COMDLG Поделитесь своим мнением Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум. Нажмите, чтобы отменить ответ. Так же с этой статьей читают:. Найти в ячейке любое слово из списка ВПР с поиском по нескольким листам. Главная Хитрости Тренинги Азы Excel. Спасибо за сообщение Ваше сообщение было получено и отправлено администратору. Основные понятия 22 Сводные таблицы и анализ данных 7 Графики и диаграммы 5 Работа с VB проектом 11 Power BI и Power Query Условное форматирование 5 Списки и диапазоны 5 Макросы VBA процедуры 61 Разное
Функция Excel ВПР (VLOOKUP) и ГПР (HLOOKUP) с примерами использования
Пермтрансавто пермь расписание автобусов
Зимний холодильниксвоими руками
Перенос данных таблицы через функцию ВПР
В скольких странах английский официальный язык