Обща формула
=MATCH(bignum,range)
Обобщение
За да получите последната относителна позиция (т.е. последния ред, последната колона) за числови данни (със или без празни клетки), можете да използвате функцията MATCH с така нареченото "голямо число".
В показания пример формулата в E5 е:
=MATCH(9.99E+307,B4:B9)
Последна * относителна * позиция, не ред на работен лист
Когато се изграждат разширени формули, които създават динамични диапазони, често е необходимо да се разбере последното местоположение на данните в списък. В зависимост от данните това може да е последният ред с данни, последната колона с данни или пресичането и на двете. Забележка: искаме последната * относителна позиция * в даден диапазон, а не номера на реда в работния лист:
Обяснение
Тази формула използва функцията MATCH в режим на приблизително съвпадение, за да намери последната числова стойност в диапазон. Приблизителното съвпадение е активирано чрез задаване от 3-ия аргумент в MATCH на 1 или пропускане на този аргумент, който по подразбиране е 1.
Справочната стойност е така нареченото „голямо число“ (понякога съкратено „bignum“), което умишлено е по-голямо от всяка стойност, която ще се появи в диапазона.
Резултатът е, че MATCH ще "отстъпи" до последната числова стойност в диапазона и ще върне тази позиция.
Забележка: този подход работи добре с празни клетки в диапазона, но не е надежден със смесени данни, които включват както числа, така и текст.
Относно bignum
Най-големият номер, който Excel може да обработи, е 9.99999999999999E + 307.
Когато използвате MATCH по този начин, можете да използвате всяко голямо число, което е гарантирано по-голямо от всяка стойност в диапазона, например:
=MATCH(1E+06,range) // 1 million =MATCH(1E+09,range) // 1 billion =MATCH(1E+12,range) // 1 trillion
Предимството при използването на 9.99E + 307 или подобно е, че това е (1) огромен брой и (2) разпознаваем като заместител за „голямо число“. Ще го видите да се използва в различни усъвършенствани формули на Excel.
Динамичен обхват
Можете да използвате тази формула, за да създадете динамичен диапазон с други функции като INDEX и OFFSET. Вижте връзките по-долу за примери и обяснение:
- Динамичен обхват с INDEX и COUNTA
- Динамичен обхват с OFFSET и COUNTA
Вдъхновение за тази статия дойде от отличната книга на Майк Гирвин Control + Shift + Enter, където Майк обяснява концепцията за „последна относителна позиция“.
