Fórmula genérica
(=ROW(INDIRECT(start&":"&end)))
Resumen
Para crear una matriz de números como (1; 2; 3; 4; 5) puede usar una fórmula basada en las funciones FILA e INDIRECTO. Esta técnica se usa con mayor frecuencia en fórmulas de matriz que necesitan una matriz numérica para el procesamiento de algún tipo. En el ejemplo que se muestra, la fórmula en D5 es:
(=ROW(INDIRECT(B5&":"&C5)))
que devuelve una matriz como (1; 2; 3; 4; 5).
Nota: cuando se ingresa en una sola celda, Excel mostrará solo el primer elemento de la matriz. Use F9 en la barra de fórmulas para ver el resultado real de la matriz. Normalmente, usará esta fórmula dentro de una fórmula de matriz más grande, ingresada con control + shift + enter.
Explicación
Nota: En Excel 365, la nueva función SECUENCIA es una forma mejor y más fácil de crear una matriz de números. El método que se explica a continuación funcionará en versiones anteriores.
El núcleo de esta fórmula es una cadena que representa filas. Por ejemplo, para crear una matriz con 10 números, puede codificar una cadena en INDIRECTO como este:
=ROW(INDIRECT("1:10"))
La función INDIRECTO interpreta este texto en el rango 1:10 (10 filas) y la función FILA devuelve el número de fila para cada fila en ese rango dentro de una matriz.
El ejemplo que se muestra usa una versión más genérica de la fórmula que recoge los números de inicio y final de B5 y C5 respectivamente, por lo que la solución se ve así:
=ROW(INDIRECT(B5&":"&C5)) =ROW(INDIRECT(1&":"&5)) =ROW(INDIRECT("1:5")) =ROW(1:5) =(1;2;3;4;5)
La razón por la que INDIRECTO se usa en la fórmula es para protegerse contra cambios en la hoja de trabajo. Sin INDIRECTO, insertar o eliminar filas puede cambiar la referencia del rango, por ejemplo:
=ROW(1:5)
cambiará a:
=ROW(1:4)
Si se elimina la fila 1. Dado que INDIRECTO trabaja con una referencia construida con texto, no se ve afectado por cambios en la hoja de trabajo.
Números de fila relativos en un rango
Si necesita una matriz que consta de los números de fila relativos de un rango, puede usar una fórmula como esta:
=ROW(range)-ROW(range.firstcell)+1
Consulte esta página para obtener una explicación completa.
Valores negativos
La función FILA no manejará números negativos, por lo que no puede mezclar números negativos al principio y al final . Sin embargo, puede aplicar operaciones matemáticas a la matriz creada por ROW. Por ejemplo, la siguiente fórmula creará esta matriz: (-5; -4; -3; -2; -1)
=ROW(INDIRECT(1&":"&5))-6
Números en orden inverso, n a 1
Para crear una matriz de números positivos en orden descendente, de n a 1, puede usar una fórmula como esta:
=ABS(ROW(INDIRECT("1:"&n))-(n+1))
