Función SCAN – Excel y Google Sheets
Este tutorial demostrará cómo utilizar la función SCAN en Excel. (Nota: Esta función sólo está disponible en Excel 365.)
Definición
La función SCAN aplica una función LAMBDA para cada elemento de un array y devuelve un array de resultados con el mismo tamaño que el array de origen.
=SCAN(0;C3:C7;LAMBDA(a;b;a+b))
Sintaxis y Argumentos
Esta es la sintaxis de la función SCAN:
=SCAN ([valor_inicial]; array; lambda(acumulador; valor))Valor_inicial
El 1er argumento es el valor_inicial. Este argumento es el valor inicial del acumulador en la función LAMBDA. Es una entrada opcional con un valor vacío por defecto.
Array
El 2do argumento es el array, que es el array fuente. La función SCAN iterará y aplicará la función LAMBDA a través de cada elemento de este array dado.
Nota: La dimensión del array puede ser 1D Vertical u Horizontal o 2D.
Función LAMBDA
La función LAMBDA en la función SCAN consta de dos parámetros y una fórmula:
Acumulador:
En una suma acumulativa, el acumulador acumula su valor por iteración, pero esto no es necesariamente cierto para todos los escenarios. El acumulador es técnicamente la salida de la Función LAMBDA del elemento anterior.
Nota: Su valor inicial depende del argumento valor_inicial.
Valor
El valor es el elemento actual del array.
Una propiedad importante del parámetro valor es que también toma la naturaleza del elemento del array. Si el array es un rango, entonces el parámetro value se convierte en una celda. Por lo tanto, podemos utilizar funciones que pueden obtener las propiedades de las celdas, como la función FILA.
=SCAN(0;C3:C7;LAMBDA(a;b;FILA(b)))
Nota: Podemos utilizar cualquier letra como variable para el acumulador y el valor.
Fórmula Personalizada:
Aquí utilizamos cualquier fórmula y anidamos cualquier función.
Nota: Podemos omitir el acumulador, el valor o ambos parámetros de la fórmula personalizada.
Debido a la naturaleza de su Función LAMBDA, la mayoría de las aplicaciones de la Función SCAN están relacionadas con cálculos acumulativos. Veamos algunas de las aplicaciones.
Suma Acumulada
Con la función SCAN, ahora podemos realizar fácilmente una suma acumuda de matrices.
=SCAN(0;C3:C7;LAMBDA(a;b;a+b))
Vamos a recorrer la fórmula:
1ª Iteración
El acumulador (a) comienza con el valor_inicial.
El valor (b) comienza con la 1era celda del rango.
=C3
Por último, calculamos la suma de los dos.
=E3+F3
Iteraciones siguientes
El acumulador (a) es ahora igual a la salida anterior de la función LAMBDA.
=G3
El parámetro de valor (b) es igual al elemento actual del array.
=C4
De nuevo, podemos calcular la suma de los dos.
=E4+F4
Todo este proceso continúa hasta el último elemento de la matriz.
Promedio Acumulado
También podemos realizar una media acumulada utilizando la función SCAN.
=SCAN(0;C3:C7;LAMBDA(a;b;a+b))/SECUENCIA(CONTARA(C3:C7))
Vamos a recorrer la fórmula:
Suma acumulada
En primer lugar, utilizamos la función SCAN para realizar una suma acumulativa de matrices (véase la sección anterior para el desglose).
=SCAN(0;C3:C7;LAMBDA(a;b;a+b))
Función CONTARA
A continuación, contamos el número total de elementos del array utilizando la función CONTARA.
=CONTARA(C3:C7)
Función SECUENCIA
Luego, utilizamos el resultado de la Función CONTARA para crear una lista vertical de números consecutivos utilizando la Función SECUENCIA.
=SECUENCIA(F3)
Promedio Acumulado
Por último, podemos calcular la media acumulada dividiendo los resultados de la Función SECUENCIA por la suma acumulada.
=E3/G3
Combinando todas las fórmulas se obtiene nuestra fórmula original:
=SCAN(0;C3:C7;LAMBDA(a;b;a+b))/SECUENCIA(CONTARA(C3:C7))Máximo Acumulado
También podemos calcular el valor máximo acumulado utilizando la función SCAN junto con la función MAX.
=SCAN(0;C3:C7;LAMBDA(a;b;MAX(a;b)))
Veamos la fórmula.
1ª Iteración
El acumulador (a) comienza con el valor_inicial.
El parámetro de valor (b) comienza con la 1era celda del rango.
=C3
Por último, calculamos el máximo entre las dos utilizando la función MAX.
=MAX(E3;F3)
Iteraciones siguientes
El acumulador (a) es ahora igual a la salida anterior de la función LAMBDA.
=G3
El parámetro de valor (b) es igual al elemento actual del array.
=C4
Repetimos la función MAX.
=MAX(E4;F4)
Todo este proceso continúa hasta el último elemento de la matriz.
Suma Acumulada Con Reinicio
Hay escenarios en los que necesitamos restablecer la suma acumulada. Para hacer esto con la función SCAN, usamos la función IF para reiniciar el acumulador. Veamos los ejemplos.
Total del Año Hasta la Fecha
Un ejemplo perfecto de una suma acumulativa con reinicio es el total del año hasta la fecha. En este caso, necesitamos reiniciar la suma cada enero del nuevo año.
=SCAN(0;C3:C16;LAMBDA(a;b;SI(MES(DESREF(b;;-1))=1;b;a+b)))
Repasemos la fórmula.
Función DESREF
Como la matriz es un rango, podemos utilizar la función DESREF para obtener la fecha correspondiente de la columna de fecha.
=DESREF(C3;;-1)
Función MES
A continuación, utilizamos la función MES para obtener el número del mes a partir de la fecha.
Función SI
Por último, utilizamos la Función SI para comprobar si el número del mes es 1. Si es VERDADERO, reiniciamos el acumulador (a) devolviendo el elemento actual del array (b) en lugar de calcular la suma (ver resaltado en rojo). En caso contrario, calculamos la suma de a y b.
Combinando todas las fórmulas se obtiene nuestra fórmula original:
=SCAN(0;C3:C16;LAMBDA(a;b;SI(MES(DESREF(b;;-1))=1;b;a+b)))Restablecer Cada Enésima Fila
También podemos reiniciar el acumulador en cada enésima fila. En el caso anterior, debemos reiniciar la suma cada 1era fila en un patrón de 12 series.
=SCAN(0;C3:C16;LAMBDA(a;b;SI(RESIDUO(FILAS(C3:b)-1;12)=0;b;a+b)))
Repasemos la fórmula:
Función FILAS
Primero, usamos la función FILAS para obtener la fila relativa del valor (b) de la primera entrada (C3) del array.
=FILAS($C$3:C3)
Relativo Desde 0
A continuación, deducimos 1 de las filas relativas para hacer referencia a las filas relativas desde 0 en lugar de 1. La razón principal para hacer esto es para la creación del patrón de la serie 12. Empezando por 0, podemos crear una serie de números consecutivos que empiecen por 0 (véase la siguiente sección).
=F3-1
Función RESIDUO
A continuación, dividimos cada fila relativa por el número total del patrón de la serie, que es 12, y obtenemos el resto utilizando la función RESIDUO. Esto crea el patrón de la serie 12 (0 – 11).
=RESIDUO(G3;12)
Función SI
Observa que la 1era fila del patrón de la serie 12 es siempre 0. Ahora usamos la función SI para comprobar si hay 0 y devolver el elemento actual del array (b) para reiniciar el acumulador (a). En caso contrario, calculamos la suma.
=SI(H3=0;C3;E3+C3)
Combinando todas las fórmulas se obtiene nuestra fórmula original:
=SCAN(0;C3:C16;LAMBDA(a;b;SI(RESIDUO(FILAS(C3:b)-1;12)=0;b;a+b)))Suma Acumulada por Fila
Podemos ampliar la aplicación de la suma acumulada para añadir otra dimensión como una suma acumulada por fila. En el caso siguiente, tomamos las ventas acumuladas de cada vendedor a medida que avanza el mes de enero a marzo.
=SCAN(0;C3:E7;LAMBDA(a;b;SI(COLUMNA(b)=3;b;a+b)))
Veamos la fórmula:
Función COLUMNA
Primero, usamos la función COLUMNA para obtener las posiciones de las columnas de los elementos del array.
=COLUMNA(C3)
Función SI
A continuación, utilizamos la función IF para restablecer el acumulador (a). Si la posición de la columna es igual a 3 (Columna de Enero), entonces reseteamos el acumulador al elemento actual del array (b). En caso contrario, calculamos la suma.
=SI(J3=3;C3;G3+C3)
Combinando todas las fórmulas se obtiene nuestra fórmula original:
=SCAN(0;C3:E7;LAMBDA(a;b;SI(COLUMNA(b)=3;b;a+b)))SCAN Google Sheets
Actualmente la función SCAN no está disponible en Google Sheets
