Загальна формула
(=ROW(range)-ROW(range.firstcell)+1)
Резюме
Щоб отримати повний набір відносних номерів рядків у діапазоні, ви можете використовувати формулу масиву на основі функції ROW. У наведеному прикладі формула в B5: B11 має вигляд:
(=ROW(B5:B11)-ROW(B5)+1)
Примітка: це формула масиву, яку потрібно вводити за допомогою Control + Shift + Enter. Якщо ви вводите це на робочому аркуші (а не всередині іншої формули), зробіть вибір, який включає більше одного рядка, введіть формулу та підтвердьте за допомогою клавіш Control + Shift + Enter.
Ця формула буде продовжувати генерувати відносні числа, навіть коли діапазон переміщений. Однак це невдалий вибір, якщо рядки потрібно сортувати, видаляти або додавати, оскільки формула масиву запобіжить змінам. Описані тут варіанти формул будуть працювати краще.
Пояснення
Перша функція ROW генерує масив із 7 чисел, подібних до цього:
(5;6;7;8;9;10;11)
Друга функція ROW генерує масив лише з одним елементом, подібним до цього:
(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 формула повернення відносних номерів рядків для діапазону проста:
=SEQUENCE(ROWS(range))
Функція ROWS забезпечує підрахунок рядків, який повертається до функції SEQUENCE. Потім SEQUENCE будує масив чисел, починаючи з числа 1. Отже, слідуючи оригінальному прикладу вище, формула нижче повертає той самий результат:
=SEQUENCE(ROWS(B5:B11)) // returns (1;2;3;4;5;6;7)
Примітка: Формула SEQUENCE - це нова функція динамічного масиву, доступна лише в Excel 365.
