Можно ли использовать Индекс и Сопоставление или что-то подобное в Microsoft Excel, чтобы найти последнюю заполненную числовую строку для двух наборов чисел в одном столбце?

Question

Пример изображения таблицы:

Цель: В образце таблицы я хочу извлечь из столбца C последний заполненный номер CST как 1235 и последний заполненный номер НДС как 204.

Если я использую =VLOOKUP("CST",B2:C5,2,FALSE), то он возвращает первое значение CST, то есть 1234. Аналогично для НДС использование VLOOKUP возвращает 203.

Если я попробую Match, то для CST все работает нормально, но для НДС та же формула с меньшим значением lookup_value, скажем, 250 (поскольку значения НДС ниже 250), даст в качестве результата #N/A.

Скриншот (использование MATCH с разными искомыми значениями в одном столбце):

Answer 1

Для чего-то подобного я предпочитаю AGGRAGATE вместо MATCH:

=INDEX($B:$B,AGGREGATE(14,6,ROW($A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A)))/($A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A))=D$1),1))

Я поместил фактические критерии в D1 и D2, чтобы можно было ссылаться на них напрямую, а не жестко кодировать "CST"и "VAT"прописывать в формуле, сделав ее перетаскиваемой.

Два $A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A))динамически задают диапазон ссылок, поскольку это формула типа массива. Он задается от A2 до последней ячейки в столбце A, которая имеет текстовую строку.

Агрегат вернет наибольший номер строки (последнюю строку), которая соответствует критериям ИНДЕКСА.

Answer 2

Вы можете сделать это с помощью LOOKUP.

LOOKUP Если lookup_valueбольше любого числа в массиве, функция возвращает последнее число в массиве; конструкция 1/(1/(...))преобразует 0' в ошибки, поэтому последним «числом» будет значение в той же позиции, что и последнее, CSTили VATв зависимости от формулы.

Last CST:  =LOOKUP(9E+307,1/(1/(($B$2:$B$999="CST")*$C$2:$C$999)))
Last VAT:  =LOOKUP(9E+307,1/(1/(($B$2:$B$999="VAT")*$C$2:$C$999)))

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

Last CST:  =LOOKUP(2,1/(($B$2:$B$999="CST")*ROW($B$2:$B$999)),$C$2:$C$999)
Last VAT:  =LOOKUP(2,1/(($B$2:$B$999="VAT")*ROW($B$2:$B$999)),$C$2:$C$999)

Answer 3

Используйте VLOOKUP для вертикального поиска. Второй аргумент — матрица. Если нет ограничений по строкам, не указывайте номера строк, только буквы столбцов. Третий аргумент указывает, какой столбец использовать. 2 означает второй, т. е. C в вашей матрице.

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

Следовательно, используйте

=VLOOKUP("CST", B:C, 2)

и

=VLOOKUP("VAT", B:C, 2)

Обратите внимание, что для горизонтального поиска также существует функция HLOOKUP.

Answer 4

В тот день я перевернул таблицы вверх дном, так сказать, а затем использовал VLOOKUP(), когда это было возможно, а INDEX/MATCHкогда нет. Важная часть — перевернуть таблицу вверх дном, и ниже показано, как это сделать. Используйте это как таблицу для поиска того, что вы хотите найти, и когда он находит первое вхождение, он находит «реальное» последнее вхождение, поскольку оно находится в исходной («реальной») таблице.

Итак, таблица значений A1:B22. Тогда:

=INDEX(A1:B22,  ROWS(A1:A22)+1-ROW(INDIRECT("1:"&ROWS(A1:A22))),  {1,2})

По сути, он находит количество строк (22), добавляет 1 (23), затем создает массив значений, которые представляют собой это значение (23) минус ряд значений от 1 до количества строк (22), давая массив строк для возврата: 23-1=22, 23-2=21, 23-3=20 и т. д., то есть 22, 21, 20 и так далее до 1. Таким образом, INDEX()строки возвращаются в обратном порядке.

В настоящее время (2022) мы бы просто использовали XLOOKUP()и искали от конца к началу. Конечно, если делать вышесказанное, можно было бы использовать SEQUENCE()для генерации числовых последовательностей.