Oc-windows.ru

IT Новости из мира ПК
3 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel подстановка значений из другой таблицы

Excel: как сравнить 2 таблицы и подставить данные из одной в другую автоматически

У меня есть одна задачка, и уже третий день ломаю голову — не знаю, как ее выполнить. Есть 2 таблицы (примерно 500-600 строк в каждой), нужно взять столбец с названием товара из одной таблицы и сравнить его с названием товара из другой, и, если товары совпадут — скопировать и подставить значение из таблицы 2 в таблицу 1. Запутанно объяснил, но думаю, по фотке задачу поймете ( прим. : фотка вырезана цензурой, все-таки личная информация) .

Заранее благодарю. Андрей, Москва.

Доброго дня всем!

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

Пример работы с функцией ВПР

В качестве примера я взял две небольших таблички, представлены они на скриншоте ниже. В первой таблице (столбцы A, B — товар и цена) нет данных по столбцу B; во второй — заполнены оба столбца (товар и цена). Теперь нужно проверить первые столбцы в обоих таблицах и автоматически, при найденном совпадении, скопировать цену в первую табличку. Вроде, задачка простая.

Две таблицы в Excel — сравниваем первые столбцы

Как это сделать.

Ставим указатель мышки в ячейку B2 — то бишь в первую ячейки столбца, где у нас нет значения и пишем формулу:

A2 — значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);

$E$1:$F$7 — полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок «$» — он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;

2 — номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца — то значение можно было бы копировать из 2-го или 3-го столбца);

ЛОЖЬ — ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).

Какая должна быть формула

Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!

Значение было найдено и подставлено автоматически

Чтобы цена была проставлена и для других наименований товара — просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.

Растягиваем формулу (копируем формулу в другие ячейки)

После чего, как видите, первые столбцы у таблиц будут сравнены: из строк, где значения ячеек совпали — будут скопированы и подставлены нужные данные. В общем-то, понятно, что таблицы могут быть гораздо больше!

Значения из одной таблицы подставлены в другую

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

Ну а у меня на этом пока всё, удачи!

Перенос данных таблицы через функцию ВПР

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

Читать еще:  Таблица ms excel

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

Синтаксис ВПР

ВПР расшифровывается как вертикальный просмотр. То есть команда переносит данные из одного столбца в другой. Для работы со строками существует горизонтальный просмотр – ГПР.

Аргументы функции следующие:

  • искомое значение – то, что предполагается искать в таблице;
  • таблица – массив данных, из которых подтягиваются данные. Примечательно, таблица с данными должна находиться правее исходной таблицы, иначе функций ВПР не будет работать;
  • номер столбца таблицы, из которой будут подтягиваться значения;
  • интервальный просмотр – логический аргумент, определяющий точность или приблизительность значения (0 или 1).



Как перемещать данные с помощью ВПР?

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

В ячейку D3 нужно подтянуть цену гречки из правой таблицы. Пишем =ВПР и заполняем аргументы.

Искомым значением будет гречка из ячейки B3. Важно проставить именно номер ячейки, а не слово «гречка», чтобы потом можно было протянуть формулу вниз и автоматически получить остальные значения.

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

Номер столбца – в нашем случае это цифра 2, потому что необходимые нам данные (цена) стоят во втором столбце выделенной таблицы (прайса).

Интервальный просмотр – ставим 0, т.к. нам нужны точные значения, а не приблизительные.

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

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

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

Поиск и подстановка по нескольким условиям

Постановка задачи

Если вы продвинутый пользователь Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать). Для тех, кто понимает, рекламировать ее не нужно 🙂 — без нее не обходится ни один сложный расчет в Excel. Есть, однако, одна проблема: эта функция умеет искать данные только по совпадению одного параметра. А если у нас их несколько?

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

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

Способ 1. Дополнительный столбец с ключом поиска

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

Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:

Читать еще:  Excel двойной клик по ячейке

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

Плюсы : Простой способ, знакомая функция, работает с любыми данными.

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

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS) , появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

Плюсы : Не нужен дополнительный столбец, решение легко масштабируется на большее количество условий (до 127), быстро считает.

Минусы : Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

Способ 3. Формула массива

О том, как спользовать связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) в качестве более мощной альтернативы ВПР я уже подробно описывал (с видео). В нашем же случае, можно применить их для поиска по нескольким столбцам в виде формулы массива. Для этого:

  1. Выделите пустую зеленую ячейку, где должен быть результат.
  2. Введите в строке формул в нее следующую формулу:

  • Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.
  • Как это на самом деле работает:

    Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

    Плюсы : Не нужен отдельный столбец, работает и с числами и с текстом.

    Минусы : Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

    Использование ВПР в Экселе для подстановки значения

    Добрый день уважаемый читатель!

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

    Для большей информативности рассмотрим данный вопрос на примере. Итак, у нас есть 2 независимых таблицы: прайс с ценами и таблица объемов товара. В условиях работы необходимо перенести и подставить с прайса в таблицу заказов, на основе названия товара для дальнейшего перерасчёта стоимости.

    Поскольку данные в таблицах размещены вертикально, нам нужно использовать функцию ВПР, для горизонтальных данных существует функция ГПР, но она менее популярна. Основная суть работы функции, это поиск в прайсе по названию товара и подстановка его цены в заказ. Получиться таблица такого вида: Для простоты использования данных в формуле, возможно, использовать присвоенное диапазону значений имя, но это уже на ваше усмотрение. Для назначения имени диапазона нужно выделить диапазон «G2:H8», исключив «шапку» таблицы, а потом, нажав горячую комбинацию клавиш CTRL+F3, в появившемся диалоговом окне «Диспетчер имён» создайте вашему диапазону новое имя, например «Прайс». Теперь приступим к использованию функции ВПР в Экселе для подстановки значения. Устанавливаем курсор на ячейку «C2» и с помощью мастера функций, в категории «Ссылки и массивы» выбираем нужную функцию. Появится диалоговое окно «Аргументы функции»: Теперь введем необходимые аргументы:

    • Искомое значение – указываем или наименование необходимого товара, или ссылку на ячейку, где содержится искомый аргумент;
    • – указываете таблицу, с которой будут изыматься необходимые данные, в нашем случае это таблица с прайсом, возможно вместо диапазона указать его название «Прайс»;
    • Номер столбика – указываем, каким порядковым номером будет столбик, из которого необходимого достать данные с указанием цены товара. Номер столбика указывается только цифрами, а поскольку цены хранятся во втором столбике, так и указываем;
    • Интервальный просмотр – этот аргумент может иметь только два параметра: ИСТИНА или ЛОЖЬ. Первый режим при значении ЛОЖЬ производит поиск исключительно точного соответствия значений, а в случае когда функция не найдёт нужного значения, то вернётся ошибка #Н/Д. При втором режиме, когда значение ИСТИНА, формула ищет приблизительное соответствие необходимого значения.
    Читать еще:  Как скрыть текст в ячейке excel

    Теперь осталось только одно, нажать «ОК» и получить результат, а после, скопировать полученную формулу на весь диапазон таблицы.

    Избавление от полученной ошибки #Н/Д

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

    1. Возникает ошибка при указании аргумента «Интервальный просмотр» как ИСТИНА или 0, что требует наличия точного вхождения значения, а его то, как раз и нет. Для устранения этой проблемы, измените условия отбора;
    2. Если указан аргумент «Интервальный просмотр» как ЛОЖЬ или 1, но таблица, в которой производится поиск, не отсортирована по возрастанию наименований, то ошибка будет неизбежна. Лекарство, как и в первом варианте;
    3. В случаях, когда в наличии разные форматы ячеек, тех, откуда берется необходимое значение и тех где прописан аргумент поиска, например, текстовый и числовой форматы. Частенько эта ошибка возникает, когда нужно использовать числовые коды вместо текстовых значений, это номера счетов, номенклатурные номера и прочее. Для решения этой проблемы можно преобразовывать форматы данных с помощью функций ТЕКСТ и Ч. Результатом будет такая формула: =ВПР(ТЕКСТ(B2;);$G$2:$H$8;2;ЛОЖЬ);
    4. Также в случае наличия невидимых непечатаемых знаков или лишних пробелов могут возникнуть ошибки результатов. Для исправления, в этом случае, нужно задействовать функции ПЕЧСИМВ и СЖПРОБЕЛЫ, чтобы убрать излишек ненужной пунктуации. Формула приобретёт следующий вид: =ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B2));$G$2:$H$8;2;ЛОЖЬ).

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

    =ЕСЛИОШИБКА(ВПР(B7;$G$2:$H$8;2;ЛОЖЬ);»»).

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

    Не забудьте подкинуть автору на кофе…

    Ссылка на основную публикацию
    Adblock
    detector