Загальна формула
(=COLUMN(range)-COLUMN(range.firstcell)+1)
Резюме
Щоб отримати повний набір відносних номерів стовпців у діапазоні, ви можете використовувати формулу масиву на основі функції COLUMN.
У наведеному прикладі формула масиву в B4: H4 має вигляд:
(=COLUMN(B4:H4)-COLUMN(B4)+1)
На аркуші це потрібно ввести як формулу масиву з багато комірок, використовуючи Control + Shift + Enter
Це надійна формула, яка продовжуватиме генерувати відносні числа, навіть коли стовпці вставляються перед діапазоном.
Пояснення
Перша функція COLUMN генерує масив із 7 чисел, подібних до цього:
(2,3,4,5,6,7,8)
Друга функція COLUMN генерує масив лише з одним елементом, подібним до цього:
(2)
який потім віднімається з першого масиву, щоб отримати:
(0,1,2,3,4,5,6)
Нарешті, додається 1, щоб отримати:
(1,2,3,4,5,6,7)
З іменованим діапазоном
Ви можете адаптувати цю формулу для використання з іменованим діапазоном. Наприклад, у наведеному вище прикладі, якщо ви створили іменований діапазон "даних" для B4: H4, ви можете використовувати цю формулу для створення номерів стовпців:
(=COLUMN(data)-COLUMN(INDEX(data,1,1))+1)
Ви зустрінете цю формулу в інших формулах масивів, які потребують обробки даних по стовпцях.
З ПОСЛІДОВНІСТЮ
За допомогою функції SEQUENCE формула повернення відносних стовпців рядків для діапазону проста:
=SEQUENCE(COLUMNS(range))
Функція COLUMNS забезпечує підрахунок стовпців, який повертається до функції SEQUENCE. Потім SEQUENCE будує масив чисел, починаючи з числа 1. Отже, слідуючи оригінальному прикладу вище, формула нижче повертає той самий результат:
=SEQUENCE(COLUMNS(B4:H4)) // returns (1;2;3;4;5;6;7)
Примітка: Формула SEQUENCE - це нова функція динамічного масиву, доступна лише в Excel 365.
