SUMAS ACUMULADAS POR RANGO SEGÚN CRITERIOS

Hola a todos:

Hoy vamos a seguir con las fórmulas, en concreto con el mundo de las sumas acumuladas y la problemática que surge en determinados casos.

Pongamos un ejemplo visual del problema:

SUMAS ACUMULADAS POR RANGO SEGÚN CRITERIOS

Como podéis observar se trata de obtener el sumatorio de los importes en cada rango en función de si en la columna C hay datos (en este caso, fechas).

Esta tarea es posible que se pueda resolver con una fórmula matricial (tengo pendiente estudiarlo), sin embargo, lo podemos resolver con una combinación de varias fórmulas:

En concreto la fórmula que os propongo es la siguiente:

=SI(FILA(B2)>2;SI.ERROR(SI(C2<>"";SUMA($A$2:INDIRECTO("A"&FILA(B2)));"")-SUMA($B$2:INDIRECTO("B"&(FILA(C2)-1)));"");SI(C2<>"";SUMA($A$2:INDIRECTO("A"&FILA(B2)));""))

Es importante en esta fórmula tener presente el número de fila en el que estamos situados en cada momento, dado que la segunda celda (teniendo en cuenta que tenemos encabezados), la fórmula será diferente a las demás, por ello indico un condicional al inicio de la fórmula dependiendo de si la fila en la que estamos es mayor o menor de 2.

La idea es primero capturar el acumulado desde la fila A2 hasta la fila en la que estamos:

=SUMA($A$2:INDIRECTO("A"&FILA(B2)))

Y luego restar lo que ya hemos acumulado

-SUMA($B$2:INDIRECTO("B"&(FILA(C2)-1)))

De esta forma conseguimos el resultado requerido.

SUMAS ACUMULADAS POR RANGO SEGÚN CRITERIOS_1

Y eso es todo : )

Espero que os sea de utilidad!.

Descarga el archivo de ejemplo pulsando en: SUMAS ACUMULADAS POR RANGO SEGÚN CRITERIOS

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

Anuncios

CONTAR REGISTROS ÚNICOS CON VARIOS CRITERIOS

Hola a todos:

Hoy voy a trabajar un poco con formulación clásica de Excel. En concreto vamos a contar registros únicos en función de varios criterios. Para ello he preparado una sencilla tabla con unos datos que nos van a servir de ejemplo:

CONTAR REGISTROS UNICOS CON VARIOS CRITERIOS

Imaginad que queremos contar los registros únicos de la columna C que dependan de “Dirección de Area 1 y que el criterio de la columna B sea mayor o igual a 1. Es decir:

CONTAR REGISTROS UNICOS CON VARIOS CRITERIOS1

Qué los registros únicos serán 3, las Subdirecciones Zona 1.1, 1.2 y 1.3

Para poder realizar esto con una fórmula vamos a utilizar la siguiente función matricial:

{=CONTAR(1/FRECUENCIA(SI(Hoja1!$A$2:$A$18="Dirección Area 1";SI(Hoja1!$B$2:$B$18>=1;COINCIDIR(Hoja1!$C$2:$C$18;Hoja1!$C$2:$C$18;0)));FILA($A$2:$A$18)-FILA(DESREF($A$2:$A$18;;;1;))+1))}

Como podéis ver, estamos usando varios fórmulas, CONTAR, FRECUENCIA, SI, COINCIDIR, FILA y DESREF.

El resultado es el siguiente:

CONTAR REGISTROS UNICOS CON VARIOS CRITERIOS2

Obviamente, la fórmula se puede modificar variando e incluyendo nuevos criterios, solo debéis realizar algunas pruebas.

Os recuerdo que las matrices se introducen: seleccionando la celda que contiene la fórmula, pulsando en F2 , seleccionamos la fórmula y luego presionamos CTRL + MAYUS + ENTRAR

Descarga el archivo de ejemplo pulsando en: CONTAR REGISTROS ÚNICOS CON VARIOS CRITERIOS

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

SUMAR CADA N VALOR DE UN RANGO

No es la primera vez que tengo que recurrir a código cuando necesito realizar alguna tarea a la que las fórmulas no son capaces de llegar. Pero esto no siempre sucede, en algunas ocasiones, fruto de la combinación de funciones podemos llegar a resultados igual de satisfactorios.

Por ejemplo, podemos sumar cada n valor de un rango de celdas, es decir, por ejemplo, sumar el contenido de cada dos celdas.

SUMAR CADA N VALOR DE UN RANGO

En este ejemplo he marcado en azul las celdas que necesito sumar, que son las que van de dos en dos (sin tener en cuenta el encabezado). El resultado debe darnos 156.

Para lograr el cálculo debemos utilizar la siguiente fórmula o función:

{=SUMA(SI(RESIDUO(FILA(INDIRECTO("1:"&CONTARA(A1:A26)))-1;2)=0;A1:A26;""))}

En realidad la clave de esta función matricial se apoya en el uso de la función residuo, condicionando su resultado a que los números del rango (matriz) se consideren o no para la suma. Esto lo podéis observar si aplicáis solo parte de la fórmula pero en otra columna, en la “B”:

{=SI(RESIDUO(FILA(INDIRECTO("1:"&CONTARA($A$1:$A$26)))-1;2)=0;$A$1:$A$26;"")}

Este sería el resultado:

SUMAR CADA N VALOR DE UN RANGO1

Como podéis ver, cuando el resultado de Residuo es cero, es lo mismo que decir que la fila es divisible por ese “n” número definido, en este caso el 2. Al ser cierto, la función “si” condicional, toma ese número en la suma y si no lo es, no lo incluye. En el ejemplo de arriba, simplemente he aplicado parte de fórmula para lo que veáis.

De nuevo os recuerdo que las matrices se introducen: seleccionando la fórmula y luego presionando CTRL + MAYUS + ENTRAR

Aunque no tenía intención, al final no me pude resistir y he reproducido el mismo ejercicio pero con macros. Se puede hacer de muchas formas, pero lo he resuelto con un bucle for next:

Sub SUMAR_N_VALORES()
With Sheets(1)
Dim i As Double
Dim j As Double
fin = Application.CountA(.Range("A:A"))
'Desde la celda 2 hasta fin menos 1 (cabecero) e incrementando de 2 en 2
For i = 2 To fin - 1 Step 2
'Acumulamos en j el resultado de la suma
j = j + i
Next i
'Igualamos valor de celda a resultado
.Cells(2, 3) = j
End With
End Sub

El resultado es exactamente el mismo utilizando tanto la fórmula como la macro:

SUMAR CADA N VALOR DE UN RANGO

Ahora solo queda que lo adaptéis a vuestros proyectos y trabajos y obtengáis los beneficios de trabajar con Excel.

Descarga el archivo de ejemplo pulsando en: SUMAR CADA N VALOR DE UN RANGO