Впр в vba excel с заполнением
Обновлено: 05.07.2024
Доброго времени суток всем всем всем. в данной теме хотел бы раскрыть ВПР по средствам VBA, давно обсуждаемая тема конечно, но есть несколько нюансов
1.По запуску макроса необходимо выбрать несколько книг в диалоговом окне, во всех этих книгах есть КЛЮЧЕВОЕ поле по которому производится ВПР.
2.После выбора каждая книга открывается по очереди, для выбора диапазона необходимых данных и так же указывается номер таблицы (все указывается через InputBox) после чего книга закрывается и открывается следующая.
3.Данные должны вставать друг за другом, то есть допустим у нас 3 книги - это три столбца, а не один.
Так же хотелось чтобы можно было осуществлять ВПР по двум критериям, но это уже просто СУПЕР БУДЕТ
1 и 2 пункт в принципе реализованы, 3 не понимаю как сделать да и сам ВПР почему то сломался, думаю там адрес диапазона съезжает.
Доброго времени суток всем всем всем. в данной теме хотел бы раскрыть ВПР по средствам VBA, давно обсуждаемая тема конечно, но есть несколько нюансов
1.По запуску макроса необходимо выбрать несколько книг в диалоговом окне, во всех этих книгах есть КЛЮЧЕВОЕ поле по которому производится ВПР.
2.После выбора каждая книга открывается по очереди, для выбора диапазона необходимых данных и так же указывается номер таблицы (все указывается через InputBox) после чего книга закрывается и открывается следующая.
3.Данные должны вставать друг за другом, то есть допустим у нас 3 книги - это три столбца, а не один.
Так же хотелось чтобы можно было осуществлять ВПР по двум критериям, но это уже просто СУПЕР БУДЕТ
1 и 2 пункт в принципе реализованы, 3 не понимаю как сделать да и сам ВПР почему то сломался, думаю там адрес диапазона съезжает. Elhust
Каждый сам выбирает правила игры
1 и 2 пункт в принципе реализованы, 3 не понимаю как сделать да и сам ВПР почему то сломался, думаю там адрес диапазона съезжает. Автор - Elhust
Дата добавления - 11.10.2016 в 12:14
VBA имеет много встроенных функций для работы. VLOOKUP - это функция рабочего листа, которая также может использоваться в VBA. Если вам известна нормальная функция VLOOKUP, вам будет намного легче понять функцию VBA. Обычный VLOOKUP имеет 4 обязательных аргумента для работы. В функции VBA это тоже самое, что и функция рабочего листа.
Формула ВЛООКУП Функция
Формула включает в себя 4 обязательных аргумента.
- Значение поиска: это базовые значения, которые вы ищете. Исходя из этого, мы ищем значение в таблице.
- Значение таблицы: это таблица, в которой хранятся все значения. Из этой таблицы, используя значение поиска, мы получим данные.
- Номер столбца: это номер столбца из таблицы, которую мы ищем. В таблице есть много столбцов, из многих столбцов нам требуется только тот столбец, для которого мы ищем данные.
- Поиск диапазона: здесь нам нужно указать, какой результат мы хотим получить. Если мы хотим точное совпадение, мы должны указать как ЛОЖЬ или 0, если мы хотим приблизительное совпадение, мы можем указать как ИСТИНА или 1.
Где писать макросы?
VBA размещается на вкладке разработчика в Excel. Если вы не можете найти вкладку разработчика, выполните следующие действия, чтобы отобразить это.
Шаг 1: Нажмите на файл.
Шаг 2. В разделе «Файл» нажмите « Параметры».
Шаг 3: Выберите « Настроить ленту».
Шаг 4. С правой стороны убедитесь, что флажок на вкладке « Разработчик » отмечен .
Теперь вы должны увидеть вкладку « Разработчик » на ленте.
Как использовать функцию VBA VLOOKUP в Excel?
VBA VLOOKUP Функция очень проста в использовании. Давайте теперь посмотрим, как использовать функцию VBA VLOOKUP в Excel с помощью нескольких примеров.
Вы можете скачать этот шаблон VBA VLOOKUP Excel здесь - Шаблон VBA VLOOKUP Excel
Пример № 1
У меня есть таблица продаж для разных продуктов. У этого есть название продукта и продажи, сделанные против этого продукта.
В ячейке E2 я хочу узнать объем продаж телевизора с помощью функции VBA VLOOKUP.
Шаг 1. Перейдите на вкладку « Разработчик » и нажмите на Visual Basic
Шаг 2: Как только вы нажмете на Visual Basic, он откроет окно ниже для вас
Шаг 3: Нажмите на Вставить и выберите Модуль
Шаг 4: Как только вы нажмете на Модуль, он вставит новый модуль для вас. Дважды щелкните на этом недавно вставленном модуле, чтобы написать свой код VLOOKUP.
Шаг 5: Скопируйте и вставьте приведенный ниже код в ваш недавно вставленный модуль.
Sub Vlookup_Example()
Dim rng As Range, FinalResult As Variant
Set rng = Sheets("Example 1").Range("E2")
FinalResult = Application. WorksheetFunction. VLookup(Range("D2"), Range("A2:B7"), 2, False)
rng = FinalResult
End Sub
Позвольте мне разобрать код, чтобы объяснить его вам.
Строка 1: Dim rng в качестве Range, FinalResult в качестве Variant
Это первая строка кода. Используя оператор DIM, я объявил две переменные, одна из которых является rng, а другая - FinalResult.
Rng содержит Range в качестве переменной, а FinalResult содержит Variant в качестве переменной.
Строка 2: Установите rng = Sheets («Пример 1»). Range («E2»)
Ранее объявленная переменная rng является объектной переменной, поэтому нам нужно указать, к какому диапазону мы на самом деле ссылаемся. Я установил диапазон для ячейки E2. Это на самом деле, где мне нужно, чтобы результат был отображен.
Строка 3: FinalResult = Приложение. WorksheetFunction.VLookup (Range («D2»), Range («A2: B7»), 2, False)
Это важная строка кода. FinalResult содержит переменную варианта, т.е. любой тип данных. FinalResugets получает данные из функции VLOOKUP.
Строка 4: rng = FinalResult
Начальная переменная rng равна значению FinalResult. Rng означает ячейку E2, FinalResult означает значение, возвращаемое функцией VLOOKUP.
Шаг 7: Мы получим вывод ниже.
Пример № 2
Возьмите те же данные из приведенного выше примера. Здесь я собираюсь создать имена и применить то же самое в VLOOKUP. Код будет таким же, как и в предыдущем примере. Только во VLOOKUP я меняю ссылки.
Sub Vlookup_Example1()
Dim rng As Range, FinalResult As Variant, Table_Range As Range, LookupValue As Range
Set rng = Sheets("Example 1").Range("E2")
Set Table_Range = Sheets("Example 1").Range("A2:B7")
Set LookupValue = Sheets("Example 1").Range("D2")
FinalResult = Application. WorksheetFunction. VLookup(LookupValue, Table_Range, 2, False)
rng = FinalResult
End Sub
Значение поиска установлено в ячейку D2 в имени LookupValue. Массив таблицы установлен от A2 до B7 от имени Table_Range.
Создать кнопку макроса
Как только код макроса написан, нам нужно спроектировать макрос.
Шаг 1: Пойдите, чтобы Вставить и Выбрать прямоугольную форму .
Шаг 2: Нарисуйте прямоугольную форму
Шаг 3: После того, как прямоугольная форма нарисована, щелкните правой кнопкой мыши прямоугольную форму и нажмите « Назначить макрос» .
Шаг 4: Как только вы нажмете «Назначить макрос», откроются все имена макросов . Выберите имя макроса, который вы хотите назначить. Я назначаю имя макроса примера 1 прямоугольной форме.
Шаг 5: Теперь прямоугольная форма стала кнопкой макроса. Как только вы нажмете на прямоугольную форму, вы получите вывод ниже.
Вы можете изменить ячейку значения поиска D2 и получить результат. На изображении ниже я изменил значение ячейки D2 на Mobile.
Что следует помнить о VBA VLOOKUP
- В VBA тоже VLOOKUP работает так же, как функция листа.
- Объявление переменных и назначение типа данных - важная вещь здесь.
- В полученной ячейке мы не видим никакой формулы. Любое значение, возвращаемое функцией VBA, не содержит никакой формулы.
Рекомендуемые статьи
Это руководство к функции Excel VBA VLOOKUP. Здесь мы обсуждаем формулу VLOOKUP и как использовать VBA VLOOKUP в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
ВПР на vba
Доброго времени суток! В файлах Excel каждый день обрабатываем несколько сотен тысяч строк.
Vlookup (ВПР) на VBA
Здавствуйте, Написал аналог функции vlookup (ВПР) на VBA. Основная задача была сделать нечто, что.
Применение впр и двссыл
Коллеги, доброго дня, Необходимо выполнить ВПР с возможностью изменения диапазона, по которому.
Правосторонний ВПР с условиями (VBA)
Ребят выручите пожалуйста, задача очень простая для тех, кто знает вба, я его вообще не пониманию.
Но как мне теперь доработать этот код так, чтоб пользователь просто выбрал файл "БД" из которого будут данные подтягиваться?
Помогите пожалуйста!))
Вот пример макроса с использованием словаря и загрузкой с отдельной БД:
Вот пример макроса с использованием словаря и загрузкой с отдельной БД: А Вы не подскажите мне, как переделать код, чтоб в свой файл я мог подтянуть поля не по порядку, а по конкретной позиции поля в "БД"?
То есть например в свой файл мне нужно подтянуть 5-й, 9-й, 14-й столбец из книги "БД" То есть например в свой файл мне нужно подтянуть 5-й, 9-й, 14-й столбец из книги "БД" Например если нужно будет подтянуть первый и сороковой - то с таким подходом может и не сработать, т.к. может не хватить памяти, если база действительно база, а не так списочек.
Тогда берите в массив не всё что есть, а конкретно только нужные столбцы в разные массивы. Спасибо за советы. Я так и подумал, что отдельно возьму нужные поля. toiai, здравствуйте!
У меня есть пара вопросов по вашему коду.
1. Если в диапазоне, указанном в строке 17 есть пустые ячейки, то ячейки в этой строке в соседних столбцах все стираются. Можно ли этого как-то избежать?
Если какая-то ячейка в этом диапазоне пуста, то соседние ячейки стираются.
Небольшое пояснение.
Есть таблица, разбитая на разделы. В некоторые ячейки столбца "В" вносятся значения. Запускается макрос, выбирается файл, к указанным значениям, в соседние столбцы, подставляются необходимые значения из выбранного файла. Если же ячейка пустая, то соседние столбцы стираются, что крайне не желательно.
2. При указанном диапазоне "В12:В200" и существующей таблице на листе выдаётся ошибка 1004. Если из выражения убрать lastRow, то работает без ошибки.
3. Можно ли приспособить данный макрос для работы с тексбоксами? Нужно чтобы макрос брал значение из одного текстбокса, находил его в указанном файле и возвращал ряд значений в другие текстбоксы. Если можно, то как это сделать? В VBA чайник, причём даже без свистка, а нужда в таком макросе есть.
Извините, если данный вопрос нужно было вынести в отдельную тему.
Добавлено через 7 минут
Если кто может помочь с моим вопросом, буду рад любой помощи.
Все, кто сталкивался с функцией ВПР знают, что она умеет возвращать исключительно одно значений - первое найденное. Но что делать, когда надо видеть все значения? К примеру имеется такая таблица:
И из этой таблицы необходимо получить фамилии(поле ФИО) всех сотрудников из отдела Сбыта:
Тут два варианта:
-
Вариант 1: все значения необходимо получить в одной ячейке через разделитель(скажем запятую). Такое возможно только через использование VBA(на момент написания статьи). Такую функцию я написал давно: Как сцепить несколько значений в одну ячейку по критерию? СцепитьЕсли
. В результате получим что-то вроде: Александров Иван,Александров Петр,Андреев Алексей .
Здесь надо сразу оговориться - при помощи непосредственно ВПР (VLookup) это сделать ну никак не получится. Но это можно сделать при помощи её родственников - связки ИНДЕКС(ПОИСКПОЗ) (INDEX(MATCH)) . Плюс еще пара функций. В сборе функция выглядит так:
=ИНДЕКС( $A$12:$G$111 ;НАИМЕНЬШИЙ(ЕСЛИ( $I$12 = $C$12:$C$111 ;СТРОКА( $C$12:$C$111 )-11);СТРОКА( A1 ));1)
=INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1)
где:
- $A$12:$G$111 - таблица с ФИО и всеми данными
- $I$12 - ячейка с названием отдела
- $C$12:$C$111 - столбец с названиями отделов в таблице $A$12:$G$111
А теперь начнем разбирать формулу по кусочкам
Предлагаю сразу скачать пример файла с данными и формулами - тогда понять будет проще:
Tips_All_VlookupAllVals.xls (84,5 KiB, 18 494 скачиваний)
- Первое и самое главное - формула вводится в ячейку как формула массива. Это значит, что для приложенного к статье примера необходимо будет выделить ячейку J12 , записать в неё формулу и завершить ввод нажатием сразу трех клавиш: Ctrl+Shift+Enter. После этого скопировать ячейку, выделить диапазон J13:J39 и вставить туда скопированную ячейку.
- За отбор ФИО указанного отдела отвечает этот кусок: ЕСЛИ( $I$12 = $C$12:$C$111 ;СТРОКА( $C$12:$C$111 )-11)
IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11)
Как видно здесь идет сравнение отдела, записанного в $I$12 со всеми отделами в таблице: $C$12:$C$111 . Если отдел совпадает, то функция возвращает номер строки СТРОКА( $C$12:$C$111 ) . Но нам нужен не номер строки на листе, а номер строки внутри таблицы(потому что в формулу ИНДЕКС (INDEX) у нас передан диапазон $A$12:$G$111 и если передать в его второй аргумент( номер_строки ) 14, то получим не Александров Иван , а Андреев Олег ). Поэтому отнимаем от номера строки 11, чтобы было возвращено число 3(а не 14). Т.к. формула записана формулой массива - ЕСЛИ (IF) возвращает массив из номеров строк и логического ЛОЖЬ (FALSE) (если отдел не совпадает):
шаг 1: ЕСЛИ(;-11)
шаг 2: ЕСЛИ(;)
шаг 3: - Т.к. ЕСЛИ (IF) в данном случае возвращает массив значений, включая ненужные нам ЛОЖЬ (FALSE) - применяем НАИМЕНЬШИЙ (SMALL) , которая будет брать только числа (сначала 3, затем 6 и т.д.), а логические значения ЛОЖЬ (FALSE) будет игнорировать:
НАИМЕНЬШИЙ(;СТРОКА(A1))
SMALL(;ROW(A1))
Функция НАИМЕНЬШИЙ (SMALL) отбирает из указанного массива чисел наименьшее. При этом можно отобрать по рангу - первое наименьшее, второе наименьшее, третье наименьшее и т.д.
И в довершение неплохо бы убрать ошибку в случае, если ни одно значение не соответствует критерию или значений больше, чем ячеек с формулами. Более подробно подобное решение я описывал в этой статье: Как в ячейке с формулой вместо ошибки показать 0
Для всех версий Excel:
=ЕСЛИ(ЕОШ(ИНДЕКС( $A$12:$G$111 ;НАИМЕНЬШИЙ(ЕСЛИ( $I$12 = $C$12:$C$111 ;СТРОКА( $C$12:$C$111 )-11);СТРОКА( A1 ));1));"";ИНДЕКС( $A$12:$G$111 ;НАИМЕНЬШИЙ(ЕСЛИ( $I$12 = $C$12:$C$111 ;СТРОКА( $C$12:$C$111 )-11);СТРОКА( A1 ));1))
=IF(ISERR(INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1));"";INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1))
Для Excel 2007 и выше:
=ЕСЛИОШИБКА(ИНДЕКС( $A$12:$G$111 ;НАИМЕНЬШИЙ(ЕСЛИ( $I$12 = $C$12:$C$111 ;СТРОКА( $C$12:$C$111 )-11);СТРОКА( A1 ));1);"")
=IFERROR(INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1);"")
Плюс к этому в приложенном к статье файле я привел формулу, которая возвращает список всех сотрудников выбранного отдела без повторений и формулу с возможностью просто поменять заголовок в результирующем столбце(в файле это столбец K, ячейка K11 ) на любой из исходной таблицы и данные будут отображены из этого столбца.
Например, если вместо столбца ФИО записать в K11 Адрес - будут отображены все адреса сотрудников выбранного отдела. Если записать Телефон - все телефоны сотрудников выбранного отдела.
Читайте также: