Общая формула
(=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(COLUMNS(range))
Функция COLUMNS обеспечивает количество столбцов, которое возвращается в функцию SEQUENCE. Затем SEQUENCE создает массив чисел, начиная с числа 1. Итак, следуя исходному примеру выше, формула ниже возвращает тот же результат:
=SEQUENCE(COLUMNS(B4:H4)) // returns (1;2;3;4;5;6;7)
Примечание. Формула ПОСЛЕДОВАТЕЛЬНОСТЬ - это новая функция динамического массива, доступная только в Excel 365.
