Bazaprogram.ru

Новости из мира ПК
6 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Впр макросом vba excel

Функция ВПР в Excel

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

Формула ВПР

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

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

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

Функция ВПР имеет четыре аргумента:

  1. Искомое значение, в рассматриваемом примере, это табельный номер, ячейка с табельным номером выделена рамкой синего цвета;
  2. Таблица, в приведенном примере это таблица с табельными номерами и именами, выделена рамкой зеленого цвета;
  3. Номер столбца, в используемой для примера таблице, столбец с именами имеет порядковый номер два;
  4. Интервальный просмотр. Это необязательный аргумент, о нем чуть позже.

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

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

Ошибки #Н/Д, #ССЫЛКА! и #ЗНАЧ!

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

Неправильный ввод формулы

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

Результатом вычисления функции будет ошибка вида #Н/Д.

В нашем простом примере таблица состоит всего из двух столбцов, на практике же столбцов может быть больше. Важно, чтобы, во-первых, столбец, в котором будет производиться поиск искомых значений был крайним левым, а во-вторых правильно определить номер столбца. Если в таблице два столбца, а при вводе формулы в третьем аргументе указано число, которое больше двух, то результатом вычисления функции будет ошибка типа #ССЫЛКА!, а если меньше единицы, то #ЗНАЧ!

Число отформатировано как текст

В первой таблице, в ячейке с адресом «A2» число 2551 отформатировано как текст, а во второй таблице, в ячейке «D3» записано числовое значение 2551. Поскольку число 2551 не равно тексту 2551, функция выдает ошибку #Н/Д.

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

Различие в написании текстовых значений

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

В ячейке с адресом «A2» написана буква кириллицы, а в «D3» — буква латиницы, в результате ошибка.

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

ЛОЖЬ и ИСТИНА

Аргумент «Интервальный_просмотр» не является обязательным для функции ВПР. Этот аргумент может принимать два значения ЛОЖЬ (если необходимо найти точное совпадение) и ИСТИНА (если необходимо найти приблизительное совпадение).

Этот момент не всегда понятен пользователям, поэтому требует уточнения, которое проще всего показать на примере.

В последнем аргументе формулы установлено значение «ИСТИНА», что соответствует приблизительному поиску. Искомым значением является число 2552, но в таблице (той, которая расположена слева) искомое значение отсутствует, точного совпадения нет, поэтому функция ищет ближайшее меньшее число, то есть 2551 и возвращает значение «Иван».

Если искомое значение – это текст, то при интервальном просмотре, соответствующем значению «ИСТИНА» и при отсутствии точного совпадения, функция будет искать ближайшее меньшее значение по алфавиту.

Искомое значение – Петр, но в таблице нет значения Петр, поэтому функция ищет ближайшее меньшее значение по алфавиту. Буква «Ф» в алфавите находится после буквы «П», а вот «И» — до буквы «П», поэтому она и является меньшим значением. Соответственно результатом функции будет значение «Иванов».

В случае, когда необходимо найти неточное совпадение с текстовым значением, необходимо использовать интервальный просмотр «ЛОЖЬ», а к искомому значению подставлять специальные символы совпадения (?-одиночный символ и *-произвольная последовательность символов). Тильда (

) ставится в том случае, если необходимо найти сами знаки (?) и (*).

Читать еще:  Excel sin в градусах

Несколько условий в ВПР

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

Ограничения в функции ВПР

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

VBA-аналог функции ВПР

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

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

  1. Номер столбца с искомыми значениями;
  2. Таблица;
  3. Номер столбца в таблице;
  4. Условие (тип) поиска;
  5. Номер столбца для вставки результатов.

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

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

Программа подстановки данных из одного файла в другой (замена функции ВПР)

Программа предназначена для сравнения и подстановки значений в таблицах Excel.

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

То же самое можно сделать при помощи формулы =ВПР(), но:

  • формулы могут тормозить работу с файлом при пересчёте, если объём данных большой (много строк или столбцов)
  • если источник данных или файл, в который подставляются данные, каждый раз новый, — требуется время на прописывание или редактирование формул
  • если с файлами работают люди, «далёкие» от Excel, — их проще обучить нажимать одну кнопку, чем объяснять им, как прописывать эти формулы
  • иногда нужны дополнительные возможности (не учитывать заданные слова и символы при сравнении, выделять цветом изменения, копировать недостающие строки, и т.д.)

В настройках программы можно задать:

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

Как скачать и протестировать программу

Для загрузки надстройки Lookup воспользуйтесь кнопкой Скачать программу

Если не удаётся скачать надстройку, читайте инструкцию про антивирус

Если скачали файл, но он не запускается, читайте почему не появляется панель инструментов

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

Этого вполне достаточно, чтобы всё настроить и проверить, используя раздел Справка по программе

Если вам понравится, как работает программа, вы можете Купить лицензию

Лицензия (для постоянного использования) стоит 1200 рублей .

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

  • 243608 просмотров

Комментарии

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

Здравствуйте!
По видео я понял, что отличающиеся строки выстраиваются (дополнительно) внизу таблицы. Если она большая — то листать вниз — очень неудобно. Есть ли возможность ОТЛИЧИЯ встраивать на отдельный лист: к примеру синенькое — из прайса поставщика ( у меня этого нет), зелененькое — из моего прайса ( у поставщика нет). Будет крайне наглядно. Спасибо!

Юрий, вот теперь всё понятно.
Нажатием одной кнопки в надстройке Lookup такое не сделать
В 3 нажатия кнопок — легко (3 разных набора настроек)

Первое нажатие подставляет данные в ТРЕТИЙ столбец (во втором остались ранее подставленные значения)
Второе нажатие сравнивает второй и третий столбцы, помечая цветом различия
Третье нажатие копирует третий столбец во второй, и затирает третий столбец

Инструкция, как сделать 3 кнопки запуска с разными настройками на панели инструментов:
https://excelvba.ru/programmes/Lookup/manuals/SettingSwitcher

Игорь, добрый день!
К примеру есть файл (товар откуда берем данные) состоящий из двух столбцов. Столбец 1, это наименование товара, столбец 2, это количество. Файл куда будем подставлять данные (товар куда вставляем данные) так же состоит из 2 столбцов с такими же названиями. Сравнивать будем файлы по первому столбцу и в случае совпадения значения подставляем данные из второго столбца файла (товар откуда берем данные) во второй столбец файла (товар куда вставляем данные).
При первом сравнении в файле (товар куда вставляем данные) будут получены значения из файла (товар откуда берем данные).
А теперь вопрос. Если в первом файле изменилось значение в столбце 2, то при следующем сравнении, это значение заменит во втором файле уже ранее полученное значение. Как выделить цветом или еще каким то образом ячейку с этим изменившимся значением? Важно понимать какие ячейки файла (товар куда вставляем данные), в столбце 2 поменяли значения и все.

Читать еще:  Срез данных в excel

Юрий, при такой формулировке задания — не смогу сделать.
(что с чем сравниваться должно, что где как должно выделяться, — не понятно)

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

Здравствуйте, Алексей.
Программа на такое не рассчитана, но если поколдовать с настройками, и сделать в пару нажатий (с разными настройками), то теоретически можно

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

У меня — точно нет (я делаю программы только под windows)

Скажите, а под mac os аналоги есть?

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

Напишите мне на почту, прикрепив XML файл с настройками программы (на форме настроек есть слева снизу кнопка «Экспортировать настройки в файл»)

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

А галочку эту вы в настройках включали.
Конечно включал и даже При такой галочке он вместо значения тянет ПО ВСЕМУ СТОЛБЦУ опять же формулу из которого значение состоит.

А галочку эту вы в настройках включали?

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

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

Почему программа Lookup меняет формулы на значения.
Поясню. В одном столбце 1000 строк, во всех стоят формулы. При подстановке значений ВСЕ эти формулы меняются на значения, хотя в этот столбец по поиску вносится всего 100 подстановок. То есть 900 ячеек должны остаться не тронутыми с формулами как и было, а 100 ячеек как раз формулы заменятся значениями из подстановки

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

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

Дай бог тебе здоровья добрый человек. Второй раз меня выручаете!

С касперским обычно проблем нет
Только что проверил файл на их сайте, — пишет, что проблем не найдено:

сегодня касперский стал определять как вирус и удалять
Пишет — Trojan:O97M/Foretype.A!ml
Эта опасная программа выполняет команды злоумышленника

Здравствуйте, Виктор
Код программы закрыт.
Для вашего случая программа не подойдёт (она сравнивает только по полному совпадению)
Переделать (доработать) программу можно, но доработка будет стоить недешево (около 1500 руб дополнительно к стоимости программы)

Здравствуйте, подскажите после покупки, код программы будет виден, или можно ли как то переделать что бы например при нахождении двух данных в 1 книге ячейке A1 «1000,2000» B1 «Ок» и сопоставлении их во 2 книге A1, A2 проставлялись так же B1, B2 значением из 1 книги

что то вроде
1 книга
A1 1000,2000 B1 OK

2 книга
A1 1000 B1 OK
A2 2000 B2 OK

Поиск в надстройке Lookup идет по полному совпадению ячеек (искомое значение равно найденному)
А поиск, выполняемый вами вручную в Excel, идет по частичному совпадению (вхождению искомого текста в ячейку)

В вашем случае, поиск по частичному совпадению выполнять нельзя, — будете искать APV3, а будет также найдена строка с APV31 (и потом кучу времени потратите на поиск ошибок, угадывая, что с чем могло еще так совпасть)

После настройки и запуска надстройки оказалось, что он не может найти артикул в тексте и срабатывает только если удалить лишний текст в ячейке. На фото правая таблица содержит 35 000 строк и редактировать каждую ячейку займет колоссальное кол-во времени. При этом видно, что обычный поиск по документу всё находит. Возможно всё дело в неправильной настройке? Или лучшим решением будет заказать у вас макрос который справится с поставленной задачей? Спасибо! Очень жду ответа.

Спасибо за подсказку. Покупаю надстройку. ))

Впр макросом vba excel

= Мир MS Excel/функция ВПР в макросе. — Мир MS Excel

—> —> —> Правила форумаFAQНовости сайтаНовые сообщенияУчастникиRSS

Читать еще:  Бд в excel
Отметить все сообщения прочитанными и перейти на главную страницу форума

—>

  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин
Мир MS Excel » Вопросы и решения » Вопросы по VBA » функция ВПР в макросе. (Макросы/Sub)

функция ВПР в макросе.

koyaanisqatsiДата: Вторник, 17.05.2016, 15:50 | Сообщение № 1

Подскажите пожалуйста как сделать вывод информации в столбце «N» и «O» c помощью макроса. Брать данные из таблицы хочу из диспетчера имен. Уникальное значение искомое в столбце «В»

Подскажите пожалуйста как сделать вывод информации в столбце «N» и «O» c помощью макроса. Брать данные из таблицы хочу из диспетчера имен. Уникальное значение искомое в столбце «В»

Подскажите пожалуйста как сделать вывод информации в столбце «N» и «O» c помощью макроса. Брать данные из таблицы хочу из диспетчера имен. Уникальное значение искомое в столбце «В»

krosav4igДата: Вторник, 17.05.2016, 16:05 | Сообщение № 2

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

koyaanisqatsiДата: Вторник, 17.05.2016, 16:09 | Сообщение № 3
koyaanisqatsiДата: Вторник, 17.05.2016, 16:16 | Сообщение № 4
koyaanisqatsiДата: Среда, 18.05.2016, 11:12 | Сообщение № 5

krosav4ig, Макрос работает нормально все ищет.

Но когда не находит нужного пишет #Н/Д можно сделать чтобы не выдавал ошибки а просто были бы пустые поля ?

Так же происходит и когда на искомое значение нажать клавишу делит. Даже если до этого он нашел значение и отобразил нужную информацию.

krosav4ig, Макрос работает нормально все ищет.

Но когда не находит нужного пишет #Н/Д можно сделать чтобы не выдавал ошибки а просто были бы пустые поля ?

Так же происходит и когда на искомое значение нажать клавишу делит. Даже если до этого он нашел значение и отобразил нужную информацию. koyaanisqatsi

Сообщение krosav4ig, Макрос работает нормально все ищет.

Но когда не находит нужного пишет #Н/Д можно сделать чтобы не выдавал ошибки а просто были бы пустые поля ?

Так же происходит и когда на искомое значение нажать клавишу делит. Даже если до этого он нашел значение и отобразил нужную информацию. Автор — koyaanisqatsi
Дата добавления — 18.05.2016 в 11:12

Впр макросом vba excel

batiq » 15.07.2005 (Пт) 16:29

uhm » 15.07.2005 (Пт) 16:38

GSerg » 16.07.2005 (Сб) 3:50

На будущее.
Найди в папке с Офисом файл funcs.xls. Открой. И вопросы твои исчезнут.

batiq » 18.07.2005 (Пн) 10:24

batiq » 18.07.2005 (Пн) 10:37

uhm » 18.07.2005 (Пн) 10:38

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

Кстати, GSerg , так не честно — спрашивали-то про аналог функции ВПР, а ты дал саму функцию.

uhm » 18.07.2005 (Пн) 10:42

batiq » 18.07.2005 (Пн) 10:55

uhm » 18.07.2005 (Пн) 11:04

Ну, что-нибудь типа

With Workbooks(«название»).Worksheets(«название»).
или
With Workbooks(номер).Worksheets(номер).

а дальше для обращения используешь .Range(. )

batiq » 18.07.2005 (Пн) 11:21

batiq » 18.07.2005 (Пн) 11:46

GSerg » 18.07.2005 (Пн) 12:11

uhm » 18.07.2005 (Пн) 12:12

Dim i As Range
For Each i In Range(«A1:B2»)
.
Next i

С книгами и листами проще работать по имени, а не по номеру. Номер — это просто порядковый номер данной конкретной книги или листа, он не будет постоянным от запуска к запуску.

batiq » 18.07.2005 (Пн) 12:51

Так я и сделал, но только вместо Range сделал:
Workbooks(«SiteBase.xls»).Worksheets(SheetsName).Range(«A:A»). Так нельзя?.

batiq » 19.07.2005 (Вт) 15:20

batiq » 19.07.2005 (Вт) 15:21

Так я и сделал, но только вместо Range сделал:
Workbooks(«SiteBase.xls»).Worksheets(SheetsName).Range(«A:A»). Так нельзя?.

Или может следует указать путь к книге?.

uhm » 19.07.2005 (Вт) 15:25

Номер, насколько я понимаю, присваевается по порядку, т. е., открыл ты одну книгу Эксель — будет она у тебя номер 1, открыл следующую — номер 2, и т. д. Поэтому, если не нужно пробегать все книги или листы в цикле, проще пользоваться именами, а не номерами.

Workbooks(«SiteBase.xls»).Worksheets(SheetsName).Range(«A:A») — можно так писать. Проверь, что у тебя SheetsName нормальный.

uhm » 19.07.2005 (Вт) 15:27

batiq » 19.07.2005 (Вт) 16:42

uhm » 19.07.2005 (Вт) 17:07

batiq » 21.07.2005 (Чт) 16:16

uhm » 22.07.2005 (Пт) 10:05

Жаль

Написал бы хоть, что и как не получается.

alibek » 22.07.2005 (Пт) 10:24

batiq » 22.07.2005 (Пт) 14:54

batiq » 22.07.2005 (Пт) 14:59

batiq » 22.07.2005 (Пт) 16:22

для меня все — достижение!. я новичек в бейсике..

подскажите как использовать vlookup (не могу найти по ней документацию)

надо присвоить ячейке результат функции ВПР()

vlookup

batiq » 25.07.2005 (Пн) 11:34

batiq писал(а): для меня все — достижение!. я новичек в бейсике..

подскажите как использовать vlookup (не могу найти по ней документацию)

надо присвоить ячейке результат функции ВПР()

GSerg » 25.07.2005 (Пн) 11:52

batiq » 25.07.2005 (Пн) 12:09

GSerg » 25.07.2005 (Пн) 12:19

Ну так проблема в чём?

Код: Выделить всё with worksheets.add
.cells(1,5).formula = «= vlookup(A1, B1:C100, 2, FALSE())»
end with

Либо, если надо чтобы работало только в русскоязычной версии Excel:
Код: Выделить всё with worksheets.add
.cells(1,5).formulalocal = «= ВПР(A1; B1:C100; 2; ЛОЖЬ())»
end with

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

Ссылка на основную публикацию
Adblock
detector