Как перемещать данные с помощью ВПР?
Давайте посмотрим на пример на практике. У нас есть таблица, в которой фиксируются партии заказанного товара (она выделена зеленым цветом). Справа находится прайс-лист, в котором указаны цены на каждый продукт (выделены синим цветом). Нам нужно переместить данные о ценах из таблицы справа налево, чтобы рассчитать стоимость каждого лота. Чтобы сделать это вручную, потребуется много времени, поэтому давайте воспользуемся функцией вертикального просмотра.
В ячейке D3 нужно поднять цену на гречку из правой таблицы. Пишем = ВПР и заполняем аргументы.
Искомое значение будет гречка из ячейки В3. Важно писать именно номер ячейки, а не слово «гречка», чтобы в дальнейшем мы могли растянуть формулу и получить остальные значения автоматически.
Таблица — выберите прайс-лист без заголовка. Это просто названия самих товаров и их цены. Мы восстановим этот массив с помощью клавиши F4, чтобы он не изменился при перетаскивании формулы.
Номер столбца — в нашем случае это номер 2, потому что нужные нам данные (цена) находятся во втором столбце выбранной таблицы (цена).
Просмотр интервалов — установите 0, потому что нам нужны точные значения, а не приближения.
Мы видим, что цена на гречку поднялась из правой таблицы в левую.
Давайте сократим формулу и визуально проверим некоторые продукты, чтобы понять, что мы все сделали правильно.
Итак, с помощью элементарных действий можно подставлять значения из одной таблицы в другую. Важно помнить, что функция вертикального отображения работает только в том случае, если таблица, из которой извлекаются данные, находится справа. В противном случае вам придется переместить его или использовать команды ИНДЕКС и ПОИСК. Освоив эти две функции, вы можете реализовать гораздо более сложные решения, чем возможности, предлагаемые функцией VLOOKUP или HORIZ LOOK.
- 22 апреля 2011 г –
- Учебники по Excel –
- Теги: таблицы Excel, уроки Excel, данные Excel
- 75 комментариев
Довольно частая ситуация, когда данные из одной таблицы в Excel нужно заменить в другой.
Возьмем пример: допустим, есть 2 таблицы — продажи и прайс-лист. Задача состоит в том, чтобы заменить цены из таблицы прайс-листа в таблицу продаж, чтобы вы могли в конечном итоге вычислить общую сумму продаж.
Предлагаю 2 варианта выполнения этой задачи.
Вариант 1. Воспользуйтесь функцией ВПР, чтобы скачать пример
Функция ВПР ищет определенное значение в крайнем левом столбце указанной таблицы, перемещаясь сверху вниз. В нашем примере функция ВПР будет искать «корм для кошек» в таблице «прайс-лист» и, когда найдет его, заменит цену корма для кошек в таблице «продажи». Точно так, как показано на фото выше. Сразу хочу отметить 2 важных условия для правильного функционирования формулы: 1 столбец «название товара» в обеих таблицах должен иметь одинаковый формат, 2 столбца «название товара» в таблице прайс-листа должны быть отсортированы по возрастанию.
Вот и все, вызовем функцию ВПР. Щелкаем в ячейке, в которую будет введена цена (в нашем примере — C5), затем щелкаем значок fx на панели инструментов (или на функцию «Вставить») и в открывшемся окне выбираем ссылки и массивы, а затем ВПР. Как показано на изображении.
и нажмите ОК. Откроется следующее окно, в котором задаются параметры замены:
нужное значение — нажимаем на ячейку, где стоит нужное значение — у нас есть этот корм для кошек
table — это таблица, из которой берутся данные. Щелкаем по маленькому квадрату с красной стрелкой и мышкой ищем нашу таблицу списка, нажимаем Enter
номер столбца — здесь нужно указать именно порядковый номер столбца таблицы, из которого будут взяты цены. В нашем примере первый столбец — это имя, столбец 2 — цена. Итак, поставим цифру 2
интервальное сканирование: здесь вы можете ввести ЛОЖЬ или ИСТИНА. Других вариантов нет. Вы можете писать словами или ввести число 0 или 1. 0-ЛОЖЬ, 1-ИСТИНА. Если мы вводим FALSE, выполняется поиск точного совпадения с данным параметром, если вы вводите TRUE, это дает разрешение на поиск приблизительного совпадения, то есть поиск ближайшего совпадения с заданным параметром. Чтобы делать меньше ошибок, лучше всегда указывать FALSE, то есть искать точное совпадение.
Все, жмем ОК и радуемся:)
Для еще большего удобства работы с такой формулой вы можете назвать таблицу прайс-листа, а затем указать в формуле не диапазон поиска, а только имя таблицы. Для этого выбираем таблицу прайс-листа и кликаем правой кнопкой, там находим «название диапазона». В открывшемся новом окне напишите название диапазона, например «цена». А затем в формуле ВПР вы можете просто ввести имя диапазона
И второй способ решить эту проблему — заменить данные в Excel через функцию СУММЕСЛИ.
