Acumular las compras por cliente

Los datos de compras de cinco clientes entre los años 2008 a 2011 han sido puestos en una hoja de cálculo en el rango B2:D24. Deseamos obtener las ventas totales de cada cliente y las acumuladas en cada año (F2:K7).

El primer paso consistirá, con ayuda de Filtro avanzado, en colocar la lista de clientes en la columna F. Para ello, seleccionamos B2:B24 y accedemos a Datos + Avanzadas.

Excel mostrará el cuadro de diálogo Filtro avanzado. Lo rellenamos indicando que queremos copiar, en otro lugar, sólo registros únicos. El lugar elegido será la celda F2.

El resultado será una lista desordenada.

Para ordenarla, seleccionamos F2:K7 y accedemos a Datos + Ordenar. En el cuadro de diálogo Ordenar, dejamos los valores por defecto y pulsamos Aceptar.

La lista habrá quedado ordenada por la columna Comprador.

Las ventas acumuladas de cada cliente (columna G) pueden obtenerse con distintas fórmulas: matriciales y no matriciales. Estudiaremos cuatro soluciones:

1ª solución (no matricial):  Con la función SUMAR.SI(rango;criterios;[rango_suma])

En G3:

=SUMAR.SI($B$3:$B$24;F3;$D$3:$D$24)

La función SUMAR.SI tiene tres argumento: el primero es el rango de datos que deben cumplir un criterio para que puedan ser admitidos; el segundo es el criterio propiamente dicho (los datos del primer argumento que no cumplan este criterio serán desechados); el tercero es el rango que se debe sumar si se cumple el criterio.

Extendemos la fórmula hasta la fila 7.

2ª solución (matricial): Con la función SUMA combinada con SI

En G3:

=SUMA(SI($B$3:$B$24=F3;$D$3:$D$24))     [Terminar con Ctrl + Mayúscula + Intro]

Únicamente se suman los datos de la columna D si en la misma fila de la columna B está el mismo dato que hay en F3.

Extendemos la fórmula hasta la fila 7.

3ª solución (matricial): Con la función SUMA

En G3:

=SUMA(($B$3:$B$24=F3)*($D$3:$D$24))     [Terminar con Ctrl + Mayúscula + Intro]

($B$3:$B$24=F3) devuelve una matriz de valores VERDADERO y FALSO. Lo comprobamos seleccionando M3:M24, escribiendo: =($B$3:$B$24=F3) y terminando con Ctrl + Mayúscula + Intro. A efectos numéricos, VERDADERO es 1 y FALSO es 0.

($B$3:$B$24=F3)*($D$3:$D$24) es el producto de dos matrices: una de VERDADEROS y FALSOS; la otra, es la matriz de la columna D. El resultado será una matriz de ceros (cuando el dato de la primera matriz es FALSO) y valores de la columna D (cuando es VERDADERO). Para comprobarlo, seleccionamos N3:N24, escribimos: =($B$3:$B$24=F3)*($D$3:$D$24) y terminamos con Ctrl + Mayúscula + Intro.

Finalmente, sumamos esta última columna con SUMA. Estamos trabajando con matrices, por lo que debemos terminar con Ctrl + Mayúscula + Intro. Sólo faltará extender la fórmula hasta la fila 7.

4ª solución (no matricial): Con la función SUMAPRODUCTO

En G3:

=SUMAPRODUCTO(($B$3:$B$24=F3)*($D$3:$D$24))

La fórmula es igual que la anterior, salvo que en vez de SUMA hemos puesto SUMAPRODUCTO. Esta función tiene la virtud de convertir fórmulas matriciales en no matriciales, por tanto, se termina con Intro. Como en los casos anteriores, extendemos la fórmula hasta la fila 7 para completar los totales acumulados de todos los compradores.

Las ventas por año de cada cliente (columnas H, I, J y K) también se pueden obtener de distintas maneras. Cualquiera de las fórmulas siguientes es válida:

En H3:

1ª fórmula:

=SUMAPRODUCTO(($B$3:$B$24=$F3)*(AÑO($C$3:$C$24)=H$2)*($D$3:$D$24))      [Pulsar Intro]

2ª fórmula:

=SUMA(SI($B$3:$B$24=$F3;SI(AÑO($C$3:$C$24)=H$2;$D$3:$D$24;0);0))     [Pulsar Ctrl + Mayús + Intro]

3ª fórmula:

=SUMA(($B$3:$B$24=$F3)*(AÑO($C$3:$C$24)=H$2)*($D$3:$D$24))     [Pulsar Ctrl + Mayús + Intro]

En los tres casos el resultado es cero ya que el primer cliente, Aitor, no ha realizado ninguna compra en el año 2008.

Extendiendo la fórmula al rango H3:K7 el ejercicio queda terminado. Se puede comprobar que, para cada comprador, la columna Acumulado es la suma de las compras realizadas durante los años 2008 a 2011.

Descargar archivo (SE1-Ventas acumuladas.xls)

Descargar archivo (SE1-Ventas acumuladas.xlsx)