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: 44% [?]

поведал 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:

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

  39. admin:

    Олег:не совсем поняла вопрос, если еще нужна помощь, прислайте подробнее на мыло vizushka@mail.ru

  40. Олег:

    Добрый день,

    вопросик, а как связать получается по клиентам
    июль август
    ООО «БрокСервис» 345 562
    ООО «БрокСервис» 245 125
    ООО «БрокСервис» 678 225
    ООО «БрокСервис» 213 457

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

    Спасибо за помощь.

    Олег

  41. admin:

    Andrey: скопировать данные и поставить в крайний левый.

  42. admin:

    Валерия: можно, а какой именно файлик Вам прислать:)?

  43. Валерия:

    Добрый день! а можно попросить прислать файлик на мыло? хочу попрактиковаться

  44. Andrey:

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

  45. admin:

    messi: можно посмотреть таблицу?присылайте на мыло vizushka@mail.ru

  46. messi:

    Делаю искомое значение по дате, исправила в ТЕКСТ(A2;»ДД.ММ.ГГГГ»), но из таблицы данные столбца даты читаются как числовой, к примеру 41139, как изменить данные в таблице откуда делаешь запрос? спасибо заранее)

  47. Анна:

    Спасибо за материал!!!

  48. Алексей:

    Спасибо огромное! очень помогло с подстановкой значений в неотсортированном списке

  49. admin:

    Роман: отправила ответ на мыло

  50. Роман:

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

  51. admin:

    я вам с него уже написала vizushka@mail.ru

  52. Евгений:

    Будьте добры ваш ящик, я скину файлик с более наглядным описанием

  53. admin:

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

  54. Евгений:

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

  55. admin:

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

  56. Евгений:

    Здравствуйте, а как быть если записи могут дублироватся?

  57. admin:

    Les:либо еще короче-без квадратных скобок и указания листов-просто пишете имя файла, откуда будут браться данные и через восклицательный знак имя диапазона. Вот так: пример1.xlsx!резервы

  58. admin:

    Les: да, в таком случае надо в графе «Таблица» указывать имя файла, лист и имя диапазона на этом листе. Например:если файл, откуда надо брать данные называется пример1, диапазон данных находится на первом листе,а имя диапазона данных, скажем «резервы», то в графе «Таблица» пишем вот так: [пример1.xlsx]Лист1!резервы

  59. Les:

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

  60. admin:

    Les:зайдите в ваш документ под названием РЕЗЕРВЫ, выделите там диапазон, откуда будут браться данные(если это таблица, то ее всю выделяете, правой кнопкой мыши жмете, выбираете из списка «Имя диапазона», пишете любое имя, например «моирезервы», жмете ОК. Далее открываете тот файл, в котором вы хотите использовать функци. ВПР, и когда задаете условия для функции ВПР, в графе «Таблица» вы просто указываете прямо буквами имя диапазона, т.е. моирезервы, и в следующем поле: «Номер столбца» указываете номер столбца из диапазона под именем мои резервы, из которого должно браться знаечение.
    Если что-то непонятно-присылайте мне на почту оба ваших файла-я покажу наглядно. vizushka@mail.ru

  61. Les:

    т.е например вместо» C:\Documents and Settings\les\Мои документы\РЕЗЕРВЫ » написать просто например «1кв».а как прописать что это имя взято из другой книги?

  62. admin:

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

  63. Les:

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

  64. admin:

    Les:нет, надо указать либо диапазон, либо, как раз чтобы ускорить процесс — имя диапазона.

  65. Les:

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

  66. admin:

    rumata: Как правило каждый из кормов для кошек имеет свое название, либо номер артикула, которые отличаются друг от друга!!!

  67. rumata:

    а как быть если в прайс листе кроме «корм для кошек» 200р есть «корм для кошек» 250р. и еще «корм для кошек» 300р.

  68. Anna:

    Mikhail, попробуйте поставить «звездочки» до и после кода: *7N7748*

  69. admin:

    Mikhail:отправила вопрос на почту

  70. Mikhail:

    интересует как сделать чтобы при поиске «корм для кошек» результат цены, например мог быть поставлен из «корм для кошек/котят»? на моём примере: ищет артикул «7N7748″ запчасти в прайсе, а в прайсе записано как «7N7748/4N8969″,который стоит Х-долларов, то есть с кодом аналогичной запчасти, в результате не находится искомый результат, показывает н/д. Конечно можно воспользоваться поиском, но если таких позиций много.

  71. admin:

    Сергей-можно скопировать столбец B и вставить в соседнем столбце через специальную вставку: правой кнопкой мыши по столбцу щелкаете, выбираете специальная вставка и отмечаете — «значения» и жмете Ок. в том столбце будут стоять просто числа, никак не привязанные к формуле. Не будут изменяться вообще

  72. Сергей:

    Интересует тот же вопрос, что и алекса, как это осуществить?
    Спасибо

  73. admin:

    алекс: отправила ответ на почту

  74. алекс:

    многоуважаемый админ у меня такая проблема хотелось бы чтобы после выполнения функции значение как бы впечаталось(А+Б=В)ЧТО БЫ ПРИ ИЗМЕНЕНИИ «А» ИЛИ «Б» «В» ОСТАВАЛАСЬ НЕИЗМЕННОЙ

  75. Marina:

    спасибо за урок! с этим вопросом столкнулась на собеседовании и была крайне удивлена что мне это не знакомо. теперь все ОК))

Оставьте комментарий




Рубрики