25 sept 20223 Min. de lectura

Calculo del total por producto y vendedor

En el siguiente modelo tenemos una tabla de hechos, con un IDproducto, y, unidades vendidas.

Tenemos otro modelo con los productos.

Tenemos otro modelo donde tenemos el idproducto, el nombre del producto, idvendedor1, idvendedor2, y, coste de producto.

Teniendo estos tres modelos, encontrar las unidades vendidas de cada producto no es difícil, pues, del modelo producto, buscamos el producto en el modelo coste de producto, ahora, buscamos el idproducto en el modelo idproducto, y, que nos devuelva las unidades vendidas.

También, queremos resumir por vendedor.

Tenemos otro modelo, donde tenemos el idvendedor, y, el vendedor.

Entonces, buscando el vendedor del modelo vendedor en el modelo idvendedor/vendedor, obtenemos el idvendedor, el cual buscamos en el modelo coste producto, una vez obtenido el idvendedor, buscamos que idproducto tiene asignado, el cual buscamos en la tabla idproducto, y, recuperamos las unidades vendidas, pero, un producto puede ser vendido por dos vendedores, por ejemplo, el producto aspiradora, ha sido vendido por el vendedor 102, y, 101.

Todos los modelos están en formato de rango.

Vamos a comenzar por las unidades totales por producto.

Como hemos dicho, tenemos una tabla con el idproducto, y, las unidades vendidas, pero, lo primera que tenemos que hacer es conseguir el idproducto, de la tabla coste de producto.

Podemos hacerlo con la función BUSCARX, donde busco cada producto en el rango producto, en la columna producto del modelo coste de producto, y, que me devuelva la columna idproducto.

=BUSCARX(B14;G4:G9;F4:F9)

Obtenemos el id para el primer producto.

Fijamos las siguientes referencias y arrastramos.

=BUSCARX($B14;$G$4:$G$9;$F$4:$F$9)

Ya tenemos el id para cada producto.

También, podemos hacerlo con la función INDICE, junto con COINCIDIR, usaremos la función COINCIDIR para encontrar la fila que debe de devolver.

=INDICE(F4:F9;COINCIDIR(Hoja1!B14;G4:G9;0))

Fijamos las siguientes referencias, y, arrastramos.

=INDICE($F$4:$F$9;COINCIDIR($B14;$G$4:$G$9;0))

Vemos que tenemos los mismos resultados.

Ahora, debemos de encontrar el idproducto en el modelo idproducto, y, que nos devuelva las cantidades vendidas, pero, algunos idproducto se repiten, por lo que voy a usar la función SUMAR.SI.CONJUNTO.

Donde como argumento rango de suma, seleccionamos M4:M21 del modelo idproducto, como argumento rango de criterios 1, seleccionamos el rango L4:L21, del modelo idproducto, y, como criterio1, es la expresión anterior.

=SUMAR.SI.CONJUNTO(M4:M21;L4:L21;BUSCARX(Hoja1!$B14;Hoja1!$G$4:$G$9;Hoja1!$F$4:$F$9))

Aceptamos, y, tenemos las cantidades para el primer producto.

Fijamos la siguiente referencia, y, arrastramos.

=SUMAR.SI.CONJUNTO($M$4:$M$21;$L$4:$L$21;BUSCARX(Hoja1!$B14;Hoja1!$G$4:$G$9;Hoja1!$F$4:$F$9))

Ya tenemos las unidades vendidas para cada producto.

Para calcular el total, nos colocamos en la celda C20, y, pulsamos ALT más igual (auto suma).

Lo siguiente es identificar el producto, y, que nos devuelva las unidades vendidas por los vendedores, dichos datos, se encuentran en el modelo coste de producto, pero, como dijimos anteriormente hay productos que han sido vendidos por más de un vendedor.

Bien, con la función INDICE, y, COINCIDIR, le digo que de la matriz B4:B6, me devuelva el numero de fila que indica la función COINCIDIR, donde buscamos el valor de G14, en el rango C4:C6, con una coincidencia exacta.

=INDICE(B4:B6;COINCIDIR(G14;C4:C6;0))

Aceptamos, y, tenemos el idvendedor.

Como hemos dicho, un vendedor puede aparecer en la columna idvendedor-1, e, idvendedor-2, por lo que debemos de preguntar que si el valor obtenido anteriormente, se encuentra en una de las dos columnas, para ello, debo de preguntar dos veces, y, con que una de las condiciones se cumplan, me vale, para ello, voy a usar el operador lógico O, que devuelve VERDADERO, si una de las condiciones se cumple.

Quedaría como sigue:

=(H4:H9=INDICE(B4:B6;COINCIDIR(G14;C4:C6;0)))+(I4:I9=INDICE(B4:B6;COINCIDIR(G14;C4:C6;0)))

Si se dan cuenta no he usado el operador lógico O, en su lugar, entre paréntesis hemos puesto la primera condición, seguido del signo mas (+) que equivale al operador lógico O, y, entre paréntesis la segunda condición.

Tenemos una matriz desbordada con cero donde no hay coincidencia, y, uno, donde si ha habido coincidencia.

Ahora, voy a preguntar que, si el resultado de la comparación anterior es igual a 1, que me devuelva en rango F4:F9, es decir, los idproducto del modelo coste de producto, y, omito el argumento valor si falso del condicional SI.

=SI((H4:H9=INDICE(B4:B6;COINCIDIR(G14;C4:C6;0)))+(I4:I9=INDICE(B4:B6;COINCIDIR(G14;C4:C6;0)));F4:F9)

Aceptamos, y, vemos los id de cada producto.

Lo siguiente es buscar estos idproducto en la tabla idproducto, y, que nos devuelva la cantidad, para ello, voy a usar la función SUMAR.SI.CONJUNTO.

Donde como argumento rango de suma, seleccionamos el rango M4:M21, como argumento rango de criterios 1, seleccionamos el rango L4:L21, y, como argumento criterio 1, es la expresión anterior.