Fórmula genérica
(=ROW(INDIRECT(start&":"&end)))
Resumo
Para criar uma matriz de números como (1; 2; 3; 4; 5), você pode usar uma fórmula baseada nas funções ROW e INDIRECT. Essa técnica é mais frequentemente usada em fórmulas de matriz que precisam de uma matriz numérica para processamento de algum tipo. No exemplo mostrado, a fórmula em D5 é:
(=ROW(INDIRECT(B5&":"&C5)))
que retorna uma matriz como (1; 2; 3; 4; 5).
Observação: quando inserido em uma única célula, o Excel exibirá apenas o primeiro item da matriz. Use F9 na barra de fórmulas para ver o resultado real da matriz. Normalmente, você usará esta fórmula dentro de uma fórmula de matriz maior, inserida com control + shift + enter.
Explicação
Observação: no Excel 365, a nova função SEQUENCE é uma maneira melhor e mais fácil de criar uma matriz de números. O método explicado abaixo funcionará nas versões anteriores.
O núcleo desta fórmula é uma string que representa as linhas. Por exemplo, para criar uma matriz com 10 números, você pode codificar uma string em INDIRETO assim:
=ROW(INDIRECT("1:10"))
A função INDIRETO interpreta este texto para significar o intervalo 1:10 (10 linhas) e a função ROW retorna o número da linha para cada linha nesse intervalo dentro de uma matriz.
O exemplo mostrado usa uma versão mais genérica da fórmula que pega os números inicial e final de B5 e C5 respectivamente, então a solução se parece com esta:
=ROW(INDIRECT(B5&":"&C5)) =ROW(INDIRECT(1&":"&5)) =ROW(INDIRECT("1:5")) =ROW(1:5) =(1;2;3;4;5)
O motivo pelo qual INDIRETO é usado na fórmula é para evitar alterações na planilha. Sem INDIRETO, inserir ou excluir linhas pode alterar a referência do intervalo, por exemplo:
=ROW(1:5)
mudará para:
=ROW(1:4)
Se a linha 1 for excluída. Como INDIRETO trabalha com uma referência construída com texto, não é afetado por alterações na planilha.
Números relativos de linha em um intervalo
Se você precisar de uma matriz que consista nos números relativos das linhas de um intervalo, poderá usar uma fórmula como esta:
=ROW(range)-ROW(range.firstcell)+1
Veja esta página para uma explicação completa.
Valores negativos
A função ROW não lida com números negativos, então você não pode misturar números negativos no início e no fim . No entanto, você pode aplicar operações matemáticas ao array criado por ROW. Por exemplo, a seguinte fórmula criará esta matriz: (-5; -4; -3; -2; -1)
=ROW(INDIRECT(1&":"&5))-6
Números na ordem inversa, n para 1
Para criar uma matriz de números positivos em ordem decrescente, de n a 1, você pode usar uma fórmula como esta:
=ABS(ROW(INDIRECT("1:"&n))-(n+1))
