11

Я привык работать с 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. Я также могу поменять местами столбцы получателя / категории. Только не меняйте колонку сортировки, пожалуйста.


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

6 ответов6

3

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

=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 VLOOKUP , но с условным MAX используемым вместо MATCH .

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

2

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

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

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

  • установка последнего аргумента в FALSE возвращает первое значение, и
  • установка последнего аргумента в TRUE возвращает последнее значение.

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

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

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

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

Официальная документация для некоторых таблиц приведена ниже; ни в одном из них не указано поведение "последнее совпадение", но оно подразумевается в документации Google Sheets:

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

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

  • Google Sheets:

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

1

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

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

Снова введите формулу, нажав CTRL + SHIFT + ВВОД.

0
=LOOKUP([payee field] , [payee range] , [category range])

Это даст вам последнее значение

Получу ли я бонусные баллы за опоздание на 3 года?

0

У меня была попытка в наиболее частом значении. Не уверен, что это будет работать в libreOffice, но, похоже, работает в Excel

= ИНДЕКС ($ B $ 2:$ B $ 9, MATCH (МАКС (- ($ A $ 2:$ A $ 9 = D2)* COUNTIFS ($ B $ 2:$ B $ 9, $ B $ 2:$ B $ 9, $ A $ 2:$ A $ 9, D2)), - ($ A $ 2:$ A $ 9 = D2)* COUNTIFS ($ B $ 2:$ B $ 9, $ B $ 2:$ B $ 9, $ A $ 2:$ A $ 9, D2, ), 0))

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

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

= НЕПРЯМОЙ ("B" & MAX (- ($ A $ 2:$ A $ 9 = D2)* ROW ($ A $ 2:$ A $ 9)))

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

Обе эти функции должны быть введены с помощью Ctrl+ Shift + Enter

-1

Вы получили #DIV/0! ошибки, потому что вы должны написать свою формулу, как:

=LOOKUP(2;IF(([payee range] = [search value]);1;"");[category range])

это будет работать и найдет последний матч.

([payee range] = [search value]) : логическая матрица ИСТИНА / ЛОЖЬ

IF(([payee range] = [search value]);1;"") : псевдобулева матрица 1/""

=LOOKUP(2; {pseudo-boolean matrix 1/""} );[category range]) : всегда возвращать последнюю 1 позицию

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .