Excel vba впр

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

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

Формула ВПР

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ЛОЖЬ и ИСТИНА

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую

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

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

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

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

ВПР — это аббревиатура от вертикального просмотра. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку). Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP). ГПР, в противоположность ВПР, производит горизонтальный поиск, однако концепция Excel (да и вообще концепция организации данных) подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам. Если вы в Excel слишком часто пользуетесь функцией ГПР, то, вполне вероятно, что вы чего-то не поняли в этой жизни.

Синтаксис

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

=ВПР( ; ; [; ] ), тут:

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

— ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра ;

— номер столбца в диапазоне, из которого будет возвращено значение;

— это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона . В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1.

Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0) либо же о диапазонном просмотре (ИСТИНА или 1).

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

Как же конкретно работает формула ВПР

• Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона.
  
• Вид формулы II. Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром , в противном случае возвращается код ошибки #Н/Д (#N/A).
  

Схемы работы формул

ВПР тип I

ВПР тип II

Следствия для формул вида I

1. Формулы можно использовать для распределения значений по диапазонам.
2. Если первый столбец содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
3. Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
4. Данный вид вернёт ошибку #Н/Д, только если не найдёт значения меньше или равное искомому.
5. Понять, что формула возвращает неправильные значения, в случае если ваш массив не отсортирован, довольно затруднительно.

Следствия для формул вида II

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

Производительность работы функции ВПР

Вы добрались до кульминационного места статьи. Казалось бы, ну какая разница, укажу ли я в качестве последнего параметра ноль или единицу? В основном все указывают, конечно же, ноль, так как это довольно практично: не надо заботиться о сортировке первого столбца массива, сразу видно, найдено значение или нет. Но если у вас на листе несколько тысяч формул ВПР (VLOOKUP), то вы заметите, что ВПР вида II работает медленно. При этом обычно все начинают думать:

• мне нужен более мощный компьютер;
• мне нужна более быстрая формула, например, многие знают про ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая якобы быстрее на жалкие 5–10%.

И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ! Не на 57%, а на 5 700%. Данный факт я проверил вполне надёжно.

Секрет такой быстрой работы кроется в том, что на отсортированном массиве можно применять чрезвычайно эффективный алгоритм поиска, который носит название бинарного поиска (метод деления пополам, метод дихотомии). Так вот ВПР вида I его применяет, а ВПР вида II ищет без какой-либо оптимизации вообще. То же самое относится и к функции ПОИСКПОЗ (MATCH), которая включает в себя аналогичный параметр, а также и к функции ПРОСМОТР (LOOKUP), которая работает только на отсортированных массивах и включена в Excel ради совместимости с Lotus 1-2-3.

Недостатки формулы

Недостатки ВПР очевидны: во-первых, она ищет только в первом столбце указанного массива, а во-вторых, только справа от данного столбца. А как вы понимаете, вполне может случиться так, что столбец, содержащий необходимую информацию, окажется слева от столбца, в котором мы будем искать. Этого недостатка лишена уже упомянутая связка формул ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), что делает её наиболее гибким решением по извлечению данных из таблиц в сравнении с ВПР (VLOOKUP).

Некоторые аспекты применения формулы в реальной жизни

Диапазонный поиск

Классическая иллюстрация к диапазонному поиску — задача определения скидки по размеру заказа.

Поиск текстовых строк

Безусловно, ВПР ищет не только числа, но и текст. При этом надо принимать во внимание, что регистр символов формула не различает. Если использовать символы подстановки, то можно организовать нечёткий поиск. Есть два символа подстановки: «?» — заменяет один любой символ в текстовой строке, «*» — заменяет любое количество любых символов.

Борьба с пробелами

Часто поднимается вопрос, как решить проблему лишних пробелов при поиске. Если справочную таблицу ещё можно вычистить от них, то первый параметр формулы ВПР не всегда зависит от вас. Поэтому если риск засорения ячеек лишними пробелами присутствует, то можно применять для очистки функции СЖПРОБЕЛЫ (TRIM).

Разный формат данных

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

=ВПР(−−D7; Продукты!$A$2:$C$5; 3; 0) — если D7 содержит текст, а таблица — числа;

=ВПР(D7 & «»); Продукты!$A$2:$C$5; 3; 0) — и наоборот.

Кстати, перевести текст в число можно сразу несколькими способами, выбирайте:

• Двойное отрицание —D7.
• Умножение на единицу D7*1.
• Сложение с нулём D7+0.
• Возведение в первую степень D7^1.

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

Как подавить выдачу #Н/Д

Это очень удобно делать при помощи функции ЕСЛИОШИБКА (IFERROR).

Например: =ЕСЛИОШИБКА( ВПР(D7; Продукты!$A$2:$C$5; 3; 0); «»).

Если ВПР вернёт код ошибки #Н/Д, то ЕСЛИОШИБКА его перехватит и подставит параметр 2 (в данном случае пустая строка), а если ошибки не произошло, то эта функция сделает вид, что её вообще нет, а есть только ВПР, вернувший нормальный результат.

Массив

Часто забывают ссылку массива сделать абсолютной, и при протягивании массив «плывёт». Помните, что вместо A2:C5 следует использовать $A$2:$C$5.

Хорошей идеей является размещение справочного массива на отдельном листе рабочей книги. Не путается под ногами, да и сохраннее будет.

Ещё более хорошей идеей будет объявление этого массива в виде именованного диапазона.

Многие пользователи при указании массива используют конструкцию вида A:C, указывая столбцы целиком. Этот подход имеет право на существование, так как вы избавлены от необходимости отслеживать тот факт, что ваш массив включает все необходимые строки. Если вы добавите строки на лист с первоначальным массивом, то диапазон, указанный как A:C, не придётся корректировать. Безусловно, эта синтаксическая конструкция заставляет Excel проводить несколько большую работу, чем при точном указании диапазона, но данными накладными расходами можно пренебречь. Речь идёт о сотых долях секунды.

Ну и на грани гениальности — оформить массив в виде умной таблицы.

Использование функции СТОЛБЕЦ для указания колонки извлечения

Если таблица, в которую вы извлекаете данные при помощи ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов. При этом все ВПР-формулы будут одинаковыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что у первого параметра координата столбца абсолютная.

Создание составного ключа через &»|»&

Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем «Код»), а числовым, то для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.

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

ВПР с возвратом всех значений

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

И из этой таблицы необходимо получить фамилии(поле ФИО) всех сотрудников из отдела Сбыта:

Тут два варианта:

Вариант 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, 10 390 скачиваний)

• Первое и самое главное — формула вводится в ячейку как формула массива. Это значит, что для приложенного к статье примера необходимо будет выделить ячейку 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: ЕСЛИ(<ЛОЖЬ:ЛОЖЬ: ИСТИНА :ЛОЖЬ:ЛОЖЬ: ИСТИНА :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;<12:13: 14 :15:16: 17 :18:19:20>-11)
  шаг 2: ЕСЛИ(<ЛОЖЬ:ЛОЖЬ: ИСТИНА :ЛОЖЬ:ЛОЖЬ: ИСТИНА :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;<1:2: 3 :4:5: 6 :7:8:9>)
  шаг 3:
• Т.к. ЕСЛИ (IF) в данном случае возвращает массив значений, включая ненужные нам ЛОЖЬ (FALSE) — применяем НАИМЕНЬШИЙ (SMALL) , которая будет брать только числа (сначала 3, затем 6 и т.д.), а логические значения ЛОЖЬ (FALSE) будет игнорировать:
  НАИМЕНЬШИЙ(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;СТРОКА(A1))
  SMALL(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;ROW(A1))
  Функция НАИМЕНЬШИЙ (SMALL) отбирает из указанного массива чисел наименьшее. При этом можно отобрать по рангу — первое наименьшее, второе наименьшее, третье наименьшее и т.д.

1. с первым аргументом разобрались — это уже отобранные записи, где число — строка в таблице с нужной ФИО и ненужные нам логические ЛОЖЬ (FALSE) .
2. второй аргумент СТРОКА(A1) для каждой следующей строки будет меняться. В приложенном примере первая ячейка с формулой — J12 . В ней СТРОКА(A1) равна 1, т.е. НАИМЕНЬШИЙ (SMALL) вернет первое наименьшее из массива чисел — 3 . В ячейке J13 это уже будет СТРОКА(A2) и она равна 2, а значит НАИМЕНЬШИЙ (SMALL) вернет второе наименьшее из массива чисел — 6 . И т.д. Когда все числа закончатся — НАИМЕНЬШИЙ (SMALL) вернет значение ошибки #ЧИСЛО! (#NUM!)
   ячейка J12
   шаг 1: НАИМЕНЬШИЙ(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;СТРОКА(A1))
   шаг 2: НАИМЕНЬШИЙ(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;1)
   шаг 3: НАИМЕНЬШИЙ( 3 )
   ячейка J13
   шаг 1: НАИМЕНЬШИЙ(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;СТРОКА(A2))
   шаг 2: НАИМЕНЬШИЙ(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;2)
   шаг 3: НАИМЕНЬШИЙ( 6 )

И в довершение неплохо бы убрать ошибку в случае, если ни одно значение не соответствует критерию или значений больше, чем ячеек с формулами. Более подробно подобное решение я описывал в этой статье: Как в ячейке с формулой вместо ошибки показать 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 Адрес — будут отображены все адреса сотрудников выбранного отдела. Если записать Телефон — все телефоны сотрудников выбранного отдела.

Tips_All_VlookupAllVals.xls (84,5 KiB, 10 390 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Конференция VBStreets

Весь вкус программирования!

• Список форумов‹Смежные технологии‹VBA
• Изменить размер шрифта

• FAQ
• Вход

функция ВПР

функция ВПР

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

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

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

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

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

Совет: Чтобы получить дополнительную справку о функции ВПР, просмотрите эти видео с 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.

Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона задано значение 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, ложь).

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