☰

Как заставить VLOOKUP вернуть *последнее* совпадение?

tag-icon • tag-icon microsoft-excel libreoffice-calc openoffice-calc vlookup lookup
Как заставить VLOOKUP вернуть *последнее* совпадение?

Я привык работать с функцией ВПР, но на этот раз у меня возникла проблема.Мне не нужно первое совпадающее значение, апоследний.Как? (Я работаю с LibreOffice Calc, но решение MS Excel должно быть столь же полезным.)

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

У меня есть (исключая обработку ошибок) обычная формула «первого совпадения»:

=VLOOKUP( 
[payee field] , [payee+category range] , [index of category column] , 
0 )

я виделрешениявот так, но я получаю #DIV/0!ошибки:

=LOOKUP(2 , 1/( [payee range] = [search value] ) , [category range] )

Решением может быть любая формула, не обязательно VLOOKUP. Я также могу поменять местами столбцы получателя/категории. Только не меняйте столбец сортировки, пожалуйста.

Бонусные баллы за решение, которое выбираетнаиболее частыйценность, а не последнее!

решение1

Вы можете использовать формулу массива для получения данных из последней совпавшей записи.

=INDEX(IF($A$1:$A$20="c",$B$1:$B$20),MAX(IF($A$1:$A$20="c",ROW($A$1:$A$20))))

Введите формулу, используя Ctrl+ Shift+ Enter.

Это работает как конструкция INDEX/ MATCHв a , но вместо нее используется VLOOKUPусловное выражение .MAXMATCH

Обратите внимание: здесь предполагается, что ваша таблица начинается со строки 1. Если ваши данные начинаются с другой строки, вам нужно будет скорректировать часть, ROW(...)вычтя разницу между верхней строкой и 1.

решение2

(Отвечаю здесь, так как нет отдельного вопроса для отсортированных данных.)

Если данныебылиsorted, можно использовать VLOOKUPс range_lookupаргументом TRUE(или опустить, так как он используется по умолчанию), который официально описан для Excel как «поиск приблизительного соответствия».

Другими словами, для отсортированных данных:

  • установка последнего аргумента FALSEвозвращаетпервыйценность и
  • установка последнего аргумента TRUEвозвращаетпоследнийценить.

Это в значительной степени недокументировано и неясно, но относится к VisiCalc (1979), и сегодня сохраняется по крайней мере в Microsoft Excel, LibreOffice Calc и Google Sheets. Это в конечном счете связано с первоначальной реализацией в LOOKUPVisiCalc (и отсюда VLOOKUPи HLOOKUP), когда не было четвертого параметра. Значение находится побинарный поиск, используя включающую левую границу и исключающую правую границу (распространенная и элегантная реализация), что приводит к такому поведению.

Технически это означает, что поиск начинается с интервала-кандидата [0, n), где n— длина массива, а условие инварианта цикла таково A[imin] <= key && key < A[imax](левая граница <= цели, правая граница, которая начинается на единицу после конца, > цели; для проверки проверьте значения в конечных точках до или проверьте результат после), и последовательное деление пополам и выбор той стороны, которая сохраняет этот инвариант: путем исключения одна сторона будет, пока вы не дойдете до интервала с 1 членом, , [k, k+1)и затем алгоритм возвращает k. Это не обязательно должно быть точное совпадение (!): это просто ближайшее совпадение снизу. В случае дублирующихся совпадений это приводит к возвратупоследнийсовпадение, так как требуется, чтобы следующее значение былобольшечем ключ (или конец массива). В случае дубликатов вам нужнонекоторыйповедение, и это разумно и легко реализовать.

Такое поведение явно указано в этой старой статье базы знаний Microsoft (выделено мной): «XL: Как вернуть первое или последнее совпадение в массиве» (Q214069):

Вы можете использовать функцию LOOKUP() для поиска значения в массиве отсортированных данных и возврата соответствующего значения, содержащегося в этой позиции в другом массиве. Если искомое значение повторяется в массиве,он возвращает последнее найденное совпадение. Такое поведение справедливо для функций VLOOKUP(), HLOOKUP() и LOOKUP().

Ниже приведена официальная документация для некоторых электронных таблиц; ни в одной из них не указано поведение «последнего совпадения», но оно подразумевается в документации Google Таблиц:

  • Майкрософт Эксель

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

  • Google Таблицы:

    Если is_sortedесть TRUEили опущено,ближайший матч(меньше или равнок ключу поиска) возвращается

решение3

Если значения в массиве поиска последовательны (т.е. вы ищете наибольшее значение, например, самую позднюю дату), вам даже не нужно использовать функцию INDIRECT. Попробуйте этот простой код:

=MAX(IF($A$1:$A$20="c",$B$1:$B$20,)

Снова введите формулу, используя CTRL + SHIFT + ENTER.

решение4

Я попробовал наиболее часто встречающееся значение. Не уверен, будет ли это работать в libreOffice, но, кажется, работает в excel

=ИНДЕКС($B$2:$B$9,ПОИСКПОЗ(МАКС(--($A$2:$A$9=D2)*СЧЁТЕСЛИМН($B$2:$B$9,$B$2:$B$9,$A$2:$A$9,D2)),--($A$2:$A$9=D2)*СЧЁТЕСЛИМН($B$2:$B$9,$B$2:$B$9,$A$2:$A$9,D2),0))

Столбец A будет получателем платежа, столбец B будет категорией, D2 — получатель платежа, по которому вы хотите фильтровать. Я не уверен, зачем он добавляет дополнительные переносы строк в функцию выше.

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

=ДВССЫЛ("B" & МАКС(--($A$2:$A$9=D2)*СТРОКА($A$2:$A$9)))

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

Обе эти функции необходимо вводить с помощьюCtrl+shift+enter

Связанный контент