Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция впр в макросе excel

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

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

Формула ВПР

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ЛОЖЬ и ИСТИНА

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

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

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

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

Читать еще:  Excel не сохраняет изменения при закрытии

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

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

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

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

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

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

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

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

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

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

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

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

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

ВПР (функция ВПР)

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

ФУНКЦИЯ ВПР используется, если нужно найти элементы в таблице или диапазоне по строкам. Например, можно найти цену на автомобильную часть по номеру детали или получить имя сотрудника на основе его кода сотрудника.

Совет: Чтобы получить дополнительную справку о функции ВПР, просмотрите эти видео с YouTube от экспертов сообщества Excel!

Самая простая функция ВПР означает следующее:

= ВПР (необходимые условия для поиска, номер столбца в диапазоне, содержащий возвращаемое значение, возвращаемое приближенное или точное соответствие — обозначено как 1/истина или 0/ложь).

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

Используйте функцию ВПР для поиска значения в таблице.

ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])

= ВПР (A2; A10: C20; 2; ИСТИНА)

= ВПР (a2; сведения о клиенте)! А:Ф, 3, ЛОЖЬ)

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

Например, если Таблица-массив охватывает ячейки B2: D7, то lookup_value должен находиться в столбце B.

Искомое_значение может являться значением или ссылкой на ячейку.

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

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

Номер столбца (начиная с 1 для самого левого столбца table_array), содержащего возвращаемое значение.

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

Приближенное соответствие: 1/истина предполагает, что первый столбец в таблице отсортирован либо в числовом формате, либо в алфавитном порядке, а затем будет искать ближайшее значение. Это способ по умолчанию, если не указан другой. Например, = ВПР (90; a1: B100; 2; истина).

Точное совпадение: 0/ложь осуществляет поиск точного значения в первом столбце. Например, = ВПР («Смит»; a1: B100; 2; ложь).

Начало работы

Для построения синтаксиса функции ВПР вам потребуется следующая информация:

Значение, которое вам нужно найти, то есть искомое значение.

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

Читать еще:  Кластерный анализ в excel пример

Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона задано значение B2: D11, число B должно быть первым столбцом, а в качестве второго — «C» и т. д.

При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

Теперь объедините все перечисленное выше аргументы следующим образом:

= ВПР (искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением, приближенное соответствие (истина) или точное совпадение (ложь)).

Примеры

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

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

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

Здесь в столбцах A-F и H есть значения или формулы, которые используют только значения на листе, а остальные столбцы используют функцию ВПР и значения столбца A (клиентский код) и столбец B (юрист) для получения данных из других таблиц.

Скопируйте таблицу с общими полями на новый лист и присвойте ей имя.

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

Для каждой из перечисленных связей обратите внимание на следующее:

Поле, связывающее таблицы (в диалоговом окне отображаются в круглых скобках). Это lookup_value формулы функции ВПР.

Имя связанной таблицы подстановки. Это table_array формулы функции ВПР.

Поле (столбец) в связанной таблице подстановки с данными, которые вы хотите добавить в новый столбец. Эти сведения не отображаются в диалоговом окне Управление связями. чтобы узнать, какое поле вы хотите извлечь, вам потребуется ознакомиться со связанной таблицей подстановки. Вы хотите отметить номер столбца (A = 1) – это col_index_num в формуле.

Чтобы добавить поле в новую таблицу, введите формулу ВПР в первом пустом столбце с помощью сведений, собранных в действии 3.

В нашем примере столбец G использует юрист ( lookup_value) для получения данных о тарифных курсах из четвертого столбца ( col_index_num = 4) из таблицы судебные тблатторнэйс ( table_array) с формулой = ВПР ([@Attorney], tbl_Attorneys, 4, ложь).

В формуле также могут использоваться ссылки на ячейки и ссылки на диапазоны. В нашем примере это было бы = ВПР (a2; «Юристы»! А:Д, 4, ложь).

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

Функция впр в макросе excel

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

—> —> —> Правила форума

FAQНовости сайтаНовые сообщенияУчастникиRSS
Отметить все сообщения прочитанными и перейти на главную страницу форума

—>

  • Страница 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

Усовершенствуем функцию ВПР в Excel

Прочтение публикации Упрощаем бинарный поиск в Excel сподвигло на дополнительное усовершенствование функции ВПР по сравнению с приведенным в статье.

Что не было учтено, и что хотелось бы добавить:

1. Универсальность, т.е. возможность вызывать функцию как для отсортированного массива, так и для неотсортированного.

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

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

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

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

Дополнительные плюшки

1. Возможность поиска в массиве, отсортированном по убыванию (Ordered = -1).
2. Функция позволяет делать поиск если любой (или оба) из аргументов a и b являются горизонтальными рядами (т.е. обобщает и функцию горизонтального просмотра ГПР).

Комментарии

1. Вызов CDbl(«») нужен для генерации ошибки (выдача #ЗНАЧ#).
2. Используется не ВПР, а функция Match (русский аналог — ПОИСКПОЗ).

Спасибо за внимание!

Ой, у вас баннер убежал!

Редакторский дайджест

Присылаем лучшие статьи раз в месяц

Скоро на этот адрес придет письмо. Подтвердите подписку, если всё в силе.

  • Скопировать ссылку
  • Facebook
  • Twitter
  • ВКонтакте
  • Telegram
  • Pocket

Похожие публикации

  • 21 июня 2012 в 09:42

Я не могу написать бинарный поиск

В поисках оптимизации загрузки Vista

Очень быстрый поиск с программой Everything

Вакансии

AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Комментарии 13

для интеграции есть адд-он к Excel: RExcel — http://rcom.univie.ac.at/download.html#RExcel.

А если все делать в R, то есть пакеты и для открытия файлов xls, и сохранения результатов в них же.

но с удовольствием бы перешел на R.

Ээээ, кхм…
Это хорошо, что сделали типа ВПР, но через ПОИСКПОЗ, хотя в первоначальной статье именно про это в коментариях и говорилось.
Однако…
Функции пита ВПР или ПОИСКПОЗ прменяются в 2-х видах задач:
1. Разовая обработка больших массивов данных в экселе. Как оказала практика, если вы через ОЛЕ выгружаете данные эксель, то лучше выгружать сразу в Аксес. Для статитстической обработки очень больших данных это намного практичнее и быстрее.
Более того, если выгружать в эксель и обрабатывать в нем, то нюансы функции ВПР не так важны, как и скорость — работа разовая, можно 1- сек подождать.

2. Рабочие файлы, в которых ежедневно, ежеднелельно и ежемесячно ведется работа, обрабатываемые данные могут заносится туда выгрузками или вручную (т.е. никаких сортировок).
И функции типа ВПР, ПОИСПОЗ или СУММПРОИЗВ применяются в массовых масштабах для создания реляционной базы.
Данные измеряются не 200 тыс строк, а на порядок меньше, зато указанные функции используются по 5-15 раз в каждой строке, итого они вызываются под 1 млн раз.
Это я к чему? При такое количестве вызоов функций любое использование ВБА подвешивает программу на минуты и часы. По этой простой причине примеются исключительно встроенные фукнции, т.к. скрипты катасрофически тормознутые.
Если у вас 100 вызовов ВБА — это нормально, если 100 тыс. — это беда.
Причем тормозит именно сам механизм ВБа, даже если вы там внутри вызываете встроенные функции.

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