Общая формула
(=ROW(INDIRECT(start&":"&end)))
Резюме
Чтобы создать массив чисел вроде (1; 2; 3; 4; 5), вы можете использовать формулу, основанную на функциях СТРОКА и ДВССЫЛ. Этот метод чаще всего используется в формулах массива, которым для какой-либо обработки нужен числовой массив. В показанном примере формула в D5:
(=ROW(INDIRECT(B5&":"&C5)))
который возвращает массив типа (1; 2; 3; 4; 5).
Примечание: при вводе в одну ячейку Excel отобразит только первый элемент в массиве. Используйте F9 в строке формул, чтобы увидеть фактический результат массива. Обычно вы будете использовать эту формулу в формуле большего массива, введенной с помощью Ctrl + Shift + Enter.
Объяснение
Примечание. В Excel 365 новая функция ПОСЛЕДОВАТЕЛЬНОСТЬ - лучший и простой способ создать массив чисел. Метод, описанный ниже, будет работать в предыдущих версиях.
Ядром этой формулы является строка, представляющая строки. Например, чтобы создать массив из 10 чисел, вы можете жестко закодировать строку в INDIRECT следующим образом:
=ROW(INDIRECT("1:10"))
Функция ДВССЫЛ интерпретирует этот текст как обозначение диапазона 1:10 (10 строк), а функция СТРОКА возвращает номер строки для каждой строки в этом диапазоне внутри массива.
В показанном примере используется более общая версия формулы, которая берет начальные и конечные числа из B5 и C5 соответственно, поэтому решение выглядит следующим образом:
=ROW(INDIRECT(B5&":"&C5)) =ROW(INDIRECT(1&":"&5)) =ROW(INDIRECT("1:5")) =ROW(1:5) =(1;2;3;4;5)
Причина использования INDIRECT в формуле - защита от изменений рабочего листа. Без INDIRECT вставка или удаление строк может изменить ссылку на диапазон, например:
=ROW(1:5)
изменится на:
=ROW(1:4)
Если строка 1 удалена. Поскольку INDIRECT работает со ссылкой, созданной с помощью текста, на нее не влияют изменения на листе.
Относительные номера строк в диапазоне
Если вам нужен массив, состоящий из относительных номеров строк диапазона, вы можете использовать следующую формулу:
=ROW(range)-ROW(range.firstcell)+1
См. Эту страницу для полного объяснения.
Отрицательные значения
Функция СТРОКА не обрабатывает отрицательные числа, поэтому вы не можете смешивать отрицательные числа для начала и конца . Однако вы можете применять математические операции к массиву, созданному ROW. Например, следующая формула создаст этот массив: (-5; -4; -3; -2; -1)
=ROW(INDIRECT(1&":"&5))-6
Числа в обратном порядке, от n до 1
Чтобы создать массив положительных чисел в порядке убывания от n до 1, вы можете использовать следующую формулу:
=ABS(ROW(INDIRECT("1:"&n))-(n+1))
