Как Сделать Чтобы Дата в Excel не Менялась Автоматически При Протягивании • Использование кода

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как Сделать Чтобы Дата в Excel не Менялась Автоматически При Протягивании • Использование кода

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

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

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

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

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

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

Как Сделать Чтобы Дата в Excel не Менялась Автоматически При Протягивании • Использование кода

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

Как Сделать Чтобы Дата в Excel не Менялась Автоматически При Протягивании • Использование кода

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

Рассмотрим на примере: допустим есть 2 таблицы — продажи и прайс-лист. Задача-подставить цены из таблицы прайс-лист в таблицу продажи, чтобы можно было в итоге посчитать общую сумму продаж.

Как Сделать Чтобы Дата в Excel не Менялась Автоматически При Протягивании • Использование кода

Вариант 1. Использовать функцию ВПР. скачать пример

Все, вызываем функцию ВПР. Щелкаем в той ячейке, куда будет подставляться цена( С5 нашего примера), далее жмем значок fx на панели инструментов (либо Вставка-функция) и в открывшемся окошке выбираем ссылки и массивы и далее ВПР. Как показано на картинке.

Как Сделать Чтобы Дата в Excel не Менялась Автоматически При Протягивании • Использование кода

и жмем ОК. Откроется следующее окно, в котором и задаются параметры подстановки:

Как Сделать Чтобы Дата в Excel не Менялась Автоматически При Протягивании • Использование кода

искомое значение — щелкаем по той ячейке, в которой находится искомое значение — у нас это корм для кошек

таблица — это таблица, из которой берутся данные. Щелкаем на квадратик с красной стрелкой и мышкой обводим нашу таблицу прайс-лист, жмем Enter

номер столбца — здесь нужно указать именно порядковый номер столбца таблицы из которой будут браться цены. В нашем примере столбец номер один-наименование, столбец номер 2-цена. Таким образом, мы ставим цифру 2

Для еще большего удобства работы с такой формулой, можно таблице прайс-лист присвоить имя, и потом в формуле указывать не диапазон поиска, и просто имя таблицы. Для этого выделяем таблицу прайс-лист и жмем правой кнопкой мыши, там находим «имя диапазона»

Как Сделать Чтобы Дата в Excel не Менялась Автоматически При Протягивании • Использование кода

В новом открывшемся окне пишем имя диапазона, например «прайс»

Как Сделать Чтобы Дата в Excel не Менялась Автоматически При Протягивании • Использование кода

И тогда в формуле ВПР можно просто впечатать имя диапазона

Как Сделать Чтобы Дата в Excel не Менялась Автоматически При Протягивании • Использование кода

И второй способ решения данной задачи — подстановка данных в excel через функцию СУММЕСЛИ

эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
У меня за плечами многолетний опыт работы в области компьютерного программирования, устранения неисправностей и ремонта оборудования. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Примечание : должен сказать, что функция ВПР достаточно требовательна к ресурсам компьютера. В некоторых случаях, при чрезмерно большом документе, чтобы сравнить таблицы может понадобиться довольно длительное время. В этих случаях, стоит рассмотреть либо другие формулы, либо совсем иные решения (каждый случай индивидуален).
Как Сделать Чтобы Дата в Excel не Менялась Автоматически При Протягивании • Использование кода

Как остановить Excel от изменения чисел до дат — Учебные пособия по Windows

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

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Adblock
detector