La función SECUENCIA

La función SECUENCIA nos permite generar una lista de números secuenciales en una matriz, como 1, 2, 3 y 4.

Sintaxis:

=SECUENCIA (rows; [columns];[start];[step])

l Rows. Argumento obligatorio. Numero de filas a devolver.

l Columns. Argumento opcional. Numero de columnas a devolver.

l Star. Argumento opcional. El primer número de la secuencia.

l Step. Argumento opcional. Cantidad que se incrementa a cada valor de forma consecutiva.

Los argumentos opcionales si faltan tendrán un valor predeterminado de 1.

Una matriz puede considerarse como una fila de valores, una columna de valores o una combinación de filas y columnas de valores.

La función SECUENCIA devolverá una matriz, que se desbordará si es el resultado final de una fórmula. Esto significa que Excel creará dinámicamente el rango de matriz del tamaño adecuado al presionar ENTRAR.

Si los datos de soporte están en una Tabla de Excel, la matriz cambiará de tamaño automáticamente al agregar o eliminar datos del rango de la matriz si usa Referencias estructuradas.

Cuando se crea una tabla de Excel, Excel asigna un nombre a la tabla y a cada encabezado de columna de la tabla. Cuando se agregan fórmulas a una tabla de Excel, estos nombres pueden aparecer automáticamente a medida que se escribe la fórmula y se seleccionan las referencias de celda en la tabla en lugar de especificarlas manualmente.

Dicha combinación de nombres de tabla y columna se denomina una referencia estructurada.

En el ejemplo siguiente, se crea una matriz que tiene 3 filas de alto por 4 columnas de ancho, para ello, como argumento filas, ponemos 3, y, como argumento columnas, ponemos 4.

=SECUENCIA (3;4)

Si necesitamos crear un conjunto de datos de ejemplo rápido, este es un ejemplo que usa SECUENCIA con TEXTO, FECHA, AÑO y HOY para crear una lista dinámica de meses para una fila de encabezado, donde la fecha subyacente siempre será el año actual.

Sabemos que la función TEXTO, tiene dos argumentos, que es valor, y, formato, queremos generar los nombres de los seis primeros meses, por eso, debemos de usar la función TEXTO.

Tenemos otra función llamada FECHA, a la cual, dando el año, el mes, y, el día, nos lo convierte en formato de fecha.

Vamos, primero, a construir la función FECHA, lo hare en la celda B6, que es donde queremos poner los nombres de los meses.

Escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.

=FECHA(

El primer argumento es año, queremos el año actual, pues usamos la función AÑO junto con la función HOY, que nos devuelve la fecha actual.

=FECHA(AÑO(HOY());

El siguiente argumento es mes, ponemos, de momento, 1.

=FECHA(AÑO(HOY());1;

El ultimo argumento es día, ponemos, también, 1.

Cerramos paréntesis, y, aceptamos.

=FECHA(AÑO(HOY());1;1)

Pues, tenemos la fecha 1 de enero del año 2022.

Pero, queremos los seis primeros meses del año, el argumento mes de la función FECHA, lo podemos convertir en matricial, es decir, indicarle que nos devuelva una matriz desbordada desde 1 al 6.

Por ejemplo, si en una celda, escribo la siguiente expresión:

=SECUENCIA(1;6)

Me va a devolver una matriz desbordada, de una fila, y, seis columnas, empezando desde el numero 1 hasta el número 6.

Pues, vamos a sustituir el argumento mes de la función FECHA, por la función SECUENCIA, a ver que nos devuelve.

=FECHA(AÑO(HOY());SECUENCIA(1;6);1)

Pues, nos ha devuelto una matriz desbordada, y, cada fecha es el día 1 de enero del 2022, hasta el 1 de junio del 2022.

Vamos a ver otro ejemplo donde vamos a anidar la función SECUENCIA con ALEATORIO.ENTRE para crear una matriz de 5 filas por 6 columnas con un conjunto aleatorio de enteros en aumento.

Vamos a crear números aleatorios, entre 100, y, 500.

La fórmula es:

=SECUENCIA (5;6; ALEATORIO.ENTRE(100;500); ALEATORIO.ENTRE(100;500))

Si desmenuzamos la formula, y, vamos haciendo una a una, para poder entenderla, seria de la siguiente manera:

Primero, SECUENCIA (5;6), que nos crea una matriz de 5 filas de alto, y, 6 filas de ancho.

Estamos usando el primer y segundo argumento.

Ahora, usaremos el tercer argumento, que es Star, es decir, primer número de la secuencia.

Si modifico la formula anterior, y, añado como tercer argumento el número 10, quiere decir que la secuencia debe de empezar por el número 10.

=SECUENCIA (5;6;10)

Pero, vamos a sustituir ese tercer argumento, por la función ALEATORIO.ENTRE, que generara números aleatorios entre 100 y 500, la formula quedaría:

=SECUENCIA (5;6; ALEATORIO.ENTRE(100;500))

El cuarto argumento es Step, es decir, cantidad que se incrementa con cada valor, si volvemos a la función =SECUENCIA (5;6;10), y, como cuarto argumento ponemos, por ejemplo, 2, =SECUENCIA (5;6;10;2), la matriz derramada quedaría:

Vemos que después del 10, viene 12, después viene 14, y, así sucesivamente, es decir, que va incrementando el valor de 2 en 2, pero en nuestro caso, vamos a volver a usar la función ALEATORIO.ENTRE(100;500), para que el número que sume sea un numero aleatorio entre 100 y 500, la formula quedaría:

=SECUENCIA (5;6; ALEATORIO.ENTRE(100;500); ALEATORIO.ENTRE(100;500))

Pues, de esta manera, salen los números.

Lo siguiente es crear una matriz, en horizontal, pondremos los seis primeros meses, que ya tenemos la formula, y, en vertical, a partir del día actual, pondremos los días de la semana.

Para los meses, la formula será la misma.

=TEXTO(FECHA(AÑO(HOY()); SECUENCIA(1;6);1);"mmmm")

Para los días, vamos a usar la expresión anterior, pero debemos de hacer algunos cambios.

El primer cambio, va a ser el argumento mes, en la expresión anterior, usamos SECUENCIA, pues lo sustituimos, y, que nos devuelva el mes actual, y, en el argumento día, pusimos 1, pues es aquí, donde vamos a usar la función SECUENCIA, nos tiene que crear una matriz, en este caso, en vertical, que debe de ser de 7, que son los días de la semana, y, como argumento columna, ponemos 1.

=TEXTO(FECHA(AÑO(HOY()); MES(HOY());SECUENCIA(7;1));"dddd")

Aceptamos.

La matriz derramada quedaría:

Antes hemos usado la siguiente expresión, para crear una matriz de cinco filas de alto, y, 6 columnas de ancho.

SECUENCIA (5;6; ALEATORIO.ENTRE(100;500); ALEATORIO.ENTRE(100;500))

Pero, como hemos añadido los días de la semana, vamos a cambiar el argumento filas de 5 a 7, para tener datos para todos los días de la semana.

=SECUENCIA(7;6;ALEATORIO.ENTRE(100;500);ALEATORIO.ENTRE(100;500))

Vamos a calcular el total para cada mes, para cada dia de la semana, y, el total general, para ello, seleccionamos los valores.

Pulsamos ALT más igual, que es el atajo de auto suma, y, ya los tenemos.

Lo siguiente es calcular el porcentaje de cada día respecto al total de todos los días, para ello, debemos de dividir cada total del día de la semana, entre la suma de todos los totales, y, fijamos el rango de suma.

=H7/SUMA($H$7:$H$13)

Arrastramos, y, ya tenemos la diferencia porcentual de cada día respecto al total de todos los días.

Por último, podemos añadirle un gráfico de columnas, pero que quede de otro color la columna de mayor valor.

Para ello, en la celda siguiente a la del primer porcentaje, vamos a usar el condicional SI, para preguntar que si el valor máximo, donde usamos la función MAX, del primer porcentaje es igual a dicho porcentaje, pues que lo ponga, en caso contrario, debe de poner un texto en blanco.

=SI(MAX(I7:I13)=I7;I7;"")

Fijamos el rango dentro de la función MAX, para que al copiar no se actualice.

=SI(MAX($I$7:$I$13)=I7;I7;"")

Arrastramos.

Podemos ver resaltado el porcentaje mayor.

Seleccionamos los porcentajes, hacemos clic en insertar, y, seleccionamos grafico de columnas 2D.

Eliminamos el título, y el eje horizontal.

Seleccionamos el grafico, vamos a la pestaña diseño de gráfico, y, hacemos clic en seleccionar datos, se abre la siguiente ventana.

Clic en agregar.

En valores de la serie, seleccionamos los valores de la formula creada.

Aceptamos.

Vemos como se ha añadido una nueva columna, al lado de la columna de mayor valor.

Hacemos clic con el botón alternativo del ratón, sobre la columna de color naranja, y, seleccionamos dar formato a serie de datos.

En superposición de series, que es la distancia que hay entre una columna y otra, ponemos el 100%, que quiere decir que la columna naranja se ponga encima de la azul.

Y, vemos, como queda de otro color la columna de mayor valor.

Si, ahora, añadimos un valor, veremos que la columna de color naranja cambia.

Con esto damos por terminada esta clase.

Para el siguiente ejemplo, queremos calcular cuantos días de la semana tiene un año, es decir, cuantos lunes, cuantos martes…

En tres celdas, vamos a poner año, mes, y, día.

En la celda D2, usamos la función FECHA, que tiene tres argumentos, que son año, mes, y, día, dichos valores, son los de las celdas desde A2 a C2.

=FECHA(A2;B2;C2)

Lo siguiente que vamos a hacer, es usar la función SECUENCIA, para crear una matriz de 365 filas, de una columna, y, debe de comenzar a partir de la fecha especificada, por lo que escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.

=SECUENCIA(

Como argumento filas, ponemos 365.

=SECUENCIA(365;

Como argumento columnas, ponemos 1.

=SECUENCIA(365;1;

Y, como argumento inicio, especificamos la celda con la fecha.

Cerramos paréntesis, y, aceptamos.

=SECUENCIA(365;1;D2)

En este caso, tenemos un calendario desde el día 1 de enero, hasta, el 31 de diciembre del año 2022.

Tenemos el cuadro preparado, con los meses en vertical, y, los días en horizontal, para ser rellenado.

Pues, empecemos.

En la celda K3, vamos a hacer uso de la función TEXTO, abrimos un paréntesis, ponemos la función TEXTO.

=(TEXTO(

Como argumento valor, seleccionamos todas las fechas.

=(TEXTO(G2#;

Como argumento formato, entre comillas dobles, ponemos cuatro D.

=(TEXTO(G2#;"dddd")

Cerramos paréntesis, y, lo igualamos al valor de K2, y, cerramos paréntesis.

=(TEXTO(G2#;"dddd")=K2)

Obtenemos una matriz desbordada donde el día de la matriz desbordada G2# es igual a lunes, VERDADERO donde es lunes, y, FALSO donde no lo es.

Esta sería la primera condición, comparar cada día de cada fecha con el lunes, ahora, vamos por la segunda condición.

Ponemos el símbolo de asterisco (*), que es igual que el operador lógico Y, es decir, devuelve VERDADERO, si todas las condiciones se cumplen.

Abrimos otro paréntesis, donde de nuevo, ponemos la función TEXTO, como argumento valor, sigue siendo las fechas, como argumento formato, entre comillas dobles, ponemos cuatro M, y, lo igualamos al valor de la celda J3.

Cerramos paréntesis, y, aceptamos.

=(TEXTO(G2#;"dddd")=K2)*(TEXTO(G2#;"mmmm")=J3)

Tenemos una matriz desbordada, ahora, VERDADERO se ha convertido en 1, y, FALSO se ha convertido en 0, 1 quiere decir que hay coincidencia en el día de la semana y mes, y, 0 que no la hay.

Pero, queremos el total, por lo que después del signo igual, ponemos la función SUMA.

=SUMA((TEXTO(G2#;"dddd")=K2)*(TEXTO(G2#;"mmmm")=J3))

Vemos que hay cuatro lunes para el mes de enero.

Ahora debemos de copiar, tanto hacia abajo, como a la derecha, por lo que debemos de fijar G2 para que no se actualice al copiar, fijamos la fila de la referencia K2, para que, al copiar hacia abajo, no se actualice, y, la columna de la referencia J3, para que al copiar hacia la derecha tampoco de actualice.

=SUMA((TEXTO($G$2#;"dddd")=K$2)*(TEXTO($G$2#;"mmmm")=$J3))

Seleccionamos la celda, pulsamos CTRL más C, para copiar, a continuación, seleccionamos todo el rango, y, pulsamos CTRL más V, para pegar.

Ya tenemos cuantos días aparece cada día de la semana para cada mes.

Si hacemos una autosuma en los días de la semana, veremos que son 52 días.

Podemos cambiar el año, el mes, o, el día.

La función para usar en formato condicional, vamos a hacerla primero en una celda, para comprobar su funcionamiento, y, después lo llevamos a formato condicional.

Tenemos una función llamada RESIDUIO, que devuelve el resto de una división, devuelve 1 cuando la división no es exacta, y, 0 cuando lo es, entonces, voy a dividir cada número entre 3, ¿Por qué entre 3?, porque si dividimos 30 entre 3, nos devuelve una división exacta.

=RESIDUO(SUMA(K3:Q3);3)

Arrastramos, y, podemos ver que, para los meses con treinta días, nos devuelve cero, porque la división es exacta.

Pues, esta es la función para usar en formato condicional, seleccionamos, y, copiamos la función, ahora, seleccionamos los meses, y datos, nos vamos a la pestaña de inicio, desplegamos formato condicional, y, seleccionamos nueva regla.

En la ventana que se abre, marcamos la última opción.

Pegamos la función, y, lo igualamos a cero, pero, debemos del poner el símbolo de dólar delante de K3, y, de Q3, para cuando haya coincidencia, se resalta toda la fila.

Aplicamos un formato.

Aceptamos, y, podemos ver como los meses con 30 días, aparecen con otro formato.

Para el siguiente ejemplo, vamos a aprender a realizar un índice dinámico, trabajamos con el modelo que suelo usar habitualmente, pero solo con las columnas provincia, centro, y, producto.

En la celda F2, creamos una lista desplegable a través de una validación de datos para seleccionar un encabezado.

Seleccionamos un encabezado.

Lo siguiente es traernos los datos del encabezado seleccionado, para ello, vamos a usar INDIRECTO, donde como argumento referencia, entre comillas dobles ponemos el nombre de la tabla seguido de una apertura de corchetes.

=INDIRECTO("Ventas["

Concatenamos con F1.

=INDIRECTO("Ventas[" & F1

Concatenamos con un cierre de corchetes, y, cerramos paréntesis.

=INDIRECTO("Ventas[" & F1 & "]")

Nos quedamos con los valores únicos.

=UNICOS(INDIRECTO("Ventas[" & F1 & "]"))

En la celda E2, vamos a crear el índice dinámico, lo primero es saber cuantos elementos componen los resultados obtenidos, después de haber seleccionado un encabezado, para ello, usamos CONTARA, y, como argumento es la matriz desbordada F2#.

=CONTARA(F2#)

En este caso, nos devuelve un recuento de 6.

Pues, ya tenemos el argumento filas de la función SECUENCIA, después del signo igual, ponemos SECUENCIA, como argumento filas, es la función CONTARA, omitimos el resto de los argumentos.

=SECUENCIA(CONTARA(F2#))

Aceptamos, y, ya lo tenemos, si vamos cambiando de encabezado, veremos como se crear dicho contador.

Miguel Angel Franco