Общая формула
(=ROW(range)-ROW(range.firstcell)+1)
Резюме
Чтобы получить полный набор относительных номеров строк в диапазоне, вы можете использовать формулу массива, основанную на функции СТРОКА. В показанном примере формула в B5: B11:
(=ROW(B5:B11)-ROW(B5)+1)
Примечание: это формула массива, которую необходимо вводить с помощью Control + Shift + Enter. Если вы вводите это на листе (а не внутри другой формулы), сделайте выбор, который включает более одной строки, введите формулу и подтвердите, нажав Control + Shift + Enter.
Эта формула будет продолжать генерировать относительные числа даже при перемещении диапазона. Однако это не лучший выбор, если строки нужно отсортировать, удалить или добавить, потому что формула массива предотвратит изменения. Описанные здесь варианты формулы будут работать лучше.
Объяснение
Первая функция СТРОКА генерирует массив из 7 чисел, подобный этому:
(5;6;7;8;9;10;11)
Вторая функция СТРОКА генерирует массив только с одним элементом вроде этого:
(5)
который затем вычитается из первого массива, чтобы получить:
(0;1;2;3;4;5;6)
Наконец, добавляется 1, чтобы получить:
(1;2;3;4;5;6;7)
Общая версия с именованным диапазоном
С именованным диапазоном вы можете создать более общую версию формулы, используя функцию MIN или функцию INDEX. Например, с именованным диапазоном «список» вы можете использовать MIN следующим образом:
(ROW(list)-MIN(ROW(list))+1)
С помощью INDEX мы получаем первую ссылку в названном диапазоне и используем для этого ROW:
(=ROW(list)-ROW(INDEX(list,1,1))+1)
Такие формулы «относительных строк» часто встречаются внутри сложных формул массива, которым для вычисления результата нужны номера строк.
С ПОСЛЕДОВАТЕЛЬНОСТЬЮ
С функцией ПОСЛЕДОВАТЕЛЬНОСТЬ формула для возврата относительных номеров строк для диапазона проста:
=SEQUENCE(ROWS(range))
Функция ROWS обеспечивает количество строк, которое возвращается в функцию SEQUENCE. Затем SEQUENCE создает массив чисел, начиная с числа 1. Итак, следуя исходному примеру выше, формула ниже возвращает тот же результат:
=SEQUENCE(ROWS(B5:B11)) // returns (1;2;3;4;5;6;7)
Примечание. Формула ПОСЛЕДОВАТЕЛЬНОСТЬ - это новая функция динамического массива, доступная только в Excel 365.
