У Кирка есть большая таблица данных в Excel. Каждая строка содержит номер автомобиля, дату (таблица отсортирована по этому столбцу), начальный и конечный пробег. Он хотел бы выполнить поиск в таблице данных в обратном направлении, чтобы найти конечный пробег для того же номера транспортного средства, который будет использоваться в качестве начального пробега в текущей строке — аналогично функции ВПР, но при просмотре снизу вверх, а не сверху вниз.

Есть несколько способов подойти к этому с помощью формулы. Предположим, для этого примера, что номер автомобиля находится в столбце A, дата в столбце B, начальный пробег в столбце C и конечный пробег в столбце D.

Вам нужна формула, которую вы можете поместить в столбец C, которая ищет последний конечный пробег для текущего автомобиля. Следующая формула обеспечивает один подход; поместите его в ячейку C3:

=LOOKUP(2,1/FIND(A3,A$2:A2,1),D$2:D2)

Вы можете скопировать формулу вниз по столбцу настолько, насколько вам нужно. Если номер автомобиля в столбце A ранее не появлялся в таблице данных, то формула вернет ошибку, например #VALUE! или # N / A. В этом случае вы можете легко ввести формулу с начальным пробегом, который вы хотите использовать для автомобиля.

Вот еще один шаблонный подход, но его следует вводить как формулу массива (нажав Ctrl + Shift + Enter):

=IF(A3="","",MAX(IF(($A$2:A2=A3)*($D$2:D2),$D$2:D2)))

Опять же, поместите формулу в ячейку C3 и скопируйте ее, насколько это необходимо.

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

=IF(A3="","",INDIRECT("D"&LARGE(($A$2:A3=A3)*ROW($2:3),2)))

Разница с этой формулой массива в том, что если транспортное средство не появилось ранее в таблице данных, она возвращает # ССЫЛКА! ошибка.

Вот две еще более короткие формулы массива, которые можно использовать в C3 (и, опять же, копировать при необходимости):

=MAX((D$2:D2)*(--(A$2:A2=A3)))

=MAX(IF(A$2:A2=A3,D$2:D2))

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (11745) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Looking_Backward_through_a_Data_Table [Взгляд назад через таблицу данных].