Как сделать левый впр

Добавил пользователь Morpheus
Обновлено: 04.10.2024

ВПР (англ. вариант – VLOOKUP) – очень удобная и полезная функция особенно для работы с большим объемом данных, поскольку позволяет автоматически сопоставить диапазоны с десятками тысяч наименований. Является разновидностью поиска, но только вертикального (сверху вниз) – извлекает информацию из таблицы или определенного диапазона по строкам. Работает во всех версиях Excel и даже Google Sheets.

Синтаксис

Элемент – может быть числовым (адрес ячейки) или текстовым ("текст").

Адрес таблицы – диапазон ячеек, где примерно находится значение.

Номер столбца – принимает целое число из диапазона от 1 до n, из него будет извлечен результат.

Интервальный просмотр – приблизительное (ближайшее) соответствие критерию обозначается как 1 (истина), а точное соответствие – 0 (ложь). Данный логический аргумент указывать необязательно, если таблица отсортирована от минимального к максимальному значению. Если таблица не отсортирована и аргумент опущен, это равносильно истине.

Как работает функция?

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

С одним условием

Рассмотрим функцию на простом примере поиска сотрудника по присвоенному ему коду. Таблицу нужно отсортировать в порядке возрастания.

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

С несколькими условиями

Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.

Поиск по нескольким столбцам

Объем данных расширен, и нужно найти конкретное значение среди нескольких столбцов, просуммировав данные с помощью функции СУММ.

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

Сравнение двух таблиц

По желанию теперь можно найти численную и процентную разницу.

Поиск в выпадающем списке

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

Меняется фамилия в списке – меняется и зарплата.

Перенос данных

Есть таблица с менеджерами и объемом их продаж. Во второй таблице значится сумма премии за продажу для каждого менеджера. Необходимо перенести данные в левую таблицу, чтобы подсчитать общую выручку (произведение объема продаж и премии за 1 продажу: =ПРОИЗВЕД(C2*D2)).

Ошибки

В качестве номера столбца указано число 0.
Длина первого аргумента превышает 255 знаков.

Вместо заключения

В Excel есть улучшенная версия ВПР – функция ПРОСМОТРХ. Считается более простой и удобной в использовании, работает в любом направлении и возвращает точные совпадения по умолчанию.

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

В программах OpenOffice и LibreOffice используется VLOOKUP, ее синтаксис =VLOOKUP(lookupvalue; datatable; columnindex; mode), а работает функция точно так же, как описано выше.

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

Ничего не могу сделать, таково условие задачи! Может формулой массива или еще как, главное, чтобы ВПР в формуле фигурировало.

Ничего не могу сделать, таково условие задачи! Может формулой массива или еще как, главное, чтобы ВПР в формуле фигурировало. finlib

Нет, смысл именно в последнем столбце, он так и называется ФИО для ВПР. Остальные данные были для другого задания. Чтобы не путаться, я их убрал.

Нет, смысл именно в последнем столбце, он так и называется ФИО для ВПР. Остальные данные были для другого задания. Чтобы не путаться, я их убрал. finlib

Можну операцию произвести и с функцией СУММЕСЛИ, только в начале необходимо произвести сортировку по алфвавиту.

Или Вы неправильно поняли задание или одно из двух
Здесь два варианта
1 Перенести столбец влево, затем использовать ВПР
2 Указать, что задание не может быть выполнено из-за особенностей функции ВПР.
Можно конечно формулой, но не думаю, что студентам такие формулы задают

Ух ты! Да, кажется сработало. Спасибо большущее!! Буду разбираться как это работает. Это не по учебе, задали мне тут задачку на засыпку
Там еще вопрос про вставку названия по столбцу с максимальным значением есть, буду признателен, если с ней тоже поможете!

Синтаксис

Элемент – может быть числовым (адрес ячейки) или текстовым ("текст").

Адрес таблицы – диапазон ячеек, где примерно находится значение.

Номер столбца – принимает целое число из диапазона от 1 до n, из него будет извлечен результат.

Как работает функция?

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

С одним условием

С несколькими условиями

Поиск по нескольким столбцам

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

Сравнение двух таблиц

По желанию теперь можно найти численную и процентную разницу.

Поиск в выпадающем списке

Меняется фамилия в списке – меняется и зарплата.

Перенос данных

Ошибки

В качестве номера столбца указано число 0.
Длина первого аргумента превышает 255 знаков.

Вместо заключения

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

Любой пользователь, который работает с функцией ВПР (LOOKUP), через некоторое время понимает, что не очень удобно работать с функцией, которая производит поиск только справа от столбца, в котором находится искомое значение. Функции ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX) могут разрешить эту проблему, но в целом формула будет намного длиннее и труднее по сравнению с более простой ВПР.

Пользовательская надстройка rLOOKUP работает как ВПР, но позволяет производить обратный поиск. Функция просто использует методы указательным и матч в VBA. Она предоставляется в Excel надстройка для простоты использования.

Загрузите надстройку: .xlam файл (15 Кб).

После установки надстройки функцию rLOOKUP можна найти Формулы -> Вставить функцию -> Категория: Определенные пользователем -> rLOOKUP.

Описание функции

Аргументы для функции rLOOKUP такие же, как и для функции ВПР, см. скриншоты ниже. Чтобы выполнить обратный поиск, просто введите отрицательное число в аргумент 'Col_Index_Num' (смотри примеры ниже). Формула будет Lookup в направлении влево.

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

Пример функции

Следующие данные Excel показывает адреса офисов, имена сотрудников и должность.

Для ответа на этот запрос "Кто работает офисе B43?"мы можем использовать функцию ВПР:

ВПР ( "B43"; A1:C6;2; ЛОЖЬ) возвращает "Dilbert".

Тем не менее, если мы хотим знать, "Где находится офис Дилберта?", то ВПР не будет работать. Вот тут пригодится функция rLOOKUP:

rLOOKUP ( "Dilbert"; A1:B6, -2).

Обратите внимание, в аргументах номер столбца указывается отрицательным число -2, что позволяет производить поиск в колонке слева.

Вообще, функция работает в обоих направлениях:

rLOOKUP ( "B43";A1:C6;2) возвращает "Dilbert" и

rLOOKUP ( "Dilbert"; A1:B6; -2) возвращает "B43".

Другие примеры приведены ниже:

Как работает функция

Функция использует функции ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX) для запуска обратного поиска.

Исходный код для функции смотрите ниже.

'The Source Column is the Column where the Lookup_value is located. It is the leftmost column if
'Col_index_number is positive. If Col_index_number is negative, it is the rightmost column
Set Source_Col = Table_array.Columns(IIf(Col_index_num > 0, 1, Table_array.Columns.Count))

'Dest_col_num is the column number within the Table_array range from where we have to pick up the entry against the Lookup_value
Dest_Col_num = IIf(Col_index_num > 0, Col_index_num, Table_array.Columns.Count + Col_index_num + 1)

Читайте также: