Add Me!Close Menu Navigation
Полный список уроков excel

Подстановка данных в excel функция ВПР

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

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

vpr-12

Предлагаю 2 варианта выполнения этой задачи.

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

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

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

vpr-2

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

vpr-3

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

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

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

интервальный просмотр - здесь можно ввести либо ЛОЖЬ либо ИСТИНА. Других вариантов нет. Можно либо словами написать, либо ввести цифру 0 или 1. 0-ЛОЖЬ, 1-ИСТИНА. Если вводим ЛОЖЬ — выполняется поиск точного соответствия заданному параметру, если вы введете  ИСТИНА, то таким образом Вы даете разрешение на поиск приблизительно соответствия, то есть поиск максимально похожего заданному параметру. Чтобы было меньше ошибок, лучше всегда указывать ЛОЖЬ, т.е. поиск точного соответствия.

Все, нажимаем ОК и радуемся:)

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

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

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

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

ПРОДВИНУТЫЙ КУРС по работе в excel
В этом курсе Вы узнаете:

  • Как защищать данные в таблицах.
  • Как работать с картинками и прочими графическими объектами.
  • Как вставлять результаты вычислений в графические объекты.
  • Как создавать и настраивать диаграммы.
  • Как сортировать и фильтровать данные.
  • Как добавлять промежуточные итоги.
  • Как профессионально использовать функцию ЕСЛИ.
  • Как форматировать ячейки в зависимости от данных в них.
  • Что такое мгновенное заполнение и как им пользоваться.

Popularity: 40% [?]

поведал admin

75 - Комментарии к записи “Подстановка данных в excel функция ВПР”

  1. admin:

    Натали: здравствуйте! с одного листа на другой точно также как в примере можно подставлять данные.

  2. Натали:

    Добрый день!
    Подскажите как можно подтянуть текст с одного листа на другой?

  3. Юрий:

    Уважаемая admin, которая оказывается девушка :-)
    Я к Вам в очередной раз за помощью.

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

    Например:
    Корм для кошек — 01
    Корм для собак — 01
    Корм для рыбок — 01
    Корм для рыбок — 02
    Корм для кошек — 02
    Корм для рыбок — 03
    Корм для рыбок — 04
    Корм для кошек — 02

    Так чтоб для каждого значения нумерация начиналась с 1 и продолжалась по порядку в случае если опять вводится данное значение (корм).

    Очень надеюсь на Вашу помощь, если это вообще реально реализовать в excel.

  4. admin:

    Юлия: не совсем поняла вопрос. прислайте пример на мыло vizushka@mail.ru я посмотрю, скажу..

  5. Юлия:

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

  6. raushan:

    Спасибо огромное все умеют на работе теперь и я научилась! а то натура гордая спрашивать не любит )))

  7. admin:

    Наталья: можно использовать функция ЕСЛИОШИБКА. например ЕСЛИОШИБКА(ВПР(тут ваши условия поиска по ВПР);0) в таком случае вместо #Н/Д будет ставиться 0.

  8. Наталья:

    Добрый день! Подскажите, пожалуйста! Я сделала все как написано, но у меня не все значения есть в таблице. Можно ли чтобы у тех данных которых нет в таблице, не проставлялась ошибка #Н/Д. Или как нибудь отфильтровать или проверку данных сделать в этих ячейках. Мне нужно общую сумму посчитать, а так как в некоторых ячейках ошибка, то не получается.

  9. admin:

    Ols: так надо проделать все тоже самое, что описано в уроке и будет где нет соответствующих lанных стоять #Н/Д

  10. Ols:

    Уважаемый admin, подскажите пожалуйста, в случае если имеются те же две таблицы, только в таблице «прайс-лист» (таблица 2) наименование товаров меньшее, чем в первой таблице и необходимо проставить все цены из таблицы 2 в таблицу 1, а там где нет соответствующих данных стояло бы #Н/Д.
    Заранее благодарю!

  11. admin:

    Юрий: Пожалуйста, обращайтесь если что:) ага, оказывается девушка:)

  12. Юрий:

    Как говорил Кот-Мотроскин — «Уррааа… Заработаалоо!!»
    Круто, круто… спасибо Вам огромное !
    А админ то девушка оказывается)

  13. admin:

    Юрий: я даже упростила формулу эту еще-диапазон из которого выбираются названия корма я назвала «корма», а диапазон, откуда должны складываться кол-ва продаж я назвала «продано», и тогда формула выглядит вот так {=СУММ((корма=B25)*(продано))} вобщем вот пример

  14. admin:

    Юрий: да, пожалуйста, вот ваш файлик

  15. Юрий:

    Уважаемый admin, а можно Вас попросить сделать ещё один пример.
    Возможно я не совсем правильно пояснил что мне нужно в итоге. Вот посмотрите в данной таблице и пример если можно в ней:
    http://rghost.ru/57200756

  16. Юрий:

    Спасибо за ответ. Пока ничего не понял ))
    Посмотрю пример уже завтра с утра, попробую разобраться, отпишусь.

  17. admin:

    Юрий:Да это можно.Для этого надо воспользоваться формулой массива. В таблице внизу, где перчислены все корма пишеет формулу примерно такого вида =СУММ((A3:A8=A11)*(C3:E8)), где А3:А8-это список кормов, который вы постоянно заполняете(еженедельно), А11-это корм, который вы хотите просуммировать, а С3:С8-это диапазон (те самые 50 недель). И главное-это формула массива, поэтому вводится не простым нажатием энтера, а комбинация Ctrl+Shift+Enter. Если непонятно-смотрим пример , если непонятно-пишите еще:)

  18. Юрий:

    Класс, спасибо большое Автору. Давно искал это.
    Только ещё проблема одна есть, попробую описать на данном примере. Подскажите как её решить:

    Предположим, что список этих кормов у нас растянут в низ на 50 раз. (50 недель) т.е этих таблиц «Продажи» у нас в низ 50 штук. (надеюсь понятно пояснил)
    Всё отлично — по данной формуле подставляет нужное значение к каждой переменной!
    Заполняю графу «продано» в ручную каждую неделю, таких то кормов 10 шт., таких то 3 шт, таких то 6 шт.
    Каждую неделю количество соответственно разное.

    ВОПРОС Автору:
    В итоге таблице в Самом низу, мне нужно добавить ещё таблицу с 2-я Графами. Графа1 — где будут перечислены все виды кормов. Графа 2-а — Где нужна формула для подсчёта — общего количества по каждому отдельному виду кормов проданное за 50 недель. — !?
    Надеюсь мой вопрос понятен. Реально ли реализовать это формулой?

  19. Ольга:

    admin, спасибо)

  20. Оксана:

    Огромное спасибо, давно искала эту функцию, то что надо

  21. Сергей:

    а есть варианты к кому можно обратиться с таким вопросом?

  22. admin:

    Сергей: боюсь, что обычными функциями этого не реализовать, возможно макрос можно написать, но это не ко мне:(…

  23. Сергей:

    Вопрос: имею две ячейки с числами(к примеру черного и красного цвета) , при появлении красного числа нужно что бы черное число стало перечеркнутым » крест на крест» , а при отсутствии красного числа, черное оставалось не перечеркнутым
    Как это воплотить в реальность?

  24. admin:

    Ольга: можно сделать сумму ВПРов, т.е. =ВПР(A3;имя1;2;0)+ВПР(A3;имя2;2;0)+ВПР(A3;имя3;2;0) и так далее, где А3-это сухие смеси, а имя1,имя2, имя3 — это имена диапазонов поиска по каждому листу с данными, цифра 2-это номер столбца, написала просто для примера, 0-интервальный просмотр

  25. Ольга:

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

  26. Olegman:

    Спасибо. Нашел здесь именно то, что было нужно)

  27. admin:

    Ольга: так и делать-при выборе диапазона, откуда будут подставляться данные жмете на другой лист и там выделяете диапазон.

  28. Ольга:

    А как делать привязку, если прайс и таблица продаж находятся на разных листах?

  29. Андрей:

    Большое спасибо «admin»! Просто и понятно (о ВПР), не то, что в справке EXCEL, примеры корявые.

  30. Сергей:

    сам не разобрался, если не трудно отправил на почту пример. если не трудно. спасибо

  31. admin:

    Сергей: =ИНДЕКС(март!A1:B4;ПОИСКПОЗ(B3;март!B1:B4;0);1)
    =ИНДЕКС(диапазон, из которого нужно вернуть значение; ПОИСКПОЗ(искомое значение; диапазон, содержащий искомое значение; 0);номер столбца, в котором находится значение)

  32. Сергей:

    А пример формулы можно?

  33. admin:

    Сергей: я бы использовала индекс и поискпоз. вместе

  34. Сергей:

    помогите с формулой
    есть несколько листов в книге эксель месяца — январь, февраль, март и тп
    столбец А это дата рядом В это сумма (1янв. 5млн
    2янв. 7млн.)
    на листе декабрь забита формула которая ищет максимальную сумму из всех листов со столбцов В.
    так вот — реально сделать такую формулу что бы рядом с максимальной суммой этой ячейке присваивалось значение рядом стоящей даты из ячейки А на соответствующем листе?

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

  35. admin:

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

  36. Здравствуйте!
    Подскажите пожалуйста, «номер столбца» почему 2 получается? У меня никак, это первый пример, который я пытаюсь разобрать, поэтому поймите меня правильно…)
    Когда я выделяю столбец, у меня получается диапазон Н3:Н13, как у вас получается 2?..
    Спасибо за ответ!

  37. admin:

    ilyas: в отдельные столбцы посредством впр вставить значения цены за единицу и пол-во продаж?

  38. ilyas:

    А как расчитать Общую сумму продаж Определенного Товара, Если цена за единицу и количество продаж находятся в другом листе