Как перенести данные из одной ячейки в другую в excel по условию
Перейти к содержимому

Как перенести данные из одной ячейки в другую в excel по условию

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

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

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

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

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

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

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

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

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

Заказы.

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

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

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

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

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

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

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

Пример.

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

Функция ВПР в Экселе – пошаговая инструкция

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

Так было со мной. На моей первой официальной работе, мне приходилось раз в несколько дней сравнивать два списка. Списки были относительно небольшие, около 100 строк каждый. Но сравнивал я их при помощи ручки и линейки. Это было мучение, более того это было неэффективное мучение, которое каждый раз, занимало у меня (по меньшей мере) пол рабочего дня.

Продолжался этот мазохизм ровно до тех пор, пока однажды коммерческий директор не увидел, как я безжалостно орудую линейкой над двумя листиками.

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

Сказать, что я был удивлён – это значит ничего не сказать. Я лицезрел настоящее чудо.

Это была потрясающая демонстрации силы автоматизации.

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

Функция ВПР в Экселе – быстрый перенос данных

Самое простое применение функция ВПР это быстрый перенос данных из одной таблицы в другую.

Например, у вас есть большой прайс на 500 позиций и запрос от покупателя, скажем на 50 позиций (в реальности и прайс и запрос могут быть гораздо больше, но принцип от этого не меняется).

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

Итак, у нас в прайсе 500 позиций. Позиции обозначаются следующим образом, буквами обозначается вид позиции, а цифрами модификация.

Например, «Стул_1» и «Стул_21» это два совершенно разных стула.

Цены в прайсе указаны для примера и вряд ли имеют отношение к реальным ценам.

В ООО «ЫкэА» пришел запрос от «Петровича».

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

Однако это нас не страшит, во-первых, у нас есть ВПР, во-вторых мы и не такое видали.

Вот собственно и сам запрос:

Функция ВПР в Экселе-1

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

Нам не хочется терять такого клиента и мы практически мгновенно открываем прайс:

Функция ВПР в Экселе-2

Получается у нас должно быть открыто два файла (две книги в Эксель). Запрос от Петровича и Прайс.

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

Для этого перейдем в таблицу запроса и в первой ячейке столбца «Цены» (D4) введем «=впр» и два раза кликнем на значок функции:

Функция ВПР в Экселе-3

Сразу же после этого, в строке формулы нужно поставить курсор внутри надписи ВПР и нажать Fx, перед вами появится окно с аргументами функции ВПР:

Функция ВПР в Экселе-4

В аргументах функции вы говорите Экселю что и где нужно искать:

Искомое значение — это значение (в данном случае наименование), цену которого вы хотите найти в прайсе. Соответственно кликайте на первую ячейку столбца «Наименование».

Далее, сразу переходите в «Прайс»:

Функция ВПР в Экселе-5

Теперь в аргументах функции заполните следующие поля:

Таблица — выделяете столбцы, которые содержат искомые наименования и цены, таким образом, чтобы наименования были крайним левым столбцом.

Так работает функция ВПР — ищет искомые значения в крайнем левом столбце (для ВПР это столбец №1). Когда ВПР находит искомое значение он начинает смотреть правее, в тот столбец, который вы указали в «Номере столбца».

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

Интервальный просмотр — ставьте 0. Ноль обозначает точное соответствие.

После заполнения аргументов функции нажимайте «Ок» и если всё сделано верно, то в столбце «Цена» (файл «Запрос от Петровича»), появится цена.

Вам нужно протянуть цены на оставшиеся ячейки:

Функция ВПР в Экселе-6

Коллеги, вот и всё, вы овладели функцией ВПР.

Очень важное замечание!

Обратите внимание на то, что сейчас мы работали в двух разных файлах (книгах).

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

Делает это он при помощи значка $, который проставляет перед столбцами и строками таблицы.

Это позволяет не съезжать формуле когда вы протягиваете её вниз. Это очень актуально когда вы работаете в рамках одного листа или одной книги (в этом случае Эксель автоматически Не закрепляет ячейки).

Давайте посмотрим что получиться если протянуть формулу «без закрепления»:

Функция ВПР в Экселе-8

Обратите внимание, что для первой ячейки всё в порядке и диапазон B3:C502 точно соответствует той таблице, которую мы выделили для поиска данных, однако (без закрепления) так будет не всегда, по мере «протягивания» формулы ВПР вниз, будет смещаться и таблица, пока однажды, мы не увидим вот такую странную надпись #Н/Д:

Функция ВПР в Экселе-9

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

Функция ВПР в Экселе-10

Посему, если не хотите чтобы от вас уезжали, закрепляйте диапазон.

Очень важное замечание №2

Как вы заметили, формулы ссылаются на определённые ячейки, другими словами между формулами и исходными данными есть связь. Стоит изменить исходные данные и значения в формулах сразу изменятся.

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

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

Для этого нужно выделить столбец с формулами, нажать Ctrl+C и в левом верхнем углу выбрать «Вставить» — «Вставить значения».

Функция ВПР в Экселе-18

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

Видео — «Быстрый перенос данных с помощью функции ВПР в Экселе»

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

Это очень актуально для тех кто работает в закупках и отправляет заказы поставщику.

Обычно происходит следующая ситуация. Вы отправляете заказ поставщику, через некоторое время получаете ответ в виде счёта и сверяете заказ с счётом.

Всё ли есть в счёте, в нужном ли количестве, по правильным ли ценам и т.д.

Функция ВПР в Экселе – сравнение двух таблиц

Итак, у вас есть «Заказ поставщику» (1) и ответ поставщика в виде «Счёта на оплату» (2).

Для удобства восприятия я разместил их на одном листе:

Функция ВПР в Экселе-11

Ваша задача сверить количество позиций и их цены.

Для начала проверим все ли позиции и по правильной ли цене указал в счёте поставщик.

Для этого нужно из Счёта перетянуть данные в Заказ при помощи функции ВПР.

Перед «перетяжкой», в таблицу «Заказ поставщику» нужно добавить два «сравнительных» столбца:

«Кол/во в счёте» и «Цена в счёте»

После добавления столбцов, нужно перетянуть соответствующие данные при помощи ВПР:

Обратите внимание, я закрепил диапазоны ячеек.

Теперь когда данные перенесены, нужно их сравнить, для это необходимо добавить еще два столбца (Разница 1 и Разница 2):

В столбце «Разница 1» нужно вычесть от исходного количества (D4) количество в счёте (E4).

В столбце «Разница 2» нужно вычесть от исходной цены (G4) цену в счёте (H4).

Таким образом мы сможем увидеть разницу и в количестве и в цене.

Если значение «0», то значит всё хорошо и данные одинаковые.

Если значение плюсовое (например «+3»), то это значит что в счёте не хватает 3 штук.

Если значение отрицательное, это значит, что нам пытаются «впихнуть» лишнее.

Если значение #Н/Д — это значит, что в счёте вообще нет такой позиции.

Тоже самое и с ценами, если значения в плюсе, значит нам сделали скидку, а если значения в минусе — значит нам пытаются продать по завышенной цене.

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

Функция ВПР в Экселе-16

Друзья, вот так мы проверили насколько соответствует Заказ, полученному Счёту и казалось бы что это всё что необходимо для счастливой жизни. Однако это не совсем так.

Нужно еще проверить соответствие Счёта, отправленному заказу, на предмет лишних позиций.

Вдруг хитрый поставщик, среди сотни позиций решил нам скрытно что-то «допродать».

Для этого в «Счёт на оплату» нужно добавить столбец «Кол/во в заказе» и «отвепээрить» туда значения из столбца «Количество» Заказа поставщику.

И если в столбце «Количество в заказе» мы вдруг увидим значения #Н/Д это значит, что позиции с таким наименование не было в нашем заказе поставщику.

Функция ВПР в Экселе-18

Это не всегда означает, что поставщик хочет нас «нагреть», чаще всего просто может быть ошибка в названии товара. Стоит например поставить лишний пробел, как Эксель не задумываясь выдаст #Н/Д. Для него это разные названия.

Теперь всё тоже самое продемонстрирую в небольшом видео.

Видео — «Сравнение двух таблиц с помощью функции ВПР в Экселе»

Эпилог

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *