Обща формула
(=MATCH(2,1/(range"")))
Обобщение
За да получите последната относителна позиция (т.е. последния ред, последната колона) за смесени данни, които могат да съдържат празни клетки, можете да използвате функцията MATCH, както е описано по-долу.
Забележка: това е формула на масив и трябва да се въведе с Control + Shift + Enter.
В показания пример формулата в E5 е:
(=MATCH(2,1/(B4:B10"")))
Последна * относителна * позиция, не ред на работен лист
Когато се конструират по-усъвършенствани формули, често е необходимо да се разбере последното местоположение на данните в списък. В зависимост от данните това може да е последният ред с данни, последната колона с данни или пресичането и на двете. Искаме последната * относителна позиция * в даден диапазон, а не номера на реда в работния лист:
Обяснение
Тази формула използва функцията MATCH, конфигурирана да намери позицията на последната непразна клетка в диапазон.
Работейки отвътре навън, търсещият масив вътре в MATCH е изграден по следния начин:
=1/(B4:B10"")) =1/(TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE) =(1;#DIV/0!;1;#DIV/0!;1;1;#DIV/0!)
Забележка: всички стойности в масива са или 1, или # DIV / 0! грешка.
След това MATCH се настройва да съответства на стойността 2 в "режим на приблизително съвпадение", като се пропусне 3-тият аргумент.
Тъй като справочната стойност 2 никога няма да бъде намерена, MATCH винаги ще намери последната 1 в масива за търсене, която съответства на последната непразна клетка.
Този подход ще работи с всякакъв вид данни, включително числа, текст, дати и т.н. Работи и с нулеви текстови низове, които се връщат от формули като тази:
=IF(A1<100,"")
Динамичен обхват
Можете да използвате тази формула, за да създадете динамичен диапазон с други функции като INDEX и OFFSET. Вижте връзките по-долу за примери и обяснение:
- Динамичен обхват с INDEX и COUNTA
- Динамичен обхват с OFFSET и COUNTA
Вдъхновение за тази статия дойде от отличната книга на Майк Гирвин Control + Shift + Enter, където Майк върши страхотна работа, обяснявайки концепцията за „последна относителна позиция“.
