Как сделать чтобы впр выбирал максимальное значение

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

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

Описание функции ВПР

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

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

Вот другой пример, который наглядно демонстрирует принцип работы функции ВПР:

Синтаксис

Аргументы

Обязательный. Диапазон ячеек, содержащий данные. Можно использовать ссылку на диапазон (например, A2:D8) или имя диапазона. Значения в первом столбце аргумента таблица — это значения, в которых выполняется поиск аргумента искомое_значение. Эти значения могут быть текстовыми, числовыми или логическими. Текстовые значения в нижнем и верхнем регистре считаются эквивалентными.

Обязательный. Номер столбца в аргументе таблица, из которого возвращается совпадающее значение. Если номер_столбца равен 1, то возвращается значение из первого столбца аргумента таблица; если номер_столбца равен 2, — значение из второго столбца аргумента таблица и т. д.

Если значение аргумента номер_столбца :

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

Если аргумент интервальный_просмотр имеет значение ИСТИНА или опущен, то возвращается точное или приблизительное совпадение. Если точное совпадение не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение.

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

Замечания

Нюансы использования ВПР

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

Поиск в первом столбце

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

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

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

Поиск до первого совпадения

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

Демонстрация поиска ВПР до первого совпадения

Данное ограничение можно обойти, решение не самое простое, но детально описано в курсе Excel.

Независимость от регистра

Для Excel и ВПР не важно в каком регистре (заглавные или прописные буквы) записано искомое значение и как оно записано в самом диапазоне.

Независимость от регистра

Лишние пробелы

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

Различие в формате данных

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

— если в D5 буквы, а в таблице — цифры;

Приведение формата в самой формуле

Существует несколько вариаций, как изменить формат текста на цифру:

Возвести в степень: G2^1;
Двойное отрицание: —G2;
Прибавить ноль: G2+0;
Умножить на один: G2*1.

Если есть риск возникновения ошибки при вычислении формулы, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR), которая вернет определенное значение, в случае возникновения ошибки

Не зафиксирован массив

В примерах выше мы не использовали закрепление массива, т.к. всегда искали одно значение, т.е. не пользовались автозаполнением.

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

Фиксирование диапазона в ВПР

Относительный поиск

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

0 — 60: F
60 — 70: D
70 — 80: C
80 — 90: B
90 — 100: A

Вот так выглядит формула, если для ее построения использовать логическую функцию ЕСЛИ (да-да, с использованием ПЕРЕКЛЮЧ или ЕСЛИМН решение тоже будет лучше, но, допустим, у вас не самый свежий Excel, да и ВПР здесь выигрывает и у новых логических функций тоже):

Изящное решение с ВПР по поиску оценки

посмотрите на рисунке на формуле с ЕСЛИ и сравните с таковой ВПР и неточным совпадением, последняя выглядит значительно лучше:

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

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

ВПР для поиска максимального значения

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

Здравствуйте, друзья. Сегодня практикуем функцию ВПР Excel (на английском — VLOOKUP). Я покажу вам несколько примеров использования функции, которые многие мои ученики не использовали, а зря.

Если вы плохо представляете, как работает ВПР – сначала изучите эту статью, а потом возвращайтесь обратно!

Неточный поиск в Excel

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

0-200 у.е. – премии не будет
200-300 у.е. – 3% от продаж
300-500 у.е. – 5%
свыше 500 у.е. – 7%

Требуется для каждого сотрудника посчитать бонусы.

Мои ученики решили задачу с помощью множественного применения функции ЕСЛИ. Получилась гигантская формула: =ЕСЛИ(D3

Да, результат верный, но формула слишком большая и очень запутана. Как её сократить? Вспомним, что неточный поиск ВПР ищет ближайшую меньшую величину. Воспользуемся этим: =ВПР(D3;$H$3:$I$6;2;ИСТИНА)

Как это работает? Например, у Смирнова продажи – 205 у.е. ВПР ищет в таблице с бонусами ближайшее меньшее число, а это – 200. Функция вернет соответствующий этой строке бонус (3%).

ВПР по двум условиям в Excel

Рассмотрим на примере предыдущей таблицы. Заметили, что в ней — два Соколова? Первый – в Центральном районе, второй — в Южном. Как найти продажи второго? Точный поиск ВПР вернет первого, если искать по фамилии. Придется искать по двум параметрам – ФИО и району.

Для этого добавим дополнительный столбец D в таблице с исходными данными. С помощью функции СЦЕПИТЬ, объединим в ней имена и регионы. Вот так:

Теперь с помощью ВПР будем искать в массиве D3:D17 связку Фамилия+Регион:

Аналогично можно использовать три и более параметров.

Использование имен в формуле ВПР

Вернемся к формуле из предыдущего параграфа: = ВПР(I2&I3;$D$3:$E$17;2;0) . Она простая и короткая, но с ней сложно разобраться, если вы видите ее впервые. Простым решением будет использование информативных имён вместо ссылок.

Теперь та же самая формула выглядит так:

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

Динамическое изменение столбца для вывода

Очень интересный пример. Взгляните на картинку:

Здесь есть продажи за первое полугодие помесячно. Мы должны найти продажи, например, Богданова, за любой месяц. Чтобы изменять возвращаемый столбец – нужно каждый раз в формуле исправлять порядковый номер. Это неудобно, особенно когда формул несколько. Я решил эту проблему так:

В ячейке L3 организовал выпадающий список с перечнем месяцев. Можно обойтись и без выпадающего списка, а записывать месяца вручную. Но мой вариант удобнее
Получил номер столбца для выбранного месяца с помощью функции =ПОИСКПОЗ(месяц;B2:I2) . Такая формула вернет порядковый номер выбранного месяца в диапазоне шапки B2:I2
Вставил формулу из предыдущего пункта вместо номера выводимого столбца функции: =ВПР(имя;B2:I17;ПОИСКПОЗ(месяц;B2:I2);0) . Теперь если я изменю период, ПОИСКПОЗ пересчитает номер выводимого столбца и результат пересчитается.

Так можно менять номер выводимой колонки без исправления формулы. Очень удобно!

Подстановочные символы и ВПР

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

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

Обход ошибок при поиске ВПР

У нас есть две основные функции обхода таких ошибок:

ЕСЛИОШИБКА(выражение, значение_если_ошибка) – универсальная формула. Если в первом ее аргументе возвращается любая ошибка, возвращается второй аргумент
ЕНД(выражение) – проверяет выражение, и возвращает ИСТИНА, если там ошибка, или ЛОЖЬ, если все нормально. Эту функцию обычно применяют в составе других. Например, ЕСЛИ

Ускорение работы с помощью двойного ВПР

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

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

Попробуйте сами и убедитесь, что ВПР стал работать гораздо живее!

Это все хитрости, которые я хотел вам показать. Пробуйте и используйте, чтобы стать более эффективными в своей работе. До встречи!

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

Так было со мной. На моей первой официальной работе, мне приходилось раз в несколько дней сравнивать два списка. Списки были относительно небольшие, около 100 строк каждый. Но сравнивал я их при помощи ручки и линейки. Это было мучение, более того это было неэффективное мучение, которое каждый раз, занимало у меня (по меньшей мере) пол рабочего дня.

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

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

Сказать, что я был удивлён – это значит ничего не сказать. Я лицезрел настоящее чудо.

Это была потрясающая демонстрации силы автоматизации.

Функция ВПР в Экселе одинаково нужна и маркетологом, и логистам, и закупщикам – всем тем, кто работает с таблицами данных, это просто Must Have.

Функция ВПР в Экселе – быстрый перенос данных

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

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

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

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

Цены в прайсе указаны для примера и вряд ли имеют отношение к реальным ценам.

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

Однако это нас не страшит, во-первых, у нас есть ВПР, во-вторых мы и не такое видали.

Вот собственно и сам запрос:

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

Нам не хочется терять такого клиента и мы практически мгновенно открываем прайс:

Получается у нас должно быть открыто два файла (две книги в Эксель). Запрос от Петровича и Прайс.

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

Сразу же после этого, в строке формулы нужно поставить курсор внутри надписи ВПР и нажать Fx, перед вами появится окно с аргументами функции ВПР:

В аргументах функции вы говорите Экселю что и где нужно искать:

Теперь в аргументах функции заполните следующие поля:

Таблица — выделяете столбцы, которые содержат искомые наименования и цены, таким образом, чтобы наименования были крайним левым столбцом.

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

Интервальный просмотр — ставьте 0. Ноль обозначает точное соответствие.

Вам нужно протянуть цены на оставшиеся ячейки:

Коллеги, вот и всё, вы овладели функцией ВПР.

Очень важное замечание!

Обратите внимание на то, что сейчас мы работали в двух разных файлах (книгах).

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

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

Это позволяет не съезжать формуле когда вы протягиваете её вниз. Это очень актуально когда вы работаете в рамках одного листа или одной книги (в этом случае Эксель автоматически Не закрепляет ячейки).

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

Очень важное замечание №2

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

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

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

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

Это очень актуально для тех кто работает в закупках и отправляет заказы поставщику.

Обычно происходит следующая ситуация. Вы отправляете заказ поставщику, через некоторое время получаете ответ в виде счёта и сверяете заказ с счётом.

Всё ли есть в счёте, в нужном ли количестве, по правильным ли ценам и т.д.

Функция ВПР в Экселе – сравнение двух таблиц

Для удобства восприятия я разместил их на одном листе:

Ваша задача сверить количество позиций и их цены.

Для начала проверим все ли позиции и по правильной ли цене указал в счёте поставщик.

Для этого нужно из Счёта перетянуть данные в Заказ при помощи функции ВПР.

После добавления столбцов, нужно перетянуть соответствующие данные при помощи ВПР:

Обратите внимание, я закрепил диапазоны ячеек.

Теперь когда данные перенесены, нужно их сравнить, для это необходимо добавить еще два столбца (Разница 1 и Разница 2):

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

Тоже самое и с ценами, если значения в плюсе, значит нам сделали скидку, а если значения в минусе — значит нам пытаются продать по завышенной цене.

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

Друзья, вот так мы проверили насколько соответствует Заказ, полученному Счёту и казалось бы что это всё что необходимо для счастливой жизни. Однако это не совсем так.

Нужно еще проверить соответствие Счёта, отправленному заказу, на предмет лишних позиций.

Теперь всё тоже самое продемонстрирую в небольшом видео.

Эпилог

Полезность

Коллеги, если вы часто работаете в Эксель, то рекомендую прочитать еще парочку моих очень полезных статей по этой тематике, там будет (как всегда) только-то что необходимо в работе:

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

Рассмотрим такой учебный пример. У нас есть база данных с фамилиями, именами, отчествами сотрудников компании, а также годами их рождения. Нам необходимо по части сотрудников сделать сводку, где указать их фамилии и года рождения. Структурно лист с базой данных выглядит вот так:

Как вы видите, мы могли бы по фамилии вытащить год рождения при помощи функции ВПР, но год рождения стоит слева от столбца Фамилия, конечно можно поменять их местами, но в реальной жизни такое решение не всегда возможно, потому будет решать такую задачу. (Кстати, если вы еще не знакомы с функцией ВПР или неуверенно ею пользуетесь, ознакомьтесь со статьей на нашем сайте: "Функция ВПР в Excel"). Для ее решение нам будут нужны функции СМЕЩ и ПОИСКПОЗ. Разберем их синтаксис:

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

Смещение по строкам - указываем число, на какое количество строк надо сдвинуться. Положительное число - количество строк вниз, отрицательное число - количество строк вверх.

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

К примеру для таблицы в скриншоте сверху функция СМЕЩ(C3;-1;1) вернет значение Петрович. Почему именно так? В качестве стартовой ячейки мы указали ячейку С3 (в ней содержится имя Иван). Относительно этой ячейки мы сдвигаемся на одну строку вверх (второй параметр -1) и на один столбец вправо (третий параметр 1).

Искомое значение - это то значение, что мы ищем в таблице

Просматриваемый массив - таблица, где мы ищем искомое значение

Тип сопоставления - как будет искать Excel это значение. Используйте значение 0 для поиска точного результата.

К примеру для таблицы в скринщоте выше функция ПОИСКПОЗ("Петров";B2:B6;0) вернет значение 3. Мы ищем фамилию Петров в перечне фамилий. Она там третья по списку, поэтому формула вернула число 3.

Теперь давайте объединим наши знания и решим задачу по альтернативному ВПР в Экселе.

Итак, для нашего примера формула будет следующая:

Сначала мы используем формулу СМЕЩ, так как мы будет считать смещение относительно конкретной ячейки. Первый параметр - ссылка на заголовок столбца "Фамилия" на листе с базой данных, далее нам нужно узнать на сколько строк нам надо сдвинуться, для этого мы используем функцию ПОИСКПОЗ. В ней в качестве первого параметра указываем фамилию, которую ищем, вторым параметром наш столбец с фамилиями в листе с базой данных. Из полученного значения мы вычитаем 1, что бы убрать эффект заголовка столбца. Последний параметр - смещение на один столбец влево, т.е. -1.

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

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