En este artículo utilizaremos distintas técnicas para calcular los valores acumulados en un conjunto de datos.
Con este conjunto de datos vamos a calcular el acumulado de distintas formas:
- Una función SUMA
- Una operación +
- Un sistema SCAN+LAMBDA
En cada una de ellas escribiremos o bien una función que arrastraremos o bien una función que devolverá una matriz. Es decir, no habrá funciones diferenciadas en base a la fila dónde se encuentre.
Opción 1. Función SUMA
El truco de la función SUMA para que no deba crearse funciones diferenciadas es sumar un rango dónde la primera celda tenga una referencia fija y la segunda sea móvil.
Así, para la primera celda del conjunto de datos, estableceremos la suma desde la celda de la misma fila pero de la columna de ventas fijada (o sea: $C$4) hasta esa propia celda.
=SUMA($C$4:C4)
Cuando escribimos esa función observamos el siguiente comportamiento.
- Escribimos =SUMA( y seleccionamos la celda C4 –> =SUMA(C4
- Si pulsamos F4 para fijar esa referencia aparecen los dólares –> =SUMA($C$4
- Si pulsamos : desaparecen los dólares y aparece la misma dirección –> =SUMA(C4:C4
- … y de aquí no le sacamos (al menos no fácilmente)
A. En dos pasos:
- Escribimos la función sin fijar la referencia –> =SUMA(C4:C4)
- Editamos la función y fijamos únicamente la primera de las celdas –> =SUMA($C$4:C4)
B. Fijar sin utilizar F4
- En lugar de seleccionar la celda C4, escribimos su dirección incluyendo los signos del dólar.
- Seguimos con los : y acabamos con C4 sin dólares
C. Cambiar el modo de introducción durante la escritura de la función.
- Escribimos la función sin fijado –> =SUMA(C4:C4)
- Antes de dar INTRO, pulsamos la tecla F2 (en la parte inferior izquierda de la pantalla el modo de celda ha cambiado de introducir a modificar). En ese momento podemos seleccionar la primera de las referencias y pulsar F4 para fijarla sin que la segunda se vea afectada.
Una vez obtenida la fórmula la extendemos por todo el rango.
Opción 2. Operador +
Para la siguiente modalidad utilizaremos una función de suma con el pretexto de que la venta acumulada de una línea no deja de ser el acumulado anterior + la venta de la línea actual. Así que escribimos la función en la celda E4:
=E3+C4
Obviamente eso nos devuelve un error #¡VALOR! puesto que la celda E3 no contiene un valor numérico.
La solución generalmente aplicada es la de crear una función en la primera fila distinta del resto: la primera fila se iguala con la primera venta y el resto utilizan la fórmula antes propuesta.
Pero esa respuesta puede no ser la adecuada por distintos motivos. Se me ocurren por lo menos 2:
- Que alguien inserte una línea entre los 2 primeros clientes heredando la función. Tanto si hereda la primera como segunda, el resultado será incorrecto.
- Que estemos en una tabla en cuyo caso se impone la necesidad de que todas las fórmulas sean iguales.
La solución para este problema pasa por evaluar el valor de la celda superior para, si es un numero, tenerlo en cuenta y, en caso contrario, obviarlo.
Evidentemente podríamos empezar a retorcer la función con la utilización de funciones SI y ESNUMERO pero es matar moscas a cañonazos.
Excel nos lo pone muchísimo más simple: la función N. Esa función es capaz de realizar la conversión de los datos. Básicamente, si encuentra algo que no sea un número o una fecha, toma valor 0. De esa forma, al encontrar un «Acumulado» en la primera celda, tomará valor 0.
Opción 3. Un sistema SCAN+LAMBDA
Esta opción se basa en la capacidad de la función SCAN de recorrer una matriz e ir acumulando en una variable los valores según una función definida en una LAMBDA
Paso 1. Iniciar la función SCAN con el valor inicial y el rango a utilizar.
=SCAN(0;C4:C23
Paso 2. Incluir la función LAMBDA dónde indicaremos el nombre que daremos al parámetro que almacenará el acumulado (pAcumulado), el nombre que daremos al parámetro que tomará cada valor de la matriz escaneada (pVenta) y, finalmente, que función/formula aplicaremos (en este caso que, para cada línea, el pAcumulado es = al pAcumulado + pVenta)
=SCAN(0;C4:C23;LAMBDA(pAcumulado;pVenta;pAcumulado+pVenta))
Nota: una versión un pelín friki pero que está en la línea de las funciones matriciales actuales.
Si solo queremos el resultado total, en lugar de utilizar la función SCAN podemos utilizar la función REDUCE que devuelve solo el último valor.
Deja un comentario si te gustó
0 comentarios