Обща формула
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
Обобщение
За да получите позицията на последното съвпадение (т.е. последното появяване) на справочна стойност, можете да използвате формула на масив, базирана на функциите IF, ROW, INDEX, MATCH и MAX. В показания пример формулата в H6 е:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Където "names" е наименуваният диапазон C4: C11.
Забележка: това е формула на масив и трябва да се въведе с control + shift + enter.
Обяснение
Същността на тази формула е, че изграждаме списък с номера на редове за даден диапазон, съвпадащи по стойност и след това използваме функцията MAX, за да получим най-големия номер на ред, който съответства на последната съответстваща стойност. Използваме имената на имената на диапазона само за удобство.
Работейки отвътре навън, тази част от формулата ще генерира относителен набор от номера на редове:
ROW(names)-MIN(ROW(names))+1
Резултатът от горния израз е масив от числа като този:
(1;2;3;4;5;6;7;8)
Забележете, че получаваме 8 числа, съответстващи на 8 реда в таблицата. Вижте тази страница за подробности как работи тази част от формулата.
За целите на тази формула искаме само номера на редове за съвпадащи стойности, така че използваме функцията IF, за да филтрираме стойностите по следния начин:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
Това води до масив, който изглежда така:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Обърнете внимание, че този масив все още съдържа осем елемента. Обаче са оцелели само номера на редове, където стойността в посочения диапазон "имена" е равна на "amy" (т.е. 1, 4, 7). Всички останали елементи в масива са FALSE, тъй като не са успели при логическия тест във функцията IF.
И накрая, функцията IF доставя този масив на функцията MAX. MAX връща най-високата стойност в масива, числото 7, което съответства на номера на последния ред, където името е "amy". След като знаем последния съвпадащ номер на ред, можем да използваме INDEX, за да извлечем стойност на тази позиция.
Последен до последен и т.н.
За да получите втората до последната позиция, третата до последната и т.н., можете да превключите от функция MIN към функция LARGE по следния начин:
(=LARGE(IF(criteria,ROW(rng)-MIN(ROW(rng))+1),k))
където k представлява "n-ти по големина". Например, за да получите последното съвпадение в горния пример, можете да използвате:
(=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2))
Както и преди, това е формула на масив и трябва да се въведе с control + shift + enter.
