Как с помощью впр найти максимальное значение

Perform a Vlookup that returns the highest value, lowest value, or average value from a dataset.

Sections:

Vlookup to Return Max

Vlookup to Return Min

Vlookup to Return Average

Notes

Vlookup to Return Max

Return the max value from a table of data.

=VLOOKUP(MAX(A1:A5),A1:B5,2,FALSE)

Result:

This Vlookup function is exactly the same as the regular one except that the MAX() function is used for the lookup value argument.

The MAX() function returns the highest value from the list of numbers and then that value is used to perform the lookup. Otherwise, this is a standard Vlookup function.

Vlookup to Return Min

Return the min value from a table of data.

=VLOOKUP(MIN(A1:A5),A1:B5,2,FALSE)

Result:

The MIN() function is used in this example to find the smallest value from the list of numbers; then, that value is used to perform the lookup in the lookup_value argument. Otherwise, this is a standard Vlookup function.

Vlookup to Return Average

Return the average value from a table of data.

=VLOOKUP(AVERAGE(A1:A5),A1:B5,2,TRUE)

Result:

The AVERAGE() function is used in the lookup_value argument in order to find the average value from the list of numbers; then, that value, which is the average, is used to perform the lookup.

Note: in this example the last argument, range_lookup, is set to TRUE. This is because the average function can return a value that is not in the list of values and so you need the Vlookup function to find the next highest value. In this example, it doesn’t matter, but, if you want to check it out, change the 5 in row 5 to 12 and the TRUE value in cell D3 to FALSE and you will get an #N/A error.

Notes

Vlookups can be really helpful once you get used to using them and this is just one more way you can make them work for you to quickly return relevant and useful data.

Make sure to download the sample file for this tutorial so you can work with these examples in Excel.

Similar Content on TeachExcel

Subscribe for Weekly Tutorials

BONUS: subscribe now to download our Top Tutorials Ebook!

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

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

Синтаксис

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

Аргументы

Замечания

• При поиске в большом массиве, особенно, если на листе Excel много формул, у пользователя могут возникать проблемы с производительностью. Первое, что приходит на ум — это произвести апгрейд компьютера. Да, функция ВПР считается достаточно медленной, однако, мало кто знает, что ее скорость работы можно увеличить десятикратно, если использовать поставить аргумент равным ИСТИНА (или 1). Да, здесь есть нюанс в виде обязательной сортировки, поэтому такое ускорение будет работать не всегда. Кстати скорость увеличивается из-за сортировки, в этом случае нет необходимости сравнивать каждое значение из столбца.
• Функция ВПР осуществляет поиск всегда в самом первом (левом) столбце диапазона. Данное ограничение можно обойти, там где возможно, конечно, уменьшив аргумент таблица, чтобы искомый столбец стал самым первым
  
• При поиске текстовых значений в первом столбце аргумента таблица убедитесь, что данные в этом столбце таблицы не содержат начальных пробелов, конечных пробелов, используемых не по правилам прямых ( ‘ или » ) и «парных» ( ‘ или “ ) кавычек или непечатаемых символов. В этих случаях функция ВПР может вернуть неправильное или непредвиденное значение
  
• При поиске числовых значений или значений дат убедитесь, что данные в первом столбце аргумента таблица не являются текстовыми значениями. В этом случае функция ВПР может вернуть неправильное или непредвиденное значение
• Если значение аргумента «интервальный_просмотр» — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, поставьте перед ними знак тильды (~)
  

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Все можно исправить посредством приведения параметров в одинаковый формат. К примеру:

=ВПР(−−G2; A3:D9; 4; 0)

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

=ВПР(G2 & «»); A3:D9; 4; 0)

— и иначе.

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

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

Прекращение проявления ошибки #Н/Д

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

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

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

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

Массив можно оформить как умную таблицу. Команда «Форматировать как таблицу» из вкладки «Главная», либо горячее сочетание Ctrl+T. В этом случае ссылки на такой диапазон будут автоматически становиться абсолютными. Но при работе с такими таблицами в Excel есть свои нюансы и, не всегда это подойдет

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

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

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

=ВПР(E1;A2:B6;2;1)

Пример

Видео работы функции

Дополнительные материалы

Как сравнить два файла MS Excel
#Excelwordlchamp 1-1 Решаем задачи первого тура

ВПР на максималках

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

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

Итак, что мы имеем:

Таблица раз — со списком значений, который надо обогатить данными

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

Обычная задача для ВПР или ИНДЕКС звучит, как «мне надо добавить данные из одной таблицы в другую по какому-то критерию или критериям», после чего берем критерий первой таблицы и начинаем искать по нему первое совпадающее значение из другой таблицы и возвращаем значение из искомого столбца:

=ВПР(A2;Лист2!$C$1:$C$170;2;0), где A2 - критерий, Лист2!$C$1:$I$13 - диапазон в котором ищем, 2 - номер столбца, из которого  возвращаем значение, 0 - тип сопоставлениятип 

или вот так:

=ИНДЕКС(Лист2!$B$1:$I$170;ПОИСКПОЗ(Лист1!A3;Лист2!$C$1:$C$170;0);3), где - Лист2!$B$1:$I$170 - диапазон, в кокотором ищем, ПОИСКПОЗ(Лист1!A3;Лист2!$C$1:$C$170;0) - строка, которую ищем, 3 - столбец, откуда возвращаем данные,  

В целом двух этих формул хватит для 90% ситуаций при работе с excel, чтобы подтянуть данные из другой таблицы. Хватит ровно до того момента, пока к нам не присоединится условие с датой, при этом дата будет неизвестной переменной.

Собственно сама задача:

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

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

В итоге с помощью того Googlа и небольшой доработки напильником была рождена формула следующего типа:

{=ИНДЕКС(Лист2!$B$2:$I$170;ПОИСКПОЗ(A4&МАКС(ЕСЛИ(A4=Лист2!$C$2:$C$170;Лист2!$B$2:$B$170;""));Лист2!$C$2:$C$170&Лист2!$B$2:$B$170;);ПОИСКПОЗ("Статус";Лист2!$B$1:$I$1;0))}

Вот он — ВПР на максималках) тут использовано все, чтобы вернуть ту искомую запись в таблице: и массивы, и индекс, и несколько критериев, и условие по дате.
Как это работает:

1. Используем в формуле ИНДЕКС массивы. При вводе формулы используем сочетание клавиш ctrl+shift+enter

2. Выделяем просматриваемый диапазон, в моем случае от B2 до I170

3. Искомую строку определяем по формуле ПОИСКПОЗ, при этом поиск осуществляем по двум условиям A4&МАКС (идентификатор A4 исходной таблицы и максимальное значение даты при равном идентификаторе, значение даты берется из функции ЕСЛИ). Тут важно не забыть, что поиск по нескольким критериям можно задать через & перечислив критерии, а также надо через & перечислить диапазоны, в которых excel будет искать эти критерии, в той же последовательности, что и критерии

4. Искомый столбец оформил тоже через формулу ПОИСКПОЗ, где формула ищет позицию искомого значения «Статус» в просматриваемом диапазоне заголовков второй таблицы и возвращает таким образом номер столбца, из которого нам надо вытащить значение

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

Зачем этот пост?

Хотел поделиться в сети информацией для будущих искателей решения схожей проблемы, так как мой ТОП операций и функций excel ctrl+c и ctrl+v Но мне не подвернулось готового решения, когда я искал. Может кому-то повезет больше с моей помощью.