Обща формула
=MATCH(bigtext,range)
Обобщение
За да получите последната относителна позиция (т.е. последния ред, последната колона) за текстови данни (със или без празни клетки), можете да използвате функцията MATCH. В показания пример формулата в D5 е:
=MATCH(REPT("z",255),B4:B11)
Обяснение
Тази формула използва функцията MATCH в режим на приблизително съвпадение, за да намери последната текстова стойност в диапазон. Приблизителното съвпадение е активирано чрез задаване от 3-ия аргумент в MATCH на 1 или пропускане на този аргумент, който по подразбиране е 1.
Справочната стойност е така нареченият „голям текст“ (понякога съкратен „голям текст“), който умишлено е стойност „по-голяма“ от всяка стойност, която ще се появи в диапазона. Когато работите с текст, който сортира по азбучен ред, това означава текстова стойност, която винаги ще се появява в края на азбучния ред на сортиране.
Тъй като тази формула съвпада с текст, идеята е да се изгради справочна стойност, която никога няма да се появи в действителния текст, но винаги ще бъде последна. За целта използваме функцията REPT, за да повторим буквата "z" 255 пъти. Числото 255 представлява най-големия брой знаци, които MATCH позволява в справочна стойност.
Когато MATCH не може да намери тази стойност, тя ще "отстъпи" до последната текстова стойност в диапазона и ще върне позицията на тази стойност.
Забележка: този подход работи добре с празни клетки в диапазона, но не е надежден със смесени данни, които включват както числа, така и текст.
Последна относителна позиция спрямо номера на последния ред
Когато се изграждат разширени формули, които създават динамични диапазони, често е необходимо да се разбере последното местоположение на данните в списък. В зависимост от данните това може да е последният ред с данни, последната колона с данни или пресичането и на двете. Забележка: искаме последната относителна позиция в даден диапазон, а не номера на реда в работния лист:
Динамичен обхват
Можете да използвате тази формула, за да създадете динамичен диапазон с други функции като INDEX и OFFSET. Вижте връзките по-долу за примери и обяснение:
- Динамичен обхват с INDEX и COUNTA
- Динамичен обхват с OFFSET и COUNTA
Вдъхновение за тази статия дойде от отличната книга на Майк Гирвин Control + Shift + Enter, където Майк обяснява концепцията за „последна относителна позиция“.
