Общая формула
=ROW()-ROW(rng.firstcell)+1>ROWS(rng)-n
Резюме
Чтобы отметить последние n строк в диапазоне, вы можете использовать вспомогательный столбец с формулой, основанной на функциях ROW и ROWS. В показанном примере формула в ячейке E5, скопированная вниз, имеет следующий вид:
=ROW()-ROW(INDEX(data,1,1))+1>ROWS(data)-n
где данные (B5: E15) и n (G5) являются именованными диапазонами.
Эта формула возвращает ИСТИНА, если строка является "последней n строкой", и ЛОЖЬ, если нет.
Объяснение
Суть этой формулы основана на другой формуле, которая вычисляет «текущую строку» в диапазоне:
=ROW()-ROW(rng.firstcell)+1
Вкратце, мы получаем текущую строку в книге, а затем вычитаем номер первой строки диапазона плюс 1. В результате получается скорректированный номер строки, который начинается с 1. Функция ИНДЕКС - это просто один из способов получить первую ячейку в заданной ассортимент:
ROW(INDEX(data,1,1) // first cell
Это сделано только для удобства. Формулу можно переписать так:
=ROW()-ROW($E$5)+1
Когда у нас есть номер текущей строки, мы можем сравнить номер строки с общим количеством строк в данных за вычетом n:
current_row > ROWS(data)-n
Если это выражение возвращает TRUE, текущая строка является одной из последних n строк, которые мы ищем.
Последние n строк в таблице
Если данные находятся в таблице Excel, формулу можно изменить следующим образом:
=ROW()-@ROW(Table1)+1>ROWS(Table1)-n
Логика точно такая же, но мы используем немного другой подход, чтобы получить первую ячейку в таблице:
@ROW(Table1)
Вы можете думать о символе @ как о "одиночном":
=ROW(Table1) // returns (5;6;7;8;9;10;11;12;13;14;15) =@ROW(Table1) // returns (5)
Как и раньше, формула таблицы возвращает ИСТИНА в «последних n строках» и ЛОЖЬ в остальных.
Только последняя строка
Чтобы проверить только последнюю строку, вы можете использовать немного упрощенную версию формулы:
=ROW()-ROW(rng.first)+1=ROWS(rng)
